Biblioteka PHP

Pobieranie rekordów

Opis składni, operatorów i możliwości zapytań SELECT pobierających i przetwarzających dane zapisane w bazie danych.

Strona Główna > 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 przykład zapytania na stworzonej w poprzednim rozdziale 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;

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:
Operator Nazwa Składnia Opis
= Równość wyrażenie = wyrażenie Zwraca prawdę, jeżeli oba wyrażenia mają identyczną wartość.
!= Nierówność wyrażenie != wyrażenie Zwraca prawdę, jeżeli oba wyrażenia mają różne wartości.
Operatory AND oraz OR posiadają także warianty && oraz ||. 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(*), 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: 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.

Złożone komendy SQL

SELECT DISTINCT pole FROM tabela
SELECT DISTINCT - pobranie niepowtarzalnych wartości danego pola (bez duplikatów)

SELECT * FROM tabela WHERE pole IN ('wart1', 'wart2', 'wart3' itd..)
Predykat IN pozwala pobrać dane z tabeli, w której pole ma wartość odpowiadającą jednej z podanych

SELECT * FROM tabela WHERE pole BETWEEN liczba AND liczba
Predykat BETWEEN pozwala określić zakres wartości jakie ma spełniać wartość z podanego pola.

SELECT * FROM tabela1 JOIN tabela2 ON tabela1.pole = tabela2.pole
JOIN / ON pozwala na połączeniu zapytań - pobieramy dane z tabeli 1 gdzie jakieś jej pole ma wartość taką jak pole z tabeli 2.

SELECT pole, pole+1 AS alias FROM tabela
Operator AS pozwala określić alias dla tabeli czy dla pola. W tym przypadku na wartości pola wykonujemy operację i wynik udostępnić musimy pod aliasem - używamy AS

SELECT pole FROM tabela UNION ALL SELECT pole FROM tabela
UNION czy UNION ALL pozwala na łączenie wielu zapytań w jedno. Warunek - ta sama ilość kolumn i te same typy danych. UNION ALL działa szybciej ale dopuszcza duplikaty danych.

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


Podziel się!

Flaker.pl Blipnij del.icio.us Napisz na Twitterze Napisz na Facebooku Google Bookmarks Wyślij link Wersja do druku

Menu

Biblioteki

Pokaż/Dodaj komentarze

Biblioteka Riklaunima. Hosting megiteam.pl. Serwis oparty o Django/Python.