Thaum ua haujlwm nrog cov lus hauv SQL, yuav muaj qee qhov xwm txheej thaum koj xav tau nug peb lossis ntau lub rooj. Koj tuaj yeem koom nrog peb lub rooj los ntawm thawj zaug siv cov lus sib koom los koom nrog ob lub rooj los tsim cov rooj sib koom ib ntus. Tom qab ntawd siv daim ntawv thib ob los koom nrog rooj thib peb.
Cov kauj ruam
Kauj Ruam 1. Ntaus SELECT ua raws cov npe uas koj xav nug
Ntaus lub npe kab ntawm txhua ntawm peb qhov koj xav nug. Cais txhua kab npe nrog tus lej. Hauv qhov piv txwv no, peb yuav nug los ntawm peb lub rooj npe hu ua "Cov Tub Kawm Ntawv", "Tsev Kawm Ntawv", "Cov Lus Qhia"
Piv txwv SELECT student_id, student_name, school_id, school, grade
Kauj Ruam 2. Ntaus FROM ua raws thawj lub npe npe
Qhov no tuaj yeem mus rau kab sib cais lossis tam sim ntawd tom qab thawj nqe lus. Hauv peb qhov piv txwv peb yuav ntaus FROM Cov Tub Ntxhais Kawm.
Kauj Ruam 3. Ntaus nqe lus koom nrog tom qab lub npe ntawm lub rooj thib ob
Muaj plaub hom kev koom nrog nqe lus koj tuaj yeem siv los koom nrog ob lub rooj. Lawv yog raws li hauv qab no:
- Ntaus JOIN los ua ib qho Sab Hauv Koom Nrog. Qhov no rov qab cov ntaub ntawv uas muaj qhov sib piv qhov tseem ceeb hauv ob lub rooj. Piv txwv li los ntawm Cov Tub Ntxhais Kawm Koom nrog Cov Lus Qhia.
- Ntaus LEFT JOIN los ua ib qho Sab Nraud Koom Nrog. Qhov no xa rov qab txhua cov ntaub ntawv los ntawm cov lus sab laug thiab sib piv qhov txiaj ntsig los ntawm lub rooj sab xis. Piv txwv li FROM Cov Tub Ntxhais Kawm LEFT JOIN Paub meej.
- Ntaus RIGHT JOIN los ua a Txheej Txoj Cai Koom Nrog. Qhov no xa rov qab txhua cov ntaub ntawv los ntawm lub rooj sab xis thiab sib piv cov txiaj ntsig los ntawm lub rooj sab laug. Piv txwv li LOS NTAWM Cov Tub Ntxhais Kawm RIGHT JOIN Paub meej.
- Ntaus FULL JOIN los ua a Koom Nrog Sab Nraud. Qhov no xa tag nrho cov ntaub ntawv los ntawm ob lub rooj. Piv txwv li LOS NTAWM Cov Tub Ntxhais Kawm Ua tiav Cov Lus Qhia Koom Tes.
Kauj Ruam 4. Ntaus nqe lus "ON" rau cov ntxhuav thiab kab lus tau koom nrog
Cov syntax rau nqe lus no yog "ON table_1.primary_key = table_2.foreign_key". "Table_1" yog lub npe ntawm thawj lub rooj koj koom nrog, thiab "primary_key" yog thawj kab npe hauv thawj lub rooj. "Table_2" yog lub npe ntawm lub rooj thib ob, thiab "foreign_key" yog lub npe kab lus los ntawm lub rooj thib ob uas sib phim rau thawj kab ntawm thawj lub rooj.
- Hauv peb qhov piv txwv, "Cov Tub Kawm Ntawv" yog thawj lub rooj thiab "student_id" yog lub hauv paus tseem ceeb los ntawm Cov Tub Ntxhais Kawm cov lus, uas tseem nyob hauv Cov Lus Qhia ntxaws. Yog li peb yuav ntaus ON Cov menyuam.student_id = Paub meej.student_id. Qhov no koom nrog Cov Tub Ntxhais Kawm lub rooj nrog Cov Lus Qhia ntxaws siv "student_id" ua qhov tseem ceeb.
- Xwb, yog tus tub kawm ntawv lub npe nyob hauv "Cov Lus Qhia" lub rooj, koj tuaj yeem tso saib cov npe menyuam kawm ntawv nyob hauv qhov chaw ntawm tus tub ntxhais kawm ntawv -id los ntawm kev ntaus ON Cov menyuam kawm ntawv.student_id = Paub meej.student_name.
Kauj Ruam 5. Ntaus nqe lus koom nrog tom qab lub npe ntawm lub rooj thib peb
Qhov no tuaj yeem nyob ntawm kab sib cais lossis tam sim ntawd tom qab "ON" nqe lus uas koom nrog thawj ob lub rooj. Koj tuaj yeem siv ib qho ntawm plaub nqe lus koom nrog.
Hauv peb qhov piv txwv, peb yuav ntaus JOIN Cov Tsev Kawm Ntawv
Kauj Ruam 6. Ntaus nqe lus "ON" uas qhia tias cov ntxhuav thiab kab ntawv twg yuav koom nrog
Cov syntax rau qhov koom nrog thib peb yog "ON table_3.primary_key = table_1.foreign_key". "Tab_1". "Table_3 yog lub npe ntawm cov lus thib peb. Qhov no ntxiv cov lus peb mus rau qhov kawg koom nrog siv thawj kab npe los ntawm cov lus thib peb thiab tus yuam sij txawv teb chaws los ntawm thawj lub rooj. Hauv peb qhov piv txwv, qhov no peb yuav ntaus ON Schools.student_id = Cov Tub Ntxhais Kawm..student_id. Tag nrho cov nqe lus koom nrog yuav tsum zoo ib yam li no:
SELECT student_id, student_name, school_id, school, grade FROM Students FULL JOIN Details on Students.student_id = Details.student_id JOIN Schools ON Schools.student_id = Students.student_id
