Kuzivisa shanduko WHERE uye PASI mamiriro ezvinhu
The Structured Query Language (SQL) inopa vashandisi vepa database kuti vakwanise kuumba mibvunzo yakagadzirirwa kuti vabvise ruzivo kubva kune databases. Mune imwe nyaya yapfuura, takatsvaga kubvisa ruzivo kubva kune database pakushandisa SQL SELECT mibvunzo . Ngatitaurirei pahurukuro iyoyo uye tsvaga kuti ungaita sei mibvunzo yakakosha yekuwana dheyo inofanana nemamiriro ezvinhu chaiwo.
Ngatimboongororei muenzaniso unobva pane imwe nheyo yeNorthwind inowanzoshandiswa, iyo inowanzotengeswa nemitambo yemagwatidziro semudzidzisi.
Heino zvakanyanyiswa kubva pane dheti yeMichafura yemuchina:
| ProductID | ProductName | SupplierID | QuantityPerUnit | UnitPrice | UnitsIntock |
|---|---|---|---|---|---|
| 1 | Chai | 1 | 10 mabhokisi x 20 mabhegi | 18.00 | 39 |
| 2 | Chang | 1 | 24 - 12 oz mabhodhoro | 19.00 | 17 |
| 3 | Aniseed Syrup | 1 | 12 - 550 ml mabhodhoro | 10.00 | 13 |
| 4 | Chef Anton's Cajun Mwaka | 2 | 48 - 6 oz jars | 22.00 | 53 |
| 5 | Chef Anton's Gumbo Mix | 2 | 36 mabhokisi | 21.35 | 0 |
| 6 | Ambuya va Boysenberry Vanoparadzira | 3 | 12 - 8 oz jars | 25.00 | 120 |
| 7 | Uncle Bob's Organic Akaoma Pears | 3 | 12 - 1 lb pkgs. | 30.00 | 15 |
Mamiriro ezvinhu akareruka
Mimirairo yekutanga yatichaisa pamubvunzo wedu inosanganisira zviri nyore kumiganhu. Tinogona kutsanangurira izvi mune WHERE clause ye SELECT mubvunzo, uchishandisa zviri nyore zvinyorwa zvinyorwa zvakavakirwa nevashandi vese, zvakadai se <,>,> =, uye <=.
Chokutanga, ngatiedzei mubvunzo wakajeka unotibvumira kubvisa urongwa hwezvinhu zvose zviri mu database iyo ine UnitCrice yepamusoro pe20.00:
Izvi zvinogadzira urongwa hwezvinhu zvina, sezvinoratidzwa pasi apa:
ProductName UnitPrice ------- -------- Chef Anton's Gumbo Mix 21.35 Chein Anton's Cajun Seasoning 22.00 Amai va Boysenberry Vanoparadzira 25.00 Mbuya Dob Bob's Pean Pears Pears 30.00Isu tinogonawo kushandisa izwi re WHERE rine string. Izvi zvinowanzofananidza vateveri kune nhamba, neA inomiririra kukosha 1 uye Z inomiririra kukosha 26. Somuenzaniso, tinogona kuratidza mishonga yose nemazita achitanga neU, V, W, X, Y kana Z nemubvunzo unotevera:
Sarudza MutengoNhamba KUBVA muzvigadzirwa ZVAKAPA Nhamba> = 'T'Iyo inobereka chigumisiro:
ProductName ------- Uncle Bob's Organic Dried PearsKuratidza Mapato achishandisa Mitsara
Iyo WHERE clause inotitenderawo kuti tishandise mamiriro ezvinhu akaenzana pane kukosha kuburikidza nekushandisa zvinhu zvakawanda. Somuenzaniso, kana tichida kutora mubvunzo wedu pamusoro uye nekugadzirisa migumisiro kune zvigadzirwa nemitengo pakati pe15.00 ne20.00, tinogona kushandisa sarudzo inotevera:
Sarudza MutengoNhamba, UnitPrice KUBVA muzvigadzirwa PANI UnitPrice> 15.00 NEIIchipiri <20.00Izvi zvinobudisa chigumisiro chinoratidzwa pasi apa:
ProductName UnitPrice ------- -------- Chai 18.00 Chang 19.00Kuratidza Rimi NOMWE PAKATI
SQL inopawo nzira shomanana PAKATI izwi rechiito rinoderedza nhamba yemamiriro ezvinhu atinofanira kuisa uye anoita kuti mhinduro iwedzere kuverengwa. Semuenzaniso, panzvimbo yekushandisa zviri zviviri ZVIMI zviri pamusoro apa, tinogona kutaura mhinduro imwechete se:
Sarudza MutengoNhamba, UnitCrice KUBVA muzvigadzirwa ZVIIIPiPrice PANE 15.00 NO 20.00Sezvimwe nezvimwe zvikamu zvatinosangana nazvo, PAMUSORO pese tichishanda nemaketani. Kana tichida kubudisa mazita emarudzi ose anotanga neV, W kana X, tingashandisa mubvunzo:
Sarudza MutengoNhamba KUBVA muzvishandiso PAKAPENZWA MUTA PANE "A" uye "D"Iyo inobereka chigumisiro:
ProductName ------- Aniseed Syrup Chai Chang Chef Anton's Gumbo Mix Chef Anton's Cajun SeasoningIyo WHERE chikamu chikamu chine simba cheSQL mutauro inokubvumira kuti udzivise migumisiro kune hutsika huri mukati mematanda akaenzana. Inonyanya kushandiswa kubatsira kuburitsa manyorerwo emabhizimisi uye inofanira kuva chikamu chemudziyo wezvigadzirwa zvezvesaiti.
Zvinowanzobatsira kubatanidza zvikamu zvinowanikwa mune imwe nzira yakachengetwa kuitira kuti iwane nyore kune avo vasina SQL ruzivo.