InnoMarts logo
InnoMarts
Fast websites
← Back to course

Фільтрація і Сортування Даних

Фільтрація і Сортування Даних

Вітаю знову.

У попередній лекції ти вивчив типи даних і обмеження.

Ти дізнався, що PostgreSQL не повинен приймати дурниці.

Жодних бананових віків.

Жодних негативних цін.

Жодних дубльованих email.

Жодних загадкових порожніх користувачів, які заходять у базу даних так, ніби це їхня квартира.

Дуже добре.

Сьогодні ми навчимося знаходити саме ті дані, які нам потрібні.

Бо коли таблиця має багато рядків, цього вже недостатньо:

SELECT * FROM products;

Це повертає все.

А іноді тобі не потрібно все.

Тобі потрібно:

Саме тут фільтрація і сортування стають важливими.

База даних корисна не тільки тому, що вона зберігає дані.

База даних корисна тому, що допомагає знайти правильні дані.

Інакше це просто дуже дорога шухляда.

Що Ти Вивчиш

У цій лекції ти вивчиш:

Наприкінці цієї лекції ти зможеш ставити PostgreSQL більш точні питання.

Не:

Дай мені все.

А:

Дай мені активних customers з Мілана, посортованих за іменем, але тільки перших 10.

Набагато краще.

Набагато професійніше.

Менше крику на базу даних.

Підготуй Базу Даних

Відкрий PostgreSQL:

sudo -iu postgres psql

Підключись до бази даних:

\c learning_postgresql

Якщо цієї бази немає, створи її:

CREATE DATABASE learning_postgresql;

Потім підключись:

\c learning_postgresql

Тепер створи таблицю для цієї лекції.

Ми використаємо products.

Спочатку видали стару таблицю, якщо вона існує:

DROP TABLE IF EXISTS products;

Тепер створи її:

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)
);

Ця таблиця має:

Тепер встав дані:

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);

Перевір дані:

SELECT * FROM products;

Тепер у нас достатньо даних для практики.

Не мільйони рядків.

Але достатньо, щоб PostgreSQL трохи розімʼявся.

Як database yoga.

WHERE

WHERE фільтрує рядки.

Без WHERE PostgreSQL повертає всі рядки:

SELECT * FROM products;

З WHERE PostgreSQL повертає тільки рядки, які відповідають умові:

SELECT * FROM products
WHERE category = 'Electronics';

Це означає:

Дай мені тільки товари, де категорія — Electronics.

Ти маєш побачити товари типу:

WHERE — одна з найважливіших частин SQL.

Він допомагає ставити конкретні питання.

А конкретні питання дають корисні відповіді.

Дуже життєвий урок.

І дуже SQL.

Фільтрація за Текстом

Щоб фільтрувати за текстом, використовуй одинарні лапки.

Приклад:

SELECT * FROM products
WHERE category = 'Office';

Це повертає товари з категорії Office.

Інший приклад:

SELECT * FROM products
WHERE name = 'Laptop';

Це повертає товар з назвою Laptop.

Важливо:

Текстові значення потребують одинарних лапок.

Правильно:

WHERE name = 'Laptop'

Неправильно:

WHERE name = Laptop

Без лапок PostgreSQL думає, що Laptop — це назва колонки.

PostgreSQL не вгадує.

Це база даних.

Не читач думок.

Фільтрація за Boolean

Boolean-колонки зберігають true або false.

Приклад:

SELECT * FROM products
WHERE available = true;

Це повертає доступні товари.

Можна також знайти недоступні товари:

SELECT * FROM products
WHERE available = false;

Це корисно для значень типу:

Не зберігай такі дані як текст:

yes
no
maybe

Використовуй BOOLEAN.

Твої запити будуть чистішими.

База даних буде менше протестувати.

Усі виграють.

Майже.

Оператори Порівняння

PostgreSQL дозволяє порівнювати значення.

Популярні оператори порівняння:

=   дорівнює
!=  не дорівнює
>   більше ніж
<   менше ніж
>=  більше або дорівнює
<=  менше або дорівнює

Важливо для MDX:

Коли пишеш оператори типу <, <=, > і >= у звичайному тексті, клади їх у backticks.

Інакше MDX може подумати, що ти намагаєшся писати JSX.

А потім може емоційно вибухнути.

SQL-блоки безпечні.

Markdown-текст — небезпечний район.

Більше Ніж

Знайди товари з ціною більшою за 100:

SELECT * FROM products
WHERE price > 100;

Це повертає товари типу:

Це корисно, коли ти хочеш знайти дорогі товари.

Або коли хочеш зрозуміти, чому гаманець плаче.

Менше Ніж

Знайди товари з ціною меншою за 50:

SELECT * FROM products
WHERE price < 50;

Це повертає дешевші товари, наприклад:

Це корисно для знижок, бюджету, фільтрів і моментів, коли життя каже:

Можливо, не laptop за 900 euro сьогодні.

Сумно.

Але фінансово відповідально.

Більше або Дорівнює

Знайди товари з rating більшим або рівним 4:

SELECT * FROM products
WHERE rating >= 4;

Це повертає товари з rating 4 і 5.

Оператор >= означає:

більше або дорівнює

Тому 4 включено.

Це корисно, коли ти хочеш хороші товари.

Не ідеальні.

Але достатньо хороші.

Як багато реальних проєктів.

Менше або Дорівнює

Знайди товари з ціною меншою або рівною 25:

SELECT * FROM products
WHERE price <= 25;

Це включає товари з ціною 25.

Тому Mouse зʼявляється, бо його ціна точно 25.

Оператор <= означає:

менше або дорівнює

Маленькі символи.

Велике значення.

Бази даних повні такого.

Не Дорівнює

Знайди товари, які не належать до категорії Electronics:

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

Це повертає товари з інших категорій.

Можна також використати:

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

І !=, і <> можуть означати “не дорівнює” у PostgreSQL.

У цьому курсі ми зазвичай будемо використовувати !=, бо його легко читати.

PostgreSQL дає варіанти.

Іноді забагато.

Але ці нормальні.

AND

AND поєднує умови.

Усі умови мають бути правдивими.

Приклад:

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

Це означає:

Дай мені товари, де категорія Electronics і ціна менша за 100.

Обидві умови мають бути правдивими.

Тому можна отримати:

Але не Laptop.

Laptop — це Electronics, так.

Але Laptop не дешевший за 100.

Laptop вибрав дорогий стиль життя.

Більше Прикладів з AND

Знайди доступні Electronics товари:

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

Знайди Furniture товари з rating 5:

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

Знайди товари дешевші за 100 і з rating щонайменше 4:

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

AND суворий.

Кожна умова має пройти.

Як дуже серйозний checklist.

Жодної умови не залишаємо позаду.

OR

OR поєднує умови, де хоча б одна умова має бути правдивою.

Приклад:

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

Це означає:

Дай мені товари, де категорія Office або Furniture.

Товар може відповідати одній з цих умов.

Не обовʼязково обом.

Бо щось зазвичай не може бути одночасно Office і Furniture.

Хіба що твоє офісне крісло має кризу ідентичності.

Більше Прикладів з OR

Знайди товари, які дуже дешеві або мають високий rating:

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

Це повертає товари, які відповідають хоча б одній умові.

Тобто товар зʼявиться, якщо:

OR гнучкіший за AND.

Іноді занадто гнучкий.

Використовуй обережно.

AND і OR Разом

Можна поєднувати AND і OR.

Але треба бути уважним.

Приклад:

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

Це може означати не те, що ти думаєш.

PostgreSQL виконує AND перед OR.

Тому запит інтерпретується так:

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

Це означає:

Electronics дешевші за 100 або будь-що з rating 5.

Якщо хочеш інше значення, використовуй дужки.

Дужки роблять твою логіку зрозумілою.

Твій майбутній ти подякує.

Можливо, кавою.

Дужки

Використовуй дужки, щоб групувати умови.

Приклад:

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

Це означає:

Дай мені Electronics товари, де ціна менша за 100 або rating дорівнює 5.

Тепер товар має бути Electronics.

Потім він також має відповідати одній з умов у дужках.

Дужки — це не декорація.

Це логічний паркан.

Використовуй їх, коли змішуєш AND і OR.

Інакше запит може стати диким звіром.

LIKE

LIKE шукає текстові шаблони.

Приклад:

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

Це знаходить назви, які починаються з Desk.

Символ % означає:

тут може бути що завгодно

Тому Desk% підходить для:

Інший приклад:

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

Це знаходить назви, які містять book.

Може знайти:

Але є важливий момент.

LIKE часто чутливий до регістру.

Тому book може не знайти Book.

PostgreSQL має інший варіант.

Він називається ILIKE.

ILIKE

ILIKE — це як LIKE, але без урахування регістру.

Приклад:

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

Це може знайти:

Дуже корисно.

Особливо коли users вводять текст як люди.

Люди не послідовні.

Іноді пишуть laptop.

Іноді Laptop.

Іноді LAPTOP.

Іноді lapotp.

Останнє — не вина PostgreSQL.

ORDER BY

ORDER BY сортує результати.

Сортування за ціною від найнижчої до найвищої:

SELECT * FROM products
ORDER BY price;

Це те саме, що:

SELECT * FROM products
ORDER BY price ASC;

Сортування за ціною від найвищої до найнижчої:

SELECT * FROM products
ORDER BY price DESC;

Сортування за назвою в алфавітному порядку:

SELECT * FROM products
ORDER BY name;

Сортування робить дані легшими для читання.

Без сортування рядки приходять у тому порядку, в якому PostgreSQL вирішить їх повернути.

Не довіряй natural order.

Natural order — це не контракт.

Це настрій.

ORDER BY з WHERE

Можна фільтрувати й сортувати одночасно.

Приклад:

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

Це означає:

Дай мені Electronics товари, посортовані від найдорожчих до найдешевших.

Інший приклад:

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

Це означає:

Дай мені доступні товари, посортовані за ціною від найнижчої до найвищої.

Це дуже часто використовується в реальних застосунках.

Спочатку фільтр.

Потім сортування.

Потім показ користувачу.

Користувач думає, що сайт розумний.

Насправді SQL робить важку роботу.

Тихо.

Як втомлений працівник складу.

LIMIT

LIMIT контролює, скільки рядків буде повернуто.

Приклад:

SELECT * FROM products
LIMIT 3;

Це повертає тільки три рядки.

Знайди три найдорожчі товари:

SELECT * FROM products
ORDER BY price DESC
LIMIT 3;

Це дуже корисно для:

Без LIMIT запит може повернути занадто багато даних.

А занадто багато даних — як занадто багато пасти.

Спочатку цікаво.

Потім небезпечно.

OFFSET

OFFSET пропускає рядки.

Приклад:

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

Це означає:

Пропусти перші 3 рядки, потім поверни 3 рядки.

Це корисно для pagination.

Сторінка 1:

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

Сторінка 2:

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

Сторінка 3:

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

Pagination — це спосіб, яким сайти показують результати сторінками.

Не все одразу.

Бо ніхто не хоче завантажувати 5000 товарів, щоб знайти мишку.

Можливо, тільки база даних.

База даних не скаржиться.

Але users будуть.

Поєднання Всього

Тепер поєднаємо фільтрацію, сортування і limit.

Приклад:

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

Це означає:

Дай мені доступні товари з ціною щонайменше 10.
Покажи тільки name, category, price і rating.
Сортуй за rating від найвищого до найнижчого.
Якщо rating однаковий, сортуй за price від найнижчого до найвищого.
Поверни тільки 5 рядків.

Це справжній запит.

Не іграшкова теорія.

Саме такі речі реальні застосунки роблять постійно.

Шукай.

Фільтруй.

Сортуй.

Обмежуй.

Показуй.

Повторюй.

Web development часто саме це, тільки з красивішими кнопками.

Типові Помилки

Забути Лапки для Тексту

Неправильно:

SELECT * FROM products
WHERE category = Electronics;

Правильно:

SELECT * FROM products
WHERE category = 'Electronics';

Текстові значення потребують одинарних лапок.

Назви колонок — ні.

PostgreSQL не буде вгадувати.

Знову.

Він має межі.

Добре для PostgreSQL.

Плутати AND і OR

Це:

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

означає одне або інше.

Це:

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

зазвичай нічого не повертає.

Бо один товар зазвичай не може мати обидві категорії одночасно.

AND означає всі умови.

OR означає хоча б одну умову.

Маленькі слова.

Великі наслідки.

Як “так” і “ні” в контрактах.

Забути Дужки

Небезпечно:

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

Краще:

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

Дужки роблять логіку зрозумілою.

Зрозуміла логіка означає менше сюрпризів.

Менше сюрпризів означає менше крику на terminal.

Здорово.

Використовувати LIKE, Коли Потрібен ILIKE

Це може пропустити результати через регістр:

WHERE name LIKE '%book%'

Це часто краще для user search:

WHERE name ILIKE '%book%'

Користувачів не цікавить твоя case sensitivity.

Вони хочуть результати.

PostgreSQL може допомогти.

Дозволь йому.

Практика

Напиши запити для таблиці products.

Знайди всі Electronics товари:

SELECT * FROM products
WHERE category = 'Electronics';

Знайди товари дешевші за 50:

SELECT * FROM products
WHERE price < 50;

Знайди доступні товари з rating щонайменше 4:

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

Знайди товари, назва яких містить desk:

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

Знайди три найдорожчі товари:

SELECT * FROM products
ORDER BY price DESC
LIMIT 3;

Запусти кожен запит.

Змінюй значення.

Ламай речі.

Виправляй речі.

Так SQL стає нормальним.

Мінічелендж

Створи таблицю з назвою students.

Вона має мати:

Встав щонайменше вісім студентів.

Потім напиши запити, які:

Цей challenge важливий.

Бо фільтрація і сортування всюди.

Якщо ти можеш писати такі запити, ти вже не просто зберігаєш дані.

Ти контролюєш дані.

Обережно.

Сподіваюсь.

Підсумок

Сьогодні ти вивчив:

Це великий крок.

Тепер ти можеш ставити PostgreSQL точні питання.

Не тільки:

SELECT * FROM products;

А:

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

Це справжня сила бази даних.

Маленький запит.

Велика користь.

Дуже PostgreSQL.

Наступна Лекція

У наступній лекції ми вивчимо звʼязки через primary keys і foreign keys.

Саме тут таблиці починають говорити одна з одною.

Students і courses.

Users і orders.

Products і categories.

Одна таблиця корисна.

Повʼязані таблиці — це місце, де реляційні бази даних стають потужними.

І трохи драматичнішими.