Pobieranie rekordów - Bazy danych - Podstawy PHP

Pobieranie rekordów

Osobnego omówienia wymaga operacja pobierania rekordów z bazy danych, która jest esencją pracy z tego typu aplikacjami. W poprzednim rozdziale poznaliśmy dla potrzeb testowych zapytanie SELECT * FROM tabela, lecz było ono podane wyłącznie, aby można było sprawdzić, czy modyfikacja zawartości tabel rzeczywiście się powiodła. Tymczasem jego możliwości są dużo bardziej skomplikowane i umożliwiają wykonywanie wielu ciekawych rzeczy. Teraz przyjrzymy się im dokładniej.

Filozofia zapytania SELECT

Zapytanie SELECT to w zasadzie zbiór klauzul, które możemy dodawać i odejmować, działających jak filtry dla danych. Jako rezultat działania otrzymujemy zawsze to, co przejdzie przez wszystkie z nich. Podstawową i jedyną obowiązkową klauzulą jest oczywiście SELECT dane pokazująca, co należy pobrać. Za dane możemy podstawić listę wyrażeń odseparowanych przecinkami, które są nam potrzebne. Oto malutki przykład:

SELECT 1

Jego wykonanie w wierszu poleceń spowoduje wyświetlenie się tekstowej tabelki z wynikiem:

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Pierwszy wiersz zawiera zawsze nazwy wszystkich kolumn w rekordzie. Nazwy te wykorzystamy, kiedy zaczniemy pisanie skryptów łączących się z bazą, aby pobrać wyniki z rekordów. Kolejne wiersze obrazują wszystkie pasujące rekordy wyników.

Z doświadczenia możemy powiedzieć, że pozostawianie bazie danych MySQL spraw nazewnictwa pól na liście wyników nie należy do najszczęśliwszych i w wielu przypadkach zachodzi potrzeba jego ręcznego określenia nazwy. Można to uczynić, dodając po każdej wartości do pobrania dodatku AS `nowa_nazwa`:

SELECT 1 AS `pole`

+------+
| pole |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Pobieranie statycznych danych nie wnosi jednak dużo do funkcjonalności baz danych. O wiele więcej zyskamy, kiedy będziemy mogli pobierać rekordy z tabel. Aby móc wymienić nazwy pól w klauzuli SELECT, musimy dołożyć do zapytania taki dodatek: FROM lista_tabel. lista_tabel to lista tabel odseparowanych przecinkami, z których zamierzamy korzystać. W tym odcinku poprzestaniemy na pojedynczej tabeli i dopiero później powiemy sobie, jak można obsłużyć więcej. Spróbujmy dowiedzieć się zatem czegoś o stworzonej ostatnio tabeli produkty:

SELECT id, nazwa FROM produkty;
+----+-------------------------+
| id | nazwa                   |
+----+-------------------------+
|  1 | Dlugopisy niebieskie    |
|  2 | Dlugopisy czerwone      |
|  3 | Zszywacze               |
|  4 | Karteczki samoprzylepne |
+----+-------------------------+
4 rows in set (0.02 sec)

Przypominamy, że zapytania kończymy średnikiem. Tabelka poniżej to wynik jego działania. Widzimy w niej pobrane pola id oraz nazwa wszystkich rekordów w tabeli produkty. Gdybyśmy chcieli pobrać wartości wszystkich pól, moglibyśmy w wykazie danych wpisać po prostu gwiazdkę:

SELECT * FROM produkty;

Okazuje się jednak, że paradoksalnie takie rozwiązanie ma mniejszą wydajność, niż wypisanie wszystkich pól ręcznie! Miej to na uwadze podczas projektowania twoich zapytań.

Klauzula WHERE

Do tej pory MySQL zwracał nam wszystkie rekordy bez wyjątku, lecz w codziennej praktyce na dane nakłada się rozmaite warunki pełniące rolę filtrów. Jeżeli chcemy wiedzieć, którzy użytkownicy naszego serwisu napisali już ponad 200 postów, nie musimy pobierać wszystkiego i dokonywać ręcznej analizy informacji. Wystarczy nałożyć na zapytanie warunek, który nie dopuści do listy wyników tych rekordów, gdzie ilość postów jest mniejsza niż podana wartość. Wszystko to należy do kompetencji klauzuli WHERE warunek, która pojawia się również przy zapytaniach UPDATE oraz DELETE. Najprostszą operacją jest bez wątpienia zwrócenie konkretnego rekordu:

SELECT id, nazwa FROM produkty WHERE id = 3;
+----+-------------------------+
| id | nazwa                   |
+----+-------------------------+
|  3 | Zszywacze               |
+----+-------------------------+
1 row in set (0.01 sec)

Identycznie, jak w przypadku PHP, warunek jest zbiorem wyrażeń połączonych operatorami, których kolejnością także możemy manewrować wykorzystując nawiasy. Oto lista operatorów logicznych oraz operatorów porównania:

Operatory AND oraz OR posiadają także warianty && oraz || (tak samo PHP posiada and i or). Oprócz tego, dostępne są tradycyjne operatory arytmetyczne, a także kilka specjalnych, niewystępujących nigdzie indziej:

pole IN(wartosci)

Prawda, jeśli wartość pola znajduje się na liście wartości podanej w nawiasach. Spróbujemy pobrać nim rekordy o ID 1, 2 oraz 4.

SELECT id, nazwa FROM produkty WHERE id IN(1,2,4);

pole NOT IN(wartosci)

Prawda, jeśli wartość pola NIE znajduje się na liście wartości podanej w nawiasach. Poniżej to samo zapytanie, ale omijające rekordy o podanych ID.

SELECT id, nazwa FROM produkty WHERE id NOT IN(1,2,4);

pole BETWEEN mniejszy AND wiekszy

Wartość pola znajduje się w przedziale od mniejszy do wiekszy. Spróbujemy pobrać nim produkty, których ilość w magazynie waha się od 0 do 100:

SELECT id, nazwa FROM produkty WHERE ilosc BETWEEN 0 AND 100;

pole IS NULL

Podczas tworzenia tabel powiedzieliśmy sobie nieco o polach z dozwolonymi wartościami pustymi (null). Za pomocą tego operatora oraz jego przeczenia IS NOT NULL możemy sprawdzać, czy dane pole zawiera wartość pustą, czy nie. W naszej tabeli tylko pole ilosc zezwala na użycie wartości pustych. Sprawdźmy więc, które rekordy takowe posiadają:

SELECT id, nazwa FROM produkty WHERE ilosc IS NULL;

Klauzula ORDER BY

Ta klauzula dodaje możliwość sortowania wyników według określonego kryterium. Tradycyjnie podajemy ją po WHERE. Jej składnia to: ORDER BY lista_wyrazen. lista_wyrazen to lista oddzielonych przecinkami wyrażeń, według wartości których zostaną posortowane rekordy. Domyślnie obowiązuje kolejność od najmniejszego do największego, ale możemy ją odwrócić, dodając po wyrażeniu słowo DESC. Spróbujmy posegregować nasze rekordy względem ceny.

SELECT id, nazwa, cena FROM produkty ORDER BY cena;
+----+-------------------------+------+
| id | nazwa                   | cena |
+----+-------------------------+------+
|  6 | Gumki do scierania      |  0.5 |
|  7 | Spinacze do papieru     |  0.5 |
|  5 | Strugaczki              |  0.9 |
|  1 | Dlugopisy niebieskie    | 2.15 |
|  2 | Dlugopisy czerwone      | 2.15 |
|  4 | Karteczki samoprzylepne |  3.6 |
|  3 | Zszywacze               |  9.5 |
+----+-------------------------+------+
7 rows in set (0.00 sec)

A teraz w odwrotnej kolejności:

SELECT id, nazwa, cena FROM produkty ORDER BY cena DESC;
+----+-------------------------+------+
| id | nazwa                   | cena |
+----+-------------------------+------+
|  3 | Zszywacze               |  9.5 |
|  4 | Karteczki samoprzylepne |  3.6 |
|  1 | Dlugopisy niebieskie    | 2.15 |
|  2 | Dlugopisy czerwone      | 2.15 |
|  5 | Strugaczki              |  0.9 |
|  6 | Gumki do scierania      |  0.5 |
|  7 | Spinacze do papieru     |  0.5 |
+----+-------------------------+------+
7 rows in set (0.00 sec)

Możemy też przyjąć kolejne kryterium sortowania, jeśli dwa rekordy będą miały identyczną cenę. Przyjmijmy, że wtedy będą one sortowane pod względem tytułu.

SELECT id, nazwa, cena FROM produkty ORDER BY cena DESC, nazwa;
+----+-------------------------+------+
| id | nazwa                   | cena |
+----+-------------------------+------+
|  3 | Zszywacze               |  9.5 |
|  4 | Karteczki samoprzylepne |  3.6 |
|  2 | Dlugopisy czerwone      | 2.15 |
|  1 | Dlugopisy niebieskie    | 2.15 |
|  5 | Strugaczki              |  0.9 |
|  6 | Gumki do scierania      |  0.5 |
|  7 | Spinacze do papieru     |  0.5 |
+----+-------------------------+------+
7 rows in set (0.00 sec)

Widzimy teraz, że rekordy "Długopisy czerwone" oraz "Długopisy niebieskie" zamieniły się miejscami. Jeśli połączymy wszystko z klauzulą WHERE, poczujemy prawdziwą potęgę baz danych. Posortujmy według ceny tylko te rekordy, których jakość oznaczona jest jako 3:

SELECT id, nazwa, cena FROM produkty WHERE jakosc = 3
ORDER BY cena DESC,  nazwa;
+----+-----------------------+------+
| id | nazwa                 | cena |
+----+-----------------------+------+
|  2 | Dlugopisy czerwone    | 2.15 |
|  1 | Dlugopisy niebieskie  | 2.15 |
|  6 | Gumki do scierania    |  0.5 |
+----+-----------------------+------+
3 rows in set (0.00 sec)

Klauzula LIMIT

Na stronach internetowych często prezentowane są olbrzymie ilości informacji. Aby wyświetlenie ich spisu nie przeciążało łącza, listę wyników dzieli się na strony (inaczej: porcjuje) tak, że naraz wyświetla się jedynie niewielka jej część, a do reszty możemy się dostać poprzez odpowiednie linki nawigacyjne. Oczywiste jest, że wybieranie tego małego kawałka danych powinno zachodzić po stronie bazy danych, a nie PHP. Tak jest w istocie, dzięki klauzuli LIMIT. Pozwala nam ona na zażądanie jedynie określonego kawałka rekordów pasujących do podanego wyrażenia. Pobiera ona dwie informacje: numer (nie mylić z ID!) rekordu w wynikach, od którego należy zacząć pobieranie oraz interesującą nas ilość rekordów.

Istnieje kilka składni tego polecenia. Pokażemy je na przykładach.

SELECT id, nazwa FROM produkty LIMIT 3;
+----+-----------------------+
| id | nazwa                 |
+----+-----------------------+
|  1 | Dlugopisy niebieskie  |
|  2 | Dlugopisy czerwone    |
|  3 | Zszywacze             |
+----+-----------------------+
3 rows in set (0.02 sec)

LIMIT 3 spowodowało, że zostały pokazane pierwsze trzy rekordy, począwszy od pierwszego. Aby zmienić punkt rozpoczęcia, ilość rekordów poprzedzamy "numerem startowym":

SELECT id, nazwa FROM produkty LIMIT 2, 3;
+----+-------------------------+
| id | nazwa                   |
+----+-------------------------+
|  3 | Zszywacze               |
|  4 | Karteczki samoprzylepne |
|  5 | Strugaczki              |
+----+-------------------------+
3 rows in set (0.00 sec)

Tym razem wyświetliła się nam dalsza część zbioru wyników. Przypominamy, że rasowi informatycy zaczynają liczenie od zera i tak samo jest w przypadku numerów startowych. Dlatego "2" w przykładzie oznacza w rzeczywistości rozpoczęcie od rekordu trzeciego. W poprzednim przykładzie start od pierwszego rekordu moglibyśmy zapisać jako LIMIT 0, 3.

LIMIT nie jest częścią standardu ANSI SQL, dlatego też inne systemy baz danych mogą używać innej składni lub nawet innych sposobów do wykonywania porcjowania danych. Aby zapewnić pewną kompatybilność, MySQL udostępnia niektóre z nich jako alternatywę. Oto powyższy przykład zapisany z wykorzystaniem składni bazy PostgreSQL, który działa także i tu:

SELECT id, nazwa FROM produkty LIMIT 3 OFFSET 2;
+----+-------------------------+
| id | nazwa                   |
+----+-------------------------+
|  3 | Zszywacze               |
|  4 | Karteczki samoprzylepne |
|  5 | Strugaczki              |
+----+-------------------------+
3 rows in set (0.00 sec)

Dlatego jeśli planujesz tworzenie aplikacji pod oba te systemy naraz, pamiętaj o różnicach w implementacji języka SQL między nimi. Dla większej ilości baz danych może być konieczne zupełne zrezygnowanie z klauzuli LIMIT na rzecz odpowiednio konstruowanych warunków WHERE oraz dodatkowych pól w tabelach.

Funkcje grupujące

Język SQL umożliwia stosowanie funkcji do częściowej obróbki danych po stronie serwera. Specyficzną grupą funkcji są tzw. funkcje grupujące. W przeciwieństwie do reszty, operują one na zbiorach rekordów, podając o nich różne istotne informacje. Wiąże się z tym kilka ograniczeń użycia, lecz póki co nie będą nas one dotyczyć, gdyż nie potrafimy pobierać jeszcze danych z kilku tabel naraz. Do tego zagadnienia wrócimy w następnym rozdziale.

Pierwszą funkcją, z jaką się zapoznamy, będzie COUNT(). Podaje ona ilość rekordów, które pasują do warunku.

SELECT COUNT(id) FROM produkty;
+-----------+
| COUNT(id) |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

Teraz wiemy, że w naszej tabeli jest siedem rekordów. Niektórzy programiści stosują składnię COUNT(*) (pamiętasz, co oznacza gwiazdka w zapytaniach SELECT?), jednak my zdecydowanie odradzamy jej użycie ze względów niższej wydajności.

Zadajmy sobie pytanie, dlaczego COUNT() wymaga podawania konkretnego pola, zamiast np. nazwy tabeli? Wszystko wyjaśni się, kiedy zobaczymy, jak funkcja ta reaguje na pola zezwalające obecność wartości NULL. W naszej tabeli jedynie ilosc zezwala na jej obecność. Wstawmy więc ją do rekordu o ID 5, aby mieć na czym eksperymentować:

UPDATE `produkty` SET `ilosc` = NULL WHERE `id` = 5;

Zobaczmy, co się teraz stanie po wykonaniu funkcji COUNT() na polu ilosc:

SELECT COUNT(ilosc) FROM produkty;
+--------------+
| COUNT(ilosc) |
+--------------+
|            6 |
+--------------+
1 row in set (0.00 sec)

Niespodzianka, zwróciło nam informację o sześciu rekordach, chociaż żadnego nie kasowaliśmy. Spokojnie, wszystko jest w porządku. COUNT() celowo opuszcza wartości NULL, gdyż tak wynika ich definicji. Po co liczyć coś, czego na dobrą sprawę nie ma? Zauważmy, jak mądrze postąpiliśmy, wprowadzając NULL do naszej bazy. Teoretycznie przy braku towaru w magazynie można by ustawiać rekordom wartości 0, lecz wtedy do pobrania gatunków produktów będących jeszcze na stanie musimy zastosować klauzulę WHERE:

SELECT COUNT(id) FROM produkty WHERE ilosc > 0;

Jeżeli zamiast 0 wprowadzimy wartości NULL, ulegnie ono skróceniu:

SELECT COUNT(ilosc) FROM produkty;

Nie tylko ta funkcja grupująca reaguje na wartość NULL. Udowodnimy to na przykładzie liczenia średniej ilości towaru w magazynie. Służy do tego funkcja grupująca AVG(). Hipoteza jest następująca: jeśli funkcja ta jest wrażliwa na obecność NULL, powinna dawać różne wyniki w zależności od tego, czy w rekordzie towaru niewystępującego w magazynie oznaczymy ilość przez NULL, czy przez 0. Sprawdźmy to. Oto ciąg wykonywanych przez nas operacji:

mysql> UPDATE `produkty` SET `ilosc` = NULL WHERE `id` = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT AVG(ilosc) FROM produkty;
+------------+
| AVG(ilosc) |
+------------+
|    90.8333 |
+------------+
1 row in set (0.00 sec)

mysql> UPDATE `produkty` SET `ilosc` = 0 WHERE `id` = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT AVG(ilosc) FROM produkty;
+------------+
| AVG(ilosc) |
+------------+
|    77.8571 |
+------------+
1 row in set (0.00 sec)

Oto opis poczynionych kroków:

1. Ustawiamy w rekordzie 5 pole ilosc na NULL.
2. Obliczamy średnią ilość towarów w magazynie. Wynik: 90,8(3)
3. Ustawiamy w rekordzie 5 pole ilosc na 0.
4. Obliczamy średnią ilość towaru w magazynie. Wynik: 77,86

Jak widać, AVG() dało nam różne wyniki w zależności od tego, co mieliśmy w polu ilosc, potwierdzając tym samym naszą hipotezę. Zjawisko to bardzo łatwo wytłumaczyć. Wartość 0 traktowana jest jak normalna ilość. Zgodnie ze wzorem na średnią arytmetyczną, sumujemy sześć wartości, lecz dzielimy już przez siedem, z uwzględnieniem naszego zera. Wartość NULL wyraźnie mówi bazie danych MySQL: nie licz mnie, ja nie istnieję. Bądźmy świadomi tych różnic w działaniu, gdyż tyczą się one także pozostałych funkcji grupujących.

Ostatnimi funkcjami grupującymi, które poznamy, będą MAX(), MIN() oraz SUM(). Zwracają one kolejno: największą wartość użytą w danym polu, najmniejszą oraz sumę wszystkich wartości.

Ciekawe sztuczki

Na sam koniec pragniemy pokazać pewną ciekawą sztuczkę, która ukaże potęgę języka SQL i być może zachęci wielu z Was do dalszego pogłębiania swej wiedzy o systemach bazodanowych.

Sytuacja prezentuje się następująco: mamy tabelę, w niej pięć pól mogących przyjmować wartości 1 lub 0. Czy da się pobrać rekordy posortowane według tego, ile pól zostało ustawionych na 1? Odpowiedź brzmi: tak.

Zaczynamy od utworzenia tabeli i wypełnienia jej danymi:

CREATE TABLE `stany` (
`id` SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`stan1` TINYINT(1) NOT NULL DEFAULT '0',
`stan2` TINYINT(1) NOT NULL DEFAULT '0',
`stan3` TINYINT(1) NOT NULL DEFAULT '0',
`stan4` TINYINT(1) NOT NULL DEFAULT '0',
`stan5` TINYINT(1) NOT NULL DEFAULT '0'
) ENGINE = MYISAM;

Następnie wypełniamy ją testowymi danymi:

INSERT INTO stany (stan1,stan2,stan3,stan4,stan5) VALUES
(1,0,1,1,0),
(0,0,1,1,0),
(0,1,1,1,1),
(1,0,0,0,1),
(0,0,0,0,0),
(0,0,1,0,0),
(1,1,1,1,1),
(0,1,1,0,1);

Możemy już zacząć zabawę:

SELECT id FROM stany ORDER BY (stan1+stan2+stan3+stan4+stan5) DESC;
+----+
| id |
+----+
|  7 |
|  3 |
|  1 |
|  8 |
|  2 |
|  4 |
|  6 |
|  5 |
+----+
8 rows in set (0.00 sec)

Zadziwiające? Nie do końca. Jeżeli dokładnie czytałeś rozdział o klauzuli ORDER BY, być może zauważyłeś, że nigdzie nie ma tam wzmianki o konieczności wymieniania tabel. Jest wręcz przeciwnie - czarno na białym było tam napisane "lista wyrażeń".

Aby uczynić nasz wynik bardziej przyjaznym, spróbujmy wyświetlić obok ID sumę "włączonych" pól:

mysql> SELECT id, (stan1+stan2+stan3+stan4+stan5) AS `suma` FROM stany ORDER BY
`suma` DESC;
+----+------+
| id | suma |
+----+------+
|  7 |    5 |
|  3 |    4 |
|  1 |    3 |
|  8 |    3 |
|  2 |    2 |
|  4 |    2 |
|  6 |    1 |
|  5 |    0 |
+----+------+
8 rows in set (0.00 sec)

Czyżby kolejne zaskoczenie? Wymieniając na liście danych do zwrócenia sumę, oznaczyliśmy ją etykietą "suma". Jak się okazuje, całej operacji sumowania nie musimy później powtarzać w klauzuli ORDER BY, ani żadnej innej. Wystarczy, że odwołamy się do utworzonej wcześniej etykietki.

Opisem języka SQL można by zapełnić całą książkę. To, co podaliśmy w tym podręczniku, jest jedynie wstępem do tematu, który ma nam wystarczyć na początek przy programowaniu w PHP. Zanim jednak pokażemy, jak wykorzystać potęgę baz danych w tym języku, czeka nas jeszcze jeden rozdział poświęcony relacjom oraz indeksom.

Treść pochodzi ze strony WikiBooks i jest udostępniana na licencji GNU FDL

Autor: WikiBooks