Cov lus nug database rho tawm cov ntaub ntawv los ntawm cov ntaub ntawv thiab muab nws ua rau tib neeg nyeem tau. Cov lus nug yuav tsum tau sau rau hauv cov syntax cov ntaub ntawv xav tau - feem ntau yog ib qho txawv ntawm Structured Query Language.
Cov ntsiab lus ntawm SQL Query
SQL queries siv Data Manipulation Language (cov txheej txheem ntawm SQL nqe lus uas nkag los yog hloov cov ntaub ntawv, as opposed to the Data Definition Language that modify the structure of the database itself) muaj plaub blocks, thawj ob ntawm uas yog tsis xaiv tau.
Yam tsawg kawg, SQL lus nug ua raws li hauv qab no:
xaiv X los ntawm Y;
Ntawm no, cov lus tseem ceeb xaiv txheeb xyuas cov ntaub ntawv koj xav kom tso saib thiab lo lus tseem ceeb qhia qhov twg cov ntaub ntawv los ntawm thiab cov ntaub ntawv cov ntaub ntawv koom nrog ib leeg. Optionally, ib qhov uas nqe lus teev txwv cov txheej txheem, thiab pab pawg los ntawm thiab txiav txim los ntawm cov nqe lus koom nrog qhov tseem ceeb thiab tso saib lawv nyob rau hauv ib tug tshwj xeeb sequence.
Piv txwv:
SELECT emp.ssn, emp.last_name, dept.department_name
FROM cov neeg ua haujlwm emp LEFT OUTER JOIN departments dept
ON emp.dept_no=dept.dept_no
WHERE emp.active_flag='Y'ORDER BY 2 ASC;
Cov lus nug no ua rau hauv daim phiaj uas qhia txog tus lej Xaus Saus, tus neeg ua haujlwm lub xeem, thiab tus neeg ua haujlwm lub npe-hauv kab ntawv xaj-raug coj los ntawm cov neeg ua haujlwm thiab cov rooj sib tham. Cov neeg ua haujlwm lub rooj tswj hwm, yog li nws tsuas yog qhia cov npe ntawm lub tuam tsev thaum muaj qhov sib txuam ntawm lub tuam txhab tus lej hauv ob lub rooj (sab laug sab koom yog ib txoj hauv kev sib txuas cov rooj uas nyob rau sab laug lub rooj qhia tag nrho cov txiaj ntsig thiab tsuas yog cov txiaj ntsig ntawm sab xis. -sided rooj tshwm). Tsis tas li ntawd, daim phiaj tsuas yog qhia cov neeg ua haujlwm uas nws tus chij nquag raug teeb tsa rau Y, thiab cov txiaj ntsig tau txheeb xyuas los ntawm lub npe chaw haujlwm.
Tab sis txhua qhov kev tshawb nrhiav cov ntaub ntawv no pib nrog cov lus xaiv.
The SQL SELECT Nqe lus
SQL siv SELECT nqe lus los xaiv, lossis rho tawm, cov ntaub ntawv tshwj xeeb.
Xav txog ib qho piv txwv raws li Northwind database uas nquag xa khoom nrog cov khoom lag luam database raws li kev qhia. Ntawm no yog ib qho excerpt los ntawm database cov neeg ua hauj lwm rooj:
| EmployeeID | LastName | Npe | Chaw nyob | City | Region | |
|---|---|---|---|---|---|---|
| 1 | Davolio | Nancy | Tus Neeg Muag Khoom | 507 20th Ave. E. | Seattle | UA |
| 2 | Nyob zoo | Andrew | Tus Lwm Thawj Coj, Muag | 908 W. Peev Xwm | Tacoma | UA |
| 3 | Leverling | Janet | Tus Neeg Muag Khoom | 722 Moss Bay Blvd. | Kirkland | UA |
Txhawm rau xa tus neeg ua haujlwm lub npe thiab lub npe los ntawm cov ntaub ntawv, SELECT nqe lus yuav zoo li no:
Xaiv Thawj Lub Npe, Lub Npe Xeem, Lub Npe Los Ntawm Cov Neeg Ua Haujlwm;
Nws yuav rov qab:
| Npe | LastName | |
|---|---|---|
| Nancy | Davolio | Tus Neeg Muag Khoom |
| Andrew | Nyob zoo | Tus Lwm Thawj Coj, Muag |
| Janet | Leverling | Tus Neeg Muag Khoom |
Txhawm rau kho cov txiaj ntsig ntxiv, koj tuaj yeem ntxiv cov lus nyob rau qhov twg:
Xaiv Thawj Lub Npe, Lub Npe Xeem Los Ntawm Cov Neeg Ua Haujlwm Lub Nroog='Tacoma';
Nws xa rov qab Lub Npe thiab Lub Npe Kawg ntawm txhua tus neeg ua haujlwm uas yog los ntawm Tacoma:
| Npe | LastName |
|---|---|
| Andrew | Nyob zoo |
SQL xa rov qab cov ntaub ntawv nyob rau hauv kab-thiab-kem daim ntawv uas zoo ib yam li Microsoft Excel, ua kom yooj yim saib thiab ua haujlwm nrog. Lwm yam lus nug tuaj yeem xa rov qab cov ntaub ntawv raws li daim duab lossis daim duab.
Lub Zog Ntawm Cov Lus Nug
A database muaj peev xwm nthuav tawm cov kev sib txawv thiab kev ua ub no, tab sis lub zog no tsuas yog siv los ntawm kev siv cov lus nug. Lub complex database muaj ntau lub rooj khaws cov ntaub ntawv loj. Cov lus nug tso cai rau koj lim cov ntaub ntawv rau hauv ib lub rooj kom koj tuaj yeem txheeb xyuas tau yooj yim dua.
Cov lus nug kuj tuaj yeem ua cov lej ntawm koj cov ntaub ntawv lossis ua haujlwm tswj cov ntaub ntawv. Koj tseem tuaj yeem tshuaj xyuas qhov hloov tshiab rau koj cov ntaub ntawv ua ntej muab lawv rau hauv database.
FAQ
Koj nug li cas rau Access database?
Tsim cov lus nug hauv Microsoft Access, mus rau Create > Query WizardTom ntej no, xaiv hom lus nug, xws li Cov Lus Nug Yooj Yim > OK Xaiv ib lub rooj los ntawm cov ntawv qhia zaub mov > xaiv koj cov teb thiab hom ntawm cov txiaj ntsig koj xav tau > Finish
Cov Lus Nug Lus Nug yog dab tsi?
Structured Query Language, lossis SQL, yog ib hom lus programming siv hauv kev tswj cov ntaub ntawv thiab cov ntaub ntawv sib txheeb. Vim tias nws yooj yim siv thiab ua tau zoo, nws tau muab tso rau hauv kev lag luam databases xws li MySQL, Sybase, Postgres, Oracle, thiab ntau dua.
Koj ua li cas kho qhov kev nug SQL?
txhawm rau txhim kho cov lus nug SQL thiab ua kom muaj txiaj ntsig zoo li qhov ua tau, siv SELECT nqe lus los qhia cov ntaub ntawv los nug cov ntaub ntawv cuam tshuam nkaus xwb. Tsis txhob siv cov lus xaiv DISTINCT, uas yuav siv ntau lub zog ua haujlwm. Siv cov ntawv cim cim tsuas yog thaum kawg ntawm nqe lus, thiab siv LIMIT nqe lus kom rov qab tsuas yog cov ntaub ntawv teev tseg.
