POWTÓRZENIE

1. POWTÓRZENIE • Główne elementy składni SQL: • Zasady ogólne • Podstawowe polecenia definicji danych SQL (tworzenie, usuwanie, modyfikacja) • Zbiory znaków i zestawienia w MySQL • Połączenie z bazą • Kontrola dostępu • Typy danych

2. 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_kolumnytyp_danych[(długość)opcje], nazwa_kolumny typ_danych[(długość) opcje,] ...) [opcje_tabeli] (nawiasami kwadratowymi obejmujemy elementy opcjonalne).

3. W MySQL 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.

4. Tworzenie tabeli Biuro:

5. 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). Oczywiście niektóre z tych opcji wykluczają się wzajemnie, np. PRIMARY KEY i NULL.

6. Opcje dotyczące wszystkich typów: PRIMARY KEY 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.

7. 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 MySQL wartość domyślna zależna od typu.

8. 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 ona, ż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.

9. Opcje dotyczące niektórych typów: UNSIGNED: opcja ta stosuje się 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) w sposób zależny od konkretnego typu numerycznego. ZEROFILL: stosuje się 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 MySQL 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.

10. 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ę). [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (nazwa_kolumny [(dłudość)] [ASC | DESC],...) index_type: USING {BTREE | HASH | RTREE}

11. {INDEX|KEY} [nazwa_indeksu] (nazwa_kolumny_indeksowej [(dłudość)] , ..) [index_type] 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. W MySQL istnieje możliwość zadeklarowania długości klucza indeksowania, w postaci kolumna(długość), mniejszej niż długość pola danych odpowiedniej kolumny. Wówczas do tworzenia indeksu wykorzystana jest jedynie część pola danych, co owocuje czasami o wiele mniejszymi (w sensie zużycia miejsca na dysku) i szybciej przeszukiwanymi indeksami.

12. [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [nazwa_indeksu] [index_type] (nazwa_kolumny_indeksowej,...) deklaruje indeks o nie powtarzających się wartościach. Kolumna typu BLOB nie może być indeksowana.

13. CHECK TABLE nazwa_tabeli[opcje] opcje = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED} deklaracja zaimplementowana w MySQL 5.1.9 QUICK Do not scan the rows to check for incorrect links. FAST Check only tables that have not been closed properly. CHANGED Check only tables that have been changed since the last check or that have not been closed properly. MEDIUM Scan rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. EXTENDED Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but takes a long time.

14. ANALYZE TABLE nazwa_tabeli REPAIR TABLE nazwa_tabeli OPTIMIZE TABLE nazwa_tabeli

15. Realizacja kluczy obcych w MySQL: [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, widok nie może być w danej chwili używany przez jakikolwiek obiekt w bazie.

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

17. Indeksy zajmują też miejsce na dysku:

18. Wyświetlanie zapisu tworzenia tabeli:

19. Prezentacja struktury tabeli: Polecenie równoważne: SHOW COLUMNS FROM biuro;

20. Rozszerzona prezentacja struktury tabeli:

22. Wyświetlanie indeksów tabeli:

23. Modyfikacja struktury tabel: Strukturę tabel już istniejących (i wypełnionych danymi) można modyfikować, w sensie dodawania lub usuwaniakolumn i indeksów, zmiany definicji kolumn, czy wreszcie zmiany nazwy tabeli instrukcją w postaci: ALTER [IGNORE] TABLE nazwa_tabeli operacja1[, operacja2, ...] gdzie możliwe operacje modyfikujące to: ADD [COLUMN] definicja_kolumny [FIRST|AFTERnazwa_kolumny]: stworzenie nowej kolumny, według definicji o składni analogicznej jak w instrukcji CREATE TABLE - domyślnie kolumna umieszczana jest jako ostatnia (opcje pozwalają zażądać, by nowa kolumna została stworzona w określonej pozycji - rozszerzenie MySQL).

24. 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], ...) ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [nazwa_indeksu] [index_type] (nazwa_kolumny_indeksowej[(długość)] [ASC | DESC],...)

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

26. 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_kolumnynowa_nazwa_kolumnydefinicja_kolumny [FIRST|AFTERnazwa_kolumny] – umożliwia zmianę definicji istniejącej kolumny (nazwy) - (rozszerzenie MySQL). MODIFY [COLUMN] nazwa_kolumnydefinicja_kolumny [FIRST|AFTER nazwa_kolumny] – umożliwia zmianę definicji istniejącej kolumny (właściwości).

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

28. 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]

29. 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 MySQL) dotyczy sytuacji, gdy modyfikacja struktury tabeli doprowadziła 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.

30. Usuwanie tabel: Niepotrzebne już tabele można usunąć za pomocą instrukcji DROP TABLE tabela1, ... co powoduje trwałe usunięcie zarówno danych zapisanych w tabelach podanych jako argumenty, jak i ich definicji. Używać ostrożnie Zmiana nazwy tabeli: RENAME TABLE stara_nazwa_tabeli TO nowa_nazwa_tabeli[, stara_nazwa_tabeli2 TO nowa_nazwa_tabeli2] ... - jednej lub więcej.

31. Wprowadzanie danych instrukcją INSERT: W najprostszej postaci instrukcja INSERT służy wprowadzeniu do tabeli pojedynczego wiersza danych: INSERT [IGNORE] [INTO]nazwa_tabeli [ (kolumna1, ...) ] VALUES (wyrażenie1, ...), (wyrażenie12,...),... 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).

32. 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 [ (kolumna1, ...) ] SELECT ...

35. Usuwanie wierszy instrukcją DELETE: Do usuwania wiersza bądź wierszy z tabeli służy instrukcja DELETE. 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).

36. Usunięcie biura o numerze B002: Usunięcie wszystkich biur:

37. Wprowadzanie poleceń z pliku: Plik z odpowiednimi poleceniami zapisujemy w katalogu bin naszego MySQL'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:\windows\pulpit\biuro.sql

38. Przykładowa zawartość: CREATE DATABASE IF NOT EXISTS Biuro DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_polish_ci; CONNECT biuro; CREATE TABLE IF NOT EXISTs biuro ( biuroNr varchar(4) NOT NULL, ulica varchar(25) NOT NULL, miasto varchar(25) NOT NULL, kod varchar(6) NOT NULL, PRIMARY KEY (biuroNr) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci; INSERT INTO biuro (biuroNr,ulica,miasto,kod) VALUES ('B001','Piękna 46','Białystok','15-900'); INSERT INTO biuro VALUES ('B002','Cicha 56',‘Łomża','18-400');

40. Wyświetlenie zawartości tabeli:

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

46. Tworzymy drugą powiązaną kluczem obcym tabelę: KEY `biuroNr` (`biuroNr`) –indeksy do kluczy obcych są tworzone w MySQL automatycznie

47. Wyświetlanie zapisu tworzenia tabeli:

48. Prezentacja struktury tabeli: Polecenie równoważne: SHOW COLUMNS FROM personel;

49. Rozszerzona prezentacja struktury tabeli: