Qhia tawm qhov chaw nyob thiab qhov kev sib tw
Lus Askiv Structured Query Language (SQL) muab cov neeg siv database uas muaj peev xwm los tsim kev cai queries rau extract ntaub ntawv los ntawm databases. Nyob rau hauv ib tsab xov xwm ua ntej, peb tshawb cov ntaub ntawv extracting los ntawm ib qho siv SQL SELECT queries . Cia peb nthuav dav thaum sib tham thiab tshawb saib seb koj yuav ua tiav cov lus nug zoo li cas los muab cov ntaub ntawv qhia txog cov xwm txheej.
Cia peb xav txog ib qho piv txwv raws li feem ntau siv Northwind database, uas feem ntau xa nrog cov khoom database raws li ib qho kev qhia.
Ntawm no yog ib qho excerpt los ntawm cov ntaub ntawv database cov lus:
| ProductID | ProductName | Muab khoom siv | QuantityPerUnit | UnitPrice | UnitsInStock |
|---|---|---|---|---|---|
| 1 | Chai | 1 | 10 lub thawv x 20 hnab | 18.00 | 39 |
| 2 | Tseeb | 1 | 24 - 12 oz fwj | 19.00 | 17 |
| 3 | Aniseed Syrup | 1 | 12 - 550 ml hwj | 10.00 | 13 |
| 4 | Tus kws kho mob Anton tus Cajun Seasoning | 2 | 48 - 6 oz txhiab | 22.00 | 53 |
| 5 | Tus kws lij choj Anton lub Gumbo Mix | 2 | 36 lub thawv | 21.35 | 0 |
| 6 | Pog yawg Grand Boysenberry kis mus | 3 | 12 - 8 txhiab txhiab | 25.00 | 120 |
| 7 | Txiv ntxawm Bob lub Organic qhuav Pears | 3 | 12 - 1 lb pkgs. | 30.00 | 15 |
Yooj Yim Zov Nyuaj Mob
Thawj cov kev txwv peb yuav muab tso rau hauv peb cov lus nug muaj cwj ciam yooj yim. Peb tuaj yeem hais qhia cov no hauv nqe lus ntawm SELECT cov lus nug, siv cov lus qhia yooj yim uas tsim muaj cov neeg siv kev lag luam, xws li <,>,> =, thiab <=.
Ua ntej, peb tau sim cov lus nug yooj yooj yim uas cia peb rho tawm ib daim ntawv teev tag nrho cov khoom hauv database uas muaj UnitPrice ntawm ntau tshaj 20.00:
Qhov no ua ib daim ntawv teev cov plaub khoom, raws li qhia hauv qab no:
ProductName UnitPrice ------- -------- Kws Ua Zaubmov Ntsuab Tus Gumbo Xyooj 21.35 Tus Thawj Coj Tus Txiv Ntoo Cajun Caij 22.00 Pog Grand Boysberry Spread 25.00 Txiv Neej Bob Cov Organic Pears 30.00Peb kuj tseem siv tau qhov chaw txiav yeeb yam nrog txoj hlua qhov tseem ceeb. Qhov no yeej muaj cov cim rau cov lej, nrog A uas yog tus nqi 1 thiab Z sawv cev tus nqi 26. Piv txwv li, peb yuav qhia tag nrho cov khoom nrog npe pib nrog U, V, W, X, Y lossis Z nrog cov lus nug nram qab no:
Xaiv ProductName ntawm cov khoom muag ProductName> = 'T'Uas ua rau qhov tshwm sim:
ProductName ------- Txiv ntxawm Bob lub Organic qhuav PearsQhia Cov Ntxhais Uas Siv cov Ncig Teb Chaws
QHOV CHAW LI CAS kuj tseem tso cai rau peb siv ntau yam kev mob ntawm tus nqi uas siv ntau yam kev mob. Piv txwv li, yog tias peb xav coj peb cov lus nug saum toj no thiab txwv cov ntsiab lus rau cov khoom muaj nqi ntawm 15.00 thiab 20.00, peb yuav siv cov lus nug hauv qab no:
Xaiv ProductName, UnitPrice LOS NTAWM cov khoom siv THOV QHIA> 15.00 THIAB UnitPrice <20.00Qhov no ua rau qhov tshwm sim qhia hauv qab no:
ProductName UnitPrice ------- -------- Chai 18.00 Hloov 19.00Qhia cov lus cim cia nrog BETWEEN
SQL kuj muab ib qhov shortcut synthax uas txo cov kab ke uas peb xav kom muaj thiab ua rau cov lus nug tau ntau dua. Piv txwv, es tsis txhob siv ob qhov teeb meem saum toj no, peb tuaj yeem qhia cov lus nug tib yam li:
Xaiv ProductName, UnitPrice LOS NTAWM cov khoom THOV KHOOM UnitPrice BETWEEN 15.00 THIAB 20.00Xws li nrog peb lwm tus mob clauses, BETWEEN ua haujlwm nrog cov hlua qhov tseem ceeb thiab. Yog tias peb xav tsim ib daim ntawv teev npe ntawm txhua lub teb chaws pib nrog V, W los yog X, peb yuav siv cov lus nug:
Xaiv ProductName ntawm cov khoom siv ProductName BETWEEN "A" thiab "D"Uas ua rau qhov tshwm sim:
ProductName ------- Aniseed Syrup Chai Chang Tus Thawj Kws Kws Kho Mob Anton Anton Gumbo Cov Kws Kho Mob Ua Kws Ncaj Ncees Anton's Cajun SeasoningQhov chaw clause yog qhov tseem ceeb tshaj ntawm cov lus SQL uas tso cai rau koj kom txwv tsis pub muaj qhov pom tshwm sim hauv kev teev cia. Nws yog nquag siv los pab qhia kev lag luam logic thiab yuav tsum yog ib feem ntawm txhua tus neeg tuav ntaub ntawv cov cuab yeej siv.
Nws yog feem ntau yuav pab tau siv cov kev cai clauses rau hauv txoj kev khaws cia kom nws nkag mus rau cov neeg tsis muaj kev paub txog SQL.