SQL-päringute kirjutamiseks SQLite'i andmebaasi peate teadma, kuidas klauslid SELECT, FROM, WHERE, GROUP BY, ORDER BY ja LIMIT töötavad ja kuidas neid kasutada.
Selle õpetuse käigus saate teada, kuidas neid klausleid kasutada ja kuidas kirjutada SQLite-klausleid.
Selles õpetuses saate teada
- Andmete lugemine valikuga
- Nimed ja varjunimed
- KUS
- Piiramine ja tellimine
- Duplikaatide eemaldamine
- Kokkuvõte
- Grupp BY
- Päring ja alampäring
- Tehingute määramine -UNION, Intersect
- NULL käsitsemine
- Tingimuslikud tulemused
- Levinud tabeli väljend
- Täpsemad päringud
Andmete lugemine valikuga
Klausel SELECT on peamine lause, mida kasutate SQLite andmebaasi päringute tegemiseks. Klauslis SELECT määrate, mida valida. Kuid enne valitud klauslit vaatame, kust saame FROM-klausli abil andmeid valida.
Klauslit FROM kasutatakse selleks, et määrata, kuhu soovite andmeid valida. Klauslis alates saate määrata ühe või mitu tabelit või alampäringut, millest andmeid valida, nagu näeme hiljem õpetustes.
Pange tähele, et kõigi järgmiste näidete puhul peate käivitama sqlite3.exe ja avama vooluühenduse näidisandmebaasiga:
1. samm) Selles etapis
- Avage Minu arvuti ja navigeerige järgmisse kataloogi " C: \ sqlite " ja
- Seejärel avage " sqlite3.exe ":
Samm 2) Avage järgmise käsuga andmebaas " TutorialsSampleDB.db ":
Nüüd olete andmebaasis valmis käivitama mis tahes tüüpi päringuid.
Klauslis SELECT saate valida mitte ainult veeru nime, vaid teil on palju muid võimalusi, et määrata, mida valida. Järgmiselt:
VALI *
See käsk valib kõik veerud kõigist viidatud tabelitest (või alampäringutest) klauslis FROM. Näiteks:
VALI *ÕpilasteltINNER JOIN Osakonnad on üliõpilased.DepartmentId = Departments.DepartmentId;
See valib kõik veerud nii õpilaste kui ka osakondade tabelitest:
SELECT tabenimi. *
See valib kõik veerud ainult tabelist "tabenimi". Näiteks:
VALI õpilased. *ÕpilasteltINNER JOIN Osakonnad on üliõpilased.DepartmentId = Departments.DepartmentId;
See valib ainult õpilaste tabelist kõik veerud:
Sõnasõnaline väärtus
Sõnasõnaline väärtus on konstantne väärtus, mille saab määrata valitud lauses. Sõnasõnalisi väärtusi saate tavaliselt kasutada samamoodi nagu veerus nimetusi klauslis SELECT. Need sõnasõnalised väärtused kuvatakse iga rea jaoks SQL-päringu tagastatud ridadest.
Siin on mõned näited erinevatest sõnasõnalistest väärtustest, mille saate valida:
- Numbriline sõnasõnaline - numbrid mis tahes vormingus, näiteks 1, 2,55,… jne
- String-literaalid - mis tahes string „USA”, „see on näidistekst” jne.
- NULL - NULL väärtus.
- Current_TIME - see annab teile praeguse aja.
- CURRENT_DATE - see annab teile praeguse kuupäeva.
See võib olla mugav mõnes olukorras, kus peate kõigi tagastatud ridade jaoks valima püsiva väärtuse. Näiteks kui soovite tabelist Õpilased valida kõik õpilased uue veeruga, mille nimi on riik, mis sisaldab väärtust "USA", saate seda teha:
SELECT *, 'USA' AS Country FROM Student;
See annab teile kõik õpilaste veerud ja uue veeru "Riik":
Pange tähele, et see uus veerg Riik ei ole tegelikult tabelisse lisatud uus veerg. See on virtuaalne veerg, mis on loodud päringus tulemuste kuvamiseks ja seda tabelisse ei looda.
Nimed ja varjunimed
Alias on veeru uus nimi, mis võimaldab teil valida uue nimega veeru. Veergude varjunimed määratakse märksõna "AS" abil.
Näiteks kui soovite valida veeru StudentName, mille tagastamiseks kasutatakse "StudentName" asemel "Student Name", saate sellele anda järgmise varjunime:
SELECT StudentName AS 'Student Name' õpilastelt;
See annab teile õpilaste nimed "StudentName" asemel nimega "Student Name" järgmiselt:
Pange tähele, et veeru nimi on endiselt " StudentName "; veerg StudentName on endiselt sama, varjunime järgi see ei muutu.
Alias ei muuda veeru nime; see muudab lihtsalt SELECT-klauslis kuvatava nime.
Pange tähele ka seda, et märksõna "AS" on valikuline, võite pseudonüümi nime ilma selleta panna, umbes nii:
SELECT StudentName 'Student Name' õpilastelt;
Ja see annab teile täpselt sama väljundi kui eelmine päring:
Samuti saate tabelitele anda varjunimed, mitte ainult veerud. Sama märksõnaga "AS". Näiteks saate seda teha:
VALI s. * Õpilaste hulgast;
See annab teile tabeli Õpilased kõik veerud:
See võib olla väga kasulik, kui liitute mitme tabeliga; selle asemel, et korrata päringus kogu tabeli nime, võite anda igale tabelile lühikese varjunime. Näiteks järgmises päringus:
Valige õpilased. Õpilase nimi, osakonnad. Osakonna nimiÕpilasteltINNER JOIN Osakonnad on üliõpilased.DepartmentId = Departments.DepartmentId;
Selle päringuga valitakse iga õpilase nimi tabelist "Õpilased" ja osakonna nimi tabelist "Osakonnad":
Kuid sama päringu saab kirjutada järgmiselt:
Valige s.StudentName, d.DepartmentNameÜliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentId;
- Andsime õpilastele tabeli varjunime "s" ja osakondade tabeli varjunime "d".
- Siis selle asemel, et kasutada täielikku tabeli nime, kasutasime neile viitamiseks nende varjunimesid.
- INNER JOIN ühendab tingimuse abil kaks või enam tabelit. Meie näites liitusime õpilaste tabeliga osakonna tabeliga veeruga DepartmentId. Sissejuhatus INNER JOIN on põhjalikult selgitatud ka õpetuses "SQLite Joins".
See annab teile täpse väljundi nagu eelmine päring:
KUS
SQL-päringute kirjutamine ainult SELECT-klausli abil koos klausliga FROM, nagu nägime eelmises jaotises, annab teile kõik tabelite read. Kui soovite aga tagastatud andmeid filtreerida, peate lisama klausli "KUS".
Klauslit WHERE kasutatakse SQL-päringu tagastatud tulemuste komplekti filtreerimiseks. See, kuidas klausel WHERE töötab:
- Klauslis WHERE saate määrata "avaldise".
- Seda väljendit hinnatakse iga rea eest, mis tagastatakse klauslis FROM määratud tabelist (tabelitest).
- Avaldist hinnatakse Boole'i avaldisena, mille tulemus on kas tõene, vale või null.
- Siis tagastatakse ainult read, mille puhul väljendit hinnati tõese väärtusega, ja vale või nulliga tulemusi ignoreeritakse ega lisata tulemuste hulka.
- Tulemuskomplekti WHERE klausli filtreerimiseks peate kasutama väljendeid ja operaatoreid.
SQLite operaatorite loetelu ja nende kasutamine
Järgmises jaotises selgitame, kuidas saab avaldise ja operaatorite abil filtreerida.
Avaldis on üks või mitu sõnasõnalist väärtust või veergu, mis on omavahel ühendatud operaatoriga.
Pange tähele, et väljendeid saate kasutada nii SELECT-klauslis kui ka WHERE-klauslis.
Järgmistes näidetes proovime väljendeid ja operaatoreid nii valikulauses kui ka WHERE klauslis. Selleks, et näidata, kuidas nad esinevad.
On erinevaid avaldiste ja operaatorite tüüpe, mida saate määrata järgmiselt:
SQLite liitumisoperaator "||"
Seda operaatorit kasutatakse ühe või mitme sõnasõnalise väärtuse või veeru ühendamiseks. See annab ühe rea tulemusi kõikidest liitunud sõnasõnalistest väärtustest või veergudest. Näiteks:
SELECT 'ID koos nimega:' || StudentId || Tudenginimi AS StudentIdWithNameÕpilastelt;
See liitub uueks varjunimeks " StudentIdWithName ":
- Sõna otsene stringiväärtus " Id with Name: "
- veeru " StudentId " väärtusega ja
- veeru „ StudentName ” väärtusega
SQLite CAST-operaator:
CAST-operaatorit kasutatakse väärtuse teisendamiseks andmetüübist teiseks.
Näiteks kui teil on arvväärtus salvestatud stringiväärtusena nagu see ' '12 .5' 'ja soovite selle teisendada numbriliseks väärtuseks, saate operaatori CAST abil seda teha näiteks " CAST ('12 .5' AS PÄRIS) ". Või kui teil on kümnendkoht, näiteks 12.5, ja peate saama ainult täisarvu, saate selle valada sellisesse täisarvu nagu "CAST (12.5 AS INTEGER)".
Näide
Järgmises käsus proovime teisendada erinevad väärtused muud tüüpi andmeteks:
SELECT CAST ('12,5 'AS REAL) ToReal, CAST (12,5 AS INTEGER) AS ToInteger;
See annab teile:
Tulemus on järgmine:
- CAST ('12,5 'AS REAL) - väärtus '12,5' on stringi väärtus, see teisendatakse REAL väärtuseks.
- CAST (12.5 AS INTEGER) - väärtus 12,5 on kümnendarv, teisendatakse see täisarvuks. Kümnendosa kärbitakse ja see saab 12.
SQLite aritmeetikaoperaatorid:
Võtke kaks või enam arvulist litraalväärtust või numbriveergu ja tagastage üks arvväärtus. SQLite'is toetatavad aritmeetilised operaatorid on:
|
Näide:
Järgmises näites proovime viit aritmeetikaoperaatorit, millel on ühesugused sõnalised arvväärtused
vali klausel:
VALI 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
See annab teile:
Pange tähele, kuidas me siin kasutasime SELECT-lauset ilma FROM-klauslita. Ja see on SQLite'is lubatud, kui valime sõnasõnalised väärtused.
SQLite võrdlusoperaatorid
Võrrelge kahte operandi üksteisega ja tagastage tõene või väär järgmiselt:
|
Pange tähele, et SQLite väljendab tõelist väärtust 1-ga ja valeväärtust 0-ga.
Näide:
VALI10 <6 AS '<', 10 <= 6 AS '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 AS '=', 10 == 6 AS '==',10! = 6 AS '! =', 10 <> 6 AS '<>';
See annab midagi sellist:
SQLite Pattern Matching operaatorid
" LIKE " - kasutatakse mustrite sobitamiseks. Kasutades nuppu " Meeldib ", saate otsida väärtusi, mis vastavad metamärgi abil määratud mustrile.
Operand vasakul võib olla kas stringi sõnasõnaline väärtus või stringi veerg. Mustri saab täpsustada järgmiselt:
- Sisaldab mustrit. Näiteks õpilasnimi LIKE '% a%' - see otsib õpilaste nimesid, mis sisaldavad veerus StudentName mis tahes positsiooni tähte "a".
- Alustab mustrist. Näiteks " StudentName LIKE 'a%" "- otsige õpilaste nimesid, mis algavad tähega" a ".
- Lõpeb mustriga. Näiteks " StudentName LIKE '% a' " - otsige õpilaste nimesid, mis lõpevad tähega "a".
- Mis tahes stringis oleva tähemärgi sobitamine alakriipsuga "_". Näiteks " StudentName LIKE 'J___' " - otsige õpilaste nimesid, mis on 4 tähemärgi pikkused. See peab algama tähega "J" ja pärast "J" tähte võib sellel olla veel kolm tähte.
Mustriga sobitamise näited:
- Hankige õpilaste nimed, mis algavad tähega 'j':
VALIGE õpilasnimi õpilaste hulgast, kuhu õpilase nimi sarnaneb 'j%';
Tulemus:
- Õpilaste nimed lõpevad y-tähega:
VALIGE õpilasnimi õpilaste hulgast, kus õpilasnimi sarnaneb '% y';
Tulemus:
- Hankige õpilaste nimed, mis sisaldavad tähte n:
VALI Üliõpilaste nimi õpilastest, kus õpilasnimi sarnaneb '% n%';
Tulemus:
"GLOB" - on samaväärne operaatori LIKE, kuid erinevalt LIKE operaatorist on GLOB tõstutundlik. Näiteks tagastavad järgmised kaks käsku erinevad tulemused:
VALI 'Jack' GLOB 'j%';VALI 'Jack' LIKE 'j%';
See annab teile:
- Esimene lause tagastab 0 (vale), kuna GLOB-operaator on tõstutundlik, mistõttu 'j' ei ole võrdne 'J' -ga. Kuid teine lause tagastab 1 (tõene), kuna operaator LIKE ei ole tõstutundlik, seega võrdub 'j' tähega 'J'.
Muud operaatorid:
SQLite JA
Loogiline operaator, mis ühendab ühe või mitu avaldist. See naaseb tõeseks ainult siis, kui kõik väljendid annavad tõelise väärtuse. Kuid see tagastab vale ainult siis, kui kõik väljendid annavad "vale" väärtuse.
Näide:
Järgmine päring otsib õpilasi, kellel on StudentId> 5 ja StudentName algab tähega N, tagastatud õpilased peavad vastama kahele tingimusele:
VALI *ÕpilasteltKUS (StudentId> 5) JA (StudentName LIKE 'N%');
Väljundina annab see ülaltoodud ekraanipildil teile ainult "Nancy". Nancy on ainus õpilane, kes vastab mõlemale tingimusele.
SQLite VÕI
Loogiline operaator, mis ühendab ühe või mitu avaldist, nii et kui üks kombineeritud operaatoritest annab tõene, siis naaseb see tõene. Kui aga kõik väljendid annavad vale, tagastab see vale.
Näide:
Järgmine päring otsib õpilasi, kellel on StudentId> 5 või StudentName algab tähega N, tagastatud õpilased peavad vastama vähemalt ühele tingimusest:
VALI *ÕpilasteltWHERE (StudentId> 5) VÕI (StudentNAME LIKE 'N%');
See annab teile:
Väljundina annab see ülaltoodud ekraanipildil õpilase nime, kelle nimes on täht "n", pluss õpilase ID väärtusega> 5.
Nagu näete, on tulemus erinev operaatori AND päringust.
SQLite VAHEL
VAHEL kasutatakse nende väärtuste valimiseks, mis jäävad kahe väärtuse vahemikku. Näiteks tagastab " X BETWEEN Y AND Z " väärtusega true (1), kui väärtus X on kahe väärtuse Y ja Z vahel. Vastasel juhul tagastab vale (0). " X Y JA Z VAHEL " vastab ekvivalendile " X> = Y JA X <= Z ", X peab olema suurem või võrdne Y-ga ja X on väiksem või võrdne Z-ga.
Näide:
Järgmises näite päringus kirjutame päringu, et saada õpilasi, kelle Id väärtus on vahemikus 5 kuni 8:
VALI *ÕpilasteltKUS üliõpilane oli 5. ja 8. VAHEL;
See annab ainult õpilastele, kelle ID on 5, 6, 7 ja 8:
SQLite IN
Võtab ühe operandi ja operandiloendi. See tagastatakse tõesena, kui esimene operandi väärtus on võrdne loendi ühe operandi väärtusega. IN-operaator tagastab väärtuse true (1), kui operandiloend sisaldab selle väärtustes esimest operandi väärtust. Vastasel juhul tagastab vale (0).
Nii: " col IN (x, y, z) ". See on samaväärne väärtusega " (col = x) või (col = y) või (col = z) ".
Näide:
Järgmine päring valib õpilased, kelle ID on ainult 2, 4, 6, 8:
VALI *ÕpilasteltWHERE StudentId IN (2, 4, 6, 8);
Nagu nii:
Eelmine päring annab täpse tulemuse järgmise päringuna, kuna need on samaväärsed:
VALI *ÕpilasteltKUS (StudentId = 2) VÕI (StudentId = 4) VÕI (StudentId = 6) VÕI (StudentId = 8);
Mõlemad päringud annavad täpse väljundi. Kuid erinevus kahe päringu vahel on see, et esimene päring kasutasime operaatorit "IN". Teises päringus kasutasime mitut operaatorit "OR".
IN-operaator on samaväärne mitme OR-operaatori kasutamisega. " WHERE StudentId IN (2, 4, 6, 8) " on samaväärne väärtusega " WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); "
Nagu nii:
SQLite EI SISSE
Operand "EI SISSE" on operaatori IN vastand. Kuid sama süntaksiga; selleks on vaja ühte operandi ja operandide loendit. See tagastatakse tõesena, kui esimese operandi väärtus ei ole võrdne loendi ühe operandi väärtusega. st tagastatakse väärtus true (0), kui operandiloend ei sisalda esimest operandi. Niimoodi: " col NOT IN (x, y, z) ". See on samaväärne väärtusega " (col <> x) AND (col <> y) AND (col <> z) ".
Näide:
Järgmine päring valib õpilased, kelle ID pole võrdne nende ID-dega 2, 4, 6, 8:
VALI *ÕpilasteltKUS õpilane EI SISSE (2, 4, 6, 8);
Nagu nii
Eelmise päringu korral anname täpse tulemuse järgmise päringuna, kuna need on samaväärsed:
VALI *ÕpilasteltKUS (StudentId <> 2) JA (StudentId <> 4) JA (StudentId <> 6) JA (StudentId <> 8);
Nagu nii:
Ülaltoodud ekraanipildil
Kasutasime mitut mitte võrdset operaatorit "<>", et saada nimekiri õpilastest, mis pole võrdsed ühegi järgmise ID-de 2, 4, 6 ega 8-ga. See päring tagastab kõik teised õpilased peale nende ID-de loendi.
SQLite OLEMAS
Operaatorid OLEMAS ei võta ühtegi operandi; pärast seda kulub ainult SELECT-klausel. Operaator OLEMAS tagastab väärtuse true (1), kui SELECT-klauslist on tagastatud ridu, ja vale (0), kui SELECT-klauslist pole üldse ühtegi rida tagastatud.
Näide:
Järgmises näites valime osakonna nime, kui õpilaste tabelis on osakonna ID:
Valige osakonna nimiOsakondadest AS dKUS OLEMAS (VALI Osakonna ID üliõpilaste hulgast s WHERE d.DepartmentId = s.DepartmentId);
See annab teile:
Tagastatakse ainult kolm osakonda " IT, füüsika ja kunst ". Ja osakonna nime " Matemaatika " ei tagastata, kuna selles osakonnas pole ühtegi üliõpilast, mistõttu osakonna ID pole üliõpilaste tabelis olemas. Sellepärast ignoreeris operaator OLEMAS osakonda " Matemaatika ".
SQLite EI
Pöörab eelmise operaatori tulemuse, mis järgneb sellele. Näiteks:
- MITTE VAHEL - see naaseb tõeseks, kui VAHE tagastab vale ja vastupidi.
- EI LIKE - see naaseb tõeseks, kui LIKE tagastab vale ja vastupidi.
- NOT GLOB - see saab tõene, kui GLOB tagastab vale ja vastupidi.
- EI OLE - see taastub tõesena, kui OLEMINE tagastab vale ja vastupidi.
Näide:
Järgmises näites kasutame operaatorit EI OLE koos operaatoriga OLEMAS, et saada osakondade nimesid, mida tabelis Õpilased pole, mis on operaatori OLEMIS vastupidine tulemus. Niisiis, otsing toimub DepartmentId kaudu, mida osakonna tabelis pole.
Valige osakonna nimiOsakondadest AS dKUS EI OLE (SELECT DepartmentIdÜliõpilastelt AS-ideltKUS d.osakonna ID = s.osakonna ID);
Väljund :
Tagastatakse ainult osakond " Matemaatika ". Kuna osakond " Matemaatika " on ainus osakond, pole seda õpilaste tabelis.
Piiramine ja tellimine
SQLite tellimus
SQLite järjestus on tulemuse sortimine ühe või mitme avaldise järgi. Tulemuste komplekti tellimiseks peate kasutama klauslit ORDER BY järgmiselt:
- Esiteks peate määrama klausli ORDER BY.
- Päringu lõpus tuleb määrata klausel ORDER BY; selle järel saab määrata ainult klausli LIMIT.
- Andmete järjestamiseks määrake avaldis, see võib olla veeru nimi või avaldis.
- Pärast avaldist saate määrata valikulise sortimissuuna. Kas DESC, et tellida andmete kahanemist või ASC, et andmete järjestus kasvavas järjestuses. Kui te ühtegi neist ei täpsustanud, sorteeritakse andmed kasvavas järjestuses.
- Rohkem väljendeid saate määrata, kasutades üksteise vahel tähti ",".
Näide
Järgmises näites valime välja kõik õpilased, kes on järjestatud nimede järgi, kuid kahanevas järjekorras, seejärel osakonna nime järgi kasvavas järjekorras:
Valige s.StudentName, d.DepartmentNameÜliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentIdTELLIGE d.osakonna nimi ASC, s.StudentName DESC;
See annab teile:
- SQLite tellib kõigepealt üliõpilased osakonna nime järgi kasvavas järjekorras
- Seejärel kuvatakse iga osakonna nime puhul kõik selle osakonna nime all olevad õpilased nende nimede järgi kahanevas järjekorras
SQLite limiit:
Kasutades klauslit LIMIT, saate piirata oma SQL-päringu tagastatud ridade arvu. Näiteks annab LIMIT 10 teile ainult 10 rida ja ignoreerib kõiki muid ridu.
Klauslis LIMIT saate OFFSET-klausli abil valida kindla arvu ridu alates konkreetsest positsioonist. Näiteks ignoreerib " LIMIT 4 OFFSET 4 " esimest 4 rida ja tagastab 4 rida alates viiendast reast, nii et saate ridu 5,6,7 ja 8.
Pange tähele, et klausel OFFSET on valikuline. Võite selle kirjutada nagu " LIMIT 4, 4 " ja see annab teile täpsed tulemused.
Näide :
Järgmises näites tagastame päringu abil ainult 3 õpilast alates õpilase ID-st 5:
VALI * Õpilaste seast PIIRI 4,3;
See annab teile ainult kolm õpilast, alustades 5. reast. Nii saate teile read StudentId 5, 6 ja 7:
Duplikaatide eemaldamine
Kui teie SQL-päring tagastab duplikaatväärtused, saate märksõnade " DISTINCT " abil need duplikaadid eemaldada ja tagasi tuua eraldi väärtused. Pärast klahvi DISTINCT töötamist saate määrata mitu veergu.
Näide:
Järgmine päring tagastab duplikaadid "osakonna nime väärtused": siin on meil duplikaadid nimedega IT, füüsika ja kunst.
Valige d.osakonna nimiÜliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentId;
See annab teile osakonna nime duplikaadi väärtused:
Pange tähele, kuidas osakonna nimel on duplikaatväärtusi. Nüüd kasutame duplikaatide eemaldamiseks ja ainult kordumatute väärtuste saamiseks sama päringuga märksõna DISTINCT. Nagu nii:
SELECT DISTINCT d.DepartmentNameÜliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentId;
See annab teile osakonna nime veeru jaoks ainult kolm unikaalset väärtust:
Kokkuvõte
SQLite'i agregaadid on SQLite'is määratletud sisseehitatud funktsioonid, mis koondavad mitme rea mitu väärtust ühte väärtusesse.
Siin on SQLite toetatud agregaadid:
SQLite AVG ()
Tagastas kõigi x väärtuste keskmise.
Näide:
Järgmises näites saame õpilaste keskmise hinde kõigilt eksamitelt:
VALI AVG (Märgi) MÄRGIST;
See annab teile väärtuse "18,375":
Need tulemused saadakse kõigi kaubamärgi väärtuste liitmisel nende arvudega.
COUNT () - COUNT (X) või COUNT (*)
Tagastab x-väärtuse ilmumiskordade koguarvu. Ja siin on mõned võimalused, mida saate koos COUNT-ga kasutada:
- COUNT (x): loendab ainult x väärtust, kus x on veeru nimi. See ignoreerib NULL väärtusi.
- ARV (*): loendage kõigi veergude kõik read.
- COUNT (DISTINCT x): enne x-i saate määrata DISTINCT märksõna, mis saab x-i eri väärtuste arvu.
Näide
Järgmises näites saame COUNT (DepartmentId), COUNT (*) ja COUNT (DISTINCT DepartmentId) osakondade koguarvu ja nende erinevuse:
Valige COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) õpilastelt;
See annab teile:
Järgmiselt:
- COUNT (DepartmentId) annab teile kogu osakonna ID loendi ja ignoreerib nullväärtusi.
- COUNT (DISTINCT DepartmentId) annab teile DepartmentId erinevad väärtused, mis on ainult 3. Millised on osakonna nime kolm erinevat väärtust. Pange tähele, et üliõpilase nimes on osakonna nime 8 väärtust. Kuid ainult kolm erinevat väärtust, mis on matemaatika, IT ja füüsika.
- COUNT (*) loendab õpilaste tabeli ridade arvu, mis on 10 rida 10 õpilase jaoks.
GROUP_CONCAT () - GROUP_CONCAT (X) või GROUP_CONCAT (X, Y)
Grupeerimisfunktsioon GROUP_CONCAT koondab väärtuste mitmekordsed väärtused komaks, et need eraldada. Sellel on järgmised võimalused:
- GROUP_CONCAT (X): See liidab kogu x väärtuse üheks stringiks, kusjuures väärtuste vahel eraldajana kasutatakse koma "". NULL väärtusi eiratakse.
- GROUP_CONCAT (X, Y): see ühendab x väärtused üheks stringiks, kusjuures y väärtust kasutatakse eraldajana iga väärtuse vahel vaikeseparaatori ',' asemel. Samuti eiratakse NULL-väärtusi.
- GROUP_CONCAT (DISTINCT X): see ühendab kõik x-i erinevad väärtused üheks stringiks komadega "," mida kasutatakse väärtuste vahel eraldajana. NULL väärtusi eiratakse.
GROUP_CONCAT (osakonna nimi) näide
Järgmine päring koondab kõik osakonna nime väärtused üliõpilastelt ja osakondade tabelist eraldi eraldatud stringikomaga. Seega väärtuste loendi tagastamise asemel on igal real üks väärtus. See tagastab ühel real ainult ühe väärtuse, kusjuures kõik väärtused eraldatakse komaga:
SELECT GROUP_CONCAT (d.osakonna nimi)Üliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentId;
See annab teile:
See annab teile loetelu 8 osakonna nimeväärtusest, mis on ühendatud ühte stringi komaga eraldatud.
GROUP_CONCAT (DISTINCT osakonna nimi) näide
Järgmine päring liidab osakonna nime erinevad väärtused üliõpilaste ja osakondade tabelist eraldatuna üheks koma komaks:
VALI GROUP_CONCAT (DISTINCT d.osakonna nimi)Üliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentId;
See annab teile:
Pange tähele, kuidas tulemus erineb eelmisest tulemusest; tagastati ainult kolm väärtust, mis on eri osakondade nimed, ja duplikaatide väärtused eemaldati.
GROUP_CONCAT (osakonna nimi, '&') näide
Järgmine päring liidab kõik osakonna nime veeru väärtused õpilaste ja osakondade tabelist üheks stringiks, kuid eraldajana koma asemel märgiga "&":
SELECT GROUP_CONCAT (d.osakonna nimi, & &)Üliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentId;
See annab teile:
Pange tähele, kuidas väärtuste eraldamiseks kasutatakse märki "&" vaikemärgi "," asemel.
SQLite MAX () ja MIN ()
MAX (X) tagastab teile X-väärtustest kõrgeima väärtuse. MAX tagastab väärtuse NULL, kui kõik x väärtused on nullid. Kusjuures MIN (X) tagastab teile X-väärtustest väikseima väärtuse. MIN tagastab väärtuse NULL, kui kõik X väärtused on nullid.
Näide
Järgmises päringus kasutame funktsioone MIN ja MAX, et saada tabelist " Märgid " kõrgeim ja madalaim märk :
MÄRKIDEST VALI MAKS (MÄRK), MIN (MÄRK);
See annab teile:
SQLite SUM (x), kokku (x)
Mõlemad tagastavad kõigi x väärtuste summa. Kuid need erinevad järgmiselt:
- SUM tagastatakse null, kui kõik väärtused on nullid, kuid summa tagastab 0.
- TOTAL tagastab alati ujukoma väärtused. SUM tagastab täisarvu, kui kõik x väärtused on täisarv. Kui aga väärtused pole täisarv, tagastab see ujukoma väärtuse.
Näide
Järgmises päringus kasutame kõigi märkide summa tabelites " Märgid " summat ja summat :
VALI SUMM (Märgi), KOKKU (Märgi) MÄRGIST;
See annab teile:
Nagu näete, tagastab TOTAL alati ujukoma. Kuid SUM tagastab täisarvu, kuna veerus "Märgi" olevad väärtused võivad olla täisarvud.
Näite SUM ja TOTAL erinevus:
Järgmises päringus näitame erinevust SUM ja TOTAL vahel, kui nad saavad NULL-väärtuste SUM-i:
VALI SUMM (märk), KOKKU (märgista) MÄRGIST, KUS TestId = 4;
See annab teile:
Pange tähele, et TestId = 4 jaoks pole märke, seega on selle testi nullväärtused. SUM tagastab tühja väärtusena nullväärtuse, TOTAL aga 0.
Grupp BY
Klauslit GROUP BY kasutatakse ühe või mitme veeru määramiseks, mida kasutatakse ridade rühmadesse rühmitamiseks. Samade väärtustega read koondatakse (paigutatakse) rühmadesse.
Mis tahes muu veeru puhul, mis ei kuulu veergude kaupa rühma, saate selle jaoks kasutada koondfunktsiooni.
Näide:
Järgmine päring annab teile igas osakonnas viibivate õpilaste koguarvu.
Valige d.osakonna nimi, COUNT (s.StudentId) AS StudentsCountÜliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentIdRÜHM d. Osakonna nimi;
See annab teile:
Klausel GROUPBY DepartmentName koondab kõik õpilased rühmadesse iga osakonna nime jaoks. Iga "osakonna" rühma puhul arvestab see õpilasi.
VÕTTA klausel
Kui soovite klausli GROUP BY tagastatud rühmi filtreerida, saate grupi BY järel määrata avaldise "HAVING". Nende rühmade filtreerimiseks kasutatakse väljendit.
Näide
Järgmises päringus valime need osakonnad, kus on ainult kaks õpilast:
Valige d.osakonna nimi, COUNT (s.StudentId) AS StudentsCountÜliõpilastelt AS-ideltINNER JOIN Osakonnad AS d ON s.DepartmentId = d.DepartmentIdRÜHM d. Osakonna nimiKOGU ARV (s.StudentId) = 2;
See annab teile:
Klausel HAVING COUNT (S.StudentId) = 2 filtreerib tagastatud rühmad ja tagastab ainult need rühmad, kus on täpselt kaks õpilast. Meie puhul on kunstiosakonnas 2 üliõpilast, nii et see kuvatakse väljundis.
SQLite päring ja alampäring
Mis tahes päringu sees saate kasutada mõnda muud päringut kas SELECT, INSERT, DELETE, UPDATE või mõne muu päringu sees.
Seda pesastatud päringut nimetatakse alampäringuks. Näeme nüüd SELECT-klauslis mõningaid näiteid alampäringute kasutamisest. Andmete muutmise õpetuses näeme aga, kuidas saame kasutada päringuid INSERT, DELETE ja UPDATE alampäringuid.
Alampäringu kasutamine klausli FROM näites
Järgmisesse päringusse lisame klausli FROM alampäringu:
VALIs. Õpilase nimi, t.MarkÜliõpilastelt AS-ideltSISSE LIITU(VALI StudentId, MarkTestidest AS tSISEMINE LIITUMIS Märkused AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
Päring:
VALI StudentId, MarkTestidest AS tSISEMINE LIITUMIS Märkused AS m ON t.TestId = m.TestId
Ülaltoodud päringut nimetatakse siin alampäringuks, kuna see on sisestatud klausli FROM sisse. Pange tähele, et me panime sellele varjunime "t", et saaksime viidata päringus sellest tagastatud veergudele.
See päring annab teile:
Nii et meie puhul
- s.StudentName valitakse põhipäringu hulgast, mis annab õpilaste nimed ja
- t.Mark on valitud alampäringu hulgast; mis annab nende õpilaste poolt hinnatud hinded
Alampäringu kasutamine WHERE klauslinäites
Järgmisesse päringusse lisame WHERE klauslisse alampäringu:
Valige osakonna nimiOsakondadest AS dKUS EI OLE (SELECT DepartmentIdÜliõpilastelt AS-ideltKUS d.osakonna ID = s.osakonna ID);
Päring:
Valige osakonna IDÜliõpilastelt AS-ideltKUS d.osakonna ID = s.osakonna ID
Ülaltoodud päringut nimetatakse siin alampäringuks, kuna see on pesastatud WHERE klauslis. Alampäring tagastab DepartmentId väärtused, mida operaator kasutab EI OLE.
See päring annab teile:
Ülaltoodud päringus oleme valinud osakonna, kuhu pole ühtegi üliõpilast registreeritud. Mis on siin matemaatika osakond.
Määra toimingud - UNION, Intersect
SQLite toetab järgmisi SET-toiminguid:
LIIT JA LIIT KÕIK
See ühendab ühe või mitu tulemuste komplekti (ridade rühma), mis on tagastatud mitmest SELECT-lausest, üheks tulemuste komplektiks.
UNION tagastab erinevad väärtused. Kuid UNION ALL ei hõlma duplikaate ega hõlma neid.
Pange tähele, et veeru nimi on veerus nimi, mis on määratud esimeses SELECT-lauses.
LIIDU näide
Järgmises näites saame DepartmentId loendi üliõpilaste tabelist ja DepartmentId loendi osakonnade tabelist samas veerus:
SELECT DepartmentId AS DepartmentIdUnioned FROM õpilasteltLIITVALI osakonna ID osakondadest;
See annab teile:
Päring tagastab ainult 5 rida, mis on erinevad osakonna ID väärtused. Pange tähele esimest väärtust, mis on nullväärtus.
SQLite UNION ALL näide
Järgmises näites saame DepartmentId loendi üliõpilaste tabelist ja DepartmentId loendi osakonnade tabelist samas veerus:
SELECT DepartmentId AS DepartmentIdUnioned FROM õpilasteltLIIT KÕIKVALI osakonna ID osakondadest;
See annab teile:
Päring tagastab 14 rida, 10 rida õpilaste tabelist ja 4 rida osakondade tabelist. Pange tähele, et tagastatud väärtustes on duplikaate. Pange tähele ka seda, et veeru nimi oli see, mis oli määratud esimeses SELECT-lauses.
Vaatame nüüd, kuidas UNION annab kõik erinevad tulemused, kui asendame UNION ALL liiduga:
SQLite INTERSECT
Tagastab väärtused olemas nii kombineeritud tulemuste komplektis. Ühes kombineeritud tulemuste komplektis olevaid väärtusi eiratakse.
Näide
Järgmises päringus valime DepartmentId väärtused, mis eksisteerivad veerus DepartmentId nii tabelites Õpilased kui ka Osakonnad:
VALI üliõpilastest osakonna IDRistuvadVALI osakonna ID osakondadest;
See annab teile:
Päring tagastab ainult kolm väärtust 1, 2 ja 3. Mis on väärtused, mis eksisteerivad mõlemas tabelis.
Väärtusi null ja 4 ei kaasatud, kuna nullväärtus eksisteerib ainult õpilaste tabelis, mitte osakondade tabelis. Ja väärtus 4 eksisteerib osakondade tabelis, mitte õpilaste tabelis.
Seetõttu ignoreeriti nii väärtusi NULL kui ka 4 ja neid ei kaasatud tagastatud väärtustesse.
VÄLJA ARVATUD
Oletame, et kui teil on kaks ridade loendit, nimekiri1 ja loend2 ning soovite ridu ainult loendist1, mida loendis2 pole, võite kasutada klauslit "VÄLJA". Klausel EXCEPT võrdleb kahte loendit ja tagastab need read, mis eksisteerivad loendis1 ja mida loendis2 pole.
Näide
Järgmises päringus valime osakondade tabelis olevad DepartmentId väärtused, mida õpilaste tabelis pole:
VALI osakonna ID osakondadestVÄLJA ARVATUDVALI Osakonna ID õpilastelt;
See annab teile:
Päring tagastab ainult väärtuse 4. Mis on ainus väärtus, mis eksisteerib osakondade tabelis ja mida õpilaste tabelis pole.
NULL käsitsemine
Väärtus " NULL " on SQLite eriline väärtus. Seda kasutatakse tundmatu või puuduva väärtuse tähistamiseks. Pange tähele, et nullväärtus erineb täielikult väärtusest " 0 " või tühjast "". Kuna 0 ja tühi väärtus on teada, on nullväärtus teadmata.
NULL-väärtused nõuavad SQLite'is spetsiaalset käitlemist, näeme nüüd, kuidas NULL-i väärtusi käsitseda.
Otsige NULL väärtusi
Nullväärtuste otsimiseks ei saa kasutada tavalist võrdsusoperaatorit (=). Näiteks otsib järgmine päring õpilasi, millel on null DepartmentId väärtus:
VALIGE * ÜLIõpilaste hulgast WHERE DepartmentId = NULL;
See päring ei anna tulemusi:
Kuna NULL-väärtus ei võrdu ühegi muu väärtusega, mis sisaldas nullväärtust, ei andnud see tulemust.
- Päringu toimimiseks peate nullväärtuste otsimiseks kasutama operaatorit "IS NULL" järgmiselt:
VALIGE * ÜLIõpilaste hulgast, kus osakond on tühi;
See annab teile:
Päring tagastab need õpilased, kellel on null DepartmentId väärtus.
- Kui soovite saada väärtusi, mis pole nullid, peate kasutama sellist operaatorit " IS NOT NULL ":
Valige * üliõpilaste hulgast, kus osakond EI OLE NULL;
See annab teile:
Päring tagastab need õpilased, kellel pole NULL DepartmentId väärtust.
Tingimuslikud tulemused
Kui teil on väärtuste loend ja soovite mõne tingimuse põhjal neist mõne valida. Selleks peaks selle konkreetse väärtuse tingimus valimiseks olema tõene.
Avaldis CASE hindab seda tingimuste loendit kõigi väärtuste jaoks. Kui tingimus on tõene, tagastab see selle väärtuse.
Näiteks kui teil on veerg „Hinne” ja soovite hinne väärtuse põhjal valida tekstiväärtuse järgmiselt:
- "Suurepärane", kui hinne on kõrgem kui 85.
- "Väga hea", kui hinne jääb vahemikku 70–85.
- "Hea", kui hinne jääb vahemikku 60–70.
Siis saate selleks kasutada väljendit CASE.
Seda saab kasutada mõne klahvi SELECT loogika määratlemiseks, et saaksite valida teatud tulemused sõltuvalt teatud tingimustest, näiteks if lause.
Operaatori CASE saab erinevate süntaksitega määratleda järgmiselt:
- Võite kasutada erinevaid tingimusi:
JUHTUMMILLAL tingimus1 SIIS tulemus1MILLAL tingimus2 SIIS tulemus2MILLAL tingimus3 SIIS tulemus3 ... MUU tulemusnLÕPP
- Või võite kasutada ainult ühte avaldist ja valida erinevate võimalike väärtuste vahel:
CASE väljendMILLAL väärtus1 SIIS tulemus1MILLAL väärtus2 SIIS tulemus2MILLAL väärtus3, siis tulemus3 ... VEEL taaskehtestatudLÕPP
Pange tähele, et ELSE klausel on valikuline.
Näide
Järgmises näites kasutame tabeli Õpilased osakonna ID veerus NULL väärtusega CASE- väljendit teksti "Osakonda pole" kuvamiseks järgmiselt:
VALIÕpilase nimi,JUHTUMKUI osakond on TÄIS, SIIS "osakonda pole"ELSE osakondEND AS osakondIdÕpilastelt;
- Operaator CASE kontrollib DepartmentId väärtust, kas see on null või mitte.
- Kui see on NULL-väärtus, valib see DepartmentId-väärtuse asemel sõnasõnalise väärtuse 'No Department'.
- Kui väärtus pole null, valib see veeru DepartmentId väärtuse.
See annab teile väljundi, nagu allpool näidatud:
Levinud tabeli väljend
Tavalised tabeliväljendid (CTE) on alampäringud, mis on määratletud SQL-i lauses antud nimega.
Sellel on alampäringute ees eelis, kuna see on määratletud SQL-i lausetest ja muudab päringute lugemise, hooldamise ja mõistmise hõlpsamaks.
Ühise tabeliväljendi saab määratleda, asetades WITH-lause SELECT-lausete ette järgmiselt:
CTEnimegaAS(SELECT-lause)Valige, värskendage, sisestage või värskendage avaldust siin CTE-st
" CTEname " on mis tahes nimi, mille saate CTE jaoks anda, saate seda hiljem sellele viitamiseks kasutada. Pange tähele, et saate CTE-de jaoks määratleda lause SELECT, UPDATE, INSERT või DELETE
Vaatame nüüd CLA kasutamise näidet SELECT-klauslis.
Näide
Järgmises näites määratleme CTE lausest SELECT ja seejärel kasutame seda hiljem teise päringu jaoks:
Koos kõigi osakondadegaAS(Valige osakonna ID, osakonna nimiOsakondadest)VALIs.StudentId,s.õpilase nimi,a. Osakonna nimiÜliõpilastelt AS-ideltINNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
Selles päringus määratlesime CTE ja panime sellele nime " AllDepartments ". See CTE määratleti SELECT päringu põhjal:
Valige osakonna ID, osakonna nimiOsakondadest
Pärast CTE määratlemist kasutasime seda selle järel päringus SELECT.
Pange tähele, et ühise tabeli avaldised ei mõjuta päringu väljundit. See on viis loogilise vaate või alampäringu määratlemiseks, et neid samas päringus uuesti kasutada. Levinud tabeliväljendid on nagu muutuja, mille deklareerite ja kasutate seda uuesti alampäringuna. Ainult SELECT-lause mõjutab päringu väljundit.
See päring annab teile:
Täpsemad päringud
Täpsemad päringud on need päringud, mis sisaldavad keerukaid liitumisi, alampäringuid ja mõnda agregaati. Järgmises jaotises näeme täpsema päringu näidet:
Kust me saame,
- Osakonna nimed koos kõigi osakonna üliõpilastega
- Õpilaste nimi eraldatakse komaga ja
- Osakonna näitamine, kus on vähemalt kolm õpilast
VALId. Osakonna nimi,COUNT (s.StudentId) õpilaste arv,GROUP_CONCAT (StudentName) AS õpilasedOsakondadest AS dINNER JOIN Üliõpilased, kui s ON s.DepartmentId = d.DepartmentIdGRUPP d.osakonna nimiARVUTAB (s.StudentId)> = 3;
Lisasime JOIN-klausli, et saada osakonna nimi tabelist Osakonnad. Pärast seda lisasime klausli GROUP BY kahe koondfunktsiooniga:
- "COUNT" iga osakonna rühma õpilaste lugemiseks.
- GROUP_CONCAT, et liita iga rühma õpilased ühes stringis eraldatud komadega.
- Pärast GROUP BY kasutasime osakondade filtreerimiseks klahvi HAVING ja valisime ainult need osakonnad, kus on vähemalt 3 õpilast.
Tulemus on järgmine:
Kokkuvõte:
See oli sissejuhatus SQLite päringute kirjutamiseks ja andmebaasi päringute tegemise põhitõdedeks ning tagastatud andmete filtreerimiseks. Nüüd saate kirjutada oma SQLite-päringud.