Bazy Danych W06 Wojciech St. Mościbrodzki wojmos@wojmos

1. Bazy DanychW06Wojciech St. Mościbrodzkiwojmos@wojmos.com

2. VIEW (widok, perspektywa)

3. VIEW • Widok (VIEW) to składowane zapytanie (stored query), które jest dostępne jako wirtualna tablica • Dlaczego warto stosować widoki: • Mogą pełnić rolę tablic z częścią danych (część kolumn lub część krotek) • Mogą pełnić role "ukrycia" złączeń tworzonych przez JOIN • Mogą pełnić rolę tablic agregujących • Mogą łączyć kilka tablic na poziomie unii ("normalizacja") • Mogą być wykorzystane do podnoszenia bezpieczeństwa • UWAGA: Widoki przechowywane są jako zapytania, a nie jako tabele (a więc zajmują mało miejsca)

4. Widok select * from pracownik; +----+----------+-----------+-------+--------+ | id | imie | nazwisko | placa | id_sta | +----+----------+-----------+-------+--------+ | 1 | Marek | Jackowski | 5600 | 2 | | 2 | Roman | Rendyk | 6000 | 2 | | 3 | Ewa | Tyrzyk | 8000 | 1 | | 4 | Tomasz | Pass | 6200 | 2 | | 5 | Bartosz | Nowacki | 2000 | 3 | | 6 | Wojciech | Kurtyl | 2200 | 4 | | 7 | Marek | Wiemanns | 1500 | 4 | +----+----------+-----------+-------+--------+ select * from stanowisko; +----+-------------+ | id | nazwa | +----+-------------+ | 1 | dyrektor | | 2 | manager | | 3 | specjalista | | 4 | referent | +----+-------------+ create view junior_staff as select imie, nazwisko, nazwa, placa from pracownik left outer join stanowisko on (pracownik.id_sta = stanowisko.id) where not (nazwa like 'dyrektor' or nazwa like 'manager'); select * from junior_staff; +----------+----------+-------------+-------+ | Bartosz | Nowacki | specjalista | 2000 | | Wojciech | Kurtyl | referent | 2200 | | Marek | Wiemanns | referent | 1500 | +----------+----------+-------------+-------+

5. VIEW create view kadra as select imie, nazwisko, nazwa from sprzedawca left outer join miasto on (sprzedawca.id_mia=miasto.id); select * from kadra; +--------+----------+----------+ | imie | nazwisko | nazwa | +--------+----------+----------+ | Jan | Nowak | Poznan | | Piotr | Kuna | Poznan | | Ewa | Trus | Krakow | | Iza | Pokora | Sanok | | Kasia | Gisz | Sanok | | Janusz | Wist | Warszawa | | Stefan | Kunera | Krakow | | Marek | Pokora | Poznan | +--------+----------+----------+ select * from kadra_agr; +----------+-----+------------+ | nazwa | ile | avg(placa) | +----------+-----+------------+ | Poznan | 3 | 1733.3333 | | Krakow | 2 | 1800.0000 | | Warszawa | 1 | 1900.0000 | | Sanok | 2 | 2100.0000 | +----------+-----+------------+ create view kadra_agr as select nazwa, count(sprzedawca.id) as ile, avg(placa) from sprzedawca left outer join miasto on (sprzedawca.id_mia=miasto.id) group by miasto.id;

6. Widok a bezpieczeństwo • Nadawanie praw dostępu create view junior_staff as select imie, nazwisko, nazwa, placa from pracownik left outer join stanowisko on (pracownik.id_sta=stanowisko.id) where not (nazwa like 'dyrektor' or nazwa like 'manager'); create user bazak; grant all on rbd6.junior_staff to bazak; select * from pracownik; ERROR 1142 (42000): SELECT command denied to user 'bazak'@'localhost' for table 'pracownik' select * from junior_staff; +----------+----------+-------------+-------+ | imie | nazwisko | nazwa | placa | +----------+----------+-------------+-------+ | Bartosz | Nowacki | specjalista | 2000 | | Wojciech | Kurtyl | referent | 2200 | | Marek | Wiemanns | referent | 1500 | +----------+----------+-------------+-------+

7. Widok a aktualność danych • Ponieważ widok jest tabelą wirtualną, to zmiany wprowadzone w tabelach oryginalnych, przekładają się na dane w widoku select * from junior_staff; +----------+----------+-------------+-------+ | imie | nazwisko | nazwa | placa | +----------+----------+-------------+-------+ | Bartosz | Nowacki | specjalista | 2000 | | Wojciech | Kurtyl | referent | 2200 | | Marek | Wiemanns | referent | 1500 | +----------+----------+-------------+-------+ update pracownik set placa=2500 where nazwisko like 'Nowacki'; select * from junior_staff; +----------+----------+-------------+-------+ | imie | nazwisko | nazwa | placa | +----------+----------+-------------+-------+ | Bartosz | Nowacki | specjalista | 2500 | | Wojciech | Kurtyl | referent | 2200 | | Marek | Wiemanns | referent | 1500 | +----------+----------+-------------+-------+

8. Widok a spójność tabel • Widok a modyfikacja struktury tabel – dodanie kolumny drop view if exists banal; create view banal as select * from pracownik; select * from banal; alter table pracownik add column plec char(1) default 'm'; update pracownik set plec='k' where id=3; select * from pracownik; +----+----------+-----------+-------+--------+------+ | id | imie | nazwisko | placa | id_sta | plec | +----+----------+-----------+-------+--------+------+ | 1 | Marek | Jackowski | 5600 | 2 | m | | 2 | Roman | Rendyk | 6000 | 2 | m | | 3 | Ewa | Tyrzyk | 8000 | 1 | k | +----+----------+-----------+-------+--------+------+ mysql> select * from banal; +----+----------+-----------+-------+--------+ | id | imie | nazwisko | placa | id_sta | +----+----------+-----------+-------+--------+ | 1 | Marek | Jackowski | 5600 | 2 | | 2 | Roman | Rendyk | 6000 | 2 | | 3 | Ewa | Tyrzyk | 8000 | 1 | +----+----------+-----------+-------+--------+

9. Widok a spójność tabel • Widok a modyfikacja struktury tabel – usunięcie kolumny drop view if exists banal; create view banal as select * from pracownik; select * from banal; alter table pracownik drop column imie; select * from pracownik; +----+-----------+-------+--------+------+ | id | nazwisko | placa | id_sta | plec | +----+-----------+-------+--------+------+ | 1 | Jackowski | 5600 | 2 | m | | 2 | Rendyk | 6000 | 2 | m | | 3 | Tyrzyk | 8000 | 1 | k | | 4 | Pass | 6200 | 2 | m | | 5 | Nowacki | 2500 | 3 | m | | 6 | Kurtyl | 2200 | 4 | m | | 7 | Wiemanns | 1500 | 4 | m | +----+-----------+-------+--------+------+ mysql> select * from banal; ERROR 1356 (HY000): View 'rbd6.banal' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

10. Widoki z możliwością aktualizacji • Wymogi na możliwość aktualizacji danych bazowych przez widok • Nie może zawierać funkcji agregujących • Nie może zawierać DISTINCT • Nie może używać operatora GROUP BY i HAVING • Nie może zawierać unii • Nie może być zbudowany w oparciu o widok, który nie umożliwia UPDATE • Nie może zawierać wielu odniesień do tej samej kolumny • Nie może zawierać niektórych rodzajów JOIN

11. Widok z możliwością aktualizacji tabel bazowych • Aktualizacja danych (UPDATE) update V1 set nazwa='Puck' where nazwa like 'Tczew'; select * from V1; +-----+----------+--------+--------+------+----------+ | ids | nazwisko | indeks | id_mia | idm | nazwa | +-----+----------+--------+--------+------+----------+ | 1 | Kowal | s3472 | 1 | 1 | Warszawa | | 2 | Nowak | s1231 | 2 | 2 | Puck | | 3 | Kopek | s2945 | 2 | 2 | Puck | +-----+----------+--------+--------+------+----------+ select * from miasto; +-----+----------+ | idm | nazwa | +-----+----------+ | 1 | Warszawa | | 2 | Puck | +-----+----------+

12. Widoki z możliwością wstawienia • Wymogi na możliwość wstawiania danych bazowych przez widok • Widok musi być klasy "updateable" • Nie może być kolumn o takiej samej nazwie w tablicach bazowych • Widok musi zawierać wszystkie kolumny z tablic bazowych, które nie mają ustawionej wartości domyślnej (DEFAULT) • Wszystkie kolumny muszą być referencjami prostymi z tablic bazowych (tzn. nie mogą być wyliczane na postawie innych kolumn)

13. Widoki z możliwością wstawienia create table miasto ( idm int auto_increment primary key, nazwa char(25) ); create table student ( ids int auto_increment primary key, imie char(20), nazwisko char(25), id_mia int, FOREIGN KEY (id_mia) REFERENCES miasto(idm) ON UPDATE CASCADE ); create view V2 as select nazwa, imie, nazwisko from student left outer join miasto on (student.id_mia=miasto.idm); insert into V2(nazwa, imie, nazwisko, id_mia) values ('Ewa','Peszek',1); ERROR 1471 (HY000): The target table V2 of the INSERT is not insertable-into

14. Widoki z możliwością wstawienia create table miasto (idm int auto_increment primary key, nazwa char(25)); create table student ( ids int auto_increment primary key, imie char(20), nazwisko char(25), id_mia int, FOREIGN KEY (id_mia) REFERENCES miasto(idm) ON UPDATE CASCADE ); create view V3 as select imie, nazwisko from student; insert into V3(imie, nazwisko) values ('Ewa','Peszek'); insert into V3(imie, nazwisko) values ('Ewa','Peszek'); mysql> select * from V3; +------+----------+ | imie | nazwisko | +------+----------+ | Jan | Kowal | | Iza | Nowak | | Ewa | Peszek | +------+----------+ mysql> select * from student; +-----+------+----------+--------+ | ids | imie | nazwisko | id_mia | +-----+------+----------+--------+ | 1 | Jan | Kowal | 1 | | 2 | Iza | Nowak | 2 | | 3 | Ewa | Peszek | NULL | +-----+------+----------+--------+

15. NESTED SQL (zagnieżdżone zapytania SQL)

16. Zagnieżdżone zapytanie SQL • Zagnieżdżone zapytanie SQL – częścią zapytania (najczęściej warunek WHERE coś IN (…) jest osobne zapytanie SQL SELECT * FROM pracownik WHERE id_dzi = (SELECT id FROM dzial); • UWAGA: nawiasy otaczające podzapytanie są niezbędne • Zagnieżdżonych SQL można używać z SELECT, INSERT, UPDATE i DELETE • W przypadku zapytań modyfikujących (INSERT, UPDATE, DELETE) nie jest możliwe wykonywanie SELECT na tej samej tablicy w podzapytaniu • Nested SQL czy JOIN?

17. UNION (unia)

18. UNION • Co to jest unia? • UNION jest operatorem pozwalającym na sumowanie tabel wynikowych dwóch zapytań SELECT • Jakie są wymogi na stosowanie unii? • Łączone tabele muszą być "kompatybilne", czyli mieć tą samą liczbę kolumn, a kolumny powinny być "podobne" względem typu • Po co stosuje się unie? • Wygodne narzędzie pracy na nieznormalizowanych bazach danych (lub z wykorzystaniem widoków) • Jakie są rodzaje unii? • UNION – zwykła unia (automatyczne odrzucenie duplikatów) • UNION ALL – eliminuje powtarzające się krotki (jak DISTINCT)

19. UNION • Unie pozwalają na łączenie zbiorów danych + = mysql> select * from faktury_historyczne union select * from faktury_biezace; +----+-------------+----------+----------+--------+ | id | data_zakupu | wartosc | VAT | id_kli | +----+-------------+----------+----------+--------+ | 1 | 2009-04-05 | 6000.00 | 7320.00 | 1 | | 2 | 2010-05-10 | 3000.00 | 3660.00 | 1 | | 3 | 2008-11-12 | 15000.00 | 18300.00 | 2 | | 4 | 2009-10-10 | 2000.00 | 2440.00 | 3 | | 1 | 2011-05-09 | 7000.00 | 8610.00 | 1 | | 2 | 2011-06-01 | 1000.00 | 1230.00 | 4 | +----+-------------+----------+----------+--------+ 6 rows in set (0.00 sec)

20. UNION i podzapytania • Wykorzystanie unii w podzapytaniach + = • UWAGA: konieczne jest zastosowanie klauzuli AS! select sum(wartosc) from (select * from faktury_historyczne union select * from faktury_biezace); ERROR 1248 (42000): Every derived table must have its own alias select sum(wartosc) from (select * from faktury_historyczne union select * from faktury_biezace) as sb; +--------------+ | sum(wartosc) | +--------------+ | 34000.00 | +--------------+ 1 row in set (0.00 sec)

21. TRIGGER (wyzwalacz)

22. TRIGGER – specyficzna stored procedure • TRIGGER – funkcja składowana wywoływana automatycznie, przez serwer po zaistnieniu pewnego zdarzenia • TRIGGER (wyzwalacz) jest więc swego rodzaju event guardianem • obiekt standardowy SQL99 update licznik set imie_ile=select count(*) from imie +----------------+ |imie| +----------------+ |Jan| |Tadeusz| |Piotr| |Kacper| +----------------+ mysql> insert into imie values ('Czesio')

23. Klasyfikacja TRIGGERÓW • Triggery (wyzwalacze) możemy podzielić: • według kryterium czasu: • triggery BEFORE • triggery AFTER • triggery INSTEAD OF (rzadko implementowane) • według kryterium rodzaju operacji (związek z type operacji, a nie poleceniem!) • triggery ON INSERT (działa także w przypadku LOAD DATA) • triggery ON DELETE • triggery ON UPDATE • według kryterium obiektu strzeżonego • triggery modyfikacji danych • triggery modyfikacji struktury (trigger ALTER, DROP) • triggery eventowe (trigger LOGIN)

24. Budowa TRIGGERA • Ogólna postać: CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_nametrigger_timetrigger_event ON tbl_name FOR EACH ROW trigger_stmt delimiter | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN UPDATE test4 SET b4 = b4 + 1 WHERE a4 = 3; END; | delimiter ; • Prawa (MySql): • tworzenie triggerów wymaga praw TRIGGER dla danej tablicy (wcześniej: prawo globalne SUPER)

25. obiekty NEW i OLD • Obiekty przechowujące wartość poprzednią i nową: create table t1 (id int, auto_increment primary key, liczba int); create table historia (z char(100), stamp timestamp) delimiter | CREATE TRIGGER moj1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN INSERT INTO history(z) values (CONCAT(OLD.liczba,'->',NEW.liczba)); END; | delimiter ; update t1 set liczba=2 where id=1; insert into t1(liczba) values (222);

26. Obiekty OLD i NEW • MySQL ułatwia wywołania obiektów poprzez nadanie im nazw delimiter | CREATE TRIGGER pensja_trigger BEFORE UPDATE ON pracownicy_table REFERENCING NEW ROW AS n, OLD ROW AS o FOR EACH ROW IF n.pensja <> o.pensja THEN --wykonaj odpowiednie działania; END IF; delimiter ;

27. Bezpieczniejsze tworzenie triggerów DROP TRIGGER IF EXISTS moj1; delimiter | CREATE TRIGGER moj1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN INSERT INTO history (zapis) values (3); END; | delimiter ;

28. Instrukcje sterowania

29. INSTRUKCJE sterowania (nie mylić z FUNKCJAMI) • Funkcje sterowania są zwykłymi funkcjami SQL • IF(), CASE(), IFNULL(), NULLIF() • Instrukcje sterowania są przeznaczone tylko dla obiektów składowanych. Oczywiście w obiektach składowanych MOŻNA używać również funkcji sterowania • IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE

30. Instrukcja IF • Instrukcja IF IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF CREATE PROCEDURE Sample(IN parametr char(30)) BEGIN IF parametr='Jan' THEN SELECT ('Janek!') as wynik; ELSE SELECT ('NIE Janek?') as wynik; END IF; END; mysql> call sample('Jan'); +--------+ | wynik | +--------+ | Janek! | +--------+ mysql> call sample('Janusz'); +------------+ | wynik | +------------+ | NIE Janek? | +------------+

31. Instrukcja IF • IF z wykorzystaniem bloku BEGIN-END DROP PROCEDURE IF EXISTS Sample; DELIMITER ;; CREATE PROCEDURE Sample(IN parametr char(30)) BEGIN IF parametr='Jan' THEN BEGIN SELECT ('Janek!') as wynik; SELECT ('Kope lat!') as wynik; END; ELSE SELECT ('NIE Janek?') as wynik; END IF; END; ;; DELIMITER ;

32. Instrukcja CASE mysql> call sample(1); +------------+ | JEDEN | +------------+ | JEDEN | +------------+ • Instrukcja CASE CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE mysql> call sample(2); +------------+ | DWA | +------------+ | DWA | +------------+ CREATE PROCEDURE Sample(IN parametr int) BEGIN CASE parametr WHEN 1 THEN SELECT('JEDEN'); WHEN 2 THEN SELECT('DWA'); ELSE BEGIN SELECT ('MNOSTWO!!!'); END; END CASE; END; mysql> call sample(5); +------------+ | MNOSTWO!!! | +------------+ | MNOSTWO!!! | +------------+

33. Instrukcje LOOP i LEAVE • LOOP jest pętlą nieskończoną, z której wyjście zapewnia LEAVE • nie jest to rozwiązanie "eleganckie" [begin_label:] LOOP statement_list END LOOP [end_label] mysql> call sample(3); +------------+ | (parametr) | +------------+ | 3 | +------------+ 1 row in set (0.00 sec) +------------+ | (parametr) | +------------+ | 11 | +------------+ 1 row in set (0.00 sec) CREATE PROCEDURE Sample(IN parametr int) BEGIN select (parametr); petla: LOOP IF parametr>10 THEN LEAVE petla; END IF; SET parametr = parametr + 1; END LOOP; select (parametr); END;

34. Instrukcje LOOP i LEAVE • REPEAT jest pętlą warunkową, która wykonuje się minimum 1 raz [begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label] mysql> call sample(8); +------------+ | (parametr) | +------------+ | 8 | +------------+ 1 row in set (0.00 sec) +------------+ | (parametr) | +------------+ | 9 | +------------+ 1 row in set (0.00 sec) +------------+ | (parametr) | +------------+ | 10 | +------------+ 1 row in set (0.00 sec) CREATE PROCEDURE Sample(IN parametr int) BEGIN REPEAT select (parametr); SET parametr = parametr + 1; UNTIL parametr>10 END REPEAT; END;

35. Instrukcje LOOP i LEAVE mysql> call sample(4); +------+ | (n) | +------+ | 0 | +------+ +------+ | (n) | +------+ | 1 | +------+ +------+ | (n) | +------+ | 2 | +------+ +------+ | (n) | +------+ | 3 | +------+ 1 row in set (0.00 sec) • WHILE jest typową pętlą warunkową [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label] CREATE PROCEDURE Sample(IN parametr int) BEGIN DECLARE n INT; SET n = 0; WHILE n<parametr DO select (n); SET n = n + 1; END WHILE; END;

36. Wykorzystanie obiektów składowanych CREATE PROCEDURE MakeCzlowiek() BEGIN DECLARE ile_sylab INT; DECLARE nazwisko char(50) default ''; DECLARE imie1 char(50) default ''; DECLARE temp char(10) default ''; select 2+CAST(2*rand() as signed) into ile_sylab; while (ile_sylab>0) do select dana into temp from sylaba order by rand() limit 1; set nazwisko := concat(nazwisko,temp); set ile_sylab := ile_sylab - 1; end while; set nazwisko := concat(nazwisko,'cki'); set nazwisko := concat(ucase(substring(nazwisko,1,1)),substring(nazwisko,2)); select dana into imie1 from imie order by rand() limit 1; SELECT nazwisko; insert into czlowiek(imie, nazwisko) values (imie1, nazwisko); END; mysql> call MakeCzlowiek(); select * from czlowiek; +-------------+ | Nazanacki | +-------------+

37. Wykorzystanie obiektów składowanych DROP PROCEDURE IF EXISTS MakeLudzie; DELIMITER ;; CREATE PROCEDURE MakeLudzie(IN ilu int) BEGIN delete from czlowiek; while (ilu>0) do call MakeCzlowiek(); set ilu := ilu - 1; end while; select count(*) as ilu_mamy_ludzi from czlowiek; END; ;; DELIMITER ; mysql> call MakeLudzie(100); +----------------+ | ilu_mamy_ludzi | +----------------+ | 100 | +----------------+ 1 row in set (0.06 sec)