6.4. Syntaxe SQL

Opakování Sqlite3

1	Stáhněte si z Teams soubor db02.zip a extrahujte jej tak, aby rozbalené soubory byly ve složce c:\temp\db02
2	Spusťte příkazový řádek a příkazem chcp 65001 nastavte kódování češtiny UTF-8	cmd
3	Přejděte do složky c:\temp\db02	cd
4	Ověřte si výpisem složky, že vidíte ve výpisu soubor Chinook.sqlite	dir
5	Spusťte sqlite3 s otevřením databáze Chinook.sqlite příkazem: c:\programy\sqlite\sqlite3 Chinook.sqlite
6	Ověřte, že aktuální databáze je Chinook.sqlite	.databases
7	Vypište seznam tabulek	.tables
8	Vypište řádky tabulky album	select * from album;
9	Vypište si seznam parametrů SQLite	.show

Základy teorie SQL

• SQL (Structured Query Language) - strukturovaný dotazovací jazyk - části
  • Data Definition Language - příkazy pro definici struktury databáze - tabulky (create table), pole, indexy, vazby mezi tabulkami
  • Data Manipulation Language - vkládání (insert), změny (update), mazání (delete) dat, výběr (dotazování, hledání) dat z tabulek (select)
  • Transaction Control Language - řízení transakcí - BEGIN, COMMIT
• diagramy syntaxe - http://www.sqlite.org/lang.html
  • příkaz - musí být vždy ukončen středníkem (jinak se neprovede)
  • jednotlivé příkazy SQL
• case-insensitive - u příkazů nezáleží na velikosti písmen
• komentáře - psát za dvě pomlčky -- nebo ve stylu C /* */
• názvy datových proměnných (třeba tabulek, polí) lze uzavírat do "uvozovek"
• textové hodnoty (řetězce - literály) lze uzavírat do 'apostrofů', při výskytu v řetězci se apostrof zdvojuje (publisher = 'O''Reilly')  
• lze používat i matematické výrazy (operace + - * /) včetně funkcí, porovnávání (< <= => >), rovnost (= != <>), logické operace (AND OR)

6.5. Data Definition Language v rámci SQL

6.5.1. Typy dat

• Integer - celá čísla do velikosti 8 B (1 byte = 2⁸ = -128 až 127, 8 bytes = 2⁶⁴ = 19 číslic
• Real - reálná čísla (s desetinnou částí), oddělovač tečka
• Text - řetězec znaků s proměnnou délkou, kódování UTF-8, také se používá char, varchar - textové hodnoty (řetězce) lze uzavírat do 'apostrofů', při výskytu v řetězci se apostrof zdvojuje (publisher = 'O''Reilly')  
• Blob (binary large object) - posloupnost bytů (max. velikost jednoho objektu 2 GB) - pro zvuk, obrázky, jakákoliv binární data - vkládají se např. jako x'0F0E', kdy každý byte se uvádí jako hexadecimální číslo pomocí 2 znaků

• pozn. typy dat se uvádí jen kvůli kompatibilitě s jinými SQL databázemi a nadstavbami v C, Pascalu (aby věděli jak nadefinovat typy polí) - jen v rámci SQLite do polí lze ukládat jakoukoliv hodnotu (viz příklady http://www.sqlite.org/datatype3.html)

14	Ve spuštěném sqlite3 ověřte následujícími příkazy typy dat:
15	select typeof(5);
16	select typeof(5.455);
17	select typeof(5,455);
18	select typeof("retezec");
19	select typeof("");
20	select typeof(CURRENT_DATE);
21	select typeof(x'2eda');
22	select typeof(5>2);

6.5.2. Změna tabulky

• ALTER TABLE - diagram - přidání sloupce nebo přejmenování tabulky (nelze měnit již definované sloupce)

23	Ukončete sqlite3 a znovu ho spusťte s otevřením databáze db02.sqlite příkazem: c:\programy\sqlite\sqlite3 db02.sqlite
24	Zjistěte názvy existujících tabulek a vypište si jejich řádky	.tables select * from ...
25	Zobrazte si strukturu dat tabulky library	.schema library
26	Nastavte režim csv a oddělovač podle skutečnosti v souboru knihovna-utf8.csv	.mode csv .separator ;
27	Importujte data ze souboru knihovna-utf8.csv příkazem: .import knihovna-utf8.csv library
28	Pro kontrolu si vypište prvních 10 řádků tabulky	select ... limit 10
29	Přejmenujte tabulku library na knihovna příkazem: alter table library rename to knihovna;

• upřesnění vlastností sloupce - DEFAULT hodnota - automaticky přiřazovaná hodnota (pokud není při ukládání uvedena jiná hodnota)

30

Přidejte do tabulky knihovna sloupec rok_vydani s číselným typem dat a defaultní hodnotou 2000 příkazem: alter table knihovna add column rok_vydani integer default 2000;

6.5.3. Smazání tabulky

• DROP TABLE jméno_tabulky;
• diagram - vymaže tabulku z databáze včetně definic, dat i  indexů

31	Zobrazte si strukturu dat tabulky tbl	.schema
32	Vymažte z databáze db02.sqlite tabulku tbl	drop table tbl;

6.5.4. Tvorba tabulky

• tvorba tabulky a definice struktury tabulky
• CREATE TABLE název_tabulky (název_sloupce TYP_DAT VLASTNOSTI, ...);
• diagram_create, diagram_sloupce
• název_tabulky, název_sloupce - bez mezer, bez diakritiky

33	Stáhněte si do c:\temp\db02 z Teams soubor create_psc.txt obsahující SQL příkaz pro vytvoření nové tabulky psc s použitím schématu z prvního řádku souboru psc-utf8.csv - prohlédněte si obsah obou souborů	text. editor
34	V sqlite3 vytvořte v databázi db02.sqlite tabulku psc provedením příkazu create_psc.txt pomocí .read create_psc.txt	.read
35	Vymažte ze souboru psc-utf8.csv první řádek a ověřte oddělovač	text. editor
36	Ověřte režim csv (.show), nastavte správný oddělovač (.separator) a proveďte import dat ze souboru psc-utf8.csv	.import file table
37	Ověřte výpisem naimportovaná data	select ...

Cvičení Sqlite3

2	Spusťte příkazový řádek a příkazem chcp 65001 nastavte kódování češtiny UTF-8	cmd
3	Přejděte do složky c:\temp\db02	cd
4	Ověřte si výpisem složky, že vidíte ve výpisu soubor Chinook.sqlite	dir
5	Spusťte sqlite3 s otevřením databáze Chinook.sqlite příkazem: c:\programy\sqlite\sqlite3 Chinook.sqlite
6	Ověřte, že aktuální databáze je Chinook.sqlite	.databases
7	Vypište seznam tabulek	.tables
8	Vypište řádky tabulky artist	select * from artist;
9	Vypište si seznam parametrů SQLite	.show
10	Úkolem je exportovat tabulku artist z databáze Chinook.sqlite do dokumentu HTML
11a	Zapněte režim výpisu html	.mode html
11b	Nastavte režim výpisu do souboru artist.html	.output artist.html
11c	Znovu vypište řádky tabulky artist - výsledkem je soubor c:\temp\db02\artist.html	select ...
12	Doplňte do dokumentu artist.html    - hlavičku head (kódování bude utf-8)  - tělo (body)  - html značky pro správné zobrazení tabulky ve webovém prohlížeči (tabulka bude mít jednoduchý rámeček)  - před tabulku nadpis se svým příjmením	text. editor
13	Ověřte vzhled dokumentu artist.html ve webovém prohlížeči
14	Zkomprimujte obsah složky c:\temp\db02 do souboru se svým příjmením prijmeni.zip a přiložte tento soubor k aktuálnímu zadání v Teams (tlač. Přidat práci) a úkol odevzdejte (tlač. Odevzdat)