Computers, Programming
Sab laug koom (SQL) - ib qho piv txwv, cov paub piav qhia txog, kev siv ntawm cov kev ua yuam kev
Nyob rau hauv txhua tiag tiag paub database, tag nrho cov ntaub ntawv yog faib nyob rau hauv ib tug nyias muaj nyias ib lub rooj. Muaj ntau ntawm cov ntxhuav teem tawm nyob rau hauv cov kev sib txuas tswvyim nrog txhua lwm yam. Txawm li cas los, nrog kev pab los ntawm Sql queries nws yog tseem ua tau kom muab tso rau ib qhov txuas ntawm cov ntaub ntawv, tsis nyob hauv lub Circuit Court. Qhov no yog ua los ntawm kev ua ib tug koom kev twb kev txuas, uas tso cai rau koj mus ua ib tug kev sib raug zoo ntawm tej tus naj npawb ntawm cov ntxhuav, thiab txawm txuas seemingly disparate ntaub ntawv.
Qhov tsab xov xwm yuav tham hais txog rau sab laug txheej koom. Ua ntej cov tswv yim rau qhov kev piav qhia ntawm no hom ntawm kev twb kev txuas, ntxiv nyob rau hauv ib co database ntxhuav.
Npaj cov tsim nyog ntxhuav
Piv txwv li, nyob rau hauv peb database, muaj yog cov ntaub ntawv hais txog cov neeg thiab lawv tej vaj tse. Ntsiab lus raws li nyob rau hauv lub peb cov ntxhuav: Haiv neeg (cov neeg), Realty (real estate), Realty_peoples (rooj sib raug zoo, cov neeg uas los ntawm dab tsi vaj tse belongs rau). Xav hais tias cov nram qab no cov ntaub ntawv khaws tseg nyob rau hauv lub rooj ntawm cov neeg:
Haiv neeg | ||||
id | L_name | F_name | Middle_name | Hnub yug |
1 | Ivanova | Daria | B. | 07/16/2000 |
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 |
3 | Evgenin | Alexander | Federovich | 04/30/1964 |
4 | Annina | hlub | P. | 31.12.1989 |
5 | Gerasimovsky | vam thiab cia siab | P. | 14.03.1992 |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 |
Av:
Realty | |
id | chaw nyob |
1 | Arkhangelsk, ul. Voronin, d. 7, kv.6 |
2 | Arkhangelsk, ul. Severodvinskaya, d. 84, q. 9 BR. 5 |
3 | Arkhangelsk cheeb tsam, Severodvinsk, st. Lenin, d. 134, q. 85 |
4 | Arkhangelsk cheeb tsam, Novodvinsk, ul. Proletarshaya, d. 16, q. 137 |
5 | Arkhangelsk, pl. Terekhina, d. 89, q. 13 |
Relations neeg - tej khoom vaj tse:
Realty_peoples | ||
id_peoples | id_realty | hom |
7 | 3 | Tag nrho cov kev sib koom tswv cuab |
8 | 3 | Tag nrho cov kev sib koom tswv cuab |
3 | 5 | vaj tse |
7 | 1 | vaj tse |
5 | 4 | Common qhov chaw |
6 | 4 | Common qhov chaw |
Poob koom (Sql) - hauj lwm
Poob compound muaj cov nram qab syntax:
Table_A LAUG KOOM table_B [{RAU predicate} | {SIV spisok_ nrog tolbtsov}] |
Thiab schematically raws li nram no:
Thiab qhov no qhia yog txhais raws li "Xaiv Tag nrho, tsis muaj kev zam, txoj kab ntawm rooj A thiab rooj B mus tso saib xwb tus txuam kab ntawm lub predicate. Yog hais tias lub rooj twb pom nyob rau hauv cov hlua lub rooj rau officers A, ces sau lub resulting txhua thov - qhov tseem ceeb ".
Feem ntau cov feem ntau, thaum rau sab laug twb kev txuas yog qhia RAU, siv yog tsuas yog siv thaum lub kem npe, uas yog npaj los mus ua kom cov kev twb kev txuas yog tib yam.
Sab laug koom - piv txwv ntawm qhov kev siv
Nrog rau kev twb kev txuas ntawm sab laug peb tau pom, tag nrho cov neeg ntawm daim ntawv yog hais tias muaj neeg tej khoom ntiag tug. Ua li no nyob rau hauv rau sab laug koom sql cov lus nug piv txwv:
Xaiv Haiv neeg. *, Realty_peoples.id_realty, Realty_peoples.type LOS NTAWM Haiv neeg LAUG KOOM Realty_peoples RAU Peoples.id = Realty_peoples.id_peoples; |
Nrog rau cov nram qab no tshwm sim:
Query1 | ||||||
id | L_name | F_name | Middle_name | Hnub yug | id_realty | hom |
1 | Ivanova | Daria | B. | 07/16/2000 | ||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | ||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | vaj tse |
4 | Annina | hlub | P. | 31.12.1989 | ||
5 | Gerasimovsky | vam thiab cia siab | P. | 14.03.1992 | 4 | Common qhov chaw |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common qhov chaw |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 | 1 | vaj tse |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 | 3 | Tag nrho cov kev sib koom tswv cuab |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Tag nrho cov kev sib koom tswv cuab |
Raws li peb pom, Ivanova Darya Pugin Vladislav thiab Anninoy Lyubovi Tsis sau npe tiag tiag qub txeeg qub tes cai.
Thiab dab tsi yuav peb tau txais, siv ib tug puab koom puab koom? Raws li koj paub, nws excludes tsis txuam kab, li ntawd, peb tawm ntawm peb kawg qauv yuav tsuas yuav poob:
Query1 | ||||||
id | L_name | F_name | Middle_name | Hnub yug | id_realty | hom |
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | vaj tse |
5 | Gerasimovsky | vam thiab cia siab | P. | 14.03.1992 | 4 | Common qhov chaw |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common qhov chaw |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 | 1 | vaj tse |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 | 3 | Tag nrho cov kev sib koom tswv cuab |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Tag nrho cov kev sib koom tswv cuab |
Nws yuav zoo li tias tus thib ob version kuj muaj raws li cov tej yam kev mob ntawm peb cov teeb meem. Txawm li cas los, yog hais tias peb pib xa rau lwm tus, thiab lwm lub rooj, peb cov neeg los ntawm cov kev tshwm sim twb irretrievably ploj mus. Yog li ntawd, nyob rau hauv kev xyaum, thaum combining ntau lub rooj ntau npaum li cas feem ntau siv laug thiab txoj cai kev twb kev txuas tshaj puab koom.
Tseem yuav saib mus rau sab laug koom sql piv txwv. Muab ib lub rooj rau cov chaw nyob ntawm peb lub tsev nyob:
Xaiv Haiv neeg. *, Realty_peoples.id_realty, Realty_peoples.type, Realty.address LOS NTAWM Haiv neeg SAB LAUG KOOM Realty_peoples RAU Peoples.id = Realty_peoples.id_peoples SAB LAUG KOOM Realty RAU Realty.id = Realty_peoples.id_realty |
Tam sim no peb tau tsis tsuas ib yam ntawm cov kev cai lij choj, tab sis kuj rau qhov chaw nyob ntawm av:
Query1 | |||||||
id | L_name | F_name | Middle_name | Hnub yug | id_realty | hom | chaw nyob |
1 | Ivanova | Daria | B. | 07/16/2000 | |||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | |||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | 5 | vaj tse | Arkhangelsk, pl. Terekhina, d. 89, q. 13 |
4 | Annina | hlub | P. | 31.12.1989 | |||
5 | Gerasimovsky | vam thiab cia siab | P. | 14.03.1992 | 4 | Common qhov chaw | Arkhangelsk cheeb tsam, Novodvinsk, ul. Proletarshaya, d. 16, q. 137 |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common qhov chaw | Arkhangelsk cheeb tsam, Novodvinsk, ul. Proletarshaya, d. 16, q. 137 |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 | 3 | Tag nrho cov kev sib koom tswv cuab | Arkhangelsk cheeb tsam, Severodvinsk, st. Lenin, d. 134, q. 85 |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 | 1 | vaj tse | Arkhangelsk, ul. Voronin, d. 7, kv.6 |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Tag nrho cov kev sib koom tswv cuab |
Arkhangelsk cheeb tsam, Severodvinsk, st. Lenin, d. 134, q. 85 |
Sab laug koom - tus raug siv yuam kev: invalid txoj kev rooj
Basic ua yuam kev nyob rau sab laug txheej koom lub rooj, ob:
- Puas xaiv qhov kev txiav txim ntawm lub rooj rau cov uas cov ntaub ntawv tau ploj lawm.
- Qhov twg kom raug thaum siv ib cov lus nug nrog koom lub rooj.
Xav txog thawj yuam kev. Ua ntej mus rau lub kev txiav txim siab ntawm tej teeb meem yuav tsum tau kom meej meej to taub hais tias yog dab tsi peb xav tau nyob rau hauv lub kawg. Nyob rau hauv qhov kev piv txwv saum toj no, peb coj txhua txhua ib zaug xwb ntawm cov neeg, tab sis ua poob lawm cov lus qhia txog tus kwv nyob rau hauv lub xov tooj 2, uas nws tswv twb tsis pom.
Yog hais tias peb tau tsiv lub rooj nyob rau hauv ib cov lus nug nyob rau hauv ib co chaw, thiab yuav pib nrog «... From Realty tshuav koom Haiv neeg ...» muaj ib tug vaj tse, peb yuav tsis tau poob, koj yuav tsis qhia txog cov neeg.
Tab sis tsis txhob ntshai ntawm sab laug twb kev txuas, hloov mus rau tag nrho lwm, uas yog muaj nyob rau hauv cov kev tshwm sim thiab cov txuam, thiab tsis yog tus txuam kab.
Tom qab tag nrho, lub volume ntawm cov qauv no yog feem ntau loj heev, thiab ntxiv cov ntaub ntawv no ua tau puas. Qhov loj tshaj plaws - mus xyuas seb dab tsi koj xav kom tau ib tug tshwm sim: ntawm tag nrho cov neeg nrog ib daim ntawv teev lawv cov muaj vaj tse los yog rau tag nrho cov daim ntawv teev khoom uas lawv cov tswv (yog tias muaj).
Sab laug koom - tus raug siv yuam kev: Thov muaj tseeb thaum teem cov tej yam kev mob nyob rau hauv lub qhov twg
Qhov thib ob kev ua yuam kev no kuj mob nrog tsis ntawm cov ntaub ntawv, thiab tsis yeej ib txwm tam sim ntawd khees.
Wb rov qab mus rau cov lus nug thaum uas peb tshuav ntawm kev sib txuas tau txais cov ntaub ntawv rau tag nrho cov neeg thiab lawv twb muaj lawm cov cuab yeej. Nco ntsoov cov nram no nrog cov laug koom sql Piv txwv li:
LOS NTAWM Haiv neeg LAUG KOOM Realty_peoples RAU Peoples.id = Realty_peoples.id_peoples; |
Piv txwv peb xav kom meej qhov kev thov thiab tsis tso zis cov ntaub ntawv, qhov chaw uas lub hom ntawm txoj cai - "Khoom". Yog hais tias peb tsuas append, siv tshuav koom sql, ib qho piv txwv ntawm cov hauv qab no mob:
...
Qhov twg hom <> "Khoom" |
peb yuav poob rau cov ntaub ntawv rau cov neeg uas tsis muaj kev vaj tse, vim hais tias cov null nqi thov yog tsis muab piv raws li nram no:
Query1 | ||||||
id | L_name | F_name | Middle_name | Hnub yug | id_realty | hom |
5 | Gerasimovsky | vam thiab cia siab | P. | 14.03.1992 | 4 | Common qhov chaw |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common qhov chaw |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 | 3 | Tag nrho cov kev sib koom tswv cuab |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Tag nrho cov kev sib koom tswv cuab |
Yuav kom tiv thaiv uas tsis txhob tshwm sim rau no yog vim li cas, nws yog zoo tshaj plaws los mus teev cov kev xaiv mob tam sim ntawd raws li qhov kev twb kev txuas. Peb hais kom xav txog cov nram nrog rau sab laug koom sql piv txwv.
Xaiv Haiv neeg. *, Realty_peoples.id_realty, Realty_peoples.type LOS NTAWM Haiv neeg SAB LAUG KOOM Realty_peoples RAU (Peoples.id = Realty_peoples.id_peoples THIAB hom <> "Khoom") |
Cov tshwm sim yuav tsum tau raws li nram no:
Query1 | ||||||
id | L_name | F_name | Middle_name | Hnub yug | id_realty | hom |
1 | Ivanova | Daria | B. | 07/16/2000 | ||
2 | Pugin | Vladislav | Nikolaevich | 29.01.1986 | ||
3 | Evgenin | Alexander | Federovich | 04/30/1964 | ||
4 | Annina | hlub | P. | 31.12.1989 | ||
5 | Gerasimovsky | vam thiab cia siab | P. | 14.03.1992 | 4 | Common qhov chaw |
6 | Gerasimovsky | Oleg | Albertovich | 01/29/1985 | 4 | Common qhov chaw |
7 | Sukhanovskaya | pawg neeg pab txiav | A. | 09/25/1976 | 3 | Tag nrho cov kev sib koom tswv cuab |
8 | Sukhanovskaya | Julia | Y. | 01.10.2001 | 3 | Tag nrho cov kev sib koom tswv cuab |
Yog li, los ntawm cov nram qab no tej yam yooj yim mus rau sab laug koom sql Piv txwv li, peb tau txais ib daim ntawv teev tag nrho cov neeg, mus ntxiv, ib yam ntawm cov khoom nyob rau hauv ib tug sib txig sib luag / ob leeg cov tswv cuab.
Raws li ib tug xaus kuv xav mus sab laj txog dua ib zaug hais tias ib tug qauv ntawm cov ntaub ntawv los ntawm lub database yuav tau them. Muaj ntau nuances qhib nyob rau hauv pem hauv ntej ntawm peb nrog cov laug koom sql piv txwv yooj yim, cov lus piav qhia txog uas ib tug - ua ntej koj pib sau txawm yooj yim cov lus nug, koj yuav tsum ua tib zoo mus rau to taub dab tsi peb xav tau nyob rau hauv lub kawg. Hmoov zoo!
Similar articles
Trending Now