Poniższy artykuł wykorzystuje bazę MySQL. W przypadku SQLite i PostgreSQL składania języka SQL może nieco się różnić.

Tworzenie tabeli

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 
) TYPE = 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:
  • Nazwa pola
  • Typ pola
  • Czy pole może być puste?
  • Wartość domyślna
  • Parametry dodatkowe
  • Klucze i indeksy
Przyjrzyjmy się zatem poszczególnym polom:
  • 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.
  • 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
  • opis - opis produktu z dodatkiem wazeliny. Parametry to:
    • TEXT - typ tekstowy (maksymalna długość: 64 kB)
    • NOT NULL - pole nie może być puste
  • 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.
  • 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
  • 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 istnieją dwa typy: InnoDB, i najczęściej stosowany 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:
  • TINYINT - liczba jednobajtowa. Wartości od -128 do 127.
  • UNSIGNED TINYINT - liczba jednobajtowa bez znaku. Wartości od 0 do 255. Słowo ''UNSIGNED'' przed nazwami kolejnych typów liczbowych robi to samo, co w tym przypadku.
  • SMALLINT - liczba dwubajtowa. Wartości od -32768 do 32767, a bez znaku od 0 do 65535.
  • MEDIUMINT - liczba trzybajtowa. Wartości od -8388608 do 8388607, a bez znaku od 0 do 16777215.
  • INT - liczba czterobajtowa. Wartości od -2147483648 do 2147483647, a bez znaku od 0 do 4294967294.
  • BIGINT - liczba ośmiobajtowa. Wartości od -9223372036854775808 do 9223372036854775807, a bez znaku od 0 do 18446744073709551615.
  • FLOAT - liczba zmiennoprzecinkowa czterobajtowa (tak, jak w PHP).
  • 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).
  • 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.
  • 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).
  • BLOB - typ do przechowywania danych binarnych, np. plików. Maksymalna wielkość to także 64 kB.
  • BOOL - typ logiczny, równoważnik zapisu ''TINYINT(1)''.
  • 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:
  • 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.
  • 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:
  • Tabela klientów sklepu z polami id, imie, nazwisko, wiek, miasto, ulica, numer_domu, numer_mieszkania, telefon.
  • Tabela dostawców sklepu z polami id, nazwa, dzien_tyg_dostawy, naleznosc
  • 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ę.

Wiemy już, jak utworzyć bazę danych oraz strukturę tabel. Niewątpliwie bez tych operacji nie można zacząć, lecz później podstawowymi operacjami stają się te, dzięki którym możemy zarządzać danymi. Właśnie w tym odcinku dowiemy się, jak dodawać, modyfikować oraz usuwać niepotrzebne rekordy. Na początek proste polecenie, które pozwoli nam na wyświetlenie obecnej zawartości tabeli:
SELECT * FROM `tabela`
Gdzie zamiast tabela wstawiamy nazwę naszej tabeli. Szczegółowy opis tego zapytania poznamy w następnych dwóch odcinkach. Teraz posłuży nam on jedynie do sprawdzania, czy wszystko przebiega poprawnie.

Dodawanie rekordów

Aby dodać rekord do tabeli, należy wysłać zapytanie INSERT. Ma ono generalnie dwie możliwe składnie:
INSERT INTO `tabela` VALUES('Wartość pola 1', 'Wartość pola 2', 'Wartość pola 3');
INSERT INTO `tabela` (`pole1`, `pole2`, `pole3`) VALUES('Wartość pola 1', 'Wartość pola 2', 'Wartość pola 3');
Oba powodują utworzenie nowego rekordu w podanej tabeli, lecz istnieje między nimi pewna różnica. W pierwszym zapytaniu musimy bezwzględnie podać wartości wszystkich pól nowego rekordu, jakie mamy zdefiniowane w strukturze tabeli, w identycznej kolejności. Drugie zapytanie pozwala nam w pierwszym z nawiasów wymienić listę pól, jakie nas interesują i dopiero potem podać ich wartości. W praktyce znacznie częściej używa się właśnie jego, gdyż nie trzeba podawać wartości pól ID, które nadawane są przez bazę automatycznie. Wróćmy zatem do naszej tabeli ''produkty''. Wstawmy do niej kilka rekordów:
INSERT INTO `produkty`
  (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES(
   'Długopisy niebieskie',
   'Długopisy z niebieskim wkładem firmy XXX',
   100,
   2.15,
   3);
INSERT INTO `produkty`
  (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES(
   'Długopisy czerwone',
   'Długopisy z czerwonym wkładem firmy XXX',
   50,
   2.15,
   3);
INSERT INTO `produkty`
  (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES(
   'Zszywacze',
   'Metalowy zszywacz + 100 zszywek.',
   30,
   9.50,
   4);
INSERT INTO `produkty`
  (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES(
   'Karteczki samoprzylepne',
   'Samoprzylepne kartki koloru żółtego 10x10 cm w kompletach po 100 sztuk',
   200,
   3.60,
  2);
Istnieje także możliwość wstawienia kilku rekordów naraz za pomocą jednego zapytania INSERT:
INSERT INTO `produkty` (`nazwa`, `opis`, `ilosc`, `cena`, `jakosc`) VALUES
('Strugaczki', 'Czerwone, do dwóch rozmiarów ołówków', 60, 0.90, 4),
('Gumki do ścierania', 'Gumki do ścierania ołówków firmy ZZZ', 97, 0.50, 3),
('Spinacze do papieru', 'Metalowe spinacze do papieru w kompletach po 50 sztuk.', 68, 0.50, 4);

Tutaj po VALUES podajemy kilka bloków wartości dla kolejnych rekordów, każdy z nich w nawiasach i oddzielony przecinkiem.

Pamiętaj, że wartości tekstowe musimy zawsze podawać w apostrofach. Liczby można podawać zarówno z nimi, jak i bez, lecz podczas programowania apostrofami obejmuje się najczęściej wszystkie wstawiane ze skryptu wartości

.

Modyfikowanie rekordów

Czasem zachodzi konieczność zmodyfikowania niektórych informacji. Tu pomocne będzie zapytanie ''UPDATE''. Ma ono bardzo prostą składnię:
UPDATE `tabela` SET `pole1` = 'Nowa wartość', `pole2` = 'Nowa wartość'
Jednak uważaj! Gdybyś wykonał powyższe zapytanie, podstawiając odpowiednie dane, okazałoby się, że zmiany zostały wprowadzone we wszystkich rekordach! Bardzo rzadko jest to pożądana rzecz, ponieważ o wiele częściej chcemy zmodyfikować pewną, konkretną grupę. Do jej uwzględnienia użyjemy nowej klauzuli: WHERE wyrażenie umieszczanej po liście pól do podmiany. Wyrażenie jest dowolnym poprawnym wyrażeniem języka SQL, a układa się je podobnie, jak te w PHP. Szczegółowe informacje o budowaniu wyrażeń poznamy w następnym odcinku, teraz ograniczymy się do kilku prostych sztuczek. Na początek zmienimy opis i cenę produktu o ID 1:
UPDATE `produkty` SET `opis` = 'Długopisy niebieskie firmy YYY', `cena` = '2.45' WHERE `id` = '1';
Teraz coś trudniejszego: nasz sklep planuje podwyżkę cen najlepszych produktów (jakość 4) o 50 groszy. Wiele osób próbuje robić to, pobierając ceny wszystkich interesujących je produktów i zmieniając je setkami zapytań UPDATE, po jednym dla jednego rekordu. Jest to bardzo niepraktyczne, ponieważ język SQL jest na tyle zaawansowanym narzędziem, że radzi sobie z tym bez trudu:
UPDATE `produkty` SET `cena` = (`cena` + 0.5) WHERE `jakosc` = 4;
Powinniśmy teraz ujrzeć informację, że zmodyfikowane zostały trzy rekordy.

Usuwanie rekordów

Niepotrzebne rekordy kasujemy zapytaniem DELETE. Podobnie, jak w poprzednim przypadku, należy zastosować klauzulę WHERE, aby określić, które z nich chcemy usunąć. Inaczej możemy pożegnać się z całą zawartością tabeli.
DELETE FROM `produkty` WHERE `id` = 3;
Zdarza się, że tabelę trzeba rzeczywiście wyczyścić, np. z danych testowych, aby aplikacja mogła być używana na normalnym serwerze. Jednak wtedy nie powinno się wykorzystywać polecenia DELETE FROM `produkty`. Do tego celu służy specjalne zapytanie:
TRUNCATE `produkty`

Różnica pomiędzy pierwszym i drugim jest podobna, jak pomiędzy zaznaczeniem wszystkich plików na dysku i kliknięciu "Delete", a uruchomieniem jego formatowania.

Wykonując wiele zapytań DELETE oraz INSERT zauważysz, że pozostają Ci luki w numeracji. Spróbuj dodać teraz jakiś rekord. Jeżeli wykonałeś wcześniej zapytanie kasujące ten o ID 3, MySQL pozostawi tam lukę, nadając nowemu rekordowi następny w kolejności ID - 8. Prawdopodobnie z przyczyn estetycznych niektórym nowym programistom to zachowanie przeszkadza, lecz jest to zupełnie błędne podejście do problemu. Jak wspomnieliśmy bowiem, MySQL jest relacyjną bazą danych, w której rekordy z jednej tabeli mogą być połączone odpowiednimi relacjami z rekordami w drugiej. Wyobraźmy sobie więc, co by się stało, gdybyśmy skasowali jakąś kategorię np. newsów w naszym serwisie, a potem dodali nową i okazało się, że MySQL zaliczył w jej poczet wszystkie newsy z tej usuniętej, gdyż nowy rekord zajął pustą lukę w numeracji. Działanie takie stwarza poważne zagrożenie synchronizacji bazy i wprowadza w nią element losowości. Dlatego zaufaj twórcom serwerów DB, oni naprawdę znają się na rzeczy i nie podejmuj zbędnych prób zmieniania na gorsze tego, co wyraźnie służy zarówno tobie, jak i twojej bazie.


Zamiana rekordów

Bardzo przydatną operacją jest automatyczne podmienianie rekordów. Polega ono na tym, że jeżeli wstawiamy rekord A do tabeli, w której znajduje się już rekord B o podobnym kluczu, jest on automatycznie nadpisywany przez system. MySQL posiada dwa zapytania, które są pomocne przy podmienianiu rekordów, jednak zanim się do nich dobierzemy, musimy utworzyć sobie nową tabelę.

Aby MySQL mógł zdecydować, czy rekord należy nadpisać, musi wiedzieć, które pola tabeli przechowują wartości unikalne, czyli takie, że nie można znaleźć dwóch rekordów o identycznej wartości w tym polu. Wiemy już, że taką właściwość powinno mieć pole id, i rzeczywiście - informujemy o tym bazę, tworząc dla niego tzw. klucz główny (PRIMARY KEY). Dba on o to, aby wartości się nie powtarzały. Jednak klucz ten można nałożyć tylko na jedno pole naraz, a operacja REPLACE przy rekordach mających z definicji nie tylko unikalne, ale też nadawane automatycznie ID, zbyt dużego sensu nie ma. Dlatego istnieje indeks UNIQUE, który może być nałożony na dowolnie dużo pól i także sprawi, że zyskają one tę właściwość. Aby pokazać to w praktyce, utwórzmy tabelę dla elektronicznego słownika (w wersji uproszczonej, oczywiście):

CREATE TABLE `slownik` (
`id` MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`haslo` VARCHAR(40) NOT NULL,
`znaczenie` VARCHAR(255) NOT NULL,
  UNIQUE(
   `haslo` 
  )
) ENGINE = MYISAM;

Mamy tu trzy pola: id, haslo oraz znaczenie. ID służy jedynie celom administracyjnym (wyszukiwanie względem liczby jest zawsze szybsze, niż względem tekstu). Dla użytkownika naszego słownika najważniejsze będzie jednak pole haslo, które także powinno mieć wartości unikalne. Indeks UNIQUE tworzymy tak, jak na przykładzie: po liście pól wstawiamy słowo kluczowe UNIQUE, w którym w nawiasie wymieniamy pola posiadające taką właściwość.

Jeżeli jesteśmy administratorami słownika, mogącymi dodawać do niego nowe hasła, unikalność obsługiwana przez MySQL ma dla nas duże znaczenie. Kiedy będzie w nim bowiem już dużo słów, ze zwykłej pomyłki możemy spróbować dodać po raz drugi to samo. Tradycyjne polecenie INSERT zareagowałoby błędem powiadamiającym, że próbujemy utworzyć rekord, w którym powtarza się unikalna wartość.


Błędy w zapytaniach

Także w języku SQL można popełnić błędy, które uniemożliwią wykonanie zapytania. Każdy serwer DB raportuje je inaczej. Ponieważ sztandarową bazą w tym podręczniku jest MySQL, pokażemy sposoby debugowania zapytań właśnie dla niego. Zacznijmy od błędów składni. Wykonaj następujące zapytanie:
UPDATE `produkty` SET `opis` = 'Długopisy niebieskie firmy YYY' `cena` = '2.45' WHERE `id` = '1';
Zawiera ono celowo wprowadzony błąd, który powoduje pokazanie się komunikatu
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'cena` = '2.45' WHERE `id` = '1'' at line 1

Komunikat pokazał nam użyteczną informację o miejscu lokalizacji problemu. Według niego coś jest nie tak w okolicy ciągu cena` = '2.45' WHERE `id` = '1'. Niepisana zasada mówi, że najczęściej błąd popełniliśmy bezpośrednio przed nim. Zobaczmy więc, co znajduje się w zapytaniu przed odwołaniem do pola "cena". Okazuje się, że brakuje przecinka oddzielającego je od poprzedniej definicji nowej wartości. Kiedy go tam umieścimy, wszystko zaczyna prawidłowo działać.

Inny rodzaj problemu może powstać przy pracy z polami "PRIMARY KEY" oraz "UNIQUE" (o nich dalej). Oba te atrybuty nadają polu właściwość unikalności, czyli nie mogą istnieć dwa rekordy o takich samych wartościach w tym miejscu. Przykładowo wywołajmy takie zapytanie INSERT:

INSERT INTO `produkty`
  VALUES(
   '3',
   'Długopisy niebieskie',
   'Długopisy z niebieskim wkładem firmy XXX',
   100,
   2.15,
   3);
Jest w nim wymieniony ID produktu. Jeżeli wykonywałeś wszystkie zapytania w tym odcinku, prawdopodobnie nic się teraz nie stanie, ponieważ rekord o ID 3 niedawno skasowaliśmy. W takim wypadku wykonaj je jeszcze raz, a baza pokaże ci wtedy:
Duplicate entry '3' for key 1
Oznacza on tyle, że w kluczu pierwszym (w naszym przypadku jest to pole "id") próbujemy po raz drugi umieścić wartość 3, co jest niedozwolone.

Na podstawie kursu PHP na Wikibooks, licencja GNU Free Documentation License

blog comments powered by Disqus

Kategorie

Serwisy