SQL. Pohledy, ochrana dat,... Pavel Tyl

SQL Pohledy, ochrana dat, ...

Pavel Tyl 25. 4. 2013

ANY, ALL, SOME ●

●

SOME == ANY (od SQL92 obě varianty) Př.: Najdi zaměstnance, kteří mají plat větší, než všichni zaměstnanci z Prahy

ALL, ANY, SOME ●

●

SOME == ANY (od SQL92 obě varianty) Př.: Najdi zaměstnance, kteří mají plat větší, než všichni zaměstnanci z Prahy SELECT id, jmeno FROM Zam WHERE plat > ALL ( SELECT Zam.plat FROM Zam WHERE Zam.adresa LIKE '%Praha%' );

IN – Manipulace s množinami ●

p IN A == p je prvkem množiny A

●

Množinu tvoří výčet nebo SELECT-FROM-WHERE

●

Př.:

SELECT * FROM Osoba, Byt WHERE (Osoba.rc=Byt.rc) AND (Byt.ulice IN ('Hálkova', 'Pražská')); SELECT * FROM Osoba, Byt WHERE (Osoba.rc=Byt.rc) AND (Byt.ulice IN ( SELECT * FROM Byt WHERE (ulice LIKE 'H%') ));

Manipulace s daty v SQL ●

●

●

Prázdné hodnoty – rozšíření RMD – agregační funkce aplikované na prázdnou množinu vracejí NULL Zakázáno – spojení přes NULL – dotaz bydliště = NULL Speciální příkaz IS NULL

INSERT – Příkaz vkládání ●

●

Př.: Vlož do tabulky Osoba Jana Nováka INSERT INTO Osoba (jmeno, prijmeni) VALUES ('Jan', 'Novák'); INSERT INTO Osoba SELECT * FROM tbl_name; Pozor – u vkládání pomocí SELECT není VALUES

UPDATE – Příkaz aktualizace ●

Př.: Zaměň všude jméno Honza za jméno Jan UPDATE Osoba SET jmeno='Jan' WHERE jmeno='Honza';

DELETE – Příkaz mazání ●

Př.: Smaž všechny osoby Jan DELETE FROM Osoba WHERE jmeno='Jan';

VIEWS – Definice pohledů ●

●

Pohledy jsou virtuální relace, tzv. pohled na aplikaci uživatelskýma očima CREATE VIEW jmeno_pohledu [(njmeno_atr1[, njmeno_atr2]...)] AS SELECT [DISTINCT] {* jmeno_atr1[, jmeno_atr2]...} FROM jmeno_relace1[, jmeno_relace2]... [WHERE podminka1] [GROUP BY gjmeno_atr1[, gjmeno_atr2]... [HAVING podminka2]] [ENABLE UPDATE]; CREATE VIEW pohled AS SELECT * FROM tabulka WHERE a > 0; DROP VIEW pohled;

Definice pohledů (SQL92) ● ●

●

●

●

Mají logickou datovou nezávislost Výhodou je rychlost, neboť optimalizátor může používat stále stejný optimalizační plán Bezpečnost UPDATE povolen u materializovaných pohledů (updatable views), pouze nad jednou tabulkou INSERT – NULLs, kde není definováno – Co prim. klíč? – WITH CHECK OPTION – kontrola, zda bude vložená n-tice vidět v pohledu

Ochrana dat ● ●

●

Požadavek víceuživatelského SŘBD Přístup k relaci (pohledu), definice práv GRANT {ALL | {DELETE | INSERT | SELECT | UPDATE [(atribut1[, atribut2]...)]}...} ON relace TO {PUBLIC | uzivatel1[, uzivatel2]...} Odebrání práv pomocí REVOKE

Manipulace s daty v SQL ●

Systémový katalog – informace o SŘBD – SYSTABLE – jméno relace, zakladatele, počet atributů, … – SYSCOLUMNS – jméno atributu, jm. relace, … – SYSINDEXES – jméno indexu, jm. relace, … – COMMENT – komentáře

GROUP BY a HAVING ● ●

Konstrukt GROUP BY a HAVING MIN se bude používat až na skupiny vytvořené pomocí GROUP BY SELECT S.rating, MIN(S.age) AS minage FROM Sailors S WHERE S.age>=18 GROUP BY S.rating HAVING COUNT (*)>1 13

GROUP BY a HAVING Sid

sname

rating

age

rating

minage

rating

minage

22

Petr

7

45

7

45

1

33

29

Brutus

1

33

1

33

3

25

31

Karel

8

55

8

55

3

63

32

Andy

8

25

8

25

7

45

58

Kuba

10

35

10

35

7

35

64

Horacio

7

35

7

35

8

55

71

Jana

10

16

9

40

8

25

74

Pepa

9

40

3

25

9

40

85

Michala

3

25

3

25

10

35

95

Karolina

3

63 14

Výsledek příkladu rating

3

7

8

minage

25

35

25

Sid

sname

rating

age

22

Petr

7

45

29

Brutus

1

33

31

Karel

8

55

32

Andy

8

25

58

Kuba

10

35

64

Horacio

7

35

71

Jana

10

16

74

Pepa

9

40

85

Michala

3

25

95

Karolina

3

63 15

Seskupování – Group By SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"

Store_Information store_name Sales Los Angeles $1500 San Diego $250 Los Angeles $300 Boston $700

Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-08-1999 16

Seskupování – Group By Store_Information store_name Los Angeles San Diego Los Angeles Boston

Sales $1500 $250 $300 $700

Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-08-1999

SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name store_name Los Angeles San Diego Boston

SUM(Sales) $1800 $250 $700 17

Doplňující podmínka – Having SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1" HAVING (arithmetic function condition)

Store_Information store_name Sales Los Angeles $1500 San Diego $250 Los Angeles $300 Boston $700

Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-08-1999 18

Doplňující podmínka – Having Store_Information store_name Sales Los Angeles $1500 San Diego $250 Los Angeles $300 Boston $700

Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-08-1999

SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500

store_name Los Angeles

SUM(Sales) $1800

19

SQL Uložené procedury, spouště

Pavel Tyl 25. 4. 2013

Spouště (Triggers) ●

●

●

Trigger (spoušť) je procedura, která je automaticky spouštěna DBMS jako reakce na specifikovanou akci v databázi Trigger je DB objekt spouštěný automaticky a je připojený k tabulce Databáze, která obsahuje definice spouští se nazývá AKTIVNÍ

Popis spouště ●

Spoušť má tři hlavní části: – Event (událost) – změna v DB, která vyvolá spuštění – Condition (podmínka) – dotaz nebo test, který je proveden, pokud je spoušť aktivována – Action (akce) – procedura, která je provedena při spuštění a pokud je splněna podmínka

Event (událost) ●

INSERT

●

UPDATE

●

DELETE

●

●

Nezáleží na tom, který uživatel provede událost Uživatel si není vědom aktivity spouští v DB

Trigger – příklad ● ●

●

Mějme DB studentů (tabulka Student) Mějme tabulku obsahující informace o studentech mladších než 18 let Při vložení nového záznamu do tabulky Student je aktivována spoušť, která zvýší atribut v tabulce obsahující počet studentů mladších 18 let

Spouště – After vs. Before (MySQL) ●

Podle funkce je možné tuto změnu provést před nebo po provedení změn nad tabulkou Student

Trigger – příklad ●

CREATE TRIGGER init_count BEFORE INSERT ON Student /* udalost */ DECLARE count INTEGER; BEGIN count := 0; /* akce */ END

Trigger – příklad ●

CREATE TRIGGER inc_count AFTER INSERT ON Student /* udalost */ WHEN (new.age<18) /* podminka */ FOR EACH ROW BEGIN count := count + 1; /* akce */ END

Hodnoty, se kterými pracuje trigger ●

●

New – označuje nově vkládanou/upravovanou n-tici (řádek) do relace v DB – pokud byla hodnota n-tice v DB měněna, pak se lze pomocí New odkázat na nově vložená data Old – data před změnou

Triggery vs. omezení ● ●

●

Spouště slouží v DB k zajištění konzistence dat Integritní omezení slouží ke stejnému účelu, pro zachování konzistence dat Spouště jsou však aktivovány pouze na určité stimuly

Kdy radši použít trigger? ●

●

●

Mějme relaci Objednavky (ido, pocet, idzakaznik, jednotkovacena) Při založení objednávky jsou první tři atributy vyplněny zákazníkem (nebo prodavačem u pokladny) Atribut jednotkovacena je vyplněn podle jíné relace, relace Produkty

Kdy radši použít trigger? ●

●

●

Atribut jednotkovacena je však nutné vyplnit, aby byla objednávka kompletní! Pokud by nebyla jednotkovacena uvedena, pak by při pozdější změně (např. sleva) byla změněna i ve všech objednávkách již uzavřených Napišme trigger, který tuto z tabulky Produkty hodnotu zjistí a vloží do tabulky Objednávky

Kdy radši použít trigger? ●

●

Je to vhodné vzhledem k ušetření práce prodavače a zároveň to minimalizuje možnost vzniku chyby při vkládání dat a tím i vzniku nekonzistence v databázi Mějme za úkol provést ještě další kontroly – při platbě budeme kontrolovat, zda-li celková cena není vyšší než zůstatek na účtu zákazníka – lze provést pomocí triggeru, ale i CHECK, trigger umožní implementaci i složitějších kontrolních mechanismů

Kdy radši použít trigger? ●

●

Můžeme například povolit zákazníkovi překročit limit jeho účtu max. o 10 %, pokud má u nás záznam déle než 1 rok Dále můžeme požadovat přidání zákazníka do tabulky pro zvýšení limitu platby

Condition (podmínka) ● ●

True/False vyjádření (vek > 18) Dotaz – pokud vrátí neprázdnou množinu jako výsledek, pak odpovídá True, jinak False

SQL Oracle syntaxe ●

CREATE [OR REPLACE] TRIGGER     {BEFORE|AFTER} {INSERT|DELETE| UPDATE} ON     [REFERENCING [NEW AS ] [OLD AS ]]     [FOR EACH ROW [WHEN ()]]    

Trigger granularita ●

●

FOR EACH ROW – provede se tolikrát, kolik je řádek v množině ovlivněných záznamů – Pokud je třeba odkazovat/používat konkrétní řádku/řádky z množiny ovlivněných záznamů, pak použít FOR EACH ROW Př.: pokud chceme porovnávat hodnoty nově vložených a starých záznamů v případě AFTER UPDATE triggeru.

Trigger granularita ●

●

FOR EACH STATEMENT – Provede se jednou pro celý trigger/událost Pokud je množina ovlivněných záznamů prázdná (např. pokud není splněná podmínka pro UPDATE, DELELE či INSERT), pak – FOR EACH ROW trigger se neprovede vůbec – FOR EACH STATEMENT trigger se spustí/provede.

Příklad – FOR EACH ROW ●

CREATE TRIGGER NEW_HIRED AFTER INSERT ON EMPLOYEE FOR EACH ROW UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

Příklad – FOR EACH STATEMENT ●

CREATE TRIGGER NEW_HIRED AFTER INSERT ON EMPLOYEE REFERENCING NEW_TABLE AS NEWEMPS FOR EACH STATEMENT UPDATE COMPANY_STATS SET NBEMP = NBEMP + (SELECT COUNT(*) FROM NEWEMPS)

Aktivace triggeru ● ●

Before, After aktivace provedení spouště Např. Aktivace následující spouště je po (After) provedení operace INSERT nad tabulkou Employee CREATE TRIGGER NEW_HIRE AFTER INSERT ON EMPLOYEE FOR EACH ROW UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

Before trigger ●

●

●

Pokud je jako čas aktivace zvoleno BEFORE, akce spouště jsou aktivovány pro každý řádek ovlivněných záznamů před provedení vlastního dotazu nad databází Z toho vyplývá, že tabulka, nad kterou se provádí dotaz, bude modifikována až po provedení všech operací BEFORE spouště Pozn.: BEFORE spoušť musí mít granularitu FOR EACH ROW

After trigger ●

●

●

Akce spouště jsou aktivovány for each row v množině ovlivněných záznamů nebo pro daný příkaz (záleží na granularitě spouště) Spoušť je aktivována až po provedení všech kontrol integritních omezení, které může spoušť (akce ve spoušti) ovlivnit Pozn. AFTER spouště mohou mít granularitu FOR EACH ROW a FOR EACH STATEMENT

Popis obrázku ●

●

●

Pokud jsou nad tabulkou definovány jak before tak i after spouště, pak se jako první provedou všechny before spouště První spoušť, která je spuštěna vezme jako vstup množinu záznamů, které budou ovlivněný dotazem (UPDATE, INSERT, DELETE) a provede veškeré změny definované v rámci spouště Výstup první before spouště je pak vstupem následující before spouště

Popis obrázku ●

●

●

Jakmile jsou aktivovány a dokončeny všechny Before spouště, jsou všechny změny provedeny na databázových objektech (včetně vlastního dotazu co spoušť aktivoval) Následně jsou aktivovány všechny After spouště asociované s danou akcí After spouště pak mohou modifikovat stejnou/jinou tabulku, mohou také spouštět externí akce (poslat email)

Použití Before triggerů ●

●

Before spouště jsou jakýmsi rozšířením systémových integritních omezení Používají se pro

– provedení validace vstupních dat – automatické generován hodnot pro nově vkládané/modifikované záznamy – čtení záznamů z odkazovaných tabulek pro ověření referencí ●

Before spouště nejsou používány pro další modifikace DB objektů, protože jsou aktivovány před provedení změn vlastním dotazem a jsou tedy jsou aktivovány před kontrolou IO

Použití After triggerů ●

●

After triggery mohou být chápány jako modul aplikační logiky který je proveden jako odezva na určitou událost v DB After triggery vždy pracují s DB, která je v konzistetním stavu.

●

Jsou spouštěny až po kontrole IO

●

Např.:

– spouštění operací jako odezvu na upravující operace v DB – operace mimo databázi, např. spouštění alarmů, externích programů atd. – akce mimo DB nejsou pod kontrolou DB mechanismů pro rollback

Omezení Before triggerů ●

●

Before triggery nemohou obsahovat následující operace v SQL příkazech svého těla: UPDATE

●

DELETE

●

INSERT

Příklad ●

●

●

CREATE TABLE T4 (a INTEGER, b CHAR(10)); CREATE TABLE T5 (c CHAR(10), d INTEGER); Vytvoříme trigger, který vloží záznam do tabulky T5 pokud je vložen záznam do T4. Trigger zkontroluje, zda-li nově vložený záznam má první složku 10 nebo méně a pokud ano, tak vloží reverzní záznam do T5:

Příklad ●

CREATE TRIGGER trig1 After INSERT ON T4 REFERENCING NEW AS newRow FOR EACH ROW WHEN (newRow.a <= 10) BEGIN INSERT INTO T5 VALUES (newRow.b, newRow.a);     END trig1;

Zápis triggeru v MySQL ●

mysql> delimiter // mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account FOR EACH ROW BEGIN IF NEW.amount < 0 THEN SET NEW.amount = 0; ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100; END IF; END; // mysql> delimiter ;

●

CREATE TABLE test1(a1 INT);

●

CREATE TABLE test2(a2 INT);

●

●

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 );

●

●

●

DELIMITER | CREATE TRIGGER testref After INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 (a2) VALUES NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | DELIMITER ;

●

●

INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

●

●

CREATE TRIGGER testref After INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 (a2) VALUES NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

●

SELECT * FROM test1; +------+ | a1 | +------+ | 1 | | 3 | | 1 | | 7 | | 1 | | 8 | | 4 | | 4 | +------+

●

●

CREATE TRIGGER testref After INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 (a2) VALUES NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

●

SELECT * FROM test3; +----+ | a3 | +----+ | 2 | | 5 | | 6 | | 9 | | 10 | +----+

●

●

CREATE TRIGGER testref After INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 (a2) VALUES NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

●

SELECT * FROM test4; +----+------+ | a4 | b4 | +----+------+ | 1 | 3 | | 2 | 0 | | 3 | 1 | | 4 | 2 | | 5 | 0 | | 6 | 0 | | 7 | 1 | | 8 | 1 | | 9 | 0 | | 10 | 0 | +----+------+

Instead of trigger (MSSQL2000+) ● ●

●

V MSSQL neexistují triggery Before V MSSQL jsou dva typy triggerů: – Instead of Trigger – After Trigger Before trigger nahrazujeme pomocí Instead of triggeru, ale tyto triggery si neodpovídají

Příklad Instead of trigger ●

●

CREATE TABLE [dbo].[Employee1] ( [id] CHAR(10) PRIMARY KEY, [name] VARCHAR(50) ) GO INSERT INTO [dbo]. [Employee1]  VALUES('a1', 'Jan') GO

Příklad Instead of trigger ●

●

●

CREATE TABLE [dbo].[Employee1] ( [id] CHAR(10) PRIMARY KEY, [name] VARCHAR(50) ) GO INSERT INTO [dbo]. [Employee1] VALUES ('a1', 'Jan') GO INSERT INTO [dbo]. [Employee1] (name) VALUES('Pavel')

●

--Instead of Trigger CREATE TRIGGER AutoIncrement_Trigger ON [dbo]. [Employee1] Instead of INSERT AS BEGIN DECLARE @ch CHAR DECLARE @num INT SELECT  @num=SUBSTRING(MAX(id),2,1)  FROM [dbo].[Employee1] SELECT  @ch=SUBSTRING(MAX(id),1,1)  FROM [dbo].[Employee1] IF @num=9 BEGIN SET @num=0 SET @ch= CHAR(1 + ASCII(@ch)) END INSERT INTO [dbo].Employee1] (id,name)  SELECT (@ch+CONVERT(VARCHAR(9),@num+1))), inserted.name  FROM inserted END

Příklad Instead of trigger ●

●

●

●

●

INSERT INTO [dbo]. [Employee1] (name) VALUES('Pavel') INSERT INTO [dbo]. [Employee1] (name) VALUES('Milan') INSERT INTO [dbo]. [Employee1] (name) VALUES('Jiří') INSERT INTO [dbo]. [Employee1] (name) VALUES('Alice') SELECT * FROM [dbo].[Employee1]

Uložené procedury (Stored Procedures) ● ●

CREATE PROCEDURE Můžeme definovat dva typy uložených procedur: – externí – tělo procedury je napsané v programovacím jazyce, procedura pak během svého běhu volá externí programy, rutiny – SQL – tělo procedury je napsané v SQL a je definované pouze pro prostředí SQL serveru

Uložené procedury v MySQL ●

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body

Uložené procedury v MySQL ●

proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' routine_body: Valid SQL procedure statement

Parametry uložených procedur ● ●

Parametry jsou defaultně jako IN Pokud chceme specifikovat parametr jinak musíme použít klíčová slova OUT nebo INOUT před jménem parametru

Return ●

Return umožňuje definovat návratový typ procedury

routine_body ●

●

routine_body (tělo procedury) obsahuje validní SQL Můžeme použít jak jednoduchý SELECT nebo INSERT, tak i složený příkaz uzavřený do BEGIN a END

Příklady uložené procedury ●

mysql> delimiter // mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t; END; // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;

Příklady uložené procedury ●

mysql> CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec)