Ak chcete písať dotazy SQL do databázy SQLite, musíte vedieť, ako fungujú klauzuly SELECT, FROM, WHERE, GROUP BY, ORDER BY a LIMIT a ako ich používať.
Počas tohto tutoriálu sa naučíte, ako tieto klauzuly používať a ako písať klauzuly SQLite.
V tomto návode sa naučíte
- Čítanie údajov s výberom
- Mená a prezývky
- KDE
- Obmedzenie a objednávanie
- Odstraňujú sa duplikáty
- Agregát
- Zoskupiť podľa
- Dopyt a poddotaz
- Nastaviť operácie -UNION, pretínať sa
- NULL manipulácia
- Podmienené výsledky
- Spoločný tabuľkový výraz
- Pokročilé dotazy
Čítanie údajov s výberom
Klauzula SELECT je hlavným príkazom, ktorý používate na dopytovanie databázy SQLite. V klauzule SELECT uvádzate, čo chcete vybrať. Pred klauzulou select sa však pozrime, odkiaľ môžeme vyberať údaje pomocou klauzuly FROM.
Klauzula FROM sa používa na určenie, kde chcete vybrať údaje. V klauzule from môžete určiť jednu alebo viac tabuliek alebo poddotazov, z ktorých chcete vybrať údaje, ako si ukážeme ďalej v tutoriáloch.
Všimnite si, že pre všetky nasledujúce príklady musíte spustiť sqlite3.exe a otvoriť plynulé pripojenie k ukážkovej databáze:
Krok 1) V tomto kroku
- Otvorte Môj počítač a prejdite do nasledujúceho adresára " C: \ sqlite " a
- Potom otvorte " sqlite3.exe ":
Krok 2) Otvorte databázu „ TutorialsSampleDB.db “ pomocou nasledujúceho príkazu:
Teraz ste pripravení spustiť akýkoľvek typ dotazu v databáze.
V klauzule SELECT môžete zvoliť nielen názov stĺpca, ale máte aj veľa ďalších možností, ako určiť, čo sa má vybrať. Ako nasledujúce:
VYBERTE *
Tento príkaz vyberie všetky stĺpce zo všetkých odkazovaných tabuliek (alebo poddotazov) v klauzule FROM. Napríklad:
VYBERTE *OD študentovVNÚTORNÉ PRIPOJENIE Departments ON Students.DepartmentId = Departments.DepartmentId;
Týmto sa vyberú všetky stĺpce z tabuliek študentov aj z tabuliek katedier:
VYBRAŤ názov tabuľky. *
Týmto sa vyberú všetky stĺpce iba z tabuľky „tablename“. Napríklad:
VYBERTE študentov. *OD študentovVNÚTORNÉ PRIPOJENIE Departments ON Students.DepartmentId = Departments.DepartmentId;
Týmto sa vyberú všetky stĺpce iba z tabuľky študentov:
Doslova hodnota
Doslovná hodnota je konštantná hodnota, ktorú je možné určiť v príkaze select. Doslovné hodnoty môžete bežne použiť rovnakým spôsobom, aký používate názvy stĺpcov v klauzule SELECT. Tieto doslovné hodnoty sa zobrazia pre každý riadok z riadkov vrátených dotazom SQL.
Tu je niekoľko príkladov rôznych literálnych hodnôt, ktoré môžete zvoliť:
- Numeric Literal - čísla v ľubovoľnom formáte ako 1, 2,55, ... atď.
- Reťazcové literály - ľubovoľný reťazec „USA“, „toto je ukážkový text“,… atď.
- NULL - hodnota NULL.
- Current_TIME - dá vám aktuálny čas.
- CURRENT_DATE - získate aktuálny dátum.
To môže byť užitočné v niektorých situáciách, keď musíte zvoliť konštantnú hodnotu pre všetky vrátené riadky. Napríklad ak chcete vybrať všetkých študentov z tabuľky Študenti s novým stĺpcom s názvom krajina, ktorý obsahuje hodnotu „USA“, môžete to urobiť:
SELECT *, 'USA' AS Krajina OD študentov;
Získate tak všetky stĺpce študentov a nový stĺpec „Krajina“, ako je tento:
Upozorňujeme, že tento nový stĺpec Krajina nie je v skutočnosti novým stĺpcom pridaným do tabuľky. Je to virtuálny stĺpec, ktorý sa vytvorí v dotaze na zobrazenie výsledkov a nebude sa vytvárať v tabuľke.
Mená a prezývky
Alias je nový názov pre stĺpec, ktorý vám umožní vybrať stĺpec s novým názvom. Aliasy stĺpcov sa zadávajú pomocou kľúčového slova „AS“.
Napríklad, ak chcete vybrať stĺpec StudentName, ktorý sa má vrátiť s „Student Name“ namiesto „StudentName“, môžete mu dať alias, ako je tento:
VYBERTE StudentName AS 'Meno študenta' OD študentov;
Takto získate mená študentov s menom „Student Name“ namiesto „StudentName“, napríklad takto:
Upozorňujeme, že názov stĺpca je stále „ StudentName “; stĺpec StudentName je stále rovnaký, aliasom sa nemení.
Alias nezmení názov stĺpca; iba zmení zobrazovaný názov v klauzule SELECT.
Upozorňujeme tiež, že kľúčové slovo „AS“ je voliteľné. Môžete ho vložiť aj bez neho, napríklad takto:
VYBERTE StudentName 'Meno študenta' OD študentov;
Poskytne vám úplne rovnaký výstup ako predchádzajúci dopyt:
Môžete tiež dať aliasy tabuliek, nielen stĺpcov. S rovnakým kľúčovým slovom „AS“. Môžete napríklad urobiť toto:
SELECT s. * OD študentov AS;
Získate tak všetky stĺpce v tabuľke Študenti:
To môže byť veľmi užitočné, ak sa pripájate k viac ako jednej tabuľke; namiesto opakovania celého názvu tabuľky v dotaze môžete každej tabuľke priradiť krátky alias. Napríklad v nasledujúcom dotaze:
VYBERTE študentov. Názov študenta, oddelenia. Názov oddeleniaOD študentovVNÚTORNÉ PRIPOJENIE Departments ON Students.DepartmentId = Departments.DepartmentId;
V tomto dotaze sa vyberie meno každého študenta z tabuľky „Študenti“ a jeho názov z tabuľky „Oddelenia“:
Rovnaký dopyt však možno napísať takto:
VYBERTE s.StudentName, d.DepartmentNameZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentId;
- Dali sme študentom tabuľku alias „s“ a oddeleniam alias „d“.
- Potom sme namiesto celého názvu tabuľky použili ich aliasy, aby sme sa na ne odvolali.
- INNER JOIN spája dve alebo viac tabuliek dohromady pomocou podmienky. V našom príklade sme sa pripojili k tabuľke Studenti s tabuľkou Departments so stĺpcom DepartmentId. K dispozícii je tiež podrobné vysvetlenie VNÚTORNÉHO PRIPOJENIA v návode „Pripojenie SQLite“.
Získate presný výstup ako predchádzajúci dopyt:
KDE
Samotné písanie dotazov SQL pomocou klauzuly SELECT s klauzulou FROM, ako sme videli v predchádzajúcej časti, vám poskytne všetky riadky z tabuliek. Ak však chcete filtrované vrátené údaje, musíte pridať klauzulu „WHERE“.
Klauzula WHERE sa používa na filtrovanie výsledkovej sady vrátenej dotazom SQL. Takto funguje klauzula WHERE:
- V klauzule WHERE môžete určiť „výraz“.
- Tento výraz sa vyhodnotí pre každý riadok vrátený z tabuliek uvedených v klauzule FROM.
- Výraz bude vyhodnotený ako boolovský výraz s výsledkom buď true, false, alebo null.
- Potom sa vrátia iba riadky, pre ktoré bol výraz vyhodnotený so skutočnou hodnotou, a tie, ktoré majú falošné alebo nulové výsledky, budú ignorované a nezahrnuté do množiny výsledkov.
- Ak chcete filtrovať množinu výsledkov pomocou klauzuly WHERE, musíte použiť výrazy a operátory.
Zoznam operátorov v SQLite a spôsob ich použitia
V nasledujúcej časti vysvetlíme, ako môžete filtrovať pomocou výrazu a operátorov.
Výraz je jedna alebo viac doslovných hodnôt alebo stĺpcov navzájom spojených s operátorom.
Upozorňujeme, že výrazy môžete použiť v klauzule SELECT aj v klauzule WHERE.
V nasledujúcich príkladoch vyskúšame výrazy a operátory v klauzule select aj v klauzule WHERE. Aby sme vám ukázali, ako vystupujú.
Existujú rôzne typy výrazov a operátorov, ktoré môžete určiť nasledovne:
SQLite operátor zreťazenia "||"
Tento operátor sa používa na zreťazenie jednej alebo viacerých literálnych hodnôt alebo stĺpcov navzájom. Bude produkovať jeden reťazec výsledkov zo všetkých zreťazených literálnych hodnôt alebo stĺpcov. Napríklad:
VYBERTE 'ID s menom:' || StudentId || StudentName AS StudentIdWithNameOD študentov;
Zreťazí sa to do nového aliasu " StudentIdWithName ":
- Doslovná hodnota reťazca " Id s menom: "
- s hodnotou stĺpca " StudentId " a
- s hodnotou zo stĺpca „ StudentName “
Operátor SQLite CAST:
Operátor CAST sa používa na prevod hodnoty z údajového typu na iný údajový typ.
Napríklad, ak máte číselnú hodnotu uloženú ako reťazcovú hodnotu, ako je táto „ 12,5 “, a chcete ju previesť na číselnú hodnotu, môžete na to použiť operátor CAST takto „ CAST („ 12,5 “AS SKUTOČNÉ) “. Alebo ak máte desatinnú hodnotu ako 12,5 a potrebujete získať iba celočíselnú časť, môžete ju odovzdať celému číslu ako je tento „CAST (12,5 AS INTEGER)“.
Príklad
V nasledujúcom príkaze sa pokúsime previesť rôzne hodnoty do iných dátových typov:
VYBERTE CAST ('12 .5 'AKO SKUTOČNÉ) ToReal, CAST (12.5 AS INTEGER) AS ToInteger;
Získate tak:
Výsledok je nasledovný:
- CAST ('12 .5 'AKO SKUTOČNÝ) - hodnota '12 .5' je hodnota reťazca, bude prevedená na SKUTOČNÚ hodnotu.
- CAST (12.5 AS INTEGER) - hodnota 12.5 je desatinná hodnota, bude prevedená na celočíselnú hodnotu. Desatinná časť bude skrátená a stane sa 12.
Aritmetické operátory SQLite:
Vezmite dve alebo viac číselných literálnych hodnôt alebo numerických stĺpcov a vráťte jednu číselnú hodnotu. Aritmetické operátory podporované v SQLite sú:
|
Príklad:
V nasledujúcom príklade vyskúšame päť aritmetických operátorov s rovnakými literálnymi číselnými hodnotami
klauzula select:
VYBERTE 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Získate tak:
Všimnite si, ako sme tu použili príkaz SELECT bez klauzuly FROM. A to je v SQLite povolené, pokiaľ vyberáme literálne hodnoty.
Operátory porovnania SQLite
Porovnajte navzájom dva operandy a vráťte true alebo false takto:
|
Upozorňujeme, že SQLite vyjadruje skutočnú hodnotu 1 a nepravdivú 0.
Príklad:
VYBERTE10 <6 AS '<', 10 <= 6 AS '<=',10> 6 AS '>', 10> = 6 AS '> =',10 = 6 AS '=', 10 == 6 AS '==',10! = 6 AS '! =', 10 <> 6 AS '<>';
Takto získate niečo také:
Operátory porovnávania vzorov SQLite
„ LIKE “ - používa sa na priraďovanie vzorov. Pomocou tlačidla „ Páči sa mi to “ môžete vyhľadávať hodnoty, ktoré sa zhodujú so vzorom zadaným pomocou zástupného znaku.
Operand vľavo môže byť buď hodnota reťazcového literálu, alebo stĺpec reťazca. Vzor je možné určiť nasledovne:
- Obsahuje vzor. Napríklad StudentName LIKE '% a%' - vyhľadá mená študentov, ktoré obsahujú písmeno "a" na ľubovoľnej pozícii v stĺpci StudentName.
- Začína vzorom. Napríklad „ StudentName LIKE 'a%' “ - vyhľadajte mená študentov, ktoré začínajú na písmeno „a“.
- Končí sa vzorom. Napríklad „ StudentName LIKE '% a' " - Vyhľadajte mená študentov, ktoré končia písmenom „a“.
- Zhoda ľubovoľného jedného znaku v reťazci pomocou znaku podčiarknutia „_“. Napríklad „ StudentName LIKE 'J___' “ - Vyhľadajte mená študentov, ktoré majú dĺžku 4 znaky. Musí sa začínať písmenom „J“ a za písmenom „J“ môže mať ďalšie tri ďalšie znaky.
Príklady zhody vzorov:
- Získajte mená študentov, ktoré sa začínajú písmenom „j“:
VYBERTE StudentName OD študentov, KDE StudentName LIKE 'j%';
Výsledok:
- Nechajte mená študentov zakončené písmenom „y“:
VYBERTE StudentName FROM Students WHERE StudentName LIKE '% y';
Výsledok:
- Získajte mená študentov, ktoré obsahujú písmeno „n“:
VYBERTE StudentName OD študentov, KDE StudentName LIKE '% n%';
Výsledok:
„GLOB“ - je ekvivalentom operátora LIKE, ale GLOB rozlišuje veľké a malé písmená, na rozdiel od operátora LIKE. Napríklad nasledujúce dva príkazy vrátia odlišné výsledky:
VYBERTE 'Jack' GLOB 'j%';VYBERTE 'Jack' AKO 'j%';
Získate tak:
- Prvý príkaz vracia hodnotu 0 (false), pretože operátor GLOB rozlišuje veľké a malé písmená, takže hodnota „j“ sa nerovná hodnote „J“. Druhý príkaz však vráti 1 (true), pretože operátor LIKE nerozlišuje veľké a malé písmená, takže „j“ sa rovná „J“.
Ostatní operátori:
SQLite A
Logický operátor, ktorý kombinuje jeden alebo viac výrazov. Vráti hodnotu true, iba ak všetky výrazy poskytnú hodnotu „true“. Hodnota false sa však vráti, iba ak všetky výrazy poskytnú hodnotu „false“.
Príklad:
Nasledujúci dopyt vyhľadá študentov, ktorí majú StudentId> 5 a StudentName začína písmenom N, vrátení študenti musia spĺňať dve podmienky:
VYBERTE *OD študentovKDE (StudentId> 5) AND (StudentName LIKE 'N%');
Ako výstup na vyššie uvedenej snímke obrazovky získate iba „Nancy“. Nancy je jediná študentka, ktorá spĺňa obe podmienky.
SQLite ALEBO
Logický operátor, ktorý kombinuje jeden alebo viac výrazov, takže ak jeden z kombinovaných operátorov poskytne true, potom vráti true. Ak však všetky výrazy poskytnú hodnotu false, vráti hodnotu false.
Príklad:
Nasledujúci dopyt vyhľadá študentov, ktorí majú StudentId> 5 alebo StudentName začínajú na písmeno N, vrátení študenti musia spĺňať aspoň jednu z podmienok:
VYBERTE *OD študentovKDE (StudentId> 5) ALEBO (StudentName AKO 'N%');
Získate tak:
Ako výstup na vyššie uvedenej snímke obrazovky získate meno študenta, ktorý má vo svojom mene písmeno „n“ a ID študenta s hodnotou> 5.
Ako vidíte, výsledok sa líši od dotazu pomocou operátora AND.
SQLite MEDZI
BETWEEN sa používa na výber tých hodnôt, ktoré sú v rozmedzí dvoch hodnôt. Napríklad „ X MEDZI Y A Z “ vráti hodnotu true (1), ak je hodnota X medzi dvoma hodnotami Y a Z. V opačnom prípade vráti hodnotu false (0). „ X MEDZI Y A Z “ je ekvivalentné „ X> = Y A X <= Z “, X musí byť väčšie alebo rovné Y a X je menšie alebo rovné Z.
Príklad:
V nasledujúcom príklade dotazu napíšeme dotaz, aby sme dostali študentov s hodnotou Id medzi 5 a 8:
VYBERTE *OD študentovKDE StudentId MEDZI 5 A 8;
Toto poskytne iba študentom s ID 5, 6, 7 a 8:
SQLite IN
Zoberie jedného operandu a zoznam operandov. Vráti hodnotu true, ak sa hodnota prvého operandu rovná jednej z hodnôt operandov zo zoznamu. Operátor IN vráti true (1), ak zoznam operandov obsahuje v rámci svojich hodnôt prvú hodnotu operandu. V opačnom prípade vráti hodnotu false (0).
Takto: „ col IN (x, y, z) “. Je to ekvivalent „ (col = x) alebo (col = y) alebo (col = z) “.
Príklad:
Nasledujúci dopyt vyberie iba študentov s ID 2, 4, 6, 8:
VYBERTE *OD študentovKDE StudentId IN (2, 4, 6, 8);
Páči sa ti to:
Predchádzajúci dotaz poskytne presný výsledok ako nasledujúci dotaz, pretože sú rovnocenné:
VYBERTE *OD študentovKDE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);
Oba dotazy poskytujú presný výstup. Rozdiel medzi týmito dvoma dotazmi je však v tom, že pri prvom dotaze sme použili operátor „IN“. V druhom dotaze sme použili viac operátorov „OR“.
Operátor IN je ekvivalentný použitiu viacerých operátorov OR. „ WHERE StudentId IN (2, 4, 6, 8) “ je ekvivalentné s „ WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); “
Páči sa ti to:
SQLite NIE JE
Operand „NOT IN“ je opakom operátora IN. Ale s rovnakou syntaxou; trvá to jeden operand a zoznam operandov. Vráti hodnotu true, ak sa hodnota prvého operandu nerovná jednej z hodnôt operandov zo zoznamu. tj vráti hodnotu true (0), ak zoznam operandov neobsahuje prvý operand. Takto: „ col NOT IN (x, y, z) “. Toto je ekvivalentné výrazu „ (col <> x) AND (col <> y) AND (col <> z) “.
Príklad:
Nasledujúci dopyt vyberie študentov s ID, ktoré sa nerovná jednému z týchto čísel 2, 4, 6, 8:
VYBERTE *OD študentovKDE Študent NIE JE V (2, 4, 6, 8);
Páči sa ti to
V predchádzajúcom dotaze uvádzame presný výsledok ako nasledujúci dotaz, pretože sú rovnocenné:
VYBERTE *OD študentovKDE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Páči sa ti to:
Na snímke vyššie
Na získanie zoznamu študentov sme použili niekoľko nerovných operátorov „<>“, ktoré sa nerovnajú ani s jedným z nasledujúcich ID 2, 4, 6 alebo 8. Tento dotaz vráti všetkých ostatných študentov iných ako tento zoznam ID.
SQLite EXISTUJE
Operátory EXISTU neberú žiadne operandy; trvá za ním iba klauzula SELECT. Operátor EXISTS vráti true (1), ak existujú nejaké riadky vrátené z klauzuly SELECT, a vráti false (0), ak z klauzuly SELECT nebudú vrátené vôbec žiadne riadky.
Príklad:
V nasledujúcom príklade vyberieme názov oddelenia, ak ID oddelenia existuje v tabuľke študentov:
VYBERTE názov oddeleniaZ ODDELENÍ AS dKDE EXISTUJE (VYBERTE DepartmentId OD študentov AS KDE d.DepartmentId = s.DepartmentId);
Získate tak:
Vrátené budú iba tri oddelenia „ IT, fyzika a umenie “. A názov oddelenia „ Matematika “ sa nevráti, pretože na tomto oddelení nie je žiadny študent, takže ID oddelenia v tabuľke študentov neexistuje. Preto operátor EXISTUJE oddelenie „ Matematika “ ignoroval .
SQLite NIE
Obráti výsledok predchádzajúceho operátora, ktorý nasleduje po ňom. Napríklad:
- NIE MEDZI - Vráti true, ak MEDZI vráti nepravdivé a naopak.
- NOT LIKE - Vráti true, ak LIKE vráti false a naopak.
- NIE GLOB - Vráti true, ak GLOB vráti false a naopak.
- NEEXISTUJE - vráti hodnotu true, ak funkcia EXISTS vráti hodnotu false a naopak.
Príklad:
V nasledujúcom príklade použijeme operátor NOT s operátorom EXISTS na získanie názvov oddelení, ktoré neexistujú v tabuľke Students, čo je opačný výsledok operátora EXISTS. Vyhľadávanie sa teda bude robiť pomocou DepartmentId, ktoré v tabuľke oddelení neexistujú.
VYBERTE názov oddeleniaZ ODDELENÍ AS dKDE NEEXISTUJE (VYBERTE DepartmentIdZO študentov AS sKDE d.departmentId = s.DepartmentId);
Výstup :
Vráti sa iba oddelenie „ Matematika “. Pretože oddelenie „ Matematika “ je jediné oddelenie, v tabuľke študentov neexistuje.
Obmedzenie a objednávanie
Objednávka SQLite
Objednávka SQLite je zoradenie výsledku podľa jedného alebo viacerých výrazov. Ak chcete objednať výsledkovú sadu, musíte použiť klauzulu ORDER BY nasledovne:
- Najprv musíte určiť klauzulu ORDER BY.
- Klauzula ORDER BY musí byť zadaná na konci dotazu; po nej je možné určiť iba klauzulu LIMIT.
- Ak chcete zoradiť údaje, zadajte výraz, ktorým môže byť názov stĺpca alebo výraz.
- Za výrazom môžete určiť voliteľný smer triedenia. Buď DESC, aby sa dáta zostupne zoradili, alebo ASC, aby sa dáta zoradili vzostupne. Ak by ste nezadali žiadny z nich, údaje by sa zoradili vzostupne.
- Medzi výrazmi „,“ môžete určiť viac výrazov.
Príklad
V nasledujúcom príklade vyberieme všetkých študentov zoradených podľa mien, ale v zostupnom poradí, potom podľa názvu katedry vo vzostupnom poradí:
VYBERTE s.StudentName, d.DepartmentNameZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentIdOBJEDNÁVKA BY d.DepartmentName ASC, s.StudentName DESC;
Získate tak:
- SQLite najskôr zoradí všetkých študentov podľa názvu ich oddelenia vo vzostupnom poradí
- Potom sa pri každom názve oddelenia zobrazia všetci študenti pod týmto názvom v zostupnom poradí podľa ich mien
Limit SQLite:
Počet riadkov vrátených vašim dotazom SQL môžete obmedziť pomocou klauzuly LIMIT. Napríklad LIMIT 10 vám dá iba 10 riadkov a bude ignorovať všetky ostatné riadky.
V klauzule LIMIT môžete pomocou doložky OFFSET zvoliť konkrétny počet riadkov začínajúcich od konkrétnej pozície. Napríklad výraz „ LIMIT 4 OFFSET 4 “ bude ignorovať prvé 4 riadky a vráti 4 riadky začínajúce od piateho riadku, takže získate riadky 5,6,7 a 8.
Klauzula OFFSET je voliteľná, môžete ju napísať ako „ LIMIT 4, 4 “ a poskytne vám presné výsledky.
Príklad :
V nasledujúcom príklade vrátime iba 3 študentov začínajúcich od ID študenta 5 pomocou dotazu:
VYBERTE * OD ŠTUDENTOV LIMIT 4,3;
Získate tak iba troch študentov začínajúcich od riadku 5. Poskytne vám teda riadky so StudentId 5, 6 a 7:
Odstraňujú sa duplikáty
Ak váš dotaz SQL vracia duplicitné hodnoty, môžete na odstránenie týchto duplikátov použiť kľúčové slovo „ DISTINCT “ a návrat na odlišné hodnoty. Po práci s kľúčom DISTINCT môžete určiť viac ako jeden stĺpec.
Príklad:
Nasledujúci dopyt vráti duplicitné „hodnoty názvu oddelenia“: Tu máme duplicitné hodnoty s názvami IT, fyzika a umenie.
VYBERTE d.DepartmentNameZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentId;
Získate tak duplicitné hodnoty názvu oddelenia:
Všimnite si, ako existujú duplicitné hodnoty pre názov oddelenia. Teraz použijeme kľúčové slovo DISTINCT s rovnakým dotazom na odstránenie týchto duplikátov a získanie iba jedinečných hodnôt. Páči sa ti to:
VYBERTE DISTINCT d.DepartmentNameZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentId;
Získate tak iba tri jedinečné hodnoty pre stĺpec s názvom oddelenia:
Agregát
Agregáty SQLite sú vstavané funkcie definované v SQLite, ktoré zoskupia viac hodnôt viacerých riadkov do jednej hodnoty.
Tu sú agregácie podporované programom SQLite:
SQLite AVG ()
Vrátil priemer pre všetky hodnoty x.
Príklad:
V nasledujúcom príklade dostaneme priemernú známku, ktorú môžu študenti získať zo všetkých skúšok:
VYBERTE AVG (značka) ZO značiek;
Získate tak hodnotu „18,375“:
Tieto výsledky pochádzajú zo súčtu všetkých hodnôt značiek vydelených ich počtom.
COUNT () - COUNT (X) alebo COUNT (*)
Vráti celkový počet opakovaní hodnoty x. A tu je niekoľko možností, ktoré môžete použiť s COUNT:
- COUNT (x): Počíta iba hodnoty x, kde x je názov stĺpca. Bude ignorovať NULL hodnoty.
- POČET (*): Spočítajte všetky riadky zo všetkých stĺpcov.
- POČET (DISTINCT x): Môžete určiť kľúčové slovo DISTINCT pred znakom x, ktoré získa počet odlišných hodnôt x.
Príklad
V nasledujúcom príklade získame celkový počet oddelení s COUNT (DepartmentId), COUNT (*) a COUNT (DISTINCT DepartmentId) a ich rozdielnosť:
VYBERTE POČET (DepartmentId), POČET (DISTINCT DepartmentId), POČET (*) OD študentov;
Získate tak:
Ako nasledujúce:
- COUNT (DepartmentId) vám poskytne počet všetkých ID oddelenia a bude ignorovať nulové hodnoty.
- COUNT (DISTINCT DepartmentId) vám dáva odlišné hodnoty DepartmentId, ktoré sú iba 3. Čo sú tri rôzne hodnoty názvu oddelenia. Všimnite si, že v mene študenta je 8 hodnôt názvu oddelenia. Ale iba rôzne tri hodnoty, ktoré sú matematika, IT a fyzika.
- COUNT (*) počíta počet riadkov v tabuľke študentov, čo je 10 riadkov pre 10 študentov.
GROUP_CONCAT () - GROUP_CONCAT (X) alebo GROUP_CONCAT (X, Y)
Funkcia agregácie GROUP_CONCAT zlučuje násobné hodnoty do jednej hodnoty s čiarkou na ich oddelenie. Má nasledujúce možnosti:
- GROUP_CONCAT (X): Zreťazí sa tým všetka hodnota x do jedného reťazca, pričom oddeľovač medzi hodnotami je čiarka „,“. Hodnoty NULL budú ignorované.
- GROUP_CONCAT (X, Y): Týmto sa spoja hodnoty x do jedného reťazca, pričom hodnota y sa použije ako oddeľovač medzi každou hodnotou namiesto predvoleného oddeľovača ','. Hodnoty NULL budú tiež ignorované.
- GROUP_CONCAT (DISTINCT X): Toto spojí všetky odlišné hodnoty x do jedného reťazca, pričom medzi hodnotami sa ako oddeľovač používa čiarka „,“. Hodnoty NULL budú ignorované.
GROUP_CONCAT (DepartmentName) Príklad
Nasledujúci dopyt spojí všetky hodnoty názvu oddelenia od študentov a tabuľky oddelení do jedného reťazca oddeleného čiarkou. Namiesto vrátenia zoznamu hodnôt teda v každom riadku jedna hodnota. Vráti iba jednu hodnotu v jednom riadku, pričom všetky hodnoty budú oddelené čiarkou:
VYBERTE SKUPINU_CONCAT (d.DepartmentName)ZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentId;
Získate tak:
Získate zoznam 8 hodnôt mien oddelení spojených do jedného reťazca oddeleného čiarkou.
GROUP_CONCAT (DISTINCT DepartmentName) Príklad
Nasledujúci dopyt spojí odlišné hodnoty názvu oddelenia z tabuľky študentov a oddelení do jedného reťazca oddeleného čiarkou:
VYBERTE SKUPINU_CONCAT (DISTINCT d. Názov oddelenia)ZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentId;
Získate tak:
Všimnite si, ako je výsledok iný ako predchádzajúci; vrátené iba tri hodnoty, ktoré sú názvami odlišných oddelení, a duplicitné hodnoty boli odstránené.
GROUP_CONCAT (DepartmentName, '&') Príklad
Nasledujúci dotaz spojí všetky hodnoty stĺpca názvu oddelenia z tabuľky študentov a oddelení do jedného reťazca, ale so znakom „&“ namiesto čiarky ako oddeľovačom:
VYBERTE SKUPINU_CONCAT (d.DepartmentName, '&')ZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentId;
Získate tak:
Všimnite si, ako sa na oddelenie medzi hodnotami používa znak „&“ namiesto predvoleného znaku „,“.
SQLite MAX () a MIN ()
MAX (X) vám vráti najvyššiu hodnotu z hodnôt X. MAX vráti hodnotu NULL, ak sú všetky hodnoty x nulové. Zatiaľ čo MIN (X) vám vráti najmenšiu hodnotu z hodnôt X. MIN vráti hodnotu NULL, ak sú všetky hodnoty X nulové.
Príklad
V nasledujúcom dotaze použijeme funkcie MIN a MAX na získanie najvyššej a najnižšej známky z tabuľky „ Značky “:
VYBERTE MAX (značka), MIN (značka) FROM značiek;
Získate tak:
SUM SQLite (x), celkom (x)
Obaja vrátia súčet všetkých hodnôt x. Líšia sa ale v nasledujúcich:
- SUM vráti nulovú hodnotu, ak sú všetky hodnoty nulové, ale hodnota súčtu vráti 0.
- TOTAL vždy vráti hodnoty s pohyblivou rádovou čiarkou. SUM vráti celočíselnú hodnotu, ak sú všetky hodnoty x celé číslo. Ak však hodnoty nie sú celé číslo, vráti hodnotu s pohyblivou rádovou čiarkou.
Príklad
V nasledujúcom dotaze použijeme SUM a súčet, aby sme získali súčet všetkých značiek v tabuľkách „ Značky “:
VYBERTE SUM (známka), TOTAL (známka) ZO známok;
Získate tak:
Ako vidíte, TOTAL vždy vráti plávajúcu desatinnú čiarku. Ale SUM vráti celočíselnú hodnotu, pretože hodnoty v stĺpci „Značka“ môžu byť celé čísla.
Rozdiel medzi SUM a TOTAL príkladom:
V nasledujúcom dotaze ukážeme rozdiel medzi SUM a TOTAL, keď dostanú SUM hodnôt NULL:
VYBERTE SUM (Označiť), CELKOM (Označiť) ZO Značiek KDE TestId = 4;
Získate tak:
Upozorňujeme, že pre TestId = 4 nie sú žiadne značky, takže pre tento test existujú nulové hodnoty. SUM vráti prázdnu hodnotu null, zatiaľ čo TOTAL vráti 0.
Zoskupiť podľa
Klauzula GROUP BY sa používa na určenie jedného alebo viacerých stĺpcov, ktoré sa použijú na zoskupenie riadkov do skupín. Riadky s rovnakými hodnotami sa zhromaždia (usporiadajú) do skupín.
Pre akýkoľvek iný stĺpec, ktorý nie je zahrnutý v skupine podľa stĺpcov, môžete preň použiť agregačnú funkciu.
Príklad:
Nasledujúci dotaz poskytne celkový počet študentov prítomných na jednotlivých katedrách.
VYBERTE d.DepartmentName, COUNT (s.StudentId) AS StudentsCountZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentIdSKUPINA PODĽA d. Názov oddelenia;
Získate tak:
Klauzula GROUPBY DepartmentName zoskupí všetkých študentov do skupín jeden pre každý názov oddelenia. Pre každú skupinu „katedry“ to bude spočítať študentov.
Klauzula HAVING
Ak chcete filtrovať skupiny vrátené klauzulou GROUP BY, môžete určiť klauzulu "HAVING" s výrazom po skupine GROUP BY. Výraz sa použije na filtrovanie týchto skupín.
Príklad
V nasledujúcom dotaze vyberieme tie oddelenia, ktoré majú iba dvoch študentov:
VYBERTE d.DepartmentName, COUNT (s.StudentId) AS StudentsCountZO študentov AS sVNÚTORNÉ PRIPOJENIE Oddelenia AKO ZAPNUTÉ s.DepartmentId = d.DepartmentIdSKUPINA PODĽA d. Názov oddeleniaHAVING COUNT (s.StudentId) = 2;
Získate tak:
Klauzula HAVING COUNT (S.StudentId) = 2 vyfiltruje vrátené skupiny a vráti iba tie skupiny, ktoré obsahujú presne dvoch študentov. V našom prípade má odbor výtvarná výchova 2 študentov, takže sa to zobrazuje vo výstupe.
Dopyt a poddotaz na SQLite
Vo vnútri ľubovoľného dotazu môžete použiť iný dotaz buď vo výrazoch SELECT, INSERT, DELETE, UPDATE alebo vo vnútri iného poddotazu.
Tento vnorený dopyt sa nazýva poddotaz. Teraz uvidíme niekoľko príkladov použitia poddotazov v klauzule SELECT. Avšak v tutoriáli Úpravy údajov uvidíme, ako môžeme použiť poddotazy s príkazmi INSERT, DELETE a UPDATE.
Použitie poddotazu v príklade klauzuly FROM
V nasledujúcom dotaze vložíme poddotaz do klauzuly FROM:
VYBERTEs.StudentName, t.MarkZO študentov AS sVNÚTORNÉ PRIPOJENIE(VYBERTE StudentId, MarkZ TESTOV AKO tVNÚTORNÉ PRIPOJENIE Značky AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
Dotaz:
VYBERTE StudentId, MarkZ TESTOV AKO tVNÚTORNÉ PRIPOJENIE Značky AS m ON t.TestId = m.TestId
Vyššie uvedený dopyt sa tu nazýva poddotaz, pretože je vnorený do klauzuly FROM. Všimnite si, že sme mu dali alias názov „t“, aby sme sa mohli v dotaze odvolať na stĺpce z neho vrátené.
Tento dopyt vám poskytne:
Takže v našom prípade
- s.StudentName je vybraný z hlavného dotazu, ktorý dáva meno študentov a
- t. Značka je vybratá z poddotazu; ktorý dáva známky získané každým z týchto študentov
Použitie poddotazu v príklade klauzuly WHERE
V nasledujúcom dotaze zahrnieme poddotaz do klauzuly WHERE:
VYBERTE názov oddeleniaZ ODDELENÍ AS dKDE NEEXISTUJE (VYBERTE DepartmentIdZO študentov AS sKDE d.departmentId = s.DepartmentId);
Dotaz:
SELECT DepartmentIdZO študentov AS sKDE d.departmentId = s.DepartmentId
Vyššie uvedený dopyt sa tu nazýva poddotaz, pretože je vnorený do klauzuly WHERE. Poddotaz vráti hodnoty DepartmentId, ktoré použije operátor NEEXISTUJE.
Tento dopyt vám poskytne:
Vo vyššie uvedenom dotaze sme vybrali odbor, ktorý nemá zapísaného žiadneho študenta. Čo je to tu „matematické“ oddelenie.
Operácie množiny - UNION, pretínajú sa
SQLite podporuje nasledujúce operácie SET:
ÚNIA A ÚNIA VŠETCI
Kombinuje jednu alebo viac výsledkových sád (skupina riadkov) vrátených z viacerých príkazov SELECT do jednej výsledkovej sady.
UNION vráti odlišné hodnoty. Program UNION ALL však nebude a bude obsahovať duplikáty.
Upozorňujeme, že názov stĺpca bude názov stĺpca zadaný v prvom príkaze SELECT.
Príklad ÚNIE
V nasledujúcom príklade dostaneme zoznam DepartmentId z tabuľky študentov a zoznam DepartmentId z tabuľky katedier v rovnakom stĺpci:
VYBERTE IDENTIFIKÁTOR AKO ODDELENÝUŽIADANÝ OD študentovÚNIAVYBERTE ID oddelenia Z oddelení;
Získate tak:
Dotaz vráti iba 5 riadkov, ktoré sú zreteľnými hodnotami ID oddelenia. Všimnite si prvú hodnotu, ktorá je nulovou hodnotou.
SQLite UNION ALL Príklad
V nasledujúcom príklade dostaneme zoznam DepartmentId z tabuľky študentov a zoznam DepartmentId z tabuľky katedier v rovnakom stĺpci:
VYBERTE IDENTIFIKÁTOR AKO ODDELENÝUŽIADANÝ OD študentovVŠETKO ÚNIAVYBERTE ID oddelenia Z oddelení;
Získate tak:
Dotaz vráti 14 riadkov, 10 riadkov z tabuľky študentov a 4 riadky z tabuľky katedier. Upozorňujeme, že v vrátených hodnotách sú duplikáty. Upozorňujeme tiež, že názov stĺpca bol ten, ktorý je uvedený v prvom príkaze SELECT.
Teraz sa pozrime, ako všetky programy UNION prinesú odlišné výsledky, ak nahradíme program UNION ALL programom UNION:
SQLite INTERSECT
Vráti hodnoty, ktoré existujú v obidvoch kombinovaných výsledkoch. Hodnoty, ktoré existujú v jednej z kombinovaných výsledkov, budú ignorované.
Príklad
V nasledujúcom dotaze vyberieme hodnoty DepartmentId, ktoré existujú v tabuľkách Studenti aj Departments v stĺpci DepartmentId:
VYBERTE IDENTIFIKÁCIU OD študentovPretínajú saVYBERTE ID oddelenia Z oddelení;
Získate tak:
Dotaz vráti iba tri hodnoty 1, 2 a 3. Čo sú hodnoty, ktoré existujú v oboch tabuľkách.
Hodnoty null a 4 však neboli zahrnuté, pretože nulová hodnota existuje iba v tabuľke študentov, a nie v tabuľke katedier. A hodnota 4 existuje v tabuľke oddelení, a nie v tabuľke študentov.
Preto boli hodnoty NULL aj 4 ignorované a nezahrnuté do vrátených hodnôt.
S VÝNIMKOU
Predpokladajme, že ak máte dva zoznamy riadkov, list1 a list2, a chcete, aby boli riadky iba zo zoznamu1, ktorý v zozname2 neexistuje, môžete použiť klauzulu „EXCEPT“. Klauzula EXCEPT porovnáva dva zoznamy a vracia tie riadky, ktoré existujú v zozname1 a neexistujú v zozname2.
Príklad
V nasledujúcom dotaze vyberieme hodnoty DepartmentId, ktoré existujú v tabuľke katedier a neexistujú v tabuľke študentov:
VYBERTE DepartmentId Z oddeleníS VÝNIMKOUVYBERTE IDENTIFIKÁCIU OD študentov;
Získate tak:
Dotaz vráti iba hodnotu 4. Čo je jediná hodnota, ktorá existuje v tabuľke oddelení a neexistuje v tabuľke študentov.
NULL manipulácia
Hodnota „ NULL “ je v SQLite špeciálna hodnota. Používa sa na vyjadrenie neznámej alebo chýbajúcej hodnoty. Upozorňujeme, že nulová hodnota je úplne iná ako hodnota „ 0 “ alebo prázdna „“ hodnota. Pretože 0 a prázdna hodnota je známa hodnota, nulová hodnota je neznáma.
Hodnoty NULL vyžadujú špeciálne zaobchádzanie v prostredí SQLite, teraz uvidíme, ako zaobchádzať s hodnotami NULL.
Vyhľadajte hodnoty NULL
Na hľadanie nulových hodnôt nemôžete použiť operátor normálnej rovnosti (=). Napríklad nasledujúci dotaz vyhľadá študentov, ktorí majú nulovú hodnotu DepartmentId:
VYBERTE * OD študentov, KDE DepartmentId = NULL;
Tento dopyt neprinesie žiadny výsledok:
Pretože sa hodnota NULL nerovná žiadnej inej hodnote vrátane nulovej hodnoty samotnej, preto nevrátila žiadny výsledok.
- Ak však chcete, aby dotaz fungoval, musíte na vyhľadanie hodnôt null použiť operátor „NULL“ takto:
VYBERTE * OD študentov, KDE JE DepartmentUd NULL;
Získate tak:
Dotaz vráti tých študentov, ktorí majú nulovú hodnotu DepartmentId.
- Ak chcete získať tie hodnoty, ktoré nie sú nulové, musíte použiť operátor „ NIE JE NULOVÝ “, ako je tento:
VYBERTE * OD študentov, KDE Katedra NIE JE NULL;
Získate tak:
Dotaz vráti tých študentov, ktorí nemajú hodnotu NULL DepartmentId.
Podmienené výsledky
Ak máte zoznam hodnôt a chcete na základe určitých podmienok vybrať niektorú z nich. Na to by mala byť splnená podmienka pre konkrétnu hodnotu.
Výraz CASE vyhodnotí tento zoznam podmienok pre všetky hodnoty. Ak je podmienka pravdivá, vráti túto hodnotu.
Napríklad ak máte stĺpec „Známka“ a chcete zvoliť textovú hodnotu na základe hodnoty známky takto:
- „Vynikajúce“, ak je známka vyššia ako 85.
- „Veľmi dobré“, ak je hodnotenie medzi 70 a 85.
- „Dobré“, ak je stupeň medzi 60 a 70.
Potom na to môžete použiť výraz CASE.
To možno použiť na definovanie určitej logiky v klauzule SELECT, aby ste mohli vybrať určité výsledky v závislosti od určitých podmienok, napríklad príkaz if.
Operátor CASE možno definovať s rôznymi syntaxami takto:
- Môžete použiť rôzne podmienky:
PRÍPADKEDY podmienka1 POTOM výsledok1KEDY podmienka2 POTOM výsledok2KEDY podmienka3 POTOM výsledok3 ... INÝ výsledoknKONIEC
- Alebo môžete použiť iba jeden výraz a na výber dať rôzne možné hodnoty:
Výraz CASEKEDY hodnota1 POTOM výsledok1KEDY hodnota2 POTOM výsledok2KEDY hodnota3 POTOM výsledok3 ... INÉ restulnKONIEC
Doložka ELSE je voliteľná.
Príklad
V nasledujúcom príklade použijeme výraz CASE s hodnotou NULL v stĺpci Id oddelenia v tabuľke Students na zobrazenie textu „No Department“ nasledovne:
VYBERTEMeno študenta,PRÍPADKEDY DepartmentId JE NULL POTOM „No Department“ELSE DepartmentIdKONIEC AS DepartmentIdOD študentov;
- Operátor CASE skontroluje hodnotu DepartmentId, či je nulová alebo nie.
- Ak je to hodnota NULL, potom namiesto hodnoty DepartmentId vyberie doslovnú hodnotu „Žiadne oddelenie“.
- Ak nie je nulová hodnota, vyberie hodnotu stĺpca DepartmentId.
Získate výstup uvedený nižšie:
Spoločný tabuľkový výraz
Bežné tabuľkové výrazy (CTE) sú poddotazy, ktoré sú definované vo vnútri príkazu SQL s daným menom.
Má výhodu oproti poddotazom, pretože je definovaná mimo príkazov SQL a uľahčí čítanie, údržbu a porozumenie dotazov.
Spoločný tabuľkový výraz je možné definovať vložením klauzuly WITH pred príkazy SELECT nasledovne:
S CTEnameAS(Príkaz SELECT)VÝBER, AKTUALIZÁCIA, VLOŽENIE alebo aktualizácia vyhlásenia tu Z CTE
„ Názov CTE “ je ľubovoľný názov, ktorý môžete pre CTE pomenovať, môžete ho neskôr použiť. Upozorňujeme, že na CTE môžete definovať príkaz SELECT, UPDATE, INSERT alebo DELETE
Teraz sa pozrime na príklad, ako používať CTE v klauzule SELECT.
Príklad
V nasledujúcom príklade definujeme CTE z príkazu SELECT a potom ho použijeme neskôr v ďalšom dotaze:
S AllDepartmentsAS(VYBERTE DepartmentId, DepartmentNameZ ODDELENÍ)VYBERTEs.StudentId,s.StudentName,a.DepartmentNameZO študentov AS sINNER JOIN JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;
V tomto dotaze sme definovali CTE a dali mu názov „ AllDepartments “. Tento CTE bol definovaný z dotazu SELECT:
VYBERTE DepartmentId, DepartmentNameZ ODDELENÍ
Potom, čo sme definovali CTE, použili sme ho v dotaze SELECT, ktorý nasleduje po ňom.
Upozorňujeme, že výrazy spoločnej tabuľky neovplyvňujú výstup dotazu. Je to spôsob, ako definovať logické zobrazenie alebo poddotaz, aby ste ich mohli znova použiť v rovnakom dotaze. Bežné tabuľkové výrazy sú ako premenná, ktorú deklarujete, a znova ju použijete ako poddotaz. Na výstup dotazu má vplyv iba príkaz SELECT.
Tento dopyt vám poskytne:
Pokročilé dotazy
Pokročilé dotazy sú tie dotazy, ktoré obsahujú zložité spojenia, poddotazy a niektoré agregáty. V nasledujúcej časti uvidíme príklad pokročilého dotazu:
Kde dostaneme
- Názvy katedier so všetkými študentmi za jednotlivé katedry
- Meno študentov oddelené čiarkou a
- Zobrazenie toho, že má katedra najmenej troch študentov
VYBERTEd.DepartmentName,COUNT (s.StudentId) počet študentov,GROUP_CONCAT (StudentName) AS študentiZ ODDELENÍ AS dVNÚTORNÉ PRIPOJENIE Študenti AS S ON s.DepartmentId = d.DepartmentIdSKUPINA PODĽA d.DepartmentNameMÁ POČET (s.StudentId)> = 3;
Pridali sme klauzulu JOIN, aby sme získali DepartmentName z tabuľky Departments. Potom sme pridali klauzulu GROUP BY s dvoma agregačnými funkciami:
- „COUNT“ na spočítanie študentov pre každú skupinu oddelení.
- GROUP_CONCAT na zreťazenie študentov pre každú skupinu s čiarkami oddelenými v jednom reťazci.
- Po GROUP BY sme pomocou klauzuly HAVING filtrovali oddelenia a vybrali iba tie oddelenia, ktoré majú minimálne 3 študentov.
Výsledok bude nasledovný:
Zhrnutie:
Toto bol úvod do písania dotazov SQLite a základov dotazovania sa v databáze a toho, ako môžete filtrovať vrátené údaje. Teraz môžete písať svoje vlastné dotazy SQLite.