Pentru a scrie interogări SQL într-o bază de date SQLite, trebuie să știți cum funcționează clauzele SELECT, FROM, WHERE, GROUP BY, ORDER BY și LIMIT și cum să le utilizați.
În timpul acestui tutorial, veți învăța cum să utilizați aceste clauze și cum să scrieți clauze SQLite.
În acest tutorial, veți învăța-
- Citirea datelor cu Select
- Nume și Alias
- UNDE
- Limitare și comandă
- Eliminarea duplicatelor
- Agregat
- A se grupa cu
- Interogare și interogare
- Set Operations -UNION, Intersect
- Manipularea NULL
- Rezultate condiționate
- Expresie de masă comună
- Interogări avansate
Citirea datelor cu Select
Clauza SELECT este declarația principală pe care o utilizați pentru interogarea unei baze de date SQLite. În clauza SELECT, specificați ce să selectați. Dar, înainte de clauza selectivă, să vedem de unde putem selecta datele folosind clauza FROM.
Clauza FROM este utilizată pentru a specifica unde doriți să selectați date. În clauza from, puteți specifica unul sau mai multe tabele sau subinterogări din care să selectați datele, așa cum vom vedea mai târziu în tutoriale.
Rețineți că, pentru toate exemplele următoare, trebuie să rulați sqlite3.exe și să deschideți o conexiune la baza de date eșantion, în curând:
Pasul 1) În acest pas,
- Deschideți Computerul meu și navigați la următorul director „ C: \ sqlite ” și
- Apoi deschideți „ sqlite3.exe ”:
Pasul 2) Deschideți baza de date „ TutorialsSampleDB.db ” prin următoarea comandă:
Acum sunteți gata să executați orice tip de interogare pe baza de date.
În clauza SELECT, puteți selecta nu numai un nume de coloană, dar aveți o mulțime de alte opțiuni pentru a specifica ce să selectați. Ca următorul:
SELECTAȚI *
Această comandă va selecta toate coloanele din toate tabelele de referință (sau subinterogări) din clauza FROM. De exemplu:
SELECTAȚI *DE LA StudențiINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Aceasta va selecta toate coloanele atât din tabelele studenți, cât și din tabelele departamentelor:
Alegeți numele de masă. *
Aceasta va selecta toate coloanele doar din tabelul „nume tablă”. De exemplu:
SELECTAȚI studenții. *DE LA StudențiINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Aceasta va selecta toate coloanele doar din tabelul studenților:
O valoare literală
O valoare literală este o valoare constantă care poate fi specificată în instrucțiunea select. Puteți utiliza valorile literale în mod normal în același mod în care utilizați numele coloanelor din clauza SELECT. Aceste valori literale vor fi afișate pentru fiecare rând din rândurile returnate de interogarea SQL.
Iată câteva exemple de valori literale diferite pe care le puteți selecta:
- Literal numeric - numere în orice format, cum ar fi 1, 2,55, etc. etc.
- Litere de șir - Orice șir „SUA”, „acesta este un exemplu de text”, etc.
- NULL - valoare NULL.
- Current_TIME - Vă va oferi ora curentă.
- CURRENT_DATE - aceasta vă va oferi data curentă.
Acest lucru poate fi la îndemână în unele situații în care trebuie să selectați o valoare constantă pentru toate rândurile returnate. De exemplu, dacă doriți să selectați toți studenții din tabelul Studenți, cu o nouă coloană numită țară care conține valoarea „SUA”, puteți face acest lucru:
SELECT *, „SUA” CA Țară DE LA Studenți;
Acest lucru vă va oferi toate coloanele studenților, plus o nouă coloană „Țară” ca aceasta:
Rețineți că această nouă coloană Țara nu este de fapt o nouă coloană adăugată la tabel. Este o coloană virtuală, creată în interogarea pentru afișarea rezultatelor și nu va fi creată pe masă.
Nume și Alias
Aliasul este un nume nou pentru coloană care vă permite să selectați coloana cu un nume nou. Aliasurile de coloană sunt specificate folosind cuvântul cheie „AS”.
De exemplu, dacă doriți să selectați coloana StudentName pentru a fi returnată cu „Student Name” în loc de „StudentName” îi puteți da un alias de genul acesta:
SELECTAți StudentNAME AS 'Student Name' FROM Students;
Acest lucru vă va oferi numele studenților cu numele „Student Name” în loc de „StudentName” astfel:
Rețineți că, numele coloanei încă „ StudentName ”; coloana StudentName este încă aceeași, nu se modifică prin alias.
Aliasul nu va schimba numele coloanei; va schimba doar numele afișat în clauza SELECT.
De asemenea, rețineți că, cuvântul cheie „AS” este opțional, puteți pune numele aliasului fără acesta, ceva de genul acesta:
SELECTAți StudentName 'Student Name' FROM Students;
Și vă va oferi exact același rezultat ca și interogarea anterioară:
De asemenea, puteți oferi aliasuri tabelelor, nu doar coloane. Cu același cuvânt cheie „AS”. De exemplu, puteți face acest lucru:
SELECTAȚI s. * DE LA Studenți AS s;
Acest lucru vă va oferi toate coloanele din tabelul Studenți:
Acest lucru poate fi foarte util dacă vă alăturați mai multor mese; în loc să repetați numele complet al tabelei în interogare, puteți da fiecărei tabele un nume scurt de alias. De exemplu, în următoarea interogare:
SELECTAȚI Studenții.StudentName, Departments.DepartmentNameDE LA StudențiINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Această interogare va selecta fiecare nume de student din tabelul „Studenți” cu numele departamentului său din tabelul „Departamente”:
Cu toate acestea, aceeași interogare poate fi scrisă astfel:
SELECTAȚI s.StudentName, d.DepartmentNameDE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentId;
- Am dat tabelului Studenți un alias „s”, iar tabelului departamentelor un alias „d”.
- Apoi, în loc să folosim numele întregului tabel, le-am folosit aliasurile pentru a ne referi la ele.
- INNER JOIN unește două sau mai multe tabele împreună folosind o condiție. În exemplul nostru, ne-am alăturat tabelului Studenți cu tabelul Departamente cu coloana DepartmentId. Există, de asemenea, o explicație aprofundată pentru INNER JOIN în tutorialul "SQLite Joins".
Acest lucru vă va oferi rezultatul exact ca interogare anterioară:
UNDE
Scrierea interogărilor SQL folosind singura clauză SELECT cu clauza FROM, așa cum am văzut în secțiunea anterioară, vă va oferi toate rândurile din tabele. Cu toate acestea, dacă doriți să filtrați datele returnate, trebuie să adăugați o clauză „UNDE”.
Clauza WHERE este utilizată pentru a filtra setul de rezultate returnat de interogarea SQL. Astfel funcționează clauza WHERE:
- În clauza WHERE, puteți specifica o „expresie”.
- Această expresie va fi evaluată pentru fiecare rând returnat din tabelul (tabelele) specificat (e) în clauza FROM.
- Expresia va fi evaluată ca o expresie booleană, cu un rezultat fie adevărat, fals, fie nul.
- Apoi vor fi returnate doar rândurile pentru care expresia a fost evaluată cu o valoare adevărată, iar cele cu rezultate false sau nule vor fi ignorate și nu vor fi incluse în setul de rezultate.
- Pentru a filtra setul de rezultate folosind clauza WHERE, trebuie să utilizați expresii și operatori.
Lista operatorilor din SQLite și cum să le utilizați
În secțiunea următoare, vom explica cum puteți filtra folosind expresia și operatorii.
Expresia este una sau mai multe valori sau coloane literale combinate între ele cu un operator.
Rețineți că puteți utiliza expresii atât în clauza SELECT, cât și în clauza WHERE.
În exemplele următoare, vom încerca expresiile și operatorii atât în clauza selectă, cât și în clauza WHERE. Pentru a vă arăta cum se comportă.
Există diferite tipuri de expresii și operatori pe care le puteți specifica după cum urmează:
SQLite operatorul de concatenare "||"
Acest operator este folosit pentru a concatena una sau mai multe valori sau coloane literale între ele. Va produce un șir de rezultate din toate valorile sau coloanele literale concatenate. De exemplu:
SELECTAȚI „Id cu Nume:” || StudentId || StudentName AS StudentIdWithNameDE LA Studenți;
Acest lucru se va concatena într-un nou alias „ StudentIdWithName ”:
- Valoarea literală a șirului „ Id cu Nume: ”
- cu valoarea coloanei „ StudentId ” și
- cu valoarea din coloana „ StudentName ”
Operator CAST SQLite:
Operatorul CAST este utilizat pentru a converti o valoare dintr-un tip de date în alt tip de date.
De exemplu, dacă aveți o valoare numerică stocată ca o valoare de tip șir ca aceasta " '12 .5 ' " și doriți să o convertiți într-o valoare numerică, puteți utiliza operatorul CAST pentru a face acest lucru astfel " CAST ('12 .5' AS REAL) ". Sau dacă aveți o valoare zecimală ca 12,5 și trebuie să obțineți numai partea întreagă, o puteți arunca într-un număr întreg ca acesta "CAST (12.5 AS INTEGER)".
Exemplu
În următoarea comandă vom încerca să convertim diferite valori în alte tipuri de date:
SELECTAȚI CAST ('12 .5 'AS REAL) ToReal, CAST (12.5 AS INTEGER) AS ToInteger;
Acest lucru vă va oferi:
Rezultatul este următorul:
- CAST ('12 .5 'AS REAL) - valoarea '12 .5' este o valoare șir, va fi convertită la o valoare REAL.
- CAST (12.5 AS INTEGER) - valoarea 12.5 este o valoare zecimală, va fi convertită în valoare întreagă. Partea zecimală va fi trunchiată și va deveni 12.
Operatori aritmetici SQLite:
Luați două sau mai multe valori literale numerice sau coloane numerice și returnați o valoare numerică. Operatorii aritmetici suportați în SQLite sunt:
|
Exemplu:
În exemplul următor, vom încerca cei cinci operatori aritmetici cu valori numerice literale în același
selectați clauza:
SELECTAȚI 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Acest lucru vă va oferi:
Observați cum am folosit o instrucțiune SELECT fără o clauză FROM aici. Și acest lucru este permis în SQLite atâta timp cât selectăm valori literale.
SQLite Operatori de comparație
Comparați doi operanzi între ei și întoarceți un adevărat sau fals, după cum urmează:
|
Rețineți că, SQLite exprimă valoarea adevărată cu 1 și valoarea falsă cu 0.
Exemplu:
SELECTAȚI10 <6 AS '<', 10 <= 6 AS '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 AS '=', 10 == 6 AS '==',10! = 6 AS '! =', 10 <> 6 AS '<>';
Acest lucru va da așa ceva:
Operatori de potrivire a modelelor SQLite
„ LIKE ” - este folosit pentru potrivirea modelelor. Folosind „ Apreciați ”, puteți căuta valori care se potrivesc cu un model specificat folosind un wildcard.
Operandul din stânga poate fi fie o valoare literală de șir, fie o coloană de șir. Modelul poate fi specificat după cum urmează:
- Conține model. De exemplu, StudentName LIKE '% a%' - aceasta va căuta numele studenților care conțin litera „a” în orice poziție din coloana StudentName.
- Începe cu modelul. De exemplu, „ StudentName LIKE 'a%' ” - căutați numele studenților care încep cu litera „a”.
- Se încheie cu modelul. De exemplu, „ StudentName LIKE '% a' ” - Căutați numele studenților care se termină cu litera „a”.
- Potrivirea oricărui singur caracter dintr-un șir folosind litera de subliniere „_”. De exemplu, „ StudentName LIKE 'J___' ” - Căutați numele studenților care au o lungime de 4 caractere. Trebuie să înceapă cu litera „J” și poate avea alte trei caractere după litera „J”.
Exemple de potrivire a modelelor:
- Obțineți numele studenților care încep cu litera „j”:
SELECTAți StudentNAME DIN Studenți WHERE StudentNAME LIKE 'j%';
Rezultat:
- Obțineți numele studenților care se termină cu litera „y”:
SELECTAți StudentNAME DIN Studenți WHERE StudentNAME LIKE '% y';
Rezultat:
- Obțineți numele studenților care conțin litera „n”:
SELECTAți StudentNAME DIN Studenți WHERE StudentNAME LIKE '% n%';
Rezultat:
"GLOB" - este echivalent cu operatorul LIKE, dar GLOB este sensibil la majuscule și minuscule, spre deosebire de operatorul LIKE. De exemplu, următoarele două comenzi vor da rezultate diferite:
SELECTAȚI „Jack” GLOB „j%”;SELECTAȚI „Jack” LIKE „j%”;
Acest lucru vă va oferi:
- Prima declarație returnează 0 (fals) deoarece operatorul GLOB este sensibil la majuscule, deci „j” nu este egal cu „J”. Cu toate acestea, a doua declarație va returna 1 (adevărat) deoarece operatorul LIKE nu este sensibil la majuscule, deci „j” este egal cu „J”.
Alți operatori:
SQLite ȘI
Un operator logic care combină una sau mai multe expresii. Va reveni adevărat, numai dacă toate expresiile dau o valoare „adevărată”. Cu toate acestea, va returna fals numai dacă toate expresiile dau o valoare „falsă”.
Exemplu:
Următoarea interogare va căuta studenții care au StudentId> 5 și StudentName începe cu litera N, studenții returnați trebuie să îndeplinească cele două condiții:
SELECTAȚI *DE LA StudențiUNDE (StudentId> 5) ȘI (StudentName LIKE 'N%');
Ca rezultat, în captura de ecran de mai sus, acest lucru vă va oferi doar „Nancy”. Nancy este singurul student care îndeplinește ambele condiții.
SQLite SAU
Un operator logic care combină una sau mai multe expresii, astfel încât dacă unul dintre operatorii combinați produce adevărat, atunci acesta va reveni adevărat. Cu toate acestea, dacă toate expresiile dau fals, va reveni fals.
Exemplu:
Următoarea interogare va căuta studenții care au StudentId> 5 sau StudentName începe cu litera N, studenții returnați trebuie să îndeplinească cel puțin una dintre condiții:
SELECTAȚI *DE LA StudențiWHERE (StudentId> 5) SAU (StudentName LIKE 'N%');
Acest lucru vă va oferi:
Ca rezultat, în captura de ecran de mai sus, acest lucru vă va oferi numele unui student cu litera „n” în numele lor, plus codul de student cu valoare> 5
După cum puteți vedea, rezultatul este diferit de interogarea cu operatorul AND.
SQLite ÎNTRE
BETWEEN este utilizat pentru a selecta acele valori care se află într-un interval de două valori. De exemplu, „ X BETWEEN Y AND Z ” va returna adevărat (1) dacă valoarea X este între cele două valori Y și Z. În caz contrar, va returna false (0). „ X ÎNTRE Y ȘI Z ” este echivalent cu „ X> = Y ȘI X <= Z ”, X trebuie să fie mai mare sau egal cu Y și X este mai mic sau egal cu Z.
Exemplu:
În următorul exemplu de interogare, vom scrie o interogare pentru a obține studenți cu valoare Id între 5 și 8:
SELECTAȚI *DE LA StudențiUNDE StudentID ÎNTRE 5 ȘI 8;
Acest lucru va oferi doar studenților cu ID-urile 5, 6, 7 și 8:
SQLite IN
Ia un operand și o listă de operanzi. Va reveni adevărat dacă prima valoare a operandului este egală cu una dintre valorile operandilor din listă. Operatorul IN returnează true (1) dacă lista de operanzi conține prima valoare a operandului în valorile sale. În caz contrar, se va întoarce false (0).
Astfel: „ col IN (x, y, z) ”. Aceasta este echivalentă cu „ (col = x) sau (col = y) sau (col = z) ”.
Exemplu:
Următoarea interogare va selecta doar studenții cu ID-urile 2, 4, 6, 8:
SELECTAȚI *DE LA StudențiUNDE StudentId IN (2, 4, 6, 8);
Ca aceasta:
Interogarea anterioară va da rezultatul exact ca următoarea interogare, deoarece acestea sunt echivalente:
SELECTAȚI *DE LA StudențiWHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);
Ambele interogări dau rezultatul exact. Cu toate acestea, diferența dintre cele două interogări este, prima interogare pe care am folosit-o operatorul „IN”. În a doua interogare, am folosit mai mulți operatori „SAU”.
Operatorul IN este echivalent cu utilizarea mai multor operatori SAU. „ WHERE StudentId IN (2, 4, 6, 8) ” este echivalent cu „ WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); "
Ca aceasta:
SQLite NOT IN
Operandul „NOT IN” este opusul operatorului IN. Dar cu aceeași sintaxă; este nevoie de un operand și o listă de operanzi. Va reveni adevărat dacă valoarea primului operand nu este egală cu una dintre valorile operandilor din listă. adică va reveni adevărat (0) dacă lista de operanzi nu conține primul operand. Astfel: „ col NOT IN (x, y, z) ”. Aceasta este echivalentă cu „ (col <> x) AND (col <> y) AND (col <> z) ”.
Exemplu:
Următoarea interogare va selecta studenții cu ID-uri care nu sunt egale cu unul dintre aceste ID-uri 2, 4, 6, 8:
SELECTAȚI *DE LA StudențiUNDE StudentID NOT IN (2, 4, 6, 8);
Ca aceasta
Interogarea anterioară oferim rezultatul exact ca următoarea interogare, deoarece acestea sunt echivalente:
SELECTAȚI *DE LA StudențiWHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Ca aceasta:
În captura de ecran de mai sus,
Am folosit mai mulți operatori care nu sunt egali "<>" pentru a obține o listă de studenți care nu sunt egali cu niciunul dintre următorii ID-uri 2, 4, 6 și nici 8. Această interogare va returna toți ceilalți studenți, în afară de aceste liste de ID-uri.
SQLite EXISTĂ
Operatorii EXISTS nu iau niciun operand; are nevoie doar de o clauză SELECT după ea. Operatorul EXISTS va returna true (1) dacă există rânduri returnate din clauza SELECT și va reveni false (0) dacă nu există deloc rânduri returnate din clauza SELECT.
Exemplu:
În exemplul următor, vom selecta numele departamentului, dacă ID-ul departamentului există în tabelul studenților:
SELELEȚI Numele departamentuluiDIN Departamente AS dWHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);
Acest lucru vă va oferi:
Doar cele trei departamente „ IT, Fizică și Arte ” vor fi returnate. Și numele departamentului „ Math ” nu va fi returnat deoarece nu există niciun student în acel departament, deci ID-ul departamentului nu există în tabelul studenților. De aceea, operatorul EXISTS a ignorat departamentul „ Matematică ”.
SQLite NU
Inversează rezultatul operatorului precedent care vine după el. De exemplu:
- NU ÎNTRE - Va reveni adevărat dacă ÎNTRE returnează fals și invers.
- NOT LIKE - Va reveni adevărat dacă LIKE returnează fals și invers.
- NOT GLOB - Va reveni adevărat dacă GLOB returnează fals și invers.
- NU EXISTĂ - Va reveni adevărat dacă EXISTĂ returnează fals și invers.
Exemplu:
În exemplul următor, vom folosi operatorul NOT cu operatorul EXISTS pentru a obține numele departamentelor care nu există în tabelul Studenți, care este rezultatul invers al operatorului EXISTS. Deci, căutarea se va face prin DepartmentId care nu există în tabelul departamentului.
SELELEȚI Numele departamentuluiDIN Departamente AS dUNDE NU EXISTĂ (SELECT DepartmentIdDE LA Elevii AS sWHERE d.DepartmentId = s.DepartmentId);
Ieșire :
Numai departamentul „ Matematică ” va fi returnat. Deoarece departamentul „ Matematică ” este singurul departament, care nu există în tabelul studenților.
Limitare și comandă
Comandă SQLite
SQLite Order este de a sorta rezultatul dvs. după una sau mai multe expresii. Pentru a comanda setul de rezultate, trebuie să utilizați clauza ORDER BY după cum urmează:
- Mai întâi, trebuie să specificați clauza ORDER BY.
- Clauza ORDER BY trebuie specificată la sfârșitul interogării; numai clauza LIMIT poate fi specificată după aceasta.
- Specificați expresia cu care ordonați datele, această expresie poate fi un nume de coloană sau o expresie.
- După expresie, puteți specifica o direcție de sortare opțională. Fie DESC, pentru a ordona datele descrescătoare, fie ASC pentru a ordona datele ascendente. Dacă nu ați specificat niciunul dintre ele, datele vor fi sortate crescător.
- Puteți specifica mai multe expresii folosind „,” între ele.
Exemplu
În exemplul următor, vom selecta toți elevii ordonați după numele lor, dar în ordine descrescătoare, apoi după numele departamentului în ordine crescătoare:
SELECTAȚI s.StudentName, d.DepartmentNameDE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentIdCOMANDĂ DE d.DepartmentName ASC, s.StudentName DESC;
Acest lucru vă va oferi:
- SQLite va ordona mai întâi toți studenții după numele departamentului lor în ordine crescătoare
- Apoi, pentru fiecare nume de departament, toți studenții sub acel nume de departament vor fi afișați în ordine descrescătoare după numele lor
Limită SQLite:
Puteți limita numărul de rânduri returnate de interogarea dvs. SQL, utilizând clauza LIMIT. De exemplu, LIMIT 10 vă va oferi doar 10 rânduri și va ignora toate celelalte rânduri.
În clauza LIMIT, puteți selecta un anumit număr de rânduri începând de la o anumită poziție utilizând clauza OFFSET. De exemplu, „ LIMIT 4 OFFSET 4 ” va ignora primele 4 rânduri și a returnat 4 rânduri începând de la al cincilea rând, deci veți obține rândurile 5,6,7 și 8.
Rețineți că clauza OFFSET este opțională, o puteți scrie ca „ LIMIT 4, 4 ” și vă va oferi rezultatele exacte.
Exemplu :
În exemplul următor, vom returna doar 3 studenți începând de la ID-ul studentului 5 folosind interogarea:
SELECTAȚI * DIN LIMITE Studenți 4,3;
Acest lucru vă va oferi doar trei studenți începând cu rândul 5. Deci vă va oferi rândurile cu StudentId 5, 6 și 7:
Eliminarea duplicatelor
Dacă interogarea dvs. SQL returnează valori duplicat, puteți utiliza cuvântul cheie „ DISTINCT ” pentru a elimina acele duplicate și a reveni la valori distincte. Puteți specifica mai multe coloane după lucrul cu cheia DISTINCT.
Exemplu:
Următoarea interogare va returna „valorile numelui departamentului” duplicat: Aici avem valori duplicate cu nume IT, Fizică și Arte.
SELECȚIONEAZĂ d.DepartmentNameDE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentId;
Acest lucru vă va oferi valori duplicate pentru numele departamentului:
Observați, cum există valori duplicate pentru numele departamentului. Acum, vom folosi cuvântul cheie DISTINCT cu aceeași interogare pentru a elimina acele duplicate și pentru a obține doar valori unice. Ca aceasta:
SELECT DISTINCT d.DepartmentNameDE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentId;
Acest lucru vă va oferi doar trei valori unice pentru coloana cu numele departamentului:
Agregat
Agregatele SQLite sunt funcții încorporate definite în SQLite care vor grupa mai multe valori de mai multe rânduri într-o singură valoare.
Iată cumulatele acceptate de SQLite:
SQLite AVG ()
A returnat media pentru toate valorile x.
Exemplu:
În exemplul următor vom obține nota medie a studenților pentru a obține de la toate examenele:
SELECTAȚI AVG (Mark) DIN Marcaje;
Aceasta vă va oferi valoarea „18.375”:
Aceste rezultate provin din însumarea tuturor valorilor de marcă împărțite la numărul lor.
COUNT () - COUNT (X) sau COUNT (*)
Returnează numărul total de câte ori a apărut valoarea x. Iată câteva opțiuni pe care le puteți folosi cu COUNT:
- COUNT (x): Numără doar valorile x, unde x este un nume de coloană. Acesta va ignora valorile NULL.
- COUNT (*): Numărați toate rândurile din toate coloanele.
- COUNT (DISTINCT x): puteți specifica un cuvânt cheie DISTINCT înainte de x, care va obține numărul valorilor distincte ale lui x.
Exemplu
În exemplul următor, vom obține numărul total de departamente cu COUNT (DepartmentId), COUNT (*) și COUNT (DISTINCT DepartmentId) și modul în care acestea sunt diferite:
SELECTAȚI COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) DE LA Studenți;
Acest lucru vă va oferi:
Ca următorul:
- COUNT (DepartmentId) vă va oferi numărul tuturor ID-urilor departamentului și va ignora valorile nule.
- COUNT (DISTINCT DepartmentId) vă oferă valori distincte ale DepartmentId, care sunt doar 3. Care sunt cele trei valori diferite ale numelui departamentului. Observați că există 8 valori ale numelui departamentului în numele studentului. Dar numai diferitele trei valori care sunt Matematica, IT și Fizică.
- COUNT (*) contorizează numărul rândurilor din tabelul studenților care sunt 10 rânduri pentru 10 studenți.
GROUP_CONCAT () - GROUP_CONCAT (X) sau GROUP_CONCAT (X, Y)
Funcția agregată GROUP_CONCAT concatenează valorile multiple într-o singură valoare cu o virgulă pentru a le separa. Are următoarele opțiuni:
- GROUP_CONCAT (X): Aceasta va concatena toată valoarea lui x într-un singur șir, cu virgula "," utilizată ca separator între valori. Valorile NULL vor fi ignorate.
- GROUP_CONCAT (X, Y): Aceasta va concatena valorile lui x într-un singur șir, cu valoarea lui y folosită ca separator între fiecare valoare în loc de separatorul implicit ','. De asemenea, valorile NULL vor fi ignorate.
- GROUP_CONCAT (DISTINCT X): Aceasta va concatena toate valorile distincte ale lui x într-un singur șir, cu virgula "," utilizată ca separator între valori. Valorile NULL vor fi ignorate.
GROUP_CONCAT (DepartmentName) Exemplu
Următoarea interogare va concatena toate valorile numelui departamentului de la studenți și tabelul departamentelor într-o singură șir separată prin virgulă. Deci, în loc să returnați o listă de valori, o valoare pe fiecare rând. Se va returna o singură valoare pe un rând, cu toate valorile separate prin virgulă:
SELECTAȚI GROUP_CONCAT (d.DepartmentName)DE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentId;
Acest lucru vă va oferi:
Acest lucru vă va oferi lista celor 8 nume ale departamentelor valorile concatenate într-un șir separat prin virgulă.
GROUP_CONCAT (DISTINCT DepartmentName) Exemplu
Următoarea interogare va concatena valorile distincte ale numelui departamentului din tabelul studenților și departamentelor într-o singură șir separată prin virgulă:
SELECTARE GROUP_CONCAT (DISTINCT d.DepartmentName)DE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentId;
Acest lucru vă va oferi:
Observați cum rezultatul este diferit de rezultatul anterior; au fost returnate doar trei valori, care sunt numele departamentelor distincte, iar valorile duplicate au fost eliminate.
GROUP_CONCAT (DepartmentName, '&') Exemplu
Următoarea interogare va concatena toate valorile coloanei cu numele departamentului din tabelul studenților și departamentelor într-un singur șir, dar cu caracterul „&” în loc de virgulă ca separator:
SELECTAȚI GROUP_CONCAT (d.DepartmentName, '&')DE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentId;
Acest lucru vă va oferi:
Observați cum este utilizat caracterul „&” în locul caracterului implicit „,„ pentru a separa valorile.
SQLite MAX () și MIN ()
MAX (X) vă returnează cea mai mare valoare din valorile X. MAX va returna o valoare NULL dacă toate valorile lui x sunt nule. În timp ce MIN (X) vă returnează cea mai mică valoare din valorile X. MIN va returna o valoare NULL dacă toate valorile lui X sunt nule.
Exemplu
În următoarea interogare, vom folosi funcțiile MIN și MAX pentru a obține cea mai mare notă și cea mai mică notă din tabelul „ Marci ”:
SELECȚIAȚI MAX (Mark), MIN (Mark) DIN Marcaje;
Acest lucru vă va oferi:
Suma SQLite (x), Total (x)
Ambii vor returna suma tuturor valorilor x. Dar acestea sunt diferite în următoarele:
- SUM va returna nul dacă toate valorile sunt nule, dar Total va returna 0.
- TOTAL returnează întotdeauna valori în virgulă mobilă. SUM returnează o valoare întreagă dacă toate valorile x sunt un număr întreg. Cu toate acestea, dacă valorile nu sunt un număr întreg, va returna o valoare în virgulă mobilă.
Exemplu
În următoarea interogare vom folosi SUM și total pentru a obține suma tuturor marcajelor din tabelele „ Mărci ”:
SELECT SUM (Mark), TOTAL (Mark) FROM Marks;
Acest lucru vă va oferi:
După cum puteți vedea, TOTAL returnează întotdeauna un punct mobil. Dar SUM returnează o valoare întreagă, deoarece valorile din coloana „Mark” ar putea fi în număr întreg.
Diferența dintre SUM și TOTAL exemplu:
În următoarea interogare vom arăta diferența dintre SUM și TOTAL atunci când obțin SUMA valorilor NULL:
SELECT SUM (Mark), TOTAL (Mark) FROM Marks WHERE TestId = 4;
Acest lucru vă va oferi:
Rețineți că nu există semne pentru TestId = 4, deci există valori nule pentru testul respectiv. SUM returnează o valoare nulă ca gol, în timp ce TOTAL returnează 0.
A se grupa cu
Clauza GROUP BY este utilizată pentru a specifica una sau mai multe coloane care vor fi utilizate pentru a grupa rândurile în grupuri. Rândurile cu aceleași valori vor fi adunate (aranjate) împreună în grupuri.
Pentru orice altă coloană care nu este inclusă în grup pe coloane, puteți utiliza o funcție agregată pentru aceasta.
Exemplu:
Următoarea interogare vă va oferi numărul total de studenți prezenți în fiecare departament.
SELECȚIONEAZĂ d.DepartmentName, COUNT (s.StudentId) AS StudentsCountDE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentIdGRUPUL PE d. Numele departamentului;
Acest lucru vă va oferi:
Clauza GROUPBY DepartmentName va grupa toți studenții în grupuri câte unul pentru fiecare nume de departament. Pentru fiecare grup de „departament”, acesta va număra elevii pe el.
Având clauza
Dacă doriți să filtrați grupurile returnate de clauza GROUP BY, atunci puteți specifica o clauză „HAVING” cu expresie după GROUP BY. Expresia va fi utilizată pentru a filtra aceste grupuri.
Exemplu
În următoarea interogare, vom selecta acele departamente care au doar doi studenți:
SELECȚIONEAZĂ d.DepartmentName, COUNT (s.StudentId) AS StudentsCountDE LA Elevii AS sINNER JOIN Departamente AS d ON s.DepartmentId = d.DepartmentIdGRUPUL PE d. Numele departamentuluiAVÂND COUNT (s.StudentId) = 2;
Acest lucru vă va oferi:
Clauza HAVING COUNT (S.StudentId) = 2 va filtra grupurile returnate și va returna doar acele grupuri care conțin exact doi studenți pe ea. În cazul nostru, departamentul de Arte are 2 studenți, deci este afișat în rezultat.
SQLite Interogare și Subinterogare
În cadrul oricărei interogări, puteți utiliza o altă interogare fie într-un SELECT, INSERT, DELETE, UPDATE, fie într-o altă interogare.
Această interogare imbricată se numește subinterogare. Vom vedea acum câteva exemple de utilizare a subinterogărilor în clauza SELECT. Cu toate acestea, în tutorialul Modificarea datelor, vom vedea cum putem utiliza subinterogări cu instrucțiuni INSERT, DELETE și UPDATE.
Utilizarea subconsultării în exemplul clauzei FROM
În următoarea interogare vom include o subinterogare în clauza FROM:
SELECTAȚIs.StudentName, t.MarkDE LA Elevii AS sINTERIOR ÎNSCRIEȚI-VĂ(SELECT StudentId, MarkDIN Teste ASINNER JOIN marchează AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
Interogarea:
SELECT StudentId, MarkDIN Teste ASINNER JOIN marchează AS m ON t.TestId = m.TestId
Interogarea de mai sus se numește subinterogare aici, deoarece este imbricată în clauza FROM. Observați că i-am dat un nume de alias „t”, astfel încât să ne putem referi la coloanele returnate din acesta în interogare.
Această interogare vă va oferi:
Deci, în cazul nostru,
- s.StudentName este selectat din interogarea principală care dă numele studenților și
- t.Mark este selectat din subinterogare; care dă note obținute de fiecare dintre acești elevi
Utilizarea subconsultării în exemplul clauzei WHERE
În următoarea interogare vom include o subinterogare în clauza WHERE:
SELELEȚI Numele departamentuluiDIN Departamente AS dUNDE NU EXISTĂ (SELECT DepartmentIdDE LA Elevii AS sWHERE d.DepartmentId = s.DepartmentId);
Interogarea:
SELECT DepartmentIdDE LA Elevii AS sUNDE d.DepartmentId = s.DepartmentId
Interogarea de mai sus se numește subinterogare aici, deoarece este cuibărită în clauza WHERE. Subinterogarea va returna valorile DepartmentId care vor fi folosite de operator NU EXISTĂ.
Această interogare vă va oferi:
În interogarea de mai sus, am selectat departamentul care nu are niciun student înscris în el. Care este departamentul „Matematică” de aici.
Set Operations - UNION, Intersect
SQLite acceptă următoarele operații SET:
UNION & UNION ALL
Combină unul sau mai multe seturi de rezultate (un grup de rânduri) returnate din mai multe instrucțiuni SELECT într-un singur set de rezultate.
UNION va returna valori distincte. Cu toate acestea, UNION ALL nu va include și va include duplicate.
Rețineți că numele coloanei va fi numele coloanei specificat în prima instrucțiune SELECT.
UNION Exemplu
În exemplul următor, vom obține lista Departamentului din tabelul studenților și lista Departamentului din tabelul departamentelor din aceeași coloană:
SELECT DepartmentId AS DepartmentIdUnioned FROM StudentsUNIUNESELECȚIEI DepartamentId DIN Departamente;
Acest lucru vă va oferi:
Interogarea returnează doar 5 rânduri, care sunt valorile distincte ale ID-ului departamentului. Observați prima valoare care este valoarea nulă.
SQLite UNION ALL Exemplu
În exemplul următor, vom obține lista Departamentului din tabelul studenților și lista Departamentului din tabelul departamentelor din aceeași coloană:
SELECT DepartmentId AS DepartmentIdUnioned FROM StudentsUNION TOATESELECȚIEI DepartamentId DIN Departamente;
Acest lucru vă va oferi:
Interogarea va returna 14 rânduri, 10 rânduri din tabelul studenților și 4 din tabelul departamentelor. Rețineți că există valori duplicate în valorile returnate. De asemenea, rețineți că numele coloanei a fost cel specificat în prima instrucțiune SELECT.
Acum, să vedem cum UNION va da rezultate diferite dacă înlocuim UNION ALL cu UNION:
SQLite INTERSECT
Returnează valorile existente atât în setul de rezultate combinate. Valorile care există într-unul dintre seturile de rezultate combinate vor fi ignorate.
Exemplu
În următoarea interogare, vom selecta valorile DepartmentId care există atât în tabelele Studenți, cât și Departamente din coloana DepartmentId:
SELECTAȚI DepartamentulId din studențiIntersecteazăSELECȚIEI DepartamentId DIN Departamente;
Acest lucru vă va oferi:
Interogarea returnează doar trei valori 1, 2 și 3. Care sunt valorile care există în ambele tabele.
Cu toate acestea, valorile nule și 4 nu au fost incluse, deoarece valoarea nulă există numai în tabelul studenților și nu în tabelul departamentelor. Iar valoarea 4 există în tabelul departamentelor și nu în tabelul studenților.
De aceea, ambele valori NULL și 4 au fost ignorate și nu au fost incluse în valorile returnate.
CU EXCEPTIA
Să presupunem că dacă aveți două liste de rânduri, list1 și list2 și doriți doar rândurile din list1 care nu există în list2, puteți utiliza clauza „EXCEPT”. Clauza EXCEPT compară cele două liste și returnează acele rânduri care există în list1 și nu există în list2.
Exemplu
În următoarea interogare, vom selecta valorile DepartmentId care există în tabelul departamentelor și nu există în tabelul studenților:
SELECTAȚI DepartmentId DIN DepartamenteCU EXCEPTIASELECT DepartmentId FROM Students;
Acest lucru vă va oferi:
Interogarea returnează doar valoarea 4. Care este singura valoare care există în tabelul departamentelor și nu există în tabelul studenților.
Manipularea NULL
Valoarea " NULL " este o valoare specială în SQLite. Este folosit pentru a reprezenta o valoare necunoscută sau lipsă. Rețineți că valoarea nulă este complet diferită de valoarea „ 0 ” sau valoarea „„ goală ”. Deoarece 0 și valoarea necompletată sunt o valoare cunoscută, totuși, valoarea nulă este necunoscută.
Valorile NULL necesită o manipulare specială în SQLite, vom vedea acum cum să gestionăm valorile NULL.
Căutați valori NULL
Nu puteți utiliza operatorul de egalitate normal (=) pentru a căuta valorile nule. De exemplu, următoarea interogare caută studenții care au o valoare nulă DepartmentId:
SELECTAȚI * DIN STUDENȚI UNDE DepartmentId = NULL;
Această interogare nu va da niciun rezultat:
Deoarece valoarea NULL nu este egală cu nici o altă valoare, a inclus o valoare nulă în sine, de aceea nu a returnat niciun rezultat.
- Cu toate acestea, pentru a face ca interogarea să funcționeze, trebuie să utilizați operatorul „IS NULL” pentru a căuta valori nule după cum urmează:
SELECTAȚI * DIN STUDENȚII UNDE Departamentul este NUL;
Acest lucru vă va oferi:
Interogarea va returna acei studenți care au o valoare nulă DepartmentId.
- Dacă doriți să obțineți acele valori care nu sunt nule, atunci trebuie să utilizați operatorul „ NU ESTE NUL ” astfel:
SELECTAȚI * DIN STUDENȚII ÎNDE Departamentul nu este nul;
Acest lucru vă va oferi:
Interogarea va returna acei studenți care nu au o valoare NULL DepartmentId.
Rezultate condiționate
Dacă aveți o listă de valori și doriți să selectați una dintre ele pe baza unor condiții. Pentru aceasta, condiția pentru valoarea respectivă ar trebui să fie adevărată pentru a fi selectată.
Expresia CASE va evalua această listă de condiții pentru toate valorile. Dacă condiția este adevărată, va returna acea valoare.
De exemplu, dacă aveți o coloană „Notă” și doriți să selectați o valoare text pe baza valorii notei, după cum urmează:
- „Excelent” dacă nota este mai mare de 85.
- „Foarte bine” dacă nota este între 70 și 85.
- „Bine” dacă nota este între 60 și 70.
Apoi puteți utiliza expresia CASE pentru a face acest lucru.
Aceasta poate fi utilizată pentru a defini o anumită logică în clauza SELECT, astfel încât să puteți selecta anumite rezultate în funcție de anumite condiții, cum ar fi instrucțiunea if de exemplu.
Operatorul CASE poate fi definit cu sintaxi diferite după cum urmează:
- Puteți utiliza diferite condiții:
CAZWHEN condition1 THEN result1WHEN condition2 THEN rezultat2WHEN condition3 THEN result3 ... ELSE resultnSFÂRȘIT
- Sau, puteți utiliza o singură expresie și puteți pune diferite valori posibile din care să alegeți:
Expresie CASEWHEN value1 THEN result1WHEN value2 THEN result2WHEN value3 THEN result3 ... ELSE restulnSFÂRȘIT
Rețineți că clauza ELSE este opțională.
Exemplu
În exemplul următor, vom folosi expresia CASE cu valoare NULL în coloana Id departament din tabelul Studenți pentru a afișa textul „Fără departament” după cum urmează:
SELECTAȚINumele studentului,CAZWHEN DepartmentId IS NULL THEN 'No Department'Departamentul ELSEIdEND AS DepartmentIdDE LA Studenți;
- Operatorul CASE va verifica valoarea DepartmentId dacă este nulă sau nu.
- Dacă este o valoare NULL, atunci va selecta valoarea literală „Fără departament” în loc de valoarea DepartmentId.
- Dacă nu este o valoare nulă, atunci va selecta valoarea coloanei DepartmentId.
Acest lucru vă va oferi rezultatul așa cum se arată mai jos:
Expresie de masă comună
Expresiile obișnuite de tabel (CTE) sunt subinterogări care sunt definite în instrucțiunea SQL cu un nume dat.
Are un avantaj față de subinterogări, deoarece este definit din instrucțiunile SQL și va face interogările mai ușor de citit, întreținut și înțeles.
O expresie de tabel comună poate fi definită prin plasarea clauzei WITH în fața unei instrucțiuni SELECT după cum urmează:
CU CTEnameLA FEL DE(Instrucțiunea SELECT)Alegeți, actualizați, inserați sau actualizați declarația aici DE LA CTE
„ CTEname ” este orice nume pe care îl puteți da pentru CTE, îl puteți folosi pentru a face referire la el mai târziu. Rețineți că puteți defini instrucțiunile SELECT, UPDATE, INSERT sau DELETE pe CTE
Acum, să vedem un exemplu de utilizare a CTE în clauza SELECT.
Exemplu
În exemplul următor, vom defini un CTE dintr-o instrucțiune SELECT și apoi îl vom folosi ulterior pentru o altă interogare:
CU AllDepartmentsLA FEL DE(SELECT DepartmentId, DepartmentNameDIN Departamente)SELECTAȚIs.StudentId,s.StudentName,a.DepartmentNameDE LA Elevii AS sINNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
În această interogare, am definit un CTE și i-am dat numele „ AllDepartments ”. Acest CTE a fost definit dintr-o interogare SELECT:
SELECT DepartmentId, DepartmentNameDIN Departamente
Apoi, după ce am definit CTE, l-am folosit în interogarea SELECT care vine după el.
Rețineți că, expresiile de tabel comune nu afectează rezultatul interogării. Este o modalitate de a defini o vizualizare logică sau o subinterogare pentru a le reutiliza în aceeași interogare. Expresiile obișnuite de tabel sunt ca o variabilă pe care o declarați și o reutilizați ca subinterogare. Doar instrucțiunea SELECT afectează rezultatul interogării.
Această interogare vă va oferi:
Interogări avansate
Interogările avansate sunt acele interogări care conțin îmbinări complexe, subinterogări și unele agregate. În secțiunea următoare vom vedea un exemplu de interogare avansată:
De unde avem,
- Numele departamentului cu toți studenții pentru fiecare departament
- Numele elevilor separați cu virgulă și
- Se afișează departamentul care are cel puțin trei studenți
SELECTAȚId. Numele departamentului,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) AS StudențiDIN Departamente AS dINNER JOIN Students AS s ON s.DepartmentId = d.DepartmentIdGRUPĂ DUPĂ Numele departamentuluiAVÂND COUNT (s.StudentId)> = 3;
Am adăugat o clauză JOIN pentru a obține DepartamentulNume din tabelul Departamente. După aceea, am adăugat o clauză GROUP BY cu două funcții agregate:
- „COUNT” pentru a număra studenții pentru fiecare grup de departament.
- GROUP_CONCAT pentru concatenarea studenților pentru fiecare grup cu virgulă separată într-un șir.
- După GROUP BY, am folosit clauza HAVING pentru a filtra departamentele și a selecta doar acele departamente care au cel puțin 3 studenți.
Rezultatul va fi după cum urmează:
Rezumat:
Aceasta a fost o introducere în scrierea interogărilor SQLite și elementele de bază ale interogării bazei de date și modul în care puteți filtra datele returnate. Acum puteți scrie propriile interogări SQLite.