Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00102 009738 11027986 na godz. na dobę w sumie
PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start - książka
PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start - książka
Autor: Liczba stron: 640
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-246-1723-4 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> webmasterstwo >> php - programowanie
Porównaj ceny (książka, ebook, audiobook).

Poznaj możliwości PHP6 oraz MySQL 5 i twórz dynamiczne strony WWW

Każda funkcjonalna i atrakcyjna dla użytkowników strona internetowa musi być na bieżąco aktualizowana, a umieszczone na niej interesujące informacje powinny być łatwo dostępne. Najpopularniejsze narzędzia typu open source, służące do tworzenia dynamicznych witryn, to język PHP i system zarządzania relacyjnymi bazami danych MySQL. Oba te narzędzia oferują wysoką wydajność, przenośność i niezawodność. Wśród wielu ogromnych możliwości oraz zalet PHP i MySQL mają także taką, że sprawne posługiwanie się nimi nie jest zbyt skomplikowane nawet dla początkujących.

Książka 'PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start' zawiera precyzyjny opis czynności oraz bogato ilustrowane zrzutami ekranu niezbędne wskazówki i wyjaśnienia, ułatwiające samodzielne zbudowanie dynamicznej strony internetowej. Dzięki temu podręcznikowi nauczysz się wyszukiwać i usuwać błędy w skryptach PHP, tworzyć formularze w języku HTML oraz zapobiegać atakom na Twoje witryny. Poznasz także podstawowe i zaawansowane techniki tworzenia różnych aplikacji (na przykład stron wielojęzycznych lub obsługujących fora dyskusyjne).

Szybko i łatwo naucz się tworzyć funkcjonalne oraz bezpieczne
witryny internetowe

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

PHP6 i MySQL 5. Dynammiczne strony www. Szybki start Autor: Larry Ullman T³umaczenie: Jaromir Senczyk ISBN: 978-83-246-1723-4 Tytu³ orygina³u: PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide Format: 170x230, stron: 640 Poznaj mo¿liwoœci PHP6 oraz MySQL 5 i twórz dynamiczne strony WWW • Jak utworzyæ podstawowy skrypt PHP? • Jak korzystaæ z wielowymiarowych tablic? • Jak budowaæ bazy danych? Ka¿da funkcjonalna i atrakcyjna dla u¿ytkowników strona internetowa musi byæ na bie¿¹co aktualizowana, a umieszczone na niej interesuj¹ce informacje powinny byæ ³atwo dostêpne. Najpopularniejsze narzêdzia typu open source, s³u¿¹ce do tworzenia dynamicznych witryn, to jêzyk PHP i system zarz¹dzania relacyjnymi bazami danych MySQL. Oba te narzêdzia oferuj¹ wysok¹ wydajnoœæ, przenoœnoœæ i niezawodnoœæ. Wœród wielu ogromnych mo¿liwoœci oraz zalet PHP i MySQL maj¹ tak¿e tak¹, ¿e sprawne pos³ugiwanie siê nimi nie jest zbyt skomplikowane nawet dla pocz¹tkuj¹cych. Ksi¹¿ka „PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start” zawiera precyzyjny opis czynnoœci oraz bogato ilustrowane zrzutami ekranu niezbêdne wskazówki i wyjaœnienia, u³atwiaj¹ce samodzielne zbudowanie dynamicznej strony internetowej. Dziêki temu podrêcznikowi nauczysz siê wyszukiwaæ i usuwaæ b³êdy w skryptach PHP, tworzyæ formularze w jêzyku HTML oraz zapobiegaæ atakom na Twoje witryny. Poznasz tak¿e podstawowe i zaawansowane techniki tworzenia ró¿nych aplikacji (na przyk³ad stron wielojêzycznych lub obs³uguj¹cych fora dyskusyjne). • PHP i MySQL • Tworzenie formularza w jêzyku HTML • Tablice i ³añcuchy • Tworzenie i wywo³ywanie w³asnych funkcji • Wype³nianie baz danych • Zabezpieczenia • Stosowanie modyfikatorów • Szyfrowanie danych • Tworzenie uniwersalnych witryn • Budowanie strony domowej • Wielojêzyczna strona WWW • Tworzenie kont u¿ytkowników i nadawanie uprawnieñ Szybko i ³atwo naucz siê tworzyæ funkcjonalne oraz bezpieczne witryny internetowe Spis treści Wprowadzenie 9 Czym są dynamiczne strony WWW? ................................................................... 10 Co będzie Ci potrzebne? ....................................................................................... 16 O tej książce ........................................................................................................... 17 19 Podstawy składni.................................................................................................... 20 Przesyłanie danych do przeglądarki internetowej ............................................... 24 Wstawianie komentarzy......................................................................................... 28 Co to są zmienne? .................................................................................................. 32 Łańcuchy ................................................................................................................ 36 Łączenie łańcuchów............................................................................................... 39 Liczby ..................................................................................................................... 41 Stałe ........................................................................................................................ 45 Apostrof kontra cudzysłów .................................................................................... 48 Programowanie w PHP 51 Tworzenie formularza w języku HTML............................................................... 52 Obsługa formularza HTML................................................................................... 56 Wyrażenia warunkowe i operatory ....................................................................... 60 Weryfikacja danych pochodzących z formularza ................................................. 64 Co to są tablice? ..................................................................................................... 70 Pętle for i while ...................................................................................................... 88 91 Wykorzystywanie plików zewnętrznych .............................................................. 92 Wyświetlanie i obsługa formularza przez jeden skrypt ....................................... 102 Tworzenie formularzy z pamięcią....................................................................... 107 Tworzenie i wywoływanie własnych funkcji...................................................... 110 125 Elementy bazy danych i ich nazwy..................................................................... 126 Wybór typu kolumny ........................................................................................... 128 Wybór innych właściwości kolumn .................................................................... 132 Korzystanie z serwera MySQL-a ........................................................................ 134 S p i s t r e ś c i 5 Rozdział 1. Wprowadzenie do PHP Rozdział 2. Rozdział 3. Tworzenie dynamicznych stron WWW Rozdział 4. Wprowadzenie do MySQL Rozdział 6. Zaawansowany SQL i MySQL i c ś e r t s i p S Rozdział 7. Obsługa i usuwanie błędów 141 Tworzenie baz danych i tabel.............................................................................. 142 Wprowadzanie rekordów..................................................................................... 145 Wybieranie danych .............................................................................................. 149 Wyrażenia warunkowe ........................................................................................ 151 Stosowanie LIKE i NOT LIKE.......................................................................... 154 Sortowanie wyników zapytania ........................................................................... 156 Ograniczanie wyników zapytania........................................................................ 158 Uaktualnianie danych .......................................................................................... 160 Usuwanie danych................................................................................................. 162 Funkcje................................................................................................................. 164 175 Projekt bazy danych............................................................................................. 176 Złączenia............................................................................................................... 191 Grupowanie wyników zapytania ......................................................................... 196 Indeksy ................................................................................................................. 198 Stosowanie różnych typów tabeli........................................................................ 203 Wyszukiwanie FULLTEXT................................................................................ 206 Wykonywanie transakcji...................................................................................... 212 217 Ogólne typy błędów i ich usuwanie.................................................................... 218 Wyświetlanie błędów PHP.................................................................................. 224 Sterowanie raportowaniem błędów PHP ........................................................... 226 Tworzenie własnych funkcji obsługi błędów ..................................................... 229 Techniki usuwania błędów z PHP ...................................................................... 234 Techniki usuwania błędów SQL i MySQL ........................................................ 238 241 Modyfikacja szablonu .......................................................................................... 242 Łączenie się z MySQL-em i wybieranie bazy.................................................... 244 Wykonywanie prostych zapytań.......................................................................... 248 Odczytywanie wyników zapytania ...................................................................... 257 Bezpieczeństwo zapytań...................................................................................... 261 Zliczanie zwróconych rekordów ......................................................................... 267 Uaktualnianie rekordów w PHP ......................................................................... 269 277 Przekazywanie wartości do skryptu..................................................................... 278 Stosowanie ukrytych pól formularza................................................................... 282 Edycja istniejących rekordów ............................................................................. 288 Spis treści Rozdział 5. Wprowadzenie do SQL Rozdział 8. PHP i MySQL Rozdział 9. Tworzenie aplikacji internetowych 6 Spis treści Rozdział 10. Tworzenie aplikacji internetowych Stronicowanie wyników zapytań......................................................................... 295 Wyświetlanie tabel z możliwością sortowania.................................................... 303 309 Wysyłanie poczty elektronicznej ........................................................................ 310 Funkcje daty i czasu............................................................................................. 316 Obsługa przesyłania plików................................................................................. 320 Skrypty PHP i JavaScript .................................................................................... 333 Nagłówki HTTP ................................................................................................... 340 345 Strona logowania.................................................................................................. 346 Funkcje logowania ............................................................................................... 349 Posługiwanie się ciasteczkami............................................................................. 354 Sesje...................................................................................................................... 367 Zwiększanie bezpieczeństwa sesji ...................................................................... 376 Rozdział 11. Sesje i „ciasteczka” Rozdział 12. Zabezpieczenia 379 Zapobieganie spamowi ........................................................................................ 380 Walidacja danych według typu ........................................................................... 387 Zapobieganie atakom XSS................................................................................... 392 Zapobieganie wstrzykiwaniu poleceń SQL........................................................ 395 Szyfrowanie i bazy danych .................................................................................. 401 S p i s t r e ś c i Rozdział 13. Wyrażenie regularne Perl 407 Skrypt testujący.................................................................................................... 408 Definiowanie prostych wzorców......................................................................... 412 Stosowanie kwantyfikatorów ............................................................................... 415 Klasy znaków........................................................................................................ 418 Wyszukiwanie wszystkich dopasowań................................................................ 421 Stosowanie modyfikatorów.................................................................................. 425 Dopasowywanie i zastępowanie wzorców.......................................................... 427 Rozdział 14. Tworzenie uniwersalnych witryn 431 Zbiory znaków i kodowanie................................................................................. 432 Tworzenie wielojęzycznych stron WWW .......................................................... 434 Unicode w PHP ................................................................................................... 438 Uporządkowanie zbioru znaków w PHP ............................................................ 442 Transliteracja w PHP........................................................................................... 445 Języki i MySQL.................................................................................................... 448 Strefy czasowe i MySQL ..................................................................................... 452 Lokalizatory.......................................................................................................... 455 7 Spis treści Rozdział 15. Forum dyskusyjne — przykład 459 Baza danych.......................................................................................................... 460 Szablony................................................................................................................ 469 Strona domowa..................................................................................................... 478 Strona forum......................................................................................................... 479 Strona wątku......................................................................................................... 484 Wstawianie wiadomości....................................................................................... 489 Rozdział 16. Rejestracja użytkowników — przykład 501 Tworzenie szablonu ............................................................................................. 502 Skrypty konfiguracyjne........................................................................................ 508 Tworzenie strony domowej ................................................................................. 516 Rejestracja ............................................................................................................ 518 Aktywacja konta ................................................................................................... 527 Logowanie i wylogowywanie się......................................................................... 531 Zarządzanie hasłami............................................................................................. 537 Rozdział 17. Sklep internetowy — przykład 547 Tworzenie bazy danych ....................................................................................... 548 Część administracyjna aplikacji .......................................................................... 554 Tworzenie szablonu części publicznej aplikacji................................................. 571 Katalog produktów............................................................................................... 575 Koszyk................................................................................................................... 587 Rejestrowanie zamówień..................................................................................... 597 Instalacja 605 Instalacja w systemie Windows .......................................................................... 606 Definiowanie uprawnień MySQL....................................................................... 609 Testowanie instalacji............................................................................................ 613 Konfigurowanie PHP........................................................................................... 616 Skorowidz 619 i c ś e r t s i p S Dodatek A 8 Zaawansowany SQL i MySQL 6 Rozdział 6. Zaawansowany SQL i MySQL Rozdział ten zaczyna się w miejscu, w którym ostatni się kończył. Omówię w nim bardziej zaawansowane zagadnienia dotyczące SQL-a i MySQL-a. Poznałeś już podstawy obu tych technologii i z pewnością wystarczą Ci one do realizacji wielu projektów, ale dopiero ich bardziej wyszukane możliwości wyniosą Twe aplikacje internetowe na wyższy poziom. Zacznę od szczegółowego omówienia procesu projektowania bazy danych, opierając się na przykładzie systemu zarządzania forum. Doprowadzi nas to oczywiście do tematu złączeń, będących integralną częścią każdej relacyjnej bazy danych. Następnie będę opisywał kolejną kategorię funkcji wbudowanych MySQL-a używanych do grupowania wyników zapytań. Na zakończenie przejdę do bardziej zaawansowanych zagadnień. Powiem o indeksach, nauczę Cię zmieniać strukturę istniejących tabel oraz omówię typy tabel dostępne w MySQL-u. Rozdział zakończę omówieniem dwóch dodatkowych możliwości MySQL-a: przeszukiwania tekstów i transakcji. Z a a w a n s o w a n y S Q L i M y S Q L 175 Rozdział 6. Projekt bazy danych Pierwsze, co musisz zrobić, gdy pracujesz z systemem zarządzania relacyjnymi bazami danych, takim jak MySQL, to utworzyć strukturę bazy (zwaną również schematem bazy danych). Projektowanie bazy danych lub inaczej modelowanie danych to niezbędny etap gwarantujący długotrwałe i bezproblemowe zarządzanie Twoimi informacjami. W procesie zwanym normalizacją eliminuje się niepotrzebne powtórzenia informacji i inne problemy, które zagrażają spójności danych. Dzięki technikom, które poznasz w tym rozdziale, Twoje bazy będą wydajne i niezawodne. Jeden z przykładów, które zaprezentuję — forum, na którym użytkownicy mogą wymieniać się opiniami — będzie intensywnie wykorzystywany dopiero w rozdziale 15., „Forum dyskusyjne — przykład”. Jednak omówione przeze mnie zasady normalizacji odnoszą się do wszystkich aplikacji bazodanowych, jakie możesz utworzyć. (Przykład bazy sitename używany w poprzednich dwóch rozdziałach został poprawnie zaprojektowany również z punktu widzenia normalizacji, ale zagadnienie to nie zostało jeszcze omówione.) Normalizacja Proces normalizacji został wymyślony na początku lat siedemdziesiątych przez E.F. Codda, naukowca z firmy IBM, który wymyślił też relacyjne bazy danych. Tego rodzaju bazy to nic innego jak tylko zbiór danych ułożonych w pewien określony sposób. Istnieje szereg tak zwanych postaci normalnych (NF, z ang. Normal Form), które ułatwiają definiowanie struktury danych. W tym rozdziale omówię pierwsze trzy z nich, ponieważ w większości przypadków są one w pełni wystarczające. Zanim zaczniesz normalizować swoją bazę danych, musisz określić, jakie funkcje będzie pełniła Twoja aplikacja. Być może będziesz musiał w tym celu porozmawiać z klientem lub samodzielnie zastanowić się nad tym zagadnieniem. W każdym razie struktura bazy danych zależy od sposobu, w jaki aplikacja będzie odwoływała się do zgromadzonych w niej informacji. Na tym etapie będziesz więc potrzebował raczej kartki i ołówka niż MySQL-a. Oczywiście, w ten sposób projektuje się wszystkie relacyjne bazy danych, nie tylko te działające w systemie MySQL. 176 h c y n a d y z a b t k e j o r P W moim przykładowym systemie będę chciał stworzyć forum, na którym użytkownicy mogą zamieszczać swoje opinie i odpowiadać innym internautom. Aby korzystać z forum, użytkownik będzie musiał się zarejestrować, a następnie uwierzytelnić za pomocą kombinacji nazwy i hasła. Przewiduję również możliwość istnienia wielu forów poświęconych różnym tematom. W tabeli 6.1 pokazałem, jak wygląda przykładowy rekord. Baza danych będzie nosić nazwę forum. Wskazówki (cid:132) Istnieje bardzo dobra metoda na określenie, jakiego rodzaju informacje powinny się znaleźć w bazie danych. Wystarczy, że zastanowisz się, jakiego rodzaju pytania o dane będą zadawane przez użytkowników i jakie dane będą musiały się znaleźć w odpowiedziach. (cid:132) Nauka normalizacji może sprawić Ci trudności, jeśli niepotrzebnie skoncentrujesz się na szczegółach. Każda z postaci normalnych jest zdefiniowana w dość skomplikowany sposób, a próba przełożenia tych definicji na język niefachowy może być myląca. Dlatego radzę Ci, abyś podczas lektury omówienia postaci normalnych skoncentrował się na ogólnym obrazie zmian zachodzących w schemacie bazy danych. Po zakończeniu normalizacji i otrzymaniu końcowej postaci bazy danych cały proces powinien stać się dla Ciebie wystarczająco zrozumiały. Tabela 6.1. Przykładowy rekord pokazujący, jakiego rodzaju informacje chcę przechowywać w mojej bazie danych Przykładowe dane forum Element username password actual name user email forum message subject Pytanie na temat normalizacji message body Przykład janek haslo Jan Kowalski jank@example.com MySQL message date Nie rozumiem jednej rzeczy. Dla drugiej postaci normalnej (2NF) podano... 2 lutego 2008 12:20 Klucze W rozdziale 4., „Wprowadzenie do MySQL-a”, wspominałem już, że klucze są integralną częścią znormalizowanych baz danych. Spotkasz się z dwoma rodzajami kluczy, głównymi i obcymi. Klucz główny to unikatowy identyfikator, który podlega pewnym ściśle określonym regułom. Musi on: (cid:88) Zawsze mieć jakąś wartość (inną niż NULL). (cid:88) Mieć stałą wartość (taką, która nigdy nie ulega zmianie). (cid:88) Mieć inną wartość dla każdego rekordu w tabeli. Najlepszym, z życia wziętym przykładem klucza głównego jest numer ubezpieczenia społecznego przydzielany obywatelom USA. Każdy ma tam własny, niezmienny, unikatowy numer polisy. Ułatwia to identyfikowanie osób. Wkrótce przekonasz się, że wielokrotnie sam będziesz tworzył we wszystkich tabelach klucze główne. Jest to po prostu dobra praktyka projektowa. Drugi rodzaj kluczy stanowią klucze obce. Reprezentują one w tabeli B klucze główne tabeli A. Jeżeli masz na przykład bazę danych filmy, w której występują tabele film i reżyser, to klucz główny tabeli reżyser będzie pełnił rolę klucza obcego w tabeli film. Już niedługo zobaczysz, jak to wszystko działa w praktyce. Tabela 6.2. Dodałem do tabeli klucz główny, dzięki czemu będę mógł łatwo odwoływać się do rekordów Baza danych forum Element message ID username password actual name user email forum message subject Pytanie na temat normalizacji message body Przykład 1 janek haslo Jan Kowalski jank@example.com MySQL message date Nie rozumiem jednej rzeczy. Dla drugiej postaci normalnej (2NF) podano... 2 lutego 2008 12:20 Zaawansowany SQL i MySQL Baza danych forum składa się w zasadzie tylko z jednej prostej tabeli (tabela 6.1), ale zanim rozpocznę proces normalizacji, chcę utworzyć w niej przynajmniej jeden klucz główny (klucze obce pojawią się w następnych krokach). Aby przypisać klucz główny: 1. Poszukaj pól, które spełniają wszystkie trzy warunki określone dla kluczy głównych. W naszym przykładzie (tabela 6.1) żadna z kolumn nie spełnia kryteriów klucza głównego. Nazwa użytkownika i adres e-mail są unikalne dla każdego użytkownika forum, ale nie dla każdego rekordu bazy danych (ten sam użytkownik może umieszczać wiele wiadomości na forum). Również ten sam temat wiadomości może występować wiele razy. Tekst wiadomości będzie prawdopodobnie zawsze unikalny, ale może się zmieniać na skutek późniejszych poprawek, tym samym naruszając jedno z kryteriów wyboru klucza głównego. 2. Jeżeli nie istnieje żaden logiczny kandydat na klucz główny — wprowadź go! (tabela 6.2). Sytuacja, w której musisz sam utworzyć klucz główny, ponieważ żadne z istniejących pól nie nadaje się do pełnienia tej roli, występuje dość często. W tym konkretnym przykładzie utworzę pole message ID. Wskazówki (cid:132) Stosuję się do reguły, w myśl której w nazwach kluczy głównych powinna wystąpić przynajmniej część nazwy tabeli (np. message) i przyrostek id. Niektórzy projektanci dodają również skrót pk (ang. primary key — klucz główny). (cid:132) MySQL zezwala na stosowanie w każdej tabeli tylko jednego klucza głównego, choć możesz oprzeć go na kilku kolumnach (oznacza to, że kombinacja tych kolumn musi być unikatowa). (cid:132) Najlepiej byłoby, gdyby Twój klucz główny był zawsze liczbą całkowitą, ponieważ pozwala to MySQL-owi osiągnąć najwyższą możliwą wydajność. 177 P r o j e k t b a z y d a n y c h Wskazówki (cid:132) Modelowanie baz danych rządzi się pewnymi regułami. Istnieje określona konwencja reprezentowania struktury bazy (zostanie ona tutaj zachowana). Na rysunku 6.1 pokazałem symbole trzech rodzajów zależności. (cid:132) Proces projektowania bazy danych prowadzi do powstania diagramu zależności między encjami (ERD), na którym występują prostokąty reprezentujące tabele oraz symbole z rysunku 6.1. (cid:132) Istnieje wiele programów służących do tworzenia schematów baz danych. Jednym z nich jest MySQL Workbench (www.mysql.com). (cid:132) Termin „relacyjny” w określeniu „system zarządzania relacyjnymi bazami danych” odnosi się do tabel, które nazywa się relacjami. Rysunek 6.1. Pokazane tu symbole często spotyka się na diagramach strukturalnych. Opisują one zależności występujące między tabelami Rozdział 6. Zależności Mówiąc o zależnościach w bazach danych mam na myśli to, w jaki sposób dane z jednej tabeli są powiązane z danymi występującymi w drugiej. Zależności między dwiema tabelami mogą przybierać postać jeden do jednego, jeden do wielu lub wiele do wielu. (Dwie tabele tej samej bazy danych mogą być również wcale niepowiązane). O zależności „jeden do jednego” mówimy wtedy, gdy jeden i tylko jeden element tabeli A odnosi się do jednego i tylko jednego elementu tabeli B (np. każdy mieszkaniec USA ma tylko jeden numer ubezpieczenia społecznego, a każdy numer polisy jest przyporządkowany tylko do jednego obywatela. Nikt nie może mieć dwóch polis, podobnie jak żaden numer ubezpieczenia nie może odnosić się do dwóch osób). Zależność „jeden do wielu” występuje wtedy, gdy jakiś element tabeli A może odnosić się do kilku różnych elementów tabeli B. Na przykład, określenia mężczyzna i kobieta mogą być używane w odniesieniu do wielu osób, ale każdy człowiek może mieć tylko jedną płeć. Jest to najczęściej występująca zależność między tabelami w znormalizowanych bazach danych. Istnieje też zależność „wiele do wielu”, w której kilka elementów tabeli A może odnosić się do kilku elementów tabeli B. Na przykład rekord płyta może zawierać piosenki wykonywane przez różnych artystów, a każdy artysta może mieć na swoim koncie kilka płyt. W swoich projektach powinieneś unikać tego typu zależności, ponieważ prowadzą one do problemów ze spójnością danych i sprzyjają ich powielaniu. Zamiast zależności „wiele do wielu” stworzysz tabelę pośrednią, dzięki której zależność „wiele do wielu” zostanie rozbita na dwie zależności „jeden do wielu”. Temat zależności jest w pewien sposób związany z zagadnieniem kluczy, ponieważ klucze zdefiniowane w jednej tabeli odwołują się zazwyczaj do pól występujących w innych tabelach. 178 h c y n a d y z a b t k e j o r P Pierwsza postać normalna Jak już powiedziałem wcześniej, normalizacja bazy danych jest procesem dostosowywania struktury bazy danych do kilku postaci. Dostosowywanie to musi być precyzyjne i odbywać się w określonej kolejności. Mówimy, że baza danych jest pierwszej postaci normalnej (1NF), jeżeli: (cid:88) Każda kolumna zawiera tylko jedną wartość (czyli jest atomowa lub niepodzielna). (cid:88) Żadna tabela nie ma powtarzających się kolumn dla danych pozostających w pewnej zależności. Tabela 6.3. Tabela z atomowymi kolumnami Baza danych forum Element message ID username password first name last name user email forum message subject message body Przykład 1 janek haslo Jan Kowalski jank@example.com MySQL Pytanie na temat normalizacji Nie rozumiem jednej rzeczy. Dla drugiej postaci normalnej (2NF) podano... 2 lutego 2008 12:20 message date Zaawansowany SQL i MySQL Na przykład tabela zawierająca pole, w którym można umieścić kilka numerów telefonów (domowy, komórkowy, numer faksu itd.) nie jest zgodna z pierwszą postacią normalną, ponieważ w jednej kolumnie może się znaleźć więcej niż jedna wartość. Jeśli chodzi o drugi warunek, to tabela film zawierająca kolumny aktor1, aktor2, aktor3 i tak dalej, nie będzie w pierwszej postaci normalnej, ponieważ powtarzające się kolumny zawierają ten sam rodzaj informacji. Proces normalizacji rozpocznę od sprawdzenia, czy aktualna struktura bazy (tabela 6.2) jest zgodna z 1NF. Kolumny, które nie są atomowe, zostaną rozbite na wiele kolumn. Jeśli tabela posiada powtarzające się, podobne kolumny, to zostaną one przekształcone w osobną tabelę. Aby uczynić bazę zgodną z 1NF: 1. Zidentyfikuj pole, które może zawierać kilka informacji naraz. Gdy spojrzysz jeszcze raz na tabelę 6.2, zobaczysz, że jedno z pól nie jest zgodne z 1NF: actual name. Zawiera ono zarówno imię jak i nazwisko użytkownika. Pole message date (data dodania do bazy) przechowuje, co prawda, dzień, miesiąc i rok, ale raczej nie będzie nas interesować taki poziom szczegółowości i potraktujemy przechowywaną przez nie wartość jako niepodzielną. Zresztą pod koniec poprzedniego rozdziału pokazałem, że MySQL potrafi doskonale obsługiwać dane reprezentujące daty i czas za pomocą typu DATETIME. Gdyby tabela zawierała na przykład jedną, wspólną kolumnę dla imienia i nazwiska zamiast dwóch osobnych albo przechowywała wiele numerów telefonów (stacjonarny, komórkowy, domowy, służbowy) w jednej kolumnie, to kolumny te również należałoby rozbić. 2. Rozbij wszystkie pola odszukane w kroku 1. na kilka mniejszych, niepodzielnych pól (tabela 6.3). 179 P r o j e k t b a z y d a n y c h Rozdział 6. Aby poradzić sobie z tym problemem, utwórz osobne pola first name i last name, które będą zawierać tylko jedną wartość. 3. Przekształć każdą grupę powtarzających się kolumn w osobną tabelę. Problem ten nie występuje w bazie danych forum. Zademonstruję go zatem na przykładzie przedstawionym w tabeli 6.4. Powtarzające się kolumny zawierające informacje o aktorach powodują dwa problemy. Po pierwsze, dla każdego filmu można podać tylko ograniczoną liczbę aktorów. Nawet jeśli wprowadzimy kolumny aktor 1 aż do aktor 100, to ograniczeniem będzie stu aktorów. Po drugie, każdy rekord, który nie zawiera maksymalnej liczby aktorów, będzie mieć wartości NULL w nadmiarowych kolumnach. W schemacie bazy danych powinniśmy unikać kolumn zawierających wartości NULL. Dodatkowym problemem jest również to, że kolumny zawierające informacje o reżyserze i aktorach nie są atomowe. Aby rozwiązać problemy występujące w tabeli film, utworzę dodatkową tabelę (tabela 6.5). Każdy jej rekord zawiera informacje o jednym autorze, co rozwiązuje problemy opisane powyżej. Także nazwiska i imiona aktorów są teraz przechowywane jako wartości atomowe. Zwróć również uwagę, że do nowej tabeli dodałem kolumnę indeksu głównego. Zasada, że każda tabela posiada klucz główny, wynika wprost z pierwszej postaci normalnej. 4. Upewnij się, że wszystkie nowe pola utworzone w kroku 2. i 3. są zgodne z 1NF. h c y n a d y z a b t k e j o r P 180 Wskazówki (cid:132) Dostosowanie tabeli do 1NF wymaga analizy tabeli w poziomie. Wszystkie kolumny jednego rekordu przeglądamy pod kątem występowania w nich danych, które nie są atomowe oraz występowania powtarzających się, podobnych danych. (cid:132) Postacie normalne opisane są w różnych źródłach w różny sposób, często z użyciem bardziej technicznego żargonu. Najważniejszy jest jednak sens i końcowy rezultat procesu normalizacji, a nie słownictwo zastosowane do jego opisu. Tabela 6.4. Tabela film nie jest zgodna z 1NF z dwóch powodów. Po pierwsze, zawiera powtarzające się kolumny podobnych danych (aktor1 itd.). Po drugie, kolumny aktor i reżyser nie zawierają wartości atomowych Tabela film Kolumna film ID tytuł filmu rok produkcji reżyser aktor1 aktor2 aktor3 Wartość 976 Casablanca 1943 Michael Curtiz Humphrey Bogart Ingrid Bergman Peter Lorre Tabela 6.5. Aby tabela film (tabela 6.4) była zgodna z 1NF, powiążę aktorów z filmami za pomocą tabeli film-aktor Tabela film-aktor ID Film 1 2 3 4 5 Casablanca Casablanca Casablanca Sokół maltański Humphrey Sokół maltański Peter Imię aktora Nazwisko aktora Humphrey Ingrid Peter Bogart Bergman Lorre Bogart Lorre Rysunek 6.2. Aby baza danych o filmach była zgodna z 2NF, potrzebne są cztery tabele. Reżyserzy są reprezentowani w tabeli film za pomocą klucza reżyser ID; filmy są reprezentowane w tabeli film-aktor za pomocą klucza film ID; aktorzy są reprezentowani w tabeli film-aktor za pomocą klucza aktor ID Zaawansowany SQL i MySQL Druga postać normalna Każda baza, która ma spełniać drugą postać normalną (2NF), musi być najpierw zgodna z 1NF (proces normalizacji trzeba przeprowadzać we właściwej kolejności). Następnie wszystkie kolumny, które nie zawierają kluczy (kluczy obcych), muszą być powiązane zależnością z kluczem głównym. Kolumny, które naruszają ten warunek, łatwo zidentyfikować po tym, że zawierają wartości niebędące kluczami i powtarzające się w różnych rekordach. Wartości te muszą zostać umieszczone w osobnej tabeli i być powiązane z tabelą wyjściową za pomocą klucza. Przykładem może być fikcyjna tabela film (tabela 6.4), która zawierałaby ponad dwadzieścia razy nazwisko Martina Scorsese jako reżysera różnych filmów. Sytuacja taka jest niezgodna z drugą postacią normalną, ponieważ kolumna zawierająca informację o reżyserze nie jest kluczem i nie jest powiązana zależnością z kluczem głównym (film ID). Rozwiązanie polega na utworzeniu osobnej tabeli reżyserzy wyposażonej we własny klucz główny, który wystąpiłby jako klucz obcy w tabeli film, tworząc w ten sposób powiązanie obu tabel. Patrząc na tabelę 6.5, można dostrzec dwa kolejne naruszenia drugiej 2NF — ani tytuły filmów, ani nazwiska aktorów nie są powiązane z kluczem głównym tabeli. Zatem baza danych o filmach w najprostszej postaci wymaga czterech tabel (rysunek 6.2). W ten sposób informacje o każdym filmie, aktorze i reżyserze są przechowywane tylko jeden raz, a każda kolumna niebędąca kluczem jest zależna od klucza głównego danej tabeli. W zasadzie proces normalizacji można by określić jako tworzenie coraz większej liczby tabel, tak aby całkowicie wyeliminować możliwość powielenia jakichkolwiek danych. P r o j e k t b a z y d a n y c h 181 Rysunek 6.3. Aby baza danych forum była zgodna z 2NF, potrzebne są trzy tabele Rysunek 6.4. Każda tabela powinna mieć własny klucz główny Rozdział 6. Aby uczynić bazę zgodną z 2NF: 1. Zidentyfikuj kolumny, które nie są kluczami i które nie są powiązane z kluczem głównym. Przyglądając się tabeli 6.3 zauważysz, że żadne z pól username, first name, last name, email i forum nie są kluczami (jedyną kolumną będącą kluczem jest na razie message ID) i żadne z nich nie jest powiązane zależnością z message ID. Natomiast message subject, body i date również nie są kluczami, ale ich wartości zależą od klucza message ID. 2. Utwórz odpowiednie tabele (patrz rysunek 6.3). Najlogiczniejsza modyfikacja istniejącej struktury polega na utworzeniu trzech tabel: users, forums i messages. Na diagramie bazy danych każda tabela została przeze mnie oznaczona prostokątem. Jej nazwa pełni rolę nagłówka, pod którym wymienione są wszystkie kolumny (atrybuty) tabeli. 3. Przypisz lub utwórz nowe klucze główne (rysunek 6.4). Posługując się technikami opisanymi wcześniej w tym rozdziale, upewnij się, że każda nowa tabela ma zdefiniowany klucz główny. W tabeli users stworzyłem klucz user ID, a w tabeli forums klucz forum ID. Ponieważ pole username w tabeli users oraz pole name w tabeli forums muszą być unikalne dla każdego rekordu i zawsze mieć wartość, to mógłbyś użyć ich jako kluczy głównych. Oznaczałoby to jednak, że wartości tych pól nie mogą się zmieniać (zgodnie z jednym z kryteriów wyboru klucza głównego). Użycie kluczy tekstowych zamiast numerycznych powodowałoby również nieco wolniejsze działanie bazy danych. h c y n a d y z a b t k e j o r P 182 Zaawansowany SQL i MySQL 4. Utwórz pomocnicze klucze obce, które połączą tabele zależnościami (rysunek 6.5). Ostatni krok na drodze do zgodności z 2NF polega na dodaniu kluczy obcych, które określą powiązania między tabelami. Pamiętaj, że to, co w jednej tabeli jest kluczem głównym, w drugiej najprawdopodobniej będzie kluczem obcym. W naszym przykładzie kolumna user ID z tabeli users łączy się z kolumną user ID z tabeli messages. Dlatego też tabela users pozostaje w zależności „jeden do wielu” z tabelą messages (każdy użytkownik może umieścić na forum wiele wiadomości, ale każda wiadomość może mieć tylko jednego autora). Połączone zostały również dwie kolumny forum ID, tworząc w ten sposób zależność „jeden do wielu” pomiędzy tabelami messages i forums (każda wiadomość może należeć tylko do jednego forum, ale dane forum może zawierać wiele wiadomości). (cid:132) W prawidłowo znormalizowanej bazie danych w jednej tabeli nie mają prawa pojawić się dwa takie same rekordy (dwa lub więcej rekordów, w których wartości występujące w poszczególnych kolumnach są identyczne). (cid:132) Aby łatwiej przyswoić sobie proces normalizacji, zapamiętaj, że pierwsza postać normalna wiąże się z analizą tabeli w poziomie, podczas gdy druga postać normalna powstaje na skutek analizy w pionie (poszukiwania wartości powtarzających się w wielu rekordach). P r o j e k t b a z y d a n y c h 183 Rysunek 6.5. Aby zdefiniować zależność między tymi trzema tabelami, dodałem do tabeli messages dwa klucze obce, z których każdy łączy ją z jedną z pozostałych dwóch tabel Wskazówki (cid:132) Inny sposób sprawdzenia, czy tabele spełniają drugą postać normalną, polega na przyjrzeniu się powiązaniom tabel. Idealna sytuacja polega na występowaniu samych zależności „jeden do wielu”. Tabele podlegające zależnościom „wiele do wielu” mogą wymagać restrukturyzacji. (cid:132) Jeśli przyjrzymy się jeszcze raz rysunkowi 6.2, możemy zauważyć, że tabela film-aktor spełnia funkcję tabeli pośredniczącej. Pozwala ona zamienić zależność „wiele do wielu” zachodzącą pomiędzy filmami i aktorami na dwie zależności „jeden do wielu”. Tabele pośredniczące łatwo rozpoznać po tym, że wszystkie ich kolumny są kluczami obcymi. W tym przypadku kolumna odgrywająca rolę klucza głównego nie jest potrzebna, ponieważ kluczem głównym może być kombinacja obu kolumn tabeli. Rozdział 6. Trzecia postać normalna Mówimy, że baza danych spełnia trzecią postać normalną (3NF), jeżeli jest ona zgodna z 2NF, a każda kolumna, która nie jest kluczem, jest zależna od klucza głównego. Jeżeli prawidłowo przeszedłeś przez pierwsze dwa etapy normalizacji, prawdopodobnie dostosowanie bazy danych do 3NF nie będzie już wymagać żadnych zmian. W moim przykładzie (patrz rysunek 6.5) również nie ma problemów, które należałoby rozwiązać, aby baza była zgodna z trzecią postacią normalną. Dlatego też przedstawię je omawiając hipotetyczną sytuację. Weźmy na przykład pojedynczą tabelę przechowującą informacje o zarejestrowanych klientach: imię, nazwisko, e-mail, numer telefonu, adres pocztowy itp. Tabela taka nie jest zgodna z 3NF, ponieważ wiele kolumn nie będzie zależnych od klucza głównego. Nazwa ulicy będzie zależeć od miasta, a miasto od stanu. Również kod pocztowy nie będzie zależeć od tego, jaką osobę opisuje rekord. Aby znormalizować taką bazę danych, powinno się stworzyć osobną tabelę reprezentującą państwa, osobną reprezentującą miasta (połączoną kluczem obcym z tabelą państw) i jeszcze inną dla kodów. Wszystkie te tabele byłyby połączone z tabelą opisującą klientów. Jeśli takie rozwiązanie wydaje Ci się przesadą, to masz rację. Wyższy stopień normalizacji często nie jest konieczny. Chociaż powinno się dążyć do jak najpełniejszej normalizacji, to czasami warto ją poświęcić na rzecz prostoty. (patrz ramka „Rezygnacja z normalizacji”). W praktyce stopień normalizacji zależy od potrzeb konkretnej aplikacji i szczegółów bazy danych. Jak już wspomniałem, nasz przykład bazy forum nie wymaga dalszej normalizacji (jest już zgodny z 3NF) i dlatego proces dostosowywania do trzeciej postaci normalnej przedstawię właśnie na przykładzie omówionej wyżej bazy danych o klientach. h c y n a d y z a b t k e j o r P Aby uczynić bazę zgodną z 3NF: 1. Zidentyfikuj wszystkie pola, które nie są bezpośrednio powiązane z kluczem głównym. Jak już wspomniałem, na tym etapie poszukujemy kolumn, które powiązane są między sobą zależnościami (tak jak miasto i stan) zamiast z kluczem głównym. W bazie danych forum nie występowały takie zależności. Przyjrzyjmy się tabeli messages. Każda wartość pola subject jest specyficzna dla danego message ID, każda wartość pola body jest specyficzna dla wybranego message ID itd. 2. Utwórz odpowiednie tabele. Jeśli w punkcie 1. udało się odnaleźć problematyczne kolumny, takie jak na przykład miasto i stan, to tworzymy dla nich osobne tabele cities i states. 3. Przypisz lub utwórz nowe klucze główne. Każda tabela musi mieć klucz główny, wobec czego do nowych tabel dodaję kolumny city ID i state ID. 4. Utwórz pomocnicze klucze obce, definiując tym samym zależności (rysunek 6.6). Na zakończenie dodałem do tabeli Cities klucz obcy State ID oraz klucz obcy City ID do tabeli Clients. W efekcie uzyskałem połączenie rekordu klienta nie tylko z informacją o mieście, w którym jest zameldowany, ale również o stanie. 184 Rysunek 6.6. Uproszczona wersja hipotetycznej bazy clients będzie zawierać dwie nowe tabele przechowujące informacje o miastach i stanach Zaawansowany SQL i MySQL Wskazówka (cid:132) W praktyce nie normalizowałbym tabeli Clients aż do takiego stopnia. Gdybym pozostawił pola opisujące miasto i stan w tabeli Clients, to najgorszą rzeczą, jaka mogłaby się zdarzyć, byłaby zmiana nazwy miasta i związana z tym konieczność aktualizacji rekordów wszystkich klientów będących jego mieszkańcami. W rzeczywistości jednak sytuacja taka zdarza się bardzo rzadko. (cid:132) Mimo istnienia zasad normalizacji baz danych dwóch różnych projektantów może dokonać normalizacji tej samej bazy w nieco inny sposób. Projektowanie baz danych pozostawia pewien margines dla indywidualnych preferencji i interpretacji. Najważniejsze jest, by zaprojektowana baza danych nie naruszała postaci normalnej, gdyż prędzej czy później doprowadzi to do pojawienia się poważnych problemów. P r o j e k t b a z y d a n y c h 185 Rezygnacja z normalizacji Choć spełnienie trzeciej postaci normalnej jest korzystne, nie oznacza to jeszcze, że masz normalizować każdą bazę danych, z którą pracujesz. Jednocześnie powinieneś uzmysłowić sobie, że odejście od sprawdzonych metod może mieć w perspektywie dłuższego czasu katastrofalne skutki. Z normalizacji rezygnuje się zazwyczaj z dwóch powodów — ze względu na wydajność i dla wygody. Dużo łatwiej jest ogarnąć mniejszą liczbę tabel, a poza tym — łatwiej się nimi zarządza. Ze względu na liczbę powiązań między tabelami, uaktualnianie, odczytywanie i modyfikowanie danych w znormalizowanych bazach danych trwa z reguły dłużej. Krótko mówiąc, normalizacja jest poświęceniem prostoty i szybkości na rzecz spójności i skalowalności. Należy jednak pamiętać, że istnieje znacznie więcej sposobów na przyspieszenie bazy danych niż na odzyskanie informacji utraconych na skutek przechowywania ich w źle zaprojektowanej bazie. W miarę nabywania doświadczenia będziesz potrafił coraz lepiej modelować bazy danych, ale mimo wszystko staraj się trzymać po stronie normalizacji. Rozdział 6. Tworzenie bazy danych Aby zakończyć projekt bazy danych, musimy wykonać trzy ostatnie kroki: 1. Sprawdzić, czy w bazie znajdą się wszystkie potrzebne informacje. 2. Zidentyfikować typy kolumn. 3. Nazwać wszystkie elementy bazy danych. Ostateczny projekt bazy danych został przedstawiony w tabeli 6.6. W porównaniu do rysunku 6.5 została dodana jeszcze jedna kolumna. Ponieważ wiadomość umieszczana na forum może być odpowiedzią na inną wiadomość, musimy jakoś reprezentować tę zależność w bazie. Rozwiązanie polega na dodaniu kolumny parent_id w tabeli messages. Jeśli wiadomość jest odpowiedzią, to jej pole parent_id będzie zawierać wartość pola message_id oryginalnej wiadomości (czyli message_id spełnia funkcję klucza obcego w tej samej tabeli). Jeśli pole parent_id ma wartość równą 0, oznacza to, że wiadomość stanowi początek nowego wątku, czyli nie jest odpowiedzią na żadną inną wiadomość. Jeśli wprowadzasz jakiekolwiek zmiany w strukturze tabel, powinieneś ponownie sprawdzić, czy spełniają one wszystkie postacie normalne, aby mieć pewność, że baza danych jest nadal znormalizowana. Zagadnienie nazw tabel i kolumn oraz wyboru typów kolumn omówiłem już w rozdziale 4. Gdy schemat jest gotowy, możesz utworzyć odpowiadającą mu bazę danych MySQL-a za pomocą poleceń omówionych w rozdziale 5., „Wprowadzenie do SQL”. Aby utworzyć bazę danych: 1. Użyj wybranego klienta do dostępu do serwera MySQL-a. Podobnie jak w poprzednim rozdziale, we wszystkich przykładach będziemy posługiwali się monitorem (klientem) mysqla. Oczywiście możesz też śmiało korzystać z phpMyAdmina i innych narzędzi. 186 h c y n a d y z a b t k e j o r P 2. Utwórz bazę danych forum (rysunek 6.7). CREATE DATABASE forum; USE forum; Możliwe, że Twoja konfiguracja nie pozwala na tworzenie nowych baz danych. W takiej sytuacji po prostu wykorzystaj jakąś już istniejącą bazę i dodawaj do niej kolejne tabele. Tabela 6.6. Ostateczny projekt bazy forum wraz z typami kolumn forum Nazwa kolumny forum_id name message_id forum_id parent_id user_id subject body date_entered user_id username pass first_name last_name email Typ kolumny TINYINT VARCHAR(60) INT TINYINT INT MEDIUMINT VARCHAR(100) LONGTEXT TIMESTAMP MEDIUMINT VARCHAR(30) CHAR(40) VARCHAR(20) VARCHAR(40) VARCHAR(80) Tabela forums forums messages messages messages messages messages messages messages users users users users users users Rysunek 6.7. Najpierw musisz utworzyć i wybrać bazę danych 3. Utwórz tabelę forums (rysunek 6.8). CREATE TABLE forums ( forum_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(60) NOT NULL, PRIMARY KEY (forum_id) ); Kolejność, w jakiej tworzysz tabele, nie ma oczywiście znaczenia. Ja zacznę od forums. Pamiętaj, że zawsze możesz rozpisać polecenie SQL w kilku wierszach na ekranie, jeżeli tylko będzie Ci tak wygodniej. Rysunek 6.8. Utwórz pierwszą tabelę Rysunek 6.9. Utwórz drugą tabelę Zaawansowany SQL i MySQL Tabela ta zawiera tylko dwie kolumny (sytuacja taka ma często miejsce w przypadku znormalizowanych baz danych). Ponieważ nie spodziewam się, że tabela ta będzie zawierać dużą liczbę rekordów, klucz główny otrzymał typ TINYINT. Jeśli chcesz, by baza zawierała również opis każdego forum, możesz dodać do tej tabeli kolumnę typu VARCHAR(255). 4. Utwórz tabelę messages (rysunek 6.9). CREATE TABLE messages ( message_id INT UNSIGNED NOT NULL AUTO_INCREMENT, forum_id TINYINT UNSIGNED NOT NULL, parent_id INT UNSIGNED NOT NULL, user_id MEDIUMINT UNSIGNED NOT NULL, subject VARCHAR(100) NOT NULL, body LONGTEXT NOT NULL, date_entered TIMESTAMP NOT NULL, PRIMARY KEY (message_id) ); W tym przypadku klucz główny musi być zdecydowanie bardziej pojemny, ponieważ spodziewam się, że tabela ta będzie zawierać bardzo dużo rekordów. Trzy kolumny będące kluczami obcymi — forum_id, parent_id i user_id — będą mieć taki sam rozmiar i typ jak ich odpowiedniki będące kluczami głównymi w innych tabelach. Pole subject zostało ograniczone do 100 znaków, a pole body może zawierać znaczną ilość tekstu. Pole date_entered otrzymało typ TIMESTAMP. Będzie ono przechowywać datę i czas dodania rekordu. Jego wartość zostanie automatycznie zaktualizowana (bieżącą datą i czasem) w momencie wstawienia rekordu (właśnie w ten sposób zachowuje się typ TIMESTAMP). P r o j e k t b a z y d a n y c h 187 Rozdział 6. 5. Utwórz tabelę users (rysunek 6.10). CREATE TABLE users ( user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(30) NOT NULL, pass CHAR(40) NOT NULL, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(40) NOT NULL, email VARCHAR(80) NOT NULL, PRIMARY KEY (user_id) ); Większość kolumn tej tabeli wykorzystuje definicje znane z tabeli users bazy sitename używanej w poprzednich dwóch rozdziałach. Kolumna pass jest typu CHAR(40), ponieważ dla haseł będę stosować funkcję SHA1(), która zwraca zawsze łańcuch o długości 40 znaków (patrz rozdział 5.). 6. Jeśli chcesz, możesz upewnić się, że baza danych ma taką strukturę jak powinna (rysunek 6.11). SHOW TABLES; SHOW COLUMNS FROM forums; SHOW COLUMNS FROM messages; SHOW COLUMNS FROM users; Ten krok jest opcjonalny, ponieważ MySQL i tak wyświetla informacje o pomyślnym wykonaniu każdego wprowadzanego polecenia. Warto jednak przypomnieć sobie strukturę bazy danych. Wskazówka (cid:132) W przypadku połączenia klucz główny-klucz obcy (na przykład forum_id tabeli forum z forum_id tabeli messages), obie kolumny muszą być tego samego typu (w naszym przykładzie: TINYINT UNSIGNED NOT NULL). h c y n a d y z a b t k e j o r P Rysunek 6.10. Trzecia i ostatnia tabela w bazie Rysunek 6.11. Sprawdź strukturę bazy za pomocą polecenia SHOW 188 Wypełnianie bazy danych W rozdziale 15. napiszemy w PHP interfejs WWW dla bazy forums. Będzie on dostarczać standardowego sposobu wypełniania bazy danymi (poprzez rejestrowanie się użytkowników i umieszczanie wiadomości na forum). Zanim jednak dojdziemy do tego punktu, musisz się jeszcze sporo nauczyć. Dlatego na razie wypełnimy bazę danymi za pomocą klienta mysqla. Możesz wykonać po kolei poniższe kroki lub skorzystać z gotowych poleceń SQL-a dołączonych do przykładów zamieszczonych na serwerze ftp. Rysunek 6.12. Dodawanie rekordów do tabeli forums Rysunek 6.13. Dodawanie rekordów do tabeli users Zaawansowany SQL i MySQL Aby zapełnić bazę danymi: 1. Dodaj kilka nowych rekordów do tabeli forums (rysunek 6.12). INSERT INTO forums (name) VALUES ( MySQL ), ( PHP ), ( Programowanie ), ( HTML ), ( CSS ), ( Bazy danych ); Ponieważ tablica messages jest zależna od wartości odczytywanych z tabel forums i users, wypełnię najpierw te ostatnie. W przypadku powyższego polecenia INSERT wystarczy jedynie podać wartość kolumny name (MySQL automatycznie nada wartość kolumnie forum_id). 2. Dodaj nowe rekordy do tabeli users (rysunek 6.13). INSERT INTO users (username, pass, first_name, last_name, email) VALUES ( janek , SHA1( haslo ), Jan , Kowalski , jank@example.com ), ( ak , SHA1( haselko ), Arkadiusz , Kaczmarek , ak@example.com ), ( GosiaM , SHA1( tajne ), Małgorzata , Malinowska , mm@example.com ); Jeśli masz wątpliwości co do składni polecenia INSERT lub zastosowania funkcji SHA1(), skorzystaj z informacji podanych w rozdziale 5. P r o j e k t b a z y d a n y c h 189 Rozdział 6. 3. Wstaw nowe rekordy do tabeli messages (patrz rysunek 6.14). SELECT * FROM forums; SELECT user_id, username FROM users; INSERT INTO messages (forum_id, parent_id, user_id, subject, body) VALUES (1, 0, 1, Pytanie na temat normalizacji , (cid:180) Nie rozumiem jednej rzeczy. Dla drugiej (cid:180)postaci normalnej (2NF) podano... ), (1, 0, 2, Projekt bazy danych , Projektuję (cid:180)nową bazę danych i natrafiłem na pewien (cid:180)problem. Ile tabel powinna mieć moja (cid:180)baza?... ), (1, 2, 1, Projekt bazy danych , Liczba (cid:180)tabel w Twojej bazie danych... ), (1, 3, 2, Projekt bazy danych , OK, (cid:180)dziękuję! ), (2, 0, 3, Błędy PHP , Próbuję uruchomić (cid:180)skrypty z rozdziału 3. i pierwszy skrypt (cid:180)kalkulatora nie działa. Gdy akceptuję (cid:180)formularz... ); Ponieważ dwa pola tabeli messages (forum_id oraz user _id) odwołują się do wartości przechowywanych w innych tabelach, przed wstawieniem nowych rekordów dokonam wyboru tych wartości. Na przykład, gdy użytkownik janek utworzy nową wiadomość na forum MySQL-a, musisz użyć forum_id równy 1 i user_id równy 1. Sprawę dodatkowo komplikuje kolumna parent_id. Musi ona zawierać wartość message_id tej wiadomości, na którą odpowiedź stanowi nowa wiadomość. Druga wiadomość umieszczona w bazie danych będzie mieć message_id równy 2 i wobec tego każda wiadomość stanowiąca odpowiedź na tę wiadomość będzie musiała mieć wartość parent_id równą 2. W tworzonych przez Ciebie skryptach PHP, wszystko to będzie wyglądało znacznie prościej. Muszę teraz jednak wyłożyć Ci całą teorię, posługując się terminologią języka SQL. Zwróć również uwagę, że nie wprowadziłem wartości dla pola date_entered. MySQL automatycznie umieści w nim bieżącą datę i czas, ponieważ jest to kolumna typu TIMESTAMP. 4. Powtórz kroki od 1. do 3., aby zapełnić bazę danymi. We wszystkich kolejnych przykładach w tym rozdziale będę wykorzystywał bazę, którą właśnie zapełniłem. Jeśli chcesz, możesz wykonać u siebie te same polecenia INSERT co ja lub utworzyć swoje własne. h c y n a d y z a b t k e j o r P Rysunek 6.14. W przypadku znormalizowanych baz danych bardzo często spotkasz się z sytuacją, w której, aby wstawić jakiś rekord do tabeli, będziesz musiał znać wartości przechowywane w innych tabelach 190 Zaawansowany SQL i MySQL Złączenia Ponieważ relacyjne bazy danych mają złożoną strukturę, aby wydobyć te informacje, które najbardziej nas interesują, musimy czasem wykonać jakieś niestandardowe zapytanie. Na przykład, jeśli chcesz dowiedzieć się, jakie wiadomości zawiera forum MySQL-a, musisz najpierw dowiedzieć się, jaka jest wartość forum_id dla tego forum, a następnie użyć jej do pobrania wszystkich rekordów tabeli message, które mają taką wartość forum_id. Jak z tego wynika, to proste zadanie wymaga wykonania dwóch zapytań. Jednak stosując złączenie, możesz poradzić sobie z nim w jednym kroku. Złączenie jest zapytaniem SQL-a używającym dwóch lub więcej tabel i tworzącym wirtualną tabelę wyników. W specyfikacji SQL-a występują dwa główne typy złączeń: wewnętrzne i zewnętrzne (oba mają szereg podtypów). Złączenie wewnętrzne zwraca wszystkie rekordy podanych tabel, dla których zachodzi dopasowanie. Na przykład, aby uzyskać wszystkie wiadomości zamieszczone na forum MySQL, powinieneś użyć następującego złączenia wewnętrznego (rysunek 6.15): SELECT * FROM messages INNER JOIN forums ON messages.forum_id = forums.forum_id WHERE forums.name = MySQL Złączenie to wybiera wszystkie kolumny obu tabel, jeśli spełnione są dwa warunki. Po pierwsze, kolumna forums.name musi zawierać wartość MySQL (której odpowiada wartość forum_id równa 1). Po drugie, wartość forum_id w tabeli forums musi odpowiadać wartości forum_id w tabeli messages. Ze względu na sprawdzenie równości dotyczące pól w różnych tabelach (messages.forum_id = forums.forum_id) złączenie takie nazywa się równościowym. Złączenia wewnętrzne możesz również zapisywać bez stosowania klauzuli INNER JOIN: SELECT * FROM messages, forums WHERE messages.forum_id = forums.forum_id AND forums.name = mySQL Jeżeli wybierasz dane z wielu tabel i kolumn, a w kilku tabelach występują kolumny o takiej samej nazwie, musisz zastosować notację z kropką (tabela.kolumna). W przypadku relacyjnych baz danych robi się to praktycznie zawsze, ponieważ klucz główny jednej tabeli ma taką samą nazwę jak obcy klucz drugiej. Jeżeli nie wskażesz jednoznacznie kolumny, do której się odwołujesz, zobaczysz komunikat o błędzie (rysunek 6.16). Z ł ą c z e n i a Rysunek 6.15. To złączenie zwraca kolumny obu tabel dla rekordów, dla których wartości forum_id są równe MySQL (1) Rysunek 6.16. Ogólne odwołanie do kolumny występującej w kilku tabelach spowoduje wystąpienie błędu niejednoznaczności 191 Rozdział 6. Złączenia zewnętrzne różnią się od złączeń wewnętrznych tym, że potrafią zwracać rekordy, które nie spełniają wyrażenia warunkowego. Istnieją trzy podtypy złączeń zewnętrznych: left (lewe), right (prawe) i full (pełne). Przykładem pierwszego podtypu jest następujące złączenie: SELECT * FROM forums LEFT JOIN messages ON forums.forum_id = messages.forum_id; Najważniejsze przy tego rodzaju złączeniach jest to, które tabele należy wymienić jako pierwsze. W powyższym przykładzie w przypadku udanego dopasowania zwrócone zostaną wszystkie rekordy tabeli forums wraz ze wszystkimi informacjami z tabeli messages. Jeśli dla danego rekordu tabeli forums nie można dopasować informacji w tabeli messages, to zamiast nich zostaną zwrócone wartości NULL (rysunek 6.17). Jeżeli w warunku porównania złączenia zewnętrznego bądź wewnętrznego występuje kolumna o takiej samej nazwie w obu tabelach, możesz uprościć zapis zapytania, stosując klauzulę USING: SELECT * FROM messages INNER JOIN forums USING (forum_id) WHERE forums.name = mySQL SELECT * FROM forums LEFT JOIN messages USING (forum_id) Zanim przejdę do przykładów, jeszcze dwie uwagi. Po pierwsze, ponieważ składnia wymagana przy tworzeniu złączeń jest dość skomplikowana, przy ich pisaniu przydają się aliasy omówione w rozdziale 5. Po drugie, ponieważ złączenia często zwracają dużo danych, to najlepiej zawsze określić, które kolumny nas interesują, zamiast wybierać wszystkie. a i n e z c ą ł Z Rysunek 6.17. To złączenie zewnętrzne zwraca więcej rekordów niż złączenie wewnętrzne, ponieważ zwraca wszystkie wiersze pierwszej tabeli. Zapytanie to zwraca również nazwy wszystkich forów, nawet jeśli nie zawierają one jeszcze żadnej wiadomości 192 Aby wykorzystać złączenia: 1. Pobierz nazwę forum i temat wiadomości dla każdego rekordu tabeli messages (patrz rysunek 6.18). SELECT f.name, m.subject FROM forums AS f INNER JOIN messages AS m USING (forum_id) ORDER BY f.name; Zapytanie to zawiera złączenie wewnętrzne, które spowoduje w efekcie zastąpienie wartości forum_id z tabeli messages odpowiadającą jej wartością name z tabeli forums dla każdego rekordu tabeli messages. W wyniku otrzymasz zatem temat każdej wiadomości wraz z nazwą forum, do którego należy ta wiadomość. Zwróć uwagę, że w połączeniach nadal możesz używać klauzul ORDER BY. Zaawansowany SQL i MySQL 2. Pobierz temat i datę wprowadzenia każdej wiadomości wysłanej przez użytkownika janek (rysunek 6.19). SELECT m.subject, DATE_FORMAT(m.date_entered, M D, Y ) AS Date FROM users AS u INNER JOIN messages AS m USING (user_id) WHERE u.username = janek ; Również to złączenie używa dwóch tabel, users i messages. Złączenie obu tabel odbywa się za pomocą kolumny user_id i dlatego została ona umieszczona wewnątrz klauzuli USING. Wyrażenie warunkowe WHERE pozwala zidentyfikować interesującego nas użytkownika, a funkcja DATE_FORMAT sformatować wartość pola date_entered. Rysunek 6.18. Podstawowe złączenie wewnętrzne zwraca w tym przykładzie tylko dwie kolumny Rysunek 6.19. Nieco bardziej skomplikowana wersja złączenia wewnętrznego tabele users i messages Z ł ą c z e n i a 193 Rozdział 6. 3. Pobierz identyfikator wiadomości, temat oraz nazwę forum dla każdej wiadomości, której autorem jest użytkownik janek (patrz rysunek 6.20). SELECT m.message_id, m.subject, f.name FROM users AS u INNER JOIN messages AS m USING (user_id) INNER JOIN forums AS f USING (forum_id) WHERE u.username = janek ; Powyższe złączenie jest podobne do zastosowanego w punkcie 2., ale idzie o krok dalej, dołączając trzecią tabelę. Zwróć szczególną uwagę na sposób konstrukcji złączenia wewnętrznego trzech tabel oraz zastosowanie aliasów w celu łatwiejszego odwoływania się do tabel i ich kolumn. 4. Dla każdego użytkownika pobierz nazwę użytkownika, temat wiadomości i nazwę forum (patrz rysunek 6.21). SELECT u.username, m.subject, f.name FROM users AS u LEFT JOIN messages AS m USING (user_id) LEFT JOIN forums AS f USING (forum_id); Gdybyś zastosował podobne złączenie wewnętrzne, to użytkownicy, którzy nie są jeszcze autorami żadnej wiadomości, nie zostaliby uwzględnieni (patrz rysunek 6.22). Zatem jeśli interesują Cię wszyscy użytkownicy, powinieneś użyć złączenia zewnętrznego. Zwróć uwagę, że tabela, która ma być uwzględniona w całości (users w tym przypadku), musi być podana jako pierwsza tabela złączenia typu left. a i n e z c ą ł Z Rysunek 6.20. Złączenie wewnętrzne wszystkich trzech tabel bazy Rysunek 6.21. To złączenie zewnętrzne zwraca dla każdego użytkownika temat każdej wiadomości i nazwę każdego forum. Jeśli użytkownik nie umieścił jeszcze żadnej wiadomości na forum (tak jak tomekj widoczny na dole rysunku), to temat wiadomości i nazwa forum będą mieć dla niego wartości NULL 194 Zaawansowany SQL i MySQL (cid:132) Złączenia, które nie zawierają klauzuli WHERE (np. SELECT * FROM urls, url_associations), nazywamy złączeniami pełnymi. Zwracają one rekordy z obu tabel. W przypadku dużych tabel ilość zwracanych danych może stanowić pewien problem. (cid:132) Nigdy nie zostanie zwrócona wartość NULL występująca w kolumnie, do której odwołujemy się w złączeniu. To dlatego, że nie jest ona równa żadnej innej wartości, w tym także innym wartościom NULL. Wskazówki (cid:132) Możesz nawet złączyć tabelę z nią samą. (cid:132) Złączenia można tworzyć z wykorzystaniem wyrażeń warunkowych odwołujących się do dowolnych kolumn. Nie muszą one pełnić roli kluczy, choć sytuacja taka jest najczęstsza. (cid:132) Stosując składnię bazadanych.tabela. kolumna, możesz wykonywać złączenia tabeli należących do różnych baz danych, o ile tylko wszystkie znajdują się na tym samym serwerze. Ta metoda nie zadziała, jeżeli bazy komunikują się ze sobą za pośrednictwem sieci. Rysunek 6.22. To złączenie wewnętrzne nie zwróci użytkownika, który nie umieścił jeszcze żadnej wiadomości na forum (tak jak tomekj widoczny na dole rysunku 6.21) Z ł ą c z e n i a 195 Rozdział 6. Grupowanie wyników zapytania W poprzednim rozdziale przedstawiłem dwie różne klauzule — ORDER BY i LIMIT — mające wpływ na wynik zapytania. Pierwsza z nich umożliwia określenie uporządkowania zwracanych rekordów, a druga pozwala określić, które z rekordów będących wynikiem zapytania zostaną w rzeczywistości zwrócone. Kolejna klauzula, GROUP BY, grupuje zwracane dane w podobne do siebie bloki informacji. Na przykład, aby pogrupować wszystkie wiadomości według forum, wpisz: SELECT * FROM messages GROUP BY forum_id; Zwracane dane nie będą miały postaci pojedynczych rekordów, tylko grup informacji. Zamiast dużej liczby wiadomości pochodzących z danego forum zobaczysz je wszystkie w postaci jednego rekordu. Ten przykład nie jest być może szczególnie przydatny, ale wystarczający do przedstawienia samej koncepcji. Z klauzulą GROUP BY często używa się jednej z funkcji agregujących przedstawionych w tabeli 6.7 (funkcji tych można używać również niezależnie od klauzuli GROUP BY). Na wyrażenie GROUP BY możesz oczywiście nakładać warunki WHERE, ORDER BY i LIMIT, tworząc zapytania w rodzaju: SELECT kolumny FROM tabela WHERE warunek GROUP BY kolumna ORDER BY kolumna LIMIT x, y; a i n a t y p a z w ó k i n y w e i n a w o p u r G Aby zgrupować dane: 1. Policz zarejestrowanych użytkowników (patrz rysunek 6.23). SELECT COUNT(user_id) FROM users; COUNT() jest prawdopodobnie najpopularniejszą z funk
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

PHP6 i MySQL 5. Dynamiczne strony WWW. Szybki start
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: