Podstawy języka SQL

Share

Tworzenie tabeli

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;

• Nazwa pola
• Typ pola
• Czy pole może być puste?
• Wartość domyślna
• Parametry dodatkowe
• Klucze i indeksy

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

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

Specyfika języka SQL

• 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

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

DESCRIBE tabela;

DROP TABLE tabela;

SELECT * FROM `tabela`

Dodawanie rekordów

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');

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);

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

UPDATE `tabela` SET `pole1` = 'Nowa wartość', `pole2` = 'Nowa wartość'

UPDATE `produkty` SET `opis` = 'Długopisy niebieskie firmy YYY', `cena` = '2.45' WHERE `id` = '1';

UPDATE `produkty` SET `cena` = (`cena` + 0.5) WHERE `jakosc` = 4;

Usuwanie rekordów

DELETE FROM `produkty` WHERE `id` = 3;

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

UPDATE `produkty` SET `opis` = 'Długopisy niebieskie firmy YYY' `cena` = '2.45' WHERE `id` = '1';

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);

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

Share

Podstawy PHP, 14 July 2008