BAZY DANYCH · INF.03

SELECT — czytanie danych z biblioteki

Pierwsze zapytania odpowiadają na proste pytania biblioteki: jakie książki mamy, ile kosztują i które są dostępne. Nauczysz się wybierać kolumny, zmieniać nazwy, sortować i ograniczać wynik.

SELECT i projekcja: wybór kolumn

Do tej pory oglądaliśmy tabele w phpMyAdmin. Teraz zaczynamy zadawać bazie pytania. Najważniejszym poleceniem jest SELECT — nie zmienia danych, tylko je odczytuje. Wynikiem jest zawsze tabela: zestaw kolumn i wierszy, który możesz dalej przeglądać, kopiować albo wykorzystać w aplikacji.

Projekcja to wybór kolumn, które pokażą się w wyniku. Zamiast wyświetlać całą tabelę, mówisz bazie, które atrybuty są potrzebne. Zapytanie SELECT tytul, cena FROM ksiazki zwraca dwie kolumny: tytuł i cenę. Kolejność kolumn w wyniku jest taka, jak w zapytaniu, a nie taka, jak w definicji tabeli. To przydatne, gdy przygotowujesz raport dla człowieka, który najpierw chce widzieć tytuł, a potem cenę.

SELECT tytul, cena
FROM ksiazki;

Gwiazdka SELECT * oznacza „wszystkie kolumny”. Jest wygodna podczas nauki i szybkiego podglądu, ale w raportach lepiej jej unikać. Po pierwsze, zwraca też kolumny, których nie potrzebujesz, więc wynik jest szerszy i trudniejszy do odczytania. Po drugie, gdy ktoś doda do tabeli nową kolumnę, raport zmieni się bez twojej wiedzy. Po trzecie, aplikacja może przestać działać, jeśli oczekuje dokładnie dwóch kolumn, a otrzyma trzy.

SELECT id, imie, nazwisko
FROM uczniowie;

To zapytanie zwraca trzy kolumny i wszystkie wiersze tabeli uczniowie. Nie ma w nim warunku, więc dostajesz również uczniów nieaktywnych. Filtrowanie, czyli wybór konkretnych wierszy, pojawi się w module 04. Tutaj skupiamy się na projekcji: które kolumny i w jakiej kolejności. Nazwy tabel i kolumn można ująć w backticki, zwłaszcza gdy zawierają polskie znaki albo są słowami zarezerwowanymi, na przykład `order`.

SELECT nazwa, wychowawca, rok_szkolny
FROM klasy;

Trzy pytania do bazy mogą wyglądać podobnie, ale odpowiadają na różne potrzeby: katalog książek, lista uczniów i lista klas. W każdym przypadku wynik jest nową tabelą, której nie zapisujesz w bazie. Możesz go posortować, ograniczyć liczbę wierszy albo połączyć z inną tabelą — te operacje poznasz w kolejnych sekcjach i modułach. Ważne, aby od początku pisać nazwy kolumn świadomie, a nie „brać wszystko, co się da”.

Przykłady z życia

1. Katalog na wystawie. Na plakat nie wstawiasz całej karty katalogowej. Wybierasz tytuł, autora i rok wydania, bo tylko te informacje mieszczą się obok okładki. SELECT działa tak samo: z wielu kolumn wybiera te, które są potrzebne do konkretnego celu.

2. Lista obecności. Wychowawca potrzebuje imion, nazwisk i numerów w dzienniku, ale nie potrzebuje adresów e-mail ani dat urodzenia. Zapytanie o trzy kolumny zwraca mniej danych i szybciej się je przegląda. Minimalizacja danych to nie tylko wygoda, ale też ochrona prywatności.

3. Podgląd w phpMyAdmin. Kliknięcie Przeglądaj pokazuje domyślnie wszystkie kolumny, bo panel nie wie, czego szukasz. Gdy piszesz własne zapytanie, możesz zachować się jak doświadczony bibliotekarz: pokazać tylko to, co naprawdę potrzebne.

Informatyczna pracownia: aplikacja mobilna pobierająca listę książek nie powinna ściągać wszystkich pól, jeśli wyświetla tylko tytuł i okładkę. Każde zbędne pole zajmuje transfer i pamięć. Projektant API często tworzy osobne zapytanie dla listy i osobne dla szczegółów — to praktyczne zastosowanie projekcji.

Aliasy i DISTINCT: czytelne wyniki

Nagłówki kolumn w wyniku nie zawsze są zrozumiałe dla człowieka. cena może znaczyć cenę zakupu, okładkową albo po rabacie; kategoria_id to dla bazy numer, a nie nazwa. Alias pozwala zmienić nagłówek w wyniku zapytania bez zmiany definicji tabeli. Piszemy go po słowie AS, choć w MySQL można je pominąć — warto jednak pisać je zawsze, bo zwiększa czytelność.

SELECT tytul AS ksiazka, cena AS cena_zl
FROM ksiazki;

W wyniku pojawią się kolumny ksiazka i cena_zl. W tabeli nadal istnieją tytul i cena; alias żyje tylko w wyniku tego zapytania. Jeśli nazwa aliasu zawiera spację, polską literę albo nawias, ujmij ją w backticki. Dzięki temu raport może mieć nagłówek „Cena (zł)” zamiast technicznego cena.

SELECT tytul AS `Tytuł książki`, cena AS `Cena (zł)`
FROM ksiazki;

Alias można nadać także tabeli: FROM ksiazki AS k. Wtedy zamiast pełnej nazwy piszesz k.tytul i k.cena. W prostym zapytaniu to zbędne, ale przy łączeniu kilku tabel skróty są niemal konieczne, bo kolumny mogą mieć te same nazwy. Alias tabeli obowiązuje tylko w danym zapytaniu i nie zmienia nazwy w bazie.

SELECT k.tytul, k.cena
FROM ksiazki AS k;

DISTINCT usuwa powtarzające się wiersze wyniku. Zapytanie SELECT DISTINCT kategoria_id FROM ksiazki zwróci osiem numerów kategorii, choć książek jest dwadzieścia cztery. To dlatego, że wiele książek należy do tej samej kategorii, a my pytamy wyłącznie o listę różnych kategorii. DISTINCT działa na całym wierszu wyniku, nie na pojedynczej kolumnie.

SELECT DISTINCT kategoria_id, wydawnictwo_id
FROM ksiazki;

To zapytanie zwróci dwanaście różnych par, a nie osiem kategorii i sześć wydawnictw osobno. Para (2, 3) oznacza książkę z kategorii drugiej wydaną przez wydawnictwo trzecie; taka para może wystąpić wiele razy, ale w wyniku pojawi się raz. To częsty błąd początkujących: dodanie drugiej kolumny zmienia znaczenie „unikalności”.

SELECT DISTINCT miasto
FROM wydawnictwa;

Wydawnictw jest sześć, ale miasta tylko cztery: Kraków występuje dwa razy, podobnie Warszawa. DISTINCT daje listę miast bez powtórzeń, którą można wykorzystać na przykład do zaplanowania wysyłki. Wartość NULL również jest traktowana jako jedna różna wartość, więc jeśli w kolumnie pojawi się brak danych, w wyniku zobaczysz jeden wiersz NULL, a nie wiele takich samych.

Przykłady z życia

1. Lista miast do wysyłki. Biblioteka zamawia książki u wydawnictw z różnych miast. Zamiast czytać sześć adresów i ręcznie wypisywać miasta, zapytanie z DISTINCT daje cztery unikalne miasta. To mniej pracy i mniejsze ryzyko pominięcia któregoś adresu.

2. Czytelny raport dla dyrektora. Dyrektor nie wie, co znaczy kategoria_id. Alias kategoria albo numer kategorii sprawia, że nagłówek jest zrozumiały. Wartości nadal są liczbami, ale raport można wydrukować bez dodatkowego słownika.

3. Lista stanowisk w bibliotece. Pięć osób pracuje na trzech stanowiskach. Zapytanie SELECT DISTINCT stanowisko FROM pracownicy odpowiada na pytanie „jakie stanowiska w ogóle istnieją”, a nie „kto gdzie pracuje”. To subtelna, ale ważna różnica.

Biuro: podczas porządkowania bazy klientów często trzeba sprawdzić, ile jest różnych województw, a nie ilu jest klientów. DISTINCT jest wtedy szybszy niż ręczne filtrowanie arkusza. Podobnie w matematyce: ze zbioru par wybierasz zbiór pierwszych współrzędnych, tracąc informację o powtórzeniach.

ORDER BY i LIMIT: porządkowanie raportu

Baza nie ma obowiązku zwracać wierszy w kolejności, której oczekujesz. Jeśli chcesz listę od najdroższej książki albo od najstarszego ucznia, musisz powiedzieć to wprost w klauzuli ORDER BY. Bez niej wynik jest poprawny, ale kolejność może się zmienić po dodaniu indeksu, zmianie silnika albo nawet po ponownym uruchomieniu serwera.

SELECT tytul, cena
FROM ksiazki
ORDER BY cena DESC, tytul ASC;

ASC oznacza rosnąco i jest domyślne, DESC malejąco. Można sortować po kilku kolumnach: najpierw po cenie malejąco, a przy równych cenach po tytule rosnąco. Drugi klucz nazywamy rozstrzygającym albo tie-breakerem. Bez niego dwie książki o tej samej cenie mogą pojawić się w dowolnej kolejności, a raport przestaje być powtarzalny.

SELECT imie, nazwisko, data_urodzenia
FROM uczniowie
ORDER BY data_urodzenia ASC;

Sortowanie dat działa inaczej niż sortowanie tekstu. Data w typie DATE jest porównywana chronologicznie, więc 1 lutego 2011 wypada przed 2 listopada 2011. Gdyby data była zapisana jako tekst w formacie „02.11.2011”, sortowanie alfabetyczne ustawiłoby ją przed „14.02.2011”, mimo że jest późniejsza. To kolejny powód, aby wybierać właściwe typy danych.

LIMIT ogranicza liczbę wierszy w wyniku, a OFFSET mówi, ile wierszy pominąć. LIMIT 5 zwraca pięć pierwszych wierszy po sortowaniu. LIMIT 3 OFFSET 3 pomija trzy wiersze i zwraca kolejne trzy — to druga „strona” wyników, gdy na ekranie mieszczą się trzy pozycje. W MySQL można też zapisać LIMIT 3, 3, ale wersja z OFFSET jest czytelniejsza.

SELECT tytul, cena
FROM ksiazki
ORDER BY cena DESC, tytul ASC
LIMIT 5;

To zapytanie zwraca pięć najdroższych książek. Kolejność klauzul ma znaczenie: ORDER BY musi wystąpić przed LIMIT. MySQL odrzuci zapis z LIMIT przed ORDER BY jako błąd składni, a samo ograniczenie bez sortowania zwróci pięć przypadkowych wierszy — czyli nie to, o co prosisz. Najpierw ustalasz zbiór i kolejność, na końcu obcinasz wynik.

SELECT id, data_zwrotu
FROM wypozyczenia
ORDER BY data_zwrotu ASC;

W tabeli wypozyczenia kolumna data_zwrotu może być NULL, bo książka jeszcze nie wróciła. MySQL w sortowaniu rosnącym stawia NULL na początku, a w malejącym na końcu. Jeśli chcesz najpierw pokazać książki już zwrócone, a potem te bez daty, możesz dopisać warunek sortowania ORDER BY data_zwrotu IS NULL, data_zwrotu. To zaawansowany trik, ale warto wiedzieć, że NULL nie jest ani większe, ani mniejsze od daty — ma własne zasady.

Przykłady z życia

1. Top 5 najdroższych książek. Biblioteka chce pokazać, które pozycje są najcenniejsze w księgozbiorze. Sortujesz malejąco po cenie i bierzesz pięć pierwszych wierszy. Bez ORDER BY „pięć pierwszych” oznaczałoby pięć przypadkowych książek, a nie najdroższych.

2. Najstarszy rocznik. Wychowawca prosi o pięć osób urodzonych najwcześniej. Sortujesz rosnąco po dacie urodzenia i ograniczasz wynik do pięciu. Data w typie DATE gwarantuje, że „najstarszy” oznacza naprawdę najwcześniejszą datę, a nie pierwszy napis w kolejności alfabetycznej.

3. Druga strona katalogu. Na stronie mieszczą się cztery książki. Pierwsza strona to LIMIT 4 OFFSET 0, druga to LIMIT 4 OFFSET 4, trzecia LIMIT 4 OFFSET 8. Offset liczy się od zera, więc „pomiń 4” oznacza, że pierwszym widocznym wierszem jest piąty.

Biuro: w raporcie sprzedaży „pięć najlepiej sprzedających się produktów” zawsze wymaga sortowania po liczbie sztuk i ograniczenia wyniku. Jeśli menedżer dostanie pięć przypadkowych produktów, podejmie złą decyzję. ORDER BY i LIMIT to nie kosmetyka, lecz warunek sensownego raportu.

Ćwiczenia SQL

Zadania do samodzielnego wykonania w edytorze SQL.

• 1Katalog książek — projekcjaWyświetl tytuł, rok wydania i cenę wszystkich książek, bez zbędnych kolumn.Zadanie i podpowiedzi →
• 2Kategorie bez powtórzeńWyświetl unikalne identyfikatory kategorii jako kolumnę kategoria, rosnąco.Zadanie i podpowiedzi →
• 3Pięć najstarszych książekPosortuj książki od najstarszej i ogranicz wynik do pięciu pozycji.Zadanie i podpowiedzi →
• 4Druga strona rankingu cenPomiń cztery najdroższe książki i pokaż kolejne cztery pozycje rankingu.Zadanie i podpowiedzi →