Poizvedba SQLite: izberite, kje, OMEJITEV, POMAK, štetje, razvrščanje po

Kazalo:

Anonim

Če želite zapisovati poizvedbe SQL v bazo podatkov SQLite, morate vedeti, kako delujejo stavki SELECT, FROM, WHERE, GROUP BY, ORDER BY in LIMIT in kako jih uporabljati.

V tej vadnici boste izvedeli, kako uporabljati te določbe in kako pisati stavke SQLite.

V tej vadnici boste izvedeli-

  • Branje podatkov z Select
  • Imena in vzdevki
  • KJE
  • Omejevanje in naročanje
  • Odstranjevanje dvojnikov
  • Skupno
  • Skupina BY
  • Poizvedba in podpoizvedba
  • Nastavite operacije -UNION, križišče
  • NULL ravnanje
  • Pogojni rezultati
  • Pogost izraz tabele
  • Napredne poizvedbe

Branje podatkov z Select

Stavek SELECT je glavni stavek, ki ga uporabljate za poizvedbo po zbirki podatkov SQLite. V stavku SELECT navedite, kaj izbrati. Toda pred stavkom select poglejmo, od kod lahko izberemo podatke s pomočjo stavka FROM.

Stavek FROM se uporablja za določitev, kje želite izbrati podatke. V klavzuli from lahko določite eno ali več tabel ali podpoizvedb za izbiro podatkov, kot bomo videli kasneje v vajah.

Za vse naslednje primere morate zagnati sqlite3.exe in odpreti povezavo do vzorčne baze podatkov kot tekočo:

Korak 1) V tem koraku

  1. Odprite Moj računalnik in se pomaknite do naslednjega imenika " C: \ sqlite " in
  2. Nato odprite " sqlite3.exe ":

Korak 2) Z naslednjim ukazom odprite bazo podatkov " TutorialsSampleDB.db ":

Zdaj ste pripravljeni zagnati katero koli vrsto poizvedbe v zbirki podatkov.

V stavku SELECT lahko izberete ne samo ime stolpca, temveč imate veliko drugih možnosti, s katerimi določite, kaj izbrati. Kot sledeče:

IZBERI *

Ta ukaz bo izbral vse stolpce iz vseh referenčnih tabel (ali podpoizvedb) v stavku FROM. Na primer:

IZBERI *OD ŠTUDENTOVINNER JOIN Oddelki za študente.DepartmentId = Departments.DepartmentId;

S tem boste izbrali vse stolpce iz tabel študentov in tabel oddelkov:

IZBERI ime tabele. *

S tem boste izbrali vse stolpce samo iz tabele "ime tabele". Na primer:

IZBERITE študente. *OD ŠTUDENTOVINNER JOIN Oddelki za študente.DepartmentId = Departments.DepartmentId;

S tem boste izbrali samo vse stolpce iz tabele študentov:

Dobesedna vrednost

Dobesedna vrednost je konstantna vrednost, ki jo lahko podate v stavku select. Dobesedne vrednosti lahko običajno uporabljate enako kot imena stolpcev v stavku SELECT. Te dobesedne vrednosti bodo prikazane za vsako vrstico iz vrstic, ki jih vrne poizvedba SQL.

Tu je nekaj primerov različnih dobesednih vrednosti, ki jih lahko izberete:

  • Numeric Literal - številke v kateri koli obliki, kot so 1, 2.55, ... itd.
  • String literals - kateri koli niz 'USA', 'this is a sample text', ... itd.
  • NULL - NULL vrednost.
  • Current_TIME - prikazal vam bo trenutni čas.
  • CURRENT_DATE - s tem boste dobili trenutni datum.

To je lahko priročno v nekaterih primerih, ko morate izbrati konstantno vrednost za vse vrnjene vrstice. Če želite na primer iz tabele Študenti izbrati vse študente z novim stolpcem, imenovanim država, ki vsebuje vrednost "ZDA", lahko to storite tako:

SELECT *, 'USA' AS Country FROM Students;

Tako boste dobili vse stolpce za študente in nov stolpec "Država", kot je ta:

Upoštevajte, da ta novi stolpec Država dejansko ni nov stolpec, dodan v tabelo. Je navidezni stolpec, ustvarjen v poizvedbi za prikaz rezultatov in ne bo ustvarjen v tabeli.

Imena in vzdevki

Vzdevek je novo ime stolpca, ki omogoča izbiro stolpca z novim imenom. Vzdevki stolpcev so določeni s ključno besedo "AS".

Če želite na primer izbrati stolpec Študentsko ime, ki se mu namesto "Študentsko ime" vrne z "Študentsko ime", mu lahko dodelite vzdevek, kot je ta:

SELECT StudentName AS 'Student name' FROM Students;

Tako boste dobili imena študentov z imenom "Student Name" namesto "StudentName", kot je ta:

Upoštevajte, da je ime stolpca še vedno " Študentsko ime "; stolpec StudentName je še vedno enak, ne spremeni se z vzdevkom.

Vzdevek ne bo spremenil imena stolpca; samo spremeni prikazno ime v stavku SELECT.

Upoštevajte tudi, da je ključna beseda "AS" neobvezna, lahko vzdevek dodate brez nje, približno tako:

SELECT StudentName 'Student name' FROM Students;

In dobili boste popolnoma enak izhod kot prejšnja poizvedba:

Tabelam lahko dodelite tudi vzdevke, ne samo stolpcev. Z isto ključno besedo "AS". To lahko naredite na primer:

SELECT s. * FROM Students AS s;

Tako boste dobili vse stolpce v tabeli Študenti:

To je lahko zelo koristno, če se pridružite več kot eni mizi; namesto da v poizvedbi ponovite celotno ime tabele, lahko vsaki tabeli daste kratko vzdevek. Na primer v naslednji poizvedbi:

IZBERI Študent.Ime študentov, Oddelkov.Ime oddelkovOD ŠTUDENTOVINNER JOIN Oddelki za študente.DepartmentId = Departments.DepartmentId;

Ta poizvedba bo izbrala vsako ime študenta iz tabele "Študenti" in njegovo ime oddelka iz tabele "Oddelki":

Vendar lahko isto poizvedbo zapišemo takole:

SELECT s.StudentName, d.DepartmentNameOD ŠTUDENTOV KOT sINNER JOIN Oddelki AS d NA s.DepartmentId = d.DepartmentId;
  • Tabeli študentov smo dali vzdevek "s", tabeli oddelkov pa vzdevek "d".
  • Nato smo namesto imena celotne tabele uporabili njihove vzdevke, da se sklicujemo nanje.
  • INNER JOIN združi dve ali več tabel skupaj z uporabo pogoja. V našem primeru smo tabelo Študenti združili s tabelo Oddelki s stolpcem DepartmentId. V vadnici "SQLite Joins" je tudi podrobna razlaga za INNER JOIN.

Tako boste dobili natančen izhod kot prejšnja poizvedba:

KJE

Če zapišete poizvedbe SQL samo s stavkom SELECT s stavkom FROM, kot smo videli v prejšnjem razdelku, boste dobili vse vrstice iz tabel. Če želite filtrirati vrnjene podatke, morate dodati klavzulo "WHERE".

Stavek WHERE se uporablja za filtriranje nabora rezultatov, ki ga vrne poizvedba SQL. Klavzula WHERE deluje tako:

  • V stavku WHERE lahko podate "izraz".
  • Ta izraz bo ocenjen za vsako vrstico, vrnjeno iz tabel (-e), določenih v stavku FROM.
  • Izraz bo ovrednoten kot logični izraz z rezultatom true, false ali null.
  • Potem bodo vrnjene samo vrstice, za katere je bil izraz ovrednoten z resnično vrednostjo, tiste z napačnimi ali ničelnimi rezultati pa bodo prezrte in ne bodo vključene v nabor rezultatov.
  • Če želite filtrirati nabor rezultatov s stavkom WHERE, morate uporabiti izraze in operatorje.

Seznam operaterjev v SQLite in kako jih uporabljati

V naslednjem poglavju bomo razložili, kako lahko filtrirate z uporabo izrazov in operatorjev.

Izraz je ena ali več dobesednih vrednosti ali stolpcev, kombiniranih med seboj z operatorjem.

Upoštevajte, da lahko izraze uporabljate tako v stavku SELECT kot v stavku WHERE.

V naslednjih primerih bomo preizkusili izraze in operatorje v stavku select in stavku WHERE. Da bi vam pokazali, kako se obnesejo.

Obstajajo različne vrste izrazov in operatorjev, ki jih lahko določite na naslednji način:

SQLite operator povezovanja "||"

Ta operator se uporablja za povezovanje ene ali več dobesednih vrednosti ali stolpcev med seboj. Rezultat bo en niz rezultatov iz vseh združenih literarnih vrednosti ali stolpcev. Na primer:

SELECT 'Id z imenom:' || ID študenta || StudentName AS StudentIdWithNameOD študentov;

To se bo povezalo v nov vzdevek " StudentIdWithName ":

  • Dobesedna vrednost niza " Id z imenom: "
  • z vrednostjo stolpca " StudentId " in
  • z vrednostjo iz stolpca " Študentsko ime "

Operater CAST SQLite:

Operator CAST se uporablja za pretvorbo vrednosti iz podatkovnega tipa v drug podatkovni tip.

Če imate na primer številsko vrednost shranjeno kot niz, kot je ta " '12 .5 ' " in jo želite pretvoriti v številsko vrednost, lahko z operaterjem CAST to storite takole " CAST ('12 .5' AS REAL) ". Če pa imate decimalno vrednost, na primer 12,5, in morate dobiti samo celoštevilski del, ga lahko oddate v celo število, kot je ta "CAST (12,5 AS INTEGER)".

Primer

V naslednjem ukazu bomo poskušali pretvoriti različne vrednosti v druge vrste podatkov:

SELECT CAST ('12 .5 'AS REAL) ToReal, CAST (12.5 AS INTEGER) KOT ToInteger;

Tako boste dobili:

Rezultat je naslednji:

  • CAST ('12 .5 'AS REAL) - vrednost '12 .5' je nizna vrednost, pretvorjena bo v REALNO vrednost.
  • CAST (12,5 AS INTEGER) - vrednost 12,5 je decimalna vrednost, pretvorjena bo v celoštevilčno vrednost. Decimalni del bo okrnjen in postane 12.

Aritmetični operaterji SQLite:

Vzemite dve ali več številskih dobesednih vrednosti ali številskih stolpcev in vrnite eno številsko vrednost. Aritmetični operaterji, ki jih podpira SQLite, so:

  • Dodatek " + " - poda vsoto obeh operand.
  • Odštevanje " - " - odšteje dva operanda in povzroči razliko.
  • Množenje " * " - produkt dveh operandov.
  • Opomnik (modulo) " % " - daje preostanek, ki je posledica deljenja enega operanda z drugim.
  • Delitev " / " - vrne rezultate količnika iz deljenja levega operanda z desnim operandom.

Primer:

V naslednjem primeru bomo poskusili pet aritmetičnih operatorjev z dobesednimi številskimi vrednostmi v istem

klavzula za izbiro:

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

Tako boste dobili:

Opazite, kako smo tukaj uporabili stavek SELECT brez klavzule FROM. In to je v SQLite dovoljeno, če izberemo dobesedne vrednosti.

Operaterji primerjave SQLite

Primerjajte dva operanda med seboj in vrnite true ali false, kot sledi:

  • " < " - vrne true, če je levi operand manjši od desnega.
  • " <= " - vrne true, če je levi operand manjši ali enak desnemu operandu.
  • " > " - vrne true, če je levi operand večji od desnega.
  • " > = " - vrne true, če je levi operand večji ali enak desnemu operandu.
  • " = " in " == " - vrne true, če sta operanda enaka. Upoštevajte, da sta oba operaterja enaka in med njima ni razlike.
  • " ! = " in " <> " - vrne true, če oba operanda nista enaka. Upoštevajte, da sta oba operaterja enaka in med njima ni razlike.

Upoštevajte, da SQLite izraža resnično vrednost z 1 in napačno vrednost z 0.

Primer:

IZBERI10 <6 AS '<', 10 <= 6 AS '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 AS '=', 10 == 6 AS '==',10! = 6 AS '! =', 10 <> 6 AS '<>';

To bo dalo nekaj takega:

Operaterji za ujemanje vzorcev SQLite

" LIKE " - uporablja se za ujemanje vzorcev. Z uporabo » Všeč mi je « lahko iščete vrednosti, ki se ujemajo z vzorcem, določenim z nadomestnim znakom.

Operand na levi je lahko bodisi nizovna dobesedna vrednost bodisi stolpec niza. Vzorec lahko določite na naslednji način:

  • Vsebuje vzorec. Na primer StudentName LIKE '% a%' - s tem bo poiskal imena študentov, ki vsebujejo črko "a" v katerem koli položaju v stolpcu StudentName.
  • Začne se z vzorcem. Na primer, " StudentName LIKE 'a%' " - poiščite imena študentov, ki se začnejo s črko "a".
  • Konča z vzorcem. Na primer, " StudentName LIKE '% a' " - poiščite imena študentov, ki se končajo s črko "a".
  • Ujemanje katerega koli posameznega znaka v nizu z uporabo podčrtaja "_". Na primer, " Študentsko ime LIKE 'J___' " - poiščite imena študentov, ki so dolga 4 znake. Začeti se mora s črko "J", lahko pa ima še druge tri znake za črko "J".

Primeri ujemanja vzorcev:

  1. Pridobite imena študentov, ki se začnejo s črko 'j': 
    IZBERITE ŠTUDENTSKO IME ŠTUDENTJE, KJER ŠTUDENTSKO IME KOT 'j%';

    Rezultat:

  2. Naj se imena študentov končajo s črko 'y': 
    IZBERITE ŠTUDENTSKO IME ŠTUDENTJE, KJER ŠTUDENTSKO IME KOT '% y';

    Rezultat:

  3. Pridobite imena študentov, ki vsebujejo črko 'n': 
    IZBERITE ŠTUDENTSKO IME ŠTUDENTJE, KJER ŠTUDENTSKO IME KOT '% n%';

    Rezultat:

"GLOB" - je enakovreden operaterju LIKE, GLOB pa razlikuje med velikimi in malimi črkami, za razliko od operaterja LIKE. Na primer naslednja dva ukaza vrneta različne rezultate:

IZBERITE 'Jack' GLOB 'j%';IZBERITE 'Jack' KOT 'j%';

Tako boste dobili:

  • Prvi stavek vrne 0 (false), ker je operater GLOB občutljiv na velike in male črke, zato 'j' ni enako 'J'. Vendar bo drugi stavek vrnil 1 (true), ker operator LIKE ne razlikuje med velikimi in malimi črkami, zato je 'j' enako 'J'.

Drugi operaterji:

SQLite IN

Logični operator, ki združuje enega ali več izrazov. Vrnil bo true, le če vsi izrazi dajo vrednost "true". Vrnilo pa bo false le, če bodo vsi izrazi dobili vrednost "false".

Primer:

Naslednja poizvedba bo iskala študente, ki imajo StudentId> 5 in se StudentName začne s črko N, vrnjeni študenti morajo izpolnjevati dva pogoja:

IZBERI *OD ŠTUDENTOVKJE (StudentId> 5) IN (Študentsko ime KOT 'N%');

Kot rezultat boste na zgornjem posnetku zaslona dobili samo "Nancy". Nancy je edina študentka, ki izpolnjuje oba pogoja.

SQLite ALI

Logični operater, ki združuje enega ali več izrazov, tako da, če eden od kombiniranih operatorjev poda true, bo vrnil true. Če pa vsi izrazi dajo false, bo vrnil false.

Primer:

Naslednja poizvedba bo iskala študente, ki imajo StudentId> 5 ali se StudentName začne s črko N, vrnjeni študentje morajo izpolnjevati vsaj enega od pogojev:

IZBERI *OD ŠTUDENTOVKJE (StudentId> 5) ALI (StudentName KOT 'N%');

Tako boste dobili:

Kot rezultat boste na zgornjem posnetku zaslona dobili ime študenta, ki ima v svojem imenu črko "n" in ID študenta, ki ima vrednost> 5.

Kot lahko vidite, se rezultat razlikuje od poizvedbe z operatorjem AND.

SQLite MED

BETWEEN se uporablja za izbiro vrednosti, ki so v območju dveh vrednosti. Na primer, " X MED Y IN Z " bo vrnil true (1), če je vrednost X med obema vrednostma Y in Z. V nasprotnem primeru bo vrnila false (0). " X MED Y IN Z " je enakovreden " X> = Y IN X <= Z ", X mora biti večji ali enak Y in X manjši ali enak Z.

Primer:

V naslednjem primeru poizvedbe bomo napisali poizvedbo, da bomo dobili študente z vrednostjo Id med 5 in 8:

IZBERI *OD ŠTUDENTOVKJE ŠTUDENT MED 5 IN 8;

To bo dalo samo študentom z id 5, 6, 7 in 8:

SQLite IN

Vzame en operand in seznam operandov. Vrnilo se bo true, če je prva vrednost operanda enaka vrednosti operanda s seznama. Operator IN vrne true (1), če seznam operandov vsebuje prvo vrednost operanda znotraj svojih vrednosti. V nasprotnem primeru bo vrnil false (0).

Takole: " col IN (x, y, z) ". To je enakovredno " (col = x) ali (col = y) ali (col = z) ".

Primer:

Naslednja poizvedba bo izbrala samo študente z id 2, 4, 6, 8:

IZBERI *OD ŠTUDENTOVKJE ŠTUDENT ID (2, 4, 6, 8);

Všečkaj to:

Prejšnja poizvedba bo dala natančen rezultat kot naslednja poizvedba, ker sta enakovredni:

IZBERI *OD ŠTUDENTOVKJE (StudentId = 2) ALI (StudentId = 4) ALI (StudentId = 6) ALI (StudentId = 8);

Obe poizvedbi dajeta natančen izhod. Vendar je razlika med obema poizvedbama prva, ki smo jo uporabili pri operaterju "IN". V drugem poizvedbi smo uporabili več operatorjev "ALI".

Operator IN je enakovreden uporabi več operaterjev OR. " WHERE StudentId IN (2, 4, 6, 8) " je enakovreden " WHERE (StudentId = 2) ALI (StudentId = 4) ALI (StudentId = 6) ALI (StudentId = 8); "

Všečkaj to:

SQLite NOT IN

Operand "NOT IN" je nasprotje operaterju IN. Toda z isto sintakso; potreben je en operand in seznam operandov. Vrnilo se bo true, če prva vrednost operanda ne bo enaka vrednosti ene od operand s seznama. vrnil bo true (0), če seznam operandov ne vsebuje prvega operanda. Takole: " col NOT IN (x, y, z) ". To je enakovredno " (col <> x) AND (col <> y) AND (col <> z) ".

Primer:

Naslednja poizvedba bo izbrala študente z ID-ji, ki niso enaki enemu od teh ID-jev 2, 4, 6, 8:

IZBERI *OD ŠTUDENTOVKJE ŠTUDENT NI V (2, 4, 6, 8);

Všečkaj to

Pri prejšnji poizvedbi dobimo natančen rezultat kot naslednjo poizvedbo, ker so enakovredni:

IZBERI *OD ŠTUDENTOVKJE (StudentId <> 2) IN (StudentId <> 4) IN (StudentId <> 6) IN (StudentId <> 8);

Všečkaj to:

Na zgornjem posnetku zaslona

Za pridobitev seznama študentov smo uporabili več neenakih operatorjev "<>", ki niso enaki niti naslednjim ID-jem 2, 4, 6 ali 8. Ta poizvedba bo vrnila vse druge študente, razen teh seznamov Id.

SQLite OBSTAJA

Operatorji EXISTS ne sprejmejo nobenega operanda; za njo je potreben le stavek SELECT. Operator EXISTS bo vrnil true (1), če bodo iz stavka SELECT vrnjene vrstic, in false (0), če iz stavka SELECT sploh ni vrnjenih vrstic.

Primer:

V naslednjem primeru bomo izbrali ime oddelka, če ID oddelka obstaja v tabeli študentov:

IZBERI Ime oddelkaOD ODDELKOV AS dKJE OBSTAJA (IZBERI ODDELJENO OD ŠTUDIJ KATERI KJE d.DepartmentId = s.DepartmentId);

Tako boste dobili:

Vrnjeni bodo le trije oddelki " IT, fizika in umetnost ". In ime oddelka " Matematika " ne bo vrnjeno, ker na njem ni študenta, zato ID oddelka ne obstaja v tabeli študentov. Zato je operater EXISTS prezrl oddelek " Matematika ".

SQLite NE

Obrne rezultat prejšnjega operatorja, ki pride za njim. Na primer:

  • NOT BETWEEN - Vrnilo se bo true, če BETWEEN vrne false in obratno.
  • NOT LIKE - Vrnilo se bo true, če LIKE vrne false in obratno.
  • NOT GLOB - Vrnilo se bo, če GLOB vrne false in obratno.
  • NOT EXISTS - Vrnil se bo true, če EXISTS vrne false in obratno.

Primer:

V naslednjem primeru bomo uporabili operator NOT z operatorjem EXISTS, da dobimo imena oddelkov, ki ne obstajajo v tabeli Študenti, kar je obratni rezultat operaterja EXISTS. Torej, iskanje bo izvedeno prek DepartmentId, ki ne obstaja v tabeli oddelkov.

IZBERI Ime oddelkaOD ODDELKOV AS dKJE NE OBSTAJA (SELECT ODDOD ŠTUDENTOV KOT sKJE d.DepartmentId = s.DepartmentId);

Izhod :

Vrnjen bo le oddelek " Matematika ". Ker je oddelek " Matematika " edini oddelek, ki v tabeli študentov ne obstaja.

Omejevanje in naročanje

Naročilo SQLite

SQLite Order je razvrščanje rezultatov po enem ali več izrazih. Če želite naročiti nabor rezultatov, morate uporabiti stavek ORDER BY, kot sledi:

  • Najprej morate določiti stavek ORDER BY.
  • Na koncu poizvedbe mora biti naveden stavek ORDER BY; za njo je mogoče določiti samo klavzulo LIMIT.
  • Navedite izraz, s katerim boste razvrstili podatke, ta izraz je lahko ime stolpca ali izraz.
  • Po izrazu lahko določite neobvezno smer razvrščanja. Bodisi DESC, če želite, da se podatki padajoče, ali ASC, da se podatki povečujejo. Če ne navedete nobenega od njih, bodo podatki razvrščeni naraščajoče.
  • Več izrazov lahko določite z uporabo "," med seboj.

Primer

V naslednjem primeru bomo izbrali vse študente, razvrščene po njihovih imenih, vendar po padajočem vrstnem redu, nato po imenu oddelka v naraščajočem vrstnem redu:

SELECT s.StudentName, d.DepartmentNameOD ŠTUDENTOV KOT sINNER JOIN Oddelki KOT d NA s.DepartmentId = d.DepartmentIdNAROČITE po d.DepartmentName ASC, s.StudentName DESC;

Tako boste dobili:

  • SQLite bo najprej razvrstil vse študente po imenu oddelka v naraščajočem vrstnem redu
  • Nato bodo za vsako ime oddelka vsi učenci pod tem oddelkom prikazani v padajočem vrstnem redu po njihovih imenih

Omejitev SQLite:

Število vrstic, ki jih vrne vaša poizvedba SQL, lahko omejite s stavkom LIMIT. Na primer, LIMIT 10 vam bo dal samo 10 vrstic in prezrl vse druge vrstice.

V stavku LIMIT lahko s pomočjo stavka OFFSET izberete določeno število vrstic, začenši z določenega položaja. Na primer, " LIMIT 4 OFFSET 4 " bo prezrl prve 4 vrstice in vrnil 4 vrstice, začenši s petimi vrsticami, tako da boste dobili vrstice 5,6,7 in 8.

Upoštevajte, da je klavzula OFFSET neobvezna, lahko jo zapišete kot " LIMIT 4, 4 " in dala vam bo natančne rezultate.

Primer :

V naslednjem primeru bomo z uporabo poizvedbe vrnili samo 3 študente, začenši s študentskim id 5:

SELECT * FROM Student LIMIT 4,3;

Tako boste dobili samo tri študente, ki bodo začeli od vrstice 5. Tako boste dobili vrstice z StudentId 5, 6 in 7:

Odstranjevanje dvojnikov

Če vaša poizvedba SQL vrne podvojene vrednosti, lahko s ključno besedo " DISTINCT " odstranite te dvojnike in vrnete ločene vrednosti. Po delu ključa DISTINCT lahko določite več stolpcev.

Primer:

Naslednja poizvedba bo vrnila podvojene "vrednosti imen oddelkov": Tu imamo podvojene vrednosti z imeni IT, fizika in umetnost.

SELECT d.DepartmentNameOD ŠTUDENTOV KOT sINNER JOIN Oddelki AS d NA s.DepartmentId = d.DepartmentId;

Tako boste dobili podvojene vrednosti za ime oddelka:

Upoštevajte, kako obstajajo podvojene vrednosti za ime oddelka. Zdaj bomo z isto poizvedbo uporabili ključno besedo DISTINCT, da odstranimo te dvojnike in dobimo samo enolične vrednosti. Všečkaj to:

IZBERITE DISTINCT d.DepartmentNameOD ŠTUDENTOV KOT sINNER JOIN Oddelki AS d NA s.DepartmentId = d.DepartmentId;

Tako boste dobili samo tri edinstvene vrednosti za stolpec z imenom oddelka:

Skupno

SQLite agregati so vgrajene funkcije, definirane v SQLite, ki bodo združile več vrednosti več vrstic v eno vrednost.

Tukaj so agregati, ki jih podpira SQLite:

SQLite AVG ()

Vrnjeno povprečje za vse vrednosti x.

Primer:

V naslednjem primeru bomo dobili povprečno oceno študentov na vseh izpitih:

SELECT AVG (Mark) FROM Marks;

Tako boste dobili vrednost "18.375":

Ti rezultati izhajajo iz seštevanja vseh vrednosti znamk, deljenega z njihovim štetjem.

COUNT () - COUNT (X) ali COUNT (*)

Vrne skupno število števila pojavitev vrednosti x. Tu je nekaj možnosti, ki jih lahko uporabite s COUNT:

  • COUNT (x): šteje samo x vrednosti, pri čemer je x ime stolpca. Vrednosti NULL bodo prezrte.
  • ŠTEVILO (*): preštejte vse vrstice iz vseh stolpcev.
  • COUNT (DISTINCT x): pred x lahko določite ključno besedo DISTINCT, ki bo prejela število različnih vrednosti x.

Primer

V naslednjem primeru bomo dobili skupno število oddelkov z COUNT (DepartmentId), COUNT (*) in COUNT (DISTINCT DepartmentId) in kako se razlikujejo:

SELECT COUNT (DepartmentId), COUNT (DISTINCT DepartmentId), COUNT (*) FROM Students;

Tako boste dobili:

Kot sledeče:

  • COUNT (DepartmentId) vam bo dal število vseh ID-jev oddelka in bo prezrl ničelne vrednosti.
  • COUNT (DISTINCT DepartmentId) vam daje različne vrednosti DepartmentId, ki so le 3. Katere so tri različne vrednosti imena oddelka. Upoštevajte, da je v imenu študenta 8 vrednosti imena oddelka. Toda le različne tri vrednote, ki so matematika, informatika in fizika.
  • COUNT (*) šteje število vrstic v tabeli študentov, ki so 10 vrstic za 10 študentov.

GROUP_CONCAT () - GROUP_CONCAT (X) ali GROUP_CONCAT (X, Y)

Skupinska funkcija GROUP_CONCAT združi večkratnike v eno vrednost z vejico, da jih loči. Ima naslednje možnosti:

  • GROUP_CONCAT (X): To bo združilo vso vrednost x v en niz z vejico ",", ki se uporablja kot ločilo med vrednostmi. Vrednosti NULL bodo prezrte.
  • GROUP_CONCAT (X, Y): To bo združilo vrednosti x v en niz, pri čemer bo vrednost y uporabljena kot ločilo med vsako vrednostjo namesto privzetega ločila ','. Tudi vrednosti NULL bodo prezrte.
  • GROUP_CONCAT (DISTINCT X): To bo združilo vse različne vrednosti x v en niz z vejico ",", ki se uporablja kot ločilo med vrednostmi. Vrednosti NULL bodo prezrte.

Primer GROUP_CONCAT (Ime oddelka)

Naslednja poizvedba bo združila vse vrednosti imena oddelka iz tabele študentov in oddelkov v eno ločeno vejico. Namesto da bi vrnili seznam vrednosti, po eno vrednost v vsaki vrstici. Vrnil bo samo eno vrednost v eno vrstico, vse vrednosti pa bodo ločene z vejico:

SELECT GROUP_CONCAT (d.DepartmentName)OD ŠTUDENTOV KOT sINNER JOIN Oddelki AS d NA s.DepartmentId = d.DepartmentId;

Tako boste dobili:

Tako boste dobili seznam vrednosti imen 8 oddelkov, združenih v eno ločeno vejico.

Primer GROUP_CONCAT (DISTINCTName oddelka)

Naslednja poizvedba bo združila različne vrednosti imena oddelka iz tabele študentov in oddelkov v eno ločeno vejico:

SELECT GROUP_CONCAT (DISTINCT d.DepartmentName)OD ŠTUDENTOV KOT sINNER JOIN Oddelki AS d NA s.DepartmentId = d.DepartmentId;

Tako boste dobili:

Opazite, kako se rezultat razlikuje od prejšnjega rezultata; vrnjene so bile le tri vrednosti, ki so imena različnih oddelkov, in podvojene vrednosti so bile odstranjene.

GROUP_CONCAT (Ime oddelka, '&') Primer

Naslednja poizvedba bo združila vse vrednosti stolpca z imeni oddelka iz tabele študentov in oddelkov v en niz, vendar z ločnico namesto z vejico kot ločilo:

SELECT GROUP_CONCAT (d.DepartmentName, '&')OD ŠTUDENTOV KOT sINNER JOIN Oddelki AS d NA s.DepartmentId = d.DepartmentId;

Tako boste dobili:

Upoštevajte, kako se znak "&" uporablja namesto privzetega znaka "," za ločevanje med vrednostmi.

SQLite MAX () in MIN ()

MAX (X) vam vrne najvišjo vrednost med vrednostmi X. MAX bo vrnil NULL vrednost, če so vse vrednosti x nič. Medtem ko vam MIN (X) vrne najmanjšo vrednost od vrednosti X. MIN bo vrnil NULL vrednost, če so vse vrednosti X nič.

Primer

V naslednji poizvedbi bomo s pomočjo funkcij MIN in MAX dobili najvišjo in najnižjo oceno iz tabele " Oznake ":

IZBERI MAX (Mark), MIN (Mark) FROM Marks;

Tako boste dobili:

SQLite SUM (x), skupaj (x)

Oba bosta vrnila vsoto vseh x vrednosti. Razlikujejo pa se v naslednjem:

  • SUM bo vrnil nič, če so vse vrednosti nič, Total pa vrne 0.
  • TOTAL vedno vrne vrednosti s plavajočo vejico. SUM vrne celoštevilčno vrednost, če so vse vrednosti x celo število. Če pa vrednosti niso celo število, bo vrnila vrednost s plavajočo vejico.

Primer

V naslednji poizvedbi bomo uporabili SUM in total, da dobimo vsoto vseh ocen v tabelah " Oznake ":

IZBERI SUM (Oznaka), SKUPAJ (Oznaka) IZ Oznak;

Tako boste dobili:

Kot lahko vidite, TOTAL vedno vrne plavajočo vejico. Toda SUM vrne celoštevilčno vrednost, ker so vrednosti v stolpcu »Označi« morda v celih številih.

Razlika med primerom SUM in SKUPAJ:

V naslednjem poizvedbi bomo prikazali razliko med SUM in SKUPAJ, ko dobijo SUM NULL vrednosti:

IZBERITE SUM (Oznaka), SKUPAJ (Oznaka) IZ OZNAK, KJER TestId = 4;

Tako boste dobili:

Upoštevajte, da za TestId = 4 ni nobenih oznak, zato za ta test obstajajo ničelne vrednosti. SUM vrne ničlo kot prazno, TOTAL pa vrne 0.

Skupina BY

Stavek GROUP BY se uporablja za določanje enega ali več stolpcev, ki bodo uporabljeni za združevanje vrstic v skupine. Vrstice z enakimi vrednostmi bodo zbrane (razporejene) skupaj v skupine.

Za kateri koli drug stolpec, ki ni vključen v skupino po stolpcih, lahko zanj uporabite agregatno funkcijo.

Primer:

Naslednja poizvedba vam bo pokazala skupno število študentov, prisotnih v posameznem oddelku.

SELECT d.DepartmentName, COUNT (s.StudentId) KOT Študentska številkaOD ŠTUDENTOV KOT sINNER JOIN Oddelki KOT d NA s.DepartmentId = d.DepartmentIdSKUPINA PO d. DepartmentName;

Tako boste dobili:

Klavzula GROUPBY DepartmentName bo vse študente združila v eno skupino za vsako ime oddelka. Za vsako skupino "oddelka" bo štela študente.

Klavzula HAVING

Če želite filtrirati skupine, ki jih vrne stavek GROUP BY, lahko za GROUP BY določite stavek "HAVING" z izrazom. Izraz bo uporabljen za filtriranje teh skupin.

Primer

V naslednjem poizvedbi bomo izbrali tiste oddelke, na katerih sta samo dva študenta:

SELECT d.DepartmentName, COUNT (s.StudentId) KOT Študentska številkaOD ŠTUDENTOV KOT sINNER JOIN Oddelki KOT d NA s.DepartmentId = d.DepartmentIdSKUPINA PO d. DepartmentNameIMAJO ŠTEVILO (s.StudentId) = 2;

Tako boste dobili:

Stavek HAVING COUNT (S.StudentId) = 2 bo filtriral vrnjene skupine in vrnil samo tiste skupine, ki vsebujejo natanko dva študenta. V našem primeru ima oddelek za umetnost 2 študenta, zato je prikazan v rezultatih.

SQLite poizvedbe in podpoizvedbe

Znotraj katere koli poizvedbe lahko uporabite drugo poizvedbo bodisi v SELECT, INSERT, DELETE, UPDATE ali znotraj drugega podpoizvedbe.

Ta ugnezdena poizvedba se imenuje podpoizvedba. Zdaj bomo videli nekaj primerov uporabe podpoizvedb v stavku SELECT. Vendar bomo v vadnici za spreminjanje podatkov videli, kako lahko uporabljamo podpoizvedbe z izjavo INSERT, DELETE in UPDATE.

Uporaba podpoizvedbe v primeru stavka FROM

V naslednjo poizvedbo bomo vključili podpoizvedbo znotraj stavka FROM:

IZBERIs.StudentName, t.MarkOD ŠTUDENTOV KOT sINNER JOIN(SELECT StudentId, MarkIZ TESTOV KOT tINNER JOIN Oznake AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;

Poizvedba:

 SELECT StudentId, MarkIZ TESTOV KOT tINNER JOIN Oznake AS m ON t.TestId = m.TestId

Zgornja poizvedba se tukaj imenuje podpoizvedba, ker je ugnezdena znotraj stavka FROM. Upoštevajte, da smo mu dali vzdevek "t", da se lahko v poizvedbi sklicujemo na vrnjene stolpce.

Ta poizvedba vam bo dala:

Torej v našem primeru

  • s.StudentName je izbran iz glavne poizvedbe, ki daje ime študentov in
  • t.Mark je izbran iz podpoizvedbe; ki daje ocene, ki jih je pridobil vsak od teh študentov

Uporaba podpoizvedbe v primeru stavka WHERE

V naslednjo poizvedbo bomo vključili podpoizvedbo v stavek WHERE:

IZBERI Ime oddelkaOD ODDELKOV AS dKJE NE OBSTAJA (SELECT ODDOD ŠTUDENTOV KOT sKJE d.DepartmentId = s.DepartmentId);

Poizvedba:

IZBERI ID oddelkaOD ŠTUDENTOV KOT sKJE d.DepartmentId = s.DepartmentId

Zgornja poizvedba se tukaj imenuje podpoizvedba, ker je ugnezdena v stavek WHERE. Podpoizvedba bo vrnila vrednosti DepartmentId, ki jih bo uporabil operater NE OBSTAJA.

Ta poizvedba vam bo dala:

V zgornji poizvedbi smo izbrali oddelek, v katerega ni vpisan noben študent. Kar je tukaj oddelek za matematiko.

Set Operations - UNION, križišče

SQLite podpira naslednje operacije SET:

UNIJA IN UNIJA VSE

Združuje enega ali več naborov rezultatov (skupina vrstic), vrnjenih iz več stavkov SELECT, v en nabor rezultatov.

UNION bo vrnil različne vrednosti. Vendar pa UNION ALL ne bo in bo vključeval dvojnikov.

Ime stolpca bo ime stolpca, določeno v prvem stavku SELECT.

Primer UNION

V naslednjem primeru bomo dobili seznam DepartmentId iz tabele študentov in seznam DepartmentId iz tabele oddelkov v istem stolpcu:

IZBERI ODDELEK KOT ODDELEK ODDELJEN OD ŠTUDENTOVUNIJAIZBERI ID ODDELKA;

Tako boste dobili:

Poizvedba vrne samo 5 vrstic, ki so različne vrednosti ID oddelka. Upoštevajte prvo vrednost, ki je ničelna vrednost.

Primer SQLite UNION ALL

V naslednjem primeru bomo dobili seznam DepartmentId iz tabele študentov in seznam DepartmentId iz tabele oddelkov v istem stolpcu:

IZBERI ODDELEK KOT ODDELEK ODDELJEN OD ŠTUDENTOVUNIJA VSEIZBERI ID ODDELKA;

Tako boste dobili:

Poizvedba bo vrnila 14 vrstic, 10 vrstic iz tabele študentov in 4 iz tabele oddelkov. Upoštevajte, da so v vrnjenih vrednostih dvojniki. Upoštevajte tudi, da je bilo ime stolpca določeno v prvem stavku SELECT.

Zdaj pa poglejmo, kako bodo UNION vsi dali drugačne rezultate, če bomo UNION ALL zamenjali z UNION:

SQLite INTERSECT

Vrne vrednosti, ki obstajajo v obeh kombiniranih nizih rezultatov. Vrednosti, ki obstajajo v enem od kombiniranih nizov rezultatov, bodo prezrte.

Primer

V naslednji poizvedbi bomo izbrali vrednosti DepartmentId, ki obstajajo v tabelah Študenti in Oddelki v stolpcu DepartmentId:

IZBERI ODDELEK OD ŠTUDENTOVPresečiIZBERI ID ODDELKA;

Tako boste dobili:

Poizvedba vrne le tri vrednosti 1, 2 in 3. Katere so vrednosti, ki obstajajo v obeh tabelah.

Vendar vrednosti null in 4 nista bili vključeni, ker vrednost null obstaja samo v tabeli študentov in ne v tabeli oddelkov. In vrednost 4 obstaja v tabeli oddelkov in ne v tabeli študentov.

Zato sta bili vrednosti NULL in 4 prezrti in nista bili vključeni v vrnjene vrednosti.

RAZEN

Recimo, če imate dva seznama vrstic, list1 in list2 in želite vrstice samo s seznama1, ki ne obstaja na seznamu2, lahko uporabite klavzulo "RAZEN". Stavek EXCEPT primerja oba seznama in vrne tiste vrstice, ki obstajajo v seznamu1 in ne obstajajo na seznamu2.

Primer

V naslednjem poizvedbi bomo izbrali vrednosti DepartmentId, ki obstajajo v tabeli oddelkov in ne obstajajo v tabeli študentov:

IZBERI ID ODDELKARAZENIZBERI ODDELEK OD ŠTUDENTOV;

Tako boste dobili:

Poizvedba vrne samo vrednost 4. Kar je edina vrednost, ki obstaja v tabeli oddelkov in ne obstaja v tabeli študentov.

NULL ravnanje

Vrednost " NULL " je posebna vrednost v SQLite. Uporablja se za predstavitev vrednosti, ki je neznana ali manjkajoča. Upoštevajte, da se ničelna vrednost popolnoma razlikuje od vrednosti " 0 " ali praznega "". Ker je 0 in prazna vrednost znana, pa nična vrednost ni znana.

Vrednosti NULL zahtevajo posebno obdelavo v SQLite, zdaj bomo videli, kako ravnati z vrednostmi NULL.

Poiščite NULL vrednosti

Za iskanje ničelnih vrednosti ne morete uporabiti običajnega operatorja enakosti (=). Na primer, naslednja poizvedba išče študente, ki imajo ničelno vrednost DepartmentId:

SELECT * FROM Students WHERE DepartmentId = NULL;

Ta poizvedba ne bo dala nobenega rezultata:

Ker vrednost NULL ni enaka nobeni drugi vrednosti, je sama vključila ničelno vrednost, zato ni vrnila nobenega rezultata.

  • Da pa bo poizvedba delovala, morate za iskanje ničelnih vrednosti uporabiti operator "IS NULL", kot sledi:
IZBERITE * OD ŠTUDENTOV, KJER JE ODDELEK NIČEN;

Tako boste dobili:

Poizvedba bo vrnila tiste študente, ki imajo ničelno vrednost DepartmentId.

  • Če želite dobiti tiste vrednosti, ki niso nič, potem morate uporabiti operator " NI NULL ", kot je ta:
IZBERITE * OD ŠTUDENTOV, KJER ODDELEK NI NULL;

Tako boste dobili:

Poizvedba bo vrnila tiste študente, ki nimajo vrednosti NULL DepartmentId.

Pogojni rezultati

Če imate seznam vrednosti in želite katero od njih izbrati glede na nekatere pogoje. V tem primeru mora biti pogoj za to vrednost resničen, da bo izbran.

Izraz CASE bo ovrednotil seznam pogojev za vse vrednosti. Če je pogoj resničen, bo vrnil to vrednost.

Če imate na primer stolpec "Ocena" in želite na podlagi vrednosti ocene izbrati besedilno vrednost, kot sledi:

- "Odlično", če je ocena višja od 85.

- "Zelo dobro", če je ocena med 70 in 85.

- "Dobro", če je ocena med 60 in 70.

Nato lahko za to uporabite izraz CASE.

S tem lahko določimo nekaj logike v stavku SELECT, tako da lahko izberete določene rezultate, odvisno od določenih pogojev, na primer stavka if.

Operater CASE lahko definiramo z različnimi sintaksami, kot sledi:

  1. Uporabite lahko različne pogoje:
OVITEKKDAJ pogoj1 TADA rezultat1KDAJ pogoj2 TADA rezultat2KDAJ pogoj3 Nato rezultat3… DRUGI rezultatnKONEC
  1. Lahko pa uporabite samo en izraz in izberete različne možne vrednosti:
CASE izrazKDAJ vrednost1 TADA rezultat1KDAJ vrednost2 TADA rezultat2WHEN value3 THEN result3… ELSE restulnKONEC

Stavek ELSE ni obvezen.

Primer

V naslednjem primeru bomo uporabili izraz CASE z vrednostjo NULL v stolpcu Id oddelka v tabeli Študenti, da prikažemo besedilo 'Brez oddelka', kot sledi:

IZBERIŠtudentsko ime,OVITEKKDAJ ODDELEK JE NULL, TAKO 'Ni oddelka'ELSE Oddelek IdEND AS DepartmentIdOD študentov;
  • Operator CASE bo preveril vrednost DepartmentId, ali je nična ali ne.
  • Če gre za NULL vrednost, bo namesto vrednosti DepartmentId izbral dobesedno vrednost 'No Department'.
  • Če ni ničelna vrednost, bo izbrala vrednost stolpca DepartmentId.

Tako boste dobili izhod, kot je prikazano spodaj:

Pogost izraz tabele

Pogosti izrazi tabel (CTE) so podpoizvedbe, ki so definirane znotraj stavka SQL z danim imenom.

Ima prednost pred podpoizvedbami, ker je opredeljena iz stavkov SQL in bo olajšala branje, vzdrževanje in razumevanje poizvedb.

Pogost izraz tabele lahko definiramo tako, da postavimo stavek WITH pred stavke SELECT na naslednji način:

Z imenom CTEAS(Stavek SELECT)Izberite, posodobite, vstavite ali posodobite izjavo tukaj IZ CTE

" Ime CTE " je katero koli ime, ki ga lahko navedete za CTE, z njim se lahko pozneje sklicujete. Upoštevajte, da lahko na CTE določite stavek SELECT, UPDATE, INSERT ali DELETE

Zdaj pa si oglejmo primer uporabe CTE v stavku SELECT.

Primer

V naslednjem primeru bomo definirali CTE iz stavka SELECT in ga nato uporabili za drugo poizvedbo:

Z AllDepartmentsAS(IZBERI ID oddelka, ime oddelkaIZ ODDELKOV)IZBERIs.StudentId,s.StudentName,a.OddelekNameOD ŠTUDENTOV KOT sINNER JOIN AllDepartments AS ON ON s.DepartmentId = a.DepartmentId;

V tej poizvedbi smo definirali CTE in mu dali ime " AllDepartments ". Ta CTE je bil definiran iz poizvedbe SELECT:

 IZBERI ID oddelka, ime oddelkaIZ ODDELKOV

Potem, ko smo definirali CTE, smo ga uporabili v poizvedbi SELECT, ki sledi za njim.

Upoštevajte, da izrazi skupne tabele ne vplivajo na rezultat poizvedbe. To je način, da definirate logični pogled ali podpoizvedbo, da jih ponovno uporabite v isti poizvedbi. Pogosti izrazi tabele so kot spremenljivka, ki jo prijavite, in jo znova uporabite kot podpoizvedbo. Samo stavek SELECT vpliva na rezultat poizvedbe.

Ta poizvedba vam bo dala:

Napredne poizvedbe

Napredne poizvedbe so tiste poizvedbe, ki vsebujejo zapletena združevanja, podpoizvedbe in nekatere agregate. V naslednjem poglavju bomo videli primer napredne poizvedbe:

Kje dobimo,

  • Imena oddelkov z vsemi študenti za vsak oddelek
  • Učenci poimenujejo ločeno z vejico in
  • Prikaz oddelka, v katerem so vsaj trije študenti
IZBERId.DepartmentName,COUNT (s.StudentId) Število študentov,GROUP_CONCAT (StudentName) AS ŠtudentiOD ODDELKOV AS dINNER JOIN Student as AS on s.DepartmentId = d.DepartmentIdGROUP BY d.DepartmentNameIMAJO ŠTEVILO (s.StudentId)> = 3;

Dodali smo klavzulo JOIN, da dobimo DepartmentName iz tabele Departments. Po tem smo dodali stavek GROUP BY z dvema skupnima funkcijama:

  • "COUNT" za štetje študentov za vsako skupino oddelkov.
  • GROUP_CONCAT za združitev študentov za vsako skupino z vejico, ločeno v enem nizu.
  • Po GROUP BY smo s klavzulo HAVING filtrirali oddelke in izbrali samo tiste oddelke, ki imajo vsaj 3 študente.

Rezultat bo naslednji:

Povzetek:

To je bil uvod v pisanje poizvedb SQLite in osnove poizvedbe v zbirki podatkov ter kako lahko filtrirate vrnjene podatke. Zdaj lahko pišete lastne poizvedbe SQLite.