SQL rongoa ngā tukanga: te waihanga me te whakamahi

ngā tukanga SQL rongoa he kōwae pūmanawa whakamahi e taea te rongoa i roto i te pātengi raraunga i roto i te puka o ngā taonga. I roto i te mau parau te tahi atu, ko reira he ahanoa e kei SQL-tauākī. Ka taea te mahi i ēnei ngā tukanga rongoa i roto i te tono kiritaki ki te tiki i te mahi pai. I tua atu, kei te maha i huaina taua wāhi i te tahi atu hōtuhi ranei ara i tetahi atu wāhanga.

whakataki

whakapono tokomaha iwi e he rite ki nga ngā tukanga o te ngā ratou reo taumata-tiketike hōtaka (aua, ki te kahore hoki MS SQL). Mahalo he pono tenei. E ratou rite tawhā, ka taea e ratou te whakaputa i te uara ōrite. Ano, i roto i ētahi wā, haere mai ratou ki te whakapā. Hei tauira, kua ngā ratou ki pātengi raraunga o DDL me nga raraunga DML, me te mahi kaiwhakamahi (ingoa waehere - UDF).

ngā tukanga SQL Mau penapena i te whānui o te painga e te wehe ratou i roto i taua tukanga ngā. Haumaru, rerekētanga hōtaka hua - tenei katoa rata ana ngā kaiwhakamahi mahi ki pātengi raraunga, ake me te ake. Ko te tihi o te rongonui haere mai ngā tukanga mo te tau 2005-2010, ka ka e ahau te hōtaka i te "Microsoft" i raro i te ingoa «SQL Whakahaere Tūmau Studio». Ki te tona awhina, hei mahi ki te pātengi raraunga i nui māmā, atu mahi, me te ake watea. Tau i muri i te tau, i tenei tikanga o whakawhitihia mōhiohio riro rongonui i roto i te taiao hōtaka. I teie mahana, MS SQL Tūmau he hōtaka tino mua, e hoki ngā kaiwhakamahi ki "kōrero" ki pātengi raraunga, e tu ana i runga i te par ki "Excel".

A, no te karanga koutou ki te tikanga, kei te tonu tukatuka reira e te tūmau, kahore tukanga faufaa, me te wawaotanga kaiwhakamahi. ka taea e koe te mahi i tetahi mahi ki te kōrero: mukua, mahia, huringa. Neke atu i te tenei katoa kei roto i te DDL-operator, e mahi kotahi-i hoatu mahi matatini i runga i aua taonga. A reira katoa tupu rawa hohoro, a kahore e te tūmau mau utaina. Tenei tere me te hua kia hohoro koe te whakawhiti nui o ngā mōhiohio i te kaiwhakamahi ki te tūmau me te hurihia.

Hei whakatinana i tenei mahi ki te kōrero, i reira e rave rahi hangarau reo hōtaka. Ko ētahi o ēnei, mo te tauira, PL / SQL i te pūnaha whakahaere pātengi raraunga Oracle, psql i InterBase me ngā pūnaha Firebird, rite pai rite te aronui "maykrosoftovskih» faatere i-SQL. Kua hangaia katoa o ratou ki te hanga me te whakahaere i ngā tukanga rongoa, tuku ki a koutou te whakamahi i ō rātou ake hātepe i runga i te kawenga pātengi raraunga nui. He tika te reira me ki te āta whakarite i te hunga e kawe i roto i te whakahaere o taua mōhiohio, ka taea e te tiaki ahanoa katoa i kāore hunga-tuatoru me te, na reira, te hanga, whakarerekē mukunga o etahi raraunga ranei.

hua

Ka taea te programmed ēnei ahanoa pātengi raraunga i roto i ngā huarahi rerekē. Tenei e āhei ngā kaiwhakamahi ki te tīpako i te momo o te tukanga e pai kia tino tika, e whakaora wa, me te pūngao. I tua atu, kei te tukatuka te tikanga ano, ko te kupu te haapae wa nui pau i runga i te kōrero i waenganui i te tūmau me te kaiwhakamahi. Ka taea te reprogrammed te kōwae me te puta ke i roto i te aronga tika i roto i tino i tetahi wa. Rawa utu te tuhituhi i te tere ki ai te whakarewatanga o te tukanga SQL penapena puta: ko tere atu rite ki reira te tukanga, hanga ana e ia he watea, me te pūkenga.

haumaru

Tenei momo o te tukatuka mōhiohio rerekē i ngā tukanga ōrite i roto i taua whakarato reira haumarutanga whakarei. whakarite ana tēnei e te meka e taea te to'o atu kaiwhakamahi i ngā tukanga whakauru katoa. Tenei ka tukua te Kaiwhakahaere ki te whakahaere ngā mahi ki a ratou takitahi, kahore te wehi o te mōhio ranei whakaurunga ki te pātengi raraunga.

whakawhiti raraunga

Kōrero i waenganui i te tukanga SQL rongoa me te tono kiritaki ko ki te whakamahi i te tawhā, me te hoki mai uara. E kore te mea te whakamutunga e tika ana ki te tuku i te raraunga ki te tukanga rongoa, engari te mōhiohio (i runga i te tono o te kaiwhakamahi te nuinga), me tukatuka hoki SQL. Kua oti tukanga Kia rongoa tona mahi, ka tukua te reira i te pākete raraunga hoki (engari, ano, ki te hiahiatia) ki te tono karanga mā te whakamahi i te whānuitanga o ngā tikanga e e kia ai whakatinana rite te karanga ki te tukanga SQL rongoa me hoki, mo te tauira:

- raraunga tuku mā te momo Huaputa tawhā;

- raraunga tuku mā te tauākī hoki;

- tuku Raraunga mā kōwhiringa kawe.

Na inaianei titiro ki te āhua o titiro tenei tukanga te roto taua.

1. Waihanga Exec-rongoa i roto i te tikanga SQL

Ka taea e koe te hanga i tētahi tukanga i roto i MS SQL (Managment Studio). I muri i hanga te tikanga ko, kua whakarārangitia te reira i runga i te kōpuku pātengi raraunga programmable, i roto i nei whakamana i te tukanga e te kaiwhakahaere o te hanganga. Hei mahia SQL ngā tukanga rongoa te whakamahi i Exec-he tukanga e kei roto i te ingoa ahanoa.

A, no te hanga i te ingoa mai tukanga tuatahi, a ka hanga e tetahi atu ranei ngā tawhā e kua whakaritea ki a ia. Ka taea e ngā tawhā e kōwhiringa. I muri i te tawhā (s), arā te tinana o te tukanga, kia kia tuhituhi, he mea e tika ana ki te kawe i roto i te tahi mau ngā mahi e tika ana.

Ko te meka e taea whai i te tinana taurangi rohe, kei roto i te reira, a ka he rohe, me te i roto i te pā ana ki te ngā tukanga enei taurangi. I roto i te mau parau te tahi atu, e taea te whakaaro e ratou anake i roto i te maimoatanga tinana SQL Tūmau Microsoft. E whakaaro ngā tukanga Penapena i roto i tenei take rohe.

Ko te kupu, ki te waihanga i te tukanga, e hiahia ana matou he ingoa tukanga, me te i te iti rawa kia kotahi tawhā rite te tinana tukanga. Me mōhio e te kōwhiringa pai i roto i tenei take ko te hanga me te whakatinanatanga o ngā tukanga ki te ingoa aronuinga i roto i te classifier.

Ka taea e whai i te tinana tukanga ahua o ngā SQL tetahi, hei tauira, pērā i te hanga i te tepu, te whakauru i tetahi atu ranei rarangi o te tepu, te haamauraa i te momo pātengi raraunga, me te huru, a na i runga i. Otiia, i te tinana tukanga whakawhāiti etahi ngā mahi i roto i reira. E whakarārangitia ētahi o nga whāititanga tino nui i raro:

- e kore e te tinana te waihanga i tetahi atu tikanga penapena;

- e kore me te tinana waihanga he ongo teka e pā ana ki te ahanoa;

- e kore e te tinana te waihanga i tetahi tāpae.

2. Tautuhia te tāupe i roto i te tinana o te tikanga

Ka taea e hanga e koe taurangi rohe ki te tukanga o te tinana, a katahi ka waiho ratou i roto i te tinana tukanga. Ko te mahi pai, ko te ki te waihanga i tētahi tāupe i te timatanga o te tinana tikanga penapena. Ko taea hoki whakaturia koe taurangi ki hea i roto i te tinana o te ahanoa.

I te tahi taime kia kite koe e kua whakaturia etahi taurangi i roto i te rarangi kotahi, me ia tāupe wehea e te piko. mōhio hoki e prefixed te tāupe te ki @. I roto i te tinana o te tikanga, ka taea e koe whakaturia he tāupe ki e hiahia ana koe. Hei tauira, ka taea te kauwhautia te tāupe @ NAME1 ki te mutunga o te tinana tukanga. I roto i te tikanga ki te tautapa i te uara ki te tāupe whakaaturia te whakamahi i te huinga o te raraunga whaiaro. I roto i te rerekē ki te āhuatanga, ka whakaaturia neke atu i te kotahi tāupe i roto i te rarangi kotahi, kotahi anake huinga o ngā raraunga whaiaro e whakamahia ana i roto i tenei āhuatanga.

maha ui Kaiwhakamahi te uiraa: "Me pēhea te ki te tautapa uara maha i roto i te tauākī kotahi i roto i te tinana o te tukanga 'Well?. pātai Interesting, engari he nui māmā ake i a koutou whakaaro tenei. Ko te whakahoki: ki te awhina o taua takirua rite «Tīpako Var = uara". Ka taea e koe te whakamahi i enei takirua, wehea e ngā piko.

3. Waihanga he tukanga SQL rongoa

I roto i te whānui o ngā tauira o iwi momo whakaatu hanga he tukanga rongoa ohie, me te mahia i te reira. Heoi, kia tango i te tukanga taua tawhā e te tukanga piiraa e whai reira he uara tata ki reira (engari e kore e nga wa katoa). Ki te ōrite ratou, ka tīmata te tukanga hāngai i roto i te tinana. Hei tauira, ki te hanga koe i te tukanga e ka tangohia i te pa, me te rohe i te kaiwaea me hoki te raraunga e pā ana ki te nui kōrero te kaituhi ki te pa hāngai me te rohe. Ka uiui i te tukanga he tepu pātengi raraunga o kaituhi, hei tauira, Pubs, hoki e mahi ana i tenei kaituhi tātaitanga. Hei whiwhi i ēnei pātengi raraunga, hei tauira, tikiake te Google te hōtuhi SQL ki te whārangi SQL2005.

I roto i te tauira o mua, te tikanga e rua ngā tawhā, i roto i te reo Ingarihi e kia huaina herenga @State me @City. hāngai te momo raraunga ki te momo i tohua i roto i te tono. He te tinana tukanga taurangi ā-@TotalAuthors (kaituhi katoa), ka whakamahia tenei tāupe te ki te whakaatu i to ratou maha. Next mai he tono kōwhiringa wāhanga e kaute katoa. I te pae hopea, whakaaturia te uara tātai i roto i te matapihi putanga mā te whakamahi i te tauākī tā.

Me pēhea te ki te mahi i te tukanga SQL rongoa

E rua ngā huarahi ki te mahi i te tukanga. whakaaturia te ara tuatahi e haere tawhā rite mahi he rārangi piko-wehea te i muri i te ingoa tukanga. Pea to tatou e rua ngā uara (rite i roto i te tauira o mua). E kohia ēnei uara whakamahi taurangi a @State @City tukanga. I roto i tenei tikanga, tawhā nui o te tikanga tuku. huaina ana tēnei tikanga ko te raupapa tuku o tohenga. I roto i te tikanga tuarua, kua whakaritea kua nga tawhā tika, i roto i nei take e kore te mea nui te tikanga. mohiotia ana tēnei tikanga tuarua te rite te tuku o ngā tohenga ingoa.

paku ai te tukanga afe i te angamaheni. Kua neke katoa te taua, pera i roto i te tauira o mua, engari te tawhā ki konei anake. Ko te rongoa @City tawhā tuatahi, ko @State rongoa muri ki te uara taunoa. Kei te nuinga o te wā tohaina te tautuhinga taunoa wehe. SQL ngā tukanga rongoa he tawhā rite ohie. I roto i tenei take, ngā te kōwhiringa "te UT 'whakakapi te uara taunoa o" CA ". I roto i te haere i te whakatinanatanga tuarua kotahi anake uara tohenga mo @City, me taunoa kōwhiringa @State ki "CA". E tohutohu hötaka mātanga e e kei ofi ange ki te mutunga o te rārangi tawhā nga taurangi katoa taunoa. Kore, e kore te mea taea te mahia, ka whai koe ki te mahi ki te whakawhitinga o ngā tohenga ingoa e te roa me te uaua ake.

4. Ngā Hātepe Penapena Tūmau SQL: ara o hoki mai

E toru ngā huarahi matua ki te unga raraunga rongoa i roto i te tukanga i huaina. e rārangi ratou i raro:

- ngā uara hoki tukanga rongoa;

- Huaputa tawhā ngā tukanga rongoa;

- Tīpakohia tetahi o nga ngā tukanga penapena.

4.1 uara Hoki o ngā tukanga SQL penapena

I roto i tenei tikanga, whakatakoto ana te tukanga ki te uara o te tāupe rohe, me te hoki mai te reira. hoki e taea te tika te tukanga hoki i te uara tamau. I roto i te tauira e whai ake, kua hanga e matou he tukanga e hoki te katoa te maha o kaitautoko. Ki te whakarite tenei tatou ki te tikanga o mua, ka taea e kite koe e whakakapi te uara o te tā e te tahatua.

Na kia titiro a i pehea ki te mahi i te tukanga, me te whakaatu i te uara, hoki reira. ngā tukanga e mahi ana e hiahiatia ana ki te whakatū i me te tā tāupe, kawea nei i roto i muri i te tukanga. Pānui e hei utu o tā te operator taea te whakamahi i te Tīpakohia-operator, mo te tauira, Tīpakohia @RetValue, ko OutputValue.

4.2 ngā tukanga Huaputa Tawhā SQL rongoa

Ka taea te whakamahi i te uara whakautu ki te hoki mai te tāupe kotahi e kua tatou i kite i roto i te tauira o mua. Mā te whakamahi i te Huaputa taea te tukanga ki te unga tetahi atu ranei taurangi ki te rōpū karanga. denoted putanga tawhā te rite te wa tenei kupu matua «putanga» ina hanga tukanga. Ki te tohua te tawhā te rite te tawhā putanga, me tautapa te ahanoa tukanga reira he uara. Rongoa SQL ngā tukanga, tauira o e taea te kite i raro, i roto i te take taua e hoki ki te kōrero whakamutunga.

I roto i tenei tauira, i reira ka kia rua nga wiki ingoa: @TotalAuthors me @TotalNoContract. E tohua ana ratou i roto i te rārangi tawhā. E whakaritea ēnei taurangi uara i roto i te tinana tukanga. A, no te whakamahi tatou i te tawhā putanga, ka taea e te kaiwaea kite te uara whakaturia i roto i te tinana tukanga.

I tua atu, i roto i te tauira o mua, e whakaaturia e rua ngā taurangi ki te kite i te uara e kua tāutatia ngā tukanga penapena, Server MS SQL hei tawhā putanga. Na whakamana te tukanga e tono te uara noa «CA» tawhā. Ko te tawhā e whai ake nei he putanga, me te, na reira, i whakaatu e haere taurangi i roto i te tikanga tikanga. Me mōhio e ka hoki tohua te kupu matua putanga tāupe konei. I muri oti te tikanga kei te pai, kei te whakaaturia te uara hoki mā te whakamahi i te tawhā putanga i runga i te matapihi karere.

4.3 tīpako i te ngā tukanga SQL rongoa

whakamahia ana tēnei tikanga te ki hoki he huinga i roto i te puka ripanga uara raraunga (huingapūkete) mō te karanga i te tukanga rongoa. I roto i tenei tauira, SQL rongoa tukanga ki ngā tawhā @AuthID tono tepu "kaituhi" na roto i te tātari i te pūkete hoki e tenei @AuthId tawhā. whakatau Tīpakohia kaiwhakahaere aha kia hoki ki te karanga i te tukanga rongoa. A, no te AuthId tuku he tukanga rongoa hoki. He pera tonu te tukanga hoki kotahi anake record ranei kahore i te katoa. Heoi, e kore e te tukanga rongoa i tetahi here ki runga ki te hoki o neke atu i te kotahi te urunga. Pinepine e taea te kitea tauira i roto i nei mahi i te raraunga hoki tīpakohia te whakamahi i tawhā ki te taurangi tatau e whakarato he plurality o uara katoa.

I roto i te mutunga

Ko te hōtaka tino nui te tukanga penapena, hoki ranei whakawhitia, me te whakau i nga taurangi e tika ana e tika ana ki te tono kiritaki. Mai mahi ana te tukanga rongoa kei i runga i te tūmau ano, e taea te karohia utu raraunga i roto i ngā pukapuka nui i waenganui i te tūmau me te tono kiritaki (mo etahi tātai). hanga tenei reira taea ki te whakaiti i te kawenga i runga i te tūmau SQL, o te akoranga, ko nei, te ringa o ratou kau ma'u. Ko tētahi o nga momo e penapena ngā tukanga T SQL, engari e hiahiatia ana te ratou ako ki te hunga whai wāhi i roto i te hanga o te pātengi raraunga faahiahia. He he ano hoki he, ara nui nui nui o onäianei e taea e whai hua i roto i te ako o ngā tukanga penapena, Heoi, tenei hiahia mo te hunga e whakamahere ana ki te mahi i hōtaka kikī, tae atu ngaiotanga.