6.6. Data Manipulation Language - vkládání do tabulky

6.6.1. Opakování tvorby tabulky

• CREATE TABLE název_tabulky (název_sloupce TYP_DAT, ...); - diagram_create, diagram_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

1	Stáhněte si přes FTP z fileserveru pomocí účtu anonym soubor db03.zip a extrahujte jej tak, aby rozbalené soubory byly ve složce c:\temp\db03	FileZilla
2	Spusťte sqlite3 a zároveň otevřte databázi db03.sqlite	cmd
3	Ověřte v sqlite3 cestu k databázi, tabulky v databázi a hlavně schéma tabulky skupina	.databases, .tables, .schema
4	Vypište si řádky tabulky skupina	select ...

6.6.2. Primární klíč tabulky

• v tabulce je nutný primární klíč (PRIMARY KEY) = jedinečná hodnota každého řádku pro jeho rozlišení, také se mu říká hlavní index (používá se např. pro vazby mezi tabulkami)
• pokud se PRIMARY KEY při tvorbě tabulky neuvede, vytvoří se automaticky jako sloupec ROWID

5

Vypište primární klíč tabulky skupina příkazem SELECT rowid,* FROM skupina;

sqlite3

• primární klíč se definuje v rámci CREATE TABLE za typem sloupce pomocí řetězce PRIMARY KEY - diagram
• např. CREATE TABLE tbl ( id INTEGER PRIMARY KEY, a TEXT );
• většinou se používá se s typem INTEGER, často má sloupec jméno id
• PRIMARY KEY může být v tabulce jen jeden
• hodnoty musí být jednoznačné (nesmí se opakovat)  a musí existovat (NOT NULL), hodnota v poli se nesmí měnit
• pokud se při insertu hodnota nezadá, generuje se automaticky
• pro sloupec označený jako primary key se automaticky vytváří index
• pozn. PRIMARY KEY lze definovat i pro více sloupců najednou - např. CREATE TABLE rooms ( room_number INTEGER NOT NULL, building_number INTEGER NOT NULL, PRIMARY KEY( room_number, building_number ));

6	Úkolem je vytvořit tabulku seznam, která bude obsahovat sloupec id typu INTEGER PRIMARY KEY, dále 3 stejné sloupce jako tabulka skupina + nakonec nový sloupec vytvoreno s časovým razítkem
7	Nastavte v sqlite3 výstup do souboru skupina.sql	.output ...
8	Proveďte export tabulky skupina příkazem .dump do souboru skupina.sql a nastavte zpátky standardní výstup stdout	.dump skupina
9	Editujte skupina.sql v PSPadu (všimněte si zvýraznění syntaxe) - odstraňte všechny příkazy kromě CREATE TABLE a změňte název tabulky na seznam	PSPad
10	Jako první sloupec přidejte id INTEGER PRIMARY KEY	PSPad

6.6.3. Speciální vlastnosti sloupců - DEFAULT

• diagram, diagram_sloupce, diagram_create
• DEFAULT hodnota - automaticky přiřazovaná hodnota (pokud není při ukládání uvedena jiná hodnota)
  • pokud nemá pole hodnotu DEFAULT a není při INSERTu uvedena jiná hodnota, pak má pole hodnotu NULL,
  • časová razítka - musí být psané velkými písmeny:
    • DEFAULT CURRENT_TIME - vkládá akt. čas HH:MM:SS
    • DEFAULT CURRENT_DATE - YYYY-MM-DD
    • DEFAULT CURRENT_TIMESTAMP - YYYY-MM-DD HH:MM:SS

11	Jako poslední sloupec přidejte vytvoreno TEXT DEFAULT CURRENT_TIMESTAMP a uložte dokument pod jménem seznam.sql	PSPad
12	Proveďte příkazy ze souboru seznam.sql pomocí příkazu .read	sqlite3
13	Ověřte vytvoření tabulky příkazem .schema seznam	sqlite3

6.6.4. Insert - vkládání nových řádků do tabulky

• diagram
• INSERT INTO tabulka (sloupec1, ...) VALUES (hodnota, ...);
• např. INSERT INTO parts ( name, stock, status ) VALUES ( 'Widget', 17, 'IN STOCK' );
• hodnoty typu text se uzavírají do apostrofů
• příkaz se volá opakovaně pro každý vkládaný řádek
• počet sloupců i hodnot musí být stejný, pořadí hodnot musí odpovídat sloupcům
• sloupce PRIMARY KEY a DEFAULT se nemusí uvádět - dostanou hodnoty automaticky
• ostatní neuvedená pole dostanou hodnotu NULL - hodnota NULL neznamená prázdný řetězec u textových polí nebo nulu u číselných

14	Editujte skupina.sql v PSPadu - odstraňte všechny příkazy kromě insert	PSPad
15	Změňte v řádcích INSERT název tabulky na seznam podle vzoru: INSERT INTO "seznam" (jmeno,rok,prezdivka) VALUES('Karel',1992,'Karlos');	PSPad
16	Přidejte nový řádek s vlastními hodnotami	PSPad
17	Nastavte kódování UTF-8 a uložte soubor jako seznam-data.sql	PSPad
18	Vložte řádky ze seznam-data.sql do tabulky příkazem .read	sqlite3
19	Zapněte si vypisování hlavičky příkazem .header on a sloupce příkazem .mode column	sqlite3
20	Ověřte příkazem select ... řádky v tabulce seznam	sqlite3
21	Všimněte si, že jsme nemuseli vkládat sloupce id s primárním klíčem a vytvoreno a časovým razítkem - vygenerovaly se automaticky
22	Pro porovnání rowid a id proveďte SELECT rowid,* FROM skupina;

6.6.5. Další speciální vlastnosti sloupců (constraints)

• diagram, diagram_sloupce, diagram_create
• DEFAULT - viz výše

• CHECK - kontrola hodnot podle podmínek (nesplnění podmínky vyvolá při vkládání chybu), pokud CHECK nestačí, používají se TRIGGERy
• kontrola rodného čísla

23	Úkolem je do tabulky seznam přidat sloupce: a) telefon s kontrolou na 9 číslic b) rodne_cislo s kontrolou na sedmý znak = '/'
24	a) alter table ... add column ... integer check (length(telefon)=...);
25	Zkuste vložit do tabulky nový řádek s vlastními hodnotami podle vzoru (analyzujte příčinu chyby): INSERT INTO "seznam" (jmeno,rok,prezdivka,telefon) VALUES('xxx',1111,'yyy',123456);
26	Vkládaný řádek opravte tak, aby se do tabulky vložil bez chyby (proveďte vložení a ověření výpisem řádků)
27	b) podobně přidejte sloupec rodne_cislo s podmínkou substr(rodne_cislo,7,1)='/'
28	Zkuste vložit do tabulky nový řádek s vlastními hodnotami podle vzoru (analyzujte příčinu chyby): INSERT INTO "seznam" (jmeno,rok,prezdivka,telefon,rodne_cislo) VALUES('xxx',1111,'yyy',987654321,'750804-4566');
29	Vkládaný řádek opravte tak, aby se do tabulky vložil bez chyby (proveďte vložení a ověření výpisem řádků)

• UNIQUE - jedinečnost
  • hodnota se může ve sloupci vyskytovat jen jednou (pokus o uložení duplicitní - stejné - hodnoty vyvolá chybu)
  • pro pole se vytvoří automaticky index,
  • může být definováno i pro více sloupců najednou - create table contacts (name text, phone text, unique (name,phone));
• NOT NULL - pole musí obsahovat hodnotu (pokus o uložení záznamu bez hodnoty vyvolá chybu)

30	V databázi db03.sqlite samostatně vytvořte tabulku kody se sloupci a) id integer primary key b) kod integer unique not null c) poznamka text	sqlite3
31	Vložte do tabulky kody nový řádek s vlastními hodnotami podle vzoru INSERT INTO ... (kod,poznamka) VALUES (123,'xxx');
32	Zkuste vložit do tabulky znovu stejný řádek a analyzujte příčinu chyby
33	Vložte do tabulky kody nový řádek s vlastní hodnotou poznámky podle vzoru INSERT INTO ... (poznamka) VALUES ('xxx');
34	Analyzujte chybu a vložte řádek znovu s opravenou hodnotou (tabulka bude mít poté dva řádky - ověřte výpisem))

6.6.6. Update - změna hodnot existujícího řádku

• diagram
• UPDATE table_name SET column_name=new_value [, ...] WHERE výraz;
• výraz určuje výběr řádků pro změnu - využívá se hodnota pole typu PRIMARY KEY nebo UNIQUE
• např. UPDATE parts SET price = 4.25, stock = 75 WHERE part_id = 454;
• 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ý)
• pokud se WHERE neuvede, změní se všechny řádky tabulky
• při chybě ve výrazu se žádné změny neprovedou

35	Vložte do tabulky kody nový řádek s vlastními hodnotami kódu a poznámky	sqlite3
36	Změňte u všech řádků obsah sloupce poznamka na 'upraveno updatem' příkazem: UPDATE kody SET poznamka = ...;
37	U prvního řádku (id=1) změňte kod na hodnotu 159 příkazem: UPDATE kody SET kod = ... WHERE ...;

6.6.7. Delete - mazání řádků tabulky

• diagram
• DELETE FROM table; -- smazání všech řádků tabulky
• DELETE FROM table_name WHERE expression;
• např. DELETE FROM parts WHERE part_id = 385;
• např. DELETE FROM parts WHERE part_id >= 43 AND part_id <= 246;

38	Smažte druhý řádek tabulky y - podmínku doplňte sami: DELETE FROM ... WHERE ...;
39	Ukončete sqlite3 a zkopírujte na fileserver celou složku c:\temp\db03