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 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 powyż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:
OperatorNazwaSkładniaOpis=Równośćwyrażenie = wyrażenieZwraca prawdę, jeżeli oba wyrażenia mają identyczną wartość.!=Nierównośćwyrażenie != wyrażenieZwraca prawdę, jeżeli oba wyrażenia mają różne wartości.<>Nierównośćwyrażenie <> wyrażenieZwraca prawdę, jeżeli oba wyrażenia mają różne wartości.Większe niżwyrażenie > wyrażenieZwraca prawdę, jeżeli lewe wyrażenie ma większą wartość od prawego.=Większe lub równewyrażenie >= wyrażenieZwraca prawdę, jeżeli lewe wyrażenie ma większą lub równą wartość prawemu.!Negacja (nie)!wyrażenieZwraca prawdę, jeżeli wyrażenie jest fałszywe i fałsz, jeśli prawdziwe.NOTNegacja (nie)NOT wyrażenieZwraca prawdę, jeżeli wyrażenie jest fałszywe i fałsz, jeśli prawdziwe.ANDKoniunkcja logiczna (i)wyrażenie AND wyrażenieZwraca prawdę, jeżeli oba wyrażenia są prawdziwe.ORAlternatywa logiczna (lub)wyrażenie OR wyrażenieZwraca prawdę, jeżeli przynajmniej jedno z wyrażeń jest prawdziwe.
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);
Nie możemy wśród wartości wymienić NULL
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