SQLite Upit: Odaberite, Gdje, LIMIT, OFFSET, Broj, Grupiraj po
Marcus Allen
Za pisanje SQL upita u SQLite baze podataka, morate znati kako rade klauzule SELECT, FROM, WHERE, GROUP BY, ORDER BY i LIMIT i kako ih koristiti.
Tijekom ovog tutorijala naučit ćete kako koristiti te rečenice i kako pisati SQLite klauzule.
Čitanje podataka s odabirom
Klauzula SELECT glavna je izjava koju koristite za postavljanje upita SQLite baza podataka. U klauzuli SELECT navodite što odabrati. Ali prije klauzule odabira, da vidimo odakle možemo odabrati podatke pomoću klauzule FROM.
Klauzula FROM koristi se za određivanje gdje želite odabrati podatke. U klauzuli from možete navesti jednu ili više tablica ili podupita iz kojih ćete odabrati podatke, kao što ćemo vidjeti kasnije u vodičima.
Imajte na umu da za sve sljedeće primjere morate pokrenuti sqlite3.exe i otvoriti vezu s oglednom bazom podataka kao protočnu:
Korak 1) U ovom koraku
- Otvorite Moje računalo i dođite do sljedećeg direktorija "C:\sqlite"I
- Zatim otvorite "sqlite3.exe"
Korak 2) Otvori bazu podataka “TutorialsSampleDB.db” sljedećom naredbom:
Sada ste spremni pokrenuti bilo koju vrstu upita u bazi podataka.
U klauzuli SELECT možete odabrati ne samo naziv stupca, već imate i puno drugih opcija za određivanje što odabrati. Kao što slijedi:
IZABERI *
Ova naredba će odabrati sve stupce iz svih referentnih tablica (ili podupita) u klauzuli FROM. Na primjer:
SELECT * FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Ovo će odabrati sve stupce iz tablica studenti i tablica odjela:
SELECT naziv tablice.*
Ovo će odabrati sve stupce samo iz tablice "tablename". Na primjer:
SELECT Students.* FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Ovo će odabrati sve stupce samo iz tablice učenika:
Doslovna vrijednost
Literalna vrijednost je konstantna vrijednost koja se može specificirati u naredbi select. Literalne vrijednosti možete normalno koristiti na isti način na koji koristite nazive stupaca u klauzuli SELECT. Ove literalne vrijednosti bit će prikazane za svaki red iz redaka koje je vratio SQL upit.
Evo nekoliko primjera različitih literalnih vrijednosti koje možete odabrati:
- Numerički literal – brojevi u bilo kojem formatu poput 1, 2.55, … itd.
- String literali – Bilo koji niz 'USA', 'ovo je ogledni tekst', … itd.
- NULL – vrijednost NULL.
- Current_TIME – Dat će vam trenutno vrijeme.
- CURRENT_DATE – ovo će vam dati trenutni datum.
Ovo može biti zgodno u nekim situacijama kada morate odabrati konstantnu vrijednost za sve vraćene retke. Na primjer, ako želite odabrati sve studente iz tablice Studenti, s novim stupcem pod nazivom zemlja koji sadrži vrijednost "SAD", možete učiniti sljedeće:
SELECT *, 'USA' AS Country FROM Students;
Ovo će vam dati sve stupce učenika, plus novi stupac "Država" poput ovog:
Imajte na umu da ovaj novi stupac Država zapravo nije novi stupac dodan u tablicu. To je virtualni stupac, kreiran u upitu za prikaz rezultata i neće se kreirati na tablici.
Imena i aliasi
Alias je novi naziv za stupac koji vam omogućuje odabir stupca s novim nazivom. Zamjenska imena stupaca navedena su pomoću ključne riječi "AS".
Na primjer, ako želite odabrati da se stupac StudentName vrati s "Student Name" umjesto "StudentName", možete mu dati pseudonim poput ovog:
SELECT StudentName AS 'Student Name' FROM Students;
Ovo će vam dati imena učenika s imenom “Student Name” umjesto “StudentName” ovako:
Imajte na umu da je naziv stupca i dalje "Ime studenta“; stupac StudentName je još uvijek isti, ne mijenja se po aliasu.
Alias neće promijeniti naziv stupca; samo će promijeniti ime za prikaz u klauzuli SELECT.
Također, imajte na umu da je ključna riječ "AS" izborna, možete staviti alias ime bez nje, otprilike ovako:
SELECT StudentName 'Student Name' FROM Students;
I dat će vam točno isti rezultat kao prethodni upit:
Također možete dati pseudonime tablicama, ne samo stupcima. S istom ključnom riječi "AS". Na primjer, možete učiniti sljedeće:
SELECT s.* FROM Students AS s;
Ovo će vam dati sve stupce u tablici Učenici:
Ovo može biti vrlo korisno ako se pridružujete više od jedne tablice; umjesto ponavljanja punog naziva tablice u upitu, svakoj tablici možete dati kratko zamjensko ime. Na primjer, u sljedećem upitu:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Ovaj će upit odabrati ime svakog studenta iz tablice "Studenti" s nazivom odjela iz tablice "Odjeli":
Međutim, isti se upit može napisati ovako:
SELECT s.StudentName, d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
- Tablici Students smo dali alias "s", a tablici odjela alias "d".
- Tada smo umjesto punog naziva tablice koristili njihove aliase za referencu na njih.
- INNER JOIN spaja dvije ili više tablica pomoću uvjeta. U našem smo primjeru spojili tablicu Studenti s tablicom Departments sa stupcem DepartmentId. Postoji i detaljno objašnjenje za INNER JOIN u "SQLite Joins” vodič.
Ovo će vam dati točan rezultat kao prethodni upit:
GDJE
Pisanje SQL upita samo pomoću klauzule SELECT s klauzulom FROM, kao što smo vidjeli u prethodnom odjeljku, dat će vam sve retke iz tablica. Međutim, ako želite filtrirati vraćene podatke, morate dodati klauzulu "WHERE".
Klauzula WHERE koristi se za filtriranje skupa rezultata koje vraća SQL upit. Ovako radi klauzula WHERE:
- U klauzuli WHERE možete navesti "izraz".
- Taj će izraz biti procijenjen za svaki redak vraćen iz tablica(a) navedenih u klauzuli FROM.
- Izraz će se procijeniti kao Boolean izraz, s rezultatom istinitim, lažnim ili null.
- Tada će se vratiti samo redovi za koje je izraz procijenjen s točnom vrijednošću, a oni s lažnim ili nultim rezultatima bit će zanemareni i neće biti uključeni u skup rezultata.
- Za filtriranje skupa rezultata korištenjem WHERE klauzule, morate koristiti izraze i operatore.
Popis operatera u SQLite i kako ih koristiti
U sljedećem odjeljku objasnit ćemo kako možete filtrirati pomoću izraza i operatora.
Izraz je jedna ili više literalnih vrijednosti ili stupaca međusobno kombiniranih s operatorom.
Imajte na umu da izraze možete koristiti i u klauzuli SELECT i u klauzuli WHERE.
U sljedećim primjerima isprobat ćemo izraze i operatore u klauzuli odabira i klauzuli WHERE. Kako bismo vam pokazali kako rade.
Postoje različite vrste izraza i operatora koje možete navesti na sljedeći način:
SQLite operator ulančavanja “||”
Ovaj se operator koristi za međusobno spajanje jedne ili više literalnih vrijednosti ili stupaca. Proizvodit će jedan niz rezultata iz svih spojenih literalnih vrijednosti ili stupaca. Na primjer:
SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName FROM Students;
Ovo će se spojiti u novi alias "StudentIdWithName"
- Literalna vrijednost niza "Id s imenom: "
- s vrijednošću "Studentska iskaznica” stupac i
- s vrijednošću od “Ime studenta" stupac
SQLite CAST operator:
CAST operator se koristi za pretvaranje vrijednosti iz jednog tipa podataka u drugi tip podataka.
Na primjer, ako imate numeričku vrijednost pohranjenu kao vrijednost niza poput ove ” '12.5' " i želite to pretvoriti u numeričku vrijednost, možete upotrijebiti CAST operator da to učinite ovako "ULOGE ('12.5' KAO STVARNO)“. Ili ako imate decimalnu vrijednost kao što je 12.5, a trebate dobiti samo cijeli broj, možete ga pretvoriti u cijeli broj poput ovog "CAST(12.5 AS INTEGER)".
Primjer
U sljedećoj naredbi pokušat ćemo pretvoriti različite vrijednosti u druge tipove podataka:
SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;
Ovo će vam dati:
Rezultat je sljedeći:
- CAST('12.5' AS REAL) – vrijednost '12.5' je vrijednost niza, bit će pretvorena u REAL vrijednost.
- CAST(12.5 AS INTEGER) – vrijednost 12.5 je decimalna vrijednost, pretvorit će se u cjelobrojnu vrijednost. Decimalni dio bit će skraćen i postaje 12.
SQLite Aritmetika Operatorovi:
Uzmite dvije ili više numeričkih literalnih vrijednosti ili numeričkih stupaca i vratite jednu numeričku vrijednost. Aritmetički operatori podržani u SQLite je:
|
Primjer:
U sljedećem primjeru isprobat ćemo pet aritmetičkih operatora s doslovnim numeričkim vrijednostima u istom
odaberite klauzulu:
SELECT 25+6, 25-6, 25*6, 25%6, 25/6;
Ovo će vam dati:
Primijetite kako smo ovdje koristili naredbu SELECT bez klauzule FROM. I ovo je dozvoljeno unutra SQLite sve dok odabiremo doslovne vrijednosti.
SQLite Operatori usporedbe
Usporedite dva operanda jedan s drugim i vratite true ili false na sljedeći način:
|
Imajte na umu da, SQLite izražava pravu vrijednost s 1, a lažnu s 0.
Primjer:
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 '<>';
Ovo će dati nešto poput ovoga:
SQLite Operatori podudaranja uzorka
"LIKE” – koristi se za usklađivanje uzoraka. Korištenje "Like“, možete tražiti vrijednosti koje odgovaraju uzorku navedenom pomoću zamjenskog znaka.
Operand s lijeve strane može biti ili literalna vrijednost niza ili stupac niza. Uzorak se može odrediti na sljedeći način:
- Sadrži uzorak. Na primjer, Ime učenika KAO '%a%' – ovo će tražiti imena učenika koja sadrže slovo “a” na bilo kojem mjestu u stupcu StudentName.
- Počinje s uzorkom. Na primjer, "Ime učenika LIKE 'a%'” – traženje imena učenika koja počinju na slovo “a”.
- Završava s uzorkom. Na primjer, "Ime studenta KAO '%a'” – Potražite imena učenika koja završavaju slovom “a”.
- Spajanje bilo kojeg pojedinačnog znaka u nizu pomoću podvlake "_". Na primjer, "Ime učenika KAO 'J___'” – Tražite imena učenika koja imaju 4 znaka. Mora započeti slovom "J" i može imati bilo koja druga tri znaka nakon slova "J".
Primjeri podudaranja uzoraka:
- Dobijte imena učenika koja počinju slovom 'j':
SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';
Rezultat:
- Neka imena učenika završavaju slovom 'y':
SELECT StudentName FROM Students WHERE StudentName LIKE '%y';
Rezultat:
- Dobijte imena učenika koja sadrže slovo 'n':
SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';
Rezultat:
“GLOB” – ekvivalentan je LIKE operatoru, ali GLOB razlikuje velika i mala slova, za razliku od LIKE operatora. Na primjer, sljedeće dvije naredbe vratit će različite rezultate:
SELECT 'Jack' GLOB 'j%'; SELECT 'Jack' LIKE 'j%';
Ovo će vam dati:
- Prva izjava vraća 0(false) jer operator GLOB razlikuje velika i mala slova, pa 'j' nije jednako 'J'. Međutim, druga izjava vratit će 1 (true) jer operator LIKE ne razlikuje velika i mala slova, pa je 'j' jednako 'J'.
Ostali operateri:
SQLite I
Logički operator koji kombinira jedan ili više izraza. Vratit će true samo ako svi izrazi daju vrijednost "true". Međutim, vratit će false samo ako svi izrazi daju "false" vrijednost.
Primjer:
Sljedeći upit tražit će studente koji imaju StudentId > 5 i StudentName počinje slovom N, vraćeni studenti moraju ispunjavati dva uvjeta:
SELECT * FROM Students WHERE (StudentId > 5) AND (StudentName LIKE 'N%');
Kao rezultat, na gornjoj snimci zaslona, to će vam dati samo "Nancy". Nancy je jedina studentica koja ispunjava oba uvjeta.
SQLite OR
Logički operator koji kombinira jedan ili više izraza, tako da ako jedan od kombiniranih operatora daje true, onda će vratiti true. Međutim, ako svi izrazi daju false, vratit će false.
Primjer:
Sljedeći upit tražit će studente koji imaju StudentId > 5 ili StudentName počinje slovom N, vraćeni studenti moraju ispunjavati barem jedan od uvjeta:
SELECT * FROM Students WHERE (StudentId > 5) OR (StudentName LIKE 'N%');
Ovo će vam dati:
Kao rezultat, na gornjoj snimci zaslona, ovo će vam dati ime učenika sa slovom "n" u imenu plus ID učenika koji ima vrijednost>5.
Kao što vidite, rezultat se razlikuje od upita s operatorom AND.
SQLite IZMEĐU
BETWEEN se koristi za odabir onih vrijednosti koje su unutar raspona od dvije vrijednosti. Na primjer, "X IZMEĐU Y I Z” će vratiti true (1) ako je vrijednost X između dvije vrijednosti Y i Z. U suprotnom, vratit će false (0). “X IZMEĐU Y I Z" je ekvivalentno "X >= Y I X <= Z“, X mora biti veći ili jednak Y, a X manji ili jednak Z.
Primjer:
U sljedećem primjeru upita, napisat ćemo upit za dobivanje učenika s Id vrijednošću između 5 i 8:
SELECT * FROM Students WHERE StudentId BETWEEN 5 AND 8;
Ovo će dati samo studentima s ID-ovima 5, 6, 7 i 8:
SQLite IN
Uzima jedan operand i popis operanda. Vratit će true ako je vrijednost prvog operanda jednaka jednoj od vrijednosti operanda s popisa. Operator IN vraća true (1) ako popis operanda sadrži prvu vrijednost operanda unutar svojih vrijednosti. U suprotnom će vratiti false (0).
Kao ovo: "stupac IN(x, y, z)“. Ovo je ekvivalentno " (col=x) ili (col=y) ili (col=z) ".
Primjer:
Sljedeći upit će odabrati samo studente s ID-ovima 2, 4, 6, 8:
SELECT * FROM Students WHERE StudentId IN(2, 4, 6, 8);
Kao ovo:
Prethodni upit će dati točan rezultat kao sljedeći upit jer su ekvivalentni:
SELECT * FROM Students WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);
Oba upita daju točan rezultat. Međutim, razlika između ova dva upita je što smo prvi upit koristili operatorom "IN". U drugom upitu koristili smo više operatora "ILI".
IN operator je ekvivalentan korištenju višestrukih OR operatora. "WHERE StudentId IN(2, 4, 6, 8)”je ekvivalentno ” WHERE (Id učenika = 2) ILI (Id učenika = 4) ILI (Id učenika = 6) ILI (Id učenika = 8);"
Kao ovo:
SQLite NE U
Operand “NOT IN” je suprotan operatoru IN. Ali s istom sintaksom; potreban je jedan operand i lista operanda. Vratit će true ako vrijednost prvog operanda nije jednaka vrijednosti jednog od operanda s popisa. tj. vratit će true (0) ako popis operanda ne sadrži prvi operand. Kao ovo: "stupac NIJE IN(x, y, z)“. Ovo je ekvivalentno "(kol<>x) I (kol<>y) I (kol<>z)".
Primjer:
Sljedeći upit će odabrati studente s ID-ovima koji nisu jednaki jednom od ovih ID-ova 2, 4, 6, 8:
SELECT * FROM Students WHERE StudentId NOT IN(2, 4, 6, 8);
Ovako
Prethodnom upitu dajemo točan rezultat kao sljedeći upit jer su ekvivalentni:
SELECT * FROM Students WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Kao ovo:
Na gornjoj snimci zaslona,
Koristili smo više nejednakih operatora “<>” da dobijemo popis učenika koji nisu jednaki nijednom od sljedećih ID-ova 2, 4, 6 ili 8. Ovaj upit će vratiti sve ostale studente osim ovih popisa ID-ova.
SQLite POSTOJI
Operatori EXISTS ne uzimaju nikakve operande; potrebna je samo SELECT klauzula nakon njega. Operator EXISTS će vratiti true (1) ako ima redaka vraćenih iz klauzule SELECT, i vratit će false (0) ako uopće nema redaka vraćenih iz klauzule SELECT.
Primjer:
U sljedećem primjeru odabrat ćemo naziv odjela, ako ID odjela postoji u tablici studenata:
SELECT DepartmentName FROM Departments AS d WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);
Ovo će vam dati:
Samo tri odjela"IT, fizika i umjetnost” bit će vraćeno. I naziv odjela "matematika” neće biti vraćeno jer u tom odjelu nema učenika, pa Id odjela ne postoji u tablici učenika. Zato je operator EXISTS zanemario “matematika” odjel.
SQLite NE
Revpredstavlja rezultat prethodnog operatora koji dolazi nakon njega. Na primjer:
- NOT BETWEEN – Vratit će true ako BETWEEN vrati false i obrnuto.
- NOT LIKE – Vratit će true ako LIKE vrati false i obrnuto.
- NIJE GLOB – Vratit će true ako GLOB vrati false i obrnuto.
- NOT EXISTS – Vratit će true ako EXISTS vrati false i obrnuto.
Primjer:
U sljedećem primjeru koristit ćemo operator NOT s operatorom EXISTS kako bismo dobili nazive odjela koji ne postoje u tablici Studenti, što je obrnuti rezultat operatora EXISTS. Dakle, pretraga će se vršiti kroz DepartmentId koji ne postoji u tablici odjela.
SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId);
Izlaz:
Samo odjel “matematika ” bit će vraćeno. Jer "matematika” odjel je jedini odjel koji ne postoji u tablici učenika.
Ograničenje i naručivanje
SQLite Kako
SQLite Redoslijed je sortiranje rezultata prema jednom ili više izraza. Da biste poredali skup rezultata, morate koristiti klauzulu ORDER BY na sljedeći način:
- Prvo, morate navesti klauzulu ORDER BY.
- Klauzula ORDER BY mora biti navedena na kraju upita; samo klauzula LIMIT može biti specificirana nakon njega.
- Odredite izraz po kojem ćete poredati podatke, ovaj izraz može biti naziv stupca ili izraz.
- Nakon izraza možete navesti izborni smjer sortiranja. Ili DESC, za redoslijed podataka silazno ili ASC za poredak podataka uzlazno. Ako niste naveli nijedan od njih, podaci bi bili poredani uzlazno.
- Možete navesti više izraza koristeći "," između sebe.
Primjer
U sljedećem primjeru odabrat ćemo sve studente poredane prema njihovim imenima, ali silaznim redoslijedom, zatim prema nazivu odjela u rastućem redoslijedu:
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;
Ovo će vam dati:
- SQLite prvo će poredati sve učenike prema nazivu odjela uzlaznim redoslijedom
- Tada će za svaki naziv odjela biti prikazani svi studenti pod tim nazivom odjela prema njihovim imenima silaznim redoslijedom
SQLite Ograničiti:
Možete ograničiti broj redaka koje vraća vaš SQL upit pomoću klauzule LIMIT. Na primjer, LIMIT 10 će vam dati samo 10 redaka i zanemariti sve ostale retke.
U klauzuli LIMIT možete odabrati određeni broj redaka počevši od određene pozicije pomoću klauzule OFFSET. Na primjer, "OGRANIČENJE 4 POMAK 4” će zanemariti prva 4 retka i vratiti 4 retka počevši od petog reda, tako da ćete dobiti retke 5,6,7 i 8.
Imajte na umu da klauzula OFFSET nije obavezna, možete je napisati kao "OGRANIČENJE 4, 4” i to će vam dati točne rezultate.
Primjer:
U sljedećem primjeru vratit ćemo samo 3 učenika počevši od ID-a učenika 5 pomoću upita:
SELECT * FROM Students LIMIT 4,3;
Ovo će vam dati samo tri učenika počevši od retka 5. Dakle, to će vam dati retke s StudentId 5, 6 i 7:
Uklanjanje duplikata
Ako vaš SQL upit vraća duplicirane vrijednosti, možete koristiti "DISTINCT” za uklanjanje tih duplikata i povratak na različite vrijednosti. Možete navesti više od jednog stupca nakon rada ključa DISTINCT.
Primjer:
Sljedeći upit vratit će duple "vrijednosti naziva odjela": Ovdje imamo duple vrijednosti s nazivima IT, Physics i Arts.
SELECT d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Ovo će vam dati duplicirane vrijednosti za naziv odjela:
Primijetite kako postoje dvostruke vrijednosti za naziv odjela. Sada ćemo koristiti ključnu riječ DISTINCT s istim upitom kako bismo uklonili te duplikate i dobili samo jedinstvene vrijednosti. Kao ovo:
SELECT DISTINCT d.DepartmentName FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
To će vam dati samo tri jedinstvene vrijednosti za stupac naziva odjela:
skupina
SQLite Agregati su ugrađene funkcije definirane u SQLite koji će grupirati više vrijednosti više redaka u jednu vrijednost.
Ovdje su podržani agregati SQLite:
SQLite AVG()
Vraćen je prosjek za sve x vrijednosti.
Primjer:
U sljedećem primjeru dobit ćemo prosječnu ocjenu koju će studenti dobiti na svim ispitima:
SELECT AVG(Mark) FROM Marks;
To će vam dati vrijednost "18.375":
Ovi rezultati dolaze iz zbroja svih vrijednosti ocjena podijeljenog njihovim brojem.
COUNT() – COUNT(X) ili COUNT(*)
Vraća ukupan broj pojavljivanja x vrijednosti. Evo nekoliko opcija koje možete koristiti s COUNT:
- COUNT(x): Broji samo x vrijednosti, gdje je x naziv stupca. Ignorirat će NULL vrijednosti.
- COUNT(*): Broji sve retke iz svih stupaca.
- BROJ (DISTINCT x): možete navesti ključnu riječ DISTINCT prije x koja će dobiti broj različitih vrijednosti x.
Primjer
U sljedećem primjeru dobit ćemo ukupan broj odjela s COUNT(DepartmentId), COUNT(*) i COUNT(DISTINCT DepartmentId) i po čemu se oni razlikuju:
SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;
Ovo će vam dati:
Kao što slijedi:
- COUNT(DepartmentId) će vam dati broj svih ID-ova odjela, a zanemarit će nulte vrijednosti.
- COUNT(DISTINCT DepartmentId) daje vam različite vrijednosti DepartmentId-a, koje su samo 3. To su tri različite vrijednosti naziva odjela. Primijetite da postoji 8 vrijednosti naziva odjela u imenu učenika. Ali samo različite tri vrijednosti koje su matematika, informatika i fizika.
- COUNT(*) broji broj redaka u tablici učenika koji su 10 redaka za 10 učenika.
GROUP_CONCAT() – GROUP_CONCAT(X) ili GROUP_CONCAT(X,Y)
Skupna funkcija GROUP_CONCAT spaja višestruke vrijednosti u jednu vrijednost sa zarezom koji ih razdvaja. Ima sljedeće mogućnosti:
- GROUP_CONCAT(X): Ovo će ulančati sve vrijednosti x u jedan niz, sa zarezom “,” koji se koristi kao razdjelnik između vrijednosti. NULL vrijednosti će se zanemariti.
- GROUP_CONCAT(X, Y): Ovo će ulančati vrijednosti x u jedan niz, s vrijednošću y koja se koristi kao razdjelnik između svake vrijednosti umjesto zadanog razdjelnika ','. NULL vrijednosti također će biti zanemarene.
- GROUP_CONCAT(DISTINCT X): ovo će spojiti sve različite vrijednosti x u jedan niz, sa zarezom “,” koji se koristi kao razdjelnik između vrijednosti. NULL vrijednosti će se zanemariti.
GROUP_CONCAT(DepartmentName) Primjer
Sljedeći upit spojit će sve vrijednosti naziva odjela iz tablice studenata i odjela u jedan niz odvojen zarezom. Dakle, umjesto vraćanja popisa vrijednosti, jedna vrijednost u svakom retku. Vratit će samo jednu vrijednost u jednom retku, sa svim vrijednostima odvojenim zarezom:
SELECT GROUP_CONCAT(d.DepartmentName) FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Ovo će vam dati:
Ovo će vam dati popis od 8 vrijednosti naziva odjela spojenih u jedan niz odvojenih zarezima.
GROUP_CONCAT(DISTINCT DepartmentName) Primjer
Sljedeći upit spojit će različite vrijednosti naziva odjela iz tablice studenata i odjela u jedan niz odvojen zarezima:
SELECT GROUP_CONCAT(DISTINCT d.DepartmentName) FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Ovo će vam dati:
Primijetite kako se rezultat razlikuje od prethodnog rezultata; vraćene su samo tri vrijednosti koje su različita imena odjela, a duplicirane vrijednosti su uklonjene.
GROUP_CONCAT(DepartmentName ,'&') Primjer
Sljedeći upit spojit će sve vrijednosti stupca naziva odjela iz tablice studenata i odjela u jedan niz, ali sa znakom '&' umjesto zarezom kao razdjelnikom:
SELECT GROUP_CONCAT(d.DepartmentName, '&') FROM Students AS s INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Ovo će vam dati:
Primijetite kako se znak "&" koristi umjesto zadanog znaka "," za razdvajanje između vrijednosti.
SQLite MAX() & MIN()
MAX(X) vraća najveću vrijednost od X vrijednosti. MAX će vratiti NULL vrijednost ako su sve vrijednosti x null. Dok MIN(X) vraća najmanju vrijednost od X vrijednosti. MIN će vratiti NULL vrijednost ako su sve vrijednosti X null.
Primjer
U sljedećem upitu koristit ćemo funkcije MIN i MAX da dobijemo najvišu i najnižu ocjenu iz "Marks” tablica:
SELECT MAX(Mark), MIN(Mark) FROM Marks;
Ovo će vam dati:
SQLite SUM(x), Ukupno(x)
Obje će vratiti zbroj svih x vrijednosti. Ali razlikuju se u sljedećem:
- SUM će vratiti null ako su sve vrijednosti null, ali Total će vratiti 0.
- TOTAL uvijek vraća vrijednosti s pomičnim zarezom. SUM vraća vrijednost cijelog broja ako su sve vrijednosti x cijeli broj. Međutim, ako vrijednosti nisu cijeli broj, vratit će vrijednost s pomičnim zarezom.
Primjer
U sljedećem upitu koristit ćemo SUM i total kako bismo dobili zbroj svih ocjena u "Marks” stolovi:
SELECT SUM(Mark), TOTAL(Mark) FROM Marks;
Ovo će vam dati:
Kao što vidite, TOTAL uvijek vraća pokretni zarez. Ali SUM vraća vrijednost cijelog broja jer vrijednosti u stupcu "Označi" mogu biti u cijelim brojevima.
Primjer razlike između SUM i TOTAL:
U sljedećem upitu pokazat ćemo razliku između SUM i TOTAL kada dobiju SUM vrijednosti NULL:
SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;
Ovo će vam dati:
Imajte na umu da nema ocjena za TestId = 4, tako da postoje nulte vrijednosti za taj test. SUM vraća nultu vrijednost kao prazno mjesto, dok TOTAL vraća 0.
Grupiraj BY
Klauzula GROUP BY koristi se za određivanje jednog ili više stupaca koji će se koristiti za grupiranje redaka u grupe. Redovi s istim vrijednostima bit će skupljeni (raspoređeni) u grupe.
Za bilo koji drugi stupac koji nije uključen u grupu po stupcima, možete koristiti agregatnu funkciju za njega.
Primjer:
Sljedeći upit će vam dati ukupan broj učenika prisutnih u svakom odjelu.
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;
Ovo će vam dati:
Klauzula GROUPBY DepartmentName će grupirati sve učenike u grupe po jednu za svaki naziv odjela. Za svaku grupu “odjela” računat će studente na njoj.
HAVING klauzula
Ako želite filtrirati grupe koje vraća klauzula GROUP BY, tada možete navesti klauzulu "HAVING" s izrazom nakon GROUP BY. Izraz će se koristiti za filtriranje ovih grupa.
Primjer
U sljedećem upitu ćemo odabrati one odjele koji imaju samo dva studenta:
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;
Ovo će vam dati:
Klauzula HAVING COUNT(S.StudentId) = 2 će filtrirati vraćene grupe i vratiti samo one grupe koje sadrže točno dva učenika. U našem slučaju, umjetnički odjel ima 2 studenta, tako da je prikazan u ispisu.
SQLite Upit i podupit
Unutar bilo kojeg upita, možete koristiti drugi upit bilo u SELECT, INSERT, DELETE, UPDATE ili unutar drugog podupita.
Ovaj ugniježđeni upit naziva se podupit. Sada ćemo vidjeti neke primjere korištenja podupita u klauzuli SELECT. Međutim, u vodiču za modificiranje podataka vidjet ćemo kako možemo koristiti podupit s naredbom INSERT, DELETE i UPDATE.
Korištenje podupita u primjeru FROM klauzule
U sljedeći upit uključit ćemo podupit unutar klauzule 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;
Upit:
SELECT StudentId, Mark FROM Tests AS t INNER JOIN Marks AS m ON t.TestId = m.TestId
Gore navedeni upit ovdje se naziva podupit jer je ugniježđen unutar klauzule FROM. Primijetite da smo mu dali pseudonim "t" kako bismo se mogli pozvati na stupce koje je vratio u upitu.
Ovaj upit će vam dati:
Dakle, u našem slučaju,
- s.StudentName odabire se iz glavnog upita koji daje imena studenata i
- t.Mark je odabran iz podupita; koji daje ocjene koje je postigao svaki od ovih učenika
Korištenje podupita u primjeru WHERE klauzule
U sljedeći upit ćemo uključiti podupit u klauzulu WHERE:
SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId);
Upit:
SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId
Gore navedeni upit ovdje se naziva podupit jer je ugniježđen u klauzulu WHERE. Podupit će vratiti vrijednosti DepartmentId koje će koristiti operator NOT EXISTS.
Ovaj upit će vam dati:
U gornjem upitu odabrali smo odjel na kojem nema upisanog studenta. Što je ovdje odjel za matematiku.
Postaviti Operacije – UNION, Intersect
SQLite podržava sljedeće SET operacije:
UNIJA & UNION SVI
Kombinira jedan ili više skupova rezultata (grupa redaka) vraćenih iz više naredbi SELECT u jedan skup rezultata.
UNION će vratiti različite vrijednosti. Međutim, UNION ALL neće i uključivat će duplikate.
Imajte na umu da će naziv stupca biti naziv stupca naveden u prvoj izjavi SELECT.
Primjer UNIJE
U sljedećem primjeru dobit ćemo popis DepartmentId iz tablice studenata i popis DepartmentId iz tablice odjela u istom stupcu:
SELECT DepartmentId AS DepartmentIdUnioned FROM Students UNION SELECT DepartmentId FROM Departments;
Ovo će vam dati:
Upit vraća samo 5 redaka koji su različite vrijednosti ID-a odjela. Primijetite prvu vrijednost koja je nulta vrijednost.
SQLite UNIJA SVE Primjer
U sljedećem primjeru dobit ćemo popis DepartmentId iz tablice studenata i popis DepartmentId iz tablice odjela u istom stupcu:
SELECT DepartmentId AS DepartmentIdUnioned FROM Students UNION ALL SELECT DepartmentId FROM Departments;
Ovo će vam dati:
Upit će vratiti 14 redaka, 10 redaka iz tablice učenika i 4 iz tablice odjela. Imajte na umu da postoje duplikati u vraćenim vrijednostima. Također imajte na umu da je naziv stupca bio onaj naveden u prvoj izjavi SELECT.
Sada, da vidimo kako će UNION all dati različite rezultate ako zamijenimo UNION ALL s UNION:
SQLite PRESJEKATI
Vraća vrijednosti koje postoje u oba kombinirana skupa rezultata. Vrijednosti koje postoje u jednom od kombiniranih skupova rezultata bit će zanemarene.
Primjer
U sljedećem upitu odabrat ćemo vrijednosti DepartmentId koje postoje u tablicama Studenti i Odjeli u stupcu DepartmentId:
SELECT DepartmentId FROM Students Intersect SELECT DepartmentId FROM Departments;
Ovo će vam dati:
Upit vraća samo tri vrijednosti 1, 2 i 3. To su vrijednosti koje postoje u objema tablicama.
Međutim, vrijednosti null i 4 nisu uključene jer vrijednost null postoji samo u tablici učenika, a ne u tablici odjela. A vrijednost 4 postoji u tablici odjela, a ne u tablici učenika.
Zbog toga su obje vrijednosti NULL i 4 zanemarene i nisu uključene u vraćene vrijednosti.
OSIM
Pretpostavimo da imate dva popisa redaka, popis1 i popis2, i želite retke samo s popisa1 koji ne postoji na popisu2, možete koristiti klauzulu “EXCEPT”. Klauzula EXCEPT uspoređuje dva popisa i vraća one retke koji postoje u popisu1, a ne postoje u popisu2.
Primjer
U sljedećem upitu odabrat ćemo vrijednosti DepartmentId koje postoje u tablici odjela i ne postoje u tablici studenata:
SELECT DepartmentId FROM Departments EXCEPT SELECT DepartmentId FROM Students;
Ovo će vam dati:
Upit vraća samo vrijednost 4. To je jedina vrijednost koja postoji u tablici odjela, a ne postoji u tablici učenika.
NULL rukovanje
"NULL” vrijednost je posebna vrijednost u SQLite. Koristi se za predstavljanje vrijednosti koja je nepoznata ili nedostaje. Imajte na umu da je null vrijednost potpuno drugačija od "0” ili prazna vrijednost „”. Međutim, budući da su 0 i prazna vrijednost poznata vrijednost, nulta vrijednost je nepoznata.
NULL vrijednosti zahtijevaju posebno rukovanje SQLite, sada ćemo vidjeti kako postupati s NULL vrijednostima.
Traži NULL vrijednosti
Ne možete koristiti normalni operator jednakosti (=) za pretraživanje nultih vrijednosti. Na primjer, sljedeći upit traži studente koji imaju vrijednost null DepartmentId:
SELECT * FROM Students WHERE DepartmentId = NULL;
Ovaj upit neće dati nikakav rezultat:
Budući da NULL vrijednost nije jednaka nijednoj drugoj vrijednosti koja uključuje samu null vrijednost, to je razlog zašto nije vratio nikakav rezultat.
- Međutim, kako bi upit funkcionirao, morate koristiti "JE NULL" operator za traženje nulte vrijednosti na sljedeći način:
SELECT * FROM Students WHERE DepartmentId IS NULL;
Ovo će vam dati:
Upit će vratiti one studente koji imaju nultu vrijednost DepartmentId.
- Ako želite dobiti one vrijednosti koje nisu null, tada morate koristiti "NIJE NULL” ovako:
SELECT * FROM Students WHERE DepartmentId IS NOT NULL;
Ovo će vam dati:
Upit će vratiti one studente koji nemaju vrijednost NULL DepartmentId.
Uvjetni rezultati
Ako imate popis vrijednosti i želite odabrati bilo koju od njih na temelju nekih uvjeta. Za to bi uvjet za tu određenu vrijednost trebao biti istinit kako bi bio odabran.
CASE izraz će procijeniti ovaj popis uvjeta za sve vrijednosti. Ako je uvjet istinit, vratit će tu vrijednost.
Na primjer, ako imate stupac "Ocjena" i želite odabrati tekstualnu vrijednost na temelju vrijednosti ocjene na sljedeći način:
– “Odličan” ako je ocjena veća od 85.
– “Vrlo dobar” ako je ocjena između 70 i 85.
– “Dobar” ako je ocjena između 60 i 70.
Tada možete koristiti izraz CASE da to učinite.
Ovo se može koristiti za definiranje neke logike u SELECT klauzuli tako da možete odabrati određene rezultate ovisno o određenim uvjetima kao što je na primjer if izjava.
CASE operator može se definirati različitim sintaksama na sljedeći način:
- Možete koristiti različite uvjete:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition3 THEN result3 … ELSE resultn END
- Ili, možete koristiti samo jedan izraz i staviti različite moguće vrijednosti za odabir:
CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 WHEN value3 THEN result3 … ELSE restuln END
Imajte na umu da je klauzula ELSE opcionalna.
Primjer
U sljedećem primjeru koristit ćemo SLUČAJ izraz sa NULL vrijednost u stupcu Id odjela u tablici Studenti za prikaz teksta 'Bez odjela' na sljedeći način:
SELECT
StudentName,
CASE
WHEN DepartmentId IS NULL THEN 'No Department'
ELSE DepartmentId
END AS DepartmentId
FROM Students;
- Operator CASE će provjeriti vrijednost DepartmentId-a je li null ili nije.
- Ako je vrijednost NULL, tada će odabrati literalnu vrijednost 'No Department' umjesto vrijednosti DepartmentId.
- Ako nije vrijednost null, tada će odabrati vrijednost stupca DepartmentId.
Ovo će vam dati izlaz kao što je prikazano u nastavku:
Uobičajeni tablični izraz
Uobičajeni tablični izrazi (CTE) su podupiti koji su definirani unutar SQL naredbe s danim imenom.
Ima prednost nad podupitima jer je definiran iz SQL naredbi i učinit će upite lakšim za čitanje, održavanje i razumijevanje.
Uobičajeni tablični izraz može se definirati stavljanjem klauzule WITH ispred naredbi SELECT na sljedeći način:
WITH CTEname AS ( SELECT statement ) SELECT, UPDATE, INSERT, or update statement here FROM CTE
"CTEname” je bilo koje ime koje možete dati za CTE, možete ga koristiti za kasnije pozivanje na njega. Imajte na umu da možete definirati naredbu SELECT, UPDATE, INSERT ili DELETE na CTE-ovima
Sada, da vidimo primjer kako koristiti CTE u SELECT klauzuli.
Primjer
U sljedećem primjeru definirat ćemo CTE iz naredbe SELECT, a zatim ćemo ga kasnije upotrijebiti u drugom upitu:
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;
U ovom smo upitu definirali CTE i dali mu naziv "Svi odjeli“. Ovaj CTE je definiran iz SELECT upita:
SELECT DepartmentId, DepartmentName FROM Departments
Nakon što smo definirali CTE, upotrijebili smo ga u SELECT upitu koji dolazi nakon njega.
Imajte na umu da Uobičajeni izrazi tablice ne utječu na izlaz upita. To je način definiranja logičkog pogleda ili podupita kako bi se ponovno upotrijebili u istom upitu. Uobičajeni tablični izrazi su poput varijable koju deklarirate i ponovno je koristite kao podupit. Samo izjava SELECT utječe na izlaz upita.
Ovaj upit će vam dati:
Napredni upiti
Napredni upiti su oni upiti koji sadrže složene spojeve, podupite i neke agregate. U sljedećem odjeljku vidjet ćemo primjer naprednog upita:
Gdje dobivamo,
- Nazivi odjela sa svim studentima za svaki odjel
- Imena učenika odvojena zarezom i
- Prikazuje odjel s najmanje tri učenika
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;
Dodali smo a PRIDRUŽITE klauzula za dobivanje DepartmentName iz tablice Departments. Nakon toga dodali smo klauzulu GROUP BY s dvije agregatne funkcije:
- “BROJ” za brojanje učenika za svaku grupu odjela.
- GROUP_CONCAT za spajanje učenika za svaku grupu s zarezom odvojenim u jednom nizu.
- Nakon GROUP BY, koristili smo klauzulu HAVING za filtriranje odjela i odabir samo onih odjela koji imaju najmanje 3 učenika.
Rezultat će biti sljedeći:
Rezime
Ovo je bio uvod u pisanje SQLite upite i osnove postavljanja upita bazi podataka te kako možete filtrirati vraćene podatke. Sada možete napisati svoje SQLite upita.
