Relacyjne Bazy Danych Wykład 02/03 Wojciech St. Mościbrodzki wojmos@wojmos

1. Relacyjne Bazy DanychWykład 02/03Wojciech St. Mościbrodzkiwojmos@wojmos.com

2. Encje - przypomnienie • Encja i encja-typ • każda unikalna (identyfikowalna) rzecz • posiada atrybuty (cechy) przyjmujące określone wartości • zbiór wartości atrybutu, nazywamy dziedziną • encja (ściślej: encja-typ) to zbiór wszystkich encji identyfikowalnych przez ten sam zbiór atrybutów • Przykłady: • Jan Kowalski = encja • cechy (atrybuty) Jana Kowalskiego = {wzrost, kolor oczu, imię, nazwisko} • wartości atrybutów Jana Kowalskiego = {174, zielony, Jan, Kowalski} • student (np. Jan Kowalski) = encja (ściślej: encja-typ) • atrybut encji student: kolor oczu • wartości atrybutu kolor oczu (dziedzina atrybutu) = {zielony, piwny, niebieski}

3. Encja - identyfikowalność • klucz kandydujący • nieformalnie: atrybuty, których wartości identyfikują encje • formalnie: to każdy zbiór atrybutów, który posiada dwie własności: • wartości atrybutów są różne dla każdej encji • usunięcie dowolnego atrybutu ze zbioru, sprawia, że warunek powyżej nie jest spełniony • klucz główny • wybrany (arbitralnie) jeden z kluczy kandydujących Klucze kandydujące: {imię}, {semestr, miasto}, {semestr, wiek} itd.

4. Diagram Związków Encji (ERD) atrybut klucz klucz zew. • ERD służy do modelowania danych i związków pomiędzy nimi ENCJA wyliczony • Formalnie – na diagramach ER NIE OZNACZAMY TYPÓW DANYCH encja klucz główny atrybut

5. Diagram Związków Encji (ERD) - RELACJE • ERD służy do modelowania danych i związków pomiędzy nimi różne notacje (różni autorzy) struktura pól krotność relacji Klasyczna Krucza Stopka Notacja Chena 1 1 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 0 1 0 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 N 1 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 N N ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 Notacja J. Martina Notacja Barkera ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2

6. Diagram Związków Encji (ERD) – przypadki szczególne nagłówek faktury ilość towar data ilość numer numer linia faktury • Encja słaba • encja słaba to taka encja, która nie jest rozróżnialna (identyfikowalna) przez własne atrybuty (wymaga klucza zewnętrznego) • Asocjacja (encja asocjacyjna, relacja wiele-do-wiele) • asocjacja to relacja wiele-do-wiele (posiada cechy encji, może mieć atrybuty) • w bazie danych odpowiada jej osobna tabela, zwana tabelą pośredniczącą) ENCJA 1 ENCJA2 ENCJA 1 ENCJA2 ENCJA 1 ENCJA2

7. Klasyfikacja relacji • Kryterium krotności (dla relacji binarnych): • jeden-do-jeden • jeden-do-wiele • wiele-do-wiele • Kryterium "-narności" • unarne • binarne • trynarne • większe

8. Model logiczny danych (ERD) a model fizyczny • Model logiczny: • zawiera informacje o encjach i relacjach • nie zawiera informacji o typach danych (np. na poziomie SQL) • jest niezależny od bazy danych • Model fizyczny: • zawiera informacje o encjach, relacjach i typach • określa typy danych • jest związany z implementacją

9. Relacje a tabele relacja 1-do-1 relacja 1-do-

10. Relacja jeden-do-wiele – realizacja w bazie danych

11. Podstawy SQL

12. Podstawy SQL - przypomnienie • SQL jest językiem stosunkowo prostym (i archaicznym) • Założeniem SQL była realizacja podstawowych operacji bazodanowych: • wyszukiwanie rekordów - SELECT • dodawanie rekordów - INSERT • usuwanie rekordów - DELETE • modyfikacja rekordów - UPDATE • Poza „wielką czwórką” istnieją polecenia do zarządzania tabelami, bazami, tworzące indeksy, itp. • Polecenia wydawane w SQL nazywamy zapytaniami SQL (a nie – kwerendami; terminologia Microsoft)

13. Programowanie w SQL • SQL nie jest językiem programowania – jest językiem zapytań SEQEL System R (IBM) RDMS (MIT) quel Ingres (Berkley) '70s SQL Oracle (Oracle) SQL-86 (standard ANSI): w kierunku wieloplatformowości '87 SQL-92: kursory, ALTER, DATA, TIMESTAMP, VARCHAR... '90s SQL-1999 (SQL3): triggery, BOOLEAN, control-flow (IF...) ... SQL-2003: funkcje okien danych, XML, MERGE SQL-2006: rozbudowa XML, funkcje WWW SQL-2008: INSTEAD, TRUNCATE... '00s

14. SQL jako język zapytań • Typowy podział poleceń (zapytań) SQL: • SQL DML (ang. Data Manipulation Language – „język manipulacji danymi”), • SELECT, INSERT, UPDATE, DELETE • SQL DDL (ang. Data Definition Language – „język definicji danych”), • CREATE, DROP, ALTER, … • SQL DCL (ang. Data Control Language – „język kontroli nad danymi”). • GRANT, REVOKE, … • Inny podział: • polecenia standardowego (lub dialektowego) SQL (wykonuje je serwer) • polecenia typowe dla serwera konkretnego producenta (wykonuje je serwer) • polecenia dla klienta (oprogramowanie klienckie) – np. DELIMITER (mysql) • UWAGA: średnik (;) nie jest elementem zapytania, tylko znakiem terminalnym (po jego odebraniu klient przesyła zapytanie do serwera)

15. Podstawy SQL • Typy danych: • Numeryczne (mogą być SIGNED lub UNSIGNED): • Całkowite: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT • Rzeczywiste: FLOAT, DOUBLE, DECIMAL, NUMERIC • Bitowe: BIT • Logiczne: BOOL lub BOOLEAN (implementowane w Mysql jako TINYINT(1)) • Tekstowe: • CHAR – 0..255 znaków, przy przechowywaniu uzupełniane spacjami do podanego rozmiaru • VARCHAR – 0..65535 znaków, zajmują długość łańcucha + informacja o dłougości (1 lub 2 bajty) • BINARY i VARBINARY – przechowują łańcuchy w postaci binarnej • TEXT – duży obiekt tekstowy (BLOB – wersja binarna) • ENUM – typ wyliczeniowy, elementami są napisy • SET – typ specjalny, może zawierać dowolną liczbę (!!!) wartości ze zbioru

16. Typy danych: • Typy związane z czasem: • DATETIME – data (domyślnie) w formacie: YYYY-MM-DD HH:MM:SS • DATE – data w formacie: YYYY-MM-DD • TIME – czas w formacie: HH:MM:SS • Typy specjalne: • BLOB – duże obiekty binarne (np. obrazki, pliki mp3) • TIMESTAMP – specjalny typ (znacznik czasowy, w formacie DATETIME)

17. Programowanie z użyciem SQL • Metody na "uprogramowienie" SQL: • Embedded SQL (SQL osadzony): • konieczny odpowiedni kompilator • wartości przenoszone przez uwspólnione zmienne (widziane w SQL i w języku programowania) • Modular SQL (moduły SQL dla języków programowania) • konieczne są odpowiednie moduły dołączane • przekazywanie wartości zależne od języka • Dynamic SQL (część "programistyczna" w innym języku – np. PHP) • wymaga odpowiednich konstrukcji językowych • Programming Extensions for SQL (rozbudowa składni SQL – poza standardem ANSI) • wymaga serwera SQL rozumiejące "rozbudowaną" składnię • np. PL/SQL (Oracle), PSM (MySQL), PL PSM (PostgreSQL)

18. Podstawy SQL i pracy z klientem MySQL • Polecenia klienta MySQL: • są specyficzne dla dialektu MySQL - NIE NALEŻĄ do standardu SQL • w rzeczywistości są poleceniami, które klient "tłumaczy" na zwykłe zapytania SELECT i kieruje do systemowej bazy danych mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | gcs | | kardio | | mysql | | wojmos | +--------------------+ mysql> show tables; +------------------+ | Tables_in_wojmos | +------------------+ | czlowiek | | faktura | +------------------+ mysql> explain czlowiek; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | imie | char(30) | YES | | NULL | | | nazwisko | char(50) | YES | | NULL | | +----------+----------+------+-----+---------+-------+

19. Podstawowe typy zapytań • Zapytania wyszukujące dane: • SELECT • Zapytania modyfikujące dane: • INSERT • DELETE • UPDATE • Zapytania modyfikujące bazę danych i jej struktury: • CREATE • DROP • ALTER • GRANT / REVOKE • Pseudozapytania MySQL: • SHOW, EXPLAIN, DESCRIBE

20. CREATE DATABASE • Polecenie CREATE służy do tworzenia baz: CREATE {DATABASE} [IF NOT EXISTS] db_name[create_specification] create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name • W dokumentacji obowiązują następujące konwencje: • {xxx} – xxx musi wystąpić • xxx | yyy – musi wystąpić xxx ALBO yyy • [xxx] – xxx może wystąpić (albo i nie) • zzz: - wyjaśnienie symbolu zzz • [ xxx | yyy ] – może wystąpić xxx ALBO yyy albo nic • { xxx | yyy } – musi wystąpić xxx ALBO yyy

21. CREATE TABLE • Polecenie CREATE TABLE służy do tworzenia tablic: CREATE TABLE {tablica} [IF NOT EXISTS] (specyfikacja) [opcje] specyfikacja: nazwa_kolumny, definicja_kolumny definicja_kolumny: typ [NULL|NOT NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] CREATE TABLE player ( ID int auto_increment primary key, name char(50) NOT NULL, dex int, str int, cha int );

22. Podstawowe typy danych SQL • Typy danych języka SQL: • numeryczne: • całkowite: INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT • rzeczywiste: FLOAT(M,D), DOUBLE(M,D), DECIMAL(M,D) • logiczne: BOOL, • znakowe: • łańcuchy krótkie: CHAR, VARCHAR • obiekty tekstowe: TEXT • daty i czasu: • czasowe: DATE, TIME, DATETIME, YEAR • specjalne: • znacznik czasowy: TIMESTAMP • obiekt binarny: BLOB • wyliczeniowy: ENUM

23. Zapytania typu SELECT mysql> select distinct imie from czlowiek where imie like 'jan%'; +--------+ | imie | +--------+ | Jan | | Janusz | +--------+ • Typowy SELECT składa się z: • wskazania kolumn • wskazania tabeli FROM • filtru rekordów WHERE warunek • żądania sortowania ORDER BY select imie from czlowiek; +-------+ | imie | +-------+ | Roman | | Roman | +-------+ mysql> select * from czlowiek; +-------+-------------+ | imie | nazwisko | +-------+-------------+ | Roman | Pipokucki | | Roman | Popopizacki | +-------+-------------+ mysql> select * from czlowiek order by imie DESC, nazwisko ASC; +-------+-----------+ | imie | nazwisko | +-------+-----------+ | Roman | Kukurocki | | Roman | Kupikucki | +-------+-----------+

24. Zapytania typu INSERT • Zapytania typu INSERT dodają do tablicy dane INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... insert into player (name, dex, str, cha) values ('Raven',18,8,10); Query OK, 1 row affected (0.02 sec) insert into czlowiek values ('Jan','Nowak'); Query OK, 1 row affected (0.02 sec) • Specjalna forma INSERT (SELECT AS INSERT): insert into player (name) select imie from czlowiek;

25. Zapytania typu UPDATE • UPDATE służy do aktualizacji (modyfikacji) rekordów • UWAGA1: WHERE nie jest konieczne do działania UPDATE • UWAGA2: brak WHERE może przynieść opłakane skutki UPDATE table SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] update player set dex=20 where name like 'Raven'; update pracownik set placa=placa+200 where name like 'Kowalski'; update pracownik set placa=placa+200 where ID=23445; update pracownik set placa=placa+200;

26. Polecenie ALTER służy do zarządzania strukturą • Polecenie ALTER służy do zarządzania strukturą: ALTER TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_type] | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | CHANGE [COLUMN] old_col_name new_col_name column_definition | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} | RENAME [TO] new_tbl_name | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

27. Złączenia tablic – operacje JOIN

28. JOIN – w pytaniach i odpowiedziach • Do czego służą operacje typu JOIN • Do umożliwienia wykonywania operacji (np. SELECT) na danych przechowywanych w wielu tabelach • Na jakich obiektach przeprowadza się JOIN • Operacje JOIN są wykonywane na dwóch (lub więcej) tablicach (zwykle – pozostających w pewnej relacji) • Jak wykonywane są "długie" operacje JOIN • Każdą wielotablicową operację JOIN można rozpatrywać jako łańcuch "binarnych" (dwutablicowych) JOINów.

29. Dane przykładowe do operacji JOIN • Rozpatrzmy dwa zbiory danych: encje STUDENT i JĘZYK create table pracownik ( id int auto_increment primary key, imie char(10), nazwisko char(30), placa int ); create table jezyk ( id int auto_increment primary key, nazwa char(15) ); insert into pracownik values (1,'Jan','Nowak',3400); insert into pracownik values (2,'Ewa','Malina',2100); insert into pracownik values (3,'Iza','Trus',4000); insert into jezyk values (11,'polski'); insert into jezyk values (12,'angielski'); insert into jezyk values (13,'niemiecki'); (w tej chwili nie ma pomiędzy naszymi encjami relacji)

30. Iloczyn kartezjański • Iloczyn kartezjański • Wersja algebry relacji: Iloczynem kartezjańskim zbiorów A i B nazywamy zbiór wszystkich par (a,b), takich, że aA bB • Wersja bazodanowa: iloczyn kartezjański dwóch encji A i B to zbiór wszystkich par (a,b), takich, że a jest elementem encji A, zaś b jest elementem encji B • Iloczyn kartezjański zawiera KAŻDĄ możliwą parę (zbiór wszystkich par) mysql> select * from pracownik, jezyk; +----+------+----------+-------+----+-----------+ | id | imie | nazwisko | placa | id | nazwa | +----+------+----------+-------+----+-----------+ | 1 | Jan | Nowak | 3400 | 11 | polski | | 2 | Ewa | Malina | 2100 | 11 | polski | | 3 | Iza | Trus | 4000 | 11 | polski | | 1 | Jan | Nowak | 3400 | 12 | angielski | | 2 | Ewa | Malina | 2100 | 12 | angielski | | 3 | Iza | Trus | 4000 | 12 | angielski | | 1 | Jan | Nowak | 3400 | 13 | niemiecki | | 2 | Ewa | Malina | 2100 | 13 | niemiecki | | 3 | Iza | Trus | 4000 | 13 | niemiecki | +----+------+----------+-------+----+-----------+

31. Iloczyn kartezjański a relacja • Relację tworzą tylko te pary, które mają określoną właściwość (spełniają określony warunek). • Innymi słowy: relacja to jeden z podzbiorów iloczynu kartezjańskiego • Istnieje DUŻO możliwych relacji na dwóch encjach 1 1 11 11 2 2 12 12 3 3 13 13 Relacja A (1-do-1): jaki język obowiązuje w kraju zamieszkania pracownika Relacja B (1-do-): jakie są języki ojczyste pracowników 1 1 11 11 2 2 12 12 3 3 13 13 Relacja D (-do-): jakimi językami mówią pracownicy Relacja C (1-do-1): jakiego języka uczą się pracownicy

32. Relacje zaimplementowane w bazie danych • Implementacja relacji wymaga dodania kolumny (zakładamy relację 1-do-) alter table pracownik add column id_jez int; • Oczywiście istnieje wiele możliwych relacji 1-do- na tych encjach: 1 1 11 11 2 2 12 12 3 3 13 13 Relacja A (1-do-): jakie są języki ojczyste pracowników Relacja B (1-do-): główny język projektów naszych pracowników

33. Wykorzystanie operatora JOIN • Operatory JOIN (binarne, czyli na dwóch encjach) dzielimy na: • operatory INNER JOIN (odnoszą się tylko do elementów będących w relacji): • EQUI JOIN (klasyczny INNER, wymaga równości pomiędzy kolumnami) • NATURAL JOIN (wymaga równości, budowany w oparciu o nazwy kolumn) • operatory OUTER JOIN (dopuszczają elementy niebędące w relacji) • LEFT OUTER (wszystkie elementy z encji po lewej stronie operatora) • RIGHT OUTER (wszystkie elementy z encji po prawej stronie operatora) • FULL OUTER (złożenie LEFT i RIGHT) • NATURAL OUTER JOIN (OUTER budowany w oparciu o nazwy kolumn) • operator THETA JOIN (opiera się na warunku innym niż równość; rzadki) • operator ANTI JOIN (opiera się na warunku różności; rzadki) • operator CROSS JOIN (pełny iloczyn kartezjański) • Operator JOIN może występować także w wersji unarnej: • Operator SELF JOIN (tabela joinowana sama z sobą; raczej rzadki) Najważniejszą rolę pełnią INNER JOIN oraz OUTER JOIN (LEFT i RIGHT)

34. Dane do analizy • Wprowadzamy dane relacji update pracownik set id_jez=11 where id=1; update pracownik set id_jez=11 where id=2; update pracownik set id_jez=12 where id=3; 1 11 2 12 3 13 Relacja (1-do-): główny język projektów naszych pracowników mysql> select * from jezyk; +----+-----------+ | id | nazwa | +----+-----------+ | 11 | polski | | 12 | angielski | | 13 | niemiecki | +----+-----------+ 3 rows in set (0.01 sec) mysql> select * from pracownik; +----+------+----------+-------+--------+ | id | imie | nazwisko | placa | id_jez | +----+------+----------+-------+--------+ | 1 | Jan | Nowak | 3400 | 11 | | 2 | Ewa | Malina | 2100 | 11 | | 3 | Iza | Trus | 4000 | 12 | +----+------+----------+-------+--------+ 3 rows in set (0.00 sec)

35. INNER JOIN 1 11 2 12 3 13 • INNER JOIN: • wymaga klauzuli ON (opisującej kolumny używane w warunku złączenia) • warunek zawsze zbudowany jest na równości (stąd nazwa: EQUI JOIN) • zwraca tylko te pary, które należą do relacji (WAŻNE!) mysql> SELECT * from pracownik INNER JOIN jezyk ON(pracownik.id_jez=jezyk.id); +----+------+----------+-------+--------+----+-----------+ | id | imie | nazwisko | placa | id_jez | id | nazwa | +----+------+----------+-------+--------+----+-----------+ | 1 | Jan | Nowak | 3400 | 11 | 11 | polski | | 2 | Ewa | Malina | 2100 | 11 | 11 | polski | | 3 | Iza | Trus | 4000 | 12 | 12 | angielski | +----+------+----------+-------+--------+----+-----------+ • równość id_jez=id występuje tylko 3 przypadkach • operator INNER JOIN zwraca podzbiór iloczynu kartezjańskiego • element, który nie znajduje się w relacji (nie wychodzi z niego żaden łuk) NIE ZOSTANIE zawarty w wyniku INNER JOIN

36. OUTER JOIN 1 11 2 12 3 13 • OUTER JOIN: • wymaga klauzuli ON (opisującej kolumny używane w warunku złączenia) • warunek równość LUB brak elementu po "drugiej" stronie • zwraca te pary, które należą do relacji ORAZ pary (element encji-NULL)(WAŻNE!) SELECT * from jezyk LEFT OUTER JOIN pracownik ON (jezyk.id=pracownik.id_jez); +----+-----------+------+------+----------+-------+--------+ | id | nazwa | id | imie | nazwisko | placa | id_jez | +----+-----------+------+------+----------+-------+--------+ | 11 | polski | 1 | Jan | Nowak | 3400 | 11 | | 11 | polski | 2 | Ewa | Malina | 2100 | 11 | | 12 | angielski | 3 | Iza | Trus | 4000 | 12 | | 13 | niemiecki | NULL | NULL | NULL | NULL | NULL | +----+-----------+------+------+----------+-------+--------+ • warunek (id=id_jez) lub (jezyk.id nie jest elementem relacji) występuje w 4 przypadkach • operator OUTER JOIN zwraca podzbiór ilocz. kart. ORAZ pary zbudowane "sztucznie" z elementu NULL • element, który nie znajduje się w relacji (nie wychodzi z niego żaden łuk) ZOSTANIE zawarty w wyniku OUTER JOIN (jeśli jest po "odpowiedniej stronie")

37. LEFT vs RIGHT OUTER JOIN 1 1 1 1 11 11 11 11 2 2 2 2 12 12 12 12 3 3 3 3 13 13 13 13 4 4 4 4 LEFT OUTER JOIN select * from ksiazka LEFT OUTER JOIN autor on (ksiazka.id_aut=autor.id); KSIĄŻKA AUTOR +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | +----+--------+--------+------+----------+ KSIĄŻKA AUTOR NULL select * from ksiazka RIGHT OUTER JOIN autor on (ksiazka.id_aut=autor.id); RIGHT OUTER JOIN Autorzy są "twórcami" książek KSIĄŻKA AUTOR +------+--------+--------+----+----------+ | id | tytul | id_aut | id | nazwisko | +------+--------+--------+----+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | NULL | NULL | NULL | 13 | Reymont | +------+--------+--------+----+----------+ NULL

38. LEFT, RIGHT i FULL OUTER JOIN 1 1 1 11 11 11 2 2 2 12 12 12 3 3 3 13 13 13 4 4 4 select * from ksiazka RIGHT OUTER JOIN autor on (ksiazka.id_aut=autor.id); select * from ksiazka FULL OUTER JOIN autor on (ksiazka.id_aut=autor.id); +------+--------+--------+----+----------+ | id | tytul | id_aut | id | nazwisko | +------+--------+--------+----+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | NULL | NULL | NULL | 13 | Reymont | +------+--------+--------+----+----------+ +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | |NULL| NULL | NULL | 13 | Reymont | +----+--------+--------+------+----------+ select * from ksiazka LEFT OUTER JOIN autor on (ksiazka.id_aut=autor.id); LEFT OUTER RIGHT OUTER FULL OUTER +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | +----+--------+--------+------+----------+ NULL NULL NULL NULL

39. OUTER JOIN vs INNER JOIN 1 1 1 1 11 11 11 11 2 2 2 2 12 12 12 12 3 3 3 3 13 13 13 13 4 4 4 4 INNER JOIN INNER JOIN +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | +----+--------+--------+------+----------+ RIGHT OUTER FULL OUTER +------+--------+--------+----+----------+ | id | tytul | id_aut | id | nazwisko | +------+--------+--------+----+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | NULL | NULL | NULL | 13 | Reymont | +------+--------+--------+----+----------+ +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | |NULL| NULL | NULL | 13 | Reymont | +----+--------+--------+------+----------+ LEFT OUTER +----+--------+--------+------+----------+ | id | tytul | id_aut | id | nazwisko | +----+--------+--------+------+----------+ | 1 | LOTR | 11 | 11 | Tolkien | | 2 | Hobbit | 11 | 11 | Tolkien | | 3 | Eden | 12 | 12 | Lem | | 4 | Dziady | NULL | NULL | NULL | +----+--------+--------+------+----------+ LEFT OUTER RIGHT OUTER FULL OUTER NULL NULL NULL NULL

40. NATURAL JOIN • NATURAL JOIN jest przykładem JOINA, w którym: • warunek ON nie jest podawany w zapytaniu, • jest automatycznie generowany na podstawie NAZW kolumn • jeśli używamy klauzuli USING, to specyfikujemy kolumnę (jeśli nie – brane są pod uwagę WSZYSTKIE pary o jednakowych nazwach) • istnieje NATURAL JOIN (inner) oraz NATURAL [LEFT|RIGHT|FULL] OUTER select * from pracownikNATURAL JOIN dzial USING (ID_dzial); • NATURAL JOIN wymaga odpowiedniego nazywania kolumn • (jest to potencjalnie niebezpieczne dla nieuważnego programisty) Create table pracownik ( id int auto_increment primary key, nazwisko char(30), ID_dzial int ); Create table dzial ( ID_dzial int auto_increment primary key, nazwa char(30) );

41. CROSS JOIN • CROSS JOIN zwraca iloczyn kartezjański select * from pracownik CROSS JOIN jezyk +----+------+----------+-------+--------+----+-----------+ | id | imie | nazwisko | placa | id_jez | id | nazwa | +----+------+----------+-------+--------+----+-----------+ | 1 | Jan | Nowak | 3400 | 11 | 11 | polski | | 2 | Ewa | Malina | 2100 | 11 | 11 | polski | | 3 | Iza | Trus | 4000 | 12 | 11 | polski | | 1 | Jan | Nowak | 3400 | 11 | 12 | angielski | | 2 | Ewa | Malina | 2100 | 11 | 12 | angielski | | 3 | Iza | Trus | 4000 | 12 | 12 | angielski | | 1 | Jan | Nowak | 3400 | 11 | 13 | niemiecki | | 2 | Ewa | Malina | 2100 | 11 | 13 | niemiecki | | 3 | Iza | Trus | 4000 | 12 | 13 | niemiecki | +----+------+----------+-------+--------+----+-----------+ select * from pracownik, jezyk

42. GROUP BY • Rozważmy następującą bazę: create table paragon ( id int auto_increment primary key, numer char(10), wartosc numeric(5,2), data_zakupu date, id_kli int ); create table klient ( id int auto_increment primary key, nazwa char(15) );

43. GROUP BY • Dotychczas tworzyliśmy po prostu tablicę wynikową za pomocą JOIN: select nazwa, numer, wartosc from klient left outer join paragon on (klient.id=paragon.id_kli); +-----------------+-------+---------+ | nazwa | numer | wartosc | +-----------------+-------+---------+ | Alfa sp. z o.o. | P001 | 110.2 | | Alfa sp. z o.o. | P002 | 20.25 | | Alfa sp. z o.o. | P003 | 311.5 | | Alfa sp. z o.o. | P008 | 45 | | Beta SA | P004 | 100.25 | | Beta SA | P005 | 70 | | Beta SA | P006 | 19.2 | | Beta SA | P007 | 30 | | Gamma Inc. | NULL | NULL | +-----------------+-------+---------+ • Teraz chcemy przeprowadzić operacje na grupach danych

44. GROUP BY • GROUP BY pozwala na zastosowanie SUM dogrup(a nie całości) select nazwa, wartosc from klient left outer join paragon on (klient.id=paragon.id_kli); +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 110.2 | | Alfa sp. z o.o. | 20.25 | | Alfa sp. z o.o. | 311.5 | | Alfa sp. z o.o. | 45 | | Beta SA | 100.25 | | Beta SA | 70 | | Beta SA | 19.2 | | Beta SA | 30 | | Gamma Inc. | NULL | +-----------------+---------+ select klient.id, nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) GROUP BY klient.id; +----+-----------------+--------------+ | id | nazwa | sum(wartosc) | +----+-----------------+--------------+ | 1 | Alfa sp. z o.o. | 486.95 | | 2 | Beta SA | 219.45 | | 3 | Gamma Inc. | NULL | +----+-----------------+--------------+ select sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli); +--------------+ | sum(wartosc) | +--------------+ | 706.40 | +--------------+

45. GROUP • Klauzuli GROUP BY używamy ze wszystkimi funkcjami, które mają działać na grupach danych i wyliczać dla każdej grupy reprezentującą ją wartość • Wyliczoną wartość nazywamy agregatem, a funkcje – funkcjami agregującymi • Funkcje agregujące to m.in. COUNT, MAX, MIN, SUM i AVG # wylicz wartość zakupów # dla każdej z firm select nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id; # wylicz średni zakup # dla każdej z firm select nazwa, avg(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id; # jaki jest największy zakup # każdej z firm select nazwa, max(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id; # ile zakupów zrobiła # każda z firm select nazwa, count(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id;

46. HAVING • Klauzula HAVING pozwala wyfiltrować krotki, dla których agregat spełnia określony warunek (agregat – czyli WARTOŚĆ REPREZENTUJĄCA KAŻDĄ Z GRUP) select nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id HAVING sum(wartosc)>300 +-----------------+--------------+ | nazwa | sum(wartosc) | +-----------------+--------------+ | Alfa sp. z o.o. | 486.95 | +-----------------+--------------+ +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 110.2 | | Alfa sp. z o.o. | 20.25 | | Alfa sp. z o.o. | 311.5 | | Alfa sp. z o.o. | 45 | | Beta SA | 100.25 | | Beta SA | 70 | | Beta SA | 19.2 | | Beta SA | 30 | | Gamma Inc. | NULL | +-----------------+---------+ HAVING +-----------------+--------------+ | nazwa | sum(wartosc) | +-----------------+--------------+ | Alfa sp. z o.o. | 486.95 | | Beta SA | 219.45 | | Gamma Inc. | NULL | +-----------------+--------------+ GROUP + SUM

47. HAVING vs WHERE • WHERE jest filtrem dla danych PRZED agregacją, • HAVING jest filtrem dla agregatów (wyników PO agregacji) select nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) WHERE wartosc>50 group by klient.id HAVING sum(wartosc)>200; +-----------------+---------+ | Alfa sp. z o.o. | 421.70 | +-----------------+---------+ HAVING +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 421.70 | | Beta SA | 170.25 | +-----------------+---------+ +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 110.2 | | Alfa sp. z o.o. | 20.25 | | Alfa sp. z o.o. | 311.5 | | Alfa sp. z o.o. | 45 | | Beta SA | 100.25 | | Beta SA | 70 | | Beta SA | 19.2 | | Beta SA | 30 | | Gamma Inc. | NULL | +-----------------+---------+ GROUP+SUM +-----------------+---------+ | nazwa | wartosc | +-----------------+---------+ | Alfa sp. z o.o. | 110.2 | | Alfa sp. z o.o. | 311.5 | | Beta SA | 100.25 | | Beta SA | 70 | +-----------------+---------+ WHERE

48. JOIN z użyciem aliasów • Użycie aliasów pozwala za skrócenie długich zapytań JOIN (ale czasem może zmniejszyć czytelność polecenia) select nazwa, sum(wartosc) from klient left outer join paragon on (klient.id=paragon.id_kli) group by klient.id HAVING sum(wartosc)>300 select nazwa, sum(wartosc) from klient as k left outer join paragon as p on (k.id=p.id_kli) group by k.id HAVING sum(wartosc)>300 select nazwa, sum(wartosc) as ile from klient as k left outer join paragon as p on (k.id=p.id_kli) group by k.id HAVING ile>300

49. JOIN przez kilka tabel REDUNDANCJA Ta kolumna jest niepotrzebna, dlatego należy ją usunąć!

50. JOIN przez kilka tabel • Gdzie sprzedajemy (ile faktur w poszczególnych miastach) select miasto.nazwa, count(faktura.id) from miasto left outer join kupujacy on (miasto.id=kupujacy.id_mia) left outer join faktura on (kupujacy.id=faktura.id_kup) group by miasto.id +----------+-------------------+ | nazwa | count(faktura.id) | +----------+-------------------+ | Poznan | 4 | | Krakow | 3 | | Gdansk | 2 | | Warszawa | 4 | | Szczecin | 2 | | Tczew | 2 | | Sanok | 2 | | Radom | 0 | +----------+-------------------+