SQLite Query: Select, Where, LIMIT, OFFSET, Count, Group By

Chcete-li psát dotazy SQL v databázi SQLite, musíte vědět, jak fungují klauzule SELECT, FROM, WHERE, GROUP BY, ORDER BY a LIMIT a jak je používat.

Během tohoto kurzu se naučíte, jak tyto klauzule používat a jak psát klauzule SQLite.

V tomto výukovém programu se naučíte

Čtení dat pomocí Select
Jména a aliasy
KDE
Omezení a objednávání
Odebírání duplikátů
Agregát
Skupina vytvořená
Dotaz a poddotaz
Nastavit operace - UNION, protínat
NULL manipulace
Podmíněné výsledky
Společný tabulkový výraz
Pokročilé dotazy

Čtení dat pomocí Select

Klauzule SELECT je hlavní příkaz, který používáte k dotazování na databázi SQLite. V klauzuli SELECT uvedete, co chcete vybrat. Ale před klauzulí select se podívejme, odkud můžeme vybrat data pomocí klauzule FROM.

Klauzule FROM se používá k určení, kde chcete vybrat data. V klauzuli from můžete určit jednu nebo více tabulek nebo poddotazů, ze kterých chcete data vybrat, jak uvidíme později v tutoriálech.

Všimněte si, že u všech následujících příkladů musíte spustit soubor sqlite3.exe a otevřít připojení k ukázkové databázi průběžně:

Krok 1) V tomto kroku

Otevřete Tento počítač a přejděte do následujícího adresáře " C: \ sqlite " a
Poté otevřete soubor „ sqlite3.exe “:

Krok 2) Otevřete databázi „ TutorialsSampleDB.db “ pomocí následujícího příkazu:

Nyní jste připraveni spustit jakýkoli typ dotazu v databázi.

V klauzuli SELECT můžete vybrat nejen název sloupce, ale máte spoustu dalších možností, jak určit, co se má vybrat. Následovně:

VYBRAT *

Tento příkaz vybere všechny sloupce ze všech odkazovaných tabulek (nebo poddotazů) v klauzuli FROM. Například:

VYBRAT *OD studentůVNITŘNÍ PŘIPOJENÍ Departments ON Students.DepartmentId = Departments.DepartmentId;

Tím vyberete všechny sloupce jak z tabulek studentů, tak z tabulek kateder:

VYBRAT název_tabulky. *

Tím vyberete všechny sloupce pouze z tabulky „tablename“. Například:

VYBERTE studenty. *OD studentůVNITŘNÍ PŘIPOJENÍ Departments ON Students.DepartmentId = Departments.DepartmentId;

Tím vyberete všechny sloupce pouze z tabulky studentů:

Doslovná hodnota

Doslovná hodnota je konstantní hodnota, kterou lze zadat v příkazu select. Doslovné hodnoty můžete normálně použít stejným způsobem, jakým používáte názvy sloupců v klauzuli SELECT. Tyto doslovné hodnoty se zobrazí pro každý řádek z řádků vrácených dotazem SQL.

Zde je několik příkladů různých doslovných hodnot, které můžete vybrat:

Numeric Literal - čísla v libovolném formátu jako 1, 2,55,… atd.
Řetězcové literály - libovolný řetězec „USA“, „toto je ukázkový text“,… atd.
NULL - hodnota NULL.
Current_TIME - Poskytne vám aktuální čas.
CURRENT_DATE - získáte aktuální datum.

To může být užitečné v některých situacích, kdy musíte vybrat konstantní hodnotu pro všechny vrácené řádky. Například pokud chcete vybrat všechny studenty z tabulky Studenti s novým sloupcem s názvem země, který obsahuje hodnotu „USA“, můžete to udělat:

SELECT *, 'USA' AS Země OD studentů;

Získáte tak všechny sloupce studentů plus nový sloupec „Země“, jako je tento:

Všimněte si, že tento nový sloupec Země není ve skutečnosti novým sloupcem přidaným do tabulky. Je to virtuální sloupec vytvořený v dotazu pro zobrazení výsledků a nebude vytvořen v tabulce.

Jména a aliasy

Alias je nový název sloupce, který umožňuje vybrat sloupec s novým názvem. Aliasy sloupců se zadávají pomocí klíčového slova „AS“.

Například pokud chcete vybrat sloupec StudentName, který má být vrácen s "Student Name" namísto "StudentName", můžete mu dát alias, jako je tento:

VYBERTE StudentName AS 'Student Name' OD ŽÁKŮ;

Získáte tak jména studentů se jménem „Student Name“ namísto „StudentName“ takto:

Všimněte si, že název sloupce je stále „ StudentName “; sloupec StudentName je stále stejný, nezmění se aliasem.

Alias nezmění název sloupce; pouze změní zobrazovaný název v klauzuli SELECT.

Všimněte si také, že klíčové slovo „AS“ je volitelné, alias název můžete zadat i bez něj, například takto:

VYBERTE StudentName 'Student Name' FROM students;

Získáte přesně stejný výstup jako v předchozím dotazu:

Můžete také zadat aliasy tabulek, nejen sloupce. Se stejným klíčovým slovem „AS“. Můžete to udělat například takto:

SELECT s. * FROM Students AS s;

Získáte tak všechny sloupce v tabulce Studenti:

To může být velmi užitečné, pokud se připojujete k více než jedné tabulce; namísto opakování celého názvu tabulky v dotazu můžete každé tabulce dát krátký alias. Například v následujícím dotazu:

VYBERTE studenty.StudentName, Departments.DepartmentNameOD studentůVNITŘNÍ PŘIPOJENÍ Departments ON Students.DepartmentId = Departments.DepartmentId;

Tento dotaz vybere jméno každého studenta z tabulky „Studenti“ s názvem jeho oddělení z tabulky „Oddělení“:

Stejný dotaz však lze napsat takto:

VYBRAT s.StudentName, d.DepartmentNameFROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ON s.DepartmentId = d.DepartmentId;

Dali jsme studentům tabulku alias „s“ a tabulkám oddělení alias „d“.
Poté jsme místo názvu celé tabulky použili jejich aliasy, abychom na ně odkazovali.
INNER JOIN spojuje dva nebo více stolů dohromady pomocí podmínky. V našem příkladu jsme se připojili k tabulce Studenti s tabulkou Oddělení se sloupcem DepartmentId. K dispozici je také podrobné vysvětlení VNITŘNÍHO PŘIPOJENÍ v tutoriálu „Připojení SQLite“.

Získáte přesný výstup jako předchozí dotaz:

KDE

Samotné psaní dotazů SQL pomocí klauzule SELECT s klauzulí FROM, jak jsme viděli v předchozí části, vám poskytne všechny řádky z tabulek. Pokud však chcete filtrovaná data vrátit, musíte přidat klauzuli „WHERE“.

Klauzule WHERE se používá k filtrování sady výsledků vrácené dotazem SQL. Takto funguje klauzule WHERE:

V klauzuli WHERE můžete určit „výraz“.
Tento výraz bude vyhodnocen pro každý řádek vrácený z tabulek uvedených v klauzuli FROM.
Výraz bude vyhodnocen jako logický výraz s výsledkem buď true, false, nebo null.
Pak budou vráceny pouze řádky, pro které byl výraz vyhodnocen s pravou hodnotou, a ty s falešnými nebo nulovými výsledky budou ignorovány a nebudou zahrnuty do sady výsledků.
Chcete-li filtrovat sadu výsledků pomocí klauzule WHERE, musíte použít výrazy a operátory.

Seznam operátorů v SQLite a jak je používat

V následující části vysvětlíme, jak můžete filtrovat pomocí výrazu a operátorů.

Výraz je jedna nebo více doslovných hodnot nebo sloupců navzájem kombinovaných s operátorem.

Všimněte si, že výrazy můžete použít jak v klauzuli SELECT, tak v klauzuli WHERE.

V následujících příkladech vyzkoušíme výrazy a operátory jak v klauzuli select, tak v klauzuli WHERE. Abychom vám ukázali, jak si vedou.

Existují různé typy výrazů a operátorů, které můžete určit takto:

SQLite operátor zřetězení "||"

Tento operátor se používá ke zřetězení jedné nebo více literálních hodnot nebo sloupců navzájem. Bude produkovat jeden řetězec výsledků ze všech zřetězených literálních hodnot nebo sloupců. Například:

VYBERTE 'ID s názvem:' || StudentId || StudentName AS StudentIdWithNameOD studentů;

Tím se zřetězí do nového aliasu " StudentIdWithName ":

Doslovná hodnota řetězce „ ID s názvem: “
s hodnotou sloupce " StudentId " a
s hodnotou ze sloupce „ StudentName “

Operátor SQLite CAST:

Operátor CAST se používá k převodu hodnoty z datového typu na jiný datový typ.

Například pokud máte číselnou hodnotu uloženou jako řetězcovou hodnotu, jako je tato „ 12,5 “ a chcete ji převést na číselnou hodnotu, můžete k tomu použít operátor CAST jako „ CAST („ 12,5 “AS SKUTEČNÉ) ". Nebo pokud máte desetinnou hodnotu jako 12,5 a potřebujete získat pouze celočíselnou část, můžete ji vrhnout na celé číslo jako je tento „CAST (12,5 AS INTEGER)“.

Příklad

V následujícím příkazu se pokusíme převést různé hodnoty do jiných datových typů:

VYBERTE CAST ('12 .5 'JAKO SKUTEČNÝ) ToReal, CAST (12,5 JAKO INTEGRÁTOR) JAKO ToInteger;

Tím získáte:

Výsledek je následující:

CAST ('12 .5 'AS REAL) - hodnota '12 .5' je hodnota řetězce, bude převedena na SKUTEČNOU hodnotu.
CAST (12,5 AS INTEGER) - hodnota 12,5 je desetinná hodnota, bude převedena na celočíselnou hodnotu. Desetinná část bude zkrácena na 12.

Aritmetické operátory SQLite:

Vezměte dvě nebo více číselných hodnot literálu nebo číselných sloupců a vraťte jednu číselnou hodnotu. Aritmetické operátory podporované v SQLite jsou:

Sčítání „ + “ - udává součet dvou operandů.
Odčítání " - " - odečte dva operandy a způsobí rozdíl.
Násobení „ * “ - součin dvou operandů.
Připomenutí (modulo) " % " - dává zbytek, který je výsledkem dělení jednoho operandu druhým operandem.
Dělení „ / “ - vrací výsledky kvocientu z dělení levého operandu pravým operandem.

Příklad:

V následujícím příkladu vyzkoušíme pět aritmetických operátorů se stejnými doslovnými číselnými hodnotami

klauzule select:

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

Tím získáte:

Všimněte si, jak jsme zde použili příkaz SELECT bez klauzule FROM. A to je v SQLite povoleno, pokud vybereme doslovné hodnoty.

Operátory porovnání SQLite

Porovnejte dva operandy navzájem a vraťte true nebo false takto:

" < " - vrací true, pokud je levý operand menší než pravý operand.
" <= " - vrací true, pokud je levý operand menší nebo roven pravému operandu.
" > " - vrátí true, pokud je levý operand větší než pravý operand.
" > = " - vrací true, pokud je levý operand větší nebo roven pravému operandu.
" = " a " == " - vrátí true, pokud jsou dva operandy stejné. Všimněte si, že oba operátoři jsou stejní a není mezi nimi žádný rozdíl.
" ! = " a " <> " - vrací true, pokud nejsou dva operandy stejné. Všimněte si, že oba operátoři jsou stejní a není mezi nimi žádný rozdíl.

Všimněte si, že SQLite vyjadřuje skutečnou hodnotu s 1 a falešnou hodnotu s 0.

Příklad:

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

To dá něco takového:

Operátory porovnávání vzorů SQLite

„ LIKE “ - slouží k porovnávání vzorů. Pomocí „ Like “ můžete vyhledávat hodnoty, které odpovídají vzoru určenému pomocí zástupného znaku.

Operand vlevo může být hodnota řetězcového literálu nebo sloupec řetězce. Vzor lze určit takto:

Obsahuje vzor. Například StudentName LIKE '% a%' - vyhledá jména studentů, která obsahují písmeno "a" na libovolné pozici ve sloupci StudentName.
Začíná vzorem. Například „ StudentName LIKE 'a%' “ - vyhledejte jména studentů, která začínají písmenem „a“.
Končí vzorem. Například " StudentName LIKE '% a' " - Vyhledejte jména studentů, která končí písmenem "a".
Přiřazení libovolného jednotlivého znaku v řetězci pomocí podtržítka „_“. Například " StudentName LIKE 'J___' " - Vyhledejte jména studentů o délce 4 znaků. Musí začínat písmenem „J“ a za písmenem „J“ může mít další tři další znaky.

Příklady shody vzorů:

Získejte jména studentů, která začínají písmenem „j“:
```
VYBERTE StudentName FROM Students WHERE StudentName LIKE 'j%';
```
Výsledek:

Nechte jména studentů končit písmenem „y“:

VYBERTE StudentName FROM Students WHERE StudentName LIKE '% y';

Výsledek:

Získejte jména studentů, která obsahují písmeno 'n':
```
VYBERTE StudentName FROM Students WHERE StudentName LIKE '% n%';
```
Výsledek:

„GLOB“ - je ekvivalentní operátoru LIKE, ale GLOB rozlišuje velká a malá písmena, na rozdíl od operátora LIKE. Například následující dva příkazy vrátí různé výsledky:

VYBERTE 'Jack' GLOB 'j%';VYBERTE 'Jack' LIKE 'j%';

Tím získáte:

První příkaz vrátí 0 (false), protože operátor GLOB rozlišuje velká a malá písmena, takže 'j' se nerovná 'J'. Druhý příkaz však vrátí 1 (true), protože operátor LIKE nerozlišuje velká a malá písmena, takže 'j' se rovná 'J'.

Ostatní operátoři:

SQLite AND

Logický operátor, který kombinuje jeden nebo více výrazů. Vrátí hodnotu true, pouze pokud všechny výrazy poskytnou hodnotu „true“. Vrátí však hodnotu false, pouze pokud všechny výrazy poskytnou hodnotu „false“.

Příklad:

Následující dotaz vyhledá studenty, kteří mají StudentId> 5 a StudentName začíná písmenem N, vrácení studenti musí splňovat dvě podmínky:

VYBRAT *OD studentůKDE (StudentId> 5) AND (StudentName LIKE 'N%');

Jako výstup na výše uvedeném snímku obrazovky získáte pouze „Nancy“. Nancy je jediná studentka, která splňuje obě podmínky.

SQLite NEBO

Logický operátor, který kombinuje jeden nebo více výrazů, takže pokud jeden z kombinovaných operátorů získá true, vrátí true. Pokud však všechny výrazy poskytnou hodnotu false, vrátí hodnotu false.

Příklad:

Následující dotaz vyhledá studenty, kteří mají StudentId> 5 nebo StudentName začíná písmenem N, vrácení studenti musí splňovat alespoň jednu z podmínek:

VYBRAT *OD studentůKDE (StudentId> 5) NEBO (StudentName LIKE 'N%');

Tím získáte:

Jako výstup na výše uvedeném snímku obrazovky získáte jméno studenta, který má v názvu písmeno „n“ plus ID studenta s hodnotou> 5.

Jak vidíte, výsledek se liší od dotazu operátorem AND.

SQLite MEZI

BETWEEN se používá k výběru těch hodnot, které jsou v rozmezí dvou hodnot. Například „ X MEZI Y A Z “ vrátí hodnotu true (1), pokud je hodnota X mezi dvěma hodnotami Y a Z. Jinak vrátí hodnotu false (0). „ X MEZI Y AND Z “ je ekvivalentní „ X> = Y AND X <= Z “, X musí být větší nebo rovno Y a X je menší než nebo rovno Z.

Příklad:

V následujícím příkladu dotazu napíšeme dotaz, abychom dostali studenty s hodnotou Id mezi 5 a 8:

VYBRAT *OD studentůKDE StudentID MEZI 5 A 8;

Tím získáte pouze studenty s ID 5, 6, 7 a 8:

SQLite IN

Vezme jeden operand a seznam operandů. Vrátí hodnotu true, pokud se hodnota prvního operandu rovná jedné z hodnot operandů ze seznamu. Operátor IN vrací true (1), pokud seznam operandů obsahuje první hodnotu operandu v jeho hodnotách. V opačném případě vrátí hodnotu false (0).

Takto: „ col IN (x, y, z) “. To odpovídá „ (col = x) nebo (col = y) nebo (col = z) “.

Příklad:

Následující dotaz vybere studenty pouze s ID 2, 4, 6, 8:

VYBRAT *OD studentůKDE StudentId IN (2, 4, 6, 8);

Takhle:

Předchozí dotaz poskytne přesný výsledek jako následující dotaz, protože jsou ekvivalentní:

VYBRAT *OD studentůWHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);

Oba dotazy poskytují přesný výstup. Rozdíl mezi těmito dvěma dotazy je však v prvním dotazu, který jsme použili operátor „IN“. Ve druhém dotazu jsme použili více operátorů „OR“.

Operátor IN je ekvivalentní použití více operátorů OR. „ WHERE StudentId IN (2, 4, 6, 8) “ je ekvivalentní „ WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); “

Takhle:

SQLite NENÍ V

Operand „NOT IN“ je opakem operátoru IN. Ale se stejnou syntaxí; trvá jeden operand a seznam operandů. Vrátí hodnotu true, pokud se hodnota prvního operandu nerovná jedné z hodnot operandů ze seznamu. tj. vrátí true (0), pokud seznam operandů neobsahuje první operand. Takto: „ col NOT IN (x, y, z) “. To odpovídá „ (col <> x) AND (col <> y) AND (col <> z) “.

Příklad:

Následující dotaz vybere studenty s ID, které se nerovná jednomu z těchto ID 2, 4, 6, 8:

VYBRAT *OD studentůKDE StudentI NENÍ V (2, 4, 6, 8);

Takhle

Předchozí dotaz dáváme přesný výsledek jako následující dotaz, protože jsou ekvivalentní:

VYBRAT *OD studentůKDE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

Takhle:

Na výše uvedeném snímku obrazovky

K získání seznamu studentů jsme použili několik nerovných operátorů „<>“, které se nerovnají ani jednomu z následujících Id 2, 4, 6, ani 8. Tento dotaz vrátí všechny ostatní studenty kromě tohoto seznamu Id.

SQLite EXISTUJE

Operátoři EXISTU nepřijímají žádné operandy; po něm trvá pouze klauzule SELECT. Operátor EXISTS vrátí true (1), pokud existují nějaké řádky vrácené z klauzule SELECT, a vrátí false (0), pokud z klauzule SELECT nebudou vráceny žádné řádky.

Příklad:

V následujícím příkladu vybereme název oddělení, pokud ID oddělení v tabulce studentů existuje:

VYBERTE název odděleníZ ODDĚLENÍ AS dKDE EXISTUJE (VÝBĚR DepartmentId OD studentů AS s WHERE d.DepartmentId = s.DepartmentId);

Tím získáte:

Budou vráceny pouze tři oddělení „ IT, fyzika a umění “. A název oddělení „ Matematika “ nebude vrácen, protože v tomto oddělení není žádný student, takže ID oddělení v tabulce studentů neexistuje. Proto operátor EXISTUJE oddělení „ Matematika “ ignoruje .

SQLite NE

Obrátí výsledek předchozího operátoru, který následuje po něm. Například:

NOT BETWEEN - Vrátí true, pokud BETWEEN vrátí false a naopak.
NOT LIKE - Vrátí true, pokud LIKE vrátí false a naopak.
NOT GLOB - Vrátí true, pokud GLOB vrátí false a naopak.
NOT EXISTS - Vrátí true, pokud EXISTS vrátí false a naopak.

Příklad:

V následujícím příkladu použijeme operátor NOT s operátorem EXISTS k získání názvů oddělení, která neexistují v tabulce Students, což je opačný výsledek operátoru EXISTS. Hledání se tedy bude provádět pomocí DepartmentId, které v tabulce oddělení neexistují.

VYBERTE název odděleníZ ODDĚLENÍ AS dKDE NEEXISTUJE (VYBRAT DepartmentIdFROM Students AS sKDE d.DepartmentId = s.DepartmentId);

Výstup :

Bude vráceno pouze oddělení „ Matematika “. Protože oddělení „ Matematika “ je jediné oddělení, toto v tabulce studentů neexistuje.

Omezení a objednávání

Objednávka SQLite

Pořadí SQLite je seřadit výsledek podle jednoho nebo více výrazů. Chcete-li objednat sadu výsledků, musíte použít klauzuli ORDER BY následujícím způsobem:

Nejprve musíte určit klauzuli ORDER BY.
Klauzule ORDER BY musí být uvedena na konci dotazu; po něm lze zadat pouze klauzuli LIMIT.
Určete výraz, se kterým chcete data objednat, tímto výrazem může být název sloupce nebo výraz.
Po výrazu můžete určit volitelný směr řazení. Buď DESC, chcete-li seřadit data sestupně, nebo ASC, chcete-li seřadit data vzestupně. Pokud byste žádnou z nich nezadali, data by se seřadila vzestupně.
Můžete zadat více výrazů pomocí "," mezi sebou.

Příklad

V následujícím příkladu vybereme všechny studenty seřazené podle jejich jmen, ale v sestupném pořadí, poté podle názvu oddělení ve vzestupném pořadí:

VYBRAT s.StudentName, d.DepartmentNameFROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ZAP s.DepartmentId = d.DepartmentIdOBJEDNAT PODLE d.DepartmentName ASC, s.StudentName DESC;

Tím získáte:

SQLite nejprve seřadí všechny studenty vzestupně podle názvu jejich oddělení
Potom se u každého názvu oddělení zobrazí všichni studenti pod tímto názvem oddělení v sestupném pořadí podle jejich jmen

Limit SQLite:

Počet řádků vrácených vaším dotazem SQL můžete omezit pomocí klauzule LIMIT. Například LIMIT 10 vám dá pouze 10 řádků a ignoruje všechny ostatní řádky.

V klauzuli LIMIT můžete pomocí klauzule OFFSET vybrat konkrétní počet řádků počínaje od konkrétní pozice. Například „ LIMIT 4 OFFSET 4 “ bude ignorovat první 4 řádky a vrátí 4 řádky začínající od pátých řádků, takže získáte řádky 5,6,7 a 8.

Klauzule OFFSET je volitelná, můžete ji napsat jako „ LIMIT 4, 4 “ a poskytne vám přesné výsledky.

Příklad :

V následujícím příkladu vrátíme pouze 3 studenty počínaje ID studenta 5 pomocí dotazu:

VYBRAT * OD ŽÁKŮ LIMIT 4,3;

Získáte tak pouze tři studenty počínaje řádkem 5. Poskytne vám tedy řádky se StudentId 5, 6 a 7:

Odebírání duplikátů

Pokud váš dotaz SQL vrací duplicitní hodnoty, můžete klíčové slovo „ DISTINCT “ odstranit tyto duplikáty a vrátit odlišné hodnoty. Po zadání klíče DISTINCT můžete zadat více než jeden sloupec.

Příklad:

Následující dotaz vrátí duplicitní „hodnoty názvu oddělení“: Zde máme duplicitní hodnoty s názvy IT, fyzika a umění.

VYBRAT d.DepartmentNameFROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ON s.DepartmentId = d.DepartmentId;

Získáte tak duplicitní hodnoty názvu oddělení:

Všimněte si, jak existují duplicitní hodnoty pro název oddělení. Nyní použijeme klíčové slovo DISTINCT se stejným dotazem k odstranění těchto duplikátů a získání pouze jedinečných hodnot. Takhle:

VYBERTE DISTINCT d. Název odděleníFROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ON s.DepartmentId = d.DepartmentId;

Tím získáte pouze tři jedinečné hodnoty pro sloupec s názvem oddělení:

Agregát

Agregáty SQLite jsou integrované funkce definované v SQLite, které budou seskupovat více hodnot více řádků do jedné hodnoty.

Tady jsou agregáty podporované SQLite:

SQLite AVG ()

Vrátil průměr všech hodnot x.

Příklad:

V následujícím příkladu dostaneme průměrnou známku, kterou studenti získají ze všech zkoušek:

VYBERTE AVG (Mark) FROM Marks;

Získáte tak hodnotu „18,375“:

Tyto výsledky pocházejí ze součtu všech hodnot známek děleno jejich počtem.

COUNT () - COUNT (X) nebo COUNT (*)

Vrátí celkový počet, kolikrát se hodnota x objevila. A zde jsou některé možnosti, které můžete použít u COUNT:

COUNT (x): Počítá pouze hodnoty x, kde x je název sloupce. Bude ignorovat hodnoty NULL.
POČET (*): spočítá všechny řádky ze všech sloupců.
POČET (DISTINCT x): Můžete zadat klíčové slovo DISTINCT před x, které získá počet odlišných hodnot x.

Příklad

V následujícím příkladu získáme celkový počet oddělení s COUNT (DepartmentId), COUNT (*) a COUNT (DISTINCT DepartmentId) a jak se liší:

VYBERTE POČET (DepartmentId), POČET (DISTINCT DepartmentId), POČET (*) OD studentů;

Tím získáte:

Následovně:

COUNT (DepartmentId) vám poskytne počet všech ID oddělení a bude ignorovat hodnoty null.
COUNT (DISTINCT DepartmentId) vám dává odlišné hodnoty DepartmentId, které jsou pouze 3. Které jsou tři různé hodnoty názvu oddělení. Všimněte si, že ve jménu studenta je 8 hodnot názvu oddělení. Ale pouze různé tři hodnoty, kterými jsou matematika, IT a fyzika.
COUNT (*) spočítá počet řádků v tabulce studentů, což je 10 řádků pro 10 studentů.

GROUP_CONCAT () - GROUP_CONCAT (X) nebo GROUP_CONCAT (X, Y)

GROUP_CONCAT agregační funkce zřetězuje násobné hodnoty do jedné hodnoty s čárkou, aby je oddělil. Má následující možnosti:

GROUP_CONCAT (X): Tím se zřetězí veškerá hodnota x do jednoho řetězce s čárkou „,“ použitou jako oddělovač mezi hodnotami. Hodnoty NULL budou ignorovány.
GROUP_CONCAT (X, Y): Tím se spojí hodnoty x do jednoho řetězce, přičemž hodnota y se použije jako oddělovač mezi každou hodnotou namísto výchozího oddělovače ','. Hodnoty NULL budou také ignorovány.
GROUP_CONCAT (DISTINCT X): Zřetězí všechny odlišné hodnoty x do jednoho řetězce s čárkou „,“ použitou jako oddělovač mezi hodnotami. Hodnoty NULL budou ignorovány.

GROUP_CONCAT (DepartmentName) Příklad

Následující dotaz spojí všechny hodnoty názvu oddělení ze studentů a tabulky oddělení do jednoho řetězce odděleného čárkou. Takže místo toho, abyste vrátili seznam hodnot, na každém řádku jedna hodnota. Vrátí pouze jednu hodnotu na jeden řádek se všemi hodnotami oddělenými čárkou:

VYBERTE SKUPINU_CONCAT (d. Název oddělení)FROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ON s.DepartmentId = d.DepartmentId;

Tím získáte:

Získáte tak seznam 8 hodnot jmen oddělení zřetězených do jednoho řetězce oddělených čárkami.

GROUP_CONCAT (DISTINCT DepartmentName) Příklad

Následující dotaz zřetězí odlišné hodnoty názvu oddělení z tabulky studentů a oddělení do jednoho řetězce odděleného čárkou:

VYBERTE SKUPINU_CONCAT (DISTINCT d. Název oddělení)FROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ON s.DepartmentId = d.DepartmentId;

Tím získáte:

Všimněte si, jak se výsledek liší od předchozího; vráceny pouze tři hodnoty, které jsou názvy odlišných oddělení, a duplicitní hodnoty byly odstraněny.

GROUP_CONCAT (DepartmentName, '&') Příklad

Následující dotaz zřetězí všechny hodnoty sloupce s názvem oddělení z tabulky studentů a oddělení do jednoho řetězce, ale se znakem '&' místo čárky jako oddělovače:

VYBERTE SKUPINU_CONCAT (d. Název oddělení, '&')FROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ON s.DepartmentId = d.DepartmentId;

Tím získáte:

Všimněte si, jak se k oddělení hodnot používá znak „&“ místo výchozího znaku „,“.

SQLite MAX () a MIN ()

MAX (X) vám vrátí nejvyšší hodnotu z hodnot X. MAX vrátí hodnotu NULL, pokud jsou všechny hodnoty x null. Zatímco MIN (X) vám vrátí nejmenší hodnotu z hodnot X. MIN vrátí hodnotu NULL, pokud jsou všechny hodnoty X null.

Příklad

V následujícím dotazu použijeme funkce MIN a MAX k získání nejvyšší a nejnižší známky z tabulky „ Značky “:

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

Tím získáte:

SUM SQLite (x), celkem (x)

Oba vrátí součet všech hodnot x. V následujících se ale liší:

SUM vrátí null, pokud jsou všechny hodnoty null, ale Total vrátí 0.
CELKEM vždy vrátí hodnoty s plovoucí desetinnou čárkou. Funkce SUM vrací celočíselnou hodnotu, pokud jsou všechny hodnoty x celé číslo. Pokud však hodnoty nejsou celé číslo, vrátí hodnotu s plovoucí desetinnou čárkou.

Příklad

V následujícím dotazu použijeme SUM a total k získání součtu všech známek v tabulkách „ Marks “:

VYBERTE SUM (Mark), CELKEM (Mark) FROM Marks;

Tím získáte:

Jak vidíte, TOTAL vždy vrátí plovoucí desetinnou čárku. Ale SUM vrací celočíselnou hodnotu, protože hodnoty ve sloupci „Značka“ mohou být v celých číslech.

Rozdíl mezi SUM a TOTAL příkladem:

V následujícím dotazu ukážeme rozdíl mezi SUM a TOTAL, když dostanou SUM hodnot NULL:

VYBERTE SUM (Mark), TOTAL (Mark) FROM Marks WHERE TestId = 4;

Tím získáte:

Všimněte si, že pro TestId = 4 nejsou žádné značky, takže pro tento test existují nulové hodnoty. SUM vrací nulovou hodnotu jako mezeru, zatímco TOTAL vrací 0.

Skupina vytvořená

Klauzule GROUP BY se používá k určení jednoho nebo více sloupců, které se použijí ke seskupení řádků do skupin. Řádky se stejnými hodnotami budou shromážděny (uspořádány) společně do skupin.

Pro jakýkoli jiný sloupec, který není zahrnut ve skupině podle sloupců, můžete pro něj použít agregační funkci.

Příklad:

Následující dotaz vám poskytne celkový počet studentů přítomných v každém oddělení.

VYBRAT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountFROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ZAP s.DepartmentId = d.DepartmentIdSKUPINA PODLE d. DepartmentName;

Tím získáte:

Klauzule GROUPBY DepartmentName seskupí všechny studenty do skupin po jednom pro každý název oddělení. Pro každou skupinu „oddělení“ to spočítá studenty.

Klauzule HAVING

Pokud chcete filtrovat skupiny vrácené klauzulí GROUP BY, můžete zadat klauzuli "HAVING" s výrazem za GROUP BY. Výraz se použije k filtrování těchto skupin.

Příklad

V následujícím dotazu vybereme ta oddělení, která mají pouze dva studenty:

VYBRAT d.DepartmentName, COUNT (s.StudentId) AS StudentsCountFROM Students AS sVNITŘNÍ PŘIPOJENÍ Oddělení AS d ZAP s.DepartmentId = d.DepartmentIdSKUPINA PODLE d. DepartmentNameHAVING COUNT (s.StudentId) = 2;

Tím získáte:

Klauzule HAVING COUNT (S.StudentId) = 2 vyfiltruje vrácené skupiny a vrátí pouze ty skupiny, které obsahují přesně dva studenty. V našem případě má katedra umění 2 studenty, takže se zobrazuje ve výstupu.

Dotaz a poddotaz na SQLite

Uvnitř libovolného dotazu můžete použít jiný dotaz buď v příkazu SELECT, INSERT, DELETE, UPDATE nebo v jiném poddotazu.

Tento vnořený dotaz se nazývá poddotaz. Uvidíme nyní několik příkladů použití poddotazů v klauzuli SELECT. V kurzu Úpravy dat však uvidíme, jak můžeme použít poddotazy s příkazy INSERT, DELETE a UPDATE.

Použití poddotazu v příkladu klauzule FROM

V následujícím dotazu zahrneme poddotaz dovnitř klauzule FROM:

VYBRATs.StudentName, t. MarkFROM Students AS sVNITŘNÍ SPOJENÍ(VYBERTE StudentId, MarkZ testů AS tVNITŘNÍ PŘIPOJENÍ Značky AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;

Dotaz:

 VYBERTE StudentId, MarkZ testů AS tVNITŘNÍ PŘIPOJENÍ Značky AS m ON t.TestId = m.TestId

Výše uvedený dotaz se zde nazývá poddotaz, protože je vnořený uvnitř klauzule FROM. Všimněte si, že jsme mu dali alias název „t“, abychom mohli v dotazu odkazovat na sloupce z něj vrácené.

Tento dotaz vám poskytne:

Takže v našem případě

s.StudentName je vybrán z hlavního dotazu, který dává jméno studentů a
t. Značka je vybrána z poddotazu; který dává známky získané každým z těchto studentů

Použití poddotazu v příkladu klauzule WHERE

V následujícím dotazu zahrneme poddotaz do klauzule WHERE:

VYBERTE název odděleníZ ODDĚLENÍ AS dKDE NEEXISTUJE (VYBRAT DepartmentIdFROM Students AS sKDE d.DepartmentId = s.DepartmentId);

Dotaz:

Vyberte ID odděleníFROM Students AS sWHERE d.DepartmentId = s.DepartmentId

Výše uvedený dotaz se zde nazývá poddotaz, protože je vnořený v klauzuli WHERE. Poddotaz vrátí hodnoty DepartmentId, které budou použity operátorem NEEXISTUJE.

Tento dotaz vám poskytne:

Ve výše uvedeném dotazu jsme vybrali oddělení, ve kterém není zapsán žádný student. Což je oddělení „Matematika“ tady.

Nastavit operace - UNION, protnout se

SQLite podporuje následující operace SET:

UNIE A UNIE VŠE

Kombinuje jednu nebo více sad výsledků (skupina řádků) vrácených z více příkazů SELECT do jedné sady výsledků.

UNION vrátí odlišné hodnoty. UNION ALL však nebude a bude zahrnovat duplikáty.

Všimněte si, že název sloupce bude název sloupce zadaný v prvním příkazu SELECT.

Příklad UNION

V následujícím příkladu získáme seznam DepartmentId z tabulky studentů a seznam DepartmentId z tabulky departementů ve stejném sloupci:

VYBRAT DepartmentId AS DepartmentIdUnlined OD studentůUNIEVYBERTE DepartmentId Z oddělení;

Tím získáte:

Dotaz vrací pouze 5 řádků, což jsou odlišné hodnoty ID oddělení. Všimněte si první hodnoty, která je nulovou hodnotou.

SQLite UNION VŠE Příklad

V následujícím příkladu získáme seznam DepartmentId z tabulky studentů a seznam DepartmentId z tabulky departementů ve stejném sloupci:

VYBRAT DepartmentId AS DepartmentIdUnlined OD studentůUNION ALLVYBERTE DepartmentId Z oddělení;

Tím získáte:

Dotaz vrátí 14 řádků, 10 řádků z tabulky studentů a 4 řádky z tabulky oddělení. Všimněte si, že ve vrácených hodnotách jsou duplikáty. Všimněte si také, že název sloupce byl název zadaný v prvním příkazu SELECT.

Podívejme se nyní, jak UNION bude mít různé výsledky, pokud UNION ALL nahradíme UNION:

SQLite INTERSECT

Vrátí hodnoty, které existují v obou kombinovaných výsledcích. Hodnoty, které existují v jedné z kombinovaných sad výsledků, budou ignorovány.

Příklad

V následujícím dotazu vybereme hodnoty DepartmentId, které existují v obou tabulkách Studenti a Oddělení ve sloupci DepartmentId:

VYBRAT DepartmentId OD studentůProtínajíVYBERTE DepartmentId Z oddělení;

Tím získáte:

Dotaz vrátí pouze tři hodnoty 1, 2 a 3. Které jsou hodnoty, které existují v obou tabulkách.

Hodnoty null a 4 však nebyly zahrnuty, protože hodnota null existuje pouze v tabulce studentů a ne v tabulce oddělení. A hodnota 4 existuje v tabulce oddělení, nikoli v tabulce studentů.

Proto byly hodnoty NULL i 4 ignorovány a nebyly zahrnuty do vrácených hodnot.

AŽ NA

Předpokládejme, že pokud máte dva seznamy řádků, list1 a list2, a chcete, aby řádky byly pouze ze seznamu1, který v seznamu2 neexistuje, můžete použít klauzuli "EXCEPT". Klauzule EXCEPT porovnává dva seznamy a vrací ty řádky, které existují v seznamu1 a neexistují v seznamu2.

Příklad

V následujícím dotazu vybereme hodnoty DepartmentId, které existují v tabulce oddělení a neexistují v tabulce studentů:

VYBERTE DepartmentId Z odděleníAŽ NAVYBRAT DepartmentId OD studentů;

Tím získáte:

Dotaz vrátí pouze hodnotu 4. Což je jediná hodnota, která existuje v tabulce oddělení a neexistuje v tabulce studentů.

NULL manipulace

Hodnota „ NULL “ je speciální hodnota v SQLite. Používá se k reprezentaci neznámé nebo chybějící hodnoty. Všimněte si, že nulová hodnota je úplně jiná než " 0 " nebo prázdná "" hodnota. Protože 0 a prázdná hodnota je známá hodnota, je nulová hodnota neznámá.

Hodnoty NULL vyžadují speciální zpracování v SQLite, uvidíme nyní, jak zacházet s hodnotami NULL.

Vyhledejte hodnoty NULL

K vyhledávání hodnot null nemůžete použít běžný operátor rovnosti (=). Například následující dotaz vyhledá studenty, kteří mají nulovou hodnotu DepartmentId:

VYBERTE * OD ŽÁKŮ KDE DepartmentId = NULL;

Tento dotaz neposkytne žádný výsledek:

Protože hodnota NULL se nerovná žádné jiné hodnotě včetně samotné hodnoty null, proto nevrátila žádný výsledek.

Chcete-li, aby dotaz fungoval, musíte použít operátor "IS NULL" k vyhledání hodnot null následujícím způsobem:

VYBERTE * OD STUDENTŮ KDE JE IDENTIFIKACE NULL;

Tím získáte:

Dotaz vrátí ty studenty, kteří mají nulovou hodnotu DepartmentId.

Pokud chcete získat ty hodnoty, které nejsou null, musíte použít operátor " IS NOT NULL " takto:

VYBERTE * OD STUDENTŮ, KDE NEMÁ ODDĚLENÍ NULL;

Tím získáte:

Dotaz vrátí ty studenty, kteří nemají hodnotu NULL DepartmentId.

Podmíněné výsledky

Pokud máte seznam hodnot a chcete vybrat některou z nich na základě určitých podmínek. Podmínka pro tuto konkrétní hodnotu by proto měla být pravdivá, aby mohla být vybrána.

Výraz CASE vyhodnotí tento seznam podmínek pro všechny hodnoty. Pokud je podmínka pravdivá, vrátí tuto hodnotu.

Například pokud máte sloupec „Hodnocení“ a chcete vybrat textovou hodnotu založenou na hodnotě hodnocení takto:

- "Vynikající", pokud je známka vyšší než 85.

- "Velmi dobré", pokud je známka mezi 70 a 85.

- „Dobré“, pokud je známka mezi 60 a 70.

Potom k tomu můžete použít výraz CASE.

To lze použít k definování nějaké logiky v klauzuli SELECT, takže můžete vybrat určité výsledky v závislosti na určitých podmínkách, jako je například příkaz if.

Operátor CASE lze definovat s různými syntaxemi takto:

Můžete použít různé podmínky:

PŘÍPADKDY podmínka1 POTOM výsledek1KDY podmínka2 POTOM výsledek2KDY podmínka3 POTOM výsledek3 ... JINÉ resultnKONEC

Nebo můžete použít pouze jeden výraz a vybrat různé možné hodnoty:

Výraz CASEKDY hodnota1 POTOM výsledek1KDY hodnota2 POTOM výsledek2KDY hodnota3 POTOM výsledek3 ... JINÉ restulnKONEC

Všimněte si, že klauzule ELSE je volitelná.

Příklad

V následujícím příkladu použijeme výraz CASE s hodnotou NULL ve sloupci ID oddělení v tabulce Studenti k zobrazení textu „Žádné oddělení“ následujícím způsobem:

VYBRATJméno studenta,PŘÍPADKDY DepartmentId JE NULL PAK 'No Department'ELSE DepartmentIdEND AS DepartmentIdOD studentů;

Operátor CASE zkontroluje hodnotu DepartmentId, zda je null nebo ne.
Pokud se jedná o hodnotu NULL, pak místo hodnoty DepartmentId vybere doslovnou hodnotu 'No Department'.
Pokud není nulová hodnota, pak vybere hodnotu sloupce DepartmentId.

Získáte výstup, jak je znázorněno níže:

Společný tabulkový výraz

Běžné tabulkové výrazy (CTE) jsou poddotazy, které jsou definovány uvnitř příkazu SQL s daným názvem.

Má výhodu oproti poddotazům, protože je definován mimo příkazy SQL a usnadňuje čtení, údržbu a porozumění dotazům.

Běžný výraz tabulky lze definovat vložením klauzule WITH před příkazy SELECT následujícím způsobem:

S CTEnameTAK JAKO(Příkaz SELECT)VYBERTE, AKTUALIZUJTE, VLOŽTE nebo aktualizujte prohlášení zde Z CTE

„Název CTE “ je libovolný název, který můžete pro CTE pojmenovat, můžete jej později použít. U CTE můžete definovat příkazy SELECT, UPDATE, INSERT nebo DELETE

Nyní se podívejme na příklad, jak použít CTE v klauzuli SELECT.

Příklad

V následujícím příkladu definujeme CTE z příkazu SELECT a poté jej použijeme později na jiný dotaz:

S AllDepartmentsTAK JAKO(Vyberte DepartmentId, DepartmentNameOD ODDĚLENÍ)VYBRATs.StudentId,s.StudentName,a. název odděleníFROM Students AS sVNITŘNÍ PŘIPOJENÍ AllDepartments AS a ON s.DepartmentId = a.DepartmentId;

V tomto dotazu jsme definovali CTE a dali mu název „ AllDepartments “. Tento CTE byl definován z dotazu SELECT:

 Vyberte DepartmentId, DepartmentNameOD ODDĚLENÍ

Poté, co jsme definovali CTE, jsme jej použili v dotazu SELECT, který následuje po něm.

Všimněte si, že společné tabulkové výrazy neovlivní výstup dotazu. Jedná se o způsob, jak definovat logické zobrazení nebo poddotaz, aby bylo možné je znovu použít ve stejném dotazu. Běžné tabulkové výrazy jsou jako proměnná, kterou deklarujete, a znovu ji použijete jako poddotaz. Na výstup dotazu má vliv pouze příkaz SELECT.

Tento dotaz vám poskytne:

Pokročilé dotazy

Pokročilé dotazy jsou ty dotazy, které obsahují komplexní spojení, poddotazy a některé agregáty. V následující části uvidíme příklad pokročilého dotazu:

Odkud pocházíme

Jména oddělení se všemi studenty pro každé oddělení
Jméno studenta oddělené čárkou a
Zobrazeno oddělení, ve kterém jsou nejméně tři studenti

VYBRATd. název oddělení,COUNT (s.StudentId) počet studentů,GROUP_CONCAT (StudentName) AS StudentiZ ODDĚLENÍ AS dVNITŘNÍ PŘIPOJENÍ Studenti JSOU ZAPNUTI s.DepartmentId = d.DepartmentIdSKUPINA PODLE d. Název odděleníHAVING COUNT (s.StudentId)> = 3;

Přidali jsme klauzuli JOIN, abychom získali DepartmentName z tabulky Departments. Poté jsme přidali klauzuli GROUP BY se dvěma agregačními funkcemi:

„COUNT“ pro spočítání studentů pro každou skupinu oddělení.
GROUP_CONCAT pro zřetězení studentů pro každou skupinu s čárkou oddělenou v jednom řetězci.
Po SKUPINĚ BY jsme pomocí klauzule HAVING filtrovali katedry a vybrali pouze ty katedry, které mají alespoň 3 studenty.

Výsledek bude následující: