MENU

Ankieta na stronie WWW cz. II (przy użyciu PHP oraz MySQL)

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

 

Contents copyright © 2000 - 2001, Krzysztof Dziewoński. All rights reserved.