I-MySQL JOIN: incazelo, isibonelo sokusebenzisa umyalo nezincomo

Ukuthuthukiswa kolwazi lwezinsiza ze- Intanethi akufani neze nolwazi olujwayelekile olwenziwe ku- MS SQL SERVER. Ngokuvamile, ngezinsiza ezinjalo, ulimi lwe-MY SQL lisetshenziswa, nakuba lungasetshenziswa ukuthuthukisa imikhiqizo ye-software evamile ukusetshenziswa kwendawo. Kodwa lesi sihloko asikho ngalokhu.

Ngokuvamile lapho usebenza nolwazi olwenziwe ngolimi ngalunye, umsebenzi ukhetha ukukhethwa kwedatha ukuze kukhishwe imibiko ehlukahlukene, amagrafu njalonjalo. Njengomthetho, lapho ufeza imisebenzi enjalo, kubalulekile ukusebenzisa enye, kodwa amatafula amaningana, uwahlanganisa nombuzo owodwa, ngokuyinkimbinkimbi ekwenzeni ukuklama kwawo. Ngesikhathi esifanayo, kubalulekile ukucabangela indlela idatha ezokhipha ngayo, ukuthi amatafula "azokwenziwa kanjani" futhi yimuphi umphumela oyokwamukeleka kakhulu kumklomelo. Ukuxazulula izinkinga ezinjalo, enye yokwakhiwa okujwayelekile kolwimi lwe-MySQL yi-Joyina.

Umqondo wegama Joyina

Izilimi zokuthuthukiswa kwedatha, kungakhathaliseki ukuthi yiliphi ulimi, amagama ajwayelekile avela ezichazamazwi ezikhuluma isiNgisi asetshenziswa njengesizathu (yingakho, uma wazi isiNgisi, kuzoba lula kuwe ukuthi usebenze namatafula). Ukusebenzisa ukuxhumana kwamatafula esampula, igama elifanayo lisetshenziselwa - Joyina. Esilimini sokuhlela idatha I-SQL yami isetshenziswa. Ukuhunyushwa kwaleli gama elisemthethweni kufana nalokho ngolimi uqobo - "ukuhlanganiswa".

Ukuhumusha i-MySQL-Joyina ukwakha, futhi noma yikuphi kubo, kuyoba okufanayo. Uma sichaza injongo yokuklama, okungukuthi uhlelo lokusebenza kwalo, sithola inani elilandelayo: izakhiwo zizovumela ukuqoqa amasimu adingekayo kusuka kumatafula ahlukene noma imibuzo enemibuzo esampula esisodwa.

Izinhlobo zezakhiwo zenhlangano

Uma umlimi kumele adonse isampula ematafuleni amaningana futhi uyazi ukuthi yiziphi izinkambu eziyinhloko kuzo nokuthi yiyiphi idatha edingekayo kulo mbiko, khona-ke enye yezakhiwo eziyisisekelo zombunyano ingasetshenziswa ukufeza umphumela oyifunayo. Ukwakhiwa okuyinhloko (kokuhlanganisa amatafula) kukhona amane:

1. Ukujoyina kwangaphakathi.
2. Cross Cross.
3. Ukujoyina kwesobunxele.
4. Kwesokudla Joyina.

Kuye ngomsebenzi, imiklamo ngayinye ejwayelekile izohlinzeka ngemiphumela ehlukene, okuzokuvumela ukuba uthole imibiko kwimingcele ehlukene ngesikhathi esifushane.

Ukudala nokwethula amatafula ukuze asetshenziswe kamuva

Ngaphambi kokuba uqale, isibonelo, cabanga izindlela zokusebenzisana nezinhlangano zedatha, kuyakulungela ukulungisa amatafula amaningana, esizoqhubeka nawo ukusebenza. Lokhu kuzosiza ukubukeka kubonise yonke imigomo yabaqhubi, ngaphezu kwalokho, abaqalayo kulula ukufunda zonke izinto eziyisisekelo zamatafula okuhlela.

Itafula lokuqala lizochaza ezinye zezinto umuntu ahlangabezana nazo njalo ekuphileni kwakhe konke.

Etafuleni lesibili sichaza ezinye zezinto ezivela etafuleni lokuqala, ukuze ukwazi ukusebenza nabo esikhathini esizayo.

Ngokuvamile, amathebula amabili azokwanela ukukhombisa umsebenzi wabo ngesibonelo. Manje ungaqala ukucabangela imiklamo yethu.

Ukusebenzisa ukujoyina kwangaphakathi

Uma usebenzisa i-MySQL-Joyina Ineer yokwakha, kufanelekile ukucabangela ezinye zezici zayo. Le design ikuvumela ukhethe kusuka kokubili amathebula kuphela lawo marekhodi asembili etafuleni lokuqala nelesibili. Isebenza kanjani? Etafuleni lokuqala, sinesihluthulelo esiyinhloko - I-ID, ekhombisa inombolo ye-serial yamarekhodi etafuleni.

Uma udala itafula lesibili, ukhiye ofanayo uyasetshenziswa njengenombolo ye-serial, isibonelo singabonakala kumanani. Uma ukhetha idatha, i-Option Opharetha izothola kuphela amarekhodi ezinombolo zokulandelana ezifanayo, okusho ukuthi zikhona etafuleni lokuqala nelesibili.

Uma usebenzisa i-design, udinga ukuqonda ukuthi iyiphi idatha oyidingayo ukuze uthole. Iphutha elivame kakhulu, ikakhulukazi kumsunguli wesisekelo se-novice , ukusetshenziswa okungenangqondo nokungalungile kohlelo lokujoyina ngaphakathi. Isibonelo, i-MySQL Inner Joyina, ungacabangela iskripthi esizobuya kithi kusukela olwazini oluchazwe ngaphambilini nolugcwalisiwe ulwazi ngezinto kanye nezakhiwo zabo. Kodwa kungaba nezindlela eziningana zokusebenzisa isakhiwo. Kule ndaba I-SQL yami iyilimi oluguquguqukayo kakhulu. Ngakho, ungacabangela izibonelo zokusebenzisa i-MySQL Inner Joyina.

Hlanganisa amatafula ngaphandle kokucacisa noma yimiphi imingcele. Kulesi simo, sithola umphumela waloluhlelo:

Uma sibonisa ukusebenzisa igama elithi Utility "Ukusebenzisa", ukuthi kubalulekile ukucabangela izihluthulelo eziyinhloko zamarekhodi ematafuleni, umphumela wokukhethwa kuzoshintsha kakhulu. Kulesi simo, sithola isampula eyobuyela kuphela leyo imigqa enezinkinobho ezifanayo eziyinhloko.

Kungenzeka futhi ukusebenzisa ukuhluka kwesithathu kokusebenzisa isakhiwo, uma kunombuzo emasimini lapho amatafula kufanele ahlanganiswe kufanele kuboniswe ngegama elithi "ku". Kulesi simo, isampula izobuyisela idatha elandelayo:

Izici zokusetshenziswa kokujoyina kwesobunxele

Uma sicabangela enye indlela yokuhlanganisa amatafula usebenzisa i-MySQL-Joyina ukwakha, ungabona umehluko kwimininingwane ekhishwayo. Indlela enjalo ingakhi yokwesobunxele.

Ukusebenzisa Ukujoyina Kwesobunxele ukwakha i-MySQL inezici ezithile futhi, njenge-Inner, kudinga ukuqonda okucacile komphumela okudingeka utholakale.

Kulesi simo, wonke amarekhodi avela etafuleni lokuqala azokhethwa kuqala, futhi emva kwalokho amarekhodi etafuleni lesakhiwo sesibili azohlanganiswa nawo. Kulesi simo, uma kukhona ukungena etafuleni lokuqala, isibonelo, "isitolol", futhi etafuleni lesibili azikho izakhiwo zalo, khona-ke isitatimende sokungakwesokunxele sizobonisa ukungabi nalutho ngaphambi kwerekhodi, esitshela umqashi ukuthi akukho zibonakaliso zaloluhlobo lwezinto .

Ukusebenzisa lo mklamo kuzokuvumela ukuba ubone ukuthi yiziphi izinkambu noma, isibonelo, izimpahla esitolo azitholakali entengo, isikhathi sewaranti njalonjalo.

Isibonelo sokungakwesokunxele

Ukuhlola i-MySQL Yokungakwesobunxele ukwakha opharetha ngomkhuba, sisebenzisa amatafula ahlongozwe ngaphambilini. Yisho, udinga ukukhetha lonke uhlu lwemikhiqizo esesitolo, futhi hlola ukuthi yikuphi okungabonakaliswa ngezimpawu noma izakhiwo. Kulesi simo, isampula izobonisa yonke imikhiqizo esikrinini, futhi kulabo abangenawo impahla, amanani angenalutho azoboniswa.

Ukusebenzisa lapho Kujoyina ukwakha

Njengoba ipharamitha, ukujoyina kungabandakanya kuphela amasimu ofuna ukuxhuma amatafula, kepha ungafaka futhi lapho opharetha womshwana.

Isibonelo, cabanga ngeskriphthi okufanele sibuyele kithi kuphela lawo marekhodi angenayo isici. Kulokhu, ku-Joyina ukwakha, kufanele wengeze isitatimende sombuso bese ucacisa ukuthi yikuphi okufunayo ukubuyisela njengomphumela.

Uma isetshenziswe ku-MySQL Joyina - Lapho, udinga ukuqonda ngokucacile ukuthi kuphela lawo marekhodi lapho isimo esibekiwe sisebenza khona sizoboniswa futhi ukukhethwa kuzobukeka kanje:

Izicelo ezinjalo zivumela ukuthi ukhethe okukhethwe kukho idatha ethize ephathelene nesimo esikhethwe ngumklomelo. Kunezimo eziningana ezinjalo, kodwa ngesikhathi esifanayo kubonisa imingcele yokukhethwa kwedatha kumatafula ahlangene.

Ukusebenzisa Ukujoyina ukushintsha idatha kumatafula

Ijoyina eyakhayo, empeleni, iyonke. Akuvumela nje kuphela ukwenza amasampula ahlukahlukene, kodwa futhi ukufaka izicelo ezivela kumunye kuya kumatafula amaningana, faka izimo ezengeziwe kwisampula. Umklamo ungasetshenziselwa nezinye imisebenzi yedatha. Ngakho, ukubhalisa kungasetshenziswa ukushintsha idatha etafuleni. Kunalokho, ukucacisa izimo etafuleni noma kulawo mathuba, uma ufuna ukuvuselela idatha ematafuleni amaningana ngezimo ezifanayo.

Isibonelo, cabangela le nkinga. Amatafula amathathu anikezwa lapho kukhona idatha ethile. Udinga ukwenza ushintsho kudatha kokubili amatafula usebenzisa umbuzo owodwa. Ukuze uxazulule lolu hlobo lwemisebenzi, ungasebenzisa i-Joyina ukwakha kumyalo Wokuvuselela. Uhlobo lojoyina luyakhela ngokwalo, kuncike ekulandeleni idatha, kumphumela umlimi ofuna ukuwuthola.

Ake sicabangele isibonelo esilula. Udinga ukubuyekeza umbuzo ofanayo nezimo ezifanayo. Imibuzo enjalo yenzelwe ukwandisa umsebenzi nge database. Kungani ubhala imibuzo ehlukene ematafuleni ngayinye, uma ungenza konke ukuphathwa kwedatha ngombuzo owodwa? Ukuvuselelwa kwe-MySQL Joyina isibonelo kulokhu kuzoba:

Ukwakha imibuzo eyinkimbinkimbi

Ngokuvamile uma usebenza ne-database kuyadingeka ukwakha imibuzo hhayi kuphela ukuhlanganiswa kwamatafula amaningana, kodwa futhi nokusetshenziswa kwe-subqueries. Imisebenzi enjalo kunzima ukuyiqonda kumuntu oqala uhlelo lwesisekelo semininingwane. Ubunzima ukuthi kufanele ucabange ngesinyathelo ngasinye, unqume ukuthi iyiphi idatha etafuleni noma umbuzo oyidingayo ukuze uthole nokuthi uzodinga kanjani ukusebenza nabo esikhathini esizayo.

Ukuze uthole ukuqonda okuqondile, ungacabangela (ku-MySQL Joyina) izibonelo zezinkinga eziyinkimbinkimbi. Uma ungumqali futhi uqale nje ukusebenza nge-yolwazi, ukuqeqesha okunjalo kuzozuzisa kuphela. Inketho ekahle kuyoba yizibonelo zami ze-MySQL ezishiywe ngakwesobunxele.

Lesi sicelo sizosibuyisela amarekhodi angu-58 ezinkontileka zokuthengisa lapho ibhalansi yemali yosuku olukhethiwe ligcwalisiwe noma likhona. Kulesi simo, lokhu kungukuthi okwamanje. Futhi kwisampula isimo sanezelwa ukuthi igama lesivumelwano kufanele libe nezinhlamvu "123". Imininingwane ekhonjisiwe esikrinini (idatha) izobe ihlehlise - ihlelwe ngenombolo yenkampani.

Isibonelo esilandelayo sizobonisa imininingwane yazo zonke izinkokhelo lapho inombolo yenkontileka ichaziwe khona.

Ukusebenzisa i-Subqueries

Njengoba kushiwo ngaphambili, lapho usebenza nolwazi olusetshenziswa, ungahlanganisi amatafula kuphela, kodwa futhi uhlanganise itafula elinombuzo. Le design isetshenziswa ngokuyinhloko ukusheshisa umbuzo bese uyithuthukisa.

Isibonelo, uma udinga ukhethe izinkambu ezimbili kuphela ezivela etafuleni enezinkampani ezingamakhulu amaningana futhi, zithi, amarekhodi ayizinkulungwane, khona-ke kufanele usebenzise umbuzo ozobuyela kuphela emasimini adingekayo, bese uhlangana nawo ngesampula yedatha esemqoka. Njengesibonelo se-MySQL Joyina Khetha, ungacabangela umbuzo walolu hlobo:

Lezi akuzona zonke izindlela zokusebenzisa izakhiwo ezijwayelekile ze-MySQL, kodwa kuphela ezijwayelekile. Indlela yokusebenzisa i-Joyina ukwakha futhi yiziphi izinhlobo zayo, umqambi ukhetha, kodwa kubalulekile ukukhumbula nokucabangela ukuthi yini okufanele itholakale lapho umbuzo ukwenziwa.