SQLite Upit: Odaberite, Gdje, LIMIT, OFFSET, Broj, Grupiraj po

U donjim odjeljcima opisano je čitanje podataka pomoću SELECT i FROM, filtriranje redaka pomoću WHERE i njegovih operatora, sortiranje i ograničavanje rezultata, agregacijske funkcije, GROUP BY i HAVING, podupiti, operacije s skupovima, rukovanje NULL vrijednostima, CASE izrazi i uobičajeni tablični izrazi.

Napisati 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 idite do sljedećeg direktorija "C:\sqlite", a zatim otvorite "sqlite3.exe":

Korak 2) Otvorite 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 „ImeStudenta“; stupac ImeStudenta je i dalje isti, ne mijenja se zbog aliasa.

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 Studenti dali smo alias "s", a tablici odjela alias "d". Zatim smo umjesto korištenja punog naziva tablice koristili njihove aliase za pozivanje na njih. INNER JOIN spaja dvije ili više tablica pomoću uvjeta. U našem primjeru, spojili smo tablicu Studenti s tablicom Odjela sa stupcem DepartmentId. Također postoji detaljno objašnjenje za INNER JOIN u "SQLite Pridružuje"tutorial".

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 se koristi za filtriranje skupa rezultata koje vraća SQL upit. Ovako funkcionira 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“:

• Doslovna vrijednost niza znakova „Id s nazivom: “
• s vrijednošću stupca „StudentId“ i
• s vrijednošću iz stupca "ImeStudenta"

SQLite CAST operator:

CAST operator se koristi za pretvaranje vrijednosti iz tip podataka na drugi tip podataka.

Na primjer, ako imate numeričku vrijednost pohranjenu kao niz znakova poput ovog „'12.5'” i želite je pretvoriti u numeričku vrijednost, možete upotrijebiti operator CAST da biste to učinili ovako „CAST('12.5' ​​AS REAL)“. Ili ako imate decimalnu vrijednost poput 12.5 i trebate dobiti samo cjelobrojni dio, možete je 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:

• Zbrajanje "+" – daje zbroj dvaju operanda.
• ispodtraccija “–” – podnaslovtracts dva operanda i rezultira razlikom.
• Množenje "*" – produkt dvaju operanda.
• Podsjetnik (modulo) "%" – daje ostatak koji se dobije dijeljenjem jednog operanda s drugim operandom.
• Dijeljenje "/" – vraća količnik dijeljenja lijevog operanda desnim operandom.

Primjer:

U sljedećem primjeru isprobat ćemo pet aritmetičkih operatora s doslovnim numeričkim vrijednostima u istoj select klauzuli:

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:

• „<“ – vraća vrijednost true ako je lijevi operand manji od desnog operanda.
• „<=“ – vraća vrijednost true ako je lijevi operand manji ili jednak desnom operandu.
• „>“ – vraća vrijednost true ako je lijevi operand veći od desnog operanda.
• „>=" – vraća vrijednost true ako je lijevi operand veći ili jednak desnom operandu.
• „=“ i „==“ – vraća vrijednost true ako su dva operanda jednaka. Imajte na umu da su oba operatora ista i da nema razlike među njima.
• „!=" i „<>" – vraća vrijednost true ako dva operanda nisu jednaka. Imajte na umu da su oba operatora ista i da nema razlike među njima.

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. Pomoću „Like“ možete pretraživati ​​vrijednosti koje odgovaraju uzorku određenom 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, ImeStudenta LIKE '%a%' – ovo će tražiti imena studenata koja sadrže slovo "a" na bilo kojoj poziciji u stupcu ImeStudenta.
• Počinje s uzorkom. Na primjer, „ImeUčenika KAO 'a%'“ – traži imena učenika koja počinju slovom „a“.
• Završava uzorkom. Na primjer, „ImeUčenika KAO '%a'” – Pretražite imena učenika koja završavaju slovom „a“.
• Pronalaženje bilo kojeg pojedinačnog znaka u nizu pomoću podcrte "_". Na primjer, "ImeStudenta KAO 'J___'" - Traži imena studenata duljine 4 znaka. Mora započeti slovom "J" i može imati bilo koja tri dodatna 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“ – ekvivalentno je operatoru LIKE, ali GLOB razlikuje velika i mala slova, za razliku od operatora LIKE. 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 vrijednosti koje su unutar raspona od dvije vrijednosti. Na primjer, „X BETWEEN Y AND Z“ vratit će true (1) ako je vrijednost X između dvije vrijednosti Y i Z. U suprotnom, vratit će false (0). „X BETWEEN Y AND Z“ je ekvivalentno „X >= Y AND X <= Z“, X mora biti veći ili jednak Y, a X je 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).

Ovako: „col IN(x, y, z)“. Ovo je ekvivalentno sa „(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".

Operator IN ekvivalentan je korištenju više operatora OR. Operator "WHERE StudentId IN(2, 4, 6, 8)" ekvivalentan je operatoru "WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);"

Kao ovo:

SQLite NE U

Operand „NOT IN“ je suprotan operatoru IN. Ali s istom sintaksom; uzima jedan operand i popis operanada. Vratit će vrijednost true ako vrijednost prvog operanda nije jednaka vrijednosti jedne od operanada s popisa, tj. vratit će vrijednost true (0) ako popis operanada ne sadrži prvi operand. Ovako: „col NOT IN(x, y, z)“. Ovo je ekvivalentno operatoru „(col<>x) AND (col<>y) AND (col<>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:

Vratit će se samo tri odjela: „IT, Fizika i Umjetnost“. Naziv odjela „Matematika“ neće se vratiti jer u tom odjelu nema studenata, pa ID odjela ne postoji u tablici studenata. Zato je operator EXISTS ignorirao odjel „Matematika“.

SQLite NE

Reverses je 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:

Vratit će se samo odjel "Matematika". Budući da je odjel "Matematika" jedini odjel, on ne postoji u tablici studenata.

Ograničenje i naručivanje

SQLite Narudžba

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, „LIMIT 4 OFFSET 4“ će zanemariti prva 4 retka i vratiti 4 retka počevši od petog retka, pa ćete dobiti retke 5, 6, 7 i 8.

Imajte na umu da je klauzula OFFSET opcionalna, možete je napisati poput „LIMIT 4, 4“ i dat će vam 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 upotrijebiti ključnu riječ "DISTINCT" za uklanjanje tih duplikata i vraćanje različitih vrijednosti. Možete navesti više od jednog stupca nakon što ključ DISTINCT funkcionira.

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 kako bismo dobili najvišu i najnižu ocjenu iz tablice „Ocjene”:

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 bodova u tablicama „Bodovi“:

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

Vrijednost "NULL" je posebna vrijednost u SQLiteKoristi se za predstavljanje vrijednosti koja je nepoznata ili nedostaje. Imajte na umu da je null vrijednost potpuno drugačija od "0" ili prazne vrijednosti "". Budući da su 0 i prazna vrijednost poznate vrijednosti, null 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, da bi upit radio, morate koristiti operator "IS NULL" za pretraživanje null 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 vrijednosti koje nisu null, morate koristiti operator "IS NOT 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 CASE izraz s NULL vrijednošću u stupcu ID odjela u tablici Studenti kako bismo prikazali tekst 'Nema 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 koji naziv koji možete dati CTE-u, možete ga koristiti za kasnije pozivanje na njega. Imajte na umu da možete definirati naredbe 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 upitu definirali smo 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 klauzulu JOIN kako bismo dobili DepartmentName iz tablice Departments. Nakon toga smo dodali klauzulu GROUP BY s dvije agregacijske 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:

Pitanja i odgovori