Gwne elementy skadni SQL Zasady oglne 1 Jzyk

Główne elementy składni SQL: Zasady ogólne: 1) Język SQL nie rozróżnia małych i wielkich liter w słowach kluczowych i nazwach (baz danych, tabel, indeksów i kolumn); często dotyczy to też wartości napisowych (np. we wzorcach). Legalne są nazwy zbudowane ze znaków alfanumerycznych, nie zaczynające się od cyfry (w My. SQL jest to wprawdzie dozwolone, lecz nie zalecane). Nie są dozwolone nazwy składające się wyłącznie z cyfr. Nie należy w nazwach stosować znaków przestankowych ". " i "@". 1

2

Formułowanie poleceń w SQL Zdanie (polecenie, zapytanie) języka SQL składa się ze słów zarezerwowanych (kluczowych) oraz ze słów zdefiniowanych przez użytkownika. Słowa zarezerwowane są niezmienną częścią języka i mają ustalone znaczenie. W wielu dialektach SQL wymagane jest zakończenie każdego zdania znakiem średnika ‘; ’ – chociaż standard tego nie określa. Większość elementów polecenia SQL można pisać dowolnie, wielkimi lub małymi literami, za wyjątkiem danych podawanych „dosłownie”, czyli literałów, które muszą być zapisane dokładnie w takiej postaci, w jakiej występują w bazie danych. 3

Tworzenie nowej bazy w My. SQL: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] nazwa_bazy [[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name]; CREATE DATABASE synonim CREATE SCHEMA Przy braku specyfikacji IF NOT EXISTS wystąpi błąd jeżeli baza danych o podanej nazwie istnieje. CHARACTER SET - wyszczególnia domyślny dla bazy danych zbiór znaków. COLLATE - wyszczególnia domyślne dla bazy danych zestawienie. 4

Serwer My. SQL może równocześnie zarządzać wieloma bazami danych (zbiorami tabel - database), każdą z nich identyfikuje jej nazwa i mogą one posiadać oddzielnie zdefiniowane prawa dostępu. W danej chwili (w trakcie trwania połączenia z serwerem My. SQL), jest jedna bieżąca baza danych, do której domyślnie odnoszą się komendy adresujące tabele. Odniesienia do kolumn mogą być postaci: nazwa_kolumny tabela. nazwa_kolumny bazadanych. tabela. nazwa_kolumny tabela@bazadanych. nazwa_kolumny 5

mysql> CREATE DATABASE IF NOT EXISTS gilg DEFAULT CHARACTER SET cp 1250 DEFAULT COLLATE cp 1250_polish_ci; Query OK, 1 row affected (0. 02 sec) mysql> SHOW DATABASES; +----------+ | Database | +----------+ | information_schema | | gilg | | test | +----------+ 4 rows in set (0. 00 sec) mysql> 6

Wyświetlanie zapisu tworzenia bazy: mysql> SHOW CREATE DATABASE gilg; +----------------------------+ | Database | Create Database | +----------------------------+ | gilg | CREATE DATABASE `gilg` /*!40100 DEFAULT CHARACTER SET cp 1250 COLLATE cp 1250_polish_ci */ | +----------------------------+ 1 row in set (0. 00 sec) Inny sposób prezentacji wyniku: mysql> SHOW CREATE DATABASE gilgG ************* 1. row ************* Database: gilg Create Database: CREATE DATABASE `gilg` /*!40100 DEFAULT CHARACTER SET cp 1250 COLLATE cp 1250_polish_ci */ 1 row in set (0. 01 sec) mysql> 7

Wybór używanej bazy: USE nazwa_bazy; – wybór bazy, która będzie wykorzystywana w My. SQL mysql> USE gilg; Database changed mysql> Równoważne polecenie: CONNECT nazwa_bazy; – połączenie z bazą, która będzie wykorzystywana w My. SQL mysql> CONNECT gilg; Connection id: 1 Current database: gilg mysql> 8

Modyfikacja bazy: ALTER {DATABASE | SCHEMA} [nazwa_bazy] [[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name]; Nazwa bazy danych może być pominięta, jeżeli dotyczy domyślnej (używanej) bazy. Zmiana nazwy bazy: RENAME {DATABASE | SCHEMA} nazwa_bazy TO nowa_nazwa_bazy; Dodane w My. SQL 5. 1. 07 ale usunięte w My. SQL 5. 1. 23 9

Usuwanie bazy: DROP {DATABASE nazwa_bazy; | SCHEMA} [IF EXISTS] mysql> DROP DATABASE test; Query OK, 0 rows affected (0. 13 sec) mysql> CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA wprowadzone od My. SQL 5. 0. 2. 10

Typy danych: Standard ISO SQL (1992) przewiduje kilkanaście typów danych, podzielonych na grupy: 1. Typ logiczny: BOOLEAN TINYINT(1) w My. SQL 2. Typy znakowe: np. CHAR(N), VARCHAR(N) 3. Typ bitowy: BIT[(M)] 4. Typy liczbowe: dokładny np. INT, BIGINT, SMALLINT, DECIMAL lub przybliżony, np. FLOAT, DOUBLE PRECISION 11

3. Typy daty i godziny (Datetime): np. DATE, TIME, DATETIME, TIMESTAMP, YEAR 4. Typ przedziałowy: INTERVAL – opisujący przedział czasu 5. Typ znakowy i bitowy dużych obiektów o zmiennej długości odpowiednio TEXT i BLOB oraz ich odmiany (TINYTEXT, MEDIUMTEXT, LONGTEXT, TINYBLOB, MEDIUMBLOB, LONGBLOB) różniące się limitem długości (nie dopuszczają one dodatkowego określenia długości i przechowują informację o długości faktycznie wprowadzonych danych). Kolumny BLOB i TEXT nie mogą posiadać wartości DEFAULT. 12

6. Innym częstym rozszerzeniem repertuaru typów są (np. w My. SQL) ENUM i SET; są to typy napisowe przyjmujące jedną lub odpowiednio kilka spośród z góry określonych (w definicji typu kolumny) wartości, np. : . . . kolor ENUM('niebieski', 'biały', 'czarny') NOT NULL. . . Różnorodność dostępnych typów danych i możliwość określania długości należy wykorzystywać do optymalizowania definicji tabeli pod kątem zużycia miejsca i do kontroli integralności wprowadzanych (bądź wynikających z operacji na danych) wartości. 13

Typy danych w My. SQL: BOOL, BOOLEAN może przechowywać tylko dwie wartości: TRUE lub FALSE TINYINT(1) gdzie wartość zero = FALSE zaś wartość różna od zera = TRUE CHAR(N) definiuje pole napisowe o stałej długości N (ew. uzupełniane spacjami) VARCHAR(N) jest polem o zmiennej długości nie przekraczającej N, ale maksymalnie 255 BIT[(M)] M oznacza liczbę bitów na wartość, od 1 do 64 (domyślnie 1) 14

INT[(M)] [UNSIGNED] [ZEROFILL] DECIMAL(M, D) pole liczb całkowitych, przechowuje liczby z zakresu od -2147483648 do 2147483647 (z parametrem UNSIGNED - od 0 do 4294967295). DATE pole daty przechowuje daty z zakresu od '100001 -01' do '9999 -12 -31'. pole tekstowe, przechowuje dłuższe, wielowierszowe teksty do 65535 znaków. BLOB/TEXT Pole liczbowe (ułamek dziesiętny o ustalonej liczbie cyfr dziesiętnych w części całkowitej i ułamkowej, gdzie M to maksymalna ilość cyfr w tej kolumnie, a D to maksymalna ilość cyfr w części ułamkowej). 15

Wymagana pamięć dla typów znakowych : CHAR(M) VARCHAR(M) TINYBLOB, TINYTEXT M bajtów, 0 <= M <= 255 L+l bajtów, gdzie L <= M i 0<= M <= 255 L+l bajtów, gdzie L < 2^8 (255 znaków) BLOB, TEXT L+2 bajtów, gdzie L < 2^16 (65 535 znaków) L+3 bajtów, gdzie L < 2^24 (16 777 215 znaków) L+4 bajtów, gdzie L < 2^32 (4 294 967 295 znaków) MEDIUMBLOB, MEDIUMTEXT LONGBLOB, LONGTEXT 16

ENUM ( 'value 1' , 'value 2', . . . ) 1 or 2 bajtów, zależnie od liczby wyliczeniowych wartości (maksymalnie 65 535) SET ( 'value 1' , 1, 2, 3, 4, lub 8 bajtów, zależnie od liczby 'value 2', . . . ) elementów zbioru (maksymalnie 64) Od My. SQL 4. 0 maksymalna dopuszczalna długość kolumn LONGBLOB lub LONGTEXT zależy od skonfigurowanego maksymalnego rozmiaru pakietów w protokole klient/serwer i dostępnej pamięci. 17

Odmiany typów liczbowych: TINYINT[(M)] [UNSIGNED] [ZEROFILL] SMALLINT[(M)] [UNSIGNED] [ZEROFILL] MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] BIGINT[(M)] [UNSIGNED] [ZEROFILL] DECIMAL[(M[, D])] [UNSIGNED] [ZEROFILL] FLOAT(p) [UNSIGNED] [ZEROFILL] DOUBLE[(M, D)] [UNSIGNED] [ZEROFILL] DOUBLE PRECISION[(M, D)] [UNSIGNED] [ZEROFILL] REAL[(M, D)] [UNSIGNED] [ZEROFILL] 18

Wymagana pamięć dla typów liczbowych: Typ bajty Wartość minimalna Wartość maksymalna (Signed / Unsigned) TINYINT 1 -128 / 0 127 (27 -1) / 255 (28 -1) SMALLINT 2 -32768 / 0 32767 (215 -1) / 65535 (216 -1) MEDIUMINT 3 -8388608 / 0 8388607 (223 -1) / 16777215 (224 -1) INT 4 -2147483648 / 0 2147483647 (231 -1) / 4294967295 (232 -1) BIGINT 8 -9223372036854775808 9223372036854775807 / 0 (263 -1) / 18446744073709551615 (264 -1) 19

Wymagana pamięć dla typów liczbowych cd: FLOAT (p) 4 bajty jeżeli 0<=p<=24, 8 bajtów jeżeli 25<=p<=53 FLOAT 4 bajtów DOUBLE [PRECISION], pozycja REAL 8 bajtów DECIMAL(M, D), NUMERIC(M, D) M+2 bajtów jeżeli D > 0, M+1 bajtów jeżeli D = 0 (D+2, jeżeli M < D) 20

Wymagana pamięć dla typów daty i czasu: Typ Wymagana pamięć DATETIMESTAMP TIME YEAR 3 bytes 8 bytes 4 bytes 3 bytes 1 byte 21

Odmiany typów daty: Typ DATE jest stosowany dla dat i zawiera pola 'YYYY-MM-DD' w zakresie od '1000 -01 -01' do '9999 -12 -31'. Typ DATETIME jest stosowany dla dat z czasem i zawiera pola 'YYYY-MM-DD HH: MM: SS' w zakresie od '1000 -01 -01 00: 00' do '9999 -12 -31 23: 59'. Niewłaściwe wartości DATETIME, DATE, TIMESTAMP, TIME lub YEAR są zamieniane na ``zera'' odpowiednio: Typ kolumny DATETIME Wartości "zerowe" ‘ 0000 -00 -00 00: 00’ DATE ‘ 0000 -00 -00’ TIMESTAMP 0000000 TIME ‘ 00: 00' YEAR 0000 22

TIMESTAMP - znacznik czasu, zakres od '1970 -01 -01 00: 01' UTC do '2038 -01 -19 03: 14: 07' UTC. Wartości TIMESTAMP są przechowywane jako liczba sekund od epoki ( '1970 -01 -01 00: 00' UTC). Typ kolumny TIMESTAMP(14) TIMESTAMP(12) TIMESTAMP(10) Format wyświetlania YYYYMMDDHHMMSS YYMMDDHHMM TIMESTAMP(8) TIMESTAMP(6) TIMESTAMP(4) TIMESTAMP(2) YYYYMMDD YYMM YY 23

Funkcje dotyczące daty bieżącej: CURDATE() – podaje datę bieżącą w formatach: 'YYYY-MMDD' lub YYYYMMDD CURTIME() – podaje czas bieżący w formatach: 'HH: MM: SS' lub HHMMSS w zależności od kontekstu użycia znakowym lub numerycznym: mysql> select CURDATE(); +------+ | CURDATE() | +------+ | 2016 -03 -02 | +------+ mysql> select CURTIME(); +------+ | CURTIME() | +------+ | 08: 16: 06 | +------+ 24

mysql> select CURDATE()+0; +-------+ | CURDATE()+0 | +-------+ | 20160302 | +-------+ mysql> select Now(); +-----------+ | Now() | +-----------+ | 2016 -03 -02 08: 17: 21 | +-----------+ UTC_DATE() – synonim CURDATE() UTC_TIME() – synonim CURTIME() CURRENT_TIMESTAMP i CURRENT_TIMESTAMP() są synonimami NOW() 25

Definicja danych: Do utworzenia tabeli służy instrukcja CREATE TABLE, wymagająca podania nazwy tworzonej tabeli, nazwy każdej kolumny w tej tabeli, typu danych kolumn oraz maksymalnej długości danych w kolumnie. Składnia polecenia: CREATE TABLE nazwa_tabeli ( nazwa_kolumny typ_danych[(długość)opcje], nazwa_kolumny typ_danych[(długość) opcje, ]. . . ) [opcje_tabeli] [nawiasami kwadratowymi obejmujemy elementy opcjonalne]. 26

W My. SQL większość typów danych ma domyślne lub ustalone długości (DATE, TIME, YEAR, . . . ) lub długości maksymalne (TEXT, BLOB, . . . ), parametr długości można więc często pominąć. Opcje, które mogą wystąpić po określeniu typu i długości danych to np. NULL, NOT NULL, PRIMARY KEY, UNIQUE, DEFAULT wartość_domyślna. 27

Tworzenie tabeli Biuro: CREATE TABLE biuro ( biuro. Nr VARCHAR(4) NOT NULL, ulica VARCHAR(25) NOT NULL, miasto VARCHAR(25) NOT NULL, kod VARCHAR(6) NOT NULL, PRIMARY KEY (biuro. Nr) )ENGINE = Inno. DB DEFAULT CHARSET=utf 8 COLLATE=utf 8_polish_ci; 28

Opcje definicji kolumn: Opcje mogące wystąpić w definicji kolumny w instrukcji CREATE TABLE dzielą się na opcje ogólne, które mogą być stosowane do (prawie) wszystkich typów kolumn, i opcje szczególne, stosujące się do niektórych (klas) typów. Opcje ''szczególne'' muszą być podane w pierwszej kolejności (bezpośrednio po określeniu typu). Niektóre z tych opcji mogą wykluczać się wzajemnie, np. PRIMARY KEY i NULL. 29

Opcje dotyczące wszystkich typów: PRIMARY KEY (nazwa_kolumny [(długość)] [ASC | DESC], . . . ) określa daną kolumnę jako klucz główny tabeli. Tabela może posiadać tylko (co najwyżej) jeden klucz główny, o wartościach nie powtarzających się i różnych od NULL. Klucz główny w tabeli może być tylko jeden, ale może obejmować więcej niż jedną kolumnę. Wówczas cały zestaw wartości z odpowiednich kolumn traktowany jest jako wartość klucza głównego. 30

DEFAULT wartość_domyślna określa wartość domyślną kolumny dla nowo wprowadzanych wierszy w przypadku, gdy instrukcja tworząca nowy wiersz nie zadaje tej wartości. Jeśli w definicji kolumny pominięto opcję DEFAULT (oraz nie podano opcji NOT NULL), to w takich wypadkach wartością domyślną jest NULL. Jeżeli natomiat kolumna bez opcji DEFAULT została zadeklarowana jako NOT NULL, to w miejsce brakującej wartości zostanie automatycznie wprowadzona przez My. SQL wartość domyślna zależna od typu. 31

NOT NULL | NULL określa, czy NULL jest dopuszczalną wartością w tej kolumnie. Domyślnie wartość NULL jest dopuszczalna, za wyjątkiem kluczy (kolumn indeksowanych). AUTO_INCREMENT opcja ta ma sens dla kolumn o numerycznych typach wartości. Oznacza, że jeżeli przy tworzeniu wiersza nie zada się jawnie wartości dla tej kolumny (lub zostanie podana wartość NULL bądź zero), to wartością zapisaną będzie największa z wcześniej występujących w tej kolumnie powiększona o jeden. 32

Opcje dotyczące niektórych typów: UNSIGNED: do typów numerycznych i oznacza, że dopuszczone są wyłącznie wartości nieujemne. Zastosowanie jej zwiększa również zakres dopuszczalnych wartości (dodatnich) zależnie od konkretnego typu numerycznego. ZEROFILL: do typów opisujących liczby całkowite. Powoduje dopełnienie pola danych zerami do jego maksymalnej długości. BINARY: stosuje się do typów CHAR i VARCHAR. W My. SQL sortowanie i porównywanie wartości znakowych odbywa się domyślnie z utożsamieniem małych i wielkich liter (zgodnie z kodowaniem ISO-8859 -1). Opcja BINARY wyłącza to utożsamienie dla danej kolumny. 33

Deklaracje dodatkowe do definicji tabeli: W ciągu definicji kolumn w instrukcji CREATE TABLE mogą być ponadto umieszczone dodatkowe deklaracje, służące głównie do deklarowania indeksów, w tym kluczy złożonych (indeksów obejmujących więcej niż jedną kolumnę). {INDEX|KEY} [nazwa_indeksu] (nazwa_kolumny_indeksowej [(długość)] , . . ) deklaruje indeksowanie ze względu na wartości z odpowiednich kolumn. Indeks może być nazwany (zgodnie z wcześniej omówionymi regułami tworzenia nazw). Wartości klucza indeksowania nie mogą być NULL. 34

W My. SQL istnieje możliwość zadeklarowania długości klucza indeksowania, w postaci kolumna(długość), mniejszej niż długość pola danych odpowiedniej kolumny, do tworzenia indeksu wykorzystana jest jedynie część pola danych, co owocuje czasami o wiele mniejszymi i szybciej przeszukiwanymi indeksami. [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [nazwa_indeksu] (nazwa_kolumny_indeksowej, . . . ) deklaruje indeks o nie powtarzających się wartościach. Kolumna typu BLOB nie może być indeksowana. 35

Realizacja kluczy obcych w My. SQL: [CONSTRAINT [symbol]] FOREIGN KEY [nazwa_indeksu] (nazwa_kolumny_indeksowej, . . . ) REFERENCES nazwa_tabeli [(nazwa_kolumny_indeksowej, . . . )] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION RESTRICT oznacza że usuwana tabela, kolumna, perspektywa nie może być w danej chwili używana przez jakikolwiek obiekt w bazie. 36

Dodanie indeksu do istniejącej tabeli: CREATE [UNIQUE] INDEX nazwa_indeksu ON nazwa_tabeli (nazwa_kolumny[(length)] [ASC | DESC], . . . ) [USING {BTREE | HASH | RTREE}] Usuwanie indeksu: DROP INDEX nazwa_indeksu ON nazwa_tabeli Indeks przyspiesza operacje dostępu do danych w posortowanych według kolumn indeksowanych oraz wyszukiwania wierszy, zawierających dane z indeksowanych kolumn. Natomiast spowalnia wstawianie, usuwanie i zmiany wartości w indeksowanych kolumnach, ponieważ jego zawartość musi ulec zmianie w momencie zmiany zawartości tabeli. 37

Wyświetlanie zapisu tworzenia tabeli: mysql> show create table biuroG ************* 1. row ************* Table: biuro Create Table: CREATE TABLE `biuro` ( `biuro. Nr` varchar(4) NOT NULL, `ulica` varchar(25) NOT NULL, `miasto` varchar(25) NOT NULL, `kod` varchar(6) NOT NULL, PRIMARY KEY (`biuro. Nr`) ) ENGINE=Inno. DB DEFAULT CHARSET=utf 8 COLLATE= utf 8 _polish_ci 1 row in set (0. 00 sec) mysql> 38

Prezentacja struktury tabeli: mysql> DESCRIBE biuro; +-------------+-----+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----+-------+ | biuro. Nr | varchar(4) | NO | PRI | NULL | | | ulica | varchar(25) | NO | | NULL | | | miasto | varchar(25) | NO | | NULL | | | kod | varchar(6) | NO | | NULL | | +-------------+-----+-------+ 4 rows in set (0. 01 sec) mysql> Polecenie równoważne: SHOW COLUMNS FROM biuro; 39

Rozszerzona prezentacja struktury tabeli: mysql> SHOW FULL COLUMNS FROM biuroG ************** 1. row ************** Field: biuro. Nr Type: varchar(4) Collation: utf 8_polish_ci Null: NO Key: PRI Default: NULL Extra: Privileges: select, insert, update, references Comment: ************** 2. row ************** Field: ulica Type: varchar(25) Collation: utf 8_polish_ci Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references 40 Comment:

************** 3. row ************** Field: miasto Type: varchar(25) Collation: utf 8_polish_ci Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references Comment: ************** 4. row ************** Field: kod Type: varchar(6) Collation: utf 8_polish_ci Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references Comment: 41

Wyświetlanie indeksów tabeli: mysql> SHOW INDEX FROM biuroG ************* 1. row ************* Table: biuro Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: biuro. Nr Collation: A Cardinality: 7 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0. 00 sec) 42

Modyfikacja struktury tabel: Modyfikowanie struktur tabel już istniejących (i wypełnionych danymi) w sensie: - dodawania, usuwania kolumn, indeksów, - zmiany definicji kolumn, - zmiany nazwy tabeli ALTER [IGNORE] TABLE nazwa_tabeli operacja 1[, operacja 2, . . . ] gdzie możliwe operacje modyfikujące: 43

ADD [COLUMN] definicja_kolumny [FIRST|AFTER nazwa_kolumny]: stworzenie nowej kolumny, według składni instrukcji CREATE TABLE - domyślnie kolumna umieszczana jest jako ostatnia (opcje FIRST|AFTER w określonym miejscu - rozszerzenie My. SQL)). ADD [CONSTRAINT [symbol]] PRIMARY KEY (nazwa_kolumny_indeksowej[(długość)] [ASC | DESC], . . . ) [index_type] ADD {INDEX|KEY} [nazwa_indeksu] [index_type] (nazwa_kolumny_indeksowej[(długość)] [ASC | DESC], . . . ) 44

ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [nazwa_indeksu] [index_type] (nazwa_kolumny_indeksowej[(długość)] [ASC | DESC], . . . ) ADD [CONSTRAINT [symbol]] FOREIGN KEY [nazwa_indeksu] (nazwa_kolumny_indeksowej, . . . ) REFERENCES nazwa_tabeli [(nazwa_kolumny_indeksowej, . . . )] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION 45

ALTER [COLUMN] nazwa_kolumny { SET DEFAULT wartość | DROP DEFAULT } – zmiana (lub usunięcie) wartości domyślnej z definicji istniejącej kolumny. CHANGE [COLUMN] stara_nazwa_kolumny nowa_nazwa_kolumny definicja_kolumny [FIRST|AFTER nazwa_kolumny] – umożliwia zmianę definicji istniejącej kolumny (nazwy) - (rozszerzenie My. SQL). MODIFY [COLUMN] nazwa_kolumny definicja_kolumny [FIRST|AFTER nazwa_kolumny] – umożliwia zmianę definicji istniejącej kolumny (właściwości). 46

DROP [COLUMN] nazwa_kolumny – usuwa kolumnę (i zapisane w niej dane), ewentualnie modyfikując (lub usuwając) indeksy dla których kluczem lub częścią klucza była usunięta kolumna. DROP PRIMARY KEY – usuwa klucz główny (jako klucz indeksowania, nie jako kolumnę), lub, w przypadku braku klucza głównego w tabeli, pierwszy w kolejności indeks typu UNIQUE. DROP {INDEX|KEY} nazwa_indeksu - usuwa indeks DROP FOREIGN KEY nazwa – usuwa klucz obcy 47

RENAME [TO] nowa_nazwa_tabeli – zmiana nazwy tabeli. CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] 48

Opcjonalne słowo COLUMN w niektórych instrukcjach może być dodawane dla czytelności programu, nie ma ono wpływu na znaczenie instrukcji. Opcja IGNORE (rozszerzenie My. SQL) dotyczy sytuacji, gdy modyfikacja struktury tabeli doprowadziłaby do powtarzania się wartości kluczy UNIQUE (w tym klucz głównego). Bez opcji IGNORE operacja taka zwraca błąd (modyfikacje zostają cofnięte), przy jej zastosowaniu w tabeli zmodyfikowanej zostanie zapisany tylko jeden (pierwszy w kolei) spośród wierszy odpowiadających powtórzonym wartościom klucza. 49

Usuwanie tabel: DROP TABLE tabela 1, . . . powoduje trwałe usunięcie zarówno danych zapisanych w tabelach, jak i ich definicji. Używać ostrożnie Zmiana nazwy tabeli bezpośrednio: RENAME TABLE stara_nazwa_tabeli TO nowa_nazwa_tabeli [, stara_nazwa_tabeli 2 TO nowa_nazwa_tabeli 2]. . . - jednej lub więcej. 50

Wprowadzanie danych instrukcją INSERT : W najprostszej postaci instrukcja INSERT służy wprowadzeniu do tabeli pojedynczego wiersza danych: INSERT [IGNORE] [INTO] nazwa_tabeli [ (kolumna 1, . . . ) ] VALUES (wyrażenie 1, . . . ), (wyrażenie 12, . . . ), . . . Jeżeli nazwy kolumn, do których wstawiamy wartości podanych wyrażeń nie zostaną podane jawnie, to wartości te zostaną wpisane do kolejnych kolumn w takim porządku, w jakim kolumny te były zdefiniowane instrukcją CREATE TABLE. Pola danych w tych kolumnach, dla których nie podano wartości otrzymają wartości domyślne (zdefiniowane jawnie w instrukcji CREATE TABLE lub automatyczne, np. napis pusty dla pól napisowych). 51

Szczególne zachowanie dotyczy kolumn zadeklarowanych jako AUTO_INCREMENT oraz typu TIMESTAMP (w tym ostatnim przypadku pole kolumny otrzyma wartość odpowiadającą czasowi operacji, o ile nie podamy jawnie innej wartości - innej niż NULL). Druga postać instrukcji INSERT pozwala skopiować do tabeli wiersze wybrane z innych tabel: INSERT INTO tabela [ (kolumna 1, . . . ) ] SELECT. . . 52

mysql> INSERT INTO biuro (biuro. Nr, ulica, miasto, kod) VALUES ('B 001', 'Piękna 46', 'Białystok', '15 -900'); Query OK, 1 row affected (0. 06 sec) mysql> SELECT * FROM biuro; +-----------+------+----+ | biuro. Nr | ulica | miasto | kod | +-----------+------+----+ | B 001 | Piękna 46 | Białystok | 15 -900 | +-----------+------+----+ 1 row in set (0. 00 sec) 53

mysql> INSERT INTO biuro VALUES ('B 002', 'Cicha 56', 'Łomża', '18 -400'); Query OK, 1 row affected (0. 05 sec) mysql> SELECT * FROM biuro; +-----------+------+----+ | biuro. Nr | ulica | miasto | kod | +-----------+------+----+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | +-----------+------+----+ 2 rows in set (0. 00 sec) 54

mysql> INSERT INTO biuro 2 biuro. Nr, miasto SELECT biuro. Nr, miasto FROM biuro; Query OK, 1 row affected (0. 05 sec) mysql> SELECT * FROM biuro 2; +-----------+ | biuro. Nr | miasto | +-----------+ | B 001 | Białystok | | B 002 | Łomża | +-----------+ 2 rows in set (0. 00 sec) 55

Usuwanie wierszy instrukcją DELETE: Do specyfikacji wierszy, które mają być usunięte służy klauzula WHERE: DELETE FROM nazwa_tabeli [WHERE warunki] Bez klauzuli WHERE operacja powoduje usunięcie wszystkich wierszy z tabeli. Jeżeli chcemy mieć pewność, że usunięty zostanie tylko jeden określony wiersz, to warunek powinien dotyczyć wartości klucza głównego (lub jakiegoś klucza zadeklarowanego jako UNIQUE). 56

Usunięcie biura o numerze B 002: mysql> DELETE FROM biuro WHERE biuro. Nr='B 002'; SELECT * FROM biuro; mysql> SELECT * FROM biuro; +------------+------+----+ | biuro. Nr | ulica | miasto | kod | +------------+------+----+ | B 001 | Piękna 46 | Białystok | 15 -900 | +------------+------+----+ 1 row in set (0. 00 sec) Usunięcie wszystkich biur: mysql> DELETE FROM biuro; mysql> SELECT * FROM biuroł Empty set (0. 00 sec) 57

Wprowadzanie poleceń z pliku : Plik z odpowiednimi poleceniami zapisujemy w katalogu bin naszego My. SQL'a np. pod nazwą biuro. sql. Uruchamiamy komendą: SOURCE biuro. sql Jeżeli plik zapisaliśmy w innym miejscu niż katalog bin np. na pulpicie komenda wtedy wygląda np. tak: SOURCE c: windowspulpitbiuro. sql 58

Przykładowa zawartość: CREATE DATABASE IF NOT EXISTS Biuro DEFAULT CHARACTER SET utf 8 DEFAULT COLLATE utf 8_polish_ci; CONNECT biuro; CREATE TABLE IF NOT EXISTs biuro ( biuro. Nr varchar(4) NOT NULL, ulica varchar(25) NOT NULL, miasto varchar(25) NOT NULL, kod varchar(6) NOT NULL, PRIMARY KEY (biuro. Nr) ) ENGINE=Inno. DB DEFAULT CHARSET=utf 8 COLLATE=utf 8_polish_ci; INSERT INTO biuro (biuro. Nr, ulica, miasto, kod) VALUES ('B 001', 'Piękna 46', 'Białystok', '15 -900'); INSERT INTO biuro VALUES ('B 002', 'Cicha 56', ‘Łomża', '18 -400'); 59

mysql> source biuro. sql Query OK, 1 row affected (0. 02 sec) Connection id: 1 Current database: biuro Query OK, 0 rows affected (0. 06 sec) Query OK, 1 row affected (0. 03 sec) Query OK, 1 row affected (0. 02 sec) mysql> 60

Wyświetlenie zawartości tabeli: mysql> SELECT * FROM biuro; +-----------+------+----+ | biuro. Nr | ulica | miasto | kod | +-----------+------+----+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | +-----------+------+----+ 2 rows in set (0. 00 sec) 61

mysql> INSERT INTO biuro VALUES ('B 003', 'Mała 63', 'Białystok', '15 -900'), ('B 004', 'Miodowa 32', 'Grajewo', '19 -300'), ('B 005', 'Dobra 22', ‘Łomża', '18 -400'), ('B 006', 'Słoneczna 55', 'Białystok', '15 -900'), ('B 007', 'Akacjowa 16', 'Augustów', '16 -300'); Query OK, 1 row affected (0. 05 sec) mysql> SELECT * FROM biuro; +---------------+--------+ | biuro. Nr | ulica | miasto | kod | +---------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 004 | Miodowa 32 | Grajewo | 19 -300 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | | B 007 | Akacjowa 16 | Augustów | 16 -300 | +---------------+--------+ 7 rows in set (0. 00 sec) 62

63

Modyfikowanie danych: UPDATE [IGNORE] nazwa_tabeli SET kolumna 1=wartość1 [, kolumna 2=wartość2, . . . ] [WHERE warunek_selekcji] [LIMIT liczba_wierszy]; bez klauzuli WHERE wszystkie wiersze kolumny będą tak samo zmodyfikowane. 64

mysql> SELECT * FROM biuro; +---------------+--------+ | biuro. Nr | ulica | miasto | kod | +---------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 004 | Miodowa 32 | Grajewo | 19 -000 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | | B 007 | Akacjowa 16 | Augustów | 16 -300 | +---------------+--------+ 7 rows in set (0. 00 sec) 65

mysql> UPDATE biuro SET kod=’ 19 -200’ WHERE miasto=‘Grajewo'; Query OK, 1 row affected (0. 05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM biuro; +---------------+--------+ | biuro. Nr | ulica | miasto | kod | +---------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 004 | Miodowa 32 | Grajewo | 19 -200 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | | B 007 | Akacjowa 16 | Augustów | 16 -300 | +---------------+--------+ 7 rows in set (0. 00 sec) 66

Tworzymy drugą powiązaną kluczem obcym tabelę: mysql> -> -> -> CREATE TABLE IF NOT EXISTS personel ( personel. Nr varchar(4) NOT NULL, imie varchar(25) NOT NULL, nazwisko varchar(25) NOT NULL, stanowisko varchar(25) NOT NULL, plec enum('K', 'M') NOT NULL, data. Ur date NOT NULL, pensja smallint(4) unsigned NOT NULL, biuro. Nr varchar(4) NOT NULL, PRIMARY KEY (personel. Nr), KEY biuro. Nr (biuro. Nr), CONSTRAINT biuro. Nr FOREIGN KEY (biuro. Nr) REFERENCES biuro (biuro. Nr) ON UPDATE CASCADE ON DELETE CASCADE -> ) ENGINE=Inno. DB DEFAULT CHARSET=utf 8 COLLATE=utf 8_polish_ci; Query OK, 0 rows affected (0. 08 sec) KEY `biuro. Nr` (`biuro. Nr`) – indeksy do kluczy obcych są tworzone w My. SQL Administrator automatycznie 67

Wyświetlanie zapisu tworzenia tabeli: mysql> SHOW CREATE TABLE personelG ************* 1. row ************* Table: personel Create Table: CREATE TABLE `personel` ( `personel. Nr` varchar(4) COLLATE utf 8_polish_ci NOT NULL, `imie` varchar(25) COLLATE utf 8_polish_ci NOT NULL, `nazwisko` varchar(25) COLLATE utf 8_polish_ci NOT NULL, `stanowisko` varchar(25) COLLATE utf 8_polish_ci NOT NULL, `plec` enum('K', 'M') COLLATE utf 8_polish_ci NOT NULL, `data. Ur` date NOT NULL, `pensja` smallint(4) unsigned NOT NULL, `biuro. Nr` varchar(4) COLLATE utf 8_polish_ci NOT NULL, PRIMARY KEY (`personel. Nr`), KEY `biuro. Nr` (`biuro. Nr`), CONSTRAINT `biuro. Nr` FOREIGN KEY (`biuro. Nr`) REFERENCES `biuro` (`biuro. Nr`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=Inno. DB DEFAULT CHARSET=utf 8 COLLATE=utf 8_polish_ci 1 row in set (0. 02 sec) 68

Prezentacja struktury tabeli: mysql> DESCRIBE personel; +-----------------+------+-----+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------+-----+-------+ | numer | varchar(4) | NO | PRI | NULL | | | imie | varchar(25) | NO | | NULL | | | nazwisko | varchar(25) | NO | | NULL | | | stanowisko | varchar(25) | NO | | NULL | | | plec | enum('K', 'M') | NO | | NULL | | | data. Ur | date | NO | | NULL | | | pensja | smallint(4) unsigned | NO | | NULL | | | biuro. Nr | varchar(4) | NO | MUL | NULL | | +-----------------+------+-----+-------+ 8 rows in set (0. 02 sec) Polecenie równoważne: SHOW COLUMNS FROM personel; 69

Rozszerzona prezentacja struktury tabeli: mysql> SHOW FULL COLUMNS FROM personelG ************** 1. row ************** Field: personel. Nr Type: varchar(4) Collation: utf 8_polish_ci Null: NO Key: PRI Default: NULL Extra: Privileges: select, insert, update, references Comment: ************** 2. row ************** Field: imie Type: varchar(25) Collation: utf 8_polish_ci Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references 70 Comment:

************** 3. row ************** Field: nazwisko Type: varchar(25) Collation: utf 8_polish_ci Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references Comment: ************** 4. row ************** Field: stanowisko Type: varchar(25) Collation: utf 8_polish_ci Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references Comment: 71

************** 5. row ************** Field: plec Type: enum('K', 'M') Collation: utf 8_polish_ci Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references Comment: ************** 6. row ************** Field: data. Ur Type: date Collation: NULL Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references Comment: 72

************** 7. row ************** Field: pensja Type: smallint(4) unsigned Collation: NULL Null: NO Key: Default: NULL Extra: Privileges: select, insert, update, references Comment: ************** 8. row ************** Field: biuro. Nr Type: varchar(4) Collation: utf 8_polish_ci Null: NO Key: MUL Default: NULL Extra: Privileges: select, insert, update, references Comment: 8 rows in set (0. 02 sec) 73

Wyświetlanie indeksów tabeli personel: mysql> SHOW INDEX FROM personelG ************* 1. row ************* Table: personel Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: personel. Nr Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: ************* 2. row ************* Table: personel Non_unique: 1 Key_name: biuro. Nr Seq_in_index: 1 Column_name: biuro. Nr Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 2 rows in set (0. 02 sec) 74

Powiązania kluczy obcych: 75

Wprowadzanie danych z pliku w My. SQL: Często mamy do czynienia z taką sytuacją, że posiadamy już dane które chcemy wprowadzić do bazy danych, zapisane w pliku lub plikach tekstowych postaci , , rekord-pola''. Plik, z którego chcemy wprowadzić dane do tabeli My. SQL musi się znajdować na dysku widzianym jako lokalny przez serwer bazy danych. Najlepiej, jeżeli wierszom tabeli odpowiadają rekordy pliku (domyślnie: linijki tekstu), a wartościom w kolumnach - pola rekordów (w ustalonej kolejności). 76

LOAD DATA INFILE 'plik' [ REPLACE | IGNORE ] INTO TABLE nazwa_tabeli [CHARACTER SET charset_name] [ FIELDS [ TERMINATED BY 't'] [ [OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\']] [ LINES [STARTING BY ''] [TERMINATED BY 'n']] [ IGNORE number LINES ] [(pole 1, pole 2, . . . )] Domyślnie separatorem pól jest znak tabulacji (t), a każdy rekord zakończony jest znakiem nowej linii (n). 77

STARTING BY – od jakich znaków zaczyna się linia w naszym pliku (ignorujemy niektóre znaki na początku linii); IGNORE number LINES – ignorujemy kilka początkowych linii np. nagłówka; Opcje REPLACE i IGNORE dotyczą sposobu potraktowania rekordów z pliku, których wprowadzenie spowodowałoby duplikację wartości kluczy UNIQUE (w tym klucza głównego). Przy REPLACE nowo wczytany rekord zastępuje wiersze o kolidujących wartościach kluczy, przy IGNORE - rekord taki zostanie zignorowany (pominięty). 78

W wypadku, gdy nie podano żadnej z tych opcji, kolizja wartości klucza wywoła błąd i spowoduje przerwanie wczytywania danych. Podanie nazw pól służy przyporządkowaniu kolejnych pól rekordów kolumnom tabeli. Zazwyczaj można pominąć wszystkie opcje dotyczące separatorów pól i rekordów, jeżeli plik wejściowy został właściwie przygotowany. 79

Np. dla pliku zlokalizowanego C: My. SQL 5databiuropersonel. txt o zawartości: SA 8 SA 9 SB 20 SB 21 SB 22 SB 23 SB 30 SB 31 SB 32 SG 20 SG 21 SL 20 SL 21 SL 22 SL 30 SL 31 SL 32 Katarzyna Maria Sabina Daniel Małgorzata Anna Katarzyna Dawid Małgorzata Karolina Piotr Paweł Monika Jan Julia Michał Morawska Hojna Bober Frankowski Kowalska Biały Michalska Piotrowski Plichta Mucha Cybulski Nowak Kowalski Munk Wiśniewski Lisicka Brzęczyk kierownik asystent dyrektor kierownik asystent dyrektor asystent kierownik asystent dyrektor asystent K K K M M M K M 1971 -5 -6 1970 -2 -19 1940 -6 -3 1958 -3 -24 1972 -3 -15 1960 -11 -10 1960 -11 -17 1975 -3 -22 1971 -10 -3 1953 -3 -3 1974 -12 -6 1962 -2 -2 1969 -5 -5 1977 -7 -26 1945 -10 -1 1965 -7 -13 1959 -3 -15 1700 900 2400 1800 1000 1200 2500 1100 1200 2200 1300 1500 1000 1100 3000 900 1000 B 007 B 003 B 006 B 004 B 002 B 005 80

zapiszemy: mysql>LOAD DATA INFILE ‘personel. txt' INTO TABLE personel; lub w innej lokalizacji: mysql>LOAD DATA INFILE 'C: /My. SQL 5/bin/personel. txt‘ personel; INTO TABLE W My. SQL jako znaku separacji w ścieżce dostępu używamy '/' lub '\' mysql>LOAD DATA INFILE 'C: \My. SQL 5\bin\ personel. txt' INTO TABLE personel; 81

Wyprowadzanie wyników zapytania do pliku: Istnieje też możliwość zapisania tabeli wynikowej instrukcji SELECT do pliku tekstowego, który później można wykorzystać w instrukcji LOAD DATA INFILE SELECT. . . INTO OUTFILE 'nazwa_pliku' [FIELDS [TERMINATED BY 't'] [ESCAPED BY '\']] [LINES [TERMINATED BY 'n']] FROM nazwa_tabeli Podobnie jak w LOAD DATA INFILE opcje dotyczące formatu pliku są zbyteczne, jeśli akceptujemy wartości domyślne (linijka=rekord, pola przedzielone kodem tabulacji). Podanie nazwy już istniejącego pliku spowoduje błąd. Zaleca się podawanie nazwy pliku wynikowego w postaci pełnej ścieżki. 82

np. dla tabeli biuro o zawartości: mysql> SELECT * FROM biuro; +---------------+--------+ | biuro. Nr | ulica | miasto | kod | +---------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 004 | Miodowa 32 | Grajewo | 19 -200 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | | B 007 | Akacjowa 16 | Augustów | 16 -300 | +---------------+--------+ 83

Polecenie: mysql> SELECT * INTO OUTFILE 'C: /tmp/biuro. txt' FROM biuro; spowoduje utworzenie pliku biuro. txt o zawartości: B 001 B 002 B 003 B 004 B 005 B 006 B 007 Piękna 46 Cicha 56 Mała 63 Miodowa 32 Dobra 22 Słoneczna 55 Akacjowa 16 Białystok Łomża Białystok Grajewo Łomża Białystok Augustów 15 -900 18 -400 15 -900 19 -200 18 -400 15 -900 16 -300 84

Kontrola integralności danych : Polega na przestrzeganiu więzów, które nakładamy na dane, aby uniknąć niespójnego stanu bazy danych. Mamy do dyspozycji pięć rodzajów więzów integralności, które mogą być zdefiniowane w poleceniach CREATE i ALTER TABLE: 1. Dane wymagane (NOT NULL); W standardzie ISO (również w My. SQL) możemy zdefiniować więzy danych wymaganych poprzez umieszczenie przy kolumnie klauzuli NOT NULL. Klauzulą domyślną w standardzie ISO jest dopuszczanie wartości pustych. 85

Kontrola integralności danych : 2. Więzy dziedzinowe (dopuszczalny zbiór wartości atrybutu, dopuszczalny zakres długości i format atrybutu); Z każdą kolumną związana jest dziedzina, czyli zbiór dopuszczalnych wartości. Standard ISO opisuje dwa mechanizmy określania dziedziny w poleceniach CREATE i ALTER TABLE. Pierwszy z nich to klauzula CHECK, która pozwala ograniczyć dopuszczalne wartości dla konkretnej kolumny lub dla całej tabeli: CHECK (warunek_selekcji) W więzach kolumnowych w klauzuli CHECK można odwoływać się jedynie do kolumny, dla której jest zdefiniowana klauzula, np. : plec CHAR NOT NULL CHECK (plec IN ('M', 'K')) 86

Standard ISO zezwala również na niezależne od tabel definiowanie dziedzin za pomocą polecenia CREATE DOMAIN: CREATE DOMAIN Nazwa. Dziedziny [AS] typ_danych [DEFAULT wartość_domyślna] [CHECK (warunek_selekcji)] W ten sposób dziedzinie przypisuje się nazwę Nazwa. Dziedziny, typ danych i, opcjonalnie, wartość domyślną. Również opcjonalnie można zdefiniować regułę poprawności dla dziedziny za pomocą klauzuli CHECK, np. : CREATE DOMAIN Rodzaj AS CHAR DEFAULT 'M' CHECK (VALUE IN ('M', 'K')); Teraz, definiując kolumnę, możemy zastosować nazwę dziedziny Rodzaj zamiast typu danych CHAR: plec Rodzaj NOT NULL 87

Parametr warunek_selekcji może wymagać odwołania się do innej tabeli. Na przykład, możemy stworzyć dziedzinę Numer. Biura, by upewnić się, że wprowadzane wartości odpowiadają numerom biur występującym w tabeli Biuro: CREATE DOMAIN Numer. Biura AS CHAR(4) CHECK (VALUE IN (SELECT biuro. Nr FROM Biuro)); Zazwyczaj lepiej jest definiować więzy dziedzinowe za pomocą polecenia CREATE DOMAIN niż tylko CHECK. Oba polecenia nie funkcjonują jednak w My. SQL. 88

Usuwanie dziedziny z bazy danych: DROP DOMAIN Nazwa. Dziedziny [RESTRICTI CASCADE] Zasięg usunięcia, RESTRICT lub CASCADE, określa tryb postępowania w przypadku, gdy usuwamy używaną aktualnie dziedzinę. RESTRICT - jeśli dziedzina jest wykorzystywana w istniejącej tabeli, perspektywie lub asercji, to usunięcie nie zostanie wykonane. CASCADE - w każdej kolumnie tabeli, dla której jako typ danych określono usuwaną dziedzinę, typ ten zostanie zamieniony na typ, w oparciu, o który jest zdefiniowana usuwana dziedzina. Także wartość domyślna i więzy kolumny zostaną zastąpione wartością domyślną i więzami usuwanej dziedziny, o ile występowały w definicji dziedziny. 89

Kontrola integralności danych : 3. Integralność encji - każda tabela musi posiadać klucz główny, a wartości klucza głównego muszą być w ramach tabeli unikalne i nie równe NULL; Standard ISO pozwala kontrolować integralność encji za pomocą klauzuli PRIMARY KEY w poleceniach CREATE i ALTER TABLE np. : PRIMARY KEY(nieruchomośćNr) Aby zdefiniować złożony klucz główny, podajemy nazwy kilku kolumn oddzielone przecinkami: PRIMARY KEY(klient. Nr, nieruchomośćNr) Klauzula PRIMARY KEY może wystąpić tylko raz w definicji tabeli. 90

Istnieje też możliwość zapewnienia unikalności wartości kluczy alternatywnych słowem kluczowym UNIQUE (musi być także jako NOT NULL) W tabeli może wystąpić tyle klauzul UNIQUE, ile jest potrzebnych. SQL odrzuci każdą operację INSERT i UPDATE, która będzie powodowała utworzenie duplikatu wartości dowolnego klucza kandydującego (głównego lub alternatywnego). Na przykład, dla tabeli Wizyta : klient. Nr VARCHAR{5) NOT NULL, nieruchomośćNr VARCHAR(5) NOT NULL, UNIQUE (klient. Nr, nieruchomośćNr) 91

Kontrola integralności danych : 4. Integralność referencyjna - każda wartość klucza obcego może być albo równa jakiejś wartości klucza głównego występującej w tabeli powiązanej, lub (ewentualnie) NULL Klucz obcy to kolumna lub zbiór kolumn, łączących każdy wiersz tabeli podrzędnej, w której występują, z wierszem tabeli nadrzędnej zawierającym wartość klucza kandydującego równą ich wartości. Integralność referencyjna oznacza, że jeśli w polach klucza obcego występują pewne wartości, to muszą odnosić się one do istniejącego, poprawnego wiersza w tabeli nadrzędnej. Standard ISO pozwala definiować klucze obce za pomocą klauzuli FOREIGN KEY w poleceniach CREATE i ALTER TABLE. 92

Zdefiniowanie klucza obcego biuro. Nr w tabeli Nieruchomość: FOREIGN KEY(biuro. Nr) REFERENCES Biuro ON DELETE reference_option ON UPDATE reference_option W podklauzuli PREFERENCES podaje się nazwę tabeli nadrzędnej oraz nazwę klucza głównego (w przypadku braku wpisu system przyjmuje domyślnie klucz główny tabeli nadrzędnej). SQL odrzuci każdą operacje INSERT lub UPDATE, jeżeli w jej następstwie powstałaby wartość klucza obcego, do której nie pasuje żadna wartość klucza kandydującego w tabeli nadrzędnej. 93

W SQL są określone cztery tryby postępowania w przypadku, gdy użytkownik próbuje usunąć wiersz z tabeli nadrzędnej, a w tabeli podrzędnej istnieją związane z nim wiersze: • CASCADE – usunięcie wiersza z tabeli nadrzędnej automatycznie powoduje usunięcie związanych z nim wierszy z tabeli podrzędnej. • SET NULL – usunięcie wiersza z tabeli nadrzędnej i zastąpienie wartości klucza obcego w jego wierszach podrzędnych wartością pustą. 94

• SET DEFAULT – usuniecie wiersza z tabeli nadrzędnej i zastąpienie wartości klucza obcego w jego wierszach podrzędnych wartością domyślną. • NO ACTION - zaniechanie operacji usunięcia z tabeli nadrzędnej. Jest to domyślny tryb postępowania, przyjmowany, gdy w definicji nie występuje reguła ON DELETE. SQL umożliwia takie same operacje w przypadku, gdy klucz główny w tabeli nadrzędnej zostanie zmodyfikowany. 95

Powiązania kluczy obcych: 96

Próba wykonania polecenia z nieistniejącą wartością w tabeli nadrzędnej: mysql> INSERT INTO personel VALUES ('SL 33', 'Paweł', 'Biały', 'asystent', 'M', '1982 -03 -16', 1100, 'B 008'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`biuro`. `personel`, CONSTRAINT `biuro. Nr` FOREIGN KEY (`biuro. Nr`) REFERENCES `biuro` (`biuro. Nr`) ON DELETE CASCADE ON UPDATE CASCADE) mysql> 97

Aktualizacja obiektów w tabeli nadrzędnej: mysql> UPDATE biuro SET biuro. Nr='B 008' WHERE miasto='Augustów' and ulica='Akacjowa 16'; Query OK, 1 rows affected (0. 03 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> SELECT * FROM biuro; +--------------+--------+ | biuro. Nr | ulica | miasto | kod | +--------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 004 | Miodowa 32 | Grajewo | 19 -200 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | | B 008 | Akacjowa 16 | Augustów | 16 -300 | +--------------+--------+ 7 rows in set (0. 02 sec) 98

mysql> SELECT * FROM personel; 99

Usuwanie obiektów z tabeli nadrzędnej: mysql> DELETE FROM biuro WHERE ulica='Akacjowa 16'; Query OK, 1 row affected (0. 05 sec) mysql> SELECT * FROM biuro; +--------------+--------+ | biuro. Nr | ulica | miasto | kod | +--------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 004 | Miodowa 32 | Grajewo | 19 -200 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | +--------------+--------+ 6 rows in set (0. 00 sec) 100

mysql> SELECT * FROM personel; Kolejność usuwania tabel powiązanych (powiązane, potem nadrzędne): DROP TABLE personel, biuro; 101

Kontrola integralności danych : 5. Więzy ogólne - dodatkowe warunki poprawności danych określone przez użytkowników lub administratorów bazy danych Możliwości zmian w tabelach mogą być ograniczone więzami ogólnymi (nazywanymi w SQL asercjami) rządzącymi przebiegiem rzeczywistych transakcji, z których takie zmiany wynikają. Standard ISO pozwala specyfikować więzy ogólne za pomocą klauzul CHECK i UNIQUE w poleceniach CREATE i ALTER TABLE oraz za pomocą polecenia CREATE ASSERTION: CREATE ASSERTION nazwa_asercji CHECK (warunek_selekcji) 102

Np. zdefiniować warunek zapobiegający jednoczesnemu nadzorowaniu przez pracownika więcej niż stu nieruchomości: CREATE ASSERTION Pracownik. Nadzorujący. Nie. Za. Dużo CHECK (NO EXISTS (SELECT pracownik. Nr FROM Nieruchomość GROUP BY pracownik. Nr HAVING COUNT(*)>100)); 103

Przykład realizacji więzów ogólnych: Dla tabeli Nieruchomosc utworzono dziedziny: CREATE DOMAIN Numery. Wlascieli AS VARCHAR(5) CHECK (VALUE IN (SELECT wlasciciel. Nr FROM Wlasciciel. Prywatny)); CREATE DOMAIN Numery. Pracownikow AS VARCHAR(5) CHECK (VALUE IN (SELECT pracownik. Nr FROM Personel)); CREATE DOMAIN Numery. Biur AS CHAR(4) CHECK (VALUE IN (SELECT biuro. Nr FROM Biuro)); CREATE DOMAIN Numery. Nieruchomosci AS VARCHAR(5); 104

CREATE DOMAIN Ulice AS VARCHAR(25); CREATE DOMAIN Miasta AS VARCHAR(15): CREATE DOMAIN Kody. Pocztowe AS VARCHAR(8); CREATE DOMAIN Typy. Nieruchomosci AS CHAR(l) CHECK(VALUE IN ('B', 'C', 'D', 'E', 'F', 'M', 'S')); CREATE DOMAIN Pokoje. WNieruchomos AS SMALLINT CHECK(VALUE BETWEEN 1 AND 15); CREATE DOMAIN Oplaty. Za. Wynajecie AS DECIMAL(6, 2) CHECK(VALUE BETWEEN 0 AND 9999. 99); 105

Realizacja tabeli Nieruchomosc: CREATE TABLE Nieruchomosc ( nieruchomosc. Nr Numery. Nieruchomosci ulica Ulice miasto Miasta kod_pocztowy Kody. Pocztowe, typ Typy. Nieruchomosci DEFAULT 'M', pokoje Pokoje. WNieruchomos DEFAULT 4, czynsz Oplaty. Za. Wynajecie DEFAULT 600, wlasciciel. Nr Numery. Wlascicieli NOT NULL, NOT NULL, 106

pracownik. Nr Numery. Pracownikow CONSTRAINT Pracownik. Nadzorujacy. Nie. Za. Duzo CHECK (NOT EXISTS (SELECT pracownik. Nr FROM Nieruchomosc GROUP BY pracownik. Nr HAYING COUNT{*) > 100)), Biuro. Nr Numery. Biur NOT NULL, PRIMARY KEY (nieruchomosc. Nr), FOREIGN KEY (pracownik. Nr) REFERENCES Personel ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (wlasciciel. Nr) REFERENCES Wlasciciel Prywatny ON DELETE NO ACTION ON UPDATE CASCADE, FOREIGN KEY (biuro. Nr) REFERENCES Biuro ON DELETE NO ACTION ON UPDATE CASCADE); 107