Bazy Danych W03 Wojciech St. Mościbrodzki wojmos@pjwstk.pl

1. Bazy DanychW03Wojciech St. Mościbrodzkiwojmos@pjwstk.edu.pl

2. Podstawy SQL • 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)

3. 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)

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

5. 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)

6. Podstawowe formy najważniejszych zapytań • SELECT – zapytanie zwracające zbiór kolumn z podanej tabeli SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] select_expr [, select_expr ...] [FROM table_references] SELECT nazwisko FROM student; SELECT imie, nazwisko FROM pracownik; SELECT DISTINCT imie FROM dostawca; SELECT COUNT(id) FROM klient; • Wynikiem zapytania SELECT jest zbiór danych w postaci tabeli mysql> select * from student; +----+------------+--------+ | id | nazwisko | miasto | +----+------------+--------+ | 1 | Kowalski | Gdansk | | 2 | Nowak | Poznan | | 3 | Malinowski | Krakow | +----+------------+--------+ 3 rows in set (0.00 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)

7. SELECT i WHERE i ORDER BY • WHERE jest klauzulą filtra, nakładanego na wynik zapytania (PO wyliczeniu jego wyników) • ORDER BY jest poleceniem posortowania ostatecznego rezultatu SELECT select_expr [, select_expr ...] [FROM table_references] [WHERE where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] mysql> select nazwisko from student where id<=2; +----------+ | nazwisko | +----------+ | Kowalski | | Nowak | +----------+ mysql> select distinct miasto from student order by miasto asc; +--------+ | miasto | +--------+ | Gdansk | | Krakow | +--------+

8. INSERT – wstawianie rekordów • INSERT – wersja klasyczna INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... INSERT INTO student VALUES (13,'Mazan','Pruszkowo'); INSERT INTO student(nazwisko, miasto) VALUES ('Kowalewski','Warszawa'); • INSERT – wersja z SET (podobna do UPDATE) INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ... INSERT INTO student SET nazwisko='Piotrowski'; • INSERT – wersja z SELECT INSERT [INTO] tbl_name [(col_name,...)] SELECT ... mysql> insert into student(nazwisko) select n from pracownik; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0

9. INSERT - uwagi • Wersja INSERT bez jawnego podawania kolumn wymaga, abyśmy określili wartości wszystkich istniejących w tabeli kolumn • Istnieje możliwość wstawiania jednym INSERTem wielu rekordów • Jeśli kolumna ma oznaczenie DEFAULT, w przypadku braku wartości w INSERT, zostanie wstawiona wartość domyślna INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

10. Spójność relacyjna • Spójność relacyjna (relational integrity) – sytuacja, w której dane są w takiej postaci, która jest "wysokiej jakości": • Spójność encji (entity integrity) – każde dwa elementy danych (obiekty) są rozróżnialne. Innymi słowy: istnieje klucz główny, a wartości tego klucza są ustalone (nie występuje NULL, czyli wartość nieustalona) • Spójność wartości nieustalonych (null integrity) – dla określonych danych wartość NULL jest nieakceptowalna • Spójność dziedzinowa (domain integrity) – wartości atrybutów są w dopuszczalnym zakresie (np. wiek nie może być ujemny, liczba pracowników – wyrażona ułamkiem itp.). Spójność domenowa sugeruje, że dane z różnych dziedzin nie powinny być porównywane "wprost". • Spójność referencyjna (referential integrity) – odnosi się do relacji pomiędzy dwoma tabelami; wymaga aby wartości klucza obcego odpowiadały istniejącym wartościom tabeli wskazywanej (klucz główny)

11. Spójność referencyjna • Przypomnienie: • Klucz kandydujący – jeden z zestawów atrybutów, który może pełnić rolę klucza • Klucz główny – wybrany arbitralnie klucz spośród kluczy kandydujących • Klucz obcy – zestaw atrybutów, który odpowiada kluczowi głównemu innej tabeli

12. Naruszenie spójności referencyjnej • Naruszenia spójności • DELETE: usunięcie rekordu, do którego ID jest wartością klucza obcego • UPDATE: zmiana wartości klucza obcego na nieistniejący (brak wartości klucza głównego) lub zmiana wartości klucza głównego, któremu odpowiada klucz obcy • INSERT: wstawienie rekordu, którego klucz obcy odpowiada nieistniejącej wartości klucza głównego

13. Więzy integralności • Obsługa więzów integralności w mysql (UWAGA: tylko InnoDB): [CONSTRAINT] FOREIGN KEY (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION • Tabela "główna" musi istnieć podczas tworzenia tabeli "potomnej": create table dzial ( id int auto_increment primary key, nazwa char(10) ) ENGINE=InnoDB; create table pracownik ( id int auto_increment primary key, imie char(10), nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ) ENGINE=InnoDB; • Usuwanie tabel również wymaga odpowiedniej kolejności!

14. Spójność referencyjna • Więzy typu RESTRICT (w MySQL = NO ACTION) zabraniają wykonania operacji create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ) ENGINE=InnoDB; insert into pracownik values (1,'Jan','Nowak',1); ERROR 1452 (23000): Cannot add or update a child row:a foreign key constraint fails (`rbd3/pracownik`, CONSTRAINT `pracownik_ibfk_1` FOREIGN KEY (`id_dzi`) REFERENCES `dzial` (`id`)) mysql> delete from dzial where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`rbd3/pracownik`, CONSTRAINT `pracownik_ibfk_1` FOREIGN KEY (`id_dzi`) REFERENCES `dzial` (`id`))

15. Spójność referencyjna • Więzy typu CASCADE przenoszą operacje (DELETE lub CASCADE) z rekordów tabeli "rodzica" na tabelę "potomną" create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ON DELETE CASCADE ) ENGINE=InnoDB; mysql> select count(id) from pracownik; delete from dzial where id=1; select count(id) from pracownik; +-----------+ | count(id) | +-----------+ | 4 | +-----------+ +-----------+ | count(id) | +-----------+ | 2 | +-----------+

16. Spójność referencyjna • Więzy typu SET NULL po operacji DELETE/UPDATE na rekordach tabeli "rodzica" ustawiają wartości klucza obcego na NULL create table pracownik ( id int auto_increment primary key, nazwisko char(15), id_dzi int, CONSTRAINT FOREIGN KEY (id_dzi) REFERENCES dzial(id) ON DELETE CASCADE ) ENGINE=InnoDB; select * from telefon; delete from pracownik where id=3; +----+--------+--------+ | id | serial | id_pra | +----+--------+--------+ | 1 | NP3451 | 1 | | 2 | ED2221 | 2 | | 3 | AP1111 | 3 | | 4 | AP1311 | 3 | | 5 | AS8751 | 4 | | 6 | SU9458 | 3 | +----+--------+--------+ +----+--------+--------+ | id | serial | id_pra | +----+--------+--------+ | 1 | NP3451 | 1 | | 2 | ED2221 | 2 | | 3 | AP1111 | NULL | | 4 | AP1311 | NULL | | 5 | AS8751 | 4 | | 6 | SU9458 | NULL | +----+--------+--------+

17. Spójność dziedzinowa - CHECK • CHECK zapewnia ochronę przed wstawieniem danych nie spełniających określonego warunku CREATE TABLE user(     id auto_increment PRIMARY KEY,     login VARCHAR(16),     password VARCHAR(20), CONSTRAINTCHECK(LENGTH(password) BETWEEN 8 AND 16) ) CREATE TABLE Person( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes'))