Tohutūmahi SQL roto Hono: tauira o wetereo me ngā āhuatanga

Whakawhanake i tetahi pātengi raraunga ngā kore anake te hanganga me te whakakī o tepu ki ngā mōhiohio, engari ano hoki te mahi anō ki te raraunga. Hei te mahi tika i te momo o ngā mahi i te kōwhiringa o ngā raraunga i tepu, me te pūrongo, te whakamahi i te Tīpakohia hoahoa paerewa.

Tiki raraunga i tepu

Ki te whakaaro tatou ki te raruraru o te whiriwhiri o ngā raraunga ranei te hanga o te pūrongo, ka taea e koe te whakatau i te taumata o te whīwhiwhi o te mahi. Ka rite ki te tikanga, ka e pā ana ki nui (kia rite ki te rōrahi) pātengi raraunga, e kua hanga e, mo te tauira, i roto i ngā toa ipurangi kamupene nui ranei, tīpakonga raraunga te kore iti ki te tepu kotahi. Ka rite ki te tikanga, kia waiho te tauira i te maha tika nui o tepu kihai hono anake, engari ratou ohanga hoki pātai / iti-pātai, e hanga te kaihātepe, i runga i tona mahi rānei. Hoki te tauira o te tepu kotahi taea te whakamahi i te hanganga māmā:

te wahi Tangata - ingoa o te tepu i nei e hiahia ana koe ki te hanga i tiki raraunga.

Ki te, Heoi, ka hiahia koe ki te tīpako raraunga i tepu maha, e taea te whakamahi tetahi o nga whakaaro paerewa ki te uru tepu maha.

tūhono Methods tepu atu

Ki te whakaaro tatou ki te whakamahi o taua hanganga i te taumata tuatahi, ka taea e tatou te wehewehe i te tikanga e whai ake nei ki te hono i te maha e hiahiatia mō te tepu tauira, ara:

1. Tohutūmahi roto Hono.
2. Mahue Hono ranei, ko reira te tikanga tuhi tuarua, Mauī waho Hono.
3. Cross Hono.
4. Hono tonu.

Mā te ngā kia uru tepu i mahi, ka taea e koe te ako mā te whakaaro te whakamahi o te tauākī SQL - roto Hono. e kia he tauira o tona whakamahi e whai ake:

SQL Reo me te Hono Inner Hono taea te whakamahi operator e kore anake hoki te whakakotahi e rua, neke atu rānei tepu, engari ano hoki ki te hono i te tahi atu iti-pātai, e nui whakahaere te mahi o ngā kaiwhakahaere pātengi raraunga, me, rite ki te tikanga, ka taea e tino whakatere i te whakatinanatanga o motuhake, hanganga rapu uaua.

Te whakakotahi raraunga i roto i te rārangi tepu i te raina

Ki te whakaaro tatou ki te hononga o te tau nui o iti-uiui, me raraunga huihui ki te aho ripanga kotahi ki te aho, ka taea e hoki koe te whakamahi i te Union ngā me Union katoa.

Ko te whakamahi o enei hanganga ka whakawhirinaki ki runga ki te kaiwhakawhanake ki whakaturia whāinga me nga hua e hiahia ana ia ki te whakatutuki i roto i te mutunga.

Whakaahuatanga roto Hono kaiwhakahaere

I roto i te nuinga o te wā, ki te whakakotahi tepu maha i roto i te reo SQL whakamahia e roto Hono kaiwhakahaere. Whakaahuatanga Inner Hono i SQL Ko te tino ngāwari ki te matau i te kaihātepe toharite te tika nei timata ki te matau i te pātengi raraunga. Ki te whakaaro tatou ki te whakaahuatanga o te huarahi o te mahi o tenei hanganga, whiwhi tatou i te pikitia e whai ake. Ko te operator arorau rite te katoa hanga i runga i te whakawhitinga me te taea o te kohi anake te raraunga e he i roto i ngā ia o te papa i roto i te uiui.

Ki te titiro tatou ki tenei ahua o te mahi i roto i ngā o whakairoiro tikanga, whiwhi tatou SQL Inner Hono hanganga operator, he tauira o e taea te whakaaturia e te kaupapa e whai ake nei:

Hei tauira, to tatou e rua nga tepu, whakaaturia schematically i te ahua. Ratou, i roto i te tahuri, i te maha rerekē o ngā tāurunga. I roto i ia o nga papa i mara e kua pā. Ki te tamata tatou i ki te whakamārama i te mahi o te operator i runga i te pūtake o te tuhi, te hua hoki ka waiho hei huinga o ngā pūkete i rua tepu, te wahi i nga ruma, he mara hono hāngai. Mämä noa iho, te uiui hoki aua pūkete anake (i roto i te tau tepu e rua), ko tona raraunga he i roto i te tepu tau kotahi.

Ko te wetereo o te roto Hono

Ka rite ki whakahuatia i mua, te operator roto Hono, ara, tona wetereo, rerekē ohie. Hoki te whakahaere o te whanaungatanga i waenganui i nga papa i roto i te tauira kotahi, ka rato ki mahara me te whakamahi i te hoahoa schematic e whai ake nei o te hanga o te operator, whakaritea nei i roto i te rārangi kotahi o te pūmanawa SQL-waehere, ara:

• Inner Hono [Ingoa Ripanga] i runga i [te mara matua o te tepu ki e hono] = [mara matua Puruāhei o te tepu].

Hoki te kōrero i roto i tenei tauākī ko te nuinga whakamahia tepu taviri. Ka rite ki te tikanga, i roto i te rōpū o te tepu e rokiroki mōhiohio e pā ana ki ngā kaimahi, i mua whakaahuatia Tangata Wahanga me whai i te iti rawa kia kotahi record rite. Na, tango i te piri titiro te kaiwhakahaere SQL Inner Hono, kua he tauira o e kua whakaaturia he iti mua.

whakaahuatanga TAUIRA me hononga ki te tepu tauira ororongo

E tatou te tepu Tangata, e pupuru ana ngā mōhiohio e pā ana ki nga kaimahi katoa e mahi ana i roto i te kamupene. Just mōhio e te kī ariki o te tepu, ko te mara - Pe_ID. Just runga taua mea, a ka haere i te paihere.

Ka rokiroki te Wahanga tepu tuarua mōhiohio e pā ana ki te tari i roto i nei mahi kaimahi. Ia, i roto i te tahuri, te hono mā tepu Tangata Su_Person mara. He aha te tikanga o tenei? I runga i te aronuinga raraunga taea te mea e i roto i ngā waeine i te tepu hoki ia record i roto i te tepu "kaimahi" Ka hei mōhiohio e pā ana ki te tari i roto i nei mahi ratou. Ko reira hoki tenei kōrero a ka mahi kaiwhakahaere roto Hono.

Hoki te whakamahi atu pūmanawa o whakaaro te operator SQL Inner Hono (tauira o tona whakamahinga mō te kotahi e rua ranei tepu). Ki te whakaaro tatou ki te tauira o te tepu kotahi, ka ko tino ohie katoa:

TAUIRA hono nga papa e rua, me te subquery

Tohutūmahi SQL Inner Hono, tauira o nei he whakamahi ki te tiki taea raraunga i tepu maha kia whakaritea i roto i te tikanga i runga ake, kei te mahi i te reira i runga i te pōauautanga o te parau tumu iti. Hei haafifi te mahi o nga papa e rua. Hei tauira, to tatou Haere tepu e pupuru ana ngā mōhiohio e pā ana ki te tari katoa i roto i ia o nga wehenga. I roto i tenei tepu tuhia te tau wehenga me te tau kaimahi, a me täpirihia e te ingoa o te tauira raraunga o ia tari. Titiro mua, he mea utu mea e taea e koe te whakamahi i rua tikanga ki te whakaoti i te rapanga.

Ko te ara tuatahi ko ki te hono i te tepu tari ki te rapu. Whakarite tono i roto i tenei take kia rite whai:

Ko te tikanga tuarua o whakaoti te raruraru - ko te ki te whakamahi i te subquery i roto i nei e kia tīpakohia kore nga raraunga katoa i te tepu tari, ko te tika anake. Ko tenei i roto i te rerekē ki te tikanga tuatahi, ka whakaiti i te reira i te wa o te tono.

E tano ia tapao e kore e taea e te whakaritenga taua tere tonu ake te tono mahi. I te tahi taime i reira he take i te mea e tika ana ki te whakamahi i tētahi atu raraunga tauira ki te tepu rangitahi (ki te he nui rawa te rōrahi), a ka i ngā reira ki te tauira matua.

Tauira o te whakamahi Inner Hono operator ki tauira i te maha nui o ngā ripanga

Ko te hanga tā te whakamahi o uiui matatini mo te tauira tino moni o tepu raraunga me subqueries tuatihia. Ka taea e ēnei whakaritenga makona SQL roto Hono wetereo. Ka taea te faingataa tauira o te whakamahinga o te operator, i roto i tenei take tauira kore anake o nga wahi rokiroki tokomaha, engari ano hoki ki te maha nui o subqueries tauroto. Hoki te tauira raima, ka taea e koe te tango i raraunga i te tauira o te tepu pūnaha (Inner Hono tauākī SQL). Tauira - 3 o te tepu - i roto i tenei take, ka whai i te hanganga kaua matatini.

I roto i tenei take hono (te tepu tuatahi) me toru whakaurua atu e rave rahi tikanga kōwhiringa raraunga.

A, no te whakamahi i te roto Hono kia mahara operator e te matatini atu te uiui, te roa e te whakatinana reira, na kia titiro koe mo huarahi ki te mahi i hohoro atu, me te whakaoti i te raruraru.

mutunga

I roto i te mutunga, e hiahia ana ahau ki te mea kotahi te mea: mahi ki pātengi raraunga - e kore te reira i te mea fifi roa i roto i te hōtaka, kia ki te hiahia koe, ka taea ki te whiwhi mōhiotanga ki te hanga i te pātengi raraunga katoa tangata kotahi, a ka mo te wā, riro wheako, whiwhi koe ki te mahi ki a ratou i runga i te taumata ngaio .