SQLite upit: Odaberite, Gdje, Ograniči, Pomakni, Broji, Grupiraj po

Sadržaj:

Anonim

Da biste napisali SQL upite u bazu podataka SQLite, morate znati kako funkcioniraju klauzule SELECT, FROM, WHERE, GROUP BY, ORDER BY i LIMIT i kako ih koristiti.

Tijekom ovog vodiča naučit ćete kako koristiti ove klauzule i kako pisati SQLite klauzule.

U ovom vodiču naučit ćete-

  • Čitanje podataka s Select
  • Imena i aliasi
  • GDJE
  • Ograničavanje i naručivanje
  • Uklanjanje duplikata
  • Zbirno
  • Skupina BY
  • Upit i podupit
  • Postavite operacije -UNION, presijecanje
  • NULL rukovanje
  • Uvjetni rezultati
  • Uobičajeni izraz tablice
  • Napredni upiti

Čitanje podataka s Select

Klauzula SELECT glavni je izraz koji koristite za postavljanje upita o SQLite bazi podataka. U klauzuli SELECT navodite što odabrati. Ali prije klauzule select, pogledajmo odakle možemo odabrati podatke pomoću klauzule FROM.

Klauzula FROM koristi se za određivanje mjesta na kojem želite odabrati podatke. U klauzuli from možete odrediti jednu ili više tablica ili podupita za odabir podataka, 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 uzorkom baze podataka kao tekuću:

Korak 1) U ovom koraku,

  1. Otvorite Moje računalo i prijeđite na sljedeći direktorij " C: \ sqlite " i
  2. Zatim otvorite " sqlite3.exe ":

Korak 2) Sljedećom naredbom otvorite bazu podataka " TutorialsSampleDB.db ":

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 mogućnosti da odredite što odabrati. Kako slijedi:

IZABERI *

Ova naredba će odabrati sve stupce iz svih referenciranih tablica (ili potupita) u klauzuli FROM. Na primjer:

IZABERI *OD učenikaUNUTARNJA PRIDRUŽITI Odjelima NA Studentima.DepartmentId = Departmani.DepartmentId;

Ovo će odabrati sve stupce iz tablica učenika i tablica odsjeka:

SELECT naziv tablice. *

Ovo će odabrati sve stupce samo iz tablice "naziv tablice". Na primjer:

ODABERITE studente. *OD učenikaUNUTARNJA PRIDRUŽITI Odjelima NA Studentima.DepartmentId = Departmani.DepartmentId;

Ovim ćete odabrati sve stupce samo iz tablice učenika:

Doslovna vrijednost

Doslovna vrijednost je konstantna vrijednost koja se može navesti u naredbi select. Vrijednosti bukvalno možete koristiti na isti način kao što koristite imena stupaca u klauzuli SELECT. Te će se doslovne vrijednosti prikazati za svaki redak iz redaka koje vraća SQL upit.

Evo nekoliko primjera različitih doslovnih vrijednosti koje možete odabrati:

  • Numerički doslovni - brojevi u bilo kojem formatu poput 1, 2.55, ... itd.
  • String literali - Bilo koji niz 'USA', 'ovo je uzorak teksta', ... itd.
  • NULL - NULL vrijednost.
  • Current_TIME - dat će vam trenutno vrijeme.
  • CURRENT_DATE - ovo će vam dati trenutni datum.

To može biti korisno 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 zvanim zemlja koji sadrži vrijednost "SAD", možete to učiniti:

SELECT *, 'USA' AS AS FROM Students;

To će vam dati sve stupce učenika, kao i novi stupac "Država" poput ovog:

Imajte na umu da ovaj novi stupac Zemlja zapravo nije novi stupac dodan u tablicu. To je virtualni stupac, stvoren u upitu za prikaz rezultata i neće se kreirati na tablici.

Imena i aliasi

Nadimak je novo ime stupca koje vam omogućuje odabir stupca s novim imenom. Aliasi stupaca navode se pomoću ključne riječi "AS".

Na primjer, ako želite odabrati stupac Ime studenta koji će se umjesto "StudentName" vraćati s "Ime studenta", možete mu dati pseudonim poput ovog:

ODABERITE IME UČENIKA KAO „Ime učenika“ IZ učenika;

To će vam dati imena učenika s imenom "Ime studenta" umjesto "Ime učenika" ovako:

Imajte na umu da je naziv stupca i dalje " StudentName "; stupac Ime učenika i dalje je isti, ne mijenja se nadimak.

Alias ​​neće promijeniti ime stupca; samo će promijeniti ime za prikaz u klauzuli SELECT.

Također imajte na umu da, ključna riječ "AS" nije obavezna, alias ime možete staviti i bez nje, otprilike ovako:

ODABERITE Ime učenika 'Ime studenta' IZ učenika;

I dat će vam potpuno isti izlaz kao i prethodni upit:

Tablicama također možete dati pseudonime, a ne samo stupce. S istom ključnom riječi "AS". Na primjer, možete to učiniti:

SELECT s. * FROM Students AS s;

To će vam dati sve stupce u tablici Studenti:

To može biti vrlo korisno ako se pridružujete više tablica; umjesto ponavljanja punog imena tablice u upitu, svakoj tablici možete dati kratko ime alias. Na primjer, u sljedećem upitu:

ODABERITE Ime učenika, Odjeli.Ime odjelaOD učenikaUNUTARNJA PRIDRUŽITI Odjelima NA Studentima.DepartmentId = Departmani.DepartmentId;

Ovaj će upit odabrati svako ime studenta iz tablice "Studenti", a njegov naziv odsjeka iz tablice "Odjeli":

Međutim, isti se upit može napisati ovako:

ODABERITE s.StudentName, d.DepartmentNameOD UČENIKA KAO sUNUTARNJE PRIDRUŽIVANJE Odjelima KAO d NA s.DepartmentId = d.DepartmentId;
  • Tablici učenika dali smo alias "s", a odjeljenju tablicu alias "d".
  • Zatim, umjesto da koristimo puni naziv tablice, koristili smo njihove pseudonime da bismo ih uputili.
  • INNER JOIN spaja dvije ili više tablica zajedno koristeći uvjet. U našem primjeru pridružili smo tablicu Studenti tablici Departmani s stupcem DepartmentId. Tu je i detaljno objašnjenje ZA UNUTARNJE PRIDRUŽIVANJE u vodiču "SQLite Joins".

To će vam dati točan izlaz kao prethodni upit:

GDJE

Pisanje SQL upita koristeći samo klauzulu SELECT s klauzulom FROM, kao što smo vidjeli u prethodnom odjeljku, dobit ć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 funkcionira klauzula WHERE:

  • U klauzuli WHERE možete odrediti "izraz".
  • Taj će se izraz vrednovati za svaki redak vraćen iz tablice ili tablica navedenih u klauzuli FROM.
  • Izraz će se procijeniti kao logički izraz, s rezultatom true, false ili null.
  • Tada će se vratiti samo retci za koje je izraz procijenjen s istinitom vrijednošću, a oni s lažnim ili null rezultatima će se zanemariti i neće biti uključeni u skup rezultata.
  • Da biste filtrirali skup rezultata pomoću klauzule WHERE, morate koristiti izraze i operatore.

Popis operatora u SQLiteu 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 doslovnih 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 ćemo primjerima isprobati izraze i operatore i u klauzuli select i u WHERE klauzuli. Kako bi vam pokazali kako se izvode.

Postoje različite vrste izraza i operatora koje možete odrediti kako slijedi:

SQLite operator spajanja "||"

Ovaj se operator koristi za međusobno spajanje jedne ili više doslovnih vrijednosti ili stupaca. Proizvest će jedan niz rezultata iz svih spojenih književnih vrijednosti ili stupaca. Na primjer:

ODABERI 'Id s imenom:' || StudentId || StudentName AS StudentIdWithNameOD učenika;

Ovo će se spojiti u novi pseudonim " StudentIdWithName ":

  • Vrijednost doslovnog niza " Id s imenom: "
  • s vrijednošću stupca " StudentId " i
  • s vrijednošću iz stupca " Ime učenika "

SQLite CAST operator:

Operator CAST koristi se za pretvaranje vrijednosti iz vrste podataka u drugu vrstu podataka.

Na primjer, ako imate numeričku vrijednost pohranjenu kao vrijednost niza poput ove " '12 .5 ' " i želite je pretvoriti u numeričku vrijednost, možete koristiti CAST operator da biste to učinili ovako " CAST ('12 .5' AS STVARNO) ". Ili ako imate decimalnu vrijednost poput 12,5, a trebate dobiti samo cijeli broj, možete ga prebaciti na cijeli broj poput ovog "CAST (12,5 AS INTEGER)".

Primjer

U sljedećoj naredbi pokušat ćemo pretvoriti različite vrijednosti u druge vrste podataka:

ODABIR CAST ('12 .5 'KAO STVARNO) ToReal, CAST (12.5 AS INTEGER) KAO ToInteger;

Ovo će vam dati:

Rezultat je sljedeći:

  • CAST ('12 .5 'KAO STVARNO) - vrijednost '12 .5' je vrijednost niza, pretvorit će se u STVARNU vrijednost.
  • CAST (12,5 KAO CIJELI) - vrijednost 12,5 je decimalna vrijednost, pretvorit će se u cjelobrojnu vrijednost. Decimalni dio bit će skraćen i postaje 12.

SQLite aritmetički operateri:

Uzmite dvije ili više numeričkih doslovnih vrijednosti ili numeričkih stupaca i vratite jednu numeričku vrijednost. Aritmetički operatori podržani u SQLiteu su:

  • Dodatak " + " - dajte zbroj dva operanda.
  • Oduzimanje " - " - oduzima dva operanda i rezultira razlikom.
  • Množenje " * " - proizvod dva operanda.
  • Podsjetnik (modulo) " % " - daje ostatak koji je rezultat dijeljenja jednog operanda s drugim operandom.
  • Podjela " / " - vraća rezultate količnika dijeljenjem lijevog i desnog operanda.

Primjer:

U sljedećem ćemo primjeru isprobati pet aritmetičkih operatora s doslovno numeričkim vrijednostima

klauzula za odabir:

ODABERITE 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;

Ovo će vam dati:

Primijetite kako smo ovdje koristili naredbu SELECT bez klauzule FROM. A to je dopušteno u SQLiteu sve dok odaberemo doslovne vrijednosti.

Operatori SQLite usporedbe

Usporedite dva operanda međusobno i vratite true ili false na sljedeći način:

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

Imajte na umu da SQLite istinsku vrijednost izražava s 1, a lažnu vrijednost s 0.

Primjer:

IZABERI10 <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 otprilike ovo:

Operatori za podudaranje uzorka SQLite

" LIKE " - koristi se za podudaranje uzoraka. Korištenjem " Sviđa mi se " možete tražiti vrijednosti koje odgovaraju uzorku navedenom pomoću zamjenskog znaka.

Operand s lijeve strane može biti literalna vrijednost niza ili stupac niza. Uzorak se može odrediti kako slijedi:

  • Sadrži uzorak. Na primjer, StudentName LIKE '% 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, " StudentName LIKE 'a%' " - potražite imena učenika koja počinju slovom "a".
  • Završava uzorkom. Na primjer, " StudentName LIKE '% a' " - Potražite imena učenika koja završavaju slovom "a".
  • Podudaranje bilo kojeg pojedinačnog znaka u nizu pomoću donjeg slova "_". Na primjer, " StudentName LIKE 'J___' " - Potražite imena učenika duljine 4 znaka. Mora započeti slovom "J", a može imati bilo koja druga tri znaka nakon slova "J".

Primjeri podudaranja uzoraka:

  1. Doznajte imena učenika koja počinju slovom 'j': 
    ODABERITE IME UČENIKA OD STUDIJA GDJE IME UČENIKA KAO 'j%';

    Proizlaziti:

  2. Neka imena učenika završavaju slovom 'y': 
    ODABERITE IME UČENIKA OD STUDIJA GDJE IME UČENIKA KAO '% y';

    Proizlaziti:

  3. Dohvatite imena učenika koja sadrže slovo 'n': 
    ODABERITE IME UČENIKA IZBILJE UČENIKA GDJE IME UČENIKA KAO '% n%';

    Proizlaziti:

"GLOB" - ekvivalentan je operateru LIKE, ali GLOB razlikuje velika i mala slova, za razliku od operatora LIKE. Na primjer, sljedeće dvije naredbe vratit će različite rezultate:

ODABERITE 'Jack' GLOB 'j%';ODABERITE 'Jack' KAO 'j%';

Ovo će vam dati:

  • Prva izjava vraća 0 (netačno) jer je GLOB operator osjetljiv na 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 vrijednost "false".

Primjer:

Sljedeći će upit tražiti studente koji imaju StudentId> 5, a StudentName započinje slovom N, vraćeni studenti moraju ispunjavati dva uvjeta:

IZABERI *OD učenikaGDJE (StudentId> 5) I (Ime studenta KAO 'N%');

Kao izlaz, na gornjoj snimci zaslona, ​​dobit ćete samo "Nancy". Nancy je jedina studentica koja ispunjava oba uvjeta.

SQLite ILI

Logički operator koji kombinira jedan ili više izraza, tako da ako jedan od kombiniranih operatora daje true, tada će vratiti true. Međutim, ako svi izrazi daju false, vratit će false.

Primjer:

Sljedeći će upit tražiti studente koji imaju StudentId> 5 ili StudentName započinje slovom N, vraćeni studenti moraju ispunjavati barem jedan od uvjeta:

IZABERI *OD učenikaGDJE (StudentId> 5) ILI (StudentName KAO 'N%');

Ovo će vam dati:

Kao izlaz, na gornjoj snimci zaslona, ​​to će vam dati ime učenika s imenom "n", plus ID studenta koji ima vrijednost> 5.

Kao što vidite, rezultat se razlikuje od upita s operatorom AND.

SQLite IZMEĐU

IZMEĐU se koristi za odabir onih vrijednosti koje se nalaze u rasponu od dvije vrijednosti. Na primjer, " X IZMEĐU Y I Z " vratit će 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 " ekvivalentno je " X> = Y I X <= Z ", X mora biti veći ili jednak Y i X je manji ili jednak Z.

Primjer:

U sljedećem primjeru upita napisat ćemo upit kako bismo dobili učenike s vrijednošću Id između 5 i 8:

IZABERI *OD učenikaGDJE JE UČENIK IZMEĐU 5 I 8;

To ć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 prva vrijednost operanda jednaka vrijednosti operanda s popisa. Operator IN vraća true (1) ako popis operanda sadrži prvu vrijednost operanda unutar svojih vrijednosti. U suprotnom, vratit će false (0).

Ovako: " col IN (x, y, z) ". To je ekvivalentno " (col = x) ili (col = y) ili (col = z) ".

Primjer:

Sljedeći upit odabrat će studente samo s id-ovima 2, 4, 6, 8:

IZABERI *OD učenikaWHERE StudentId IN (2, 4, 6, 8);

Kao ovo:

Prethodni upit dat će točan rezultat kao sljedeći upit, jer su ekvivalentni:

IZABERI *OD učenikaGDJE (StudentId = 2) ILI (StudentId = 4) ILI (StudentId = 6) ILI (StudentId = 8);

Oba upita daju točan izlaz. Međutim, razlika između dva upita je, prvi smo upit koristili operator "IN". U drugom upitu koristili smo više operatora "ILI".

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

Kao ovo:

SQLite NIJE IN

Operand "NOT IN" suprotan je IN operatoru. Ali s istom sintaksom; potreban je jedan operand i popis operanda. Vratit će true ako vrijednost prvog operanda nije jednaka vrijednosti operanda s popisa. tj. vratit će true (0) ako popis operanda ne sadrži prvi operand. Ovako: " col NOT IN (x, y, z) ". Ovo je ekvivalentno s " (col <> x) AND (col <> y) AND (col <> z) ".

Primjer:

Sljedeći upit odabrat će studente s ID-ovima koji nisu jednaki od ovih ID-ova 2, 4, 6, 8:

IZABERI *OD učenikaGDJE Student NIJE IN (2, 4, 6, 8);

Kao ovo

Prethodnom upitu dajemo točan rezultat kao sljedeći upit, jer su ekvivalentni:

IZABERI *OD učenikaGDJE (StudentId <> 2) I (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Kao ovo:

Na gornjoj snimci zaslona,

Koristili smo više nejednakih operatora "<>" za dobivanje popisa učenika koji nisu jednaki niti jednom od sljedećih Id-ova 2, 4, 6 ili 8. Ovaj će upit vratiti sve ostale studente osim ovih popisa Id-a.

SQLite POSTOJI

Operatori EXISTS ne uzimaju nikakve operande; nakon njega je potrebna samo klauzula SELECT. Operator EXISTS vratit će true (1) ako se iz klauzule SELECT vrati bilo koji redak, a vratit će false (0) ako uopće nema vraćenih redaka iz klauzule SELECT.

Primjer:

U sljedećem ćemo primjeru odabrati naziv odsjeka ako ID odsjeka postoji u tablici učenika:

ODABERI Ime odjelaIZ Odjela KAO dGDJE POSTOJI (ODABERITE ODJELJENO OD STUDENATA KAO GDJE d.DepartmentId = s.DepartmentId);

Ovo će vam dati:

Vratit će se samo tri odjela " IT, fizika i umjetnost ". A naziv odsjeka " Matematika " neće se vratiti jer na tom odjelu nema studenta, tako da ID odsjeka ne postoji u tablici učenika. Zbog toga je operator EXISTS ignorirao odjel " Matematika ".

SQLite NE

Obrne rezultat prethodnog operatora koji dolazi nakon njega. Na primjer:

  • NE IZMEĐU - Vratit će se istinito ako IZMEĐU vrati lažno i obrnuto.
  • NOT LIKE - Vratit će se true ako LIKE vrati false i obrnuto.
  • NOT GLOB - Vratit će se true ako GLOB vrati false i obrnuto.
  • NOT EXISTS - Vratit će se true ako EXISTS vrati false i obrnuto.

Primjer:

U sljedećem ćemo primjeru koristiti operator NOT s operatorom POSTOJI da bismo dobili imena odjela koja ne postoje u tablici Studenti, što je obrnuti rezultat operatora POSTOJI. Dakle, pretraga će se vršiti putem DepartmentId-a koji ne postoji u tablici odjeljenja.

ODABERI Ime odjelaIZ Odjela KAO dGDJE NE POSTOJI (ODABERI IDOD UČENIKA KAO sWHERE d.DepartmentId = s.DepartmentId);

Izlaz :

Vratit će se samo odjel " Matematika ". Budući da je odjel " Matematika " jedini odjel, koji ne postoji u tablici učenika.

Ograničavanje i naručivanje

Nalog SQLite

SQLite Order je razvrstavanje rezultata po jednom ili više izraza. Da biste naručili skup rezultata, morate koristiti klauzulu ORDER BY kako slijedi:

  • Prvo morate navesti klauzulu ORDER BY.
  • Klauzula ORDER BY mora biti navedena na kraju upita; nakon nje može se navesti samo klauzula LIMIT.
  • Navedite izraz kojim ćete poredati podatke, taj izraz može biti naziv stupca ili izraz.
  • Nakon izraza možete odrediti neobavezni smjer sortiranja. Bilo DESC, da biste naredili silazne podatke ili ASC da biste naredili rastuće podatke. Ako niste naveli nijednu od njih, podaci će se sortirati uzlazno.
  • Možete odrediti više izraza koristeći međusobno ",".

Primjer

U sljedećem ćemo primjeru odabrati sve studente poredane prema imenima, ali silaznim redoslijedom, a zatim prema nazivu odsjeka rastućim redoslijedom:

ODABERITE s.StudentName, d.DepartmentNameOD UČENIKA KAO sUNUTARNJI PRIDRUŽITI Odjelima KAO d NA s.DepartmentId = d.DepartmentIdNARUČITE d.d. Odjeljenja ASC, s.StudentName DESC;

Ovo će vam dati:

  • SQLite će prvo poredati sve studente prema nazivu njihovog odjela u rastućem redoslijedu
  • Tada će se za svaki naziv odsjeka svi studenti pod tim odsjekom prikazivati ​​u padajućem redoslijedu prema njihovim imenima

Ograničenje SQLitea:

Možete ograničiti broj redaka koje vraća vaš SQL upit pomoću klauzule LIMIT. Na primjer, LIMIT 10 dat će vam samo 10 redaka i zanemariti sve ostale retke.

U klauzuli LIMIT možete odabrati određeni broj redaka počevši od određenog položaja pomoću klauzule OFFSET. Na primjer, " LIMIT 4 OFFSET 4 " ignorirat će prva 4 retka i vratio je 4 retka počevši od petog retka, tako da ćete dobiti retke 5,6,7 i 8.

Imajte na umu da je klauzula OFFSET neobavezna, možete je napisati kao " LIMIT 4, 4 " i dat će vam točne rezultate.

Primjer :

U sljedećem ćemo primjeru pomoću upita vratiti samo 3 učenika počevši od studentskog ID-a 5:

ODABERI * IZ UČENIKA GRANIČNO 4,3;

To će vam dati samo tri učenika počevši od retka 5. Tako će vam dati redove s StudentId 5, 6 i 7:

Uklanjanje duplikata

Ako vaš SQL upit vrati dvostruke vrijednosti, možete upotrijebiti ključnu riječ " DISTINCT " da biste uklonili te duplikate i vratili različite vrijednosti. Nakon rada tipke DISTINCT možete odrediti više stupaca.

Primjer:

Sljedeći će upit vratiti dvostruke "vrijednosti naziva odjela": Ovdje imamo dvostruke vrijednosti s imenima IT, Fizika i Umjetnost.

SELECT d.DepartmentNameOD UČENIKA KAO sUNUTARNJE PRIDRUŽIVANJE Odjelima KAO d NA s.DepartmentId = d.DepartmentId;

To će vam dati dvostruke vrijednosti za naziv odjela:

Primijetite, kako postoje dvostruke vrijednosti za naziv odjela. Sada ćemo upotrijebiti ključnu riječ DISTINCT s istim upitom za uklanjanje tih duplikata i dobivanje samo jedinstvenih vrijednosti. Kao ovo:

ODABERITE RAZLIKU d.naziv odjeljenjaOD UČENIKA KAO sUNUTARNJE PRIDRUŽIVANJE Odjelima KAO d NA s.DepartmentId = d.DepartmentId;

To će vam dati samo tri jedinstvene vrijednosti za stupac naziva odjela:

Zbirno

SQLite agregati su ugrađene funkcije definirane u SQLiteu koje će grupirati više vrijednosti više redaka u jednu vrijednost.

Evo agregata koje podržava SQLite:

SQLite AVG ()

Vraćen prosjek za sve x vrijednosti.

Primjer:

U sljedećem ćemo primjeru dobiti prosječnu ocjenu koju su studenti dobili na svim ispitima:

ODABIR AVG (Oznaka) IZ Oznaka;

To će vam dati vrijednost "18.375":

Ti rezultati potječu od zbrajanja svih vrijednosti ocjena podijeljenih s njihovim brojanjem.

COUNT () - COUNT (X) ili COUNT (*)

Vraća ukupan broj koliko se puta pojavila vrijednost x. Evo nekoliko opcija koje možete koristiti s COUNT:

  • COUNT (x): Broji samo x vrijednosti, gdje je x naziv stupca. Zanemarit će NULL vrijednosti.
  • BROJ (*): Broji sve retke iz svih stupaca.
  • COUNT (DISTINCT x): Možete odrediti ključnu riječ DISTINCT prije x koja će dobiti broj različitih vrijednosti x.

Primjer

U sljedećem primjeru dobit ćemo ukupan broj odjeljenja s COUNT (DepartmentId), COUNT (*) i COUNT (DISTINCT DepartmentId) i kako se razlikuju:

ODABERITE BROJ (ID odjeljenja), BROJ (RAZLIKUJTE odjel ID), BROJ (*) OD učenika;

Ovo će vam dati:

Kako slijedi:

  • COUNT (DepartmentId) dat će vam broj svih ID-a odjela i zanemarit će null vrijednosti.
  • COUNT (DISTINCT DepartmentId) daju vam različite vrijednosti DepartmentId, koje su samo 3. Koje su tri različite vrijednosti naziva odjela. Primijetite da u imenu studenta postoji 8 vrijednosti naziva odsjeka. Ali samo tri različite vrijednosti koje su Math, IT i Physics.
  • 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 zarezom da bi ih odvojila. Ima sljedeće mogućnosti:

  • GROUP_CONCAT (X): Ovo će spojiti svu vrijednost x u jedan niz, sa zarezom "," koji se koristi kao razdvajač između vrijednosti. NULL vrijednosti će se zanemariti.
  • GROUP_CONCAT (X, Y): Ovo će spojiti vrijednosti x u jedan niz, s vrijednošću y koja se koristi kao razdvajač između svake vrijednosti umjesto zadanog razdjelnika ','. NULL vrijednosti također će se zanemariti.
  • GROUP_CONCAT (DISTINCT X): Ovo će spojiti sve različite vrijednosti x u jedan niz, sa zarezom "," koji se koristi kao razdvajač između vrijednosti. NULL vrijednosti će se zanemariti.

Primjer GROUP_CONCAT (Ime odjela)

Sljedeći će upit spojiti sve vrijednosti naziva odjela iz tablice učenika i odjela u jednu odvojenu zarezom. Dakle, umjesto vraćanja popisa vrijednosti, po jedna vrijednost u svakom retku. Vratit će samo jednu vrijednost u jedan redak, sa svim vrijednostima odvojenim zarezom:

ODABERITE GROUP_CONCAT (d.DepartmentName)OD UČENIKA KAO sUNUTARNJE PRIDRUŽIVANJE Odjelima KAO d NA s.DepartmentId = d.DepartmentId;

Ovo će vam dati:

To će vam dati popis vrijednosti imena 8 odjela povezanih u jedan niz odvojenih zarezom.

Primjer GROUP_CONCAT (DISTINCTNije odjel)

Sljedeći će upit povezati različite vrijednosti imena odsjeka iz tablice učenika i odsjeka u jednu odvojenu zarezom:

ODABERITE GROUP_CONCAT (DISTINCT d.DepartmentName)OD UČENIKA KAO sUNUTARNJE PRIDRUŽIVANJE Odjelima KAO d NA 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 dvostruke vrijednosti su uklonjene.

GROUP_CONCAT (Ime odjeljenja, '&') Primjer

Sljedeći će upit spojiti sve vrijednosti stupca naziva odsjeka iz tablice učenika i odsjeka u jedan niz, ali sa znakom '&' umjesto zareza kao separatora:

ODABERITE GROUP_CONCAT (d.DepartmentName, '&')OD UČENIKA KAO sUNUTARNJE PRIDRUŽIVANJE Odjelima KAO d NA s.DepartmentId = d.DepartmentId;

Ovo će vam dati:

Primijetite kako se znak "&" koristi umjesto zadanog znaka "," za razdvajanje vrijednosti.

SQLite MAX () i MIN ()

MAX (X) vraća vam najvišu vrijednost od X vrijednosti. MAX će vratiti NULL vrijednost ako su sve vrijednosti x null. Dok vam 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 iz tablice " Oznake " dobili najvišu i najnižu ocjenu :

ODABERITE MAX (Oznaka), MIN (Oznaka) IZ Oznaka;

Ovo će vam dati:

SQLite SUM (x), Ukupno (x)

Oboje će vratiti zbroj svih x vrijednosti. Ali oni se razlikuju u sljedećem:

  • SUM će vratiti nulu ako su sve vrijednosti nule, ali Total će vratiti 0.
  • TOTAL uvijek vraća vrijednosti s pomičnim zarezom. SUM vraća cijelu vrijednost ako su sve x vrijednosti cijeli broj. Međutim, ako vrijednosti nisu cijeli broj, vratit će vrijednost s pomičnim zarezom.

Primjer

U sljedećem ćemo upitu upotrijebiti SUM i zbroj da bismo dobili zbroj svih oznaka u tablicama " Oznake ":

ODABERI ZUM (Oznaka), UKUPNO (Oznaka) IZ Oznaka;

Ovo će vam dati:

Kao što vidite, TOTAL uvijek vraća pokretnu točku. Ali SUM vraća cijelu vrijednost jer su vrijednosti u stupcu "Označi" možda u cijelim brojevima.

Razlika između primjera SUM i UKUPNO:

U sljedećem ćemo upitu prikazati razliku između ZBORA i UKUPNO kad dobiju ZBOR NULL vrijednosti:

ODABERI ZUM (Oznaka), UKUPNO (Oznaka) IZ OZNAKA GDJE TestId = 4;

Ovo će vam dati:

Imajte na umu da za TestId = 4 nema oznaka, pa za taj test postoje null vrijednosti. SUM vraća nulu vrijednost kao prazno, dok TOTAL vraća 0.

Skupina BY

Klauzula GROUP BY koristi se za određivanje jednog ili više stupaca koji će se koristiti za grupiranje redaka u skupine. Redovi s istim vrijednostima skupit će se (poredati) zajedno u skupine.

Za bilo koji drugi stupac koji nije uključen u skupinu po stupcima, možete koristiti agregiranu funkciju za njega.

Primjer:

Sljedeći upit dat će vam ukupan broj učenika prisutnih na svakom odjelu.

ODABERITE d.DepartmentName, COUNT (s.StudentId) KAO Broj učenikaOD UČENIKA KAO sUNUTARNJI PRIDRUŽITI Odjelima KAO d NA s.DepartmentId = d.DepartmentIdGRUPIRAJ po d. Ime odjela;

Ovo će vam dati:

Klauzula GROUPBY DepartmentName grupirat će sve studente u skupine po jedno za svako ime odsjeka. Za svaku grupu "odsjeka" računat će studente.

Klauzula HAVING

Ako želite filtrirati grupe koje vraća klauzula GROUP BY, možete navesti klauzulu "HAVING" s izrazom nakon GROUP BY. Izraz će se koristiti za filtriranje tih grupa.

Primjer

U sljedećem ćemo upitu odabrati one odsjeke na kojima su samo dva studenta:

ODABERITE d.DepartmentName, COUNT (s.StudentId) KAO Broj učenikaOD UČENIKA KAO sUNUTARNJI PRIDRUŽITI Odjelima KAO d NA s.DepartmentId = d.DepartmentIdGRUPIRAJ po d. OdjelNazivIMAJUĆI BROJ (s.StudentId) = 2;

Ovo će vam dati:

Klauzula HAVING COUNT (S.StudentId) = 2 filtrirat će vraćene grupe i vratiti samo one grupe koje na sebi sadrže točno dva učenika. U našem slučaju, Odjel za umjetnost ima 2 učenika, pa je prikazan u izlazu.

SQLite upit i podupit

Unutar bilo kojeg upita možete upotrijebiti drugi upit bilo u SELECT, INSERT, DELETE, UPDATE ili unutar drugog podupita.

Ovaj ugniježđeni upit naziva se podupit. Sad ćemo vidjeti nekoliko primjera upotrebe podupita u klauzuli SELECT. Međutim, u vodiču za Modificiranje podataka vidjet ćemo kako možemo koristiti podupite s izrazom INSERT, DELETE i UPDATE.

Upotreba podupita u primjeru klauzule FROM

U sljedeći upit uvrstit ćemo podupit unutar klauzule FROM:

IZABERIs.StudentName, t.MarkOD UČENIKA KAO sUNUTARNJE PRIDRUŽIVANJE(ODABERI StudentId, MarkIZ TESTA KAO tUNUTARNJE PRIDRUŽIVANJE Oznake KAO m NA T.TestId = m.TestId) ON s.StudentId = t.StudentId;

Upit:

 ODABERI StudentId, MarkIZ TESTA KAO tUNUTARNJE PRIDRUŽIVANJE Oznake KAO m NA T.TestId = m.TestId

Gornji upit ovdje se naziva podupitom jer je ugniježđen unutar klauzule FROM. Primijetite da smo mu dali nadimak "t" kako bismo se mogli pozvati na stupce vraćene iz njega u upitu.

Ovaj upit će vam dati:

U našem slučaju,

  • s.StudentName odabire se iz glavnog upita koji daje ime učenika i
  • t.Oznaka je odabrana iz podupita; to daje ocjene koje je dobio svaki od ovih učenika

Upotreba podupita u primjeru klauzule WHERE

U sljedeći upit uvrstit ćemo podupit u klauzulu WHERE:

ODABERI Ime odjelaIZ Odjela KAO dGDJE NE POSTOJI (ODABERI IDOD UČENIKA KAO sWHERE d.DepartmentId = s.DepartmentId);

Upit:

ODABERI ID odjeljkaOD UČENIKA KAO sWHERE d.DepartmentId = s.DepartmentId

Gornji upit ovdje se naziva podupitom jer je ugniježđen u klauzulu WHERE. Podupit će vratiti vrijednosti DepartmentId koje će koristiti operator NE POSTOJI.

Ovaj upit će vam dati:

U gornjem upitu odabrali smo odjel na kojem nije upisan nijedan student. Koji je ovdje odjel "Matematike".

Skup operacija - UNIJA, presjek

SQLite podržava sljedeće SET operacije:

SAVEZ & SAVEZ SVE

Kombinira jedan ili više skupova rezultata (grupa redaka) vraćenih iz više SELECT izraza 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 naredbi SELECT.

Primjer UNIJE

U sljedećem primjeru dobit ćemo popis DepartmentId iz tablice učenika i popis DepartmentId iz tablice odjela u istom stupcu:

ODABERITE ODJELJENO KAO ODJELJENO OD STUDENATAUNIJAODABERI ID ODJELJKA;

Ovo će vam dati:

Upit vraća samo 5 redaka koji su različite vrijednosti ID-a odjela. Primijetite prvu vrijednost koja je null vrijednost.

Primjer SQLite UNION ALL

U sljedećem primjeru dobit ćemo popis DepartmentId iz tablice učenika i popis DepartmentId iz tablice odjela u istom stupcu:

ODABERITE ODJELJENO KAO ODJELJENO OD STUDENATASAVEZ SVEODABERI ID ODJELJKA;

Ovo će vam dati:

Upit će vratiti 14 redaka, 10 redaka iz tablice učenika i 4 iz tablice odjeljenja. Imajte na umu da u vraćenim vrijednostima postoje duplikati. Također, imajte na umu da je naziv stupca bio onaj naveden u prvoj naredbi SELECT.

Sada, da vidimo kako će UNION svi dati različite rezultate ako UNION ALL zamijenimo UNION:

SQLite INTERSECT

Vraća vrijednosti koje postoje u oba kombinirana skupa rezultata. Vrijednosti koje postoje u jednom od kombiniranih skupova rezultata bit će zanemarene.

Primjer

U slijedećem upitu odabrat ćemo vrijednosti DepartmentId koje postoje u tablicama Studenti i Odjeli u stupcu DepartmentId:

ODABERI ODJEL OD učenikaSjecišteODABERI ID ODJELJKA;

Ovo će vam dati:

Upit vraća samo tri vrijednosti 1, 2 i 3. Koje su vrijednosti koje postoje u obje tablice.

Međutim, vrijednosti null i 4 nisu uključene jer null vrijednost postoji samo u tablici učenika, a ne u tablici odjeljenja. A vrijednost 4 postoji u tablici odjela, a ne u tablici učenika.

Zbog toga su i vrijednosti NULL i 4 zanemarene i nisu uključene u vraćene vrijednosti.

OSIM

Pretpostavimo da ako imate dva popisa redaka, list1 i list2, a želite retke samo s popisa1 koji ne postoji na popisu2, možete koristiti klauzulu "OSIM". Klauzula EXCEPT uspoređuje dva popisa i vraća one retke koji postoje u listi1, a ne postoje u listi2.

Primjer

U sljedećem upitu odabrat ćemo vrijednosti DepartmentId koje postoje u tablici odjela, a ne postoje u tablici učenika:

ODABERI ID ODJELJAKAOSIMODABERI ODJEL OD učenika;

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 " posebna je vrijednost u SQLiteu. Koristi se za predstavljanje vrijednosti koja je nepoznata ili nedostaje. Imajte na umu da se null vrijednost potpuno razlikuje od vrijednosti " 0 " ili praznog "". Budući da je 0 i prazna vrijednost poznata vrijednost, null vrijednost je nepoznata.

NULL vrijednosti zahtijevaju posebno rukovanje u SQLiteu, sada ćemo vidjeti kako postupati s NULL vrijednostima.

Potražite NULL vrijednosti

Ne možete koristiti normalni operator jednakosti (=) za pretraživanje null vrijednosti. Na primjer, sljedeći upit traži studente koji imaju null vrijednost DepartmentId:

ODABERITE * FROM studenata GDJE DepartmentId = NULL;

Ovaj upit neće dati nikakav rezultat:

Budući da NULL vrijednost nije jednaka nijednoj drugoj vrijednosti, uključila je i samu nulu, zato nije vratila nikakav rezultat.

  • Međutim, da bi upit funkcionirao, morate koristiti operator "IS NULL" za traženje null vrijednosti kako slijedi:
ODABERITE * OD studenata GDJE JE ODJEL NULL;

Ovo će vam dati:

Upit će vratiti one studente koji imaju null vrijednost DepartmentId.

  • Ako želite dobiti one vrijednosti koje nisu null, tada morate koristiti operator " NIJE NULL " poput ovog:
ODABERITE * OD studenata GDJE ODJEL NIJE 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. Zbog toga bi uvjet za tu određenu vrijednost trebao biti istinit da bi se mogao odabrati.

Izraz CASE procijenit će 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, kako slijedi:

- "Izvrsno" ako je ocjena viša od 85.

- "Vrlo dobro" ako je ocjena između 70 i 85.

- "Dobro" ako je ocjena između 60 i 70.

Tada za to možete koristiti izraz CASE.

To se može koristiti za definiranje neke logike u klauzuli SELECT, tako da možete odabrati određene rezultate, ovisno o određenim uvjetima, na primjer primjericu if.

Operator CASE može se definirati s različitim sintaksama kako slijedi:

  1. Možete koristiti različite uvjete:
SLUČAJKADA uvjet1 ONDA rezultat1KADA uvjet2 ONDA rezultat2KADA uvjet3 ONDA rezultat3 ... OSTALO rezultatnKRAJ
  1. Ili možete koristiti samo jedan izraz i postaviti različite moguće vrijednosti između kojih možete odabrati:
CASE izrazKADA vrijednost1 ONDA rezultat1KADA vrijednost2 ONDA rezultat2WHEN value3 THEN result3 ... ELSE restulnKRAJ

Imajte na umu da je klauzula ELSE neobavezna.

Primjer

U sljedećem ćemo primjeru koristiti izraz CASE s NULL vrijednošću u stupcu Id odjela u tablici Studenti kako bismo prikazali tekst 'Nema odjela' kako slijedi:

IZABERIIme studenta,SLUČAJKADA ODJEL JE NIŠTA ONDA 'Nema odjela'DRUGI odjelIdKRAJ KAO odjelIdOD učenika;
  • Operator CASE provjerit će vrijednost DepartmentId je li nula ili nije.
  • Ako je to NULL vrijednost, tada će umjesto vrijednosti DepartmentId odabrati doslovnu vrijednost 'No Department'.
  • Ako nije null vrijednost, tada će odabrati vrijednost stupca DepartmentId.

To će vam dati izlaz kao što je prikazano u nastavku:

Uobičajeni izraz tablice

Uobičajeni izrazi tablice (CTE) podupiti su koji su definirani unutar SQL izraza s danim imenom.

Ima prednost u odnosu na podupite jer je definiran iz SQL izraza i olakšat će čitanje, održavanje i razumijevanje upita.

Uobičajeni izraz tablice može se definirati stavljanjem klauzule WITH ispred naredbi SELECT na sljedeći način:

S CTEnameomKAO(Naredba SELECT)SELECT, UPDATE, INSERT ili ažuriranje izjave ovdje IZ CTE-a

" CTEname " je bilo koje ime koje možete dati za CTE, a možete ga koristiti da biste ga kasnije pozvali . Imajte na umu da izjavu SELECT, UPDATE, INSERT ili DELETE možete definirati na CTE-ima

Sada, pogledajmo primjer kako koristiti CTE u klauzuli SELECT.

Primjer

U sljedećem ćemo primjeru definirati CTE iz naredbe SELECT, a zatim ćemo ga koristiti kasnije za drugi upit:

S AllDepartmentsKAO(ODABERI ID odjeljka, naziv odjeljenjaIZ Odjela)IZABERIs.StudentId,s.StudentName,a.Ime odjelaOD UČENIKA KAO sUNUTARNJE PRIDRUŽIVANJE svim odjelima KAO ON s.DepartmentId = a.DepartmentId;

U ovom smo upitu definirali CTE i dali mu naziv " AllDepartments ". Ovaj CTE definiran je iz upita SELECT:

 ODABERI ID odjeljka, naziv odjeljenjaIZ Odjela

Zatim nakon što smo definirali CTE koristili smo ga u SELECT upitu koji dolazi nakon njega.

Imajte na umu da izrazi uobičajene tablice ne utječu na izlaz upita. To je način da definirate logički prikaz ili podupit kako biste ih ponovno koristili u istom upitu. Uobičajeni izrazi tablice su poput varijable koju deklarirate i ponovno je koristite kao podupit. Samo izraz SELECT utječe na izlaz upita.

Ovaj upit će vam dati:

Napredni upiti

Napredni upiti su oni upiti koji sadrže složene spojeve, podupitaje i neke agregate. U sljedećem odjeljku vidjet ćemo primjer naprednog upita:

Gdje smo dobili,

  • Imena odsjeka sa svim studentima za svaki odjel
  • Studenti imenuju odvojene zarezima i
  • Prikaz odsjeka u kojem su najmanje tri učenika
IZABERId.ime odjela,COUNT (s.StudentId) Broj učenika,GROUP_CONCAT (Ime studenta) KAO studentiIZ Odjela KAO dUNUTARNJE PRIDRUŽIVANJE Studentima KAO NA s.DepartmentId = d.DepartmentIdGROUP BY d.DepartmentNameIMAJUĆI BROJ (s.StudentId)> = 3;

Dodali smo klauzulu JOIN da bismo iz tablice Departmani dobili odjelName. Nakon toga dodali smo klauzulu GROUP BY s dvije agregatne funkcije:

  • "COUNT" za brojanje učenika za svaku skupinu odsjeka.
  • GROUP_CONCAT za spajanje učenika za svaku skupinu zarezom odvojenim u jedan niz.
  • Nakon GROUP BY, klauzulom HAVING filtrirali smo odjele i odabrali samo one odjele koji imaju najmanje 3 učenika.

Rezultat će biti sljedeći:

Sažetak:

Ovo je bio uvod u pisanje SQLite upita i osnove ispitivanja baze podataka i kako možete filtrirati vraćene podatke. Sada možete pisati vlastite SQLite upite.