Rozšíření hledání v tabulkách

6.7.6. Opakování tvorby tabulky

• CREATE TABLE název_tabulky (název_sloupce TYP_DAT, ...); - diagram_create, diagram_sloupce
• DROP TABLE jméno_tabulky; - diagram - vymaže tabulku z databáze
• INSERT INTO tabulka (sloupec1, ...) VALUES (hodnota, ...); - diagram

1	Stáhněte si přes FTP z fileserveru pomocí účtu anonym soubor db05.zip a extrahujte jej tak, aby rozbalené soubory byly ve složce c:\temp\db05	FileZilla
2	Otevřte v textovém editoru soubor thesaurus.sql a zjistěte jaké jsou sloupce tabulky	PSPad
3	Spusťte příkazový řádek a přejděte v něm do složky db05	cmd, cd
4	Vytvořte ve složce db05 novou databázi db05.sqlite spuštěním: c:\programy\sqlite\sqlite3 db05.sqlite	cmd
5	Vytvořte v db05 novou tabulku thesaurus s odpovídajícími sloupci	create table
6	Načtěte data ze souboru thesaurus.sql	.read

6.7.7. Transakce, zamykání databáze

• transakce umožňují bezpečné provedení skupiny příkazů obalením do jednoho příkazu BEGIN TRANSACTION; ... COMMIT; - zejména u operací typu INSERT, UPDATE a DELETE
• např. u převodu z účtu na účet je třeba částku z jednoho účtu odečíst a k druhému přičíst - nesmí se stát, že by se provedla jen část operace (odečtení nebo přičtení) - proto se musí obě operace provést jako jedna transakce
• při selhání transakce se musí tabulka uvést do stavu před transakcí (odvolají se provedené změny - roll back)
• možné důvody selhání - výpadek el. energie, náhlé ukončení běžícího procesu ze strany OS (kill), zmizení souborového systému (USB flash)
• další výhody - např. vložení nového řádku příkazem INSERT vyžaduje fyzický zápis do souboru včetně přeřazení indexů - pro větší počet řádků je vkládání pomalé - provedení více příkazů INSERT v rámci jedné transakce vše zrychlí - fyzický zápis a reindexace se provádí až po ukončení transakce (po COMMIT)
• SQLite provádí automaticky každý příkaz jako transakci
• pokud je třeba transakce provádět řízeně, více příkazů se jako transakce provede obalením:
  • začátek transakce: BEGIN  TRANSACTION;
  • konec transakce: COMMIT;
• před ukončením transakce lze všechny příkazy od BEGIN odvolat pomocí ROLLBACK (operace se ukládají průběžně do tzv. journalu - lze jej vidět po zapnutí pragma journal_mode=persist; - běžně je delete)

7	Přerušte načítání dat do databáze	sqlite3
8	Otevřte znovu v textovém editoru soubor thesaurus.sql a doplňte začátek a konec transakce BEGIN TRANSACTION; ... COMMIT;	PSPad
9	Najděte a opravte chybu v názvu sloupce (viz chybové hlášení při .read), uložte soubor a zavřete PSPad	PSPad
10	Smažte v db05.sqlite tabulku thesaurus a poté ji znova nadefinujte	drop table, create table
11	Načtěte data ze souboru thesaurus.sql a porovnejte rychlost načtení, ověřte počet řádků v tabulce pomocí select count() ...	.read
12	Ověřte nastavení journalu příkazem pragma journal_mode;

• Zamykání databáze (locking) - zajišťuje výhradní přístupnost dat pro aktuální operaci (defaultně se po operaci data odemknou a jsou přístupné ostatním uživatelům = režim normal)
• režim zamykání se nastavuje příkazem pragma locking_mode=normal | exclusive (exclusive - výhradní zamknutí pro jednoho uživatele)
• pozn. po změně režimu zamykání je nutno provést jednu operaci zápisu nebo čtení pro aktivaci zámku

13	Ověřte režim zamykání příkazem pragma locking_mode;	sqlite3
14	Zamkněte databázi nastavením výhradního režimu pragma locking_mode=exclusive;
15	Smažte první řádek tabulky thesaurus příkazem delete from ? where id=?;
16	Otevřte další instanci příkazového řádku, spusťte v něm také sqlite3 s otevřením databáze db05.sqlite	cmd
17	V této druhé instanci zkuste smazat řádek tabulky thesaurus s id=2	sqlite3
18	Zavřete cmd okno, kde je hlášení o zamknuté databázi

6.7.8. Opakování hledání

• diagram1, 2
• SELECT seznam_sloupců_pro_výstup FROM jméno_tabulky LIMIT počet_řádků OFFSET od_řádku;
• SELECT seznam_sloupců FROM jméno_tabulky WHERE podmínka ORDER BY sloupec;
  • WHERE - filtrování podle podmínky např. sloupec = 'hodnota', sloupec like 'B%'
  • ORDER BY - řazení
  • SELECT DISTINCT - odstranění duplicit

19	Najděte v tabulce thesaurus synonyma pro slovo 'machine' uspořádaná abecedně: select ? from ? where ? order by ?;	sqlite3
20	Vypište slovní druhy (podst.jm., sloveso, ...) obsažené ve slovníku (každý druh může být uveden jen jednou)
21	Vypište prvních 10 synonym (podle abecedy) a zapamatujte si rychlost výpisu
22	Vypište synonymum s OFFSETEM 100000 a zapamatujte si rychlost výpisu

6.7.9. Tvorba indexů - zrychlení hledání

• indexy - rejstříky (klíče, pořadníky) podle sloupců - urychlují řazení a vyhledávání v tabulce (pozn. řadit a hledat lze i bez indexů, ale je to pomalé)
• indexy by se měly používat s rozmyslem - příliš mnoho indexů naopak zpomalí provozování databáze (při každé aktualizaci dat se musí aktualizovat navíc indexy)
• diagram
• CREATE INDEX index_name ON table_name ( column_name );
• doporučení: jméno_indexu volit jako index_jméno_sloupce (prefix "index_") nebo jméno_sloupce_index (suffix "_index")
• pro typy dat označené PRIMARY KEY a UNIQUE se index vytváří automaticky (má prefix sqlite_jméno_sloupce)
• index lze vytvořit i pro více sloupců - řadí se podle sloupce1, při stejné hodnotě pak podle sloupce 2, např. podle příjmení, pak podle jména
  • CREATE INDEX index_name ON table_name ( column_name1, column_name2 );
• smazání indexu: DROP INDEX index_name;
• indexy jsou fyzicky stavěny jako binární stromy (B-tree) - animace YouTube

23	Vytvořte index pro sloupec word_eng: create index index_word_eng on thesaurus (word_eng);	sqlite3
24	Podobně vytvořte index_synonym pro synonyma
25	Ověřte indexy ve schématu tabulky thesaurus	.schema
26	Ověřit indexy lze i příkazem .indices tabulka	.indices
27	Znovu vypište prvních 10 synonym (podle abecedy) a porovnejte rychlost
28	Také vypište synonymum s OFFSETEM 100000 a porovnejte rychlost

• reindex - příkaz pro nové vytvoření indexů

6.7.10. Vnořené dotazy

• do příkazu SELECT může vstupovat místo tabulky výstup z jiného (vnořeného) příkazu SELECT - dává se do závorek
  • např. SELECT * FROM (SELECT * FROM Tbl);
  • select name from (select name, type_id from (select * from foods));

6.7.11. Logické operace s více tabulkami

• slouží ke kombinaci více výsledků SELECT - select, union, obr.
• SELECT ... FROM ... WHERE ... compound_operator SELECT ... FROM ... WHERE ...
• výsledky selectů musí mít stejné sloupce (ale vstupní tabulky mohou mít ostatní nevypisované sloupce jiné)
• způsob řazení ORDER BY ... LIMIT ... OFFSET ... se uvádí za poslední select (aplikuje se na celkový výsledek)
• pozn. při těchto operacích nevznikají nové sloupce (= vertikální spojování) - narozdíl od JOIN (relace - horizontální spojování - viz další hodiny)

Operátory:

• UNION - sjednocení (sečtení) množin - výsledkem je tabulka select1 + select2 - z výsledku jsou odstraněny duplicitní řádky (duplicity neodstraňuje operátor UNION ALL)
• INTERSECT - průnik množin - ve výsledku jsou jen řádky obsažené zároveň v obou vstupních množinách
• EXCEPT - odečtení množin - ve výsledku jsou všechny řádky z první množiny, které zároveň nejsou v druhé množině

29	Vytvořte v db05.sqlite novou tabulku pratele se sloupci id, jmeno, mesto, team (můžete použít vzor.sql) včetně indexu pro sloupec jmeno	PSPad, sqlite3
30	Vložte do tabulky min. 3 řádky s různými daty
31	Podobně vytvořte novou tabulku nepratele se stejnymi sloupci jako u tabulky pratele + jedním novým sloupcem alias, tabulku vyplňte min. 3 řádky s jinými daty než v tabulce pratele (pozor na jméno indexu - musí být jiné než existující)
32	Vypište najednou všechny existující týmy z obou tabulek: select team from pratele union select team from nepratele;
33	Předchozí příkaz upravte pro výpis seřazený podle abecedy: ... order by ...
34	Vypište města, kde máte přátele a zároveň nepřátele (operátor intersect)
35	Poslední 3 příkazy vypište do souboru union.txt do složky db05 (.output union.txt) včetně použitého SQL příkazu (.echo on), názvu sloupce (.header on)
36	Celou složku db05 zabalte jako db05.zip a uložte na fileserver	Filezilla