Lik'homphieutha, Mananeo a
Letšehali kena (SQL) - mohlala, le tlhaloso e qaqileng, tšebeliso ea phoso
Ka ea sebele relational polokelongtshedimosetso efe kapa efe, boitsebiso bohle e ajoa ka tafole arohaneng. Ba bangata ba litafole li ngotsoeng ka leqheka la ho buisana le e mong le e tse ling. Leha ho le joalo, ka thuso ea SQL dipotso ho ka etsahala haholo ho beha kamano pakeng tsa ya data, ha aa ageletsweng ka oa potoloho ena. Sena se etsoa ka ho etsa ka kena mabapi, e lumella u ho haha kamano e pakeng tsa palo efe kapa efe ea litafole, 'me esita le hokela lesedi bonahalang disparate.
Sehlooho sena se tla bua ka ho toba ka ntle le letšehali kena. Pele ba tsoela pele ho tlhaloso ya mofuta ona wa mabapi, eketsa ka litafole tse ling polokelongtshedimosetso.
Lokisa litafole hlokahala
Ka mohlala, ka polokelongtshedimosetso rona, ho na le boitsebiso bo mabapi le batho le ea matlo a bona ba sebele. Summary thehiloeng litafole tse tharo: Peoples (batho), Realty (thekiso ea matlo), Realty_peoples (likamano tsa tafole, batho ba neng ba tloha seo thepa ke ea). Nahana ka ya data tse latelang bolokoa litafole tsa sechaba:
lichaba | ||||
ID | L_name | F_name | Middle_name | letsatsi la tsoalo |
1 | Ivanova | Daria |
B. | 07/16/2000 |
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 |
3 | Evgenin | Alexander | Federovich | 04/30/1964 |
4 | Annina | rata | P. | 31.12.1989 |
5 | Gerasimovsky | tšepa | P. | 14.03.1992 |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 |
Thekiso ea matlo:
Realty | |
ID | aterese |
1 | Arkhangelsk, ouaaoul. Voronin, le d. 7, kv.6 |
2 | Arkhangelsk, ouaaoul. Severodvinskaya, le d. 84, Q. 9 BR. 5 |
3 | Arkhangelsk sebakeng, Severodvinsk, st,. Lenin, le d. 134, Q. 85 |
4 | Arkhangelsk sebakeng, Novodvinsk, ouaaoul. Proletarshaya, le d. 16, Q. 137 |
5 | Arkhangelsk, sr. Terekhina, le d. 89, Q. 13 |
Likamano tsa batho - thepa:
Realty_peoples | ||
id_peoples | id_realty | mofuta |
7 | 3 | Kakaretso ea beng kopanetsoeng |
8 | 3 | Kakaretso ea beng kopanetsoeng |
3 | 5 | thepa |
7 | 1 | thepa |
5 | 4 | likarolo e Tloaelehileng |
6 | 4 | likarolo e Tloaelehileng |
Ka ho le letšehali kena (SQL) - Tlhaloso
Ka ho le letšehali komponeng na le hokahanngoang latelang:
Table_A letšehali Join table_B [{ON predicate} | {Sebelisa spisok_ le tolbtsov}] |
Le dira ditshwantsho tse latelang:
Le polelo ena e fetoleloa e le "Khetha All, ntle ho mokgelo, line ea Table A le Table B ho bontša feela tse tshwanang mela e predicate ena. Ha tafole ho ile ha fumanoa tafole khoele bakeng sa lipara A, ka nako eo tlatsa e litšiea sephetho Null - makgabane ".
Hangata ka ho fetisisa, ha mabapi setseng e bontšoa ON, sebelisa e sebediswa feela ha mabitso a lenaneng la, e leng e rerile ho etsa mabapi le tse e tšoanang.
Letšehali kena - mehlala tsa tšebeliso
Le mabapi ea le letšehali re ka bona, batho bohle ho tloha lethathamo le haeba ho na le Batho ba thepa. Ho etsa sena ka ho le letšehali kena SQL potso mohlala:
Khetha Peoples. *, Realty_peoples.id_realty, Realty_peoples.type HO TLOHA Peoples letšehali Join Realty_peoples ON Peoples.id = Realty_peoples.id_peoples; |
Ba le liphello tse latelang:
Query1 | ||||||
ID | L_name | F_name | Middle_name | letsatsi la tsoalo | id_realty | mofuta |
1 | Ivanova | Daria | B. | 07/16/2000 | ||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | ||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | thepa |
4 | Annina | rata | P. | 31.12.1989 | ||
5 | Gerasimovsky | tšepa | P. | 14.03.1992 | 4 | likarolo e Tloaelehileng |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | likarolo e Tloaelehileng |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 | 1 | thepa |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 | 3 | Kakaretso ea beng kopanetsoeng |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Kakaretso ea beng kopanetsoeng |
Ha re ntse re bona, Ivanova Darya Pugin Vladislav le Anninoy Lyubovi No ngolisitsoeng litokelo tsa thekiso ea matlo.
Le se neng se tla re amohetse, sebelisa e ka hare kena Inner kena? Joalokaha u tseba, e beheletse ka thoko thepa mela bao e seng tshwanelelanang, kahoo a mararo tsoa sampole rona ea ho qetela e ne e tla feela ho tla ba akhetse:
Query1 | ||||||
ID | L_name | F_name | Middle_name | letsatsi la tsoalo | id_realty | mofuta |
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | thepa |
5 | Gerasimovsky | tšepa | P. | 14.03.1992 | 4 | likarolo e Tloaelehileng |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | likarolo e Tloaelehileng |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 | 1 | thepa |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 | 3 | Kakaretso ea beng kopanetsoeng |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Kakaretso ea beng kopanetsoeng |
E ne e tla bonahala eka ha ho version ea bobeli e boetse e kopana le maemo a sa bothata ba rōna. Leha ho le joalo, haeba re qala ho hokela ka e mong, 'me tafole e' ngoe, batho ba bararo ba lebaka la se irretrievably felile. Ka hona, ka liketso, ha kopantseng litafole tse ngata haholo hangata sebediswa Left le Right mabapi feta Inner kena.
E tla tsoela pele ho sheba ka ho le letšehali kena mehlala SQL. Hokela tafole le liaterese tsa matlo a rōna:
Khetha Peoples. *, Realty_peoples.id_realty, Realty_peoples.type, Realty.address HO TLOHA Peoples Letšehali Join Realty_peoples ON Peoples.id = Realty_peoples.id_peoples Letšehali Join Realty ON Realty.id = Realty_peoples.id_realty |
Hona joale re fumana e seng feela e le oa mofuta ofe molao, empa hape le aterese ea thekiso ea matlo:
Query1 | |||||||
ID | L_name | F_name | Middle_name | letsatsi la tsoalo | id_realty | mofuta | aterese |
1 | Ivanova | Daria | B. | 07/16/2000 | |||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | |||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | thepa | Arkhangelsk, sr. Terekhina, le d. 89, Q. 13 |
4 | Annina | rata | P. | 31.12.1989 | |||
5 | Gerasimovsky | tšepa | P. | 14.03.1992 | 4 | likarolo e Tloaelehileng | Arkhangelsk sebakeng, Novodvinsk, ouaaoul. Proletarshaya, le d. 16, Q. 137 |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | likarolo e Tloaelehileng | Arkhangelsk sebakeng, Novodvinsk, ouaaoul. Proletarshaya, le d. 16, Q. 137 |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 | 3 | Kakaretso ea beng kopanetsoeng | Arkhangelsk sebakeng, Severodvinsk, st,. Lenin, le d. 134, Q. 85 |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 | 1 | thepa | Arkhangelsk, ouaaoul. Voronin, le d. 7, kv.6 |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Kakaretso ea beng kopanetsoeng |
Arkhangelsk sebakeng, Severodvinsk, st,. Lenin, le d. 134, Q. 85 |
Letšehali kena - tshebediso e tloaelehileng ea phoso: Tsamaiso lefeela tafole
liphoso tsa motheo entsoeng ka le ka ntle le letšehali kena litafole, tse peli:
- Ka nepo a khetha taelo ea litafole bakeng sa tse ya data ile lahlehile.
- Moo liphoso ha u sebelisa ka potso le kena litafole.
Nahana ka phoso pele. Pele ho qeto ea bothata leha e le efe e lokela ho ka ho hlaka ne a utloisisa hore na re batla ho fumana qetellong. Ka mohlala ona ka holimo, re o ile a nka le leng le le leng le 'ngoe ea batho, empa ka ho feletseng lahlehile boitsebiso bo mabapi le Sepheo tlas'a palo 2, eo mong'a o ne a sa fumanoa.
Haeba re ile ra fallela litafole ka potso ee libakeng tse ling, 'me o ne a tla qala ka ho «... From Realty setseng kena Peoples ...» leha e le efe thepa e' ngoe, re ne a ke ke lahlehile, u ke ke ua bolella ka batho.
Empa u se ke ua tšohile tsa mabapi le letšehali, switjha ho feletseng ka ntle, eo e kenyelelitsoe a sephetho le tshwanelelanang, 'me ha e mela e tshwanelelanang.
Ka mor'a hore tsohle, le bophahamo ba modumo ea sampole hangata kholo haholo, 'me ya data e eketsehileng ha e le hantle e na thuso. The ntho e ka sehloohong - ho utloisisa hore na seo u se batla ho fumana lebaka: batho bohle ba nang le lethathamo la thepa ea bona e fumaneha kapa kaofela lethathamo thepa le beng ba tsona (haeba leha e le efe).
Letšehali kena - tshebediso e tloaelehileng ea phoso: Request e nepahetse ha ho ipehela maemo a le hokae
The phoso ea bobeli ho boetse ho amahanngoa le ho lahleheloa ha ya data, le ha kamehla hang-hang ho bonahalang eka.
A re ke re khutlele ho potso eo ka eona re ile ra tloha ka tsela dikgokelo amohetse ya data bakeng sa batho bohle ba le thepa ea bona a le teng. Hopola tse latelang le letšehali kena mohlala SQL:
HO TLOHA Peoples letšehali Join Realty_peoples ON Peoples.id = Realty_peoples.id_peoples; |
A re nke hore re batla ho hlakisa kopo ya 'me a sa khumo ya data, moo mofuta oa molao - "Property". Ha re feela ho seoketsarekoto, sebelisa setseng kena SQL, ka mohlala oa boemo bo latelang:
...
Moo mofuta <> "Property" |
re tla lahleheloa ke ya data ka batho ba se nang thepa, hobane null boleng Null e sa bapisoa tsela e latelang:
Query1 | ||||||
ID | L_name | F_name | Middle_name | letsatsi la tsoalo | id_realty | mofuta |
5 | Gerasimovsky | tšepa | P. | 14.03.1992 | 4 | likarolo e Tloaelehileng |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | likarolo e Tloaelehileng |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 | 3 | Kakaretso ea beng kopanetsoeng |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Kakaretso ea beng kopanetsoeng |
Ho thibela liphoso ho tloha etsahalang ka lebaka lena, ho molemo ho beha boemo kgetho hang-hang holim 'mabapi. Re fana ka maikutlo a ho nahana ka e latelang le letšehali kena mohlala SQL.
Khetha Peoples. *, Realty_peoples.id_realty, Realty_peoples.type HO TLOHA Peoples Letšehali Join Realty_peoples ON (Peoples.id = Realty_peoples.id_peoples AND mofuta <> "Property") |
Phello e tla ba ka mokgwa o latelang:
Query1 | ||||||
ID | L_name | F_name | Middle_name | letsatsi la tsoalo | id_realty | mofuta |
1 | Ivanova | Daria | B. | 07/16/2000 | ||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | ||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | ||
4 | Annina |
rata | P. | 31.12.1989 | ||
5 | Gerasimovsky | tšepa | P. | 14.03.1992 | 4 | likarolo e Tloaelehileng |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | likarolo e Tloaelehileng |
7 | Sukhanovskaya | lekhotla | A. | 09/25/1976 | 3 | Kakaretso ea beng kopanetsoeng |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Kakaretso ea beng kopanetsoeng |
Kahoo, ka hore re latele tsela e bonolo ka ho le letšehali kena mohlala SQL, re ile ra fumana lethathamo la batho bohle, ho fallela ho ea pele, e 'ngoe ea thepa tsena ka tekano / beng kopanetsoeng.
Ka phetheha Ke kopa ho hatisa hang hape hore ho na le sampole ea tlhahisoleseding efe kapa efe ho tswa ho polokelongtshedimosetso le lokela ho ho nkoa ka e nang le boikarabelo. makolopetso a mangata o ile a bula ka pel'a rōna le letšehali kena mohlala e bonolo SQL, tlhaloso ea eona e le nngwe - pele u qala ho ngola esita le potso ea motheo, u lokela ho ka hloko hore ba utloisise seo re batlang ho se fumana qetellong. Mahlohonolo!
Similar articles
Trending Now