SQL

1. SQL Přednáška DB1

2. Literatura • CONNOLLY, T.M.-BEGG,C.E.-STRACHAN,A.D.: Database Systems – A Practial Approach to Design, Implementation and Management. University of Paisley, Addison-Wesley Publ. Comp., ISBN 0-201-42277-8, 1995. • POKORNÝ,J.: Počítačové databáze. Výběr informací z organizační a výpočetní techniky, Kancelářské stroje, Praha, 1991.

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

4. Základní příkazy SQL • DDL • CREATE TABLE, ALTER TABLE, DROP TABLE • totéž pro VIEW • CREATE INDEX, DROP INDEX • SET TRANSACTION • GRANT, REVOKE • DML • SELECT • INSERT • UPDATE • DELETE

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. Příkaz SELECT SELECT [ DISTINCT | ALL ] { * | [ sloupcový_výraz [AS nový_název] ] [ , …] } FROM název_tabulky [alias] [ , … ] [ WHERE podmínka ] [GROUP BY seznam_sloupců] [ HAVING podmínka ] [ORDER BY seznam_sloupců] Sloupcový výraz - název sloupce nebo výraz

7. Získání celé tabulky SELECT Isbn, Inv_č, D_nákupu, Cena, Země_vydání FROM Exemplář; SELECT * FROM Exemplář;

8. Projekce SELECT Země_vydání FROM Exemplář; SELECTDISTINCT Země_vydání FROM Exemplář;

9. Vypočítaná položka SELECT Isbn, Inv_č, D_nákupu, Cena, Cena/1.05 AS bez_DPH, Země_vydání FROM Exemplář;

10. Selekce SELECT * FROM ExemplářWHERE Země_vydání = ‘GB‘; = , != nebo <>, < , <= , > , >= , AND, OR , NOT SELECT * FROM Exemplář WHERE Země_vydání = ‘GB‘ OR Země_vydání = ‘USA‘; výčet hodnot SELECT * FROM Exemplář WHERE Země_vydání IN (‘GB‘ , ‘USA‘ ); interval SELECT * FROM Exemplář WHERE Cena BETWEEN 300 AND 900;

11. Výběr podle vzoru SELECT * FROM ExemplářWHERE Země_vydání LIKE‘G%‘; SELECT * FROM ExemplářWHERE Země_vydání LIKE‘G_‘; SELECT * FROM ExemplářWHERE Země_vydání LIKE‘U_ _‘ ; SELECT * FROM ExemplářWHERE Země_vydání LIKE‘%A‘ ; SELECT * FROM ExemplářWHERE Země_vydání LIKE‘%A%‘; SELECT * FROM ExemplářWHERE Země_vydání NOT LIKE‘G%‘;

12. Test prázdné hodnoty SELECT * FROM ExemplářWHERE Země_vydání IS NULL; SELECT * FROM ExemplářWHERE Země_vydání IS NOT NULL;

13. Řazení SELECT * FROM Exemplář ORDER BY Cena; SELECT * FROM ExemplářORDER BY Cena Desc; SELECT * FROM ExemplářORDER BY Země_vydání, Cena

14. 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 SELECTCOUNT(*) AS z_GBFROM Exemplář WHERE Země_vydání = ‘GB’ SELECTCOUNT( DISTINCT Země_vydání) AS různých_zemí FROM Exemplář SELECTSUM(Cena) AS za_kolik_z_GBFROM Exemplář WHERE Země_vydání = ‘GB’

15. Vnořený select Najdi čísla čtenářů, kteří bydlí v Plzni a mají vypůjčenou nějakou knihu. SELECT Č_čt FROM Čtenář WHERE adresa LIKE ’%Plzeň%’ ANDČ_čt IN (SELECT Č_čt FROM Výpůjčka); Najdi jména čtenářů, kteří mají rezervovanou knihu Babička. SELECT Jméno FROM Čtenář WHERE Č_čt IN (SELECT Č_čt FROM Rezervace WHERE ISBN = (SELECT ISBN FROM KNIHA WHERE Titul = ’Babička’)); Poznámka: máme zaručeno, že existuje právě jeden záznam Babičky s ISBN

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

17. Agregované funkce ve vnořeném selektu Vypiš zaměstnance, kteří mají nadprůměrný plat a uveď o kolik SELECT Číslo_zam, Jméno, Funkce, Plat – (SELECTAVG(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 selektu použito, • vnořený SELECT musí obsahovat jméno jednoho sloupce nebo výraz (vyjma EXISTS), • jméno sloupce se vztahuje k tabulce uvedené ve vnořeném selektu, • jestliže je vnořený selekt jedním ze dvou operandů, musí být uveden na pravé straně.

18. Sjednocení UNION – sjednocení dvou tabulek se stejnými sloupci Najdi čísla čtenářů, kteří mají vypůjčené nebo rezervované knihy do 31.12.2003 Č_čt SELECT Č_čt FROM Výpůjčka WHERE D_zpět < ’31.12.2003’ UNION SELECT Č_čt FROMRezervace WHERE D_rez< ’31.12.2003’; INTERSECT - průnik EXCEPT - rozdíl

19. Spojení - SELECT přes více tabulek 1:N Najdi čísla čtenářů a knihy, které mají vypůjčeny. SELECT a.Č_čt , Inv_č FROM Čtenář a, Výpůjčka b WHERE a.Č_čt = b.Č_čt; 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) Najdi čísla čtenářů a názvy knihy, které mají rezervovány. SELECT a.Č_čt , Název FROM Čtenář a, Rezervace b, Kniha c WHERE a.Č_čt = b.Č_čt AND b.ISBN = c.ISBN

20. SELECT – FROM - WHERE je v praxi základní konstrukcí jazyka SQL

21. 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 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. 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

22. 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 Jméno FROM Čtenář WHERE EXISTS ( SELECT * FROM Rezervace WHERE Č_čt = Čtenář.Č_čt)

23. 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 Čtenář ( Č_čt, Jméno) VALUES (‘123’,’Novák Petr’); 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 (Č-čt char (4), Počet SMALLINT); INSERT INTO Statistika SELECT Č_čt, COUNT(Inv_č) FROM Výpůjčka GROUP BY Č_čt;

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

25. 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 Čtenář SET Adresa= ‘Plzeň, Univerzitní 8’ WHERE Č_čt = ‘123’

26. 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 DB1 jedničku. ((Student (studijní_obor = ‘počítačové sítě’) * (Známka (Předmět = ‘DB1’ AND Známka = 1)) [Jméno] SELECT Jméno FROM Student a, Známka b WHERE Studijní_obor = ‘počítačové sítě’ AND a.Č_stud = b.Č_stud AND Předmět = ‘DB1’AND Známka = 1

27. 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

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

29. Datový typ

30. Výběrová podmínka - příklady den_v_týdnu CHAR (2) NOT NULL CHECK ( den_v_týdnuIN ( ‘Po’, ‘Út’, ‘St’, ‘Čt’, ‘Pá’, ‘So’, ‘Ne’)) plat INTCHECK ( plat > 0 ) , odmena INT CHECK ( odmena < 0.5 * plat ) pocet_kreditu SMALLINT CHECK ( pocet_kreditu BETWEEN 1 AND 6 ) Podle ANSI SQL89 mohl být odkaz jen na položky definované v tabulce. Podle ANSI SQL92 (též SQL2) může být odkaz i na jinou tabulku – pomocí konsturkce SELECT.

31. CREATE TABLE Předmět ( …. FOREIGN KEY ( garant ) REFERENCES ucitele (cislo_ucitele) …. ) Deklarativní realizace integritních omezení ANSI SQL-86 pouze NOT NULL a UNIQUE ANSI SQL-89 PRIMARY KEY CHECK (viz „výběrová kriteria“) FOREIGN KEY a REFERENCES cislo_ucitele Předmět N 1 Ucitele Predmet Garant

32. Deklarativní realizace integritních omezenípokr. ANSI SQL-92 (též SQL2) definice cizího klíče doplněna ON DELETE CASCADE ON UPDATE CASCADE ON DELETE SET NULL 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’ )) … )

33. Referenční IO cislo_ucitele Zkratka N 1 Ucitele Predmet 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 Predmet ON UPDATE CASCADE ON DELETE CASCADE ….) CREATE TABLE Předmět (… FOREIGN KEY ( Garant) REFERENCES Ucitele (cislo_ucitele) ON UPDATE CASCADE ON DELETE NULL … )

34. Pohledy CREATE VIEW jméno_pohledu [ (nové_jméno_sloupce [ , …])] AS subselect [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.