Makombiyuta, Zvirongwa
Left kubatana (SQL) - muenzaniso, kuti rondedzero, kushandiswa kukanganisa
Chero chaiye ndeyeukama Database, mashoko ose ari kuparadzirwa pasi yoga patafura. Vakawanda matafura vari kusimuka kukurukura zano nomumwe nomumwe. Zvisinei, achibatsirwa SQL queries ingangodaro kuisa link pakati data, kwete midzi mudunhu. Izvi zvinoitwa kuita kujoina kubatana, izvo zvinoita kuti kuvaka ukama pakati chero nhamba matafura, uye kunyange batanidza sokuti zvakasiyana ruzivo.
Nyaya ino kutaura zvakananga pamusoro kuruboshwe yokunze abatane. Asati achipfuura kuti tsanangudzo iyi mhando maererano, wedzera zvimwe Database pamatafura.
Kubika zvakafanira matafura
Somuenzaniso, muna Database yedu, pane ruzivo pamusoro vanhu uye chaivo wavo wakare. Pfupikiso inobva pamatafura matatu: Peoples (vanhu), Realty (Real Estate), Realty_peoples (tafura ukama, vanhu vanobva chii pfuma ndohwavo). Zvokufungidzira zvinotevera mashoko mumabhatiri matafura vanhu:
Peoples | ||||
ID | L_name | F_name | Middle_name | bhavhudhe |
1 | Ivanova | Daria | B. | 07/16/2000 |
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 |
3 | Evgenin | Alexander | Federovich | 04/30/1964 |
4 | Annina | rudo | P. | 31.12.1989 |
5 | Gerasimovsky | tarisiro | P. | 14.03.1992 |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 |
Real bwedu:
Realty | |
ID | adhiresi |
1 | Arkhangelsk, Ul. Voronin, d. 7, kv.6 |
2 | Arkhangelsk, Ul. Severodvinskaya, d. 84, q. 9 BR. 5 |
3 | Arkhangelsk nharaunda, Severodvinsk, st. Lenin, d. 134, q. 85 |
4 | Arkhangelsk nharaunda, Novodvinsk, Ul. Proletarshaya, d. 16, q. 137 |
5 | Arkhangelsk, Pl. Terekhina, d. 89, q. 13 |
Relations vanhu - pfuma:
Realty_peoples | ||
id_peoples | id_realty | mhando |
7 | 3 | Total joini uridzi |
8 | 3 | Total joini uridzi |
3 | 5 | pfuma |
7 | 1 | pfuma |
5 | 4 | Common zvikamu |
6 | 4 | Common zvikamu |
Kusiyiwa kubatana (SQL) - Description
Kusiyiwa remubatanidzwa ane zvinotevera nemarongerwo:
Table_A kuruboshwe JOIN table_B [{ON Predicate} | {KUSHANDISA spisok_ pamwe tolbtsov}] |
Uye schematically sezvinotevera:
Uye mashoko aya rinoshandurwa se "Sarudza All, pasina chaakasiya mudzinza Table A uye Table B kuratidza chete enzanirana mitsara iri Predicate. Kana tafura yakawanikwa tafura tambo kuti vaviri vaviri A, zvino kuzadza zvichiguma mbiru null - tsika ".
Kakawanda, kana akasiya kubatana zvinoratidzwa ON, achishandisa chete kushandiswa kana mbiru mazita, izvo akaronga kuti kubatana zvakafanana.
Left vanobatana - mienzaniso kushandiswa
With kubatana zviri kuruboshwe tinogona kuona, vose vanhu kubvira pasi kana paine Peoples pfuma. Kuti vaite izvi kuruboshwe kubatana SQL mubvunzo muenzaniso:
Sarudza Peoples. *, Realty_peoples.id_realty, Realty_peoples.type FROM Peoples kuruboshwe JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples; |
With zvinotevera results:
Query1 | ||||||
ID | L_name | F_name | Middle_name | bhavhudhe | id_realty | mhando |
1 | Ivanova | Daria | B. | 07/16/2000 | ||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | ||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | pfuma |
4 | Annina | rudo | P. | 31.12.1989 | ||
5 | Gerasimovsky | tarisiro | P. | 14.03.1992 | 4 | Common zvikamu |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common zvikamu |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 | 1 | pfuma |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 | 3 | Total joini uridzi |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total joini uridzi |
Sezvatinoona, Ivanova DARYA Pugin Vladislav uye Anninoy Lyubovi No vakanyorwa Real Estate kodzero.
Uye chii dai takagamuchira, uchishandisa yomukati kubatana Inner kujoina? Sezvaunoziva, izvozvo inosiya asiri enzanirana mumitsara, saka matatu kubva kwokupedzisira mharidzo yedu kuti kungoti uchakandirwa:
Query1 | ||||||
ID | L_name | F_name | Middle_name | bhavhudhe | id_realty | mhando |
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | pfuma |
5 | Gerasimovsky | tarisiro | P. | 14.03.1992 | 4 | Common zvikamu |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common zvikamu |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 | 1 | pfuma |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 | 3 | Total joini uridzi |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total joini uridzi |
Zvaizoratidza sokuti wechipiri shanduro zvakare rinoita zvinhu zviri dambudziko redu. Zvisinei, kana tiri kutanga kuisa pamusoro, umwe tafura, vanhu vatatu kubva mugumisiro zvachose dzaenda. Saka, muna tsika, kana kuti tichibatanidza akawanda matafura zvikuru aiwanzoshandisa Left uye Right kubatana pane Inner vanobatana.
Acharamba kutarisa kuruboshwe kubatana SQL mienzaniso. Kuisa tafura kero dzimba dzedu:
Sarudza Peoples. *, Realty_peoples.id_realty, Realty_peoples.type, Realty.address FROM Peoples Kuruboshwe JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples Kuruboshwe JOIN Realty ON Realty.id = Realty_peoples.id_realty |
Zvino isu kuwana kwete chete mutemo mhando, asiwo kero Zvivako:
Query1 | |||||||
ID | L_name | F_name | Middle_name | bhavhudhe | id_realty | mhando | adhiresi |
1 | Ivanova | Daria | B. | 07/16/2000 | |||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | |||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | pfuma | Arkhangelsk, Pl. Terekhina, d. 89, q. 13 |
4 | Annina | rudo | P. | 31.12.1989 | |||
5 | Gerasimovsky | tarisiro | P. | 14.03.1992 | 4 | Common zvikamu | Arkhangelsk nharaunda, Novodvinsk, Ul. Proletarshaya, d. 16, q. 137 |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common zvikamu | Arkhangelsk nharaunda, Novodvinsk, Ul. Proletarshaya, d. 16, q. 137 |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 | 3 | Total joini uridzi | Arkhangelsk nharaunda, Severodvinsk, st. Lenin, d. 134, q. 85 |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 | 1 | pfuma | Arkhangelsk, Ul. Voronin, d. 7, kv.6 |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total joini uridzi |
Arkhangelsk nharaunda, Severodvinsk, st. Lenin, d. 134, q. 85 |
Left kubatana - hwokufananidzira kushandiswa kukanganisa: haushandi nzira tafura
zvikanganiso Basic zvakaitwa rokunze kuruboshwe kubatana matafura, maviri:
- Nemazvo yasarudzwa murayiro matafura icho date chakarasika.
- Where zvikanganiso paaishandisa mubvunzo pamwe vanobatana pamatafura.
Funga chikanganiso chokutanga. Asati chisarudzo chipi dambudziko anofanira ainyatsonzwisisa kuti zvatinoda kuti pakupedzisira. In muenzaniso uyu pamusoro, isu akatora mumwe chete wevanhu, asi akarasikirwa zvachose ruzivo pamusoro chinhu pasi nhamba 2, ane muridzi haana kuwanikwa.
Kana takatama mabwendefa imwe mubvunzo mune dzimwe nzvimbo, uye ungadai kutanga «... From Realty akasiya kubatana Peoples ...» munhu pfuma, tingadai tisina akarasika, hamungavatongi kuudza vanhu.
Asi musavhundutswa nezvezvakanga kuruboshwe, chinja kune zvizere zvokunze, iyo rwuripowo zvabuda uye enzanirana, uye kwete enzanirana mitsetse.
Pashure pezvose, bhuku mharidzo kazhinji yakakura chaizvo, uye dzimwe mashoko chaizvoizvo maturo. Chinhu chikuru - kufunga zvaunoda kuwana mugumisiro: vanhu vose vane mazita avo iripo pfuma kana yose pfuma mazita avo pavanatenzi (kana).
Left kubatana - hwokufananidzira kushandiswa kukanganisa: Chikumbiro yakarurama kana kuisa zvinhu ari kupi
kukanganisa wechipiri kunewo chokuita kurasikirwa data, uye havasi nguva dzose pakarepo pachena.
Ngatidzokerei kune mubvunzo patakabva Via kwokubatana vakagamuchira mashoko dzose vanhu vavo aripo pfuma. Rangarirai zvinotevera pamwe rworuboshwe vanobatana SQL muenzaniso:
FROM Peoples kuruboshwe JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples; |
Ngatitii tinoda kujekesa chikumbiro uye haaiti goho racho data, apo mhando mutemo - "Property". Kana isu nyore append, kushandisa akasiya kubatana SQL, muenzaniso zvinotevera ezvinhu:
...
Apo mhando <> "Property" |
isu vacharasikirwa mashoko vanhu vasina pfuma, nokuti null ukoshi null haina tichienzanisa sezvinotevera:
Query1 | ||||||
ID | L_name | F_name | Middle_name | bhavhudhe | id_realty | mhando |
5 | Gerasimovsky | tarisiro | P. | 14.03.1992 | 4 | Common zvikamu |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common zvikamu |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 | 3 | Total joini uridzi |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total joini uridzi |
Kuti adzivise zvikanganiso kubva zvichiitika nokuda kwechikonzero ichi, zvakanaka kugadza kusarudzwa ezvinhu pakarepo pamusoro chokuita. We anoratidza kuti funga zvinotevera pamwe kuruboshwe abatane SQL muenzaniso.
Sarudza Peoples. *, Realty_peoples.id_realty, Realty_peoples.type FROM Peoples Kuruboshwe JOIN Realty_peoples ON (Peoples.id = Realty_peoples.id_peoples AND mhando <> "Property") |
The Zvichaguma sezvinotevera:
Query1 | ||||||
ID | L_name | F_name | Middle_name | bhavhudhe | id_realty | mhando |
1 | Ivanova | Daria | B. | 07/16/2000 | ||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | ||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | ||
4 | Annina |
rudo | P. | 31.12.1989 | ||
5 | Gerasimovsky | tarisiro | P. | 14.03.1992 | 4 | Common zvikamu |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common zvikamu |
7 | Sukhanovskaya | chikwata chemajaji | A. | 09/25/1976 | 3 | Total joini uridzi |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Total joini uridzi |
Saka, nokutevera nyore kuruboshwe kubatana SQL muenzaniso, takagamuchira pakarongwa vose, kutamira mberi, mumwe zvivako izvi ari noumhizha / kwemajoini uridzi.
Sezvo mhedziso ndinoda kusimbisa kamwe zvakare kuti marapiro chero mashoko kubva Database inofanira kutorwa musoro. nuances zhinji akasvinudzwa pamberi pedu kuruboshwe abatane SQL muenzaniso nyore, tsanangudzo imwe - usati kutanga kunyora kunyange inokosha mubvunzo, unofanira kunyatsoteerera kunzwisisa zvatinoda kuti pakupedzisira. Rombo rakanaka!
Similar articles
Trending Now