Operator SQL Bangaphakathi Joyina: imizekelo kwezivakalisi kunye neempawu

Ukuphuhlisa nayiphi database ibandakanya kungekuphela nje indalo kunye nokugcwaliswa neetafile ngolwazi ezahlukeneyo, kodwa umsebenzi ngakumbi le data. Ukuze wenze ngokuchanekileyo neendidi zemisebenzi ukukhethwa data evela iitafile ingxelo, sebenzisa i Khetha design eqhelekileyo.

Ukulanda data ukusuka kwizintlu

Xa siqwalasela ingxaki ngokhetho lwe data okanye ukwakhiwa kwengxelo, unako ukuqinisekisa inqanaba mbaxa msebenzi. Ngokuqhelekileyo, xa kusingethwe kakhulu (ngokwendlela ivolyum) oovimba, leyo kwabunjwa, umzekelo, ezivenkileni intanethi okanye iinkampani ezinkulu, ukhetho data ayiphelelanga itafile enye. Njengoko umthetho, isampuli ibe ukusuka iqela elikhulu iitafile hayi kuphela anxulunyaniswa kodwa ekwiseli imibuzo / sub-imibuzo, nto leyo eyenza lo wenkqubo, ngokuxhomekeke umsebenzi walo. Kuba isampuli etafileni enye Ungasebenzisa isakhiwo elula:

apho Person - igama itafile ofuna sokwenza retrieval data.

Ukuba, ke, kuya kufuneka ukhethe data ukusuka kwizintlu ezininzi, omnye zoyilo esemgangathweni ingasetyenziswa ukujoyina iitheyibhile ezininzi.

kokuxhuma Indlela iitafile ezongezelelweyo

Ukuba sicinga ukusebenzisa izakhiwo ezinjalo kwinqanaba lokuqala, siya ukwahlula ezi ndlela zilandelayo ukudibanisa inani elifunekayo yeetafile isampula, ezizezi:

1. Operator yangaphakathi Joyina.
2. Ekhohlo Dibanisa okanye, oko indlela yokurekhoda yesibini, Left engaphandle Joyina.
3. Cross Joyina.
4. Full Joyina.

Ukusebenzisa abaqhubi ukuba bazibandakanye amacwecwe ku umkhuba, unako ukufunda ngokuhlolisisa ukusetyenziswa kwingxelo SQL i - Inner Joyina. Umzekelo ukusetyenziswa kwayo kuya kuba ngolu hlobo lulandelayo:

SQL kolwimi Dibanisa Inner Dibanisa Umsebenzisi kusetyenziselwa kuphela yokudibanisa iitafile ezimbini okanye ngaphezulu, kodwa kwakhona ukudibanisa elinye sub-imibuzo, nto leyo lula kakhulu umsebenzi abalawuli database, yaye, njengoko umthetho, sakubanako ukunyusa isantya kakhulu ukuphunyezwa, izakhiwo ezithile umbuzo enzima.

Ukudibanisa data kwi line thebhile umgca

Ukuba sicinga udibaniso lwenani elikhulu sub-imibuzo, kunye neenkcukacha ndawonye zibe umgca itafile enye ngu line, ungasebenzisa kwakhona abaqhubi Union kunye Union All.

Ukusetyenziswa kwezi ziseko kuza kuxhomekeka umphuhlisi ukuseta iinjongo kunye neziphumo ukuba ufuna ukuphumelela ekupheleni.

Inkcazelo Inner Joyina umqhubi

Kwiimeko ezininzi, ukuba ukudibanisa amacwecwe ezininzi kulwimi SQL esetyenziswa yi Inner Dibanisa umqhubi. Inkcazelo Inner Dibanisa kwi SQL kulula kakhulu ukuba baqonde wenkqubo avareji lowo ekuqaleni nje ukuqonda iinkcukacha. Ukuba sicinga inkcazelo indlela yomsebenzi kwesi sakhiwo, sifumana umfanekiso olandelayo. Umsebenzisi logic xa iyonke yakhelwe phezu kwibhulorho kunye kungenzeka kweesampula kuphela data into nganye kwezi theyibhile ezibandakanywe umbuzo.

Xa sijonga lo msebenzi ngokubhekiselele ukutyhilwa graphic, siye sifumane SQL yangaphakathi Dibanisa isakhiwo umqhubi, umzekelo apho kuboniswa le nkqubo ilandelayo:

Ngokomzekelo, siye sibe iitafile ezimbini, eboniswe ngokwalo kulo mzobo. Nabo ke, ukuba kukho inani eyahlukileyo yezicelo. Kwimeko nganye kwezi theyibhile kufuneka amasimi ukuba bayazalana. Ukuba sizama ukuchaza umsebenzi umsebenzisi ngokusekelwe kumzobo, iziphumo wabuya iya kuba iseti iingxelo ezivela iitafile ezimbini, apho magumbi amasimi ezihambelanayo idibana. Ngamafutshane, le umbuzo ibuyisela kuphela ezo marekhodi (kule nombolo itheyibhile ezimbini), ogama data kwetafile inani elinye.

Le syntax Inner Dibanisa

Njengoko kuchaziwe ngaphambili, umqhubi Inner Dibanisa, oko kukuthi, ezinesintaksi, elula ngokungaqhelekanga. Kuba umbutho lobudlelane phakathi iitafile ngaphakathi isampulu esinye baya kwanela ukuba ukukhumbula kwaye usebenzise mzobo ulandelayo oganogram ukwakhiwa umqhubi, nto leyo kumiselwe ngomgca owodwa software SQL-ikhowudi, ezizezi:

• Inner Dibanisa [Name Table] kwi [intsimi engundoqo etafileni apho ukudibanisa] = [intsimi pluggable ezingundoqo kwetafile].

Kunxibelelwano kule ngxelo ikakhulu babesebenzisa iitafile izitshixo. Ngokuqhelekileyo, xa iqela iitafile agcina inkcazelo malunga abasebenzi, ngaphambili wachaza Person Subdivision yaye irekhodi ubuncinane enye efanayo. Ngoko ke, thatha kufutshane khangela umqhubi SQL yangaphakathi Dibanisa, umzekelo owawubona kancinane ngaphambili.

inkcazo UMZEKELO kunye uqhagamshelwano etafileni isampula audio

Thina itafile Person, leyo igcina ulwazi malunga bonke abasebenzi abasebenza kule nkampani. qaphela nje ukuba inkosi isitshixo etafileni yi endle - Pe_ID. Nje kuyo kwaye iza kuhamba inyanda.

Le theyibhile Subdivision yesibini iya ukugcina ulwazi malunga ofisi apho abasebenzi abasebenza. Yena, ke, elidityaniswe nge Su_Person endle Person itafile. Kuthetha ntoni oku? Ngokusekelwe onokuyi kwedatha kunokuthiwa kule iiyunithi itafile irekhodi nganye kwitheyibhile "Abaqeshwa" iya kuba ulwazi malunga isebe apho zisebenza. Kungenxa yale kunxibelelwano kwaye iza kusebenza nomsebenzisi Inner Joyina.

Ngokuba ukusetyenziswa ibonakala ngakumbi cinga umqhubi SQL yangaphakathi Dibanisa (imizekelo ukusetyenziswa kwayo amacwecwe elinye okanye amabini). Xa siqwalasela umzekelo etafileni enye, ngoko zonke ilula:

UMZEKELO qhagamshela amacwecwe amabini kunye subquery

Operator SQL yangaphakathi Dibanisa, imizekelo apho kukhona ukusebenzisa kuyo ze kuvunjululwe idata esuka iitafile ezininzi zingenzelwa amalungiselelo ngendlela ngasentla, isebenza kwi ingxaki encinane eyinqobo. Ukuze nzima umsebenzi yezintlu ezimbini. Ngokomzekelo, siye sibe itheyibhile Sukani eligcina inkcazelo malunga onke amasebe nganye amaqela. Kule theyibhile ebhalwe inombolo division kunye nenombolo umqeshwa, kwaye kufuneka zincediswa igama data isampuli isebe ngalinye. Xa phambili, kubalulekile esithi ukuba unako ukusebenzisa iindlela ezimbini ukusombulula le ngxaki.

Indlela yokuqala ukudibanisa etafileni kwisebe ukuze isampula. Ququzelela isicelo kule kunokwenzeka ngolu hlobo lulandelayo:

Indlela yesibini yokusombulula le ngxaki - kukusebenzisa i subquery apho hayi zonke data ziya kukhethwa etafileni kwisebe, kodwa kuphela eyimfuneko. Oku kuya kwa nshita, indlela yokuqala, oku kuya kunciphisa ixesha yesicelo.

Kubalulekile ukuphawula ukuba lungiselelo ayikwazi akusoloko ukukhawulezisa isicelo umsebenzi. Ngamanye amaxesha kukho iimeko xa kuyimfuneko ukusebenzisa i data isampula ezongezelelweyo kwi table okwexeshana (ukuba umthamo mkhulu kakhulu), kwaye ke ngoko lahlanganisa kwisampula ephambili.

Umzekelo ukusetyenziswa Inner Dibanisa operator ukuya iisampuli ukusuka kwinani elikhulu iitafile

Ukwakhiwa kubandakanya ukusetyenziswa imibuzo elukhuni izixa isampula ebonakalayo iitafile data kunye subqueries ezihambelanayo. Ezi mfuno kunokwanelisa SQL yangaphakathi Dibanisa syntax. Imizekelo yokusetyenziswa umqhubi, kule meko ukuba lukhuni iisampuli nje yeendawo ezininzi ukugcinwa, kodwa kukho inani elikhulu subqueries ekwiseli. Kuba Umzekelo eziphathekayo, ungathatha data evela kwisampula iitafile system (Inner Dibanisa ingxelo SQL). Umzekelo - 3 kwetafile - kule meko iya kuba nesakhiwo kunokuba ezintsonkothileyo.

Kulo mzekelo enxulumene (itheyibhile yokuqala) yaye yaqaliswa eminye imiqathango ezintathu eziliqela ukhetho data.

Xa usebenzisa i Inner Dibanisa umqhubi kufuneka sikhumbule ukuba ngxuba umbuzo, ixesha elide iza kuphunyezwa, ngoko kufuneka sikhangele iindlela ukuze enze ngaphezulu ngokukhawuleza nokusombulula le ngxaki.

isiphelo

Ekugqibeleni, ndingathanda ukuthi enye into: ukusebenza oovimba - akukho nto inzima kakhulu lwenkqubo, ngoko ukuba ufuna wonke umntu uya kuba nako ukufumana ulwazi ukwakha uvimba weenkcukacha, yaye ekuhambeni kwexesha, bafumane amava, ufumana ukusebenza nabo kwinqanaba professional .