Iikhomputha, Programming
Ukujoyina kwesobunxele (SQL) - umzekelo, inkcazo ecacileyo, iimpazamo zokusetyenziswa
Kuyo nayiphi na database yokwenene ezinobume, zonke iinkcukacha zikhutshwe kwitafile esahlukileyo. Uninzi izithebe ezibekwe nkqubo yonxibelelwano kunye nabanye. Noko ke, ngoncedo -Imibuzo SQL kusenokwenzeka ukuba kukho unxibelelwano phakathi kwe data, hayi okulungiswe wesekethe. Oku kwenziwa ngokwenza ukujoyina uqhagamshelwano, nto leyo evumela ukuba ukwakha ubuhlobo phakathi naliphi na inani iitafile, nkqu ukudibanisa iinkcukacha ebonakala abanemfundo.
Eli nqaku liza kuthetha ngqo malunga iqhosha langaphandle. Ngaphambi kokuqhubeka incazelo yale luhlobo lodibaniso, yongeza ezinye iitafile zesiseko sedata.
Ukulungiselela le tafile eziyimfuneko
Umzekelo, database yethu, kukho ulwazi malunga ngabantu kunye kwilifa sawo. Isishwankathelo esekelwe iitafile ezintathu: Peoples (abantu), Realty (estate wokwenene), Realty_peoples (ubudlelwane itafile, abantu koko propati bobabo). Iqalise le datha ilandelayo egcinwe kwiitheyibhile zabantu:
Peoples | ||||
id | L_name | F_name | Middle_name | Usuku lokuzalwa |
1 | ivanova | uDaria | B. | 07/16/2000 |
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 |
3 | Evgenin | Alexander | Federovich | 04/30/1964 |
4 | Annina | uthando | P. | 31.12.1989 |
5 | Gerasimovsky | ithemba | P. | 14.03.1992 |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 |
7 | Sukhanovskaya | Special | A. | 09/25/1976 |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 |
ezithengiswayo:
Realty | |
id | idilesi |
1 | Arkhangelsk, ul. Voronin, d. 7, kv.6 |
2 | Arkhangelsk, ul. Severodvinskaya, d. 84, q. 9 BR. 5 |
3 | kummandla Arkhangelsk, Severodvinsk, st. Lenin, d. 134, q. 85 |
4 | kummandla Arkhangelsk, Novodvinsk, ul. Proletarshaya, d. 16, q. 137 |
5 | Arkhangelsk, pl. Terekhina, d. 89, q. 13 |
Relations abantu - impahla:
Realty_peoples | ||
id_peoples | id_realty | uhlobo |
7 | 3 | Total kumaphulo |
8 | 3 | Total kumaphulo |
3 | 5 | impahla |
7 | 1 | impahla |
5 | 4 | iindawo Common |
6 | 4 | iindawo Common |
Iqhosha (SQL) - Description
ezimbaxa Ekhohlo ine lwesivakalisi ilandelayo:
Table_A LEFT NAWE table_B [{ON umenziwa} | {UKUSEBENZISA spisok_ kunye tolbtsov}] |
Kwaye ngokwalo ngolu hlobo lulandelayo:
Kwaye eli binzana liguqulelwa ngokuthi "Khetha Yonke, ngaphandle kokukhetha, umgca ka-Table A kunye Uluhlu B ukubonisa kuphela imiqolo ukufanisa vavisa. Ukuba itafile eyafunyanwa itafile lomtya ngababini A, uze ufakele iintsika ngenxa Null - imilinganiselo ".
Amaninzi, xa uqhagamshelwano ekhohlo kubonisiweyo, esebenzisa isetyenziswa kuphela xa amagama umhlathi, nto leyo ecwangcisiweyo ukwenza udibaniso ziyafana.
Ekhohlo join - Imizekelo yokusetyenziswa
Xa uxhulumaniso yasekhohlo sinako ukubona, bonke abantu ukusuka kuluhlu ukuba kukho impahla Peoples. Ukwenza oku ekhohlo ujoyine umzekelo umbuzo SQL:
Abantu KHETHA. *, Realty_peoples.id_realty, Realty_peoples.type FROM Peoples LEFT Joyina Realty_peoples ON Peoples.id = Realty_peoples.id_peoples; |
Kukho ezi ziphumo zilandelayo:
Query1 | ||||||
id | L_name | F_name | Middle_name | Usuku lokuzalwa | id_realty | uhlobo |
1 | ivanova | uDaria | B. | 07/16/2000 | ||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | ||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | impahla |
4 | Annina | uthando | P. | 31.12.1989 | ||
5 | Gerasimovsky | ithemba | P. | 14.03.1992 | 4 | iindawo Common |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | iindawo Common |
7 | Sukhanovskaya | Special | A. | 09/25/1976 | 1 | impahla |
7 | Sukhanovskaya | Special | A. | 09/25/1976 | 3 | Total kumaphulo |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total kumaphulo |
Njengoko sibona, ivanova Darya Pugin Vladislav kunye Anninoy Lyubovi No ebhalisiweyo amalungelo real estate.
Kwaye yintoni thina samkela, usebenzisa i ngaphakathi ukujoyina Inner ujoyine? Njengoko usazi, ayibabandakanyi imiqolo non-ethelekisa, ngoko abathathu sample yethu zokugqibela nje malithotywe:
Query1 | ||||||
id | L_name | F_name | Middle_name | Usuku lokuzalwa | id_realty | uhlobo |
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | impahla |
5 | Gerasimovsky | ithemba | P. | 14.03.1992 | 4 | iindawo Common |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | iindawo Common |
7 | Sukhanovskaya | Special | A. | 09/25/1976 | 1 | impahla |
7 | Sukhanovskaya | Special | A. | 09/25/1976 | 3 | Total kumaphulo |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total kumaphulo |
Kubonakala ngathi le nguqulelo yesibini idibana imiqathango ingxaki yethu. Noko ke, xa siqala ukuba athimbe phezu komnye, yaye omnye netafile, abantu abathathu ukusuka result sele nobutyobo ingasekho. Ngoko ke, xa kusenziwa, xa yokudibanisa iitafile ezininzi kakhulu amaxesha amaninzi asetyenziswa Ekhohlo kunye Ekunene uqhagamshelwano ngaphezu Inner ukujoyina.
Uya kuqhubeka ukukhangela ekhohlo ukujoyina imizekelo we SQL. Qhoboshela itafile kunye needilesi ze nezindlu zethu;
Abantu KHETHA. *, Realty_peoples.id_realty, Realty_peoples.type, Realty.address FROM Peoples LEFT NAWE Realty_peoples ON Peoples.id = Realty_peoples.id_peoples LEFT NAWE Realty ON Realty.id = Realty_peoples.id_realty |
Ngoku sifumana nje kuphela uhlobo umthetho; kodwa ke idilesi izindlu ezithengiswayo:
Query1 | |||||||
id | L_name | F_name | Middle_name | Usuku lokuzalwa | id_realty | uhlobo | idilesi |
1 | ivanova | uDaria | B. | 07/16/2000 | |||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | |||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | impahla | Arkhangelsk, pl. Terekhina, d. 89, q. 13 |
4 | Annina | uthando | P. | 31.12.1989 | |||
5 | Gerasimovsky | ithemba | P. | 14.03.1992 | 4 | iindawo Common | kummandla Arkhangelsk, Novodvinsk, ul. Proletarshaya, d. 16, q. 137 |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | iindawo Common | kummandla Arkhangelsk, Novodvinsk, ul. Proletarshaya, d. 16, q. 137 |
7 | Sukhanovskaya | Special | A. | 09/25/1976 | 3 | Total kumaphulo | kummandla Arkhangelsk, Severodvinsk, st. Lenin, d. 134, q. 85 |
7 | Sukhanovskaya | Special | A. | 09/25/1976 | 1 | impahla | Arkhangelsk, ul. Voronin, d. 7, kv.6 |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total kumaphulo |
kummandla Arkhangelsk, Severodvinsk, st. Lenin, d. 134, q. 85 |
Ekhohlo join - ukusetyenziswa obabuthandwa Imposiso: itheyibhile inkqubo olungasebenziyo
iimpazamo ezingundoqo alenza iqhosha langaphandle iitafile, ezimbini:
- Ngokuchanekileyo Ukhethe indlela iitafile apho data kulahlekileyo.
- Apho iimpazamo xa usebenzisa umbuzo kunye ukujoyina iitafile.
Cinga impazamo yokuqala. Phambi kokuba isigqibo nayiphi na ingxaki kufuneka kuqondwe ngokucacileyo oko sifuna ukuba ekugqibeleni. Kulo mzekelo ungentla, siye wathabatha elowo single yabantu, kodwa walahlekelwa ngokupheleleyo ulwazi malunga into phantsi inombolo 2, umnikazi alufumanekanga.
Ukuba washukumisa amacwecwe umbuzo kwezinye iindawo, yaye qala «... Ukusuka Realty ekhohlo bajoyine Peoples ...» nayiphi na ipropati mnye, singe singamnikelanga ayaba, uya kukuxelela ngabantu.
Kodwa musani ukunkwantya zo xhulumaniso ekhohlo, ukuswitsha lwangaphandle ngokupheleleyo, nto leyo ifakiwe iziphumo kunye matching, hayi imigca ezifanayo.
Ngapha koko, umthamo isampuli yi ezinkulu kakhulu, kwaye idata extra enyanisweni ngamampunge. Eyona nto ibalulekileyo - ukufumana phandle ufuna ukufumana isiphumo: bonke abantu abane uluhlu ipropati yabo ekhoyo okanye uludwe kwempahla yonke kunye nabanini bazo (ukuba ikhona).
Ekhohlo join - ukusetyenziswa obabuthandwa Imposiso: Isicelo ichanile xa ebeka imiqathango kwi Uphi
Impazamo yesibini nayo enxulumene kulahleka data, kwaye ayisoloko asenokungacaci kwangoku.
Makhe siye emva yombuzo xa eshiya nge zendibaniso lafumana idatha yabo bonke abantu kunye nempahla yabo ezikhoyo. Khumbula ezi zilandelayo iqhosha umzekelo SQL:
FROM Peoples LEFT Joyina Realty_peoples ON Peoples.id = Realty_peoples.id_peoples; |
Masithi ufuna ukucacisa isicelo kwaye akazi imveliso ye data, apho uhlobo yomthetho - "Ipropati". Ukuba isibuzi nje, ngokusebenzisa ngasekhohlo ukujoyina SQL, umzekelo imeko zilandelayo:
...
Apho uhlobo <> "Ipropati" |
siya kuphulukana data abantu abangenalo impahla, kuba Null ixabiso null akukho kuthelekiswa ngolu hlobo lulandelayo:
Query1 | ||||||
id | L_name | F_name | Middle_name | Usuku lokuzalwa | id_realty | uhlobo |
5 | Gerasimovsky | ithemba | P. | 14.03.1992 | 4 | iindawo Common |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | iindawo Common |
7 | Sukhanovskaya | Special | A. | 09/25/1976 | 3 | Total kumaphulo |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total kumaphulo |
Ukuthintela iimpazamo ekudalekeni ngenxa yesi sizathu, kuya kuba kuhle ukuba ukucwangcisa imeko yokhetho nangoko phezu uqhagamshelwano. Sicebisa ukuba siqwalasele ezi zilandelayo iqhosha umzekelo we SQL.
Abantu KHETHA. *, Realty_peoples.id_realty, Realty_peoples.type FROM Peoples LEFT NAWE Realty_peoples ON (Peoples.id = Realty_peoples.id_peoples KUNYE uhlobo <> "Ipropati") |
Umphumo iya kuba ngolu hlobo lulandelayo:
Query1 | ||||||
id | L_name | F_name | Middle_name | Usuku lokuzalwa | id_realty | uhlobo |
1 | ivanova | uDaria | B. | 07/16/2000 | ||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | ||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | ||
4 | Annina | uthando | P. | 31.12.1989 | ||
5 | Gerasimovsky | ithemba | P. | 14.03.1992 | 4 | iindawo Common |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | iindawo Common |
7 | Sukhanovskaya | Special | A. | 09/25/1976 | 3 | Total kumaphulo |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total kumaphulo |
Ngenxa yoko, ngokulandela elula ukuya ekhohlo bazibandakanye umzekelo SQL, safumana uluhlu bonke abantu, ihamba phambili, enye yezi mihlaba kwi equity / ubunini ngokuhlangeneyo.
Njengoko isiphelo Ndingathanda kwakhona ukuba isampuli naluphi na ulwazi kuvimba kufuneka kuthathwe ngenkathalo ukugxininisa. ngomahluko abaninzi wavula phambi kwethu iqhosha umzekelo SQL ezilula, ingcaciso apho omnye - ngaphambi kokuba uqalise ukubhala nokuba umbuzo esisiseko, kufuneka ngenyameko ukuqonda oko sifuna ukuba ekugqibeleni. ithamsanqa!
Similar articles
Trending Now