6.7. Data Manipulation Language - hledání v tabulce

6.7.1. Opakování - tvorba, vkládání do tabulky

• CREATE TABLE název_tabulky (název_sloupce TYP_DAT, ...); - diagram_create, diagram_sloupce, diagram vlastností sloupce
  • ALTER TABLE - diagram - přidání sloupce nebo přejmenování tabulky (nelze měnit již definované sloupce)
  • DROP TABLE jméno_tabulky; - diagram - vymaže tabulku z databáze
• INSERT INTO tabulka (sloupec1, ...) VALUES (hodnota, ...); - diagram
  • UPDATE table_name SET column_name=new_value [, ...] WHERE výraz; - diagram
  • DELETE FROM table_name WHERE expression; - diagram

1	Stáhněte si přes FTP z fileserveru pomocí účtu anonym soubor db04.zip a extrahujte jej tak, aby rozbalené soubory byly ve složce c:\temp\db04	FileZilla
2	Otevřte v textovém editoru soubor data04.sql a doplňte v příkazu CREATE TABLE odpovídající typy sloupců	PSPad
3	Přidejte 3 nové příkazy INSERT INTO s vlastními daty, soubor uložte	PSPad
4	Spusťte příkazový řádek, přejděte v něm do složky db04, nastavte si kódování UTF-8 (65001)	cmd, cd, chcp
5	Vytvořte ve složce db04 novou databázi db04.sqlite spuštěním c:\programy\sqlite\sqlite3 s parametrem db04.sqlite	cmd
6	Ověřte v sqlite3 cestu k databázi	.databases
7	Proveďte příkazy v souboru data04.sql	.read
8	Otevřte v textovém editoru soubor psc.sql a ověřte správnost dat
9	Proveďte příkazy psc.sql v databázi db04.sqlite a poté ověřte existenci tabulek a jejich schéma	.tables, .schema

6.7.2. Select - výběr dat

• nejdůležitější a nejpoužívanější příkaz - výběr dat z tabulky - jinými slovy výstup, dotazování (query), hledání (search, seek, find), procházení (browse)
• diagram1, 2
• základní verze: SELECT seznam_sloupců_pro_výstup FROM jméno_tabulky;
• místo seznamu_sloupců lze použít zástupný znak * (asterisk - wildcard), který zastupuje všechny sloupce
• příklad výpisu všech řádků tabulky: SELECT * FROM jméno_tabulky
• pozn. vypsané řádky nejsou seřazené (není definováno řazení)

10	Nastavte si v sqlite režim výpisu do sloupců (column) včetně zobrazení názvů sloupců (.header on)	.mode, .header
11	Nastavte si režim opakovaného výpisu příkazů pomocí .echo on	.echo
12	Vypište všechny sloupce všech řádků tabulky x	SELECT ? FROM ?;
13	Vypište sloupce jmeno a body u všech řádků tabulky x, pak proveďte stejný výpis ale s opačným pořadím sloupců (nejprve body, pak jmeno)	SELECT ? FROM ?;

• LIMIT - 2 - omezuje výstup na určený počet řádků (používá se pro stránkování výpisu)
• OFFSET určuje první vypisovaný řádek (používá se pro určení stránky výpisu)
• např. SELECT ... LIMIT 10 OFFSET 3 - vrací řádky 4 - 13

14	Vypište prvních 5 řádků tabulky x	SELECT * FROM x LIMIT 5;
15	Vypište prvních 5 řádků tabulky x a uveďte i offset	SELECT * FROM x LIMIT 5 OFFSET 0;
16	Vypište řádky 5 až 7 tabulky x (místo ? dosaďte správná čísla - stejně tak i v dalších cvičeních)	SELECT * FROM x LIMIT ? OFFSET ?;

• Příklad stránkování databázových výpisů na dynamických webových stránkách:
• SELECT * FROM x LIMIT y OFFSET n * y;
• kde y je počet řádků na stránku, n je číslo strany (první strana je 0)
• proměnné y a n se nastavují programově (např. pomocí PHP)

17	Ve výpisu mají být např. vždy 3 řádky z tabulky x - vypište první stránku (první 3 řádky)	SELECT * FROM x LIMIT 3 OFFSET 0 * 3;
18	Uživatel zmáčkl v prohlížeči tlačítko pro další stránku - vypište druhou stránku, třetí stránku, ...	SELECT * FROM x LIMIT 3 OFFSET ? * 3;

6.7.3. Filtrování - WHERE

• diagram1
• SELECT seznam_sloupců FROM jméno_tabulky WHERE výraz;
• výraz za WHERE omezuje (filtruje) vybrané řádky - obr. - stejně jako u příkazů UPDATE nebo DELETE (probíráno dříve)
• výraz se skládá z operandů a operátorů - příklady porovnávacích operátorů (pro 2 operandy) ve výrazu: < <= > >=, rovnost = == (je jedno který se použije), nerovnost != <> (také je jedno který)

19	Vypište z tabulky x jména patřící do skupiny01	SELECT jmeno,skupina FROM x WHERE skupina='skupina01';
20	V hledaném řetězci se ctí velikost písmen - příklad nesprávného dotazu (nic se nenajde)	SELECT jmeno,skupina FROM x WHERE skupina='Skupina01';
21	Vypište z tabulky x jména nepatřící do skupiny02 (místo ? doplňte podmínku)	SELECT jmeno,skupina FROM x WHERE ?;
22	Vypište z tabulky x jména a body u řádků s počtem bodů menším než 400	SELECT jmeno,body FROM x WHERE ?;

• nejdůležitější operátory ve výrazu (podle priority):
  • * / % (násobení dělení modulo)
  • + - (sčítání odčítání)
  • < <= > >= (porovnávání)
  • rovnost = == (je jedno který se použije), nerovnost != <> (také je jedno který), LIKE (viz dále)
  • AND OR (logický součin, součet)

23	Vypočítejte pomocí SELECT 2+3*4 a ověřte výsledek, pak výraz upravte tak, aby se nejprve sčítalo	SELECT 2+3*4;
24	Vypište z tabulky x jména a body u řádků s počtem bodů od 300 do 800 (včetně mezí)	SELECT jmeno,body FROM x WHERE body>=300 AND ?;
25	Vypište z tabulky x jména a skóre u řádků, u kterých je desetinásobek skóre větší než 50	SELECT jmeno,skore FROM x WHERE 10*skore ?;
26	Vypište jména patřící do skupiny01 nebo skupiny02	?

• operátory LIKE a NOT LIKE slouží pro hledání textových hodnot podle šablon (příkladů)
  • % zastupuje jakoukoliv posloupnost znaků (i žádnou)
  • _ zastupuje jeden znak
• podobný je operátor GLOB, jen místo % používá *

27	Vypište celé řádky se jmény končícími písmenem 'e'	SELECT * FROM x WHERE jmeno LIKE '%e';
28	Vypište celé řádky se jmény nezačínajícími písmenem 'B'	SELECT * FROM x WHERE jmeno NOT LIKE ?;
29	Vypište jména obsahující řetězec 'nn' nebo 'll' (dvě el) - opravte chybu v názvu sloupce	SELECT jmena FROM x WHERE jmeno LIKE '%nn%' OR ?;

6.7.4. Řazení - ORDER BY

• diagram1, 2, 3
• SELECT seznam_sloupců FROM jméno_tabulky WHERE výraz ORDER BY sloupec;
• sloupec za ORDER BY určuje řazení výstupu
• pokud se použije podmínka WHERE, pak se řádky nejdříve vyfiltrují a až poté seřadí

30	Vypište celé řádky tabulky x seřazené podle skore	SELECT * FROM x ORDER BY skore;
31	Vypište z tabulky x jména a body u řádků s počtem bodů větším než 300, řádky budou seřazené podle počtu bodů	SELECT jmeno,body FROM x WHERE ? ORDER BY ?;

• místo sloupce může být za ORDER BY výraz (nebo více výrazů za čárkou - výpis bude řazen ve více stupních)

32

Vypište celé řádky tabulky x seřazené podle skupin a ve skupinách podle bodů

SELECT * FROM x ORDER BY skupina, body;

• ORDER BY expression [COLLATE collation_name] [ASC|DESC] [,...]
• COLLATE - určuje řazení textů - 3 - defaultně BINARY, NOCASE řadí stejně malá a velká písmena
• pozn. SQLite řadí podle ASCII - nepodporuje řazení českých znaků (kvůli snížení velikosti DLL knihovny - nebyla by lite) - řazení lze doprogramovat funkcí
• pozn. řazení by se mělo používat jen, je-li opravdu potřeba - je časově a paměťově náročné
• ASC/DESC určuje vzestupně/sestupně - 3 - default je ASC

33	Vypište celé řádky tabulky x seřazené podle bodů sestupně (od největšího po nejmenší)	SELECT * FROM x ORDER BY ? DESC;
34	Vypište jména a body tří bodově nejlepších osob	SELECT jmeno,body FROM x ORDER BY body DESC LIMIT ?;

• odstranění duplicitních (stejných) řádků - SELECT DISTINCT ... - má smysl jen po seřazení - diagram1

35

Vypište názvy skupin uspořádané vzestupně (ve výpisu nesmí být stejné řádky)

SELECT DISTINCT skupina FROM x ORDER BY skupina;

6.7.5. Samostatné cvičení

• Všechny následující úkoly proveďte v databázi db04 na tabulce psc
• Ověřte si příkazem .show, že máte nastaveny parametry .headers on, .echo on
• Nastavte si režim výpisu typu seznam příkazem .mode list
• Výstup provedených příkazů přesměrujte do souboru cviceni04.txt (příkazem .output)
• Po provedení cvičení uložte na fileserver celou složku db04

36	Vypište názvy částí obcí se směrovacím číslem 79401 seřazené vzestupně
37	Vypište 10 nejvyšších PSČ včetně názvu části obce
38	Vypište názvy částí obce (seřazené vzestupně), které obsahují řetězec 'ia', ve výstupu odstraňte duplicitní řádky
39	Vypište názvy částí obce s hlavní poštou (poslední dvojčíslí psč 00) v původně jihomoravském kraji (začínající 6)