Filtrowanie i Sortowanie Danych

Witaj z powrotem.

W poprzedniej lekcji nauczyłeś się typów danych i ograniczeń.

Dowiedziałeś się, że PostgreSQL nie powinien akceptować bzdur.

Żadnych bananowych wieków.

Żadnych ujemnych cen.

Żadnych zduplikowanych emaili.

Żadnych tajemniczych pustych użytkowników wchodzących do bazy danych tak, jakby byli u siebie.

Bardzo dobrze.

Dzisiaj nauczymy się znajdować dane, których naprawdę potrzebujemy.

Bo kiedy tabela ma dużo wierszy, to nie wystarczy:

SELECT * FROM products;

To zwraca wszystko.

A czasami nie chcesz wszystkiego.

Chcesz:

• produkty tańsze niż 100;
• użytkowników z jednego miasta;
• aktywnych klientów;
• książki opublikowane po konkretnym roku;
• kursy zawierające słowo JavaScript;
• najdroższe produkty;
• tylko pierwsze pięć wyników.

Tutaj filtrowanie i sortowanie stają się ważne.

Baza danych jest użyteczna nie tylko dlatego, że przechowuje dane.

Baza danych jest użyteczna dlatego, że pomaga znaleźć właściwe dane.

Inaczej jest tylko bardzo drogą szufladą.

Czego Się Nauczysz

W tej lekcji nauczysz się:

• jak filtrować wiersze przez WHERE;
• jak działają operatory porównania;
• jak używać =;
• jak używać !=;
• jak używać >;
• jak używać <;
• jak używać >=;
• jak używać <=;
• jak łączyć warunki przez AND;
• jak łączyć warunki przez OR;
• jak szukać tekstu przez LIKE;
• jak szukać tekstu bez rozróżniania wielkości liter przez ILIKE;
• jak sortować dane przez ORDER BY;
• jak ograniczać wyniki przez LIMIT;
• jak pomijać wyniki przez OFFSET;
• jak unikać typowych błędów filtrowania.

Na końcu tej lekcji będziesz umieć zadawać PostgreSQL bardziej precyzyjne pytania.

Nie:

Daj mi wszystko.

Ale:

Daj mi aktywnych klientów z Mediolanu, posortowanych po imieniu, ale tylko pierwszych 10.

Dużo lepiej.

Dużo bardziej profesjonalnie.

Mniej krzyczenia na bazę danych.

Przygotuj Bazę Danych

Otwórz PostgreSQL:

sudo -iu postgres psql

Połącz się z bazą danych:

\c learning_postgresql

Jeśli nie masz tej bazy danych, utwórz ją:

CREATE DATABASE learning_postgresql;

Potem połącz się:

\c learning_postgresql

Teraz stwórz tabelę do tej lekcji.

Użyjemy products.

Najpierw usuń starą tabelę, jeśli istnieje:

DROP TABLE IF EXISTS products;

Teraz ją utwórz:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(100),
  price NUMERIC(10, 2) CHECK (price >= 0),
  available BOOLEAN DEFAULT true,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5)
);

Ta tabela ma:

• id;
• name;
• category;
• price;
• available;
• rating.

Teraz wstaw dane:

INSERT INTO products (name, category, price, available, rating)
VALUES
  ('Laptop', 'Electronics', 900.00, true, 5),
  ('Mouse', 'Electronics', 25.00, true, 4),
  ('Keyboard', 'Electronics', 70.00, false, 4),
  ('Desk Chair', 'Furniture', 150.00, true, 5),
  ('Notebook', 'Office', 5.00, true, 3),
  ('Pen', 'Office', 2.00, true, 3),
  ('Desk Lamp', 'Furniture', 45.00, true, 4),
  ('Monitor', 'Electronics', 250.00, true, 5),
  ('Bookshelf', 'Furniture', 120.00, false, 4),
  ('USB Cable', 'Electronics', 10.00, true, 2);

Sprawdź dane:

SELECT * FROM products;

Teraz mamy wystarczająco danych do praktyki.

Nie miliony wierszy.

Ale wystarczająco, żeby PostgreSQL trochę się porozciągał.

Jak joga bazodanowa.

WHERE

WHERE filtruje wiersze.

Bez WHERE PostgreSQL zwraca wszystkie wiersze:

SELECT * FROM products;

Z WHERE PostgreSQL zwraca tylko wiersze, które spełniają warunek:

SELECT * FROM products
WHERE category = 'Electronics';

To oznacza:

Daj mi tylko produkty, gdzie kategoria to Electronics.

Powinieneś zobaczyć produkty takie jak:

• Laptop;
• Mouse;
• Keyboard;
• Monitor;
• USB Cable.

WHERE to jedna z najważniejszych części SQL.

Pomaga zadawać konkretne pytania.

A konkretne pytania dają użyteczne odpowiedzi.

Bardzo życiowa lekcja.

I bardzo SQL.

Filtrowanie po Tekście

Aby filtrować po tekście, używaj pojedynczych cudzysłowów.

Przykład:

SELECT * FROM products
WHERE category = 'Office';

To zwraca produkty z kategorii Office.

Inny przykład:

SELECT * FROM products
WHERE name = 'Laptop';

To zwraca produkt o nazwie Laptop.

Ważne:

Wartości tekstowe potrzebują pojedynczych cudzysłowów.

Poprawnie:

WHERE name = 'Laptop'

Źle:

WHERE name = Laptop

Bez cudzysłowów PostgreSQL myśli, że Laptop to nazwa kolumny.

PostgreSQL nie zgaduje.

To baza danych.

Nie czytnik myśli.

Filtrowanie po Boolean

Kolumny boolean przechowują true albo false.

Przykład:

SELECT * FROM products
WHERE available = true;

To zwraca dostępne produkty.

Możesz też znaleźć niedostępne produkty:

SELECT * FROM products
WHERE available = false;

To jest przydatne dla wartości takich jak:

• active;
• published;
• completed;
• available;
• verified.

Nie zapisuj takich danych jako tekst:

yes
no
maybe

Używaj BOOLEAN.

Twoje zapytania będą czystsze.

Baza danych będzie mniej protestować.

Wszyscy wygrywają.

Prawie.

Operatory Porównania

PostgreSQL pozwala porównywać wartości.

Popularne operatory porównania:

=   równe
!=  różne
>   większe niż
<   mniejsze niż
>=  większe lub równe
<=  mniejsze lub równe

Ważne dla MDX:

Kiedy piszesz operatory takie jak <, <=, > i >= w zwykłym tekście, wkładaj je w backticks.

Inaczej MDX może pomyśleć, że próbujesz pisać JSX.

A potem może eksplodować emocjonalnie.

Bloki SQL są bezpieczne.

Tekst Markdown to niebezpieczna dzielnica.

Większe Niż

Znajdź produkty z ceną większą niż 100:

SELECT * FROM products
WHERE price > 100;

To zwraca produkty takie jak:

• Laptop;
• Desk Chair;
• Monitor;
• Bookshelf.

To przydatne, kiedy chcesz znaleźć drogie produkty.

Albo kiedy chcesz zrozumieć, dlaczego portfel płacze.

Mniejsze Niż

Znajdź produkty z ceną mniejszą niż 50:

SELECT * FROM products
WHERE price < 50;

To zwraca tańsze produkty, takie jak:

• Mouse;
• Notebook;
• Pen;
• Desk Lamp;
• USB Cable.

To przydatne dla zniżek, budżetów, filtrów i momentów, kiedy życie mówi:

Może nie laptop za 900 euro dzisiaj.

Smutne.

Ale finansowo odpowiedzialne.

Większe lub Równe

Znajdź produkty z ratingiem większym lub równym 4:

SELECT * FROM products
WHERE rating >= 4;

To zwraca produkty z ratingiem 4 i 5.

Operator >= oznacza:

większe lub równe

Więc 4 jest wliczone.

To przydatne, kiedy chcesz dobre produkty.

Nie idealne.

Ale wystarczająco dobre.

Jak wiele prawdziwych projektów.

Mniejsze lub Równe

Znajdź produkty z ceną mniejszą lub równą 25:

SELECT * FROM products
WHERE price <= 25;

To obejmuje produkty z ceną 25.

Więc Mouse pojawia się, bo jego cena to dokładnie 25.

Operator <= oznacza:

mniejsze lub równe

Małe symbole.

Duże znaczenie.

Bazy danych są tego pełne.

Różne

Znajdź produkty, które nie są w kategorii Electronics:

SELECT * FROM products
WHERE category != 'Electronics';

To zwraca produkty z innych kategorii.

Możesz też użyć:

SELECT * FROM products
WHERE category <> 'Electronics';

Zarówno !=, jak i <> mogą oznaczać różne w PostgreSQL.

W tym kursie zwykle będziemy używać !=, bo jest łatwe do czytania.

PostgreSQL daje opcje.

Czasami zbyt wiele.

Ale te są w porządku.

AND

AND łączy warunki.

Wszystkie warunki muszą być prawdziwe.

Przykład:

SELECT * FROM products
WHERE category = 'Electronics'
AND price < 100;

To oznacza:

Daj mi produkty, gdzie kategoria to Electronics i cena jest mniejsza niż 100.

Oba warunki muszą być prawdziwe.

Więc możesz dostać:

• Mouse;
• Keyboard;
• USB Cable.

Ale nie Laptop.

Laptop to Electronics, tak.

Ale Laptop nie jest tańszy niż 100.

Laptop wybrał kosztowny styl życia.

Więcej Przykładów z AND

Znajdź dostępne produkty Electronics:

SELECT * FROM products
WHERE category = 'Electronics'
AND available = true;

Znajdź produkty Furniture z ratingiem 5:

SELECT * FROM products
WHERE category = 'Furniture'
AND rating = 5;

Znajdź produkty tańsze niż 100 i z ratingiem przynajmniej 4:

SELECT * FROM products
WHERE price < 100
AND rating >= 4;

AND jest surowe.

Każdy warunek musi przejść.

Jak bardzo poważna checklista.

Żaden warunek nie zostaje z tyłu.

OR

OR łączy warunki, gdzie przynajmniej jeden warunek musi być prawdziwy.

Przykład:

SELECT * FROM products
WHERE category = 'Office'
OR category = 'Furniture';

To oznacza:

Daj mi produkty, gdzie kategoria to Office albo Furniture.

Produkt może spełniać jeden z tych warunków.

Nie musi spełniać obu.

Bo coś zwykle nie może być jednocześnie Office i Furniture.

Chyba że twoje krzesło biurowe ma kryzys tożsamości.

Więcej Przykładów z OR

Znajdź produkty bardzo tanie albo wysoko ocenione:

SELECT * FROM products
WHERE price < 10
OR rating = 5;

To zwraca produkty, które spełniają przynajmniej jeden warunek.

Więc produkt pojawia się, jeśli:

• jego cena jest mniejsza niż 10;
• albo jego rating to 5;
• albo oba naraz.

OR jest bardziej elastyczne niż AND.

Czasem zbyt elastyczne.

Używaj ostrożnie.

AND i OR Razem

Możesz łączyć AND i OR.

Ale musisz uważać.

Przykład:

SELECT * FROM products
WHERE category = 'Electronics'
AND price < 100
OR rating = 5;

To może nie znaczyć tego, co myślisz.

PostgreSQL wykonuje AND przed OR.

Więc zapytanie jest interpretowane tak:

SELECT * FROM products
WHERE (category = 'Electronics' AND price < 100)
OR rating = 5;

To oznacza:

Electronics tańsze niż 100, albo cokolwiek z ratingiem 5.

Jeśli chcesz inne znaczenie, użyj nawiasów.

Nawiasy pokazują twoją intencję jasno.

Twój przyszły ty ci podziękuje.

Może kawą.

Nawiasy

Używaj nawiasów, żeby grupować warunki.

Przykład:

SELECT * FROM products
WHERE category = 'Electronics'
AND (price < 100 OR rating = 5);

To oznacza:

Daj mi produkty Electronics, gdzie cena jest mniejsza niż 100 albo rating to 5.

Teraz produkt musi być Electronics.

Potem musi też spełnić jeden z warunków w nawiasach.

Nawiasy nie są dekoracją.

Są logicznym ogrodzeniem.

Używaj ich, kiedy mieszasz AND i OR.

Inaczej zapytanie może stać się dzikim zwierzęciem.

LIKE

LIKE szuka wzorców tekstowych.

Przykład:

SELECT * FROM products
WHERE name LIKE 'Desk%';

To znajduje nazwy zaczynające się od Desk.

Symbol % oznacza:

tutaj może być cokolwiek

Więc Desk% pasuje do:

• Desk Chair;
• Desk Lamp.

Inny przykład:

SELECT * FROM products
WHERE name LIKE '%book%';

To znajduje nazwy, które zawierają book.

Może pasować do:

• Notebook;
• Bookshelf.

Ale jest coś ważnego.

LIKE często rozróżnia wielkość liter.

Więc book może nie znaleźć Book.

PostgreSQL ma inną opcję.

Nazywa się ILIKE.

ILIKE

ILIKE jest jak LIKE, ale nie rozróżnia wielkości liter.

Przykład:

SELECT * FROM products
WHERE name ILIKE '%book%';

To może znaleźć:

• Notebook;
• Bookshelf;
• BOOKSHELF;
• notebook.

Bardzo przydatne.

Szczególnie gdy użytkownicy wpisują tekst jak ludzie.

Ludzie nie są konsekwentni.

Czasem piszą laptop.

Czasem Laptop.

Czasem LAPTOP.

Czasem lapotp.

To ostatnie nie jest winą PostgreSQL.

ORDER BY

ORDER BY sortuje wyniki.

Sortowanie po cenie od najniższej do najwyższej:

SELECT * FROM products
ORDER BY price;

To samo co:

SELECT * FROM products
ORDER BY price ASC;

Sortowanie po cenie od najwyższej do najniższej:

SELECT * FROM products
ORDER BY price DESC;

Sortowanie po nazwie alfabetycznie:

SELECT * FROM products
ORDER BY name;

Sortowanie sprawia, że dane łatwiej czytać.

Bez sortowania wiersze przychodzą w takiej kolejności, jaką PostgreSQL akurat zwróci.

Nie ufaj naturalnej kolejności.

Naturalna kolejność to nie kontrakt.

To nastrój.

ORDER BY z WHERE

Możesz filtrować i sortować jednocześnie.

Przykład:

SELECT * FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

To oznacza:

Daj mi produkty Electronics, posortowane od najdroższego do najtańszego.

Inny przykład:

SELECT name, price FROM products
WHERE available = true
ORDER BY price ASC;

To oznacza:

Daj mi dostępne produkty, posortowane po cenie od najniższej do najwyższej.

To bardzo częste w prawdziwych aplikacjach.

Najpierw filtr.

Potem sortowanie.

Potem pokazanie użytkownikowi.

Użytkownik myśli, że strona jest mądra.

A tak naprawdę SQL robi ciężką robotę.

Po cichu.

Jak zmęczony pracownik magazynu.

LIMIT

LIMIT kontroluje, ile wierszy zostanie zwróconych.

Przykład:

SELECT * FROM products
LIMIT 3;

To zwraca tylko trzy wiersze.

Znajdź trzy najdroższe produkty:

SELECT * FROM products
ORDER BY price DESC
LIMIT 3;

To bardzo przydatne dla:

• top produktów;
• najnowszych postów;
• pierwszej strony wyników;
• małych podglądów;
• dashboardów.

Bez LIMIT zapytanie może zwrócić za dużo danych.

A za dużo danych jest jak za dużo makaronu.

Na początku ekscytujące.

Potem niebezpieczne.

OFFSET

OFFSET pomija wiersze.

Przykład:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 3;

To oznacza:

Pomiń pierwsze 3 wiersze, potem zwróć 3 wiersze.

To jest przydatne dla paginacji.

Strona 1:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 0;

Strona 2:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 3;

Strona 3:

SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 6;

Paginacja to sposób, w jaki strony pokazują wyniki stronami.

Nie wszystko naraz.

Bo nikt nie chce ładować 5000 produktów tylko po to, żeby znaleźć myszkę.

Może poza bazą danych.

Baza danych nie narzeka.

Ale użytkownicy tak.

Łączenie Wszystkiego

Teraz połączmy filtrowanie, sortowanie i limit.

Przykład:

SELECT name, category, price, rating
FROM products
WHERE available = true
AND price >= 10
ORDER BY rating DESC, price ASC
LIMIT 5;

To oznacza:

Daj mi dostępne produkty z ceną przynajmniej 10.
Pokaż tylko name, category, price i rating.
Sortuj po ratingu od najwyższego do najniższego.
Jeśli ratingi są równe, sortuj po cenie od najniższej do najwyższej.
Zwróć tylko 5 wierszy.

To jest prawdziwe zapytanie.

Nie zabawkowa teoria.

To dokładnie ten typ rzeczy, który prawdziwe aplikacje robią cały czas.

Szukaj.

Filtruj.

Sortuj.

Ogranicz.

Pokaż.

Powtórz.

Web development często jest właśnie tym, tylko z ładniejszymi przyciskami.

Typowe Błędy

Zapominanie Cudzysłowów dla Tekstu

Źle:

SELECT * FROM products
WHERE category = Electronics;

Poprawnie:

SELECT * FROM products
WHERE category = 'Electronics';

Wartości tekstowe potrzebują pojedynczych cudzysłowów.

Nazwy kolumn nie.

PostgreSQL nie będzie zgadywać.

Znowu.

Ma granice.

Dobrze dla PostgreSQL.

Mylenie AND i OR

To:

WHERE category = 'Office'
OR category = 'Furniture'

oznacza jedno albo drugie.

To:

WHERE category = 'Office'
AND category = 'Furniture'

zwykle nic nie zwraca.

Bo jeden produkt zwykle nie może mieć obu kategorii jednocześnie.

AND oznacza wszystkie warunki.

OR oznacza przynajmniej jeden warunek.

Małe słowa.

Duże konsekwencje.

Jak “tak” i “nie” w umowach.

Zapominanie Nawiasów

Niebezpieczne:

WHERE category = 'Electronics'
AND price < 100
OR rating = 5

Lepiej:

WHERE category = 'Electronics'
AND (price < 100 OR rating = 5)

Nawiasy czynią logikę jasną.

Jasna logika oznacza mniej niespodzianek.

Mniej niespodzianek oznacza mniej krzyczenia na terminal.

Zdrowo.

Używanie LIKE, Kiedy Potrzebujesz ILIKE

To może pominąć wyniki przez wielkość liter:

WHERE name LIKE '%book%'

To często lepsze dla wyszukiwania użytkownika:

WHERE name ILIKE '%book%'

Użytkowników nie obchodzi twoja case sensitivity.

Oni chcą wyniki.

PostgreSQL może pomóc.

Pozwól mu.

Praktyka

Napisz zapytania dla tabeli products.

Znajdź wszystkie produkty Electronics:

SELECT * FROM products
WHERE category = 'Electronics';

Znajdź produkty tańsze niż 50:

SELECT * FROM products
WHERE price < 50;

Znajdź dostępne produkty z ratingiem przynajmniej 4:

SELECT * FROM products
WHERE available = true
AND rating >= 4;

Znajdź produkty z nazwą zawierającą desk:

SELECT * FROM products
WHERE name ILIKE '%desk%';

Znajdź trzy najdroższe produkty:

SELECT * FROM products
ORDER BY price DESC
LIMIT 3;

Uruchom każde zapytanie.

Zmieniaj wartości.

Psuj rzeczy.

Naprawiaj rzeczy.

Tak SQL staje się normalny.

Mini Wyzwanie

Stwórz tabelę o nazwie students.

Powinna mieć:

• id;
• name;
• city;
• age;
• active;
• score.

Wstaw przynajmniej ośmiu studentów.

Potem napisz zapytania, które:

• pokazują wszystkich studentów;
• pokazują studentów z jednego miasta;
• pokazują studentów starszych niż 18;
• pokazują aktywnych studentów;
• pokazują studentów ze score większym lub równym 80;
• pokazują studentów z jednego miasta i aktywnych;
• pokazują studentów, których imię zawiera an;
• sortują studentów po score od najwyższego do najniższego;
• pokazują tylko trzech najlepszych studentów po score.

To wyzwanie jest ważne.

Bo filtrowanie i sortowanie są wszędzie.

Jeśli umiesz pisać takie zapytania, nie tylko zapisujesz dane.

Ty kontrolujesz dane.

Ostrożnie.

Mam nadzieję.

Podsumowanie

Dzisiaj nauczyłeś się:

• WHERE filtruje wiersze;
• = sprawdza równość;
• != sprawdza różność;
• > sprawdza większe niż;
• < sprawdza mniejsze niż;
• >= sprawdza większe lub równe;
• <= sprawdza mniejsze lub równe;
• AND wymaga, żeby wszystkie warunki były prawdziwe;
• OR wymaga, żeby przynajmniej jeden warunek był prawdziwy;
• nawiasy czynią złożoną logikę jasną;
• LIKE szuka wzorców tekstowych;
• ILIKE szuka wzorców tekstowych bez rozróżniania wielkości liter;
• ORDER BY sortuje wyniki;
• ASC sortuje rosnąco;
• DESC sortuje malejąco;
• LIMIT ogranicza zwracane wiersze;
• OFFSET pomija wiersze.

To duży krok.

Teraz możesz zadawać PostgreSQL precyzyjne pytania.

Nie tylko:

SELECT * FROM products;

Ale:

SELECT name, price
FROM products
WHERE available = true
AND price < 100
ORDER BY price ASC
LIMIT 5;

To jest prawdziwa moc bazy danych.

Małe zapytanie.

Duża użyteczność.

Bardzo PostgreSQL.

Następna Lekcja

W następnej lekcji nauczymy się relacji z primary keys i foreign keys.

Tutaj tabele zaczynają ze sobą rozmawiać.

Studenci i kursy.

Użytkownicy i zamówienia.

Produkty i kategorie.

Jedna tabela jest użyteczna.

Połączone tabele to miejsce, gdzie relacyjne bazy danych stają się potężne.

I trochę bardziej dramatyczne.