Фильтрация и Сортировка Данных

С возвращением.

В предыдущем уроке ты изучил типы данных и ограничения.

Ты узнал, что PostgreSQL не должен принимать ерунду.

Никаких банановых возрастов.

Никаких отрицательных цен.

Никаких дублированных email.

Никаких загадочных пустых пользователей, которые заходят в базу данных так, будто это их квартира.

Очень хорошо.

Сегодня мы научимся находить именно те данные, которые нам нужны.

Потому что когда таблица имеет много строк, этого уже недостаточно:

SELECT * FROM products;

Это возвращает всё.

А иногда тебе не нужно всё.

Тебе нужно:

• товары дешевле 100;
• пользователи из одного города;
• активные customers;
• книги, опубликованные после определённого года;
• курсы, которые содержат слово JavaScript;
• самые дорогие товары;
• только первые пять результатов.

Именно здесь фильтрация и сортировка становятся важными.

База данных полезна не только потому, что она хранит данные.

База данных полезна потому, что помогает найти правильные данные.

Иначе это просто очень дорогой ящик.

Что Ты Изучишь

В этом уроке ты изучишь:

• как фильтровать строки через WHERE;
• как работают операторы сравнения;
• как использовать =;
• как использовать !=;
• как использовать >;
• как использовать <;
• как использовать >=;
• как использовать <=;
• как объединять условия через AND;
• как объединять условия через OR;
• как искать текст через LIKE;
• как искать текст без учёта регистра через ILIKE;
• как сортировать данные через ORDER BY;
• как ограничивать результаты через LIMIT;
• как пропускать результаты через OFFSET;
• как избегать типичных ошибок фильтрации.

В конце этого урока ты сможешь задавать 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)
);

Эта таблица имеет:

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

Теперь вставь данные:

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.

Ты должен увидеть товары типа:

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

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;

Это полезно для значений типа:

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

Не храни такие данные как текст:

yes
no
maybe

Используй BOOLEAN.

Твои запросы будут чище.

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

Все выигрывают.

Почти.

Операторы Сравнения

PostgreSQL позволяет сравнивать значения.

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

=   равно
!=  не равно
>   больше чем
<   меньше чем
>=  больше или равно
<=  меньше или равно

Важно для MDX:

Когда пишешь операторы типа <, <=, > и >= в обычном тексте, клади их в backticks.

Иначе MDX может подумать, что ты пытаешься писать JSX.

А потом может эмоционально взорваться.

SQL-блоки безопасны.

Markdown-текст — опасный район.

Больше Чем

Найди товары с ценой больше 100:

SELECT * FROM products
WHERE price > 100;

Это возвращает товары типа:

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

Это полезно, когда ты хочешь найти дорогие товары.

Или когда хочешь понять, почему кошелёк плачет.

Меньше Чем

Найди товары с ценой меньше 50:

SELECT * FROM products
WHERE price < 50;

Это возвращает более дешёвые товары, например:

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

Это полезно для скидок, бюджета, фильтров и моментов, когда жизнь говорит:

Возможно, не 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.

Оба условия должны быть истинными.

Поэтому можно получить:

• Mouse;
• Keyboard;
• USB Cable.

Но не 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;

Это возвращает товары, которые соответствуют хотя бы одному условию.

То есть товар появится, если:

• его цена меньше 10;
• или его 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% подходит для:

• Desk Chair;
• Desk Lamp.

Другой пример:

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

Это находит названия, которые содержат book.

Может найти:

• Notebook;
• Bookshelf.

Но есть важный момент.

LIKE часто чувствителен к регистру.

Поэтому book может не найти Book.

У PostgreSQL есть другой вариант.

Он называется ILIKE.

ILIKE

ILIKE — это как LIKE, но без учёта регистра.

Пример:

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

Это может найти:

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

Очень полезно.

Особенно когда 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;

Это очень полезно для:

• top товаров;
• последних posts;
• первой страницы результатов;
• маленьких preview;
• dashboard-ов.

Без 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.

Она должна иметь:

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

Вставь минимум восемь студентов.

Потом напиши запросы, которые:

• показывают всех студентов;
• показывают студентов из одного города;
• показывают студентов старше 18;
• показывают активных студентов;
• показывают студентов со score больше или равным 80;
• показывают студентов из одного города и активных;
• показывают студентов, чьё имя содержит an;
• сортируют студентов по score от самого высокого к самому низкому;
• показывают только трёх лучших студентов по score.

Этот challenge важен.

Потому что фильтрация и сортировка везде.

Если ты можешь писать такие запросы, ты уже не просто хранишь данные.

Ты контролируешь данные.

Осторожно.

Надеюсь.

Итог

Сегодня ты изучил:

• WHERE фильтрует строки;
• = проверяет равенство;
• != проверяет неравенство;
• > проверяет “больше чем”;
• < проверяет “меньше чем”;
• >= проверяет “больше или равно”;
• <= проверяет “меньше или равно”;
• AND требует, чтобы все условия были истинными;
• OR требует, чтобы хотя бы одно условие было истинным;
• скобки делают сложную логику понятной;
• LIKE ищет текстовые шаблоны;
• ILIKE ищет текстовые шаблоны без учёта регистра;
• ORDER BY сортирует результаты;
• ASC сортирует по возрастанию;
• DESC сортирует по убыванию;
• LIMIT ограничивает количество возвращённых строк;
• OFFSET пропускает строки.

Это большой шаг.

Теперь ты можешь задавать 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.

Одна таблица полезна.

Связанные таблицы — это место, где реляционные базы данных становятся мощными.

И немного более драматичными.