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

Allolevad jaotised tutvustavad andmete lugemist SELECT ja FROM funktsioonide abil, ridade filtreerimist WHERE ja selle operaatorite abil, tulemuste järjestamist ja piiramist, koondfunktsioone, GROUP BY ja HAVING funktsioone, alampäringuid, hulgaoperatsioone, NULL-i käsitlemist, CASE-avaldisi ja tavalisi tabeliavaldisi.

Kirjutama SQL päringuid aastal 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

Ava Minu arvuti ja navigeeri järgmisse kataloogi „C:\sqlite” ning seejärel ava „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:

Pane tähele, et veeru nimi on endiselt „StudentName“; veerg StudentName on endiselt sama, see ei muutu aliase tõttu.

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 aliase „s“ ja osakondade tabelile aliase „d“. Seejärel kasutasime tabeli täieliku nime asemel nende aliase, et neile viidata. INNER JOIN ühendab kaks või enam tabelit tingimuse abil. Meie näites ühendasime õpilaste tabeli osakondade tabeliga, millel on veerg „OsakonnaId“. INNER JOINi kohta on ka põhjalik selgitus jaotises „SQLite Liita"õ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 SQL-päringu tagastatud tulemuste filtreerimiseks. WHERE-klausli toimimine on järgmine:

• 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 liidetakse uueks aliaseks „StudentIdWithName“:

• Sõnkirjaline stringiväärtus „Id nimega:“
• veeru „StudentId” väärtusega ja
• väärtusega veerust „StudentName”

SQLite CAST-i operaator:

CAST-operaatorit kasutatakse väärtuse teisendamiseks andmetüüp teisele andmetüübile.

Näiteks kui teil on stringina salvestatud arvväärtus, näiteks „'12.5'” ja soovite selle arvväärtuseks teisendada, saate seda teha CAST-operaatori abil, näiteks „CAST( '12.5' ​​AS REAL)”. Või kui teil on kümnendväärtus, näiteks 12.5, ja teil on vaja saada ainult täisarvuosa, saate selle täisarvuks teisendada, 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:

• Liitmine „+“ – annab kahe operandi summa.
• allatractsioon „–” – alamtracts kahe operandi ja annab tulemuseks vahe.
• Korrutamine “*” – kahe operandi korrutis.
• Meeldetuletus (moodul) „%” – annab jäägi, mis saadakse ühe operandi jagamisel teise operandiga.
• Jagamine „/” – tagastab vasakpoolse operandi jagamisel parempoolse operandiga saadud jagatise.

Näide:

Järgmises näites proovime samas valikuklauslis viit aritmeetilist operaatorit sõnasõnaliste numbriliste väärtustega:

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 väärtuse „true”, kui vasakpoolne operand on väiksem kui parempoolne operand.
• „<=” – tagastab väärtuse „true”, kui vasakpoolne operand on väiksem või võrdne parema operandiga.
• „>” – tagastab väärtuse „true”, kui vasak operand on suurem kui parem operand.
• „>=” – tagastab väärtuse „true”, kui vasakpoolne operand on suurem või võrdne parema operandiga.
• „=” ja „==” – tagastab väärtuse „true”, kui kaks operandi on võrdsed. Pange tähele, et mõlemad operaatorid on samad ja nende vahel pole vahet.
• „!=" ja „<>" – tagastab väärtuse „true”, kui kaks operandi ei ole võrdsed. Pange tähele, et mõlemad operaatorid on samad ja nende vahel pole mingit 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

„LIKE“ – kasutatakse mustri sobitamiseks. Funktsiooni „Like“ abil saate otsida väärtusi, mis vastavad metamärgi abil 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 StudentName LIKE '%a%' – see otsib õpilaste nimesid, mis sisaldavad StudentName veerus mis tahes positsioonil tähte „a“.
• Alustab mustriga. Näiteks „ÕpilaseNimi LIKE 'a%'” – otsi õpilaste nimesid, mis algavad tähega „a”.
• Lõpeb mustriga. Näiteks „ÕpilaseNimi LIKE '%a'” – Otsi õpilaste nimesid, mis lõpevad tähega „a”.
• Üksiku tähemärgi leidmine stringis alakriipsu „_“ abil. Näiteks „ÕpilaseNimi LIKE 'J___'“ – Otsi õpilaste nimesid, mis on 4 tähemärgi pikkused. See peab algama tähega „J“ ja pärast „J“ tähte võib olla veel kolm tähemärki.

Mustri sobitamise näited:

Hankige õpilastele j-tähega algavad nimed:

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

Tulemus:

Hankige õpilaste nimed y-tähega:

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

Tulemus:

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

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

Tulemus:

„GLOB” – on samaväärne operaatoriga LIKE, kuid GLOB on erinevalt operaatorist LIKE tõstutundlik. Näiteks järgmised kaks käsku tagastavad 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

Funktsioon BETWEEN valib väärtused, mis jäävad kahe väärtuse vahemikku. Näiteks „X BETWEEN Y AND Z” tagastab väärtuse true (1), kui väärtus X jääb kahe väärtuse Y ja Z vahele. Vastasel juhul tagastab see väärtuse false (0). „X BETWEEN Y AND Z” on samaväärne funktsiooniga „X >= Y AND X <= Z”, X peab olema suurem või võrdne Y-ga ja X peab olema 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).

Näiteks nii: „col IN(x, y, z)“. See on samaväärne valemiga „(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 OR-operaatori kasutamisega. „WHERE StudentId IN(2, 4, 6, 8)” on samaväärne järgmisega: „WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);“

Nagu nii:

SQLite EI SISSE

„NOT IN“ operand on IN operaatori vastand. Kuid sama süntaksiga; see võtab ühe operandi ja operandide loendi. See tagastab väärtuse „true“, kui esimese operandi väärtus ei ole võrdne ühegi loendis oleva operandi väärtusega. St see tagastab väärtuse „true“ (0), kui operandide loend ei sisalda esimest operandi. Näiteks: „col NOT IN(x, y, z)“. See on samaväärne funktsiooniga „(col<>x) AND (col<>y) AND (col<>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:

Tagastati ainult kolm osakonda: „IT, füüsika ja kunstid”. Osakonna nime „Matemaatika” ei tagastata, kuna selles osakonnas pole ühtegi tudengit ja seega osakonna ID-d tudengite tabelis ei eksisteeri. Seetõttu ignoreeris operaator EXISTS osakonda „Matemaatika”.

SQLite EI

Reverses on sellele järgneva operaatori tulemus. 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:

Tagastatakse ainult osakond „Matemaatika“. Kuna osakond „Matemaatika“ on ainus osakond, siis seda õpilaste tabelis ei eksisteeri.

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 saab OFFSET-klausli abil valida kindla arvu ridu alates kindlast positsioonist. Näiteks „LIMIT 4 OFFSET 4” ignoreerib esimesi nelja rida ja tagastab alates viiendast reast neli rida, seega saadakse read 4, 4, 5,6,7 ja 8.

Pane tähele, et OFFSET-klausel on valikuline, võid selle kirjutada kujul „LIMIT 4, 4“ ja see annab sulle 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 duplikaatväärtusi, saate nende duplikaatide eemaldamiseks ja erinevate väärtuste tagastamiseks kasutada märksõna „DISTINCT”. Pärast DISTINCT-võtme toimimist 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 tabelist „Hinded” kõrgeim ja madalaim hinne:

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 funktsioone SUM ja total, et saada kõigi „Hinded“ tabelites olevate hinnete summa:

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

Väärtus „NULL” on eriväärtus SQLiteSeda kasutatakse tundmatu või puuduva väärtuse esitamiseks. Pange tähele, et nullväärtus on täiesti erinev väärtusest „0” või tühi „”. Kuna 0 ja tühi väärtus on teadaolev väärtus, on nullväärtus teadmata.

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 aga kasutama operaatorit „IS NULL”, et otsida nullväärtusi 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 väärtusi, mis ei ole nullid, peate kasutama operaatorit „IS NOT NULL” järgmiselt:

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:

Võite kasutada erinevaid tingimusi:

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

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 õpilaste tabeli osakonna ID veerus CASE-avaldist NULL-väärtusega, 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

„CTEnimi” on mis tahes nimi, mille saate CTE-le anda ja mida saate hiljem sellele viidates kasutada. Pange tähele, et CTE-dele saab defineerida SELECT-, UPDATE-, INSERT- või DELETE-lauseid.

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 defineerisime CTE ja andsime sellele nimeks „KõikOsakonnad“. See CTE defineeriti SELECT-päringu abil:

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 JOIN-klausli, et saada osakonna nimi (DepartmentName) tabelist Departments (Osakonnad). Seejärel lisasime GROUP BY-klausli kahe koondfunktsiooniga:

• "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:

KKK