Wydawnictwo Helionul. Koœciuszki 1c44-100 Gliwicetel. 032 230 98 63e-mail: helion@helion.pl

PRZYK£ADOWY ROZDZIA£PRZYK£ADOWY ROZDZIA£

IDZ DOIDZ DO

ZAMÓW DRUKOWANY KATALOGZAMÓW DRUKOWANY KATALOG

KATALOG KSI¥¯EKKATALOG KSI¥¯EK

TWÓJ KOSZYKTWÓJ KOSZYK

CENNIK I INFORMACJECENNIK I INFORMACJE

ZAMÓW INFORMACJEO NOWOœCIACH

ZAMÓW INFORMACJEO NOWOœCIACH

ZAMÓW CENNIKZAMÓW CENNIK

CZYTELNIACZYTELNIAFRAGMENTY KSI¥¯EK ONLINEFRAGMENTY KSI¥¯EK ONLINE

SPIS TREœCISPIS TREœCI

DODAJ DO KOSZYKADODAJ DO KOSZYKA

KATALOG ONLINEKATALOG ONLINE

MySQL. Szybki start.Wydanie II

B³yskawiczny kurs u¿ytkowania jednej z najpopularniejszych baz danych

MySQL to system zarz¹dzania bazami danych, dostêpny na licencji open-source. Swoimi mo¿liwoœciami nie ustêpuje w niczym potê¿nym komercyjnym systemom. Wykorzystywany jest zarówno jako zaplecze bazodanowe witryn WWW, jak i Ÿród³o danych dla rozbudowanych aplikacji korporacyjnych. MySQL dostêpny jest niemaldla wszystkich systemów operacyjnych. Ogromn¹ zalet¹ jest jego prosta obs³uga.Dziêki temu nawet pocz¹tkuj¹cy u¿ytkownicy szybko opanuj¹ wszystkie mo¿liwoœcii wykorzystaj¹ je w pracy. Zaawansowani z pewnoœci¹ doceni¹ wydajnoœæ, stabilnoœæi funkcje znane z „kombajnów”, takich jak Oracle lub MS SQL Server.

Ksi¹¿ka „MySQL. Szybki start. Wydanie II” to kolejna edycja przewodnika po podstawach korzystania z tej bazy danych. W tej ksi¹¿ce, zaktualizowanej zgodniez najnowsz¹ wersj¹ programu, znajdziesz informacje dotycz¹ce instalowania MySQL, uruchamiania go w ró¿nych systemach operacyjnych oraz administrowania nim. Nauczysz siê zak³adaæ bazy i tabele, wykorzystywaæ jêzyk SQL do manipulowania danymi w bazie oraz ³¹czyæ siê z baz¹ danych z poziomu programów napisanychw ró¿nych jêzykach. Ka¿de zagadnienie jest przedstawione na praktycznym, bogato ilustrowanym przyk³adzie, co doskonale pomo¿e Ci w przyswojeniu wiedzy.

Ksi¹¿ka porusza nastêpuj¹ce tematy:

• Instalacja MySQL w aplikacji Windows i Linuksie• Konfiguracja serwera bazy danych• Korzystanie z programu mysqladmin• Projektowanie i tworzenie baz danych• Wprowadzanie, pobieranie i modyfikowanie danych za pomoc¹ jêzyka SQL• £¹czenie skryptów PHP i Perla z baz¹ danych MySQL• Korzystanie z MySQL w programach napisanych w Javie• Wyzwalacze i perspektywy• Administrowanie serwerem MySQL

Poznaj ogrom mo¿liwoœci systemu MySQL

Autor: Larry UllmanT³umaczenie: Pawe³ Gonera na podstawie„MySQL. Szybki start” w t³umaczeniu Marka Pa³czyñskiegoISBN: 83-246-0665-3Tytu³ orygina³u: MySQL, Second Edition:Visual QuickStart Guide (2nd Edition)Format: B5, stron: 480

Spis treści

5

Spis treści

Wprowadzenie 9

Rozdział 1. Instalowanie MySQL 17Ogólny opis instalacji 18Instalacja MySQL w systemie Windows 21Konfigurowanie MySQL w systemie Windows 24Instalowanie MySQL w systemie Linux 26Podstawowe opcje konfiguracyjne 31Uaktualnianie MySQL 34

Rozdział 2. Uruchamianie MySQL 37Uruchamianie MySQL w systemie Windows oraz Windows NT 38Uruchamianie MySQL w systemach Linux i Unix 47Wykorzystywanie mysqladmin 50Ustawianie hasła użytkownika root 53Klient MySQL 55Użytkownicy i ich prawa 59

Rozdział 3. Projektowanie bazy danych 69Normalizacja 70Klucze 71Relacje 73Pierwsza postać normalna 75Druga postać normalna 77Trzecia postać normalna 80

Rozdział 4. Tworzenie bazy danych MySQL 83Typy danych MySQL 84Dodatkowe charakterystyki kolumn 89Wprowadzenie do indeksów 92Końcowy etap projektu 94Wybór maszyny zapisu 97Zestawy znaków i sposoby sortowania 100

Spis treści

Spis treści

6

Spis

treśc

i

Tworzenie baz danych 103Tworzenie tabel 105Modyfikacja tabel 110

Rozdział 5. Podstawy SQL 113Wykorzystywanie wartości w zapytaniach 114Wprowadzanie danych 116Pobieranie danych 120Wyrażenia warunkowe 123Użycie LIKE i NOT LIKE 126Złączenia 129Sortowanie wyników zapytania 135Ograniczanie liczby zwracanych wyników 137Uaktualnianie danych 139Usuwanie danych 141

Rozdział 6. Funkcje MySQL 145Funkcje tekstowe 146Konkatenacja i aliasy 149Funkcje numeryczne 154Funkcje przetwarzania daty i czasu 157Formatowanie daty i czasu 161Funkcje szyfrowania 163Funkcje grupowania 166Pozostałe funkcje 169

Rozdział 7. MySQL i PHP 173Łączenie z MySQL i wybieranie bazy danych 174Proste zapytania 178Przetwarzanie wyników zapytania 186Korzystanie z mysqli_insert_id() 194Obsługa błędów 201Bezpieczeństwo 204

Rozdział 8. MySQL i Perl 217Instalacja Perla z obsługą MySQLw systemie operacyjnym Windows 218Instalowanie obsługi MySQL w Perluw systemie operacyjnym Unix 221Testowanie Perla i MySQL 223Łączenie z MySQL 227

Spis treści

7

Spis treści

Przetwarzanie wyników zapytania 230Wykonywanie prostych zapytań 234Pozyskiwanie wartości InsertID 239Obsługa błędów 242Zagadnienia bezpieczeństwa 246Zastosowanie instrukcji przygotowanych 249

Rozdział 9. MySQL i Java 255Instalacja sterownika MySQL dla Javy 256Łączenie z bazą danych 259Wykonywanie prostych zapytań 266Przetwarzanie wyników zapytania 273Pozyskiwanie wartości InsertID 280Zastosowanie instrukcji przygotowanych 286

Rozdział 10. SQL i MySQL dla zaawansowanych 291Wykorzystywanie transakcji 292Przeszukiwanie typu FULLTEXT 299Wyrażenia regularne 309Zmienne definiowane przez użytkownika 312Wprowadzenie do unii 315

Rozdział 11. Funkcje MySQL 5 321Podprogramy przechowywane 322Zastosowanie parametrów OUT 342Wyzwalacze 345Perspektywy 350

Rozdział 12. Techniki programowania 357Zapisywanie i pobieranie danych binarnych 358Tworzenie stron z wynikami zapytania 376Zastosowanie transakcji w języku Perl 390

Rozdział 13. Administrowanie MySQL 399Program MySQL Administrator 400Tworzenie kopii zapasowych baz danych 404Importowanie danych 408Dzienniki pracy MySQL 410Utrzymanie bazy danych 413Podnoszenie wydajności 417Korzystanie z plików wsadowych 421

Spis treści

8

Spis

treśc

i

Dodatek A Rozwiązywanie problemów 425Instalacja 426Uruchamianie MySQL 427Dostęp do MySQL 428Problemy z mysql.sock 430Zapytania zwracające nieoczekiwane wyniki 432Problemy z protokołem uwierzytelniania 433Zmiana hasła użytkownika root 434

Dodatek B Przegląd SQL i MySQL 437Podstawy SQL 438Polecenia ALTER 440Klauzule SQL 441Prawa dostępu MySQL 442Typy danych MySQL 443Funkcje MySQL 445Pozostałe informacje 449

Dodatek C Źródła informacji 451Źródła specyficzne dla MySQL 452Aplikacje MySQL innych dostawców 454SQL 455PHP 456Perl 457Java 458Pozostałe źródła informacji 459

Skorowidz 461

Tworzenie bazy danych MySQL

83

Rozdział 4. Tworzenie bazy danych MySQL

W rozdziale 3., „Projektowanie bazy danych”,przedstawiłem operacje potrzebnedo opracowania schematu bazy danych, którenazywane są normalizacją. Operacje te mogąbyć wykonywane w dowolnej relacyjnej baziedanych i nie są specyficzne dla MySQL.W tym rozdziale napiszę, w jaki sposóbzaimplementować bazę danych w serwerzeMySQL.

Proces ten rozpoczniemy od zapoznania sięz dostępnymi typami danych oraz sposobamiich dalszego dostosowywania. Kolejnopoznamy indeksy poprawiające wydajnośćbazy danych, zakończymy projektowanie każdejz tabel przez wybranie właściwych nazwi wybierzemy typy tabel. Na koniec wrócimydo pracy z narzędziami MySQL, tworząci być może modyfikując bazę danych i tabele.

Tworzeniebazy danych MySQL

Tworzenie bazy danych M

ySQL

Rozdział 4.

84

Typy danych MySQLPo zdefiniowaniu wszystkich wymaganychtabel i kolumn konieczne jest określenie typudanych przechowywanych w każdym z pól.Podczas tworzenia bazy danych (co zostanieprzedstawione w następnym rozdziale) będziewymagane określenie rodzaju informacji,które będą przechowywane w każdym z pól.Niemal każda baza danych opiera się natrzech ich kategoriach:

tekst;

liczby;

data i czas.

W każdej z wymienionych grup wyróżnia siękilka odmian typów danych, z których pewnesą charakterystyczne jedynie dla MySQL.Właściwy wybór typu dla danej kolumnywpływa nie tylko na rodzaj informacji, jakiemogą być w niej gromadzone oraz na sposóbich przechowywania, ale również na całkowitąwydajność bazy danych. Wiele typów pozwalana określenie opcjonalnego atrybutu Długość(nawiasy kwadratowe — [] — oznaczają,że pomiędzy nimi można wstawić parametropcjonalny, tymczasem nawiasy okrągłeodpowiadają argumentom obowiązkowym).

Typy

dan

ych

MyS

QL

Tabela 4.1. Znajduje się tu większość typów numerycznych, jakich można użyć w bazach danych MySQL.Dla typów FLAT, DOUBLE oraz DECIMAL argument Długość określa maksymalną liczbę cyfr, natomiastargument Pozycje określa liczbę cyfr po kropce dziesiętnej (od MySQL 5.0.3 rozmiar kolumny DECIMALjest określany przez wyrażenie)

Numeryczne typy danych MySQL

Typ Rozmiar Opis

TINYINT[Długość] 1 bajt Liczba z zakresu od –128 do 127 lub od 0 do 255, jeżeli jesttypu UNSIGNED.

SMALLINT[Długość] 2 bajty Liczba z zakresu od –32 768 do 32 767 lub od 0 do 65 535,jeżeli jest typu UNSIGNED.

MEDIUMINT[Długość] 3 bajty Liczba z zakresu od –8 388 608 do 8 388 607 lub od 0 do 16 777 215, jeżeli jest typu UNSIGNED.

INT[Długość] 4 bajty Liczba z zakresu od –2 147 483 648 do 2 147 483 647 lubod 0 do 4 294 967 295, jeżeli jest typu UNSIGNED.

BIGINT[Długość] 8 bajtów Liczba z zakresu od –9 223 372 036 854 775 808 do 9 223 372 036 854 775 807 lub od 0 do 18 446 744 073 709 551 615, jeżeli jest typu UNSIGNED.

FLOAT[Długość,Pozycje] 4 bajty Mała wartość zmiennoprzecinkowa.

DOUBLE[Długość,Pozycje] 8 bajtów Duża wartość zmiennoprzecinkowa.

DECIMAL[Długość,Pozycje] Długość + 1 lubDługość + 2 bajty

Wartość typu DOUBLE ze stałą liczbą cyfr po przecinku.

Tworzenie bazy danych MySQL

85

W tabeli 4.1 wymienione są typy numeryczne.Największa różnica występuje między typamicałkowitymi i zmiennoprzecinkowymi (którezawierają kropkę dziesiętną). Następne różnicełatwo zauważyć w zakresie możliwychwartości (dla liczb całkowitych) lub poziomudokładności (dla liczb zmiennoprzecinkowych).

W tabeli 4.2 wymienione są typy tekstowe.Większość z nich różni się rozmiarem,ale kilka pozwala na przechowywanie danychbinarnych zamiast ciągów znaków. Dostępnesą również dwa rozszerzenia dla typówtekstowych — ENUM oraz SET — któreumożliwiają definiowanie serii akceptowanychwartości w czasie tworzenia tabeli. Pole typuENUM pozwala na użycie jednej z kilku tysięcywartości, natomiast typu SET — na wybraniekilku z maksymalnie 64 możliwych wartości.Z typami ENUM i SET są związane dwie pułapki— typy te nie są obsługiwane w innych bazachdanych i ich zastosowanie podważa zasadynormalizacji.

Typy danych MySQ

L

Tabela 4.2. Znajdują się tu najczęściej używane typy pozwalające na przechowywanie tekstu w bazie danychMySQL

Tekstowe typy danych MySQL

Typ Rozmiar OpisCHAR[Długość] Liczba bajtów Pole o stałej długości; długość od 0 do 255 znaków.VARCHAR(Długość) Długość ciągu + 1 bajt Pole o stałej długości; długość od 0 do 255 znaków (od

MySQL 5.0.3 65 535 znaków).TINYTEXT Długość ciągu + 1 bajt Ciąg tekstowy o maksymalnej długości 255 znaków.TEXT Długość ciągu + 2 bajty Ciąg tekstowy o maksymalnej długości 65 536 znaków.MEDIUMTEXT Długość ciągu + 3 bajty Ciąg tekstowy o maksymalnej długości 16 777 215 znaków.LONGTEXT Długość ciągu + 4 bajty Ciąg tekstowy o maksymalnej długości 4 294 967 295 znaków.BINARY[Długość] Długość bajtów Podobny do CHAR, ale przechowuje dane binarne.VARBINARY[Długość] Długość danych + 1 bajt Podobny do VARCHAR, ale przechowuje dane binarne.TINYBLOB Długość danych + 1 bajt Przechowuje dane binarne o maksymalnej długości 255 bajtów.BLOB Długość danych + 2 bajty Przechowuje dane binarne o maksymalnej długości 65 535

bajtów.MEDIUMBLOB Długość danych + 3 bajty Przechowuje dane binarne o maksymalnej długości

16 777 215 bajtów.LONGBLOB Długość danych + 4 bajty Przechowuje dane binarne o maksymalnej długości

4 294 697 295 bajtów.ENUM 1 lub 2 bajty Wyliczenie, które pozwala na to, by każda kolumna posiadała

jedną z kilku możliwych wartości.SET 1,2,3,4 lub 8 bajtów Typ podobny do ENUM z tą różnicą, że może posiadać więcej

niż jedną z dopuszczalnych wartości.

Rozdział 4.

86

Różne typy dla daty i czasu (tabela 4.3)mają własne unikalne cechy, które sąudokumentowane w podręczniku i opisywanew różnych fragmentach tej książki. Zazwyczajz tych typów korzysta się bez modyfikacji,więc nie trzeba brać pod uwagę ich zawiłości.

Wybierając typ danych:

1. Określ, czy kolumna będzie przechowywaładane tekstowe, liczbowe, czy też daty.

Zazwyczaj jest to prosty i oczywisty etap.Do przechowywania wartości liczbowychnależy użyć typu numerycznego. Jeżelikolumna może zawierać wartości inneniż numeryczne, należy użyć kolumntekstowych.

W przypadku danych, takich jak kodypocztowe czy sumy pieniężne, które będąprzechowywane wraz z dodatkowymiznakami (np. znak myślnika czy oznaczeniewaluty), używa się pól tekstowych, choćzapisanie ich jako wartości liczbowychdaje lepsze rezultaty. Problem formatowaniabędzie rozwiązywany w innymi miejscu.

2. Wybierz dla danej kolumny odpowiednityp z danej kategorii.

Mając na uwadze wysoką wydajnośćbazy danych, warto pamiętać, że:

pola o stałej długości (jak CHAR) sązazwyczaj szybciej przetwarzane niżpola o zmiennej długości (jak VARCHAR),choć z drugiej strony zajmują więcejprzestrzeni dyskowej — więcejinformacji na ten temat zamieszczonowe wskazówce;

rozmiar każdego z pól powinien byćograniczony do najmniejszej możliwejwartości, którą można wyznaczyć,określając największą możliwą wartośćwprowadzaną do danego pola; jeżeliprzykładowo największa długośćnazwy towaru będzie równa 20,to dla danej kolumny powinno sięwybrać typ VARCHAR(20);

należy pamiętać, że wprowadzeniepięcioznakowego ciągu tekstowegodo pola typu CHAR(2) spowoduje obcięcietrzech ostatnich znaków, ta prawidłowośćznajduje zastosowanie we wszystkichpolach — jeżeli przekroczy sięmaksymalny zakres dla kolumny,cześć danych zostanie utracona.

Typy

dan

ych

MyS

QL

Tabela 4.3. Dostępne w MySQL typy daty i czasu

Typy danych MySQL

Typ Rozmiar OpisDATE 3 bajty Data w formacie:

RRRR-MM-DD.DATETIME 8 bajtów Data i czas w formacie:

RRRR-MM-DDGG:MM:SS.

TIMESTAMP 4 bajty Znacznik czasowyw formacie: GG:MM:SS;zakres wartości kończy sięw roku 2037.

TIME 3 bajty Znacznik czasowyw formacie GG:MM:SS.

YEAR 1 bajt Rok w formacie RRRRi zakresie od 1901 do 2155.

Tworzenie bazy danych MySQL

87

W przypadku liczb należy sięzdecydować, czy trzeba przechowywaćczęść ułamkową. Decyzja ta dzieli naszobszar zainteresowania na liczby całkowitei rzeczywiste. Jeżeli ważna jest dokładnośćmatematyczna, należy użyć typu DECIMAL,który jest znacznie dokładniejszy niż FLOATlub DOUBLE.

3. Ustal maksymalną długość kolumntekstowych lub liczbowych (tabela 4.4).

Zamiast rozpisywania się o sposobachi przyczynach takiego, a nie innegozdefiniowania wszystkich 22 przykładowychkolumn, wszystkie ich własności zestawionow tabeli 4.4. Niektórzy programiści mogąmieć odmienne propozycje. Najistotniejszejest jednak, aby dostosować każdy typ dorozmiarów przechowywanych informacji,zamiast korzystać zawsze z podstawowych(nieefektywnych) typów TEXT i INT.

Typy danych MySQ

L

Tabela 4.4. Innym aspektem projektowania bazydanych jest dobór optymalnego typu danych dlakażdego z pól

Baza danych finansów

Nazwa kolumny Tabela Typ kolumny

Numer faktury Faktury SMALLINT(4)

Klient ID Faktury SMALLINT(3)Data wystawienia faktury Faktury TIMESTAMPWartość faktury Faktury DECIMAL(10,2)Opis faktury Faktury TINYTEXTTermin płatności Faktury DATEKlient ID Klienci SMALLINT(3)Nazwa klienta Klienci VARCHAR(40)Ulica klienta Klienci VARCHAR(80)Miasto klienta Klienci VARCHAR(30)Stan klienta Klienci CHAR(2)Kod pocztowy klienta Klienci MEDIUMINT(5)Telefon klienta Klienci VARCHAR(14)Osoba kontaktowa Klienci VARCHAR(40)Adres e-mail kontaktowy Klienci VARCHAR(60)Wydatek ID Wydatki SMALLINT(4)Kategoria wydatku ID Wydatki TINYINT(3)Wartość wydatku Wydatki DECIMAL(10,2)Opis wydatku Wydatki TINYTEXTData zapłaty Wydatki DATEKategoria wydatku ID Kategorie

wydatkówTINYINT(3)

Kategoria wydatku Kategoriewydatków

VARCHAR(30)

Rozdział 4.

88

Wskazówki

Wiele z nazw typów posiada synonimy,np. INT — INTEGER, DEC — DECIMAL itd.

Pole typu TIMESTAMP jest uaktualnianeautomatycznie podczas wykonywaniapolecenia INSERT czy UPDATE, nawet jeżelidla danego pola nie określono żadnejwartości. W zależności od wersji MySQL,pola typu TIMESTAMP mają różnewłaściwości.

Dostępny jest również typ BLOB, będącyodmianą typu TEXT, który pozwalana przechowywanie w tabeli plikówbinarnych. Przykład użycia zostaniezaprezentowany w rozdziale 12. „Technikiprogramowania”.

W polach daty i czasu MySQL dodatkowosprawdza poprawność podanych danychw czasie ich wstawiania. Do wersji 5.0.2dostępna była tylko podstawowa kontrola,czy miesiąc nie jest większy niż 12 i dzieńwiększy niż 31. Od wersji 5.0.2 dodatkowopodany dzień musi istnieć w kalendarzu,np. jeżeli zostanie podana data 2006-02-31,zwrócony zostanie błąd daty.

Rozmiar wymagany do zapamiętaniaciągu znaków o zmiennej długości zależyrównież od wykorzystywanego zestawuznaków, np. znaki spoza alfabetuangielskiego mogą potrzebowaćwięcej miejsca.

CHAR a VARCHARCo do przewagi któregokolwiek z tychdwóch podobnych do siebie typów wciążtrwają dyskusje. Oba przechowują ciągitekstowe i mogą być definiowanez podaniem maksymalnej jego długości.Podstawowa różnica polega na tym,że jakiekolwiek dane zapisane jako CHARzawsze będą zapisywane jako ciągtekstowy o długości określonej dla danejkolumny (wypełnienie znakami spacji).Z kolei długość ciągów tekstowych typuVARCHAR jest równa długościprzechowywanego ciągu danych.Wynika z tego, że:

kolumny VARCHAR zajmują mniej miejscana dysku;

kolumny CHAR są przetwarzane szybciejniż VARCHAR, o ile nie są stosowanetypy tabel InnoDB (więcej informacji naten temat zamieszczono w podrozdziale„Wybór maszyny zapisu” w dalszejczęści tego rozdziału).

Trzeba przyznać, że w większościprzypadków różnica w wielkościzajmowanego miejsca na dysku orazw szybkości pomiędzy oboma typami jestniezauważalna, przez co rozważanie tegoproblemu nie ma szczególnego znaczenia.Co więcej, można czasami zauważyć, żepomimo zdefiniowania kolumny z użyciemjednego typu, MySQL skorzystał z drugiego.Istnieje jeszcze jedna, mniej istotna różnicapomiędzy omawianymi typami danych— przed wersją 5.0.3 MySQL usuwałnadmiarowe znaki spacji z kolumn CHARpodczas pobierania danych, a z kolumnVARCHAR podczas ich wstawiania. Od wersji5.0.3 kolumny VARCHAR nie mają tejwłaściwości i wszystkie dodatkowe spacjesą przechowywane.Jako zasadę można przyjąć korzystaniez typu VARCHAR, chyba że dana zawszelub niemal zawsze ma taką samą długość,co czasami zdarza się w przypadkuidentyfikatorów produktów(SD123, PA456 itp.).

Typy

dan

ych

MyS

QL

Tworzenie bazy danych MySQL

89

Oznaczenie AUTO_INCREMENTJednym z atrybutów kolumny numerycznejjest AUTO_INCREMENT. Jeżeli zdefiniujemypole posiadające taką właściwość,powoduje to nadawanie przez MySQLnastępnej logicznej wartości z serii. Atrybutten jest zwykle nadawany kolumnom kluczagłównego, takim jak Numer fakturylub Identyfikator klienta.Jeżeli zdefiniujemy taką kolumnę,a podczas wstawiania wartości do tabelinie zostanie podana w tej kolumnie wartość,program wstawi do niej następną logicznąwartość. Dzięki temu pierwszy numerfaktury będzie miał wartość 1, drugi 2,trzeci 3 itd. MySQL będzie nadawał tewartości automatycznie.Niektórzy mogą zastanawiać się, co sięstanie, gdy później zdecydujemy sięskasować fakturę numer 3. W numeracjipojawi się „dziura”, ale taka sytuacja jestcałkowicie prawidłowa. Nie ma żadnegoproblemu z powodu tego, że kolejnenumery faktur będą miały wartości1, 2, 4, 5, 8… W rzeczywistości problemymogłyby się pojawić, jeżeli systempróbowałby „poprawić" tę sytuację.

Dodatkowecharakterystyki kolumnDeklarując typ dla kolumny, na początkuwybieramy szeroki typ — liczba, tekst lubdata — a następnie wskazujemy dokładniejszytyp w danej grupie. Później kolumnie możnanadać dodatkową charakterystykę. Specjalnyatrybut, AUTO_INCREMENT, jest przedstawionywe wskazówce, ale można również korzystaćz innych, takich jak UNSIGNED, ZEROFILL, NOTNULL oraz DEFAULT.

Typy numeryczne mogą być określane jakoUNSIGNED. Oznacza to, że w kolumnie takiejmogą być zapisywane tylko liczby nieujemne.W przypadku liczb całkowitych dodatkowymefektem ubocznym jest podwojenie możliwegozakresu wartości (dla liczb rzeczywistych taknie jest). Typy numeryczne mogą być równieżdeklarowane jako ZEROFILL, co oznacza,że dodatkowe miejsce jest wypełniane od lewejstrony znakami zera (ZEROFILL automatyczniepowoduje zastosowanie atrybutu UNSIGNED).

Każda z kolumn może być zadeklarowanajako NOT NULL. W przypadku tworzeniabazy danych użycie NULL jest jednoznacznez poinformowaniem, że dane polenie przechowuje żadnej wartości (może się toróżnić od interpretacji wartości NULL w innychkontekstach). Rozwiązaniem idealnym byłobyoczywiście przypisanie każdemu rekordowibazy danych pewnej konkretnej wartości.W rzeczywistości jednak takie sytuacje zdarzająsię rzadko. Dołączając do deklaracji typu ciągNOT NULL możliwe jest wymuszenie takiegoograniczenia na danym polu.

Dodatkowe charakterystyki kolum

n

Rozdział 4.

90

Tworząc tabelę, można również określić wartośćdomyślną dla kolumny (poza typami TEXTi BLOB). Wtedy gdy duża część rekordówbędzie miała taką samą zawartość, wartośćdomyślna pozwala uniknąć koniecznościwpisywania wszystkich wartości w czasiewstawiania nowych wierszy, pod warunkiem,że wartości te są równe wartości domyślnej.Przykładem takiej deklaracji kolumny możebyć:

plec ENUM('M', 'K') DEFAULT 'K'.

W tabeli 4.5 wymienione są wszystkie kolumnyz bazy finanse, wraz z ich pełną definicją.

Doda

tkow

e ch

arak

tery

styk

i kol

umn

Tabela 4.5. Do każdej z kolumn można w razie potrzeby dodać dodatkowe atrybuty

Baza danych finanse, zmodyfikowana

Nazwa kolumny Tabela Typ kolumny

Numer faktury Faktury SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENTKlient ID Faktury SMALLINT(3) UNSIGNED NOT NULLData wystawienia faktury Faktury TIMESTAMP NOT NULLWartość faktury Faktury DECIMAL(10,2) UNSIGNED NOT NULLOpis faktury Faktury TINYTEXT NOT NULLTermin płatności Faktury DATEKlient ID Klienci SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENTNazwa klienta Klienci VARCHAR(40) NOT NULLUlica klienta Klienci VARCHAR(80) NOT NULLMiasto klienta Klienci VARCHAR(30) NOT NULLStan klienta Klienci CHAR(2) NOT NULLKod pocztowy klienta Klienci MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULLTelefon klienta Klienci VARCHAR(14)Osoba kontaktowa Klienci VARCHAR(40)Adres e-mail Klienci VARCHAR(60)Wydatek ID Wydatki SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENTKategoria wydatku ID Wydatki TINYINT(3) UNSIGNED NOT NULLWartość wydatku Wydatki DECIMAL(10,2) UNSIGNED NOT NULLOpis wydatku Wydatki TINYTEXT NOT NULLData zapłaty Wydatki TIMESTAMP NOT NULLKategoria wydatku ID Kategorie wydatków TIMYINT(3) UNSIGNED NOT NULL AUTO_INCREMENTKategoria wydatku Kategorie wydatków VARCHAR(30) NOT NULL

Tworzenie bazy danych MySQL

91

Wskazówki

Zgodnie ze sztuką projektowania bazydanych oraz zasadami funkcjonowaniaMySQL klucze główne nie mogą zawieraćwartości NULL.

Jeżeli kolumna ENUM zostanie określonajako NOT NULL, wówczas pierwszadopuszczalna wartość stanie się wartościądomyślną.

Istotnym jest, aby mieć świadomość,że NULL nie jest wartością równoznacznąz zerem, pustym ciągiem ("") czy znakiemspacji (" "), które to są znanymiwartościami.

Trzeba wiedzieć, że MySQL w dziwnysposób obsługuje liczby UNSIGNED. Jeżeliwykonamy odejmowanie z co najmniejjedną liczbą UNSIGNED, wynik zawszebędzie UNSIGNED. Przez to odjęcieod wartości 2 z kolumny UNSIGNED wartości10 z kolumny SIGNED nie da w wyniku –8.

Gdy chcesz dostosować kolumny:

1. Wyszukaj te, które nie mogą zawieraćwartości NULL.

Jest to najważniejszy z dodatkowychatrybutów. Każda kolumna oznaczonajako NOT NULL zawsze musi mieć podanąwartość. Jak się okaże przy okazjidodawania rekordów, brak podanejwartości w kolumnie NOT NULL powodujewygenerowanie błędu.

Jako zasadę należy przyjąć definiowaniekolumny jako NOT NULL wszędzie tam, gdziejest to możliwe.

2. Wyszukaj kolumny numeryczne, którepowinny być oznaczone jako UNSIGNED.

Jest to bardzo łatwa operacja. Jeżeli liczba,która znajdzie się w kolumnie, musi byćdodatnia, tak jak cena lub ilość, kolumnapowinna być oznaczona jako UNSIGNED.Jeżeli liczba może być ujemna, jak np.temperatura lub stan konta (niestety!),nie należy kolumny tak oznaczać.

3. Wyszukaj kolumny numeryczne, którepowinny być oznaczone jako ZEROFILL.

Atrybut ZEROFILL jest znacznie rzadziejwykorzystywany niż UNSIGNED, alew niektórych przypadkach jest niezbędny.Niektóre kody zaczynają się od 0, tak jaknp. 02101. Jeżeli chcielibyśmy zapisaćtaki kod w kolumnie całkowitej, wartośćta zostałaby zapisana jako 2101 (ponieważpoczątkowe zera nie mają znaczeniaw liczbach całkowitych). Jeżeli zdefiniujemykolumnę jako MEDIUMINT(5) UNSIGNEDZEROFILL, zapisany kod zachowapoczątkowe zero.

4. Wyszukaj kolumny, które powinny miećwartość domyślną.

Krok ten jest w zasadzie zależnyod osobistych upodobań.

Dodatkowe charakterystyki kolum

n

Rozdział 4.

92

Wprowadzenie do indeksówIndeksy składają się na szczególny system,wykorzystywany do poprawienia całościowejwydajności bazy danych. Ustalając indeksyw ramach tabeli, wskazuje się kolumny, któresą w danej tabeli ważniejsze od innych kolumntej samej tabeli (definicja dla laików).

MySQL pozwala na utworzenie maksymalnieod 16 do 64 indeksów dla jednej tabeli,w zależności od wykorzystywanej maszynyzapisu, a każdy z nich może obejmowaćdo 15 kolumn. Wykorzystanie indeksówwielokolumnowych nie musi się wydawaćtakie oczywiste, jednak stają się użytecznew przypadku częstego przeszukiwania grupytych samych kolumn (np. zawierających danena temat imienia, nazwiska, miastai województwa).

W stosowaniu indeksów wskazany jest umiar.Zwiększają one, co prawda, szybkość odczytudanych z bazy, ale spowalniają proces ichmodyfikacji (z uwagi na fakt, że zmiany musząbyć odwzorowane także w indeksach). Z drugiejstrony, zwykle znacznie częściej odczytuje siędane z tabeli, niż wstawia nowe rekordyi zmienia istniejące.

Najlepszym zastosowaniem dla indeksów jestużycie ich w kolumnach, które:

są często wykorzystywane w części WHEREzapytań;

są często wykorzystywane w części ORDERBY zapytań;

cechują się różnorodnością wartości(kolumny, w których wartości powtarzająsię wielokrotnie nie powinny byćindeksowane).

są często stosowane w wyrażeniach JOIN.W MySQL wyróżnia się kilka typów indeksów:INDEX, UNIQUE (narzucający koniecznośćwprowadzania unikatowej wartości w każdymwierszu) oraz PRIMARY KEY (będący szczególnąpostacią indeksu UNIQUE). Dostępny jest równieżindeks FULLTEXT, który jest opisany w rozdziale10., „SQL i MySQL dla zaawansowanych”.Propozycje indeksów dla bazy danych finansezestawiono w tabeli 4.6.

Wpr

owad

zeni

e do

inde

ksów

Tabela 4.6. W celu zwiększenia wydajności bazadanych została wzbogacona o kilka (choć nie jestich zbyt wiele) indeksów, które pozwolą jejna efektywniejsze pobieranie przechowywanychinformacji

Indeksy bazy danych finansów

Kolumna Tabela Typ indeksu

Numer faktury Faktury PRIMARY KEYKlient ID Faktury INDEX

Data wystawieniafaktury

Faktury INDEX

Wartość faktury Faktury INDEX

Termin płatności Faktury INDEX

Klient ID Klienci PRIMARY KEY

Nazwa klienta Klienci INDEX (lub UNIQUE)Wydatek ID Wydatki PRIMARY KEY

Kategoria wydatku ID Wydatki INDEX

Wartość wydatku Wydatki INDEX

Data zapłaty Wydatki INDEX

Kategoria wydatku ID Kategoriewydatków

PRIMARY KEY

Kategoria wydatku Kategoriewydatków

UNIQUE

Tworzenie bazy danych MySQL

93

Wskazówka

Indeksy stosowane w kolumnach o zmiennejdługości cechuje mniejsza wydajność.Ogólnie, stosowanie pól, których długośćnie jest stała, spowalnia pracę MySQL.Można to skompensować indeksująctylko część ciągu o zmiennej długości,np. pierwsze pięć lub dziesięć znaków.

W celu dodania indeksu:

1. Wyszukaj wszystkie kolumny, którepowinny być oznaczone jako PRIMARY KEY.

Powinno być to oczywiste szczególniewtedy, jeżeli wykonałeś operacjenormalizacji opisaną w poprzednimrozdziale. Należy pamiętać, że może istniećtylko jeden klucz główny w tabeli (choćmożliwe jest tworzenie złożonego kluczagłównego składającego się z wielu kolumn).

2. Wyszukaj wszystkie kolumny, którychwartości muszą być zawsze unikalne.

Również indeks UNIQUE nie jestwykorzystywany zbyt często. Większośćwartości — daty, liczby, nazwy, miasta,kody pocztowe — mogą się powtarzać,szczególnie w tabelach zawierającychtysiące wierszy. Jednak czasami okazujesię, że kolumna musi zawierać unikalnewartości, np. adres e-mail, nazwaużytkownika (w przypadku systemówdo rejestracji lub logowania) lub też poleKategoria wydatku w tabeli Kategoriewydatków.

Nie ma potrzeby definiowania kolumnPRIMARY KEY jako UNIQUE, ponieważoznaczenie PRIMARY KEY również powodujeunikalność.

3. Wyszukaj wszystkie pozostałe kolumny,które powinny skorzystać z indeksu.

Skorzystaj z wcześniejszych zaleceńna temat miejsc, w których warto tworzyćindeksy i pomyśl jakie dane będąodczytywane. Jeżeli potrzebna będzielista faktur w zakresie dat lub całkowitawartość zamówienia, utwórz indeksyw logiczny sposób. Jeżeli tabela rejestracjii logowania będzie przeszukiwanaz użyciem połączenia nazwy użytkownikai hasła, w taki sam sposób powinna byćpoindeksowana. Powinno się równieżindeksować klucze obce.

Wprow

adzenie do indeksów

Rozdział 4.

94

Końcowy etap projektuOstatnim etapem projektowania bazy danychjest zastosowanie odpowiedniej konwencjinazewnictwa. Co prawda, MySQL nie narzucazasad nazywania baz danych, tabel czy kolumn,istnieją jednak pewne sprawdzone reguły,których należy przestrzegać (zasady wymaganesą zaznaczone pogrubieniem):

używanie znaków alfanumerycznych;

nie korzystanie ze spacji;

ograniczenie maksymalnej długościnazw do 64 znaków;

nazwy pól powinny mieć charakter opisowy;

nazwy pól, z wyjątkiem kluczy, powinnybyć unikatowe w obrębie wszystkichtabel;

nie należy korzystać ze słów kluczowychMySQL;

używanie znaków podkreślenia (_) w celurozdzielania wyrazów;

korzystanie tylko z małych liter (choć niejest to rzecz obowiązkowa);

używanie liczby mnogiej w oznaczaniutabel i pojedynczej w definiowaniu kolumn;

dołączanie id (lub ID) do nazw kolumnkluczy głównych i obcych;

umieszczanie kluczy głównychw początkowej części tabeli, a w dalszejkolejności kluczy obcych.

Zamieszczone powyżej reguły mają jedyniecharakter zalecenia, ich przestrzeganie, pozakoniecznością posługiwania się znakamialfanumerycznymi bez znaków spacji, nie jestzatem obowiązkowe. Część programistówpreferuje używanie wielkich literdo rozdzielania wyrazów (zamiast znakupodkreślenia). Inni z kolei uwzględniająw nazwie kolumny jej typ. Najistotniejsze jestjednak to, by przestrzegać ustalonej konwencji.

Końc

owy

etap

pro

jekt

u

Tworzenie bazy danych MySQL

95

Ostateczny projekt bazy danych przedstawionow tabeli 4.7, który otrzymamy po wykonaniukolejnych kroków.

W celu dokończenia projektowaniabazy danych:

1. Określ nazwę całej bazy danych.

Powinna być ona łatwa do zapamiętaniai opisowa. Nazwa bazy danych równieżmusi być unikalna, ponieważ na jednymserwerze MySQL nie mogą istnieć bazydanych o takich samych nazwach.

Przykładowo w tym i poprzednim rozdzialekorzystaliśmy z bazy finanse. Można jąrównież nazwać Finanse, ale osobiściepreferuję nazwy zawierające tylko małelitery. Końcow

y etap projektu

Tabela 4.7. Ostatni etap projektu polega na zastosowaniu odpowiedniej konwencji nazewnictwa orazuporządkowania kolumn w tabelach

Baza danych finanse

Nazwa kolumny Tabela Typ kolumny

faktura_id faktury SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT

klient_id faktury SMALLINT(3) UNSIGNED NOT NULL

data_faktury faktury TIMESTAMP NOT NULL

wartosc_faktury faktury DECIMAL(10,2) UNSIGNED NOT NULL

opis_faktury faktury TINYTEXT NOT NULL

data_platnosci faktury DATE

klient_id klienci SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT

nazwa_klienta klienci VARCHAR(40) NOT NULL

ulica_klienta klienci VARCHAR(80) NOT NULL

miasto_klienta klienci VARCHAR(30) NOT NULL

stan_klienta klienci CHAR(2) NOT NULL

kod_pocztowy_klienta klienci MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULL

telefon_klienta klienci VARCHAR(14)

osoba_kontaktowa klienci VARCHAR(40)

email_kontaktowy klienci VARCHAR(60)

wydatek_id wydatki SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT

kategoria_wydatku_id wydatki TINYINT(3) UNSIGNED NOT NULL

wartosc_wydatku wydatki DECIMAL(10,2) UNSIGNED NOT NULL

opis_wydatku wydatki TINYTEXT NOT NULL

data_zaplaty wydatki TIMESTAMP NOT NULL

kategoria_wydatku_id wydatki_kategorie TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT

kategoria_wydatku wydatki_kategorie VARCHAR(30) NOT NULL

Rozdział 4.

96

Rysunek 4.1. Tabele są wyświetlane w porządkualfabetycznym; można z tego skorzystać, nadajączwiązanym tabelom podobne nazwy

2. Określ nazwę każdej tabeli.

Należy pamiętać, że powinny być onełatwe do zapamiętania i opisowe.Dodatkowo, w jednej bazie danych niemogą istnieć dwie tabele o takich samychnazwach (tabele w różnych bazach mogąmieć te same nazwy). Zdecydowałem sięna tabele faktury, klienci, wydatkii wydatki _kategorie.

3. Nadaj nazwę każdej kolumnie w każdejtabeli.

W tym przypadku można spotkać sięz wieloma wariantami, ponieważ każdyma swój styl. Jak wspomniałem, do kolumnkluczy głównych i obcych dodałem frazę_id. Jeżeli w tabeli znajduje się polez datą, w jej nazwie umieszczam frazę data.

4. Uporządkuj kolumny w każdej z tabel.

Wyniki tego kroku zależą bardziej odwłasnej organizacji niż innych względów.Kolejność kolumn nie ma żadnego wpływuna działanie tabeli lub bazy danych.Osobiście umieszczam kolumny kluczagłównego na początku, a następnie układamklucze obce.

Wskazówki

Jeżeli związanym tabelom zostaną nadanenazwy rozpoczynające się tak samo,to gdy wyświetlimy listę tabel, będą onewyświetlane razem. Przykładem są tabelewydatki i wydatki_kategorie, którena rysunku 4.1 są wyświetlane razem).

W nazwach baz danych i tabel sąrozpoznawane wielkie i małe literyw systemach typu Unix, a w Windowsnie. W nazwach kolumn wielkie i małelitery są zawsze ignorowane.

Ściśle stosując się do zasad projektowaniabazy danych, minimalizujemy liczbębłędów, jakie mogą powstać przyprogramowaniu interfejsu bazy danych.

Technicznie rzecz biorąc, można korzystaćz istniejących słów kluczowych w nazwachtabel i kolumn. Jednak w celu odwołaniasię do nich zawsze należy umieszczać tenazwy we wstecznych apostrofach:

SELECT * FROM `table`

Jednak najlepiej nie korzystać z istniejącychsłów kluczowych.

Końc

owy

etap

pro

jekt

u

Tworzenie bazy danych MySQL

97

Wybór maszyny zapisuSerwer bazy danych MySQL obsługuje kilkaróżnych typów tabel (typ tabeli określa równieżtyp maszyny zapisu). Choć każdy z typówobsługuje różny zbiór funkcji, sposób ichpracy — mówimy tu o wykonywaniu zapytań— jest właściwie spójny. W tym miejscukrótko omówię trzy główne typy tabel; wrazz postępami poznawania MySQL będzieszmógł zapoznać się ze wszystkimi szczegółamiopisanymi w podręczniku.

Najważniejszym typem tabel jest MyISAM.Tabele tego typu doskonale nadają się dlawiększości aplikacji, ponieważ bardzo szybkowykonują operacje SELECT i INSERT. Jednakmaszyna zapisu MyISAM nie obsługujetransakcji, o których będzie mowaw rozdziale 10.

Kolejnymi popularnymi typami tabel są InnoDBoraz MEMORY (czasami nazywany HEAP).Od wersji 4.0 tabele InnoDB wchodzą w składdomyślnej instalacji MySQL (jeżeli korzystaszz wcześniejszej wersji serwera, musisz włączyćobsługę InnoDB; więcej informacji na ten tematmożna znaleźć w podręczniku). Tabele InnoDBmogą być wykorzystywane w transakcjachi elegancko obsługują operacje UPDATE. Jednakmaszyna InnoDB jest wolniejsza niż MyISAMi wymaga większej ilości miejsca na dysku.

Typ tabel MEMORY pozwala na najszybszewykonywanie operacji, ponieważ takie tabeleprzechowują dane w pamięci, a nie na dysku.Jest to jednak okupione ograniczeniami,ponieważ tabele MEMORY obsługują tylkokolumny o stałej szerokości, nie możnakorzystać z atrybutu AUTO_INCREMENTi w czasie awarii traci się wszystkie dane.

Wybór m

aszyny zapisu

Rozdział 4.

98

W celu wybrania maszyny zapisu:

1. Zaloguj się do klienta mysql.

Aby wybrać maszynę zapisu, należynajpierw sprawdzić, jakie opcje są dostępne.W tym celu należy odpytać serwer MySQL,do którego podłączył się program mysql(instrukcje specyficzne dla platformymożna znaleźć w rozdziale 2.,„Uruchamianie MySQL”).

2. Sprawdź, jakie maszyny zapisu sąobsługiwane przez dany serwer MySQL,uruchamiając następujące zapytanie(rysunek 4.2):

SHOW ENGINES;

Wyniki będą inne w różnych instalacjachMySQL. Krok ten warto wykonać,ponieważ nie ma sensu próbowaćużyć niedostępnej maszyny zapisu.

3. Określ, czy potrzebujesz transakcji.

Transakcje są bezpieczniejsze, ponieważpozwalają wycofać zmiany i chronią danew przypadku awarii. Jednak tabelenietransakcyjne działają szybciej orazwymagają mniej pamięci operacyjneji dyskowej.

Jako zasadę należy przyjąć, że jeżelitransakcje są potrzebne, należy skorzystaćz InnoDB. Jeżeli nie, najlepiej wybraćMyISAM.

Rysunek 4.2. Lista dostępnych maszyn zapisu w jednej z instalacji MySQL

Wyb

ór m

aszy

ny z

apis

u

Tworzenie bazy danych MySQL

99

Jeżeli będziesz próbował utworzyć tabelę,korzystając z maszyny zapisu niedostępnejw danej wersji MySQL, zostaniezastosowana domyślna maszyna zapisudla tego serwera.

4. Określ, czy możesz poświęcić wydajnośćna rzecz trwałości.

Jeżeli wybrana tabela musi być obsługiwananaprawdę szybko, to prawdopodobnienajlepszym typem będzie MEMORY.Interakcja z taką tabelą jest naprawdę bardzoszybka, ale w przypadku awarii tracimywszystkie dane!

Wskazówki

Ta sama baza danych może zawierać tabeleróżnych typów. Baza danych do obsługihandlu elektronicznego może korzystaćz tabel MyISAM do przechowywaniadanych klientów i produktów,ale do zamówień może używać tabelInnoDB (w celu wykorzystania transakcji).

Dwoma innymi popularnymi typami tabelsą MERGE oraz BDB (Berkeley Database).Pierwszy z typów pozwala na traktowaniewielu tabel MyISAM jak jednej. Drugi jestalternatywą dla InnoDB i również obsługujetransakcje.

Maszyna zapisu InnoDB została zakupionaprzez firmę Oracle, konkurencyjną firmęz rynku baz danych. Oracle kupił równieżfirmę Sleepycat Software, w której powstałamaszyna zapisu BDB. Jeszcze nie wiadomo,jak to wpłynie na MySQL, ale możliwejest, że w przyszłych wersjach obie maszynyznikną. Jest to jeden z powodów, dla któregowarto sprawdzać, jakie maszyny zapisu sądostępne, wykonując zapytanie SHOWENGINES (dostępne od MySQL 4.0).

Każda maszyna zapisu posiada innewłaściwości, takie jak maksymalna liczbaobsługiwanych indeksów, typy kolumn,dla których można zakładać indeksy,maksymalna wielkość tabel (określanajako wielkość na serwerze) itd. Jeżelirozpoczniesz korzystanie z MySQLna tak wysokim poziomie, koniecznebędzie zapoznanie się z podręcznikiem.

Wybór m

aszyny zapisu

Rozdział 4.

100

Zestawy znakówi sposoby sortowaniaW czasie tworzenia tabel, oprócz wyborumaszyny zapisu, należy określić zestaw znakówi sposób sortowania. Oba te parametrywpływają na obsługę tekstu przez MySQL.Są to dosyć nowe możliwości, ponieważwyraźnie zaistniały w wersji MySQL 4.1(były we wcześniejszych wersjach MySQL,ale nie w tak formalnej postaci).

Zestaw znaków określa sposób zapisu liter,cyfr i symboli w kolumnach tekstowych(odnosi się tylko do typów tekstowych).Aby określić zestaw znaków dla określonejkolumny, należy do definicji tej kolumnydodać frazę CHARACTER SET nazwa_zestawu,gdzie nazwa_zestawu określa wybrany zestawznaków. W celu sprawdzenia dostępnychzestawów znaków można użyć polecenia SHOWCHARACTER SET w kliencie mysql. Domyślnymzestawem znaków jest latin1, który obejmujejęzyk angielski i inne językizachodnioeuropejskie, ale dostępne są równieżzestawy dla alfabetów środkowoeuropejskich,greki, cyrylicy, języka chińskiego, koreańskiegoi innych. W czasie pisania tej książki MySQLobsługiwał ponad 70 zestawów znaków!

Pokrewnym pojęciem jest sposób sortowania,który określa zasady porównywania znaków.Przykładowo sposób sortowania bezrozróżniania wielkości znaków identycznietraktuje małe i wielkie litery. Inny możeokreślać sposób sortowania znaków z ogonkamii kropkami w języku polskim. Każdy zestawznaków posiada zbiór skojarzonych sposobówsortowania. Dla zestawu znaków latin1domyślnym sposobem sortowaniajest latin1_swedish_ci. Choć może sięto wydawać dziwne, jest to doskonałepołączenie do obsługi języka angielskiegoi większości języków zachodnich.

Zest

awy

znak

ów i

spos

oby

sort

owan

ia

Tworzenie bazy danych MySQL

101

W celu wybrania zestawu znakówi sortowania:

1. Zaloguj się do klienta mysql.

Podobnie jak w przypadku maszyn zapisu,na początek należy sprawdzić, jakie mamydostępne opcje. W tym celu należy odpytaćserwer MySQL, do którego podłączył sięprogram mysql (instrukcje specyficzne dlaplatformy można znaleźć w rozdziale 2.).

2. Sprawdź, jakie zestawy znaków sąobsługiwane przez daną wersję MySQL,uruchamiając zapytanie (rysunek 4.3):

SHOW CHARACTER SET;

Wyniki będą różniły się w różnychinstalacjach MySQL. W mojej instalacjiMySQL 5.0.18 w systemie Windowsdostępne jest 36 zestawów znaków.

Rysunek 4.3.Część listy dostępnych zestawów znaków

Zestawy znaków

i sposoby sortowania

Rozdział 4.

102

3. Wybierz zestaw znaków do wykorzystania.

Zestaw znaków powinien opowiadaćjęzykom i typom znaków, jakie mająbyć zapisywane w bazie danych. Możnarównież wybrać inny zestaw znaków dlakażdej kolumny, jeżeli aplikacja wymaga,aby w jednej kolumnie tekst był zapisywanypo polsku, a w drugiej po tajsku.

4. Sprawdź, jakie sposoby sortowania sądostępne dla wybranego zestawu znakówprzez uruchomienie następującegozapytania (rysunek 4.4):

SHOW COLLATION LIKE 'latin1%'

Aby znaleźć możliwe sposoby sortowaniadla wybranego zestawu znaków, należyuruchomić to zapytanie. Trzeba zmienićlatin1 na używany zestaw znaków.

5. Wybierz sposób sortowania.

W liście wyświetlanej przez MySQL(rysunek 4.4) należy wyszukać domyślnysposób sortowania oraz sprawdzić, któresposoby są wkompilowane (czyli inaczejmówiąc, obsługiwane). Następnie, jeżelistwierdzisz, że potrzebujesz innego sposobuniż domyślny, przeczytaj w podręczniku,jak zmienić sposób sortowania i wykonajodpowiednią operację.

Wskazówki

Zarówno zestaw znaków, jak i sposóbsortowania mogą być ustalone na poziomieserwera, bazy danych, tabeli lub kolumny.Jeżeli nie zostanie określony zestaw znakówlub sposób sortowania dla kolumny, zostanąużyte parametry dla tabeli. To samo odnosisię do tabel (które korzystają z wartościdomyślnych bazy danych) oraz baz danych(korzystających z ustawień serwera).

Można również ustawić zestaw znakówdla sesji interakcyjnej dla połączeniaz serwerem MySQL (np. gdy korzystamyz klienta mysql). Informacje na ten tematmożna znaleźć w podręczniku MySQL.

Sposób sortowania można zmienićw zapytaniu. Ma to wpływna porządkowanie i grupowanie wynikówdziałania tylko tego jednego zapytania.

Rysunek 4.4. Lista dostępnych sposobów sortowania dla jednego wybranegozestawu znaków

Zest

awy

znak

ów i

spos

oby

sort

owan

ia

Tworzenie bazy danych MySQL

103

Rysunek 4.5. Tworzenie nowej bazy danych

Dodatkowo, choć SQL nie rozróżniawielkości liter, przyzwyczaiłem siędo pisania słów kluczowych SQL wielkimiliterami, co pomaga oddzielić je od nazwbaz danych, tabel i kolumn. Jeżeli niechcesz pisać tych słów wielkimi literami,nie jest to obowiązkowe.

Tworzenie baz danychGdy przejdziemy przez wszystkie krokiprojektowania i ostatecznego budowaniaprojektu bazy danych (wymaga to zaskakującodużo pracy), przyjdzie czas na utworzenie bazydanych w MySQL. W celu wykreowania bazydanych oraz tabel skorzystamy z klienta mysqloraz prostych instrukcji SQL.

W rozdziale 2., „Uruchamianie MySQL”,szybko zdefiniowałem dwie bazy danychna potrzeby zademonstrowania, jak dodajemyużytkowników. Składnia polecenia tworzącegonową bazę danych wygląda zatem następująco:

CREATE DATABASE nazwa_bazy_danych

Aby określić zbiór znaków oraz sposóbsortowania dla całej bazy danych, należyna końcu instrukcji CREATE dodać dodatkowąklauzulę:

CREATE DATABASE nazwa_bazy_danych CHARACTERSET nazwa_zestawu COLLATEnazwa_sposobu_sort

W celu zademonstrowania instrukcji CREATEutworzymy teraz bazę danych finanse.

Aby utworzyć bazę danych:

1. Zaloguj się do klienta mysql.

Jeżeli jeszcze nie wiesz, jak to zrobić,potrzebne wskazówki możesz znaleźćw rozdziale 2., „Uruchamianie MySQL”.Należy się zalogować jako użytkownikmający uprawnienia do tworzenia nowychbaz danych.

2. Utwórz i wybierz nową bazę danych(rysunek 4.5).

CREATE DATABASE finanse;

Jak już wcześniej widzieliśmy, ten jedenwiersz kodu powoduje utworzenie bazydanych (zakładamy, że jesteśmy zalogowanido mysql jako użytkownik z uprawnieniamido tworzenia baz danych).

Tworzenie baz danych

Rozdział 4.

104

3. Potwierdź istnienie bazy danych(rysunek 4.6):

SHOW DATABASES;

Polecenie SHOW, które nie musi byćszczegółowo omawiane, wyświetla listębaz danych, do których ma dostępzalogowany użytkownik.

Wskazówki

Bazy danych można również tworzyć,korzystając z aplikacji mysqladmin. Za jejpomocą nie można jednak kreować tabel.

mysqladmin -u root -p createnazwa_bazy_danych

Przy użyciu polecenia SHOW CREATE możnazawsze sprawdzić, w jaki sposób zostałautworzona istniejąca baza danych(rysunek 4.7):

SHOW CREATE DATABASE nazwa_bazy_danych

Rysunek 4.6. Nowo utworzona baza finanse jestwyświetlana w liście baz danych

Rysunek 4.7. Zapytanie SHOW CREATE pokazuje sposób, w jaki została utworzonabaza danych lub tabela. Zwróćmy uwagę, że w tym przykładzie wyświetlany jestkomentarz dla wersji 4.01.00 (i wyższych) odnoszący się do domyślnego zestawuznaków

Twor

zeni

e ba

z da

nych

Tworzenie bazy danych MySQL

105

Tworzenie tabelGdy mamy już bazę danych, można rozpocząćtworzenie tabel za pomocą polecenia CREATE:

CREATE TABLE nazwa_tabeli (nazwa_kolumny1 opis,nazwa_kolumny2 opis…)

Łatwo zauważyć, że po podaniu nazwy tabelinależy w nawiasie kolejno zdefiniowaćkolumny. Każda kolumna i opis powinny byćoddzielone przecinkiem. Jeżeli chcemyod razu utworzyć indeksy, na końcu instrukcjitworzenia można dodać odpowiednią klauzulę(można również utworzyć indeksy później).

CREATE TABLE nazwa_tabeli (nazwa_kolumny1 opis,nazwa_kolumny2 opis,typ_indeksu(kolumny))

Jeżeli chcemy nazwać tworzone indeksy, należyzmienić tę część zapytania na:

nazwa_indeksu (kolumny)

Aby w czasie tworzenia tabeli określić maszynęzapisu, należy na końcu instrukcji tworzącejtabelę dodać klauzulę:

CREATE TABLE nazwa_tabeli (nazwa_kolumny1 opis,nazwa_kolumny2 opis…) ENGINE = INNODB

W wersjach wcześniejszych niż 4.0.18 należałokorzystać ze słowa kluczowego TYPE zamiastENGINE. Jeżeli w czasie tworzenia tabel niezostanie podana maszyna zapisu, MySQL użyjedomyślnego typu tabel (InnoDB w Windows,w innych systemach MyISAM).

Tworzenie tabel

Rozdział 4.

106

Aby określić zestaw znaków lub sposóbsortowania dla całej tabeli, należy na końcuzapytania CREATE dodać odpowiednią klauzulę:

CREATE TABLE nazwa_tabeli (nazwa_kolumny1 opis,nazwa_kolumny2 opis…) ENGINE = MyISAM CHARACTER SETnazwa_zestawu COLLATE

nazwa_sposobu_sortowania

Teraz utwórzmy cztery tabele składające sięna bazę danych finanse.

W celu utworzenia tabel:

1. Otwórz klienta mysql i wybierz bazę finanse(rysunek 4.8).

USE finanse;

Tworzenie tabel będzie łatwiejsze, jeżeliwcześniej zostanie wybrana baza danych.Trzeba zalogować się jako użytkownikz uprawnieniami do tworzenia tabel w tejbazie danych.

2. Utwórz tabelę faktury (rysunek 4.9).CREATE TABLE faktury (faktura_id SMALLINT(4) UNSIGNED NOTNULL AUTO_INCREMENT,

klient_id SMALLINT(3) UNSIGNED NOT NULL,data_faktury TIMESTAMP NOT NULL,wartosc_faktury DECIMAL(10,2) UNSIGNEDNOT NULL,

opis_faktury TINYTEXT NOT NULL,data_platnosci DATE,PRIMARY KEY (faktura_id),INDEX (klient_id),INDEX (data_faktury),INDEX (wartosc_faktury),INDEX (data_platnosci));

Rysunek 4.8. Pierwszymi wykonanymi operacjamijest zalogowanie się do mysql i wybranie bazy finanse

Rysunek 4.9. Klient mysql pozwala na wprowadzaniepoleceń zajmujących kilka linii, co sprawia, że dłuższezapytania SQL są czytelniejsze

Twor

zeni

e ta

bel

Tworzenie bazy danych MySQL

107

Rysunek 4.10. MySQL informuje o poprawnymwykonaniu polecenia za pomocą komunikatuQuery OK

Przedstawione polecenie CREATEwykorzystuje dane dotyczące tabeli faktury,które zostały opracowane we wcześniejszejczęści rozdziału. Kolejność wprowadzaniakolumn determinuje sposób ichrozmieszczenia w tabeli. Po utworzeniusamych kolumn określono również związanez nimi indeksy, dzięki temu będą oneistniały natychmiast po utworzeniu tabeli.

Z uwagi na fakt, że monitor mysql nieprześle zapytania, zanim nie napotka znakuśrednika, wprowadzane polecenie możezajmować kilka linii, podobnie jak mato miejsce w sytuacji zaprezentowanejna rysunku 4.9.

3. Utwórz tabelę klienci (rysunek 4.10).CREATE TABLE klienci (klient_id SMALLINT(3) UNSIGNED NOTNULL AUTO_INCREMENT,

nazwa_klienta VARCHAR(40) NOT NULL,ulica_klienta VARCHAR(80) NOT NULL,miasto_klienta VARCHAR(30) NOT NULL,stan_klienta VARCHAR(2) NOT NULL,kod_pocztowy_klienta MEDIUMINT(5)UNSIGNED ZEROFILL NOT NULL,

telefon_klienta VARCHAR(14),osoba_kontaktowa VARCHAR(40),email_kontaktowy VARCHAR(60),PRIMARY KEY (klient_id),INDEX (nazwa_klienta));

Tabela ta posiada więcej kolumn, ale tylkodwa indeksy.

Tworzenie tabel

Rozdział 4.

108

4. Utwórz tabelę wydatki (rysunek 4.11).CREATE TABLE wydatki (wydatek_id SMALLINT(4) UNSIGNED NOTNULL AUTO_INCREMENT,

kategoria_wydatku_id TINYINT(3)UNSIGNED NOT NULL,

wartosc_wydatku DECIMAL(10,2)UNSIGNED NOT NULL,

opis_wydatku TINYTEXT NOT NULL,data_zaplaty TIMESTAMP NOT NULL,PRIMARY KEY (wydatek_id),INDEX (kategoria_wydatku_id),INDEX (wartosc_wydatku),INDEX (data_zaplaty));

5. Na koniec utwórz tabelę wydatki_kategorie(rysunek 4.12).

CREATE TABLE wydatki_kategorie (kategoria_wydatku_id TINYINT(3)UNSIGNED NOT NULL AUTO_INCREMENT,

kategoria_wydatku VARCHAR(30) NOT NULL,PRIMARY KEY (kategoria_wydatku_id),UNIQUE (kategoria_wydatku));

To najprostsza z przedstawionych czterechtabel, ponieważ posiada tylko dwie kolumnyi dwa indeksy.

Rysunek 4.11. Tworzenie trzeciej tabeli

Rysunek 4.12. Tworzenie czwartej i ostatniej tabeli

Twor

zeni

e ta

bel

Tworzenie bazy danych MySQL

109

6. Sprawdź, czy tabele zostały utworzone(rysunek 4.13).

SHOW TABLES;SHOW COLUMNS FROM faktury;

Polecenie SHOW (ang. pokaż) powodujewyświetlenie tabel bazy danych lub nazwkolumn tabeli i ich typów.

Wskazówki

Wymieniane w innych publikacjachpolecenie DESCRIBE nazwa_tabeli jesttożsame z instrukcją SHOW COLUMNS FROMnazwa_tabeli.

Można również wykonać zapytanie SHOWCREATE TABLE nazwa_tabeli, aby sprawdzić,za pomocą jakiego polecenia zostałautworzona tabela.

Jeżeli wykonamy zapytanie SHOW CREATETABLE nazwa_tabeli po utworzeniu tabeli,możemy zweryfikować sposób, w jakiMySQL implementuje nasze wyrażenie.Operacja ta pozwala sprawdzić, jak MySQLzmienia typy kolumn ze względówwydajnościowych.

Rysunek 4.13. Gdy chcesz sprawdzić istnienie i strukturę bazy danych, należy posłużyć siępoleceniem SHOW. Nie daj się zaskoczyć przez wyniki działania tego polecenia. MySQL posiadawłasny sposób opisu tabeli, który może różnić się od tego, w jaki sposób została ona utworzona

Tworzenie tabel

Rozdział 4.

110

Modyfikacja tabelOstatnim tematem, jaki przedstawimy w tymrozdziale, jest modyfikacja istniejących tabel.Można to wykonywać z dowolnych powodów,ale przed wprowadzeniem zmian należypomyśleć o wszystkich zasadach normalizacji,indeksach, konwencjach nazewnictwa i tegotypu elementach. Bardzo łatwo zaprzepaścićcałą pracę włożoną w planowanie przezwprowadzenie do bazy danych „niewielkiejpoprawki”.

Podstawową instrukcją SQL, służącądo modyfikowania struktury tabel w baziedanych, jest ALTER. Zazwyczaj wiąże się onaz procesem dodawania, usuwania lubprzekształcania kolumn, choć pozwalarównież na zmianę nazwy tabeli oraz zmianękluczy i indeksów. Umożliwia ona równieżzmianę nazwy całej tabeli i modyfikowanieindeksów. Podstawową składnią ALTER jest:

ALTER TABLE nazwa_tabeli KLAUZULE

Ze względu na mnogość dostępnych klauzulzostały one zestawione w tabeli 4.8. Pełna ichlista znajduje się w dodatku B, „Przegląd SQLi MySQL”.

Mod

yfik

acja

tab

el

Tabela 4.8. Polecenie SQL ALTER można wykorzystywać do modyfikowania tabeli na wiele sposobów

Klauzule ALTER TABLE

Klauzula Użycie ZnaczenieADD COLUMN ALTER TABLE nazwa_tabeli ADD COLUMN nazwa_kolumny

typ_kolumnyUmieszcza na końcu tabelinową kolumnę.

CHANGE COLUMN ALTER TABLE nazwa_tabeli CHANGE COLUMN nazwa_kolumnynowa_nazwa_kolumny nowy_typ_kolumny

Pozwala na zmianę typui właściwości kolumny.

DROP COLUMN ALTER TABLE nazwa_tabeli DROP COLUMN nazwa_kolumny Usuwa kolumnę z tabeliwraz z jej danymi.

ADD INDEX ALTER TABLE nazwa_tabeli ADD INDEX nazwa_indeksu(nazwa_kolumny)

Dodaje nowy indeks dladanej kolumny.

DROP INDEX ALTER TABLE nazwa_tabeli DROP INDEX nazwa_indeksu Usuwa istniejący indeks.RENAME AS ALTER TABLE nazwa_tabeli RENAME AS nowa_nazwa_tabeli Zmienia nazwę tabeli.

Tworzenie bazy danych MySQL

111

Rysunek 4.14. Do zmiany nazwy lub typu kolumnysłuży polecenie ALTER TABLE nazwa_kolumnyCHANGE COLUMN

Kasowanie tabel i baz danychAby usunąć tabelę lub bazę danych, należyskorzystać z polecenia DROP. Użycie tegopolecenia jest bardzo proste: DROP DATABASE nazwa_bazy_danych; DROP TABLE nazwa_tabeli;

Oczywiście, jeżeli skasujemy tabelę,wszystkie zapisane w niej dane zostanąutracone. Gdy skasujemy bazę danych,wszystkie tabele przejdą do historii.

W celu zademonstrowania zasad posługiwaniasię poleceniem ALTER pole osoba_kontaktowatabeli klienci zostanie rozdzielone na dwiekolumny (zgodne z założeniami normalizacji)osoba_kontaktowa_imiei osoba_kontaktowa_nazwisko. W tymprzykładzie zakładam, że w tabeli nie majeszcze żadnych danych. Jeżeli w tabeliznajdowałyby się dane, trzeba by wziąć topod uwagę (dodać dwie kolumny, przenieśćdo nich istniejące dane i skasować kolumnęźródłową). Ponieważ polecenie ALTERpowoduje w tabeli znaczne zmiany, powinnosię zawsze wykonać kopię tabeli przedrozpoczęciem jej modyfikacji (rozdział 13.).

Gdy chcesz zmienić strukturę tabeli:

1. Otwórz klienta mysql i wybierz bazę finanse,o ile wcześniej tego nie zrobiłeś.

USE finanse;

2. Zmień nazwę pola osoba_kontaktowa(rysunek 4.14).

ALTER TABLE klienci CHANGE COLUMNosoba_kontaktowa osoba_kontaktowa_imieVARCHAR(15);

Powyższe polecenie zmienia jedynie nazwęi typ danych kolumny osoba_kontaktowa.Od tej chwili nazwą pola jest osoba_kontaktowa_imię a typem danych w nimprzechowywanych — VARCHAR(15).Wszystkie dane przechowywanew kolumnie pozostały w niej, lecz zostałyskrócone do 15 znaków.

Modyfikacja tabel

Rozdział 4.

112

3. Utwórz nową kolumnę osoba_kontaktowa_nazwisko (rysunek 4.15).

ALTER TABLE klienci ADD COLUMNosoba_kontaktowa_nazwisko VARCHAR(25)AFTER osoba_kontaktowa_imie;

Od tej chwili tabela posiada jeszcze jednąkolumnę, w której nie ma aktualnie żadnychwartości. Dodając kolumnę do tabeli, możnaskorzystać z klauzuli AFTER nazwa_kolumny,aby wskazać, w którym miejscu tabelipowinna być umieszczona nowa kolumna.

4. Potwierdź wprowadzenie zmianw strukturze tabeli (rysunek 4.16).

SHOW COLUMNS FROM klienci;

Wskazówki

Aby zmienić typ istniejącej tabeli — jest tooczywiście dozwolone — należy skorzystaćz następującego polecenia ALTER:

ALTER TABLE nazwa_tabeli ENGINE = MYISAM

Można również potwierdzić wprowadzeniezmian w strukturze tabeli przez użyciepolecenia SHOW CREATE TABLE nazwa_tabeli.Jak zauważymy po uruchomieniu tegozapytania, nie będzie ono pokazywałooryginalnego polecenia CREATE, ale raczejpolecenie CREATE, jakie spowodujeutworzenie tabeli w takiej postaci jakobecnie.

Rysunek 4.15. ALTER TABLE nazwa_tabeli ADDCOLUMN dodaje nową kolumnę do tabeli

Rysunek 4.16. Potwierdzenie zmian w strukturze tabeli przez wykonaniepolecenia SHOW COLUMNS

Mod

yfik

acja

tab

el