SQLite Päring: valige, Kus, LIMIT, NIHKE, loendamist, rühmitamist

SQL-päringute kirjutamiseks an SQLite 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 klauslid.

Andmete lugemine valikuga

SELECT-klausel on põhilause, mida kasutate päringu tegemiseks SQLite andmebaasi. SELECT-klauslis määrate, mida valida. Kuid enne valikuklauslit vaatame, kust saame FROM-klausli abil andmeid valida.

FROM-klauslit kasutatakse selleks, et määrata, kus soovite andmeid valida. Klauslis from saate määrata ühe või mitu tabelit või alampäringut, millest andmed valida, nagu näeme hiljem õpetustes.

Pange tähele, et kõigi järgmiste näidete puhul peate käivitama faili sqlite3.exe ja avama ühenduse näidisandmebaasiga voolavana:

Step 1) Selles sammus

1. Avage Minu arvuti ja liikuge järgmisse kataloogi "C:\sqlite"Ja
2. Seejärel avage "sqlite3.exe"

Step 2) Ava andmebaas "TutorialsSampleDB.db” järgmise käsuga:

Nüüd olete valmis käivitama andmebaasis mis tahes tüüpi päringuid.

Klauslis SELECT saate valida mitte ainult veeru nime, vaid teil on palju muid valikuid, mida valida. järgmiselt:

SELECT *

See käsk valib kõik veerud kõigist viidatud tabelitest (või alampäringutest) klauslis FROM. Näiteks:

SELECT * 
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

See valib kõik veerud nii õpilaste kui ka osakondade tabelite hulgast:

VALI tabelinimi.*

See valib kõik veerud ainult tabelist "tabelinimi". Näiteks:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

See valib kõik veerud ainult õpilaste tabelist:

Sõnasõnaline väärtus

Literaalne väärtus on konstantne väärtus, mille saab määrata valikulauses. Literaalseid väärtusi saate kasutada tavaliselt samamoodi, nagu kasutate veergude nimesid klauslis SELECT. Need literaalsed väärtused kuvatakse iga SQL-päringu tagastatud ridade rea jaoks.

Siin on mõned näited erinevatest literaalväärtustest, mida saate valida.

• Numbriline kirjasõna – numbrid mis tahes vormingus, näiteks 1, 2.55 jne.
• Stringi literaalid – mis tahes string "USA", "see on näidistekst" jne.
• NULL – NULL väärtus.
• Current_TIME – see annab teile praeguse kellaaja.
• CURRENT_DATE – see annab teile praeguse kuupäeva.

See võib olla kasulik mõnes olukorras, kus peate valima kõigi tagastatud ridade jaoks konstantse väärtuse. Näiteks kui soovite valida õpilaste tabelist kõik õpilased uue veeruga, mille nimi on riik ja mis sisaldab väärtust "USA", saate teha järgmist.

SELECT *, 'USA' AS Country FROM Students;

See annab teile kõik õpilaste veerud ja uue veeru "Riik", nagu see:

Pange tähele, et see uus veerg Riik ei ole tegelikult tabelisse lisatud uus veerg. See on virtuaalne veerg, mis luuakse päringus tulemuste kuvamiseks ja seda tabelisse ei looda.

Nimed ja varjunimed

Alias ​​on veeru uus nimi, mis võimaldab valida uue nimega veeru. Veergude varjunimed määratakse märksõna "AS" abil.

Näiteks kui soovite valida veeru StudentName, mis tagastatakse veeruga "Student Name", mitte "StudentName", saate anda sellele järgmise varjunime:

SELECT StudentName AS 'Student Name' FROM Students;

See annab teile õpilaste nimed koos nimega „Õpilase nimi” mitte „Õpilase nimi” järgmiselt:

Pange tähele, et veeru nimi on endiselt "Õpilase Nimi“; veerg StudentName on endiselt sama, see ei muutu aliase järgi.

Alias ​​ei muuda veeru nime; see muudab lihtsalt SELECT-klauslis kuvatavat nime.

Samuti pange tähele, et märksõna "AS" on valikuline, saate pseudonüümi nime panna ilma selleta, umbes nii:

SELECT StudentName 'Student Name' FROM Students;

Ja see annab teile täpselt sama väljundi kui eelmine päring:

Samuti saate anda tabelitele varjunimesid, mitte ainult veerge. Sama märksõnaga “AS”. Näiteks saate seda teha.

SELECT s.* FROM Students AS s;

See annab teile kõik tabeli Õpilased veerud:

See võib olla väga kasulik, kui liitute rohkem kui ühe lauaga; selle asemel, et korrata päringus tabeli täisnime, võite anda igale tabelile lühikese varjunime. Näiteks järgmises päringus:

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

See päring valib iga üliõpilase nime tabelist „Õpilased” ja osakonna nime tabelist „Osakonnad”.

Samas saab sama päringu kirjutada järgmiselt:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

• Andsime õpilaste tabelile varjunime "s" ja osakondade tabelile varjunime "d".
• Seejärel kasutasime tabeli täisnime asemel nende varjunimesid, et neile viidata.
• INNER JOIN ühendab kaks või enam tabelit tingimuse abil. Meie näites ühendasime tabeli Õpilased osakonna tabeliga, mille veerg on osakonna ID. Samuti on jaotises "INNER JOIN" üksikasjalik selgitusSQLite Liitub” õpetus.

See annab teile täpse väljundi nagu eelmine päring:

KUS

Kui kirjutate SQL-päringuid ainult SELECT-klausli abil koos FROM-klausliga, nagu nägime eelmises jaotises, saate kõik tabelite read. Kui soovite aga tagastatud andmeid filtreerida, peate lisama klausli “WHERE”.

WHERE-klauslit kasutatakse poolt tagastatud tulemuste komplekti filtreerimiseks SQL päring. WHERE-klausel töötab järgmiselt:

• WHERE-klauslis saate määrata "avaldise".
• Seda avaldist hinnatakse iga FROM-klauslis määratud tabeli(te)st tagastatud rea kohta.
• Avaldist hinnatakse Boole'i ​​avaldisena, mille tulemuseks on kas tõene, väär või null.
• Seejärel tagastatakse ainult need read, mille puhul avaldist hinnati tõese väärtusega, ning vale- või nulltulemusega ridu ignoreeritakse ja neid ei kaasata tulemuste komplekti.
• Tulemuste komplekti filtreerimiseks WHERE-klausli abil peate kasutama avaldisi ja operaatoreid.

Sisseehitatud operaatorite loend SQLite ja kuidas neid kasutada

Järgmises jaotises selgitame, kuidas saate avaldise ja operaatorite abil filtreerida.

Väljend on üks või mitu literaalset väärtust või veergu, mis on üksteisega ühendatud operaatoriga.

Pange tähele, et saate kasutada väljendeid nii SELECT- kui ka WHERE-klauslis.

Järgmistes näidetes proovime avaldisi ja operaatoreid nii valiku- kui ka WHERE-lauses. Et näidata teile, kuidas nad toimivad.

On erinevat tüüpi avaldisi ja operaatoreid, mida saate määrata järgmiselt.

SQLite konkatenatsioonioperaator “||”

Seda operaatorit kasutatakse ühe või mitme literaalse väärtuse või veeru omavaheliseks ühendamiseks. See annab ühe tulemuste stringi kõigist ühendatud literaalväärtustest või veergudest. Näiteks:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

See ühendab uue varjunime "StudentIdWithName"

• Sõnasõnaline stringi väärtus "ID nimega: "
• väärtusega "Õpilase ID” veerg ja
• väärtusega alates "Õpilase Nimi” veerus

SQLite CAST-i operaator:

CAST-operaatorit kasutatakse väärtuse teisendamiseks andmetüübist teise andmetüüp.

Näiteks kui teil on stringiväärtusena salvestatud arvväärtus, nagu see " "12.5" " ja soovite teisendada selle arvväärtuseks, saate kasutada operaatorit CAST, et seda teha järgmiseltCAST ('12.5' PÄRIS)“. Või kui teil on kümnendväärtus, näiteks 12.5, ja teil on vaja hankida ainult täisarvuline osa, saate selle üle kanda täisarvuks, näiteks "CAST(12.5 AS INTEGER)".

Näide

Järgmises käsus proovime teisendada erinevad väärtused muudeks andmetüüpideks:

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 TÄISARV) – väärtus 12.5 on kümnendväärtus, see teisendatakse täisarvuks. Kümnendosa kärbitakse ja sellest saab 12.

SQLite Aritmeetika Operators:

Võtke kaks või enam arvsõnalist väärtust või numbrilist veergu ja tagastage üks arvväärtus. Toetatud aritmeetilised operaatorid SQLite See on:

Lisa"+” – anna kahe operandi summa. Lahutamine "-” – lahutab kaks operandi ja tulemuseks on erinevus. Korrutamine"*” – kahe operandi korrutis. Meeldetuletus (moodul) "%” – annab jäägi, mis saadakse ühe operandi jagamisel teise operandiga. Jaoskond "/” – tagastab jagatised, mis saadakse vasaku operandi jagamisel parempoolse operandiga.

Näide:

Järgmises näites proovime viit aritmeetilist operaatorit, millel on literaalsed arvväärtused

vali klausel:

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

See annab teile:

Pange tähele, kuidas kasutasime siin lauset SELECT ilma FROM-klauslita. Ja see on lubatud SQLite seni, kuni valime literaalsed väärtused.

SQLite Võrdlusoperaatorid

Võrrelge kahte operandi omavahel ja tagastage tõene või väär järgmiselt:

"<” – tagastab tõene, kui vasak operandi väärtus on väiksem kui parem. "<=” – tagastab tõene, kui vasak operandi väärtus on väiksem või võrdne parempoolse operandiga. ">” – tagastab tõene, kui vasak operand on suurem kui parem. ">=” – tagastab tõene, kui vasak operand on suurem või võrdne parempoolse operandiga. "="Ja"==” – tagastab tõene, kui kaks operandi on võrdsed. Pange tähele, et mõlemad operaatorid on samad ja nende vahel pole vahet. "!="Ja"<>” – tagastab tõene, kui kaks operandi ei ole võrdsed. Pange tähele, et mõlemad operaatorid on samad ja nende vahel pole vahet.

Pange tähele, SQLite väljendab tõelist väärtust 1-ga ja vale väärtust 0-ga.

Näide:

SELECT 
  10<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 Mustri sobitamise operaatorid

"SAMASUGUNE” – kasutatakse mustrite sobitamiseks. Kasutades "nagu“, saate otsida väärtusi, mis vastavad metamärgiga määratud mustrile.

Vasakpoolne operandiks võib olla kas stringi literaalväärtus või stringi veerg. Mustri saab määrata järgmiselt:

• Sisaldab mustrit. Näiteks Õpilasenimi MEELDIB '%a%' – see otsib õpilaste nimesid, mis sisaldavad tähte "a" mis tahes positsioonis veerus StudentName.
• Alustab mustriga. Näiteks "Õpilasenimi LIKE 'a%'” – otsi õpilaste nimesid, mis algavad tähega “a”.
• Lõpeb mustriga. Näiteks "Õpilasenimi LIKE '%a'” – Otsige õpilaste nimesid, mis lõpevad tähega "a".
• Mis tahes üksiku tähemärgi sobitamine stringis, kasutades allkriipsutähte “_”. Näiteks "Õpilasenimi LIKE 'J___'” – otsige õpilaste nimesid, mis on 4 tähemärgi pikkused. See peab algama J-tähega ja pärast J-tähte võib sellel olla veel kolm märki.

Mustri sobitamise näited:

1. Hankige õpilastele j-tähega algavad nimed:

   SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

   Tulemus:

   

2. Hankige õpilaste nimed y-tähega:

   SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

   Tulemus:

   

3. Hankige õpilaste nimed, mis sisaldavad n-tähte:

   SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

   Tulemus:

   

"GLOB" – on samaväärne operaatoriga LIKE, kuid erinevalt LIKE-operaatorist on GLOB tõstutundlik. Näiteks annavad järgmised kaks käsku erinevad tulemused:

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

See annab teile:

• Esimene lause tagastab väärtuse 0 (false), kuna operaator GLOB on tõstutundlik, seega ei võrdu 'j' väärtusega J. Teine väide tagastab aga 1 (tõene), kuna LIKE-operaator ei ole tõstutundlik, seega on 'j' võrdne 'J'-ga.

Teised operaatorid:

SQLite JA

Loogiline operaator, mis ühendab ühe või mitu avaldist. See tagastab tõene ainult siis, kui kõik avaldised annavad "tõene" väärtuse. Kuid see tagastab vale ainult siis, kui kõik avaldised annavad väärtuse "false".

Näide:

Järgmine päring otsib õpilasi, kelle StudentId on > 5 ja StudentName algab tähega N, tagastatud õpilased peavad vastama kahele tingimusele:

SELECT * 
FROM Students 
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

Ülaltoodud ekraanipildi väljundina annab see teile ainult "Nancy". Nancy on ainus õpilane, kes vastab mõlemale tingimusele.

SQLite OR

Loogiline operaator, mis ühendab ühe või mitu avaldist, nii et kui üks kombineeritud operaatoritest annab tõene, tagastab see tõene. Kui aga kõik avaldised annavad vale, tagastab see vale.

Näide:

Järgmine päring otsib õpilasi, kelle StudentId > 5 või StudentName algab tähega N, tagastatud õpilased peavad vastama vähemalt ühele tingimusest:

SELECT * 
FROM Students 
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

See annab teile:

Ülaltoodud ekraanipildi väljundina annab see teile õpilase nime, kelle nimes on täht "n" ja õpilase ID, mille väärtus on> 5.

Nagu näete, on tulemus erinev operaatori AND päringust.

SQLite VAHEL

BETWEEN kasutatakse nende väärtuste valimiseks, mis jäävad kahe väärtuse vahemikku. Näiteks "X Y JA Z VAHEL” tagastab tõese (1), kui väärtus X on kahe väärtuse Y ja Z vahel. Vastasel juhul tagastab see vale (0). “X Y JA Z VAHEL" on samaväärne "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äidispäringus kirjutame päringu, et saada õpilased, kelle ID väärtus on vahemikus 5 kuni 8:

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

See annab ainult õpilastele, kelle ID-d on 5, 6, 7 ja 8:

SQLite IN

Võtab ühe operandi ja operandide loendi. See tagastab tõene, kui esimene operandi väärtus on võrdne loendis oleva ühe operandi väärtusega. IN operaator tagastab tõene (1), kui operandide loend sisaldab oma väärtuste piires esimest operandi väärtust. Vastasel juhul tagastab see vale (0).

Nagu nii: "veerg IN(x, y, z)“. See on samaväärne " (col=x) või (col=y) või (col=z) ".

Näide:

Järgmine päring valib ainult 2, 4, 6, 8 ID-ga õpilased:

SELECT * 
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

Nagu nii:

Eelmine päring annab täpse tulemuse järgmise päringuna, kuna need on samaväärsed:

SELECT * 
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Mõlemad päringud annavad täpse väljundi. Kuid nende kahe päringu erinevus seisneb selles, et esimese päringu puhul kasutasime operaatorit "IN". Teises päringus kasutasime mitut "OR" operaatorit.

IN-operaator on samaväärne mitme VÕI-operaatori kasutamisega. "WHERE StudentId IN(2, 4, 6, 8)"on samaväärne" WHERE (StudentId = 2) VÕI (StudentId = 4) OR (StudentId = 6) VÕI (StudentId = 8);"

Nagu nii:

SQLite EI SISSE

Operaand “NOT IN” on vastand operaatorile IN. Kuid sama süntaksiga; selleks kulub üks operand ja operandide loend. See tagastab tõene, kui esimene operandi väärtus ei võrdu ühe loendis oleva operandi väärtusega. st tagastab tõene (0), kui operandide loend ei sisalda esimest operandi. Nagu see: "veerg EI IN(x, y, z)“. See on samaväärne "(veerg<>x) JA (veerg<>y) JA (veerg<>z)".

Näide:

Järgmine päring valib õpilased, kelle ID-d ei võrdu ühega neist ID-dest 2, 4, 6, 8:

SELECT * 
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Niiviisi

Eelmise päringu korral anname täpse tulemuse järgmise päringuna, kuna need on samaväärsed:

SELECT * 
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Nagu nii:

Ülaltoodud ekraanipildil

Kasutasime õpilaste loendi saamiseks mitut mittevõrdset operaatorit „<>”, mis ei võrdu järgmiste ID-dega 2, 4, 6 ega 8. See päring tagastab kõik teised õpilased peale nende ID-de loendi.

SQLite OLEMAS

Operaatorid EXISTS ei võta ühtegi operandi; pärast seda kulub ainult SELECT-klausel. Operaator EXISTS tagastab tõese (1), kui SELECT-klauslist tagastatakse mõni rida, ja tagastab vale (0), kui SELECT-klauslist pole ühtegi rida tagastatud.

Näide:

Järgmises näites valime osakonna nime, kui osakonna ID on õpilaste tabelis olemas:

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

See annab teile:

Ainult kolm osakonda "IT, füüsika ja kunst” tagastatakse. Ja osakonna nimi "Math” ei tagastata, kuna selles osakonnas ei ole ühtegi üliõpilast, mistõttu osakonna ID puudub õpilaste tabelis. Sellepärast ignoreeris operaator EXISTSMath” osakond.

SQLite EI

Revmäärab sellele järgneva eelneva operaatori tulemuse. Näiteks:

• NOT BETWEEN – see tagastab tõene, kui BETWEEN tagastab vale ja vastupidi.
• NOT LIKE – see tagastab tõene, kui LIKE tagastab vale ja vastupidi.
• NOT GLOB – see tagastab tõene, kui GLOB tagastab vale ja vastupidi.
• NOT EXISTS – tagastab väärtuse tõene, kui EXISTS tagastab vale ja vastupidi.

Näide:

Järgmises näites kasutame operaatorit EI koos operaatoriga EXISTS, et saada osakondade nimed, mida tabelis Õpilased ei eksisteeri, mis on operaatori EXISTS vastupidine tulemus. Seega tehakse otsing osakonna ID kaudu, mida osakonna tabelis pole.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId 
                  FROM Students AS s 
                  WHERE d.DepartmentId = s.DepartmentId);

Väljund:

Ainult osakond "Math ” tagastatakse. Kuna "Math” osakond on ainuke osakond, mida õpilaste tabelis pole.

Piiramine ja tellimine

SQLite järjekord

SQLite Järjestus on tulemuse sorteerimine ühe või mitme avaldise järgi. Tulemuste komplekti tellimiseks peate kasutama klauslit ORDER BY järgmiselt:

• Esiteks peate määrama klausli ORDER BY.
• ORDER BY klausel tuleb täpsustada päringu lõpus; pärast seda saab määrata ainult klausli LIMIT.
• Määrake avaldis andmete järjestamiseks, see avaldis võib olla veeru nimi või avaldis.
• Pärast avaldist saate määrata valikulise sortimissuuna. Kas DESC, et järjestada andmed kahanevalt, või ASC, et järjestada andmed kasvavalt. Kui te ühtegi neist ei määranud, sorteeritakse andmed kasvavas järjekorras.
• Saate määrata rohkem väljendeid, kasutades üksteise vahel olevat ",".

Näide

Järgmises näites valime kõik üliõpilased järjestatud nimede järgi, kuid kahanevas järjekorras, seejärel osakonna nime järgi kasvavas järjekorras:

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
ORDER BY d.DepartmentName ASC , s.StudentName DESC;

See annab teile:

• SQLite järjestab kõigepealt kõik üliõpilased osakonna nime järgi kasvavas järjekorras
• Seejärel kuvatakse iga osakonna nime juures kõik selle osakonna nime all olevad üliõpilased nende nimede järgi kahanevas järjekorras

SQLite Piirang:

Saate piirata SQL-päringu poolt tagastatavate ridade arvu, kasutades klauslit LIMIT. Näiteks LIMIT 10 annab teile ainult 10 rida ja ignoreerib kõiki teisi ridu.

LIMIT-klauslis saate OFFSET-klausli abil valida kindla arvu ridu alates konkreetsest positsioonist. Näiteks "LIIT 4 NIVE 4” ignoreerib esimest 4 rida ja tagastab 4 rida alates viiendast reast, nii et saate read 5,6,7, 8, XNUMX ja XNUMX.

Pange tähele, et OFFSET-klausel 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:

SELECT * FROM Students LIMIT 4,3;

See annab teile ainult kolm õpilast alates 5. reast. See annab teile read StudentId 5, 6 ja 7:

Duplikaatide eemaldamine

Kui teie SQL-päring tagastab dubleerivad väärtused, saate kasutadaDISTINCT” märksõna, et eemaldada need duplikaadid ja tagastada erinevad väärtused. Pärast DISTINCT võtme tööd saate määrata rohkem kui ühe veeru.

Näide:

Järgmine päring tagastab duplikaat "osakonna nime väärtused": Siin on duplikaatväärtused nimedega IT, Physics ja Arts.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

See annab teile osakonna nime jaoks dubleerivad väärtused:

Pange tähele, kuidas osakonna nime jaoks on dubleeritud väärtused. Nüüd kasutame sama päringuga märksõna DISTINCT, et eemaldada need duplikaadid ja saada ainult kordumatuid väärtusi. nagu see:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

See annab teile osakonna nime veeru jaoks ainult kolm ainulaadset väärtust:

Agregeeritud

SQLite Agregaadid on sisseehitatud funktsioonid, mis on määratletud SQLite mis rühmitab mitme rea mitu väärtust üheks väärtuseks.

Siin on koondnäitajad, mida toetab SQLite:

SQLite AVG()

Tagastati kõigi x väärtuste keskmine.

Näide:

Järgmises näites saame õpilaste kõigi eksamite keskmise hinde:

SELECT AVG(Mark) FROM Marks;

See annab teile väärtuse "18.375":

Need tulemused saadakse kõigi punktide väärtuste ja nende arvuga jagatud väärtuste liitmisel.

COUNT() – COUNT(X) või COUNT(*)

Tagastab x väärtuse koguarvu. Ja siin on mõned valikud, mida saate rakendusega COUNT kasutada:

• COUNT(x): loendab ainult x väärtusi, kus x on veeru nimi. See ignoreerib NULL väärtusi.
• COUNT(*): loendab kõigi veergude kõik read.
• COUNT (DISTINCT x): saate määrata DISTINCT märksõna enne x, mis loeb x-i erinevad väärtused.

Näide

Järgmises näites saame osakondade koguarvu, millel on COUNT(osakonna ID), COUNT(*) ja COUNT(DISTINCT DepartmentId) ja kuidas need erinevad:

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

See annab teile:

järgmiselt:

• COUNT(osakonna ID) annab teile kõigi osakonna ID-de arvu ja eirab nullväärtusi.
• COUNT(DISTINCT DepartmentId) annab teile osakonna ID erinevad väärtused, mis on ainult 3. Millised on osakonna nime kolm erinevat väärtust. Pange tähele, et õpilase nimes on 8 osakonna nime väärtust. Kuid ainult kolm erinevat väärtust, milleks on matemaatika, IT ja füüsika.
• COUNT(*) loeb õpilaste tabeli ridade arvu, mis on 10 rida 10 õpilase kohta.

GROUP_CONCAT() – GROUP_CONCAT(X) või GROUP_CONCAT(X,Y)

Koondfunktsioon GROUP_CONCAT ühendab mitmekordsed väärtused üheks väärtuseks, kasutades nende eraldamiseks koma. Sellel on järgmised valikud.

• GROUP_CONCAT(X): see ühendab kõik x väärtused üheks stringiks, väärtuste eraldajana kasutatakse koma ",". NULL väärtusi ignoreeritakse.
• GROUP_CONCAT(X, Y): see ühendab x väärtused üheks stringiks, kusjuures y väärtust kasutatakse iga väärtuse eraldajana vaikeeraldaja ',' asemel. NULL väärtusi eiratakse ka.
• GROUP_CONCAT(DISTINCT X): see ühendab kõik x-i erinevad väärtused üheks stringiks, kasutades väärtuste eraldajana koma. NULL väärtusi ignoreeritakse.

GROUP_CONCAT(osakonna nimi) Näide

Järgmine päring ühendab kõik osakonna nime väärtused õpilastelt ja osakondade tabelist üheks komadega eraldatud stringiks. Nii et väärtuste loendi tagastamise asemel lisage igale reale üks väärtus. See tagastab ühel real ainult ühe väärtuse, kusjuures kõik väärtused on eraldatud komadega:

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

See annab teile:

See annab teile nimekirja 8 osakonna nimede väärtustest, mis on ühendatud üheks stringiks, eraldades koma.

GROUP_CONCAT(DISTINCT osakonnanimi) Näide

Järgmine päring ühendab osakonna nime erinevad väärtused õpilaste ja osakondade tabelist üheks komaga eraldatud stringiks:

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments 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 erinevad osakondade nimed, ja duplikaatväärtused eemaldati.

GROUP_CONCAT(osakonna nimi ,'&') Näide

Järgmine päring ühendab kõik osakonna nime veeru väärtused õpilaste ja osakondade tabelist üheks stringiks, kuid eraldajaks on koma asemel märk "&".

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

See annab teile:

Pange tähele, kuidas väärtuste eraldamiseks kasutatakse vaikemärgi "," asemel märki "&".

SQLite MAX() & MIN()

MAX(X) tagastab teile X väärtustest suurima väärtuse. MAX tagastab väärtuse NULL, kui kõik x väärtused on nullid. 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 kõrgeim ja madalaim hindMarks" tabel:

SELECT MAX(Mark), MIN(Mark) FROM Marks;

See annab teile:

SQLite SUM(x), kokku(x)

Mõlemad tagastavad kõigi x väärtuste summa. Kuid need erinevad järgmistes aspektides:

• SUM tagastab nulli, kui kõik väärtused on nullid, kuid Total tagastab 0.
• TOTAL tagastab alati ujukoma väärtused. SUM tagastab täisarvu, kui kõik x väärtused on täisarvud. Kui aga väärtused ei ole täisarvud, tagastab see ujukoma väärtuse.

Näide

Järgmises päringus kasutame SUM ja summa, et saada kõigi märkide summaMarks" tabelid:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

See annab teile:

Nagu näete, tagastab TOTAL alati ujukoma. Kuid SUM tagastab täisarvu väärtuse, kuna veerus „Mark” olevad väärtused võivad olla täisarvudes.

Näide SUM ja TOTAL erinevus:

Järgmises päringus näitame erinevust SUM ja TOTAL vahel, kui nad saavad NULL väärtuste SUM:

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

See annab teile:

Pange tähele, et TestId = 4 jaoks pole ühtegi märki, seega on selle testi jaoks nullväärtused. SUM tagastab tühja väärtuse 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 (korrastatakse) rühmadesse.

Kõigi muude veergude puhul, mis ei ole veergude kaupa rühma kaasatud, saate selle jaoks kasutada koondfunktsiooni.

Näide:

Järgmine päring annab teile igas osakonnas viibivate õpilaste koguarvu.

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s 
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

See annab teile:

Klausel GROUPBY DepartmentName koondab kõik õpilased rühmadesse iga osakonna nime järgi. Iga osakonna rühma kohta loetakse sinna kuuluvad õpilased.

ON klausel

Kui soovite filtreerida klausli GROUP BY poolt tagastatud gruppe, saate määrata klausli "HAVING", mille avaldis on pärast GROUP BY. Avaldist kasutatakse nende rühmade filtreerimiseks.

Näide

Järgmises päringus valime need osakonnad, kus on ainult kaks üliõpilast:

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s 
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName
HAVING COUNT(s.StudentId) = 2;

See annab teile:

Klausel HAVING COUNT(S.StudentId) = 2 filtreerib tagastatud rühmad ja tagastab ainult need rühmad, milles on täpselt kaks õpilast. Meie puhul on kunstide osakonnas 2 õpilast, seega kuvatakse see 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õnes muus alampäringus.

Seda pesastatud päringut nimetatakse alampäringuks. Nüüd näeme mõningaid näiteid alampäringute kasutamisest SELECT-klauslis. Andmete muutmise õpetuses näeme aga, kuidas saame kasutada alampäringuid koos INSERT-, DELETE- ja UPDATE-lausetega.

Alampäringu kasutamine klausli FROM näites

Järgmises päringus lisame FROM-klausli sisse alampäringu:

SELECT
  s.StudentName, t.Mark
FROM Students AS s 
INNER JOIN
(
   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId
)  ON s.StudentId = t.StudentId;

Päring:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

Ülaltoodud päringut nimetatakse siin alampäringuks, kuna see on pesastatud klausli FROM sees. Pange tähele, et andsime sellele varjunime "t", et saaksime viidata päringus sellelt tagastatud veergudele.

See päring annab teile:

Nii et meie puhul

• s.StudentName valitakse põhipäringust, mis annab õpilaste nimed ja
• t.Mark valitakse alampäringust; mis annab iga õpilase saadud hinded

Alampäringu kasutamine klausli WHERE näites

Järgmises päringus lisame WHERE-klauslisse alampäringu:

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId 
                  FROM Students AS s 
                  WHERE d.DepartmentId = s.DepartmentId);

Päring:

SELECT DepartmentId 
FROM Students AS s 
WHERE d.DepartmentId = s.DepartmentId

Ülaltoodud päringut nimetatakse siin alampäringuks, kuna see on pesastatud WHERE-klauslisse. Alampäring tagastab DepartmentId väärtused, mida kasutab operaator NOT EXISTS.

See päring annab teile:

Ülaltoodud päringus oleme valinud osakonna, kus pole ühtegi üliõpilast. Mis on siin "matemaatika" osakond.

komplekt Operasioonid – UNION,Intersect

SQLite toetab järgmisi SET-operatsioone:

LIIT JA LIIT KÕIK

See ühendab mitmest SELECT-lausest tagastatud ühe või mitu tulemuskomplekti (ridade rühma) üheks tulemuskomplektiks.

UNION tagastab erinevad väärtused. Kuid UNION ALL ei sisalda ega sisalda duplikaate.

Pange tähele, et veeru nimi on esimeses SELECT-lauses määratud veeru nimi.

LIIT Näide

Järgmises näites saame osakonna ID loendi õpilaste tabelist ja osakonna ID loendi osakondade tabelist samas veerus:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

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 LIIT KÕIK Näide

Järgmises näites saame osakonna ID loendi õpilaste tabelist ja osakonna ID loendi osakondade tabelist samas veerus:

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

See annab teile:

Päring tagastab 14 rida, 10 rida õpilaste tabelist ja 4 osakondade tabelist. Pange tähele, et tagastatud väärtustes on duplikaate. Samuti pange tähele, et veeru nimi oli see, mis on määratud esimeses SELECT-lauses.

Nüüd vaatame, kuidas UNION kõik annab erinevaid tulemusi, kui asendame UNION ALL sõnaga UNION:

SQLite LÕPETA

Tagastab väärtused, mis on olemas mõlemas kombineeritud tulemuste komplektis. Ühes kombineeritud tulemuste komplektis olevaid väärtusi ignoreeritakse.

Näide

Järgmises päringus valime osakonna ID väärtused, mis on olemas nii tabelites Students ja Departments veerus DepartmentId:

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

See annab teile:

Päring tagastab ainult kolm väärtust 1, 2 ja 3. Millised väärtused on mõlemas tabelis olemas.

Kuid väärtusi null ja 4 ei lisatud, kuna nullväärtus on olemas ainult õpilaste tabelis, mitte osakondade tabelis. Ja väärtus 4 on olemas osakondade tabelis, mitte õpilaste tabelis.

Seetõttu ignoreeriti nii väärtusi NULL kui ka 4 ja neid ei kaasatud tagastatud väärtustesse.

VÄLJA

Oletame, et kui teil on kaks ridade loendit, loend1 ja loend2, ja soovite ridu ainult loendist 1, mida loendis 2 pole, võite kasutada klauslit „EXCEPT”. Klausel EXCEPT võrdleb kahte loendit ja tagastab need read, mis on loendis list1 olemas ja mida loendis 2 ei ole.

Näide

Järgmises päringus valime osakonna ID väärtused, mis on osakondade tabelis olemas ja mida õpilaste tabelis ei ole:

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

See annab teile:

Päring tagastab ainult väärtuse 4. Mis on ainus väärtus, mis on osakondade tabelis olemas ja mida õpilaste tabelis pole.

NULL käsitsemine

"NULL” väärtus on eriline väärtus SQLite. Seda kasutatakse tundmatu või puuduva väärtuse tähistamiseks. Pange tähele, et nullväärtus on täiesti erinev kui "0" või tühi "" väärtus. Kuna 0 ja tühi väärtus on teada, pole nullväärtus teada.

NULL väärtused nõuavad erilist käsitsemist SQLite, näeme nüüd, kuidas NULL väärtusi käsitleda.

Otsige NULL väärtusi

Nullväärtuste otsimiseks ei saa kasutada tavalist võrdusoperaatorit (=). Näiteks otsib järgmine päring õpilasi, kellel on null DepartmentId väärtus:

SELECT * FROM Students WHERE DepartmentId = NULL;

See päring ei anna tulemust:

Kuna NULL väärtus ei võrdu ühegi teise väärtusega, mis sisaldas nullväärtust ise, ei tagastanud see tulemust.

• Päringu toimimiseks peate siiski kasutama "ON NULL" operaator nullväärtuste otsimiseks järgmiselt:

SELECT * FROM Students WHERE DepartmentId IS NULL;

See annab teile:

Päring tagastab need õpilased, kellel on null DepartmentId väärtus.

• Kui soovite saada neid väärtusi, mis pole nullid, peate kasutama "EI OLE NULL” operaator nagu see:

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

See annab teile:

Päring tagastab need õpilased, kellel ei ole NULL DepartmentId väärtust.

Tingimuslikud tulemused

Kui teil on väärtuste loend ja soovite mõne neist mõne tingimuse alusel valida. Selleks peab selle konkreetse väärtuse tingimus olema tõene, et see oleks valitud.

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 valida hindeväärtuse põhjal tekstiväärtuse järgmiselt.

– “Suurepärane”, kui hinne on kõrgem kui 85.

– “Väga hea”, kui hinne on vahemikus 70–85.

– “Hea”, kui hinne on vahemikus 60–70.

Seejärel saate selleks kasutada avaldist CASE.

Seda saab kasutada SELECT-klauslis teatud loogika määratlemiseks, et saaksite valida teatud tulemusi sõltuvalt teatud tingimustest, näiteks if-lausest.

Operaatori CASE saab määratleda erinevate süntaksitega järgmiselt:

1. Võite kasutada erinevaid tingimusi:

CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

2. Või võite kasutada ainult ühte avaldist ja valida erinevate võimalike väärtuste hulgast:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln 
END

Pange tähele, et ELSE klausel on valikuline.

Näide

Järgmises näites kasutame CASE väljendiga NULL väärtus osakonna ID veerus tabelis Õpilased, et kuvada tekst "Osakond puudub" järgmiselt:

SELECT 
  StudentName,
  CASE 
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId 
  END AS DepartmentId
FROM Students;

• CASE-operaator kontrollib osakonna ID väärtust, kas see on null või mitte.
• Kui see on NULL väärtus, valib see osakonna ID väärtuse asemel literaalse väärtuse 'No Department'.
• Kui see ei ole nullväärtus, valib see veeru DepartmentId väärtuse.

See annab teile väljundi, nagu allpool näidatud:

Ühine tabeliväljend

Tavalised tabeliavaldised (CTE-d) on alampäringud, mis on määratletud SQL-lauses antud nimega.

Sellel on eelis alampäringute ees, kuna see on määratletud SQL-lausetest ja muudab päringute lugemise, hooldamise ja mõistmise lihtsamaks.

Tavalise tabeliavaldise saab määratleda, pannes SELECT-lause ette klausli WITH järgmiselt:

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

"CTEname” on mis tahes nimi, mille saate CTE-le anda, saate seda hiljem kasutada. Pange tähele, et saate CTE-des määrata käsu SELECT, UPDATE, INSERT või DELETE

Vaatame nüüd näidet, kuidas kasutada CTE-d SELECT-klauslis.

Näide

Järgmises näites määratleme CTE SELECT-lausest ja kasutame seda hiljem mõnes teises päringus:

WITH AllDepartments
AS
(
  SELECT DepartmentId, DepartmentName
  FROM Departments
)
SELECT 
  s.StudentId,
  s.StudentName,
  a.DepartmentName
FROM Students AS s
INNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;

Selles päringus määratlesime CTE ja andsime sellele nime "Kõik osakonnad“. See CTE määrati SELECT päringuga:

SELECT DepartmentId, DepartmentName
  FROM Departments

Seejärel kasutasime pärast CTE määratlemist seda päringus SELECT, mis tulevad pärast seda.

Pange tähele, et tavalised tabeliavaldised 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 tabeliavaldised 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 keerulisi liitumisi, alampäringuid ja mõningaid liite. Järgmises jaotises näeme täpsema päringu näidet:

Kust me saame,

• Osakonna nimed koos kõigi õpilastega iga osakonna kohta
• Õpilaste nimed eraldatakse komaga ja
• Näitab osakonda, kus on vähemalt kolm üliõpilast

SELECT 
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d 
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(s.StudentId) >= 3;

Lisasime a LIITU klausel, et saada osakonna nimi tabelist Osakonnad. Pärast seda lisasime kahe koondfunktsiooniga klausli GROUP BY:

• "COUNT" iga osakonnarühma õpilaste loendamiseks.
• GROUP_CONCAT, et ühendada õpilased iga rühma jaoks komadega ühes stringis.
• Pärast GROUP BY-d kasutasime osakondade filtreerimiseks HAVING-klauslit ja valisime ainult need osakonnad, kus on vähemalt 3 õpilast.

Tulemus saab olema järgmine:

kokkuvõte

See oli sissejuhatus kirjutamisse SQLite päringuid ja andmebaasi päringute tegemise põhitõdesid ning kuidas saate tagastatud andmeid filtreerida. Nüüd saate kirjutada oma SQLite päringud.