W poprzednim odcinku mojego kursu opisałem sposób tworzenia prostej
bazy danych, składającej się z jednej tabeli. Dzisiaj chciałbym dokładniej opisać
podstawowe operacje wykonywane podczas modelowania relacyjnych baz danych. Będę
nadal opierać się na bazie MySQL. W przypadku innych produktów składnia języka
SQL jest bardzo podobna (jednakże nie jest identyczna) ale radzę zajrzeć do
dokumentacji.
Do utworzenia tabeli
użyliśmy polecenia CREATE TABLE dokładna składnia (w bazie MySQL) tego
polecenia wygląda tak:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nazwa_tabeli [(definicja_kolumny,...)]
[opcje_tabeli] [wyrażenie_SELECT]
TEMPORARY - w ten sposób tworzymy tabele tymczasowe, które będą później automatycznie
tworzone na czas połączenia użytkowników z naszą bazą. W momencie przerwania
połączenia użytkownik-baza tabela taka zostaje skasowana.
IF NOT EXISTS -
ta klauzula spowoduje utworzenie nowej tabeli ale tylko wtedy gdy tabela (o
podanej w polu nazwa_tabeli) nazwie nie będzie już wcześniej istniała w bazie
wyrażenie_SELECT
- pozwala na automatyczne załadowanie danych do nowo utworzonej tabeli
definicja_kolumny wygląda tak:
Nazwa_kolumny TYP_DANYCH
[NOT NULL | NULL] [DEFAULT wartość_domyślna] [AUTO_INCREMENT] [PRIMARY KEY]
[definicja_powiązania]
TYPY DANYCH
Podczas tworzenia tabel bardzo ważną sprawą jest dobranie odpowiednich opisów
dla danych, które będą w nich później przechowywane i temu zagadnieniu chciałbym
tym razem poświęcić więcej czasu.
DANE NUMERYCZNE
W kolumnach tego
typu będziemy umieszczali wartości liczbowe. W zależności od potrzeb mogą to
być wartości dokładne (TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT,DECIMAL,
NUMERIC) lub przybliżone (FLOAT, DOUBLE, DOUBLE PRECISION, REAL). Musimy się
zastanowić jakie wartości będą przyjmować dane w określonej kolumnie i wybrać najbardziej
odpowiedni typ dla niej.
Liczby całkowite
(INTEGER):
- INT, INTEGER
- w takich kolumnach przechowujemy liczby całkowite z zakresu -2147483648
do 2147483647 (4 bajty)
- TINYINT - liczba
całkowita z zakresu -128 do 127 (1 bajt)
- SMALLINT - liczba
całkowita z zakresu -32768 do 32767 (2 bajty)
- MEDIUMINT -
liczba całkowita z zakresu -8388608 do 8388607 (3 bajty)
- BIGINT - liczba
całkowita z zakresu -9223372036854775808 do 9223372036854775807 (8 bajtów)
Liczby rzeczywiste
(REAL):
Każdy kto miał kiedykolwiek kontakt z programowaniem wie że takie liczby przechowywane
są w pamięci komputera w postaci przybliżonej (zapis przy pomocy cechy i mantysy
czyli wykładnika potęgi). Tak też jest w przypadku MySLQ'a. Nie będę się w tym
momencie rozwodził nad teorią takiego zapisu, gdyż można ją znaleźć w dowolnej
książce dotyczącej programowania (np.: Turbo Pascal 7.0 cz. I Andrzeja Marciniaka
- tą książkę zna chyba każdy kto kiedykolwiek miał kontakt z techniczną uczelnią
w Polsce).
Oto zakresy dostępnych
typów rzeczywistych:
- FLOAT (M,D)-
liczba rzeczywista z zakresu -3.402823466E+38 do -1.175494351E-38 i 0 i 1.175494351E-38
do 3.402823466E+38 (4 bajty),
- REAL, DOUBLE
- liczba rzeczywista z zakresu 1.7976931348623157E+308 do -2.2250738585072014E-308
i 0 i 2.2250738585072014E-308 to 1.7976931348623157E+308 (8 bajtów)
Ostatnim bardzo
przydatnym i wygodnym typem numerycznym jest: DECIMAL (M,D) gdzie M jest
ilością znaczących pozycji w liczbie a Y skalą liczby np.: DECIMAL(9,2) będzie
miał zakres od -9999999.99 do 9999999.99 jest to liczba składająca się z dziewięciu
pozycji i przesunięta o dwa miejsca w prawo (liczba zajmuje M bajtów, jeśli
M<D to D+2 bajty). DECIMAL jest doskonałym typem do przechowywania wartości
"pieniężnych" np.: cen, gdyż przechowuje informacje w sposób dokładny, oczywiście
w taki sposób w jaki zdefiniujemy.
Czasowe typy
danych w MySQL'u
Jak pewnie każdy
się domyśla służą do przechowywania wartości związanych z czasem czyli roku,
daty, godziny lub wszystkiego równocześnie. Możemy skorzystać z następujących
typów:
- DATETIME - w
takiej kolumnie umieszczamy datę wraz z godziną w formacie : YYYY:MM:DD:HH:mm:SS
(odpowiednio rok, miesiąc, dzień, godzina, minuta i sekunda), typ ten akceptuje
zakres od '1000-01-01 00:00:00' do '9999-12-31 23:59:59'
- DATE - czyli
data w formacie YYYY:MM:DD (rok, miesiąc, dzień), zakres od '1000-01-01' do
'9999-12-31'
- TIME - godzina
w formacie HH:mm:SS (godzina, minuta, sekunda)
- YEAR - rok YYYY
(np.:2001) zakres od 1901 do 2155 (1 bajt)
- TIMESTAMP(n)
- jest to data z godziną, precyzję tego zapisu ustalamy sami poprzez wartość
n (dla przykładu n=14 oznacza datę i godzinę w formacie YYYY:MM:DD:HH:mm:SS
a n=2 YY) zakres od 1970 do 2037
Łańcuchowe (znakowe)
typy danych
Przy ich pomocy umieszczamy w bazie danych, informacje typowo tekstowe (łańcuchy
znaków). Do naszej dyspozycji są następujące typy:
- CHAR(n) - jest
to łańcuch znaków o długości n, gdzie n może przyjmować wartości od 0-255,
bez względu na to jaki łańcuch zapiszemy do takiej komórki tabeli, zawsze będzie
zajmował n bajtów.
- VARCHAR(n) -
ten typ danych pamięta łańcuch znaków oraz jego długość, wartość n określa
maksymalną długość łańcucha, np.: jeśli do kolumny VARCHAR(10) zapiszemy łańcuch
o długości 5 znaków to będzie on zajmował w bazie 6 bajtów
- BLOB - "pamięta"
dane w formie binarnej, ze względu na maksymalną długość takiego ciągu znaków
dzieli się na kilka podtypów: TINYBLOB (2^8 znaków), BLOB (2^16 znaków), MEDIUMBLOB
(2^24 znaków), LONGBLOB (2^32 znaków)
- TEXT - dane
tekstowe, podobnie jak BLOB ma odmiany ze względu na długość tekstu: TINYTEXT
(2^8 - 256 znaków ), TEXT (2^16 - 65535 znaków), MEDIUMTEXT (2^24 -16777216
znaków), LONGTEXT (2^32 - 4294967296 znaków)
Typ wyliczeniowy
ENUM
dane przyjmują wartości spośród wcześniej przygotowanej przez nas listy. Lista
taka może mieć maksymalnie 65535 elementów.
Typ SET
typ zbiorowy - pozycja taka może przyjmować 0 lub więcej wartości spośród listy przygotowanej
podczas tworzenia tabeli. Przykład : dla definicji SET ("jeden" , "dwa") do
komórki tabeli możemy zapisać następujące wartości:
"" lub "jeden" lub "dwa" lub "jeden, dwa". Definicja SET może mieć maksymalnie
64 elementy.
Mniej doświadczonym
użytkownikom radzę przed przystąpieniem do tworzenia tabel wydrukować sobie
ten dokument i mieć go pod ręką, gdyż nie widzę sensu uczenia się tych wszystkich
wartości na pamięć. W tym momencie pozwolę sobie przerwać opis typów danych,
aby zgodnie z obietnicą pokazać jak generować statystyki naszej bazy. Przy tej
okazji przybliżę działanie funkcji agregujących : COUNT, SUM, AVG, MIN, MAX.
Funkcja COUNT
zlicza rekordy w tabeli korzystamy z niej w następujący sposób:
SELECT COUNT(*) tyle_mamy_klientów from klienci;
Wynikiem takiego
zapytania będzie ilość rekordów w tabeli klienci.
Funkcja SUM
zwraca sumę elementów danej kolumny:
SELECT SUM(wiek) from klienci;
Dzięki takiemu
zapytaniu dowiemy się ile w sumie maja lat nasi klienci - przykład nie jest
najlepszy ale pokazuje jak działa ta funkcja.
Aby dowiedzieć
się jaki jest średni wiek zarejestrowanych klientów piszemy:
SELECT AVG(wiek) from klienci;
Za wyliczenie średniej
arytmetycznej oczywiście odpowiada finkcja AVG
W analogiczny sposób
szukamy minimalnego i maksymalnego wieku w bazie:
SELECT MAX(wiek)
from klienci;
SELECT MIN(wiek)
from klienci;
Jesli chcemy zobaczyć
kto w naszej bazie jest najstarszy piszemy:
SELECT imie,
nazwisko, wiek FROM klienci ORDER BY wiek DESC
LIMIT 1;
Klauzula ORDER
BY określa kolumnę według której mają być posortowane wyniki a DESC
powoduje że jest to porządek malejący, przeeciwne działąnie do DESC ma ASC
- sortuje w porządku malejącym. Wstawienie ASC w miejsce DESC spowoduje wyświetlenie
najmłodszego zarejestrowanego klienta. Dzięki dopisaniu "LIMIT 1"
kwerenda zwróci tylko jeden rekord.
Niestety MySQL
nie obsługuje jeszcze zapytań typu:
SELECT imie, nazwisko, wiek
FROM klienci
WHERE wiek=(SELECT max(wiek) from klienci);
Zapytania takie
działają w np.: MS DATA ENGINE albo SYBASE ale za te serwery trzeba słono zapłacić.
W tym odcinku mojego
kursu to już wszystko. Dociekliwych czytelników tradycyjnie odsyłam do dokumentacji
MySQL'a. Wszelkie uwagi oraz pytania proszę kierować na casha@hacker.pl.
Autor : Rafał Kasza casha@hacker.pl www.casha.prv.pl
|