SQLite Interogare: Selectare, Unde, LIMITĂ, OFFSET, Număr, Grupare după
Marcus Allen
Pentru a scrie interogări SQL într-un SQLite baza de date, 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 SQLite clauze.
Citirea datelor cu Select
Clauza SELECT este instrucțiunea principală pe care o utilizați pentru a interoga un SQLite Bază de date. În clauza SELECT, specificați ce să selectați. Dar înainte de clauza select, să vedem de unde putem selecta datele folosind clauza FROM.
Clauza FROM este folosită pentru a specifica unde doriți să selectați datele. În clauza from, puteți specifica unul sau mai multe tabele sau subinterogare 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 ca flux:
Pas 1) În această etapă,
- Deschideți My Computer și navigați la următorul director „C:\sqlite" și
- Apoi deschideți „sqlite3.exe„:
Pas 2) Deschide baza de date „TutorialeSampleDB.db” prin următoarea comandă:
Acum sunteți gata să rulați orice tip de interogare în 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. După cum urmează:
SELECTAȚI *
Această comandă va selecta toate coloanele din toate tabelele (sau subinterogările) la care se face referire în clauza FROM. De exemplu:
SELECT * FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Aceasta va selecta toate coloanele atât din tabelele studenți, cât și din tabelele departamentelor:
SELECTAȚI nume tabel.*
Aceasta va selecta toate coloanele numai din tabelul „tablename”. De exemplu:
SELECT Students.* FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Aceasta va selecta numai toate coloanele 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 de coloane în 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 diferite valori literale pe care le puteți selecta:
- Literal numeric – numere în orice format, cum ar fi 1, 2.55, etc.
- Literale șir – Orice șir „SUA”, „acesta este un text exemplu”, … etc.
- NULL – valoare NULL.
- Current_TIME – Vă va oferi ora curentă.
- CURRENT_DATE – aceasta vă va oferi data curentă.
Acest lucru poate fi util î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 „USA”, puteți face acest lucru:
SELECT *, 'USA' AS Country FROM Students;
Acest lucru vă va oferi toate coloanele studenților, plus o nouă coloană „Țara” 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 tabel.
Nume și Alias
Aliasul este un nume nou pentru coloana 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 ca acesta:
SELECT StudentName AS 'Student Name' FROM Students;
Acest lucru vă va oferi numele elevilor cu numele „Numele elevului” în loc de „Numele elevului”, astfel:
Rețineți că, numele coloanei rămâne „Numele studentului„; coloana StudentName este în continuare aceeași, nu se schimbă după 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ă el, ceva de genul acesta:
SELECT StudentName 'Student Name' FROM Students;
Și vă va oferi exact aceeași ieșire ca interogarea anterioară:
De asemenea, puteți da tabelelor aliasuri, nu doar coloane. Cu același cuvânt cheie „AS”. De exemplu, puteți face acest lucru:
SELECT s.* FROM Students 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 mult de o masă; în loc să repetați numele complet al tabelului în interogare, puteți da fiecărui tabel un nume scurt de alias. De exemplu, în următoarea interogare:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Această interogare va selecta numele fiecărui student din tabelul „Studenți” cu numele departamentului său din tabelul „Departamente”:
Cu toate acestea, aceeași interogare poate fi scrisă astfel:
SELECT s.StudentName, d.DepartmentName FROM Students AS s INNER JOIN Departments 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 complet al tabelului, am folosit pseudonimele lor pentru a ne referi la ele.
- INNER JOIN unește două sau mai multe tabele folosind o condiție. În exemplul nostru, am alăturat tabelul Students cu tabelul Departments cu coloana DepartmentId. Există, de asemenea, o explicație aprofundată pentru INNER JOIN în „SQLite Joins” tutorial.
Aceasta vă va oferi rezultatul exact ca interogarea anterioară:
UNDE
Scrierea de interogări SQL folosind doar clauza 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 folosită pentru a filtra setul de rezultate returnat de Interogare SQL. Iată cum funcționează clauza WHERE:
- În clauza WHERE, puteți specifica o „expresie”.
- Acea expresie va fi evaluată pentru fiecare rând returnat din tabelul(e) 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 numai 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 folosiți
În secțiunea următoare, vom explica cum puteți filtra folosind expresii și operatori.
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 următoarele exemple, vom încerca expresiile și operatorii atât din clauza select, cât și din clauza WHERE. Pentru a vă arăta cum se comportă.
Există diferite tipuri de expresii și operatori pe care îi 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:
SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName FROM Students;
Aceasta se va concatena într-un nou alias „StudentIdWithName„:
- Valoarea literală a șirului „Id cu nume:
- cu valoarea „Carnet de student” coloana și
- cu valoarea de la „Numele studentului” coloana
SQLite operator CAST:
Operatorul CAST este folosit pentru a converti o valoare dintr-un tip de date în altul tip de date.
De exemplu, dacă aveți o valoare numerică stocată ca valoare ș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” CA REAL)„. Sau dacă aveți o valoare zecimală precum 12.5 și trebuie să obțineți numai partea întreagă, o puteți transforma î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:
SELECT 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ă într-o valoare REAL.
- CAST(12.5 AS INTEGER) – valoarea 12.5 este o valoare zecimală, va fi convertită într-o valoare întreagă. Partea zecimală va fi trunchiată și devine 12.
SQLite Aritmetică Operators:
Luați două sau mai multe valori numerice literale 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
select clauza:
SELECT 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 să intre SQLite atâta timp cât selectăm valori literale.
SQLite Operatori de comparație
Comparați doi operanzi unul cu celălalt și returnaț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:
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 '<>';
Acest lucru va da ceva de genul acesta:
SQLite Operatori de potrivire a modelelor
SIMILAR” – este folosit pentru potrivirea modelelor. Folosind „Aprecieri„, 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ă ș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 elevilor care încep cu litera „a”.
- Se termină cu modelul. De exemplu, "StudentName LIKE '%a'” – Căutați numele elevilor care se termină cu litera „a”.
- Potrivirea oricărui caracter dintr-un șir folosind litera de subliniere „_”. De exemplu, "StudentName LIKE „J___”” – Căutați numele elevilor care au 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 nume de studenți care încep cu litera „j”:
SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';
Rezultat:
- Obțineți numele elevilor care se termină cu litera „y”:
SELECT StudentName FROM Students WHERE StudentName LIKE '%y';
Rezultat:
- Obțineți numele studenților care conțin litera „n”:
SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';
Rezultat:
„GLOB” – este echivalent cu operatorul LIKE, dar GLOB este sensibil la majuscule, spre deosebire de operatorul LIKE. De exemplu, următoarele două comenzi vor returna rezultate diferite:
SELECT 'Jack' GLOB 'j%'; SELECT 'Jack' LIKE 'j%';
Acest lucru vă va oferi:
- Prima instrucțiune returnează 0 (fals) deoarece operatorul GLOB este sensibil la majuscule și minuscule, deci „j” nu este egal cu „J”. Cu toate acestea, a doua instrucțiune va returna 1 (adevărat), deoarece operatorul LIKE nu face distincție între majuscule și minuscule, deci „j” este egal cu „J”.
Alti operatori:
SQLite AND
Un operator logic care combină una sau mai multe expresii. Va returna adevărat, numai dacă toate expresiile dau o valoare „adevărată”. Cu toate acestea, va returna false numai dacă toate expresiile dau o valoare „falsă”.
Exemplu:
Următoarea interogare va căuta studenți care au StudentId > 5 și StudentName începe cu litera N, studenții returnați trebuie să îndeplinească cele două condiții:
SELECT * FROM Students WHERE (StudentId > 5) AND (StudentName LIKE 'N%');
Ca rezultat, în captura de ecran de mai sus, aceasta vă va oferi doar „Nancy”. Nancy este singura studentă care îndeplinește ambele condiții.
SQLite OR
Un operator logic care combină una sau mai multe expresii, astfel încât, dacă unul dintre operatorii combinați rezultă adevărat, atunci va returna adevărat. Cu toate acestea, dacă toate expresiile sunt false, va returna false.
Exemplu:
Următoarea interogare va căuta studenți care au StudentId > 5 sau StudentName începe cu litera N, studenții returnați trebuie să îndeplinească cel puțin una dintre condiții:
SELECT * FROM Students WHERE (StudentId > 5) OR (StudentName LIKE 'N%');
Acest lucru vă va oferi:
Ca rezultat, în captura de ecran de mai sus, aceasta vă va oferi numele unui student cu litera „n” în numele său plus ID-ul studentului având 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 INTRE Y SI Z” va returna true (1) dacă valoarea X este între cele două valori Y și Z. În caz contrar, va returna false (0). „X INTRE Y SI 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ții cu valoarea ID între 5 și 8:
SELECT * FROM Students WHERE StudentId BETWEEN 5 AND 8;
Acest lucru va oferi numai studenților cu id-urile 5, 6, 7 și 8:
SQLite IN
Preia un operand și o listă de operanzi. Va returna true dacă valoarea primului operand este egală cu una dintre valoarea operanzilor din listă. Operatorul IN returnează true (1) dacă lista de operanzi conține prima valoare a operandului în valorile sale. În caz contrar, va returna false (0).
Ca aceasta: "col IN(x, y, z)„. Acest lucru este echivalent cu ” (col=x) sau (col=y) sau (col=z) ".
Exemplu:
Următoarea interogare va selecta numai studenții cu ID-uri 2, 4, 6, 8:
SELECT * FROM Students WHERE StudentId IN(2, 4, 6, 8);
Asa:
Interogarea anterioară va oferi rezultatul exact ca următoarea interogare, deoarece sunt echivalente:
SELECT * FROM Students WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);
Ambele interogări oferă rezultatul exact. Cu toate acestea, diferența dintre cele două interogări este că prima interogare am folosit 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) SAU (StudentId = 4) SAU (StudentId = 6) SAU (StudentId = 8);
Asa:
SQLite NU ÎN
Operandul „NOT IN” este opusul operatorului IN. Dar cu aceeași sintaxă; este nevoie de un operand și o listă de operanzi. Va returna true dacă valoarea primului operand nu este egală cu una dintre valoarea operanzilor din listă. adică, va returna true (0) dacă lista de operanzi nu conține primul operand. Ca aceasta: "col NOT IN(x, y, z)„. Aceasta este echivalentă cu „(col<>x) ȘI (col<>y) ȘI (col<>z)".
Exemplu:
Următoarea interogare va selecta studenți cu ID-uri care nu sunt egale cu unul dintre aceste ID-uri 2, 4, 6, 8:
SELECT * FROM Students WHERE StudentId NOT IN(2, 4, 6, 8);
Ca asta
Interogarea anterioară dăm rezultatul exact ca următoarea interogare, deoarece sunt echivalente:
SELECT * FROM Students WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Asa:
Î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ătoarele Id-uri 2, 4, 6 sau 8. Această interogare va returna toți ceilalți studenți în afară de aceste liste de Id-uri.
SQLite EXISTĂ
Operatorii EXISTS nu preiau operanzi; este nevoie doar de o clauză SELECT după ea. Operatorul EXISTS va returna true (1) dacă există rânduri returnate din clauza SELECT și va returna false (0) dacă nu există rânduri returnate de la clauza SELECT.
Exemplu:
În exemplul următor, vom selecta numele departamentului, dacă id-ul departamentului există în tabelul studenților:
SELECT DepartmentName FROM Departments AS d WHERE 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” va fi returnat. Și numele departamentului „matematica” nu va fi returnat deoarece nu există niciun student în acea secție, deci ID-ul departamentului nu există în tabelul studenților. De aceea operatorul EXISTS a ignorat „matematica” departament.
SQLite NU
Reversează rezultatul operatorului precedent care vine după el. De exemplu:
- NOT BETWEEN – Va returna true dacă BETWEEN returnează false și invers.
- NOT LIKE – Va returna true dacă LIKE returnează false și invers.
- NOT GLOB – Va returna true dacă GLOB returnează false și invers.
- NOT EXISTS – Va returna true dacă EXISTS returnează false ș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.
SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId);
producție:
Doar departamentul”matematica ” va fi returnat. Deoarece "matematica” catedra este singura catedra, care nu exista in tabelul studentilor.
Limitare și ordonare
SQLite Order
SQLite Ordinea este de a sorta rezultatul după una sau mai multe expresii. Pentru a ordona setul de rezultate, trebuie să utilizați clauza ORDER BY după cum urmează:
- În primul rând, 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 să ordonați datele, această expresie poate fi un nume de coloană sau o expresie.
- După expresie, puteți specifica o direcție opțională de sortare. Fie DESC, pentru a ordona datele descendent, fie ASC pentru a ordona datele crescător. Dacă nu ați specificat niciunul dintre ele, datele ar fi sortate crescător.
- Puteți specifica mai multe expresii folosind „,” între ele.
Exemplu
În exemplul următor, vom selecta toți studenții ordonați după nume, dar în ordine descrescătoare, apoi după numele departamentului în ordine crescătoare:
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;
Acest lucru vă va oferi:
- SQLite va ordona mai întâi toți studenții după numele departamentului î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
SQLite Limită:
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 folosind clauza OFFSET. De exemplu, "LIMITA 4 OFFSET 4” va ignora primele 4 rânduri și va returna 4 rânduri începând de la al cincilea rând, astfel încât veți obține rândurile 5,6,7, 8, XNUMX și XNUMX.
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 pornind de la id-ul studentului 5 folosind interogarea:
SELECT * FROM Students LIMIT 4,3;
Acest lucru vă va oferi doar trei studenți începând de la rândul 5. Prin urmare, vă va oferi rândurile cu StudentId 5, 6 și 7:
Eliminarea duplicatelor
Dacă interogarea dvs. SQL returnează valori duplicate, puteți utiliza „DISTINCT” pentru a elimina acele duplicate și a reveni pe valori distincte. Puteți specifica mai mult de o coloană după funcționarea tastei DISTINCT.
Exemplu:
Următoarea interogare va returna „valori nume de departament” duplicat: Aici avem valori duplicate cu nume IT, Fizică și Arte.
SELECT d.DepartmentName FROM Students AS s INNER JOIN Departments 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.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Acest lucru vă va oferi doar trei valori unice pentru coloana cu numele departamentului:
Agregat
SQLite Agregatele sunt funcții încorporate definite în SQLite care va grupa mai multe valori ale mai multor rânduri într-o singură valoare.
Iată agregatele susținute de SQLite:
SQLite AVG()
A returnat media pentru toate valorile x.
Exemplu:
În exemplul următor vom obține nota medie pe care o obțin elevii la toate examenele:
SELECT AVG(Mark) FROM Marks;
Aceasta vă va oferi valoarea „18.375”:
Aceste rezultate provin din însumarea tuturor valorilor notelor î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. Și iată câteva opțiuni pe care le puteți folosi cu COUNT:
- COUNT(x): Numărează numai valorile x, unde x este un nume de coloană. Va ignora valorile NULL.
- COUNT(*): Numără 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 sunt diferite:
SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;
Acest lucru vă va oferi:
După cum urmează:
- COUNT(DepartmentId) vă va oferi numărul tuturor ID-ului 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 cele trei valori diferite care sunt matematică, informatică și fizică.
- COUNT(*) numără numărul de rânduri 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ă mai multe valori î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 șir, cu virgula „,” folosită ca separator între valori. Valorile NULL vor fi ignorate.
- GROUP_CONCAT(X, Y): Aceasta va concatena valorile lui x într-un șir, 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 șir, cu virgula „,” folosită ca separator între valori. Valorile NULL vor fi ignorate.
GROUP_CONCAT(DepartmentName) Exemplu
Următoarea interogare va concatena toate valorile numelui departamentului din tabelul studenților și al departamentelor într-un șir separat prin virgulă. Deci, în loc să returnați o listă de valori, o valoare pe fiecare rând. Va returna o singură valoare pe un rând, cu toate valorile separate prin virgulă:
SELECT GROUP_CONCAT(d.DepartmentName) FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Acest lucru vă va oferi:
Aceasta vă va oferi lista cu valorile numelor de 8 departamente concatenate într-un șir separate prin virgulă.
GROUP_CONCAT(DISTINCT DepartmentName) Exemplu
Următoarea interogare va concatena valorile distincte ale numelui departamentului din tabelul studenți și departamente într-un șir separat prin virgulă:
SELECT GROUP_CONCAT(DISTINCT d.DepartmentName) FROM Students AS s INNER JOIN Departments 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ți și departamente într-un singur șir, dar cu caracterul „&” în loc de virgulă ca separator:
SELECT GROUP_CONCAT(d.DepartmentName, '&') FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Acest lucru vă va oferi:
Observați cum este folosit caracterul „&” în loc de caracterul implicit „,” pentru a separa valorile.
SQLite MAX() și MIN()
MAX(X) vă returnează cea mai mare valoare dintre 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 nota cea mai mare și nota cea mai mică de la „Marks" masa:
SELECT MAX(Mark), MIN(Mark) FROM Marks;
Acest lucru vă va oferi:
SQLite SUMA(x), Total(x)
Ambele vor returna suma tuturor valorilor x. Dar ele 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 notelor din „Marks" Mese:
SELECT SUM(Mark), TOTAL(Mark) FROM Marks;
Acest lucru vă va oferi:
După cum puteți vedea, TOTAL returnează întotdeauna o virgulă mobilă. Dar SUM returnează o valoare întreagă, deoarece valorile din coloana „Marcați” pot fi în numere întregi.
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 acel test. SUM returnează o valoare nulă ca un gol, în timp ce TOTAL returnează 0.
A se grupa cu
Clauza GROUP BY este folosită pentru a specifica una sau mai multe coloane care vor fi folosite 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 gruparea după coloane, puteți utiliza o funcție de agregare pentru aceasta.
Exemplu:
Următoarea interogare vă va oferi numărul total de studenți prezenți în fiecare departament.
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;
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 „catedra”, se va număra studenții pe ea.
clauza HAVING
Dacă doriți să filtrați grupurile returnate de clauza GROUP BY, atunci puteți specifica o clauză „HAVING” cu expresia după GROUP BY. Expresia va fi folosită pentru a filtra aceste grupuri.
Exemplu
În următoarea interogare, vom selecta acele departamente care au doar doi studenți:
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;
Acest lucru vă va oferi:
Clauza HAVING COUNT(S.StudentId) = 2 va filtra grupurile returnate și va returna numai 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 ieșire.
SQLite Interogare și subinterogare
În interiorul oricărei interogări, puteți utiliza o altă interogare fie într-o interogare SELECT, INSERT, DELETE, UPDATE sau în interiorul unei alte subinterogări.
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 folosi subinterogări cu instrucțiunile INSERT, DELETE și UPDATE.
Utilizarea subinterogării în exemplul clauzei FROM
În următoarea interogare vom include o subinterogare în clauza FROM:
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;
Interogarea:
SELECT StudentId, Mark FROM Tests AS t INNER JOIN Marks AS m ON t.TestId = m.TestId
Interogarea de mai sus este numită 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 elevilor și
- t.Mark este selectat din subinterogare; care dă note obţinute de fiecare dintre aceşti elevi
Folosind subinterogare în exemplul clauzei WHERE
În următoarea interogare vom include o subinterogare în clauza WHERE:
SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId);
Interogarea:
SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId
Interogarea de mai sus este numită subinterogare aici deoarece este imbricată în clauza WHERE. Subinterogarea va returna valorile DepartmentId care vor fi folosite de operatorul NOT EXISTS.
Această interogare vă va oferi:
În interogarea de mai sus, am selectat departamentul care nu are niciun student înscris în ea. Care este departamentul „Matematică” de aici.
set Opera– UNION,Intersect
SQLite acceptă următoarele operații SET:
UNIRE & UNIRE TOATE
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.
UNIREA Exemplu
În exemplul următor, vom obține lista cu DepartmentId din tabelul studenți și lista cu DepartmentId din tabelul departamente din aceeași coloană:
SELECT DepartmentId AS DepartmentIdUnioned FROM Students UNION SELECT DepartmentId FROM Departments;
Acest lucru vă va oferi:
Interogarea returnează doar 5 rânduri care sunt valori 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 cu DepartmentId din tabelul studenți și lista cu DepartmentId din tabelul departamente din aceeași coloană:
SELECT DepartmentId AS DepartmentIdUnioned FROM Students UNION ALL SELECT DepartmentId FROM Departments;
Acest lucru vă va oferi:
Interogarea va returna 14 rânduri, 10 rânduri din tabelul studenți și 4 din tabelul departamentelor. Rețineți că, există 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 all va da rezultate diferite dacă înlocuim UNION ALL cu UNION:
SQLite INTERSECT
Returnează valorile care există în ambele seturi 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ă în ambele tabele Students și Departments din coloana DepartmentId:
SELECT DepartmentId FROM Students Intersect SELECT DepartmentId FROM Departments;
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 null și 4 nu au fost incluse deoarece valoarea nulă există numai în tabelul studenți ș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 „EXCEPȚIE”. 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 departamente și nu există în tabelul studenți:
SELECT DepartmentId FROM Departments EXCEPT SELECT DepartmentId FROM Students;
Acest lucru vă va oferi:
Interogarea returnează doar valoarea 4. Care este singura valoare care există în tabelul departamente și nu există în tabelul studenți.
manipulare NULL
"NULL” valoarea este o valoare specială în SQLite. Este folosit pentru a reprezenta o valoare necunoscută sau care lipsește. Rețineți că valoarea nulă este total diferită de „0” sau valoare goală „”. Deoarece 0 și valoarea goală este o valoare cunoscută, totuși, valoarea nulă este necunoscută.
Valorile NULL necesită o manipulare specială SQLite, vom vedea acum cum să gestionăm valorile NULL.
Căutați valori NULL
Nu puteți utiliza operatorul normal de egalitate (=) pentru a căuta valorile nule. De exemplu, următoarea interogare caută studenții care au o valoare DepartmentId nulă:
SELECT * FROM Students WHERE DepartmentId = NULL;
Această interogare nu va da niciun rezultat:
Deoarece valoarea NULL nu este egală cu nicio altă valoare care include o valoare nulă în sine, de aceea nu a returnat niciun rezultat.
- Cu toate acestea, pentru a face interogarea să funcționeze, trebuie să utilizați „Este nulă” operator pentru a căuta valori nule după cum urmează:
SELECT * FROM Students WHERE DepartmentId IS NULL;
Acest lucru vă va oferi:
Interogarea va returna acei studenți care au o valoare DepartmentId nulă.
- Dacă doriți să obțineți acele valori care nu sunt nule, atunci trebuie să utilizați „NU ESTE NULL” operator ca acesta:
SELECT * FROM Students WHERE DepartmentId IS NOT NULL;
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 oricare dintre ele în funcție de anumite condiții. Pentru aceasta, condiția pentru valoarea respectivă ar trebui să fie adevărată pentru a fi selectată.
Expresia CASE va evalua aceste liste 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 folosi expresia CASE pentru a face asta.
Aceasta poate fi folosită 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 declarația if, de exemplu.
Operatorul CASE poate fi definit cu diferite sintaxe, după cum urmează:
- Puteți utiliza diferite condiții:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition3 THEN result3 … ELSE resultn END
- Sau, puteți folosi o singură expresie și puneți diferite valori posibile din care să alegeți:
CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 WHEN value3 THEN result3 … ELSE restuln END
Rețineți că clauza ELSE este opțională.
Exemplu
În exemplul următor, vom folosi CASE expresie cu NULL valoare în coloana ID departament din tabelul Studenți pentru a afișa textul „Fără departament” după cum urmează:
SELECT
StudentName,
CASE
WHEN DepartmentId IS NULL THEN 'No Department'
ELSE DepartmentId
END AS DepartmentId
FROM Students;
- Operatorul CASE va verifica valoarea DepartmentId-ului 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 comună de tabel
Expresiile comune de tabel (CTE) sunt subinterogări care sunt definite în interiorul instrucțiunii 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ă punând clauza WITH în fața unei instrucțiuni SELECT, după cum urmează:
WITH CTEname AS ( SELECT statement ) SELECT, UPDATE, INSERT, or update statement here FROM CTE
"Nume CTE” este orice nume pe care îl poți da pentru CTE, îl poți folosi pentru a te referi 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 mai târziu la o altă interogare:
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;
În această interogare, am definit un CTE și i-am dat numele „Toate departamentele„. Acest CTE a fost definit dintr-o interogare SELECT:
SELECT DepartmentId, DepartmentName FROM Departments
Apoi, după ce am definit CTE, l-am folosit în interogarea SELECT care urmează după el.
Rețineți că, expresiile comune de tabel nu afectează rezultatul interogării. Este o modalitate de a defini o vedere logică sau o subinterogare pentru a le reutiliza în aceeași interogare. Expresiile comune 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 obținem,
- Numele departamentelor cu toți studenții pentru fiecare departament
- Numele elevilor separat prin virgulă și
- Arată departamentul care are cel puțin trei studenți în ea
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;
Am adăugat un JOIN clauză pentru a obține DepartmentName din tabelul Departments. 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 departamente.
- GROUP_CONCAT pentru a concatena studenții pentru fiecare grup, separați prin virgulă î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 următorul:
Rezumat
Aceasta a fost o introducere în scris SQLite interogări și elementele de bază ale interogării bazei de date și cum puteți filtra datele returnate. Acum poți să-l scrii pe al tău SQLite întrebări.
