SQL и PostgreSQL: Полный курс для начинающих

Основы SQL: выборка SELECT, фильтрация и сортировка данных

SQL (Structured Query Language) — это язык структурированных запросов, который используется для взаимодействия с реляционными базами данных. В отличие от процедурных языков программирования (Python, Java, C++), где вы описываете как получить результат (шаг за шагом), SQL является декларативным языком. Это означает, что вы описываете что вы хотите получить, а система управления базами данных (СУБД), такая как PostgreSQL, сама решает, как выполнить этот запрос наиболее эффективным способом.

Любая работа с данными начинается с их извлечения. В этой статье мы разберем фундамент SQL: как выбирать данные, как отсекать ненужное и как упорядочивать результаты.

Анатомия таблицы

Прежде чем писать запросы, важно понимать структуру хранения данных. В реляционных базах данных информация хранится в таблицах.

!Структура таблицы: столбцы определяют тип данных, строки содержат конкретные записи

* Таблица (Table): Набор данных, объединенных общим смыслом (например, employees — сотрудники). * Столбец (Column/Field): Атрибут, описывающий данные (например, salary — зарплата). Каждый столбец имеет строго определенный тип данных (число, текст, дата). * Строка (Row/Record): Единичная запись в таблице (конкретный сотрудник).

Оператор SELECT: Выборка данных

Ключевое слово SELECT указывает базе данных, какие именно столбцы вы хотите увидеть в результате. Ключевое слово FROM указывает, из какой таблицы брать эти данные.

Выборка всех столбцов

Самый простой запрос — получить абсолютно все данные из таблицы. Для этого используется символ звездочки *.

Этот запрос вернет все строки и все столбцы таблицы employees. Использовать SELECT * в реальных приложениях не рекомендуется, так как это создает избыточную нагрузку на сеть и базу данных, если таблица содержит много столбцов или «тяжелые» данные (например, большие тексты).

Выборка конкретных столбцов

Хорошей практикой считается явное перечисление необходимых столбцов. Это делает запрос быстрее и предсказуемее.

Результат будет содержать только три указанных поля для всех сотрудников.

Использование псевдонимов (Aliases)

Иногда названия столбцов в базе данных могут быть техническими или непонятными (например, emp_f_name). С помощью ключевого слова AS можно дать столбцу временное имя (псевдоним) для вывода.

Псевдонимы существуют только на момент выполнения запроса и не меняют реальные имена столбцов в базе данных.

Уникальные значения (DISTINCT)

Если вам нужно узнать, какие уникальные значения содержатся в столбце (например, список всех отделов, где есть сотрудники), используйте DISTINCT.

Если в таблице 100 сотрудников, но все они работают только в 3 отделах, запрос вернет только 3 строки.

Арифметические операции

В SELECT можно выполнять математические вычисления. Например, если мы хотим узнать годовую зарплату, не обязательно хранить её в базе — можно вычислить её на лету.

Поддерживаются стандартные операторы: сложение +, вычитание -, умножение *, деление / и остаток от деления %.

Фильтрация данных: Оператор WHERE

Чаще всего нам не нужна вся таблица, а только строки, соответствующие определенным критериям. Для этого используется оператор WHERE. Он ставится после FROM.

СУБД просматривает каждую строку таблицы, проверяет условие в WHERE. Если условие истинно (TRUE), строка попадает в результат. Если ложно (FALSE), строка отбрасывается.

!Принцип работы WHERE: строки, не соответствующие условию, исключаются из выборки

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

PostgreSQL поддерживает стандартные математические операторы сравнения:

| Оператор | Описание | | :--- | :--- | | = | Равно | | <> или != | Не равно | | > | Больше | | < | Меньше | | >= | Больше или равно | | <= | Меньше или равно |

Пример: найти сотрудников с зарплатой выше 50 000.

Логические операторы (AND, OR, NOT)

Условия можно комбинировать.

* AND (И): Строка выбирается, только если оба условия истинны. * OR (ИЛИ): Строка выбирается, если хотя бы одно из условий истинно. * NOT (НЕ): Инвертирует условие.

Пример: найти сотрудников из отдела 'IT', у которых зарплата больше 60 000.

Важно: Приоритет AND выше, чем OR. Если вы смешиваете эти операторы, всегда используйте скобки для явного указания порядка выполнения, чтобы избежать логических ошибок.

Специальные операторы фильтрации

#### BETWEEN

Используется для проверки диапазона значений (включительно).

Это эквивалентно записи salary >= 40000 AND salary <= 60000.

#### IN

Проверяет, входит ли значение в заданный список.

Это намного компактнее, чем писать цепочку из OR.

#### LIKE

Используется для поиска по шаблону в строках. Работает с двумя спецсимволами: * % — любая последовательность символов (любой длины). * _ — ровно один любой символ.

Пример: найти всех сотрудников, чья фамилия начинается на "S".

Пример: найти сотрудников, у которых вторая буква в имени "a".

В PostgreSQL оператор LIKE чувствителен к регистру. Для поиска без учета регистра используется ILIKE.

#### IS NULL

В базах данных существует специальное значение NULL, которое означает «отсутствие значения» или «неизвестно». NULL не равен нулю, пустой строке или даже самому себе.

Ошибка: WHERE bonus = NULL (никогда ничего не вернет). Правильно: WHERE bonus IS NULL.

Пример: найти сотрудников, у которых не указан бонус.

Для поиска заполненных полей используется IS NOT NULL.

Сортировка данных: Оператор ORDER BY

Порядок строк в результате SQL-запроса не гарантирован, если вы явно не попросили их отсортировать. Для этого используется ORDER BY.

Направление сортировки

* ASC (Ascending) — по возрастанию (от А до Я, от 0 до 9). Это значение по умолчанию. * DESC (Descending) — по убыванию (от Я до А, от 9 до 0).

Пример: отсортировать сотрудников по зарплате от самой высокой к самой низкой.

Сортировка по нескольким столбцам

Можно сортировать данные по нескольким критериям последовательно. Сначала строки сортируются по первому указанному столбцу. Если значения в первом столбце совпадают, сортировка внутри этой группы идет по второму столбцу.

В этом примере сначала все записи будут сгруппированы по отделам в алфавитном порядке. Внутри каждого отдела сотрудники будут выстроены по убыванию зарплаты.

Ограничение количества строк: LIMIT и OFFSET

Иногда таблица содержит миллионы строк, а нам нужно посмотреть только топ-10 записей или реализовать постраничную навигацию (пагинацию).

* LIMIT N — возвращает только первые N строк результата. * OFFSET M — пропускает первые M строк перед возвратом результата.

Пример: найти 5 сотрудников с самой высокой зарплатой.

Пример: получить вторую страницу результатов (если на странице по 10 записей).

Важно: Всегда используйте ORDER BY вместе с LIMIT/OFFSET, иначе набор возвращаемых строк может быть непредсказуемым при повторных запусках запроса.

Порядок выполнения запроса

Для понимания работы SQL критически важно знать, в каком порядке СУБД обрабатывает ваш запрос. Этот порядок отличается от того, как вы пишете код.

Вы пишете:

SELECT

FROM

WHERE

ORDER BY

LIMIT

СУБД выполняет:

FROM (Выбор таблицы)

WHERE (Фильтрация строк)

SELECT (Выбор столбцов и вычисления)

ORDER BY (Сортировка итогового результата)

LIMIT (Обрезание результата)

!Логический порядок выполнения операций в SQL

Именно поэтому вы не можете использовать псевдоним, заданный в SELECT, внутри секции WHERE. На момент работы WHERE псевдоним еще не создан.

Ошибка:

Правильно:

Итоги

SELECT и FROM — основа любого запроса. SELECT определяет столбцы (проекцию), а FROM — источник данных (таблицу). Избегайте SELECT * в продакшене.

WHERE — мощный инструмент фильтрации, который работает построчно. Используйте операторы сравнения, логические связки (AND, OR) и специальные операторы (IN, BETWEEN, LIKE).

NULL — это не ноль и не пустая строка. Для проверки на пустоту всегда используйте конструкцию IS NULL или IS NOT NULL.

ORDER BY — единственный способ гарантировать порядок вывода строк. По умолчанию сортирует по возрастанию (ASC), для убывания используйте DESC.

Порядок выполнения отличается от порядка написания. Фильтрация (WHERE) происходит до вычисления результатов (SELECT), поэтому псевдонимы столбцов недоступны в условиях фильтрации.

Анализ данных: агрегатные функции, группировка и встроенные функции

В предыдущей статье мы научились выбирать данные, фильтровать их и сортировать. Однако в реальной работе аналитика или разработчика часто требуется не просто извлечь список строк, а получить сводную информацию: «Какова средняя зарплата в отделе?», «Сколько товаров продано за последний месяц?», «Какая максимальная цена была зафиксирована?».

SQL предоставляет мощные инструменты для таких вычислений. Мы переходим от работы с отдельными строками к анализу множеств.

Агрегатные функции

Агрегатные функции принимают на вход множество значений (например, целый столбец таблицы), а возвращают одно итоговое значение. Это ключевое отличие от обычных запросов, где одной строке в таблице соответствует одна строка в результате.

!Агрегация данных: множество значений сворачивается в один итог

Основные функции

Рассмотрим 5 самых популярных функций стандарта SQL:

COUNT() — подсчитывает количество строк.

SUM() — суммирует значения.

AVG() — вычисляет среднее арифметическое.

MIN() — находит минимальное значение.

MAX() — находит максимальное значение.

Примеры использования

Предположим, у нас есть таблица products (товары) с полями price (цена) и category (категория).

1. Сколько всего товаров в таблице?

COUNT(*) — это единственный вариант функции, который считает абсолютно все строки, включая те, где есть NULL.

2. Какова общая стоимость всех товаров?

3. Какова средняя, минимальная и максимальная цена?

Особенность работы с NULL

Важно помнить: агрегатные функции (кроме COUNT(*)) игнорируют значения NULL.

Если у вас есть столбец bonus, и у половины сотрудников он не заполнен (NULL), то запрос AVG(bonus) посчитает средний бонус только среди тех, у кого он есть. Если вы хотите считать среднее по всем сотрудникам (считая NULL как 0), данные нужно предварительно обработать функцией COALESCE (о ней ниже).

Также полезно знать конструкцию COUNT(column_name). Она посчитает количество строк, где в указанном столбце значение не NULL.

Группировка данных: GROUP BY

Агрегатные функции становятся по-настоящему мощными, когда мы хотим получить статистику не по всей таблице, а в разрезе каких-то категорий. Например, найти среднюю зарплату по каждому отделу отдельно.

Для этого используется оператор GROUP BY.

Как это работает

СУБД берет все строки таблицы.

Сортирует и объединяет их в группы по уникальным значениям столбца, указанного в GROUP BY (в нашем случае — по категориям).

Для каждой группы отдельно выполняется агрегатная функция (COUNT).

!Принцип группировки: данные разделяются на корзины по значению ключа, затем агрегируются

Железное правило GROUP BY

Если вы используете GROUP BY, то в блоке SELECT могут находиться только:

Столбцы, по которым идет группировка (те, что указаны в GROUP BY).

Агрегатные функции (SUM, COUNT и т.д.).

Ошибка:

Правильно:

Фильтрация групп: HAVING

Часто новички пытаются отфильтровать результаты группировки с помощью WHERE и получают ошибку. Для фильтрации результатов агрегации используется оператор HAVING.

* WHERE фильтрует строки ДО группировки. * HAVING фильтрует группы ПОСЛЕ группировки.

Пример: Найти категории, в которых больше 10 товаров.

Если мы напишем WHERE COUNT(*) > 10, база данных выдаст ошибку, так как на момент выполнения WHERE подсчет еще не произведен.

Комбинирование WHERE и HAVING

Их можно использовать вместе. Сначала мы отсекаем ненужные строки, потом группируем, потом отсекаем ненужные группы.

Пример: Найти категории с более чем 5 товарами, но учитывать только товары дороже 1000 рублей.

Встроенные скалярные функции

Помимо агрегации, PostgreSQL имеет сотни встроенных функций для преобразования данных в каждой строке (скалярные функции).

Строковые функции

Работа с текстом — одна из самых частых задач.

* LENGTH(str) — длина строки. * UPPER(str) / LOWER(str) — приведение к верхнему или нижнему регистру. Полезно для поиска без учета регистра. * CONCAT(str1, str2) или оператор || — склеивание строк. * SUBSTRING(str, start, length) — извлечение части строки.

Пример: Вывести имя и фамилию в одном столбце и длину фамилии.

Функции даты и времени

* NOW() — текущая дата и время. * CURRENT_DATE — только текущая дата. * EXTRACT(part FROM date) — извлечение части даты (год, месяц, день, час). * AGE(date) — вычисляет интервал времени (возраст) от указанной даты до текущего момента.

Пример: Найти всех сотрудников, нанятых в 2023 году.

Математические функции и работа с NULL

* ROUND(num, decimals) — округление числа до указанного знака. * COALESCE(val1, val2, ...) — возвращает первое не NULL значение из списка. Это стандартный способ замены NULL на значение по умолчанию.

Пример: Вывести цену, а если она не указана (NULL), вывести 0.

Обновленный порядок выполнения запроса

Теперь, зная группировку, мы можем дополнить нашу картину того, как PostgreSQL выполняет запрос:

FROM / JOIN (Сбор данных из таблиц)

WHERE (Фильтрация строк)

GROUP BY (Группировка)

HAVING (Фильтрация групп)

SELECT (Вычисление значений и агрегатов)

ORDER BY (Сортировка)

LIMIT (Ограничение количества)

Это объясняет, почему в HAVING нельзя использовать псевдонимы из SELECT — на момент фильтрации групп выборка столбцов еще не произошла.

Итоги

Агрегатные функции (COUNT, SUM, AVG, MIN, MAX) позволяют получать сводную информацию из множества строк. Помните, что они игнорируют NULL (кроме COUNT(*)).

GROUP BY используется для разделения данных на логические группы перед агрегацией. Все столбцы в SELECT, которые не обернуты в агрегатные функции, обязаны быть в GROUP BY.

HAVING — это аналог WHERE, но для групп. Используйте его для фильтрации по результатам агрегатных функций (например, SUM(price) > 1000).

Скалярные функции помогают форматировать данные: склеивать строки (||), работать с датами (EXTRACT, NOW) и обрабатывать пустоты (COALESCE).