Databázové systémy 02

1. Databázové systémy 02 Příkazy SQL a příklady

2. Základní vlastnosti SQL • Structured Query Language • Vytvoření databáze a struktury relací • ISO SQL standard obsahuje dvě základní komponenty • DDL (Data Definition Language ) • DML (Data Modification Language ) • Neprocedurální jazyk • užití: administrátor (DBA), management, vývojář aplikace, koncový uživatel

3. Základní vlastnosti SQL • jazyk pro definici dat, • interaktivní jazyk pro manipulaci dat, • jazyk pro manipulaci dat v hostitelské verzi, • možnost definice pohledů, • možnost definice přístupových práv, • možnost definice integritních omezení • (v praxi používaném standardu, tj. v SQL89, jde zatím oproti původnímu Sequelu pouze o omezené možnosti), • řízení transakcí • (příkazy pro specifikaci začátku a konce transakce; SAA-SQL umožňuje i explicitní uzamykání dat pro paralelní přístup k datům).

4. DDL CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE totéž pro VIEW CREATE INDEX, DROP INDEX SET TRANSACTION GRANT, REVOKE ADD, AND, BETWEEN, CHECK, IN COLUMN, TABLE CHAR, DATE, INDEX, INTEGER, LOGICAL, NOT, NULL, REAL, UNIQUE DML SELECT INSERT UPDATE DELETE Základní příkazy SQL

5. Jak psát příkazy • Každý příkaz musí začínat na nové řádce. • Příkaz SQL rozlišuje rezervovaná slova a uživatelem definovaná slova (názvy objektů). • Rezervovaná slova nesmí být dělená do dvou řádků. • Příkaz SQL bývá dost často ukončen, a to středníkem „;“ • SQL není case sensitive, jen hodnoty textového argumentu musí být napsány přesně.

6. DDL • Datové typy ISO SQL – tabulky, pohledy, jména, sloupce • znaková sada: A … Z, a … z, 0 … 9 a „_“, • identifikátor max. 128 znaků, • identifikátor musí začínat písmenem, • identifikátor nesmí obsahovat mezeru

7. Datový typ

8. Datové typy • INTEGER celé číslo se znaménkem ve 4 bytech, • SMALLINT celé číslo se znaménkem ve 2 bytech, • DECIMAL(p,q) pakované dekadické číslo s p ciframi, (p <=15), a předpokládanou desetinnou čárkou q cifer zprava (0=< q < p). Je-li q = 0, vynechává se. • FLOAT reálné číslo se znaménkem v 8 bytech (mantisa je tvořena 14 hexadecimálními číslicemi, obecně je typ parametrizován požadovanou přesností), • CHAR(n) řetězec znaků délky n, • VARCHAR(n) řetězec znaků maximální délky n.

9. Datové typy • Ve standardu jsou ještě NUMERIC, REAL, DOUBLE PRECISION. Kromě těchto typů dat se používají i další (nestandardní), jako např. MONEY, DATE apod. • Budeme-li používat konstanty typu DATE, budou ve tvaru mm/dd/rr. Řetězce znaků i konstanty typu DATE v závorkách '´ . • Pomocí NOT NULL se indikuje neprázdnost hodnoty definovaného atributu pro každou n-tici dané relace. • NULL indikuje hodnoty prázdné (skutečné nuly či mezery nejsou prázdné hodnoty). Interpretují se jako neznámé nebo nedefinované hodnoty. • Specifikace NOT NULL je zvlášť vhodná pro simulaci primárního klíče, který v některých implementacích není přímo podporován. • Jednoznačné hodnoty atributu lze definovat kvalifikací jeho datového typu označovanou UNIQUE.

10. Vytváření databáze • CREATE DATABASE jméno_databáze [ WITH [ LOCATION = { ‚jméno_složky' | DEFAULT } ] [ šablona = šablona | DEFAULT ] [ ENCODING = kódovací_jméno | kódovací_číslo | DEFAULT ] ] • Výsledek (možné chyby): • ERROR: uživatel nemá práva vytvořit novou databázi • ERROR: Absolutní cesta není dovolena • ERROR: Relativní cesta není dovolena. • ERROR: databáze s vybraným jménem už existuje • Příklady: • CREATE DATABASE SKOLA;

11. Vymazání databáze • Vymaže DB ze systému • Synopsis: • DROP DATABASE jméno_databáze • Popis: • Použijte příkaz DROP DATABASE k vymazání DB ze systému. Data jsou vymazány spolu s databází. • Poznámka: Pouze majitel/administrátor může vymazat DB • Příklady: • DROP DATABASE testdb;

12. Vymazání databáze • Výsledek (možné chyby): • ERROR: uživatel nemá právo vymazat DB • ERROR: nemůže být vymazána DB, která je šablona • ERROR: nelze vymazat otevřenou DB. • ERROR: jméno_databáze neexistuje • ERROR: nejste majitelem DB • ERROR: nemůže být vyvoláno z transakčního bloku.

13. Definice tabulky CREATE TABLE jméno_tabulky( {název_sloupcedatový_typ [NOT NULL ] [ UNIQUE ] [ DEFAULT hodnota] [ CHECK (výběrová_podmínka )} [ PRIMARY KEY (seznam_sloupců), ] { [ FOREIGN KEY ( seznam_sloupců_tvořící_cizí_klíč) REFERENCES název_nadřazené_tabulky[( primární_klic )], [ MATCH { PARTIAL | FULL }] [ ON UPDATE referenční akce ] [ ON DELETE referenční akce ] ] [ , … ] } { [ CHECK (výběrová_podmínka ) [ , …] } )

14. Vytváření tabulek CREATE TABLE ctenari( cislo int(3) not null primary key, jmeno varchar(20), adresa varchar(20)); CREATE TABLE knihy( Id_knihy int(3) not null, nazev varchar(20), autor varchar(20), primary key (id_knihy)); CREATE TABLE vypujcky( Id_vyp int(3) not null primary key, datum date, Id_ctenare int(3), Id_knihy int(3), foreign key(id_knihy) reference knihy(id_knihy));

15. Změna tabulky ALTER TABLE table [ * ] ADD | MODIFY [ COLUMN ] columntype ALTER TABLE table [ * ] ALTER [ COLUMN ] column { SET DEFAULT defaultvalue | DROP DEFAULT } ALTER TABLE table [ * ] RENAME [ COLUMN ] column TO newcolumn ALTER TABLE table RENAME TO newtable ALTER TABLE table ADD CONSTRAINTnewconstraint def. ALTER TABLE table OWNER TO newowner

16. Změna tabulky ALTER TABLE knihy ADD nakladatelství varchar(20); ALTER TABLE knihy MODIFY nakladatelství varchar(20); ALTER TABLE knihy DROP COLUMN id_knihy); ALTER TABLE knihy ALTER rok TO rok_vydani; ALTER TABLE vypujcky ADD CONSTRAINT FOREIGN KEY(id_knihy) REFERENCES knihy(id_knihy); ALTER TABLE knihy ADD CONSTRAINT PRIMARY KEY(id_knihy);

17. Vložit data do tabulky • Vložit nový řádek do tabulky. • INSERT INTO tabulka [ ( sloupec [, ...] ) ] { DEFAULT VALUES | VALUES ( hodnota [, ...] ) | dotaz } • Příklady: • INSERT INTO employeesVALUES (106, 'Hall', 'Timothy'); • INSERT INTO employees (id, last_name)VALUES (108, 'Williams'); • INSERT INTO book_backupSELECT * FROM books;

18. Příkaz SELECT SELECT [ DISTINCT | ALL ] { * | [Sloupec_jméno[AS nový_název] ] [ , …] } FROM název_tabulky [alias] [ , … ] [ WHERE podmínka] [GROUP BY seznam_sloupců] [ HAVING podmínka] [ORDER BY seznam_sloupců ]

19. Získání celé tabulky SELECT jmeno, prijmeni, adresa, rodne_cislo FROM Ctenari; SELECT * FROM Ctenari;

20. Projekce SELECT Nakladatelstvi FROM Knihy; SELECT DISTINCT Nakladatelstvi FROM Knihy;

21. Vypočítaná položka SELECT Isbn, Inv_cislo, D_nákupu, Cena, Cena/1.05 AS bez_DPH, Zeme_vydani FROM Knihy;

22. Přejmenování SELECT id AS id_zam, prijmeni, jmeno FROM zamestnanci;

23. Selekce SELECT * FROM Knihy WHERE Zeme_vydani = ‘GB‘; = , != nebo <>, < , <= , > , >= , AND, OR , NOT SELECT * FROM Knihy WHERE Zeme_vydani = ‘GB‘ OR Zeme_vydani = ‘USA‘;

24. Selekce výčet hodnot: SELECT * FROM Knihy WHERE Zeme_vydani IN (‘GB‘ , ‘USA‘ ); Interval: SELECT * FROM Knihy WHERE Cena BETWEEN 300 AND 900;

25. Výběr podle vzoru SELECT * FROM Knihy WHERE Zeme_vydani LIKE ‘G_‘; SELECT * FROM Knihy WHERE Zeme_vydani LIKE ‘G%‘; SELECT * FROM Knihy WHERE Zeme_vydani LIKE ‘U_ _‘ ; SELECT * FROM Knihy WHERE Zeme_vydani LIKE ‘%A‘ ; SELECT * FROM Knihy WHERE Zeme_vydani LIKE ‘%A%‘; SELECT * FROM Knihy WHERE Zeme_vydani NOT LIKE ‘G%‘;

26. Test prázdné hodnoty SELECT * FROM Knihy WHERE Zeme_vydani IS NULL; SELECT * FROM Knihy WHERE Zeme_vydani IS NOT NULL;

27. Řazení SELECT * FROM Knihy ORDER BY Cena; SELECT * FROM Knihy ORDER BY Cena Desc; SELECT * FROM Knihy ORDER BY Zeme_vydani, Cena;

28. Agregované funkce COUNT počet hodnot ve sloupci (počet řádků) SUM součet hodnot ve sloupci AVG průměr hodnot z daného sloupce MIN minimální hodnota MAX maximální hodnota SELECT COUNT(*) AS z_GB FROM Knihy WHERE Země_vydani = ‘GB’; SELECT COUNT( DISTINCT Zeme_vydani) AS ruznych_zemí FROM Knihy; SELECT SUM(Cena) AS za_kolik_z_GB FROM Knihy WHERE Zeme_vydani = ‘GB’;

29. Vnořený select Najdi čísla čtenářů, kteří bydlí v Plzni a mají vypůjčenou nějakou knihu. SELECT id_ctenare FROM Ctenari WHERE adresa LIKE ’%Plzeň%’ AND id_ctenare IN (SELECT id_cten FROM Vypujcka);

30. Vnořený select Najdi jména čtenářů, kteří mají rezervovanou knihu Babička. SELECT Jméno FROM Ctenari WHERE id_cten IN (SELECT id_cten FROM Rezervace WHERE ISBN = (SELECT ISBN FROM Knihy WHERE Titul = ’Babička’)); Poznámka: máme zaručeno, že existuje právě jeden záznam Babičky s ISBN

31. Grupování SELECT COUNT(Inv_č), SUM(Cena) AS celkem, Země_vydání FROM Exemplář GROUP BY Země_vydání ORDER BY Země_vydání SELECT COUNT(Inv_č), SUM(Cena) AS celkem, Země_vydání FROM Exemplář GROUP BY Země_vydání HAVING COUNT(Inv_č) > 2 ORDER BY Země_vydání

32. Agregované funkce vnořeného selectu Vypiš zaměstnance, kteří mají nadprůměrný plat a uveď o kolik SELECT císlo_zam, Jméno, Funkce, Plat – (SELECT AVG(Plat) FROM Zaměstnanci) AS nad_průměr FROM Zaměstnanci WHERE plat > (SELECT AVG(Plat) FROM Zaměstnanci); ORDER BY nesmí být ve vnořeném příkazu SELECT použito, musí obsahovat jméno jednoho sloupce nebo výraz (vyjma EXISTS), jméno sloupce se vztahuje k tabulce uvedené ve vnořeném příkazu SELECT, jestliže je vnořený select jedním ze dvou operandů, musí být uveden na pravé straně.

33. Sjednocení UNION – sjednocení dvou tabulek se stejnými sloupci Najdi čísla čtenářů, kteří mají vypůjčené nebo rezervované knihy do 31.12.2003 Id_ctenare SELECT id_ctenare FROM Vypujcka WHERE D_zpet < ’31.12.2003’ UNION SELECT id_ctenare FROM Rezervace WHERE D_rez< ’31.12.2003’; INTERSECT - průnik EXCEPT - rozdíl

34. Spojení - SELECT přes více tabulek 1:N Najdi čísla čtenářů a knihy, které mají vypůjčeny. SELECT a.id_ctenare , Inv_cislo FROM Ctenari a, Vypujcka b WHERE a.id_ctenare = b.id_ctenare; Poznámka: - FROM Čtenář a JOIN Výpůjčka b ON a.Č_čt = B.Č_čt; - FROM Čtenář JOIN Výpůjčka USING Č_čt¨; - FROM Čtenář NATURAL JOIN Výpůjčka - nepoužijeme-li WHERE, získáme kartézský součin, totéž lze SELECT ….. FROM Čtenář CROSS JOIN Výpůjčka (formálně ano, logicky nesmysl)

35. Spojení - SELECT přes více tabulek 1:N Najdi čísla čtenářů a názvy knihy, které mají rezervovány. SELECT a.id_ctenare , Nazev FROM Ctenari a, Rezervace b, Knihy c WHERE a.id_ctenare = b.id_ctenare AND b.ISBN = c.ISBN

36. LEFT, RIGHT a FULL spojení Zahrnuje i neporovnatelné řádky ze spojení SELECT a.* , b.* FROM Čtenář a LEFT JOIN Výpůjčka b ON a.Č_čt = b.Č_čt; LEFT JOIN poskytne nejen ty čtenáře, ke kterým byla nalezená výpůjčka, a to každého tolikrát, kolik výpůjček mají, ale i ty čtenáře, kteří výpůjčku nemají. Hodnoty z Výpůjčky jsou NULL. Obecně: LEFT JOINT připojí i ty řádky, které z levé (první) tabulky nemají v pravé (druhé) relaci (tabulce) odpovídající prvek (řádku). • RIGHT JOIN – připojuje neporovnané řádky z pravé (druhé) tabulky • - FULL JOIN – připojuje neporovnané řádky jak z první tak ze druhé tabulky

37. LEFT, RIGHT a FULL spojení Zahrnuje i neporovnatelné řádky ze spojení SELECT a.* , b.* FROM Ctenare a LEFT JOIN Vypujcka b ON a.id_ctenare = b.id_ctenare; LEFT JOIN poskytne nejen ty čtenáře, ke kterým byla nalezene výpůjčka, a to každého tolikrát, kolik výpůjček mají, ale i ty čtenáře, kteří výpůjčku nemají. Hodnoty z Výpůjčky jsou NULL.

38. Test existence (EXISTS a NOT EXISTS) Výsledkem je hodnota true tehdy a jen tehdy, je-li v tabulce poddotazu alespoň jedna řádka. Jinak je hodnota false. Najdi jména čtenářů, kteří mají rezervovanou nějakou knihu, SELECT Jmeno FROM Ctenari WHERE EXISTS ( SELECT * FROM Rezervace WHERE id_ctenare = Ctenari.id_ctenare)

39. Vložení prvku relace (řádky) INSERT INTO název_tabulky [(seznam_sloupců)] VALUES (seznam_dat) Hodnoty se uzavírají do apostrofů, pouze číselné hodnoty ne. Hodnoty se oddělují čárkou. INSERT INTO Ctenari ( id_ctenare, Jmeno) VALUES (‘123’,’Novák Petr’);

40. Vložení prvku relace (řádky) INSERT ukládá výsledek dotazu do tabulky. Vytvořme relaci obsahující čtenáře a počet vypůjčených knih. CREATE TABLE Statistika (id_ctenare char (4), Pocet SMALLINT); INSERT INTO Statistika SELECT id_ctenare, COUNT(Inv_cislo) FROM Výpůjčka GROUP BY id_ctenare;

41. Zrušení prvku relace (řádky) DELETE FROM název_tabulky WHERE podmínka DELETE FROM Kniha WHERE Autor = ‘B.Němcová’; zrušení všech řádek DELETE FROM Kniha

42. Změna prvku relace (řádky) UPDATE název_tabulky SET název_sloupce = hodnota_dat, [název_sloupce_2 = hodnota_dat] [WHERE podmínka] UPDATE Ctenari SET Adresa= ‘Plzeň, Univerzitní 8’ WHERE id_ctenare = ‘123’

43. Výraz relační algebry x příkaz SELECT Č_stud Předmět N 1 Student Známka Studijní_obor Známka Vypiš jména všech studentů studijního oboru počítačové sítě, kteří mají z předmětu DBS jedničku. ((Student (studijní_obor = ‘AVT’) * (Známka (Předmět = ‘DBS’ AND Známka = 1)) [Jméno] SELECT Jméno FROM Student a, Známka b WHERE Studijní_obor = ‘AVT’ AND a.id_cten = b.id_stud AND Predmet = ‘DBS’ AND Známka = 1

44. Deklarativní realizace integritních omezení ANSI SQL-92 (též SQL2) - definice cizího klíče doplněna ON DELETE CASCADE ON DELETE SET NULL ON UPDATE CASCADE ON UPDATE SET NULL nová syntaxe CHECK– možnost vložení příkazu SELECT Př.: Vedoucím katedry může být jen profesor nebo docent. Informace o titulu je uložena v tabulce Učitelé. CREATE TABLE katedry ( … vedoucí CHAR (8) CHECK( EXISTS SELECT * FROM Ucitele WHERE vedouci = ucitele.cis AND titul IN (‘Prof’, ‘Doc’ )) … )

45. Referenční IO cislo_ucitele Zkratka N 1 Ucitele Predmety Garant 1 1 N N Zkratka Vyuka Garant CREATE TABLE Vyuka ( … FOREIGN KEY ( Garant) REFERENCES Ucitele (cislo_ucitele) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY ( Zkratka) REFERENCES Predmety ON UPDATE CASCADE ON DELETE CASCADE …. ) CREATE TABLE Predmety (… FOREIGN KEY ( Garant) REFERENCES Ucitele (cislo_ucitele) ON UPDATE CASCADE ON DELETE NULL … )

46. Pohledy CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] • Dynamická struktura • Výsledek jedné nebo více relačních operací • Vznik nové relace, která je virtuální, v databázi neexistuje, je uložen pouze její popis.

47. Pohledy • CREATE VIEW lidé_bez_rc AS SELECT id, jméno, příjmení, vzdělání FROM lidé; • Uživatelé budou pracovat s pohledem LIDÉ_BEZ_RC, jako by to byla normální tabulka. Takže pokud bude chtít uživatel zobrazit seznam všech VŠ vzdělaných lidí, zapíše následující dotaz: • SELECT jméno, příjmeníFROM lidé_bez_rcWHERE vzdělání LIKE 'VŠ‚;

48. Aktualizace pohledu Syntaxe změny pohledu bez vymazání: CREATE OR REPLACE VIEW pohled_name ASSELECT sloupceFROM tabulkaWHERE podminka; CREATE OR REPLACE VIEW lidé_bez_rc AS SELECT id, jméno, příjmení, vzděláníFROM lidéWHERE vzdělání LIKE 'VOŠ';

49. Zrušení pohledu Syntaxe příkazu pro zrušení pohledu: DROP VIEW pohled_name; DROP VIEW lide_bez_rc;

50. Alter group • Přidat nebo odebrat uživatele ze skupiny • Synopsis: • ALTER GROUP jméno ADD USER uživatel [, ... ] • ALTER GROUP jméno DROP USER uživatel [, ... ] • Uživatel - více jmen oddělujeme čárkou. • Příklad: • ALTER GROUP sales ADD USER jessica, william; • ALTER GROUP sales DROP USER jessica;