PHP/Projekt bazy danych

Projekt bazy danych

Odstawimy teraz na chwilę PHP i nauczymy się pracować z serwerem baz danych MySQL. Poznamy również podstawy języka SQL do komunikacji z nim.

Wiersz poleceń serwera DB

Aby wykonywać operacje na bazie danych, nie jest konieczne samodzielne tworzenie odpowiednich aplikacji. MySQL posiada specjalny wiersz poleceń, w którym możemy wprowadzać zapytania i oglądać ich wyniki. Serwer DB powinien być już uruchomiony przy starcie systemu operacyjnego, dlatego przystąpimy teraz do rzeczy.

• Jeżeli pracujesz w systemie uniksowym, odszukaj katalog z danymi binarnymi, w którym mogła po instalacji zostać zlokalizowana aplikacja mysql. Uruchom ją poleceniem:

mysql -u root -p

Lub, jeżeli łączysz się poprzez sockety:

mysql -u root -p -S /tmp/mysql.sock

• Użytkownik systemu Windows musi otworzyć systemowy wiersz poleceń i komendą cd przełączyć się na katalog z instalacją MySQLa, a następnie na znajdujący się w nim folder bin. Tutaj należy uruchomić program mysql.exe z parametrami -u root -p. Przykładowe postępowanie wygląda tak:

C:\> D:
D:\> cd Serwer/mysql/bin
D:\Serwer\mysql\bin> mysql.exe -u root -p

Podstawą bezpieczeństwa w bazie danych MySQL są użytkownicy, którzy mają jasno określone przywileje dostępu do baz. Najważniejszym z nich jest root, służący na serwerach produkcyjnych jedynie do administracji. Jednak w domowych warunkach można go wykorzystać także do projektowania własnych baz oraz testowania skryptów. W naszym przypadku podaliśmy podczas instalacji, że hasło tego użytkownika to także root. Podaj je, gdy zostaniesz o to zapytany przez wiersz poleceń.

Jeżeli po wpisaniu hasła ujrzysz znak zachęty mysql>, wszystko poszło OK i jesteśmy gotowi do pracy. W wierszu poleceń obowiązują następujące reguły:

1. Zapytania kończymy średnikiem lub sekwencją \g. Jeżeli zabraknie tego elementu, ENTER zwyczajnie będzie Cię przenosił do nowej linijki!
2. Pomoc włączamy sekwencją \h
3. Opuszczamy wiersz poleceń sekwencją \q

Tworzenie bazy danych

Standardowo po instalacji MySQL posiada stworzone dwie domyślne bazy: test oraz mysql. Pierwsza jest pusta, a druga zawiera ustawienia serwera i lepiej nic tam nie grzebać bez dokładnej znajomości jej budowy. My jednak nie skorzystamy z żadnej z nich.

CREATE DATABASE produkty;

To zapytanie utworzy nam nową bazę danych o nazwie produkty. Musimy się teraz na nią przełączyć:

USE produkty;

Po każdym wykonanym zapytaniu wiersz poleceń wyświetla nam informacje o jego rezultacie. Napis Query OK oznacza, że zostało ono zaakceptowane i poprawnie wykonane. Dalej mogą wystąpić informacje diagnostyczne (ilość dokonanych zmian, czas wykonywania lub lista wyników).

Tworzenie tabeli

Jak powiedzieliśmy, właściwe dane przechowywane są w tabelach. Ich tworzenie polega na definiowaniu szczegółowej struktury rekordów. Utworzymy teraz tabelę produkty przechowującą informacje o różnych produktach:

CREATE TABLE `produkty` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`nazwa` VARCHAR(60) NOT NULL,
`opis` TEXT NOT NULL,
`ilosc` SMALLINT DEFAULT '0',
`cena` FLOAT NOT NULL,
`jakosc` TINYINT NOT NULL 
) ENGINE = MYISAM;

W pierwszej linijce nakazujemy utworzenie tabeli o podanej nazwie. Nawias rozpoczyna definicję jej struktury. Opisy budowy poszczególnych pól oddzielone są przecinkami, a informacje do każdego z nich podawane są w następującej kolejności:

1. Nazwa pola
2. Typ pola
3. Czy pole może być puste?
4. Wartość domyślna
5. Parametry dodatkowe
6. Klucze i indeksy

Przyjrzyjmy się zatem poszczególnym polom:

1. id - pole to będzie przechowywało liczbowy, jednoznaczny identyfikator rekordu. Dwa rekordy nie mogą posiadać tego samego ID. Pole to powinny posiadać w zasadzie wszystkie tabele, gdyż jest ono podstawą relacji oraz systemów zarządzania bazą. Parametry to:
   • INT - typ liczbowy
   • NOT NULL - pole nie może być puste
   • AUTO_INCREMENT - MySQL automatycznie będzie dbał o nadawanie nowo dodawanym rekordom kolejnych ID.
   • PRIMARY KEY - klucz główny określający przeznaczenie tego pola jako podstawy do identyfikacji rekordów.
2. nazwa - tutaj będziemy umieszczali nazwę produktu. Parametry to:
   • VARCHAR(60) - typ tekstowy. Maksymalna długość to 60 znaków.
   • NOT NULL - pole nie może być puste
3. opis - opis produktu z dodatkiem wazeliny. Parametry to:
   • TEXT - typ tekstowy (maksymalna długość: 64 kB)
   • NOT NULL - pole nie może być puste
4. ilosc - określa ilość sztuk produktu w magazynie. Parametry to:
   • SMALLINT - typ liczbowy
   • DEFAULT '0' - pole może pozostać puste, a domyślnie nowym rekordom nadajemy tutaj wartość 0.
5. cena - cena produktu. Parametry to:
   • FLOAT - typ liczbowy z obsługą ułamków (aby można było także grosze uwzględniać)
   • NOT NULL - pole nie może być puste
6. jakosc - liczbowe oznaczenie jakości produktu. Parametry to:
   • TINYINT - typ liczbowy
   • NOT NULL - pole nie może być puste

Ostatnia linijka zawiera zamknięcie definicji struktury oraz określenie typu tabeli. Nie musisz się nim teraz przejmować. Wystarczy na razie wiedzieć, że domyślnym jest obecnie InnoDB, ale do większości zastosowań używa się wydajniejszego MyISAM.

Zauważyłeś już pewnie, że w zapytaniu wykorzystaliśmy kilka różnych typów liczbowych. Oto dokładniejsza specyfikacja ważniejszych typów:

1. TINYINT - liczba jednobajtowa. Wartości od -128 do 127.
2. TINYINT UNSIGNED - liczba jednobajtowa bez znaku. Wartości od 0 do 255. Słowo UNSIGNED po kolejnych nazwach typów liczbowych robi to samo, co w tym przypadku.
3. SMALLINT - liczba dwubajtowa. Wartości od -32768 do 32767, a bez znaku od 0 do 65535.
4. MEDIUMINT - liczba trzybajtowa. Wartości od -8388608 do 8388607, a bez znaku od 0 do 16777215.
5. INT - liczba czterobajtowa. Wartości od -2147483648 do 2147483647, a bez znaku od 0 do 2147483647.
6. BIGINT - liczba ośmiobajtowa. Wartości od -9223372036854775808 do 9223372036854775807, a bez znaku od 0 do 18446744073709551615.
7. FLOAT - liczba zmiennoprzecinkowa czterobajtowa (tak, jak w PHP).
8. VARCHAR(M) - tekst o długości N od 0 do M znaków, gdzie M < 256. W pamięci zajmuje N + 1 bajtów (dodatkowy zawiera długość tekstu).
9. CHAR(M) - tekst o długości od 0 do M znaków, gdzie M < 256. W przeciwieństwie do poprzedniego typu, zajmuje w pamięci zawsze M bajtów nawet, jeżeli znajdujący się w nim tekst jest krótszy.
10. TEXT - typ tekstowy doskonały do przechowywania dłuższych treści. Można w nim zmieścić aż 64 kB danych (65535 znaków).
11. BLOB - typ do przechowywania danych binarnych, np. plików. Maksymalna wielkość to także 64 kB.
12. BOOL - typ logiczny, równoważnik zapisu TINYINT(1).
13. DATE - wyspecjalizowany typ do przechowywania daty. Bardzo rzadko wykorzystywany w poważniejszych aplikacjach PHP ze względu na jego niewygodne przetwarzanie i formatowanie.

W przypadku typów liczbowych bardzo często spotyka się zapisy np. INT(8) lub MEDIUMINT(6). Liczby w nawiasach nie mają nic wspólnego z wielkością danych, jakie można w nich trzymać. Mówią one bazie danych MySQL, że jeśli dana liczba jest krótsza niż np. 8 znaków, to należy ją dopełnić spacjami do tej długości, kiedy będziemy na niej operować, jak na tekście.

Specyfika języka SQL

Napisaliśmy już kilka zapytań i widać z nich, że SQL w wielu miejscach przypomina zwyczajny mówiony angielski. Pora poznać kilka jego cech:

1. Wielkość liter nie odgrywa żadnej roli, lecz programiści używają ich ze względów estetycznych. Zapis create table `produkty` także jest poprawny.
2. Odwrócone apostrofy są używane do określania nazw tabel, baz i pól, ale nie są one niezbędne. Zapisy create table produkty oraz id int not null są poprawne, lecz trzeba tu zwrócić uwagę na jedną rzecz, którą zademonstrujemy na przykładzie. Otóż chcielibyśmy mieć w tabeli pole "order". Piszemy więc order int not null, ale nagle okazuje się, że MySQL zgłasza w tej linijce błąd. Co jest nie tak? "order" jest jednym ze słów kluczowych języka SQL i aby tak nazwać tabelę, musimy koniecznie umieścić ją w odwróconych apostrofach. Wielu programistów, aby zapytania nie przypominały grochu z kapustą, z definicji używa ich zatem wszędzie, unikając w ten sposób wszystkich niespodzianek, a także niekompatybilności z nowymi wersjami, które dodają coraz więcej słów kluczowych.

Ćwiczenia

W ramach treningu utwórz następujące tabele:

1. Tabela klientów sklepu z polami id, imie, nazwisko, wiek, miejscowosc, ulica, numer_domu, numer_mieszkania, telefon.
2. Tabela dostawców sklepu z polami id, nazwa, dzien_tyg_dostawy, naleznosc
3. Tabela kategorii produktów z polami id, nazwa, opis, ilosc_produktow.

Pamiętaj, że możesz sprawdzić strukturę już utworzonej tabeli poleceniem:

DESCRIBE tabela;

Jeżeli coś Ci nie wyszło, wykonaj

DROP TABLE tabela;

aby skasować tabelę.