Pobieranie rekordów
14 July 2008
Comments
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)
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)
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. |
pole IN(wartosci)
SELECT id, nazwa FROM produkty WHERE id IN(1,2,4);
pole NOT IN(wartosci)
SELECT id, nazwa FROM produkty WHERE id NOT IN(1,2,4);
pole BETWEEN mniejszy AND wiekszy
SELECT id, nazwa FROM produkty WHERE ilosc BETWEEN 0 AND 100;
pole IS NULL
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)
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)
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)
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)
SELECT id, nazwa FROM produkty LIMIT 2, 3; +----+-------------------------+ | id | nazwa | +----+-------------------------+ | 3 | Zszywacze | | 4 | Karteczki samoprzylepne | | 5 | Strugaczki | +----+-------------------------+ 3 rows in set (0.00 sec)
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)
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)
UPDATE `produkty` SET `ilosc` = NULL WHERE `id` = 5;
SELECT COUNT(ilosc) FROM produkty; +--------------+ | COUNT(ilosc) | +--------------+ | 6 | +--------------+ 1 row in set (0.00 sec)
SELECT COUNT(id) FROM produkty WHERE ilosc > 0;
SELECT COUNT(ilosc) FROM produkty;
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)
- Ustawiamy w rekordzie 5 pole ilosc na NULL.
- Obliczamy średnią ilość towarów w magazynie. Wynik: 90,8(3)
- Ustawiamy w rekordzie 5 pole ilosc na 0.
- Obliczamy średnią ilość towaru w magazynie. Wynik: 77,86
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 podanychSELECT * 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 ASSELECT 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
RkBlog
Comment article