Позвольте мне немного рассказать о своём профессиональном опыте. Я работаю в сфере информационных технологий уже более 30 лет. Занимался тестированием, разработкой, но главным делом моей жизни были и остаются базы данных. Именно в этой области моя основная специализация, хотя я также неизбежно сталкивался с Java, SAS, Python, веб-разработкой (JavaScript, Node. js).

Эта книга — мой обобщённый опыт работы с базами данных на основе СУБД PostgreSQL.

В последнее время системы искусственного интеллекта получили очень широкое распространение. В создании данной книги мне помогал ИИ DeepSeek. Он сохранял структуру книги, помогал с формулировками отдельных главы. Мы проработали несколько стилей, и я выбрал наиболее понятный с точки зрения обучения.

Данную книгу можно воспринимать как учебник: если последовательно заниматься и выполнять практические задания, то можно в кратчайшие сроки стать хорошим специалистом по PostgreSQL.

При методичном, последовательном выполнении заданий вы, уважаемый читатель, быстро освоите PostgreSQL или улучшите уже имеющиеся навыки.

Книгу также можно использовать как справочник по СУБД PostgreSQL, поскольку практически все ключевые и сложные темы в ней подробно раскрыты, с понятными примерами и практическими задачами.

Если в процессе чтения у вас возникнут вопрос связаться с автором можно по адресу электронной почты atvcross@rambler.ru или через социальную сеть ВК https://vk.com/maxandmouse

Шаг 1. Что это за книга. Как работать с этой книгой. PostgreSQL

Ваш путь от первого SELECT до профессионала

ДЛЯ КОГО ЭТА КНИГА

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

— Для тех, кто переходит с другой СУБД — если вы работали с MySQL, Oracle или SQL Server и хотите освоить PostgreSQL.

— Для тех, кто хочет систематизировать знания — если вы уже пишете запросы, но чувствуете, что в знаниях есть пробелы.

ЖИВОЙ ПРИМЕР

Вы держите в руках книгу, которая не похожа на скучный учебник.

Здесь нет занудных определений и сухих лекций.

Есть живой разговор, реальные примеры и чёткий путь — 100 шагов, которые превратят вас из человека, который слышал про SQL, в специалиста, уверенно работающего с PostgreSQL.

КАК УСТРОЕНА КНИГА

Книга построена как 100 шагов.

Каждый шаг — это одна тема, один приём, одна конструкция.

Структура шага:

— Живой пример — зачем это нужно в реальной работе.

— Основной материал — кратко, без воды.

— Примеры — код с пояснениями.

— Типичные ошибки — что может пойти не так.

— Тонкости и нюансы — для тех, кто хочет глубже.

— Из опыта — личная история автора.

— Вопросы и ответы — разбор частых недоумений.

— Попробуй сам — задания для закрепления.

— Задачи на повторение — чтобы не забывать пройденное.

КАК РАБОТАТЬ С ЭТОЙ КНИГОЙ

— Идите по порядку.

— Шаги построены от простого к сложному. Пропускать их не стоит.

— Выполняйте задания.

— Теория без практики забывается. Каждый шаг содержит задания 'Попробуй сам' — делайте их.

— Не бойтесь ошибок.

— Ошибки — это нормально. В книге есть раздел 'Типичные ошибки', чтобы вы знали, на что обратить внимание.

— Возвращайтесь к примерам.

— Если что-то забыли — не листайте всё сначала. Книга построена так, что к любому шагу можно вернуться.

— Используйте демонстрационную схему.

— Все примеры в книге построены на одной схеме — 'Доставка цветов'.

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

— Старайтесь понять синтаксис.

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

— Повторяйте.

— Выполняйте задания на повторение и перечитывайте шаги, которые вы уже прошли.

— Иногда издательские системы заменяются кавычки на другие значки в запросах следует использовать именно одинарные кавычки

ЧТО НУЖНО ДЛЯ РАБОТЫ

— Компьютер с Windows, macOS или Linux.

— Установленная PostgreSQL (шаг 2).

— pgAdmin (устанавливается вместе с PostgreSQL).

— Желание разбираться и пробовать.

ОБОЗНАЧЕНИЯ В КНИГЕ

— Код — выделен моноширинным шрифтом.

— Что тут происходит — пояснение к примеру.

— Внимание! — важные предупреждения.

— опыт — личный опыт автора.

ЧТО ТАКОЕ БАЗЫ ДАННЫХ, НАЗНАЧЕНИЕ БАЗ ДАННЫХ

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

Сейчас это кажется неважным, но обычный смартфон может вместить несколько тысяч книг.

Огромное количество систем современного хранения информации работает под управлением баз данных. Спектр применения систем управления базами данных неограничен. Сейчас практически ни одна отрасль не обходится без систем баз данных. Даже такая далёкая от ИТ отрасль, как сельское хозяйство и фермерство, повсеместно автоматизируется с использованием передовых информационных разработок.

База данных — это организованная структура, предназначенная для хранения, изменения и обработки взаимосвязанной информации. Простыми словами: база данных — это электронное хранилище, где информация структурирована и к ней можно быстро получить доступ.

НАЗНАЧЕНИЕ ЯЗЫКА SQL, НЕОБХОДИМОСТЬ ИЗУЧЕНИЯ SQL

Structured Query Language (SQL) — язык структурированных запросов.

Язык запросов SQL — универсальный язык для работы с данными базы. Язык запросов SQL используется для управления массивами данных в БД, множествами.

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

Язык SQL относится к функциональным языкам программирования. Он отличается от алгоритмических языков. Основу языка составляет не алгоритм как таковой, а совокупность команд, определяющих взаимоотношения информационных множеств и подмножеств.

Следует отметить, что системы управления базами данных — СУБД — имеют различные реализации, такие как PostgreSQL, Oracle, MS SQL, MySQL.

Язык SQL в разных СУБД имеет небольшие отличия, например в детальном синтаксисе описания операторов. Такие отличия присутствуют в специальных функциях, относящихся к той или иной СУБД, но всё же в основном язык — это общий синтаксис, практически идентичный для любой СУБД.

В данном курсе мы будем рассматривать общепринятый синтаксис SQL PostgreSQL. Данная книга, как я ранее писал, обучает диалекту PostgreSQL, так как PostgreSQL — одна из самых популярных и распространённых СУБД на сегодняшний день.

Кроме того, бесплатная версия данной СУБД всегда доступна для скачивания, и её установка не представляет никакой сложности и не требует значительных ресурсов.

ИТОГО: ЧТО ТАКОЕ СУБД, POSTGRESQL И SQL

СУБД (Система управления базами данных) — это программа, которая хранит, организует и даёт доступ к данным. Примеры: PostgreSQL, MySQL, Oracle, SQL Server.

PostgreSQL — одна из самых популярных и мощных СУБД в мире. Её используют крупные компании (Сбербанк), государственные организации и стартапы. PostgreSQL — бесплатная, с открытым исходным кодом, надёжная и поддерживает самые современные возможности SQL.

SQL (Structured Query Language — язык структурированных запросов) — это язык, на котором вы 'разговариваете' с базой данных. С помощью SQL вы даёте команды: 'покажи всех клиентов', 'найди заказы за последний месяц', 'посчитай средний чек'. SQL — стандарт для всех реляционных баз данных. Выучив его один раз, вы сможете работать и с PostgreSQL, и с MySQL, и с другими СУБД.

Почему PostgreSQL?

— Бесплатный и с открытым кодом

— Соответствует стандартам SQL лучше большинства других СУБД

— Поддерживает продвинутые возможности: JSON, полнотекстовый поиск, оконные функции, свои типы данных

— Надёжный и производительный

— Большое сообщество и множество расширений

ИЗ ОПЫТА

Когда я только начинал изучать SQL, мне казалось, что это невозможно запомнить.

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

Именно так и построена эта книга: сначала вы понимаете, зачем нужна конструкция, а потом учитесь её использовать.

ВОПРОСЫ И ОТВЕТЫ

В: Нужно ли читать всё подряд?

О: Да, если вы новичок. Если вы уже знакомы с SQL, можете выборочно проходить шаги, но лучше не пропускать.

В: Можно ли пропустить установку и сразу начать с запросов?

О: Можно, но тогда вы не сможете выполнять примеры. Установка PostgreSQL и pgAdmin подробно описана в шаге 2, и она займёт не больше 15 минут.

В: Что делать, если я застрял на каком-то шаге?

О: Вернитесь к примерам, перечитайте 'Типичные ошибки'. Если не помогает — перейдите к следующему шагу и вернитесь позже.

ПОПРОБУЙ САМ

Пока ничего не нужно устанавливать и писать.

Просто откройте книгу и посмотрите, как устроены шаги.

Обратите внимание на структуру: везде повторяется один и тот же формат.

Шаг 2. Что нам понадобится. Установка БД PostgreSQL. Установка pgAdmin

Готовим рабочее место

ЖИВОЙ ПРИМЕР

Для учёбы, выполнения тестовых примеров и учебных задач вам потребуется установить СУБД PostgreSQL на локальный компьютер.

В этом шаге мы разберём установку PostgreSQL и pgAdmin — графического инструмента для работы с базами данных.

ПОДГОТОВКА К УСТАНОВКЕ

Для корректной установки вам понадобится:

— 64-разрядный компьютер или ноутбук

— оперативная память не менее 2 ГБ

— свободное место на диске не менее 2 ГБ

— на компьютере должна быть установлена операционная система Windows. Если у вас другая операционная система (macOS или Linux), то при установке необходимо это учитывать — процесс установки будет отличаться, но официальный сайт содержит инструкции для всех основных платформ.

СКАЧИВАНИЕ ДИСТРИБУТИВА

Перейдите на официальный сайт PostgreSQL:

https://www.postgresql.org/download/

На странице выберите вашу операционную систему.

Если у вас Windows, нажмите на значок Windows в верхней части страницы.

Вы попадёте на страницу загрузки для Windows:

https://www.postgresql.org/download/windows/

Нажмите Download the installer.

В открывшемся списке версий выберите последнюю (на момент написания — 18.3).

В колонке Windows x86—64 нажмите значок скачивания.

Через несколько секунд начнётся загрузка дистрибутива.

Если загрузка не началась, используйте ссылку Click me.

УСТАНОВКА СУБД POSTGRESQL

В этом разделе описывается стандартная установка PostgreSQL. Если вы забыли пароль после установки, воспользуйтесь инструкцией по сбросу пароля (описано в шаге 88).

Запустите скачанный файл.

Обычно он находится в папке 'Загрузки' и называется примерно postgresql-18.3-1-windows-x64.exe.

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

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

После этого запустится мастер установки.

1. Выбор папки установки.

Оставьте настройки по умолчанию и нажмите Next.

2. Выбор компонентов.

Оставьте настройки по умолчанию и нажмите Next.

3. Выбор папки для данных.

Оставьте настройки по умолчанию и нажмите Next.

4. Установка пароля администратора.

Внимание! Это очень важный шаг. Задайте пароль для доступа к СУБД.

Рекомендуется использовать простой пароль, например manager или admin.

Вы можете выбрать свой, но обязательно запишите его.

Введите пароль дважды и нажмите Next.

5. Выбор порта.

Оставьте порт по умолчанию (5432) и нажмите Next.

6. Выбор локали.

Оставьте значение по умолчанию (обычно DEFAULT) и нажмите Next.

7. Просмотр настроек.

Проверьте параметры установки и нажмите Next.

8. Установка.

Нажмите Next для начала установки.

Процесс может занять от 5 до 15 минут.

9. Завершение.

Снимите галочку Launch Stack Builder and Exit и нажмите Finish.

Если вы забыли пароль: Процедура сброса пароля подробно описана в шаге 88 (раздел 'Сброс забытого пароля пользователя PostgreSQL').

ПРОВЕРКА УСТАНОВКИ

В меню Пуск найдите и запустите pgAdmin 4.

В левой части окна раскройте Servers → PostgreSQL 18 (версия может отличаться).

Введите пароль, который вы задали при установке.

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

Если соединение успешно, вы увидите структуру сервера в левой панели.

Иногда требуется повторить подключение дважды — первый запуск может занять немного времени.

ПЕРВОЕ ЗНАКОМСТВО С pgAdmin

Для работы с запросами используется Query Tool:

Выберите в меню Tools → Query Tool.

(скриншот: pgadmin4.png)

Откроется окно, в котором можно писать и выполнять SQL-запросы.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Забыть пароль администратора.

Если пароль потерян, придётся переустанавливать PostgreSQL или воспользоваться инструкцией по сбросу пароля (шаг 88).

Ошибка 2. Выбрать не ту версию для скачивания.

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

Ошибка 3. Игнорировать требования к системе.

Если оперативной памяти или места на диске недостаточно, установка может не завершиться.

ИЗ ОПЫТА

При первой установке я выбрал сложный пароль и тут же забыл его.

Пришлось открыть эту книгу на шаге 88 и сбросить пароль, как написано.

С тех пор я использую простой пароль и записываю его в надёжное место.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли установить PostgreSQL на macOS или Linux?

О: Да. На официальном сайте есть инструкции для всех основных операционных систем.

В: Обязательно ли устанавливать pgAdmin?

О: Нет. Есть и другие инструменты (например, DBeaver, DataGrip), но pgAdmin идёт в комплекте и отлично подходит для начала.

В: Что делать, если установка зависла?

О: Попробуйте перезапустить компьютер и начать установку заново.

Шаг 3. Что где находится. Как работать с pgAdmin

Ваш главный инструмент для работы с PostgreSQL

ЖИВОЙ ПРИМЕР

Вы уже установили PostgreSQL и pgAdmin. Теперь перед вами окно pgAdmin — множество различных элементов интерфейса, сложное меню, различные кнопки, настройки — и, возможно, вы уже чувствуете лёгкое замешательство: Что здесь к чему? Где здесь писать запросы? Как посмотреть перечень таблиц? Как узнать, какие есть данные в нужной таблице?

В этом шаге мы подробно разберём интерфейс pgAdmin, чтобы вы чувствовали себя уверенно. Этот инструмент станет вашим главным помощником на протяжении всей книги.

НАЗНАЧЕНИЕ ПРОГРАММЫ pgAdmin

pgAdmin — это графический интерфейс для работы с PostgreSQL.

Он позволяет:

— просматривать структуру баз данных

— выполнять SQL-запросы

— редактировать данные в таблицах

— управлять серверами и пользователями

— и выполнять множество других задач

Простыми словами: pgAdmin — это окно в ваш PostgreSQL.

ЗАПУСК ПРОГРАММЫ pgAdmin

Нажмите Пуск (Windows) или откройте Launchpad (macOS).

Найдите pgAdmin 4 и запустите его.

Откроется интерфейс pgAdmin.

ПРОСМОТР ДЕРЕВА СЕРВЕРОВ, БАЗ, СХЕМ, ТАБЛИЦ

В левой панели pgAdmin находится дерево объектов.

Разберитесь с иерархией:

— Servers — здесь отображаются подключения к серверам PostgreSQL.

— PostgreSQL 18 (или ваша версия) — ваш локальный сервер.

— Databases — список баз данных.

— flower_delivery (ваша учебная база) — раскройте её.

— Schemas → public — здесь находятся таблицы, представления, функции.

— Tables — список таблиц в схеме public.

(скриншот: pgadmin_tree.png — дерево объектов)

Совет: чтобы найти нужную таблицу, используйте поиск (Ctrl + G).

СОЕДИНЕНИЕ С СЕРВЕРОМ БАЗЫ ДАННЫХ

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

Открытие сервера

В левой панели pgAdmin найдите Servers и раскройте его. Вы увидите сервер PostgreSQL 18 (или вашу версию).

Подключение к серверу

Нажмите на сервер левой кнопкой мыши. Откроется окно ввода пароля.

Ввод пароля

Введите пароль, который вы задали при установке PostgreSQL (рекомендуется снять галочку 'Store password', чтобы не вводить пароль каждый раз).

Успешное подключение

После ввода правильного пароля сервер станет активным — вы увидите структуру баз данных, и зелёная иконка сервера станет активной.

(скриншот: pgadmin_connected.png — сервер подключён)

Если подключиться не удалось

Проверьте:

— Запущен ли PostgreSQL (служба postgresql в Windows или systemctl status postgresql в Linux)

— Правильно ли введён пароль

— Не блокирует ли подключение брандмауэр

СОЗДАДИМ ТАБЛИЧКУ ДЛЯ ПРИМЕРА

Прежде чем изучать работу с таблицами, давайте создадим простую таблицу, с которой будем экспериментировать.

— В левой панели найдите вашу базу данных flower_delivery

— Раскройте Schemas → public → Tables

— Нажмите правой кнопкой на Tables → Create → Table

В открывшемся окне:

— В поле Name введите test_table

— На вкладке Columns добавите колонку id с типом integer и отметьте NOT NULL

— Добавьте колонку name с типом text

(скриншот: pgadmin_create_table_setup.png — настройка таблицы)

Нажмите Save. Таблица создана. Теперь она появится в списке Tables.

РАБОТА С ТАБЛИЦАМИ

Посмотреть данные:

Нажмите правой кнопкой мыши на таблицу → View/Edit Data → All Rows.

Откроется окно с содержимым таблицы.

Посмотреть структуру:

Раскройте таблицу → Columns. Там указаны все колонки, их типы и ограничения.

Добавление данных в тестовую таблицу:

— Откройте таблицу test_table через View/Edit Data → All Rows

— В появившемся окне нажмите на значок + (Add row) или кликните в последнюю пустую строку

— Заполните поля:

— В колонку id введите 1

— В колонку name введите Тестовая запись 1

— Нажмите Save Data Changes (иконка дискеты) или F6

(скриншот: pgadmin_insert_row.png — вставка строки)

Добавьте ещё две строки:

Сохраните изменения воспользовавшись кнопкой Save Data Changes

(скриншот: pgadmin_save_data.png — сохранение введенных данных)

TOOLS, ИНСТРУМЕНТЫ pgAdmin

В верхнем меню есть раздел Tools — здесь находятся основные инструменты:

— Query Tool — главное место для написания и выполнения SQL-запросов. Именно этот инструмент мы будем использовать для обучения на протяжении всей книги.

— Search Objects (Ctrl + G) — поиск по объектам базы данных.

— Import/Export — импорт и экспорт данных (например, из CSV).

ОПИСАНИЕ РАБОТЫ QUERY TOOL

Query Tool — это сердце pgAdmin. Здесь вы будете писать почти все запросы. Именно этот инструмент мы будем использовать для обучения на протяжении всей книги.

Как открыть:

Нажмите правой кнопкой на базе данных → Query Tool.

Или выберите базу → Tools → Query Tool.

Интерфейс:

— Верхняя часть — редактор кода (пишете запрос).

— Нижняя часть — результат выполнения (таблицы, сообщения, время выполнения).

— Кнопка Execute (>) или клавиша F5 — выполнить запрос.

— Кнопка Open File — загрузить запрос из файла.

— Кнопка Save — сохранить запрос.

Пример:

sql

SELECT * FROM test_table;

Напишите этот запрос в редакторе, нажмите Execute — и увидите данные из нашей тестовой таблицы (три добавленные строки).

НАСТРОЙКИ ПРОГРАММЫ pgAdmin

Чтобы изменить внешний вид или поведение pgAdmin:

В верхнем меню выберите File → Preferences.

Здесь можно настроить:

— тему (тёмная / светлая)

— размер шрифта в редакторе

— поведение при выполнении запросов (например, автоматически открывать результат в новой вкладке)

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Не могу найти Query Tool.

Убедитесь, что выбрана база данных, и только потом открывайте Query Tool.

Ошибка 2. В левой панели нет сервера.

Проверьте, запущен ли PostgreSQL. В меню Пуск найдите SQL Shell (psql) и попробуйте подключиться. Если не запущен — перезапустите службу PostgreSQL.

ТОНКОСТИ И НЮАНСЫ

— В Query Tool можно открыть несколько вкладок — удобно, если работаете с разными запросами.

Посвящается:

— Результат запроса можно экспортировать в CSV — нажмите на иконку дискеты в панели результатов.

— Если запрос выполняется долго, можно остановить его кнопкой Cancel ([-]).

ИЗ ОПЫТА

Когда я только начинал работать с pgAdmin, я долго искал, где выполнить запрос.

Оказывается, нужно сначала выбрать базу данных, а потом открывать Query Tool.

С тех пор я всегда проверяю, какая база выбрана в дереве, перед тем как открыть Query Tool.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли работать с PostgreSQL без pgAdmin?

О: Да. Есть консольный клиент psql, но для новичков pgAdmin удобнее.

В: Как сменить язык интерфейса?

О: В File → Preferences → General → Language можно выбрать русский (если поддерживается).

ПОПРОБУЙ САМ

— Запустите pgAdmin.

— Найдите в дереве тестовую таблицу test_table.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Создайте таблицу test_table2 с помощью pgAdmin (через графический интерфейс). Добавьте колонки id (целое число, первичный ключ) и description (текст).

Шаг 4. Типы данных. Как создать свою первую таблицу и заполнить её данными

Первый шаг в мир структурированных данных

ЖИВОЙ ПРИМЕР

Вы уже установили PostgreSQL и научились открывать Query Tool.

Теперь самое время создать свою первую таблицу и заполнить её данными.

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

В этом шаге мы рассмотрим два способа:

— Создание таблицы и вставка данных через визуальный интерфейс pgAdmin.

— Создание таблицы и вставка данных через SQL-запросы.

ЧТО ТАКОЕ ТАБЛИЦЫ В БАЗЕ ДАННЫХ

Таблица в базе данных — это как таблица в Excel или Word, но с одним важным отличием: каждая колонка имеет строго определённый тип данных.

Вы не можете в одну колонку записать и число, и текст — тип задаётся один раз при создании таблицы, и все данные в этой колонке должны соответствовать этому типу.

Правила именования таблиц и колонок:

Колонки таблиц в базе всегда имеют название, таблицы — тоже. Названия таблиц не должны повторяться в рамках одной схемы данных.

Наименования таблиц и колонок должны отвечать следующим базовым требованиям:

— Не должны начинаться с цифры

— Не должны содержать спецсимволы (допустимы только буквы, цифры и подчёркивание)

— Не должны совпадать с зарезервированными словами SQL (например, SELECT, TABLE, INSERT)

Примеры правильных имён:

sql

— правильно

CREATE TABLE employees (…);

CREATE TABLE orders_2026 (…);

CREATE TABLE user_profiles (…);

Примеры неправильных имён:

sql

— неправильно

CREATE TABLE 1employees (…); — начинается с цифры

CREATE TABLE employees-list (…); — содержит дефис

CREATE TABLE SELECT (…); — зарезервированное слово

ЧТО ТАКОЕ ТИПЫ ДАННЫХ

Тип данных — это правило, которое говорит базе данных: 'в этой колонке могут храниться только такие-то значения'.

Это помогает:

— не ошибаться — база не даст записать текст в колонку с числами

— экономить место — числа хранятся компактнее, чем текст

— быстро искать — базе проще сравнивать числа, чем длинные строки

Основные типы данных, которые мы будем использовать на первых шагах:

ЧТО ТАКОЕ ПЕРВИЧНЫЙ КЛЮЧ (PRIMARY KEY)

Первичный ключ — это уникальный идентификатор каждой строки в таблице.

Зачем он нужен:

— Чтобы каждая запись была уникальной.

— Чтобы можно было быстро найти строку.

— Чтобы другие таблицы могли ссылаться на эту запись (внешние ключи).

Свойства первичного ключа:

— Не может содержать NULL (пустое значение).

— Значения должны быть уникальными.

— В таблице может быть только один первичный ключ.

Примеры первичных ключей в реальной жизни:

— Номер заказа в интернет-магазине.

— ИНН человека.

— Номер телефона человека.

Что используют в качестве первичного ключа:

— Последовательность чисел (1, 2, 3, …) — самый распространённый подход.

— GUID / UUID — специальный набор символов, который гарантированно уникален во всём мире.

В таблице, которую мы создадим — employees (служащие), первичным ключом будет колонка id.

СПОСОБ 1. СОЗДАНИЕ ТАБЛИЦЫ ЧЕРЕЗ ВИЗУАЛЬНЫЙ ИНТЕРФЕЙС pgADMIN

Мы уже создавали таблицу в предыдущем шаге, но для закрепления повторим процесс ещё раз.

— Откройте базу данных.

— В левой панели pgAdmin раскройте Servers → PostgreSQL 18 → Databases.

— Выберите базу, с которой работаете (например, flower_delivery).

— Перейдите в раздел Tables.

— Раскройте Schemas → public → Tables.

— Создайте таблицу.

— Нажмите правой кнопкой мыши на Tables и выберите Create → Table.

— Задайте имя таблицы.

— В открывшемся окне в поле Name введите employees.

— Добавьте колонки.

— Перейдите на вкладку Columns и нажмите + для добавления колонок.

Добавьте следующие колонки:

— id — тип serial. Это будет первичный ключ. В разделе Constraints отметьте PRIMARY KEY.

— head_id — тип integer. В разделе Constraints отметьте NOT NULL.

— name — тип text. В разделе Constraints отметьте NOT NULL.

— position — тип text. Ограничения не требуются.

— salary — тип numeric. Ограничения не требуются.

— Сохраните таблицу.

— Нажмите Save.

СПОСОБ 1. ЗАПОЛНЕНИЕ ТАБЛИЦЫ ЧЕРЕЗ ВИЗУАЛЬНЫЙ ИНТЕРФЕЙС pgADMIN

Как и на предыдущем шаге, заполним таблицу данными через визуальный интерфейс.

— Откройте таблицу для редактирования.

— В левой панели pgAdmin найдите созданную таблицу employees.

— Нажмите на неё правой кнопкой мыши и выберите View/Edit Data → All Rows.

— Добавьте строки.

— В открывшемся окне вы увидите пустую таблицу.

— Нажмите на значок + (Add row) или кликните в последнюю строку (там, где отображается (NULL)).

— Заполните поля.

— Для каждой строки введите значения:

— Сохраните изменения.

— Нажмите Save Data Changes (иконка дискеты) или нажмите F6.

СПОСОБ 2. СОЗДАНИЕ ТАБЛИЦЫ ЧЕРЕЗ SQL

А теперь сделаем то же самое, но через SQL-запросы — этот способ профессиональнее и быстрее.

— Откройте Query Tool.

— В pgAdmin выберите базу данных (например, flower_delivery), затем в меню Tools → Query Tool.

— Напишите запрос на создание таблицы.

sql

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

head_id INTEGER NOT NULL,

name TEXT NOT NULL,

position TEXT,

salary NUMERIC

);

Что тут происходит

— CREATE TABLE — команда, которая создаёт новую таблицу в базе данных.

— employees — имя таблицы.

— id SERIAL PRIMARY KEY — автоматически увеличиваемый уникальный номер, первичный ключ.

— head_id INTEGER NOT NULL — идентификатор руководителя, не может быть пустым.

— name TEXT NOT NULL — имя, не может быть пустым.

— position TEXT — должность, может быть пустой.

— salary NUMERIC — зарплата, может быть пустой.

— Выполните запрос.

— Нажмите кнопку Execute (▶) или клавишу F5.

СПОСОБ 2. ЗАПОЛНЕНИЕ ТАБЛИЦЫ ЧЕРЕЗ SQL

— Откройте Query Tool.

— В pgAdmin выберите базу данных (например, flower_delivery), затем в меню Tools → Query Tool.

— Напишите запрос на вставку данных.

sql

INSERT INTO employees (id, head_id, name, position, salary) VALUES

(1, 1, 'Анна', 'Директор', 100000),

(2, 1, 'Борис', 'Менеджер', 70000),

(3, 1, 'Виктор', 'Менеджер', 70000),

(4, 2, 'Галина', 'Продавец', 50000),

(5, 2, 'Дмитрий', 'Продавец', 50000),

(6, 3, 'Елена', 'Продавец', 50000),

(7, 2, 'Жанна', 'Продавец', 50000),

(8, 3, 'Зоя', 'Продавец', 50000),

(9, 1, 'Иван', 'Аналитик', 60000),

(10, 9, 'Кирилл', 'Стажёр', 30000);

Что тут происходит

— INSERT INTO employees — команда для добавления данных в таблицу.

— В скобках после имени таблицы перечислены колонки, в которые мы вставляем значения.

— VALUES — список строк с данными. Каждая строка в своих скобках.

— Выполните запрос.

— Нажмите кнопку Execute (▶) или клавишу F5.

— Проверьте результат.

sql

SELECT * FROM employees;

Что тут происходит

SELECT * FROM employees — выводит все строки из таблицы employees. Звёздочка * означает 'все колонки'.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Забыть указать NOT NULL для колонки, которая не должна быть пустой.

Неверно:

sql

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name TEXT

);

Если не указать NOT NULL, в колонку name можно вставить пустое значение (NULL), что часто приводит к проблемам.

Верно:

sql

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL

);

Ошибка 2. Забыть указать PRIMARY KEY для колонки, которая должна быть уникальным идентификатором.

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

Неверно:

sql

CREATE TABLE employees (

id SERIAL,

name TEXT

);

Верно:

sql

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name TEXT

);

Ошибка 3. Попытаться вставить строку с уже существующим значением первичного ключа.

Неверно:

sql

INSERT INTO employees (id, name) VALUES (1, 'Анна');

INSERT INTO employees (id, name) VALUES (1, 'Борис');

PostgreSQL выдаст ошибку: duplicate key value violates unique constraint.

Верно:

sql

INSERT INTO employees (id, name) VALUES (1, 'Анна');

INSERT INTO employees (id, name) VALUES (2, 'Борис');

Ошибка 4. Забыть кавычки для текстовых значений.

Неверно:

sql

INSERT INTO employees (name) VALUES (Анна);

PostgreSQL подумает, что Анна — это имя колонки или переменной, и выдаст ошибку.

Верно:

sql

INSERT INTO employees (name) VALUES ('Анна');

Ошибка 5. Использовать двойные кавычки для строк.

В SQL строки записываются в одинарных кавычках. Двойные кавычки используются для имён таблиц и колонок (если они содержат пробелы или совпадают с ключевыми словами).

Неверно:

sql

INSERT INTO employees (name) VALUES ('Анна');

Верно:

sql

INSERT INTO employees (name) VALUES ('Анна');

ТОНКОСТИ И НЮАНСЫ

— SERIAL — это не тип данных, а сокращение. На самом деле PostgreSQL создаёт последовательность (SEQUENCE) и устанавливает значение по умолчанию для колонки. При вставке, если не указать id, он заполнится автоматически.

— Кавычки. Для строк — одинарные: 'Анна'. Для имён таблиц и колонок — двойные: 'employees' (обычно не нужны, если имя не совпадает с ключевым словом). Для чисел — никаких кавычек: 100000.

— Порядок колонок в INSERT. Если вы перечисляете колонки, порядок не важен, главное — чтобы значения соответствовали. Если не перечисляете, значения должны идти в том же порядке, что и при создании таблицы.

sql

— порядок колонок не важен

INSERT INTO employees (name, salary, id) VALUES ('Анна', 100000, 1);

— порядок важен — значения должны идти в порядке колонок таблицы

INSERT INTO employees VALUES (1, 'Анна', 100000);

— NOT NULL и PRIMARY KEY. PRIMARY KEY автоматически подразумевает NOT NULL, но не наоборот. Можно создать колонку, которая не может быть пустой, но не является первичным ключом.

— Что делать, если таблица уже существует. Если вы уже создали таблицу, повторный запуск CREATE TABLE вызовет ошибку. Чтобы этого избежать, можно использовать CREATE TABLE IF NOT EXISTS:

sql

CREATE TABLE IF NOT EXISTS employees (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL

);

Что тут происходит

Команда CREATE TABLE IF NOT EXISTS проверяет, существует ли таблица с таким именем. Если таблицы нет — создаёт её. Если таблица уже есть — ничего не делает и не выдаёт ошибку. Это удобно, когда скрипт может выполняться несколько раз (например, при повторной настройке базы данных).

ИЗ ОПЫТА

Когда я только начинал работать с PostgreSQL, я создал таблицу employees без первичного ключа.

Через некоторое время в ней появились дубликаты — два сотрудника с одинаковым id.

Я долго не мог понять, почему при выборке по id возвращается две строки.

С тех пор я всегда добавляю PRIMARY KEY для таблиц, где это нужно, и обязательно проверяю, что колонки, которые не должны быть пустыми, помечены NOT NULL.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли создать таблицу с другим именем?

О: Да. Имя должно быть уникальным в рамках одной схемы. Старайтесь выбирать осмысленные имена, например employees, products, orders.

В: Что делать, если ошибка в синтаксисе?

О: PostgreSQL выдаст сообщение об ошибке. Внимательно прочитайте его — там обычно указано, где примерно ошибка и что не так. Проверьте кавычки, запятые, названия колонок.

В: Как удалить таблицу?

О: Используйте команду:

sql

DROP TABLE employees;

Что тут происходит

DROP TABLE удаляет таблицу и все данные в ней. Будьте осторожны — восстановить данные будет сложно.

В: Можно ли добавить колонку в уже существующую таблицу?

О: Да. Например:

sql

ALTER TABLE employees ADD COLUMN email TEXT;

Что тут происходит

ALTER TABLE изменяет структуру таблицы. В данном случае добавляется новая колонка email с типом TEXT.

ПОПРОБУЙ САМ

— Создайте таблицу flowers_first с колонками:

— id (SERIAL, PRIMARY KEY)

— name (TEXT, NOT NULL)

— price (NUMERIC)

— Вставьте в неё 3–4 цветка (например, Роза, Тюльпан, Лилия) с разными ценами.

— Выведите все строки из таблицы.

— (По желанию) Создайте такую же таблицу через визуальный интерфейс pgAdmin.

Шаг 5. Установка демонстрационной схемы: вручную и через GitHub

Готовим полигон для экспериментов

ЖИВОЙ ПРИМЕР

Представьте, что у вас реальная служба доставки цветов. Какая информация вам нужна для работы? Клиенты (кто заказывает), заказы (что, кому, когда), цветы (розы, тюльпаны, лилии), оттенки (красный, белый, розовый). Всё это есть в нашей демонстрационной схеме.

Вы уже создали свою первую таблицу и заполнили её данными.

Теперь настало время развернуть полноценную учебную базу — схему 'Доставка цветов'.

Все примеры в книге построены на этой схеме — как на настоящей базе данных для реальной службы доставки.

В ней есть клиенты, заказы, цветы, оттенки и города.

Вы можете установить её двумя способами:

— Вручную — выполнить SQL-скрипт, который создаст все таблицы и заполнит их данными.

— Через GitHub — скачать готовый скрипт из репозитория и выполнить его.

ЧТО ВХОДИТ В ДЕМОНСТРАЦИОННУЮ СХЕМУ

Схема состоит из пяти основных таблиц:

— flowers — цветы (название, цена)

— colors — оттенки (код, название)

— cities — города (название, население)

— clients — клиенты (телефон, имя, город, дата рождения)

— orders — заказы (клиент, цветок, оттенок, количество, дата, статус)

Все данные уже подготовлены: 8 цветов, 8 оттенков, 13 городов, 14 клиентов, 21 заказ.

Как это работает и как таблицы связаны между собой:

— Клиенты (clients) делают заказы. Каждый клиент живёт в каком-то городе (city_id ссылается на cities.id).

— Заказы (orders) содержат информацию о том, какой клиент (client_phone ссылается на clients.phone), какой цветок (flower_id ссылается на flowers.id), какой оттенок (color_code ссылается на colors.code), сколько цветов (quantity) и когда был сделан заказ (order_ts).

— Цветы (flowers) — это ассортимент (Роза, Тюльпан, Лилия и т.д.). У каждого цветка есть цена.

— Оттенки (colors) — это возможные цвета цветов (красный, белый, розовый и т.д.).

— Города (cities) — это города, в которых живут клиенты.

Схема связей:

text

clients.city_id → cities.id

orders.client_phone → clients.phone

orders. flower_id → flowers.id

orders.color_code → colors.code

Таким образом, чтобы получить полную информацию о заказе (имя клиента, название цветка, оттенок, город), нужно соединить несколько таблиц через эти связи.

СПОСОБ 1. УСТАНОВКА СХЕМЫ ВРУЧНУЮ

— Откройте Query Tool.

— В pgAdmin выберите базу данных (например, flower_delivery), затем в меню Tools → Query Tool.

— Скопируйте и выполните SQL-скрипт.

Ниже приведён полный скрипт для создания схемы.

Вы можете скопировать его и вставить в окно Query Tool, затем нажать Execute (>) или F5.

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

sql

— =============================================

— Демонстрационная схема: Доставка цветов

— =============================================

— Удаление таблиц, если они существуют

DROP TABLE IF EXISTS orders;

DROP TABLE IF EXISTS clients;

DROP TABLE IF EXISTS cities;

DROP TABLE IF EXISTS colors;

DROP TABLE IF EXISTS flowers;

— Таблица цветов

CREATE TABLE flowers (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL,

price NUMERIC (10,2) NOT NULL CHECK (price> 0)

);

— Таблица оттенков

CREATE TABLE colors (

code CHAR (2) PRIMARY KEY,

name TEXT NOT NULL UNIQUE

);

— Таблица городов

CREATE TABLE cities (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL UNIQUE,

population INTEGER CHECK (population> 0)

);

— Таблица клиентов

CREATE TABLE clients (

phone TEXT PRIMARY KEY CHECK (phone ~ '^ [0—9] {9} $'),

name TEXT NOT NULL,

city_id INTEGER NOT NULL,

birth_date DATE

);

— Таблица заказов

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

client_phone TEXT NOT NULL,

flower_id INTEGER NOT NULL,

color_code CHAR (2) NOT NULL,

quantity INTEGER NOT NULL CHECK (quantity> 0),

order_ts TIMESTAMP NOT NULL,

status TEXT NOT NULL DEFAULT 'новый' CHECK (status IN ('новый', 'в доставке', 'доставлен', 'отменён'))

);

— Наполнение данными

INSERT INTO flowers (name, price) VALUES

('Роза', 150), ('Тюльпан', 90), ('Хризантема', 110),

('Лилия', 130), ('Пион', 200), ('Гербера', 80),

('Орхидея', 250), ('Ромашка', 60);

INSERT INTO colors (code, name) VALUES

('re', 'красный'), ('wh', 'белый'), ('pi', 'розовый'),

('ye', 'жёлтый'), ('pu', 'фиолетовый'), ('or', 'оранжевый'),

('bo', 'бордовый'), ('li', 'сиреневый');

INSERT INTO cities (name, population) VALUES

('Москва', 13000000), ('Санкт-Петербург', 5600000),

('Казань', 1300000), ('Новосибирск', 1600000),

('Екатеринбург', 1500000), ('Нижний Новгород', 1200000),

('Самара', 1150000), ('Ростов-на-Дону', 1130000),

('Уфа', 1120000), ('Красноярск', 1100000),

('Пермь', 1050000), ('Воронеж', NULL), ('Саратов', NULL);

INSERT INTO clients (phone, name, city_id, birth_date) VALUES

('991234567', 'Николай', 1, '1980-05-10'),

('112345678', 'Елена', 2, '1992-08-22'),

('993456789', 'Дмитрий', 1, '1985-11-03'),

('434567890', 'Анна', 3, NULL),

('115678901', 'Алексей', 2, '1978-03-15'),

('836789012', 'Ольга', 4, '1995-07-19'),

('437890123', 'Сергей', 5, NULL),

('998901234', 'Татьяна', 1, '1988-12-01'),

('439012345', 'Андрей', 3, '1991-04-25'),

('310123456', 'Мария', 6, '1983-09-12'),

('991112233', 'Ирина', 1, '1985-06-15'),

('112223344', 'Павел', 2, '1990-11-20'),

('433334455', 'Светлана', 3, NULL),

('834445566', 'Виктор', 4, NULL);

INSERT INTO orders (client_phone, flower_id, color_code, quantity, order_ts, status) VALUES

— Первые 9 — доставленные

('991234567', 2, 're', 5, '2026-02-10 10:15:00', 'доставлен'),

('112345678', 1, 'pi', 3, '2026-02-12 14:30:00', 'доставлен'),

('993456789', 5, 'wh', 7, '2026-02-15 09:45:00', 'доставлен'),

('991234567', 3, 'ye', 3, '2026-02-18 16:20:00', 'доставлен'),

('434567890', 2, 're', 11, '2026-02-20 12:10:00', 'доставлен'),

('115678901', 4, 'pu', 5, '2026-02-21 18:05:00', 'доставлен'),

('112345678', 6, 'or', 9, '2026-02-23 11:40:00', 'доставлен'),

('836789012', 1, 're', 7, '2026-02-25 13:25:00', 'доставлен'),

('437890123', 7, 'bo', 11, '2026-02-26 15:55:00', 'доставлен'),

— Следующие 6 — новые

('993456789', 2, 'ye', 11, '2026-02-27 08:15:00', 'новый'),

('998901234', 5, 'wh', 3, '2026-03-01 17:30:00', 'новый'),

('439012345', 3, 'pi', 5, '2026-03-02 10:45:00', 'новый'),

('434567890', 8, 'wh', 15, '2026-03-03 12:00:00', 'новый'),

('310123456', 1, 're', 5, '2026-03-05 19:20:00', 'новый'),

('112345678', 4, 'pu', 3, '2026-03-07 14:35:00', 'новый'),

— Дополнительные — в доставке и отменённые

('991234567', 3, 'ye', 3, '2026-03-10 11:20:00', 'в доставке'),

('112345678', 5, 'wh', 1, '2026-03-11 13:45:00', 'в доставке'),

('993456789', 1, 're', 3, '2026-03-12 09:10:00', 'в доставке'),

('434567890', 4, 'pu', 3, '2026-03-13 16:30:00', 'отменён'),

('115678901', 2, 're', 3, '2026-03-14 12:00:00', 'отменён'),

('836789012', 7, 'bo', 1, '2026-03-15 14:15:00', 'отменён');

— Проверочный запрос

SELECT COUNT (*) AS total_orders FROM orders;

— Проверьте результат.

— После выполнения в нижней части окна появится результат проверочного запроса — количество заказов (должно быть 21).

СПОСОБ 2. УСТАНОВКА СХЕМЫ ЧЕРЕЗ GITHUB

— Перейдите в репозиторий книги.

— Откройте браузер и перейдите по ссылке:

— https://github.com/atvcross/posgreesqlbook

(скриншот: github_repo.png)

— Скачайте скрипт.

Вариант А. Скачать отдельный файл.

Нажмите на файл schema/flower_delivery. sql, затем на кнопку Raw (или Download Raw File).

Сохраните файл на компьютер.

Вариант Б. Скачать весь репозиторий ZIP-архивом.

На главной странице репозитория нажмите зелёную кнопку Code, выберите Download ZIP.

Распакуйте архив, затем найдите файл schema/flower_delivery. sql в распакованной папке.

— Выполните скрипт в pgAdmin.

— Откройте Query Tool.

— Нажмите Open File (иконка папки) и выберите скачанный файл.

— Нажмите Execute (▶) или F5.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Забыть выбрать правильную базу данных перед выполнением скрипта.

Убедитесь, что в Query Tool выбрана нужная база (например, flower_delivery).

Ошибка 2. Выполнить скрипт повторно, не удалив старые таблицы.

Если таблицы уже существуют, скрипт может выдать ошибку. В нашем скрипте есть DROP TABLE IF EXISTS, поэтому он безопасен для повторного выполнения.

Ошибка 3. Ошибки в кодировке.

Если при вставке данных возникают проблемы с русским текстом, проверьте кодировку базы данных (должна быть UTF8).

Создать базу с правильной кодировкой можно командой:

sql

CREATE DATABASE flower_delivery WITH ENCODING 'UTF8';

ТОНКОСТИ И НЮАНСЫ

— Почему DROP TABLE IF EXISTS? Это делает скрипт безопасным для повторного выполнения. Если таблицы уже есть, они удаляются и создаются заново. Без этой проверки при повторном запуске скрипт выдал бы ошибку.

— Порядок удаления таблиц важен. Сначала удаляются таблицы, на которые никто не ссылается (orders), потом те, на которые ссылаются (clients, flowers, colors, cities). Если попытаться удалить clients раньше orders, PostgreSQL выдаст ошибку, потому что orders ссылается на clients.

— CHECK в колонках. Например, CHECK (price> 0) гарантирует, что цена не может быть отрицательной. Если попытаться вставить цветок с ценой -100, PostgreSQL выдаст ошибку.

— Почему в заказах только нечётное количество цветов? В России чётное количество цветов дарят только на похороны. Чтобы не вызывать неприятных ассоциаций, в схеме используются только нечётные значения.

ИЗ ОПЫТА

Когда я только начинал работать с PostgreSQL, я часто выполнял скрипты, не глядя на порядок удаления таблиц.

Из-за этого возникали ошибки типа 'таблица не может быть удалена, потому что на неё ссылаются'.

С тех пор я всегда использую DROP TABLE IF EXISTS и указываю таблицы в правильном порядке.

А однажды я забыл про кодировку и вставил русский текст в базу с кодировкой SQL_ASCII.

Текст отображался кракозябрами. Пришлось пересоздавать базу с UTF8.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли установить схему без удаления старых данных?

О: Да, если вы уверены, что таблицы не существуют, можно убрать DROP TABLE IF EXISTS. Но для чистоты эксперимента лучше выполнить скрипт как есть.

В: Что делать, если скрипт выдаёт ошибку?

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

В: Нужно ли каждый раз удалять и создавать схему заново?

О: Нет. Если вы уже установили схему, можете не выполнять скрипт повторно. Если данные испортились — можно выполнить ещё раз.

В: Почему в скрипте есть DROP TABLE IF EXISTS, а не просто DROP TABLE?

О: DROP TABLE IF EXISTS не вызывает ошибку, если таблицы нет. Это делает скрипт безопасным для повторного выполнения.

ПОПРОБУЙ САМ

— Установите демонстрационную схему одним из способов.

— Выполните проверочный запрос: SELECT COUNT (*) FROM orders; — должно быть 21.

— Посмотрите структуру таблиц в pgAdmin: раскройте Schemas → public → Tables и изучите колонки каждой таблицы.

Шаг 6. Как использовать демонстрационную схему

Начинаем работать с готовой базой данных

ЖИВОЙ ПРИМЕР

Вы установили PostgreSQL, создали свою первую таблицу и развернули демонстрационную схему 'Доставка цветов'.

Теперь у вас есть готовая база данных с клиентами, заказами, цветами и городами.

Эта схема будет использоваться на протяжении всей книги.

Все примеры, от простого SELECT до сложных аналитических функций, будут построены на этих данных.

Вы сможете выполнять запросы, экспериментировать, видеть результат сразу — и постепенно осваивать язык SQL.

В этом шаге мы разберём, как с ней работать:

— как открыть Query Tool и выполнить запрос

— как посмотреть данные в таблицах

— как быстро найти нужную таблицу

— как вернуться к схеме, если что-то забыли

ПЕРВЫЕ ШАГИ В PGADMIN

— Подключитесь к базе данных.

— В левой панели pgAdmin раскройте Servers → PostgreSQL 18 → Databases.

— Найдите вашу базу postgres или flower_delivery. Если нужной базы нет — создайте новую (см. раздел 'Как создать новую базу данных' ниже).

— Откройте Query Tool.

— Нажмите правой кнопкой мыши на базе данных и выберите Query Tool.

— Или выберите базу, затем в меню Tools → Query Tool.

— Напишите и выполните простой запрос.

sql

SELECT * FROM flowers;

Нажмите Execute (>) или F5. В нижней части окна появится список всех цветов.

КАК СОЗДАТЬ НОВУЮ БАЗУ ДАННЫХ В PGADMIN

Если базы данных flower_delivery нет в списке, вы можете создать её самостоятельно.

Способ 1. Через визуальный интерфейс pgAdmin:

— В левой панели pgAdmin раскройте Servers → PostgreSQL 18 (или ваша версия)

— Нажмите правой кнопкой мыши на Databases

— Выберите Create → Database…

(скриншот: create_database_menu.png)

— В открывшемся окне в поле Database введите имя: flower_delivery

— В поле Owner оставьте postgres (или выберите другого пользователя)

— Нажмите Save

(скриншот: create_database_window.png)

Новая база данных flower_delivery появится в списке Databases.

Способ 2. Через SQL-запрос в Query Tool:

— Откройте Query Tool (подключившись к любой существующей базе, например postgres)

— Выполните команду:

sql

CREATE DATABASE flower_delivery;

Будет создана база данных с именем flower_delivery.

Дополнительные параметры (необязательно):

sql

CREATE DATABASE flower_delivery

OWNER postgres

ENCODING 'UTF8'/* */

LC_COLLATE 'ru_RU. UTF-8' /* */

LC_CTYPE 'ru_RU. UTF-8';

Создаёт базу с владельцем postgres, кодировкой UTF8 и русской локалью.

После создания базы:

— Обновите список баз (нажмите F5 или правой кнопкой на Databases → Refresh)

— Выберите новую базу flower_delivery

— Выполните скрипт установки демонстрационной схемы из шага 5

КАК ПОСМОТРЕТЬ ДАННЫЕ В ТАБЛИЦЕ

Способ 1. Через Query Tool.

Напишите SELECT * FROM имя_таблицы; и выполните.

Способ 2. Через визуальный интерфейс.

В левой панели раскройте Schemas → public → Tables.

Нажмите правой кнопкой мыши на нужную таблицу и выберите View/Edit Data → All Rows.

КАК БЫСТРО НАЙТИ НУЖНУЮ ТАБЛИЦУ

В pgAdmin есть поиск по объектам:

— Нажмите Ctrl + G (или в меню Tools → Search Objects).

— Введите название таблицы (например, flowers).

— Нажмите Enter — pgAdmin покажет найденные объекты.

ЕСЛИ ЗАБЫЛИ СТРУКТУРУ ТАБЛИЦЫ

Способ 1. Через Query Tool.

Выполните SELECT * FROM имя_таблицы LIMIT 0; — вы увидите только названия колонок и их типы.

Способ 2. Через визуальный интерфейс.

В левой панели найдите таблицу, раскройте её → Columns.

Там отображаются все колонки с типами и ограничениями.

КАК ВЕРНУТЬСЯ К СХЕМЕ, ЕСЛИ ЧТО-ТО ЗАБЫЛИ

— В шаге 7 подробно описана структура всех таблиц (колонки, типы, связи).

— В репозитории GitHub есть файл schema/README.md с кратким описанием схемы.

— Всегда можно выполнить SELECT * FROM таблица LIMIT 5;, чтобы посмотреть примеры данных.

КРАТКОЕ ОПИСАНИЕ ДЕМОНСТРАЦИОННОЙ СХЕМЫ — ДОСТАВКА ЦВЕТОВ

Таблицы и колонки:

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

Номера телефонов в таблице clients сгенерированы случайным образом и не соответствуют реальным абонентам.

Связи между таблицами:

— clients.city_id → cities.id

— orders.client_phone → clients.phone

— orders. flower_id → flowers.id

— orders.color_code → colors.code

Особенности:

— Статусы заказов: 'новый', 'в доставке', 'доставлен', 'отменён'.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Выбрать не ту базу данных перед выполнением запроса.

Убедитесь, что в Query Tool указана правильная база (например, flower_delivery).

Это видно в верхней части окна Query Tool.

Ошибка 2. Забыть точку с запятой в конце запроса.

PostgreSQL позволяет выполнять запросы без точки с запятой, но если запросов несколько — без неё будет ошибка.

Ошибка 3. Пытаться открыть таблицу, которая ещё не создана.

Если вы не выполнили скрипт из шага 5, таблиц не будет. Вернитесь к шагу 5 и установите схему.

ТОНКОСТИ И НЮАНСЫ

— SELECT * FROM имя_таблицы LIMIT 0; — полезный приём, чтобы посмотреть структуру таблицы без загрузки данных.

— В Query Tool можно сохранять запросы. Нажмите Save (иконка дискеты) и дайте файлу имя — например, my_queries. sql.

— Вкладки в Query Tool. Можно открыть несколько вкладок, чтобы работать с разными запросами.

ИЗ ОПЫТА

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

С тех пор я всегда проверяю имя базы в верхней части Query Tool перед выполнением.

А ещё я сохраняю часто используемые запросы в файлы — это экономит время.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли работать с базой данных без pgAdmin?

О: Да. Есть консольный клиент psql, но для начала pgAdmin удобнее.

В: Что делать, если я забыл пароль от базы данных?

О: При установке PostgreSQL вы задавали пароль. Если забыли — можно сбросить его по инструкции в шаге 88.

В: Можно ли выполнять запросы, не открывая Query Tool?

О: Можно через консольный клиент psql, но в рамках книги мы используем pgAdmin.

ПОПРОБУЙ САМ

— Откройте Query Tool и выполните SELECT * FROM flowers;.

— Посмотрите данные в таблице clients через визуальный интерфейс (View/Edit Data).

— Найдите таблицу orders через поиск (Ctrl + G).

— Посмотрите структуру таблицы orders с помощью SELECT * FROM orders LIMIT 0;.

Шаг 7. Подробное описание демонстрационной схемы

Знакомство с Доставкой цветов

ЖИВОЙ ПРИМЕР

Вы уже установили PostgreSQL, создали свою первую таблицу и заполнили её данными.

Теперь настало время познакомиться с главным учебным полигоном этой книги — схемой 'Доставка цветов'.

Все примеры в книге построены на этой схеме.

В ней есть клиенты, заказы, цветы, оттенки и города.

Она достаточно проста, чтобы быстро в ней разобраться, и достаточно богата, чтобы показывать сложные запросы.

ТАБЛИЦЫ СХЕМЫ

Схема состоит из пяти основных таблиц и одной дополнительной.

1. flowers — цветы

2. colors — оттенки

3. cities — города

4. clients — клиенты

5. orders — заказы

6. employees — сотрудники (дополнительная таблица)

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

СВЯЗИ МЕЖДУ ТАБЛИЦАМИ

— clients.city_id → cities.id — каждый клиент живёт в каком-то городе.

— orders.client_phone → clients.phone — заказ сделан клиентом.

— orders. flower_id → flowers.id — в заказе указан цветок.

— orders.color_code → colors.code — в заказе указан оттенок.

— employees. head_id → employees.id — иерархия сотрудников (кто кому подчиняется).

ДАННЫЕ В ТАБЛИЦАХ

Схема заполнена тестовыми данными:

— Цветы: 8 наименований с ценами от 60 до 250 рублей.

— Оттенки: 8 вариантов (красный, белый, розовый и т.д.).

— Города: 13 городов, в том числе с NULL-населением.

— Клиенты: 14 клиентов, у некоторых нет даты рождения.

— Заказы: 21 заказ с разными статусами и датами.

— Сотрудники: 10 сотрудников с иерархией подчинения.

Номера телефонов в таблице clients сгенерированы случайным образом и не соответствуют реальным абонентам.

ПОЧЕМУ ВЫБРАНА ИМЕННО ЭТА СХЕМА

— Понятна без объяснений — все знают, что такое цветы, заказы, клиенты.

— Достаточно сложна — есть связи один-ко-многим (клиент → заказы), многие-ко-многим (заказы → цветы через flower_id).

— Позволяет показывать все конструкции SQL — от простого SELECT до оконных функций и LATERAL.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Путать внешние ключи.

Например, orders.client_phone ссылается на clients.phone, а не на clients.id.

Ошибка 2. Забывать про NULL в birth_date и population.

В таблицах clients и cities есть NULL-значения. Это важно учитывать при написании запросов.

ТОНКОСТИ И НЮАНСЫ

— phone — первичный ключ в таблице clients. Это необычно (обычно первичный ключ — числовой), но так сделано для простоты демонстрации.

— status в orders имеет ограничение CHECK — только четыре допустимых значения.

ИЗ ОПЫТА

Когда я учил SQL, мне постоянно попадались абстрактные схемы вроде table1, table2.

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

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

'Доставка цветов' — это живая, простая и в то же время полноценная база данных.

ВОПРОСЫ И ОТВЕТЫ

В: Нужно ли запоминать все колонки и связи?

О: Нет. Со временем вы запомните главные. В любой момент можно вернуться к этому шагу и освежить память.

В: Можно ли изменить схему или добавить свои данные?

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

В: Для чего нужна таблица employees?

О: Она используется в некоторых продвинутых шагах для демонстрации иерархических запросов (например, поиск всех подчинённых сотрудника).

ПОПРОБУЙ САМ

— Откройте pgAdmin и подключитесь к базе данных.

— Найдите таблицу flowers. Посмотрите, какие в ней колонки.

— Найдите таблицу orders. Посмотрите, на какие таблицы она ссылается.

— Нарисуйте на бумаге или в заметках схему связей между таблицами.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— С помощью pgAdmin посмотрите структуру всех таблиц демо-схемы.

— Раскройте Schemas → public → Tables, изучите колонки каждой таблицы (имя, тип, ограничения). Объясните для себя, что значит каждая колонка и каждая таблица, для каких данных предназначена, какой тип данных используется.

Шаг 8. Учимся выбирать. Простой оператор SELECT

Выбираем очень много данных

ЖИВОЙ ПРИМЕР

В предыдущих главах мы разбирали структуру таблиц, типы данных и вставку информации. Но как эти данные оттуда достать?

Ведь даже если мы правильно создали таблицы и соблюли все правила, тонкости и нюансы, информация в базе останется просто бесполезной, если не показать её на экране.

Хорошая новость: в языке SQL есть замечательный оператор, который позволит нам не только извлекать данные, но и выполнять с ними сложные преобразования перед выводом.

Это оператор SELECT — и он просто незаменим.

Он почти магический: может быть как простым, так и многообразным. И, что важно, полностью удовлетворяет требованиям пользователя по выбору и обработке информации из базы.

Извлечение данных из базы называется запросом (query).

Постепенно мы научимся писать большие запросы с группировками, подзапросами и аналитическими функциями. Но мы пойдём тем же путём, что и всегда: начнём с самого простого.

Напишем простой оператор SELECT — запрос, который выберет все данные о клиентах нашей службы доставки.

Напоминаю: данные для демонстрационной схемы сгенерированы случайным образом.

Комментарии в коде

Комментарии не влияют на выполнение запроса, но помогают понять, что делает код.

Однострочный комментарий:

два символа минус подряд без пробела

sql

— –Это комментарий

SELECT * FROM flowers

Многострочный комментарий:

sql

Многострочный

комментарий

SELECT * FROM flowers;

Когда использовать:

— объяснить сложную логику

— временно отключить часть запроса

— оставить пометку для себя или коллег

ПРОСТОЙ SELECT

sql

SELECT phone, name FROM clients

Что тут происходит

— SELECT phone, name — перечисляем колонки, которые хотим получить

— FROM clients — указываем таблицу, из которой извлекаем данные

ПСЕВДОНИМЫ (АЛИАСЫ) КОЛОНОК

Иногда названия колонок в таблице слишком длинные или не совсем понятны в выводе.

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

Для этого используются псевдонимы (алиасы) — временные имена, которые мы назначаем колонкам.

Синтаксис:

sql

SELECT имя_колонки AS псевдоним

FROM имя_таблицы

Или без AS (сокращённо):

sql

SELECT имя_колонки псевдоним

FROM имя_таблицы

Пример:

sql

SELECT name AS flower_name, price AS flower_price

FROM flowers

Результат:

text

flower_name flower_price

Роза 150

Тюльпан 90

Хризантема 110

Лилия 130

Пион 200

Гербера 80

Орхидея 250

Ромашка 60

Псевдонимы flower_name и flower_price временно заменяют имена колонок в выводе

Правила наименования псевдонимов:

— Не должен содержать спецсимволы (пробелы, знаки препинания, кроме подчёркивания _)

— Не должен начинаться с цифры

— Не должен совпадать с зарезервированными командами PostgreSQL (SELECT, FROM, WHERE, AND, OR, NOT и т.д.)

Правильно:

sql

SELECT name AS flower_name, price AS flower_price FROM flowers;

SELECT name AS name_1, price AS price_1 FROM flowers;

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

sql

SELECT name AS flower name, price AS flower price FROM flowers; — содержит пробел

SELECT name AS 1name, price AS 2price FROM flowers; — начинается с цифры

SELECT name AS SELECT, price AS FROM FROM flowers; — зарезервированное слово

Если очень нужно использовать пробел или спецсимвол, можно взять псевдоним в двойные кавычки:

sql

SELECT name AS 'flower name', price AS 'flower price' FROM flowers;

Но на практике лучше так не делать — код становится менее читаемым.

ВЫБОР ВСЕХ КОЛОНОК (ЗВЁЗДОЧКА *)

Иногда нам требуется показать все данные из всех колонок некоторой таблицы. Для этого используется специальный символ * (звёздочка).

sql

SELECT * FROM cities

sql

SELECT * FROM colors

Звёздочка * заменяет перечень полей. Вместо того чтобы перечислять все колонки вручную, мы просто говорим: дай всё

РАБОТА В PGADMIN

Для выполнения запросов в pgAdmin используется специальный интерфейс Query Tool.

— Воспользуйтесь меню Tools → Query Tool

— Напишите запрос в открывшейся форме ввода

— Для выполнения нажмите кнопку в виде стрелки вправо или значка Play (>) в верхней части интерфейса

— Если у вас несколько запросов в окне, выделите нужный и нажмите Play — выполнится именно он

— После нажатия данные появятся в нижней части формы

ПРИМЕРЫ

Пример 1. Наименование и стоимость цветов

sql

SELECT name, price

FROM flowers

Результат (фрагмент):

text

name price

Роза 150.00

Тюльпан 90.00

Хризантема 110.00

…

Пример 2. Оттенки цветов

sql

SELECT name FROM colors

Пример 3. Города и население

sql

SELECT name, population

FROM cities

Результат (фрагмент):

text

name population

Москва 13000000

Санкт-Петербург 5600000

Казань 1300000

…
ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Путать колонки с одинаковыми именами из разных таблиц.

В примерах выше мы выбирали колонку name из таблиц flowers, colors и cities. Это колонки разных таблиц, и данные в них, соответственно, разные. Имя колонки само по себе не означает, что в ней хранится одно и то же.

Ошибка 2. Забыть точку с запятой в конце запроса.

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

ТОНКОСТИ И НЮАНСЫ

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

— Запросы SELECT можно писать как строчными, так и заглавными буквами — синтаксис PostgreSQL не имеет ограничений. Но рекомендуется выделять ключевые слова (SELECT, FROM, WHERE и другие) заглавными буквами. Это делает код более читабельным.

— Запросы можно переносить на несколько строк — синтаксис это допускает.

ИЗ ОПЫТА

Запросы SELECT можно писать как строчными, так и заглавными буквами — синтаксис PostgreSQL не имеет ограничений. Но я рекомендую выделять ключевые слова (SELECT, FROM, WHERE и другие) заглавными буквами.

Это делает код более читабельным, удобным для быстрого сканирования, а поиск ошибок становится заметно быстрее.

Также запросы можно переносить на несколько строк — синтаксис это допускает и даже приветствует.

ВОПРОСЫ И ОТВЕТЫ

В: Как посмотреть все колонки, которые есть в таблице, зная её название?

О: Используйте запрос:

sql

SELECT * FROM название_таблицы

Он покажет все данные, а заодно и все колонки — их названия будут в заголовках результата

ПОПРОБУЙ САМ

— Выберите названия и цены цветов из таблицы flowers. Используйте псевдонимы: flower_name и flower_price.

— Выберите из таблицы clients номера телефонов и имена клиентов. Дайте столбцам псевдонимы client_phone и client_name.

— Выберите все данные об оттенках.

— Выберите названия городов и численность населения.

— Выберите все данные о заказах.

— Выберите только даты заказов.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Изучите самостоятельно интерфейс программы pgAdmin.

Шаг 9. Куда же без WHERE?

Простые условия

ЖИВОЙ ПРИМЕР

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

Человек не в силах проанализировать такие объёмы информации вручную. Гораздо удобнее ограничить вывод строк некоторыми условиями.

Для этого служит оператор

WHERE

Именно с его помощью мы задаём условия отбора строк, которые должны попасть в результат запроса.

В условиях, заданных в операторе WHERE, можно использовать:

— математические выражения: +, -, *, /

— операции сравнения:>, <, =,!=,> =, <=

Синтаксис запроса с условием выглядит так:

sql

SELECT перечень_полей

FROM имя_таблицы

WHERE условие_отбора

Например, нам срочно нужно вывести только те строки из таблицы flowers, где цена цветка больше ста рублей:

sql

SELECT *

FROM flowers

WHERE price> 100

А теперь выведем данные из таблицы clients, где имя клиента — Андрей:

sql

SELECT *

FROM clients

WHERE name = 'Андрей'/* */

ПРИМЕРЫ

Пример 1. Выведем названия городов, где проживает более миллиона человек:

sql

SELECT name

FROM cities

WHERE population> 1000000

Выбираем названия городов, у которых население превышает 1 000 000 человек.

Результат:

text

name

Москва

Санкт-Петербург

Казань

…

Пермь

Пример 2. Выведем информацию о клиентах с именем Анна:

sql

SELECT *

FROM clients

WHERE name = 'Анна' /* */

Выбираем все колонки из таблицы clients, где имя клиента равно 'Анна'.

Результат:

text

phone name city_id birth_date

434567890 Анна 3 NULL

Пример 3. Узнаем стоимость цветка 'Ромашка':

sql

SELECT price

FROM flowers

WHERE name = 'Ромашка' /* */

Выбираем цену цветка, название которого 'Ромашка'.

Результат:

text

price

Пример 4. Выведем все цветы, которые стоят меньше ста рублей:

sql

SELECT *

FROM flowers

WHERE price <100

Выбираем все колонки из таблицы flowers, где цена меньше 100 рублей.

Результат:

text

id name price

2 Тюльпан 90

6 Гербера 80

8 Ромашка 60

Пример 5. Выведем заказы, где количество цветов больше десяти:

sql

SELECT *

FROM orders

WHERE quantity> 10

Выбираем все колонки из таблицы orders, где количество цветов больше 10.

Результат (фрагмент):

text

id client_phone flower_id color_code quantity order_ts status

5 434567890 2 re 11 2026-02-20 12:10:00 доставлен

9 437890123 7 bo 11 2026-02-26 15:55:00 доставлен

10 993456789 2 ye 11 2026-02-27 08:15:00 новый

13 434567890 8 wh 15 2026-03-03 12:00:00 новый

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Пропуск кавычек при работе со строками.

Неверно:

sql

SELECT *

FROM clients

WHERE name = Андрей

PostgreSQL воспримет Андрей как имя колонки, а не как значение.

Верно:

sql

SELECT *

FROM clients

WHERE name = 'Андрей' /* */

Ошибка 2. Лишние кавычки при работе с числами.

Неверно:

sql

SELECT *

FROM flowers

WHERE price> '100' /* */

Верно:

sql

SELECT *

FROM flowers

WHERE price> 100

Ошибка 3. Использование сравнений 'больше' или 'меньше' для строк.

Неверно:

sql

SELECT *

FROM clients

WHERE name> 'Виктор' /* */

Верно:

sql

SELECT *

FROM clients

WHERE name = 'Виктор' /* */

ИЛИ

sql

SELECT *

FROM clients

WHERE name!= 'Виктор' /* */

Важно помнить: номер телефона в нашей схеме тоже хранится как строка (тип TEXT). Это особенность нашей демонстрационной базы — в других системах может быть иначе.

ТОНКОСТИ И НЮАНСЫ

Часто нам нужны только первые несколько строк таблицы. Это бывает полезно при работе с большими объёмами данных или для быстрой проверки запроса.

В PostgreSQL для этого используется ключевое слово LIMIT:

sql

SELECT *

FROM clients

LIMIT 4

Можно сочетать LIMIT с условием WHERE:

sql

SELECT *

FROM cities

WHERE population> 100000

LIMIT 5

ИЗ ОПЫТА

Старайтесь объяснять самому себе, что делает каждый SQL-запрос, простыми словами. Например:

'Выбери все заказы, где количество больше десяти'.

Это не только ускоряет обучение, но и помогает быстрее находить ошибки.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли использовать несколько условий в WHERE?

О: Да, но мы рассмотрим это в следующем шаге.

В: Можно ли в условии отбора использовать колонки, которые не будут показаны в результате?

О: Конечно. Пример:

sql

SELECT status

FROM orders

WHERE quantity> 10

Здесь мы выводим только статус заказов, но условие накладывается на колонку quantity, которая в результат не попадает.

В: Как выбрать все значения, кроме заданного?

О: Используйте оператор неравенства!=. Например, чтобы выбрать всех клиентов, кроме Виктора:

sql

SELECT *

FROM clients

WHERE name!= 'Виктор' /* */

ПОПРОБУЙ САМ

— Выберите названия первых трёх городов, где население превышает один миллион человек.

— Выберите всю информацию о клиенте с номером телефона 993456789. Помните: номер телефона — строка, поэтому в условии WHERE его нужно заключать в кавычки.

— Выберите телефоны всех клиентов, кроме тех, кого зовут Ольга.

— Выберите все города, где население меньше полутора миллионов человек.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Выберите все данные о заказах из таблицы orders. Используйте SELECT *.

— Выберите названия и цены цветов из таблицы flowers. Используйте псевдонимы: flower_name и flower_price.

— Выберите из таблицы clients номера телефонов и имена клиентов. Дайте столбцам псевдонимы client_phone и client_name.

Шаг 10. Одно условие WHERE — скучно. Добавляем AND, OR, NOT, IN, BETWEEN

Когда одного условия становится мало

ЖИВОЙ ПРИМЕР

Вы уже умеете фильтровать данные с помощью WHERE. Но что, если нужно отобрать заказы со статусом 'новый' или 'в доставке'? Или найти города с населением от 1 до 5 миллионов? Или исключить клиентов с определёнными именами?

Для этого в SQL есть логические операторы AND, OR, NOT, а также операторы IN и BETWEEN. Они позволяют комбинировать условия и делать фильтрацию гибкой и точной.

AND — логическое И

AND выбирает строку только тогда, когда все условия верны.

sql

SELECT name, population

FROM cities

WHERE population> 1000000 AND population <1500000

Выводим города с населением больше миллиона и меньше полутора миллионов

sql

SELECT *

FROM orders

WHERE status = 'новый' AND quantity <15

Заказы со статусом 'новый' и количеством цветов меньше 15

OR — логическое ИЛИ

OR выбирает строку, если хотя бы одно из условий истинно.

sql

SELECT *

FROM orders

WHERE status = 'новый' OR status = 'в доставке' /* */

Заказы со статусом 'новый' или 'в доставке'

IN — проверка вхождения в список

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

sql

SELECT *

FROM flowers

WHERE name IN ('Хризантема', 'Тюльпан', 'Пион')

Цветы из списка: Хризантема, Тюльпан или Пион

sql

SELECT *

FROM clients

WHERE city_id IN (1, 2, 3)

Клиенты из городов с id 1, 2 или 3

BETWEEN — проверка диапазона

BETWEEN выбирает строки, где значение попадает в указанный диапазон (включая границы).

sql

SELECT name, population

FROM cities

WHERE population BETWEEN 1000000 AND 1500000

Города с населением от 1 миллиона до 1.5 миллионов (включительно)

sql

SELECT *

FROM orders

WHERE order_ts BETWEEN '2026-02-01'/* */

AND '2026-02-28'/* */

Заказы за февраль 2026 года

NOT — логическое отрицание

NOT делает условие противоположным. Используется с IN, BETWEEN, LIKE и другими операторами.

sql

SELECT *

FROM flowers

WHERE name NOT IN ('Хризантема', 'Тюльпан', 'Пион')

Все цветы, кроме перечисленных

sql

SELECT name, population

FROM cities

WHERE population NOT BETWEEN 1000000 AND 1500000

Города с населением меньше 1 миллиона или больше 1.5 миллионов

ПРИМЕРЫ С КОМБИНАЦИЯМИ

Пример 1. Заказы со статусом 'новый' или 'в доставке' с количеством меньше 10

sql

SELECT *

FROM orders

WHERE (status = 'новый' OR status = 'в доставке') AND quantity <10

Скобки определяют порядок вычисления условий

Результат (фрагмент):

text

id client_phone status quantity

17 112345678 в доставке 1

18 993456789 в доставке 3

…

Пример 2. Клиенты с именем Дмитрий, Анна или Алексей, а также клиент с телефоном 998901234

sql

SELECT *

FROM clients

WHERE name IN ('Дмитрий', 'Анна', 'Алексей') OR phone = '998901234' /* */

Результат (фрагмент):

text

phone name

112345678 Елена

434567890 Анна

993456789 Дмитрий

115678901 Алексей

998901234 Татьяна

…

Пример 3. Города с населением от 1 до 1.5 миллионов или более 5 миллионов

sql

SELECT name, population

FROM cities

WHERE population BETWEEN 1000000 AND 1500000

OR population> 5000000

Результат (фрагмент):

text

name population

Москва 13000000

Санкт-Петербург 5600000

Казань 1300000

Новосибирск 1600000

…
ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Путать AND и OR.

Неверно:

sql

SELECT * FROM clients WHERE name = 'Анна' AND name = 'Дмитрий' /* */

Ни одна строка не может быть одновременно Анной и Дмитрием

Верно:

sql

SELECT * FROM clients WHERE name = 'Анна' OR name = 'Дмитрий' /* */

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

Ошибка 2. Забывать скобки при смешивании AND и OR.

Неверно:

sql

SELECT * FROM orders WHERE status = 'новый' OR status = 'в доставке' AND quantity <10

AND имеет приоритет выше, чем OR. Без скобок условие интерпретируется как: status = 'новый' OR (status = 'в доставке' AND quantity <10)

Верно:

sql

SELECT * FROM orders WHERE (status = 'новый' OR status = 'в доставке') AND quantity <10

Ошибка 3. Путать IN со списком строк и BETWEEN с диапазоном.

IN работает с перечислением значений, BETWEEN — с диапазоном.

ТОНКОСТИ И НЮАНСЫ

— BETWEEN включает границы диапазона. WHERE date BETWEEN '2026-02-01' AND '2026-02-28' включает заказы за 1 и 28 февраля.

— Для работы с датами без времени используйте приведение типов: order_ts::date BETWEEN '2026-02-01' AND '2026-02-28' /* */

— IN можно использовать с подзапросами (об этом в следующих шагах)

— NOT IN с подзапросами, возвращающими NULL, может дать неожиданный результат (все строки будут исключены)

ИЗ ОПЫТА

Бывают задачи, когда из запроса необходимо убрать некий ненужный перечень значений. Для этого наиболее рационально использовать логический оператор NOT в сочетании с IN.

sql

SELECT *

FROM flowers

WHERE name NOT IN ('Ромашка', 'Тюльпан', 'Лилия')

А однажды я забыл скобки в сложном условии с AND и OR и получил не те данные, которые ожидал. С тех пор я всегда использую скобки, даже если кажется, что они не нужны.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли комбинировать более двух условий?

О: Да, можно комбинировать столько условий, сколько необходимо.

sql

SELECT * FROM orders

WHERE quantity> 10 AND status = 'новый' AND order_ts> '2026-03-01' /* */

В: Для чего используются скобки в логических выражениях?

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

В: Можно ли сочетать IN и BETWEEN?

О: Да.

sql

SELECT name, population

FROM cities

WHERE population BETWEEN 1000000 AND 1500000

AND name IN ('Казань', 'Новосибирск', 'Екатеринбург')

ПОПРОБУЙ САМ

— Выберите из таблицы городов (cities) все города, кроме Москвы, Казани, Новосибирска. Используйте NOT IN.

— Выберите из таблицы заказов (orders) только те заказы, где количество цветов больше 5 и меньше или равно 10. Используйте BETWEEN.

— Выберите одним запросом из таблицы клиентов всех клиентов, которых зовут Анна или Андрей, а также клиента с номером телефона 993456789.

— Выберите из таблицы оттенков (colors) красный, белый, жёлтый оттенки. Используйте OR.

— Выберите из таблицы цветов (flowers) цветы, которые стоят от 80 до 130 рублей, а также 'Орхидею'. Используйте BETWEEN и OR.

— Выберите из таблицы цветов (flowers) цветы, которые не стоят от 80 до 130 рублей. Используйте NOT BETWEEN.

— Выберите из таблицы заказов (orders) только те заказы, где количество цветов равно 3, 4 или 15 и статус 'новый'. Используйте IN и AND.

Шаг 11. WHERE и LIKE. Ищем неточное совпадение

Когда вроде бы точно знаешь, но не до конца

ЖИВОЙ ПРИМЕР

Иногда нам нужно найти что-то, но мы не знаем точного значения.

Например: 'Найди всех клиентов, у которых в имени есть буква „и“'.

Мы не знаем, какое имя, но знаем, что в нём есть эта буква.

Для таких случаев в SQL есть оператор LIKE.

Простой, но мощный оператор для работы с текстом.

Он ищет по шаблону (образцу), а не по точному совпадению.

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

В LIKE используются два специальных символа:

— % — соответствует любому количеству символов (даже нолю)

— _ — соответствует ровно одному символу

[***] — ШАБЛОНЫ LIKE (краткая таблица)

ПРИМЕРЫ

Пример 1. Выберите всех клиентов, чей телефон начинается с 9:

sql

SELECT *

FROM clients

WHERE phone LIKE '9%' /* */

Мы говорим базе: 'Найди всех, у кого телефон начинается с 9, а дальше может быть что угодно'.

Результат:

text

phone name city_id birth_date

991234567 Николай 1 1980-05-10

993456789 Дмитрий 1 1985-11-03

998901234 Татьяна 1 1988-12-01

991112233 Ирина 1 1985-06-15

Пример 2. Выберите всех клиентов, чей телефон заканчивается на 33:

sql

SELECT *

FROM clients

WHERE phone LIKE '%33' /* */

Шаблон '%33' означает: 'сначала любые символы, а в конце обязательно 33'.

Результат:

text

phone name city_id birth_date

991112233 Ирина 1 1985-06-15

Пример 3. Выберите всех клиентов, чьё имя содержит букву 'и':

sql

SELECT *

FROM clients

WHERE name LIKE '%и%' /* */

Шаблон '%и%' ищет букву 'и' в любом месте строки: до неё может быть что угодно, и после неё — тоже.

Результат:

text

phone name city_id birth_date

991234567 Николай 1 1980-05-10

993456789 Дмитрий 1 1985-11-03

310123456 Мария 6 1983-09-12

991112233 Ирина 1 1985-06-15

834445566 Виктор 4 NULL

Пример 4. Выберите города, в названии которых есть буква 'д' или 'Д':

sql

SELECT name, population

FROM cities

WHERE name LIKE '%д%' OR name LIKE '%Д%' /* */

LIKE чувствителен к регистру, поэтому нужны оба варианта.

Результат:

text

name population

Нижний Новгород 1200000

Ростов-на-Дону 1130000

Пример 5. Выберите города, в названии которых нет буквы 'у':

sql

SELECT name, population

FROM cities

WHERE name NOT LIKE '%у%' /* */

Оператор NOT LIKE исключает строки, которые соответствуют шаблону.

Результат:

text

name population

Москва 13000000

Казань 1300000

Новосибирск 1600000

Нижний Новгород 1200000

Екатеринбург 1500000

Пермь 1050000

Воронеж NULL

Саратов NULL

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Путать LIKE и регулярные выражения.

LIKE ищет по простым шаблонам с % и _. Регулярные выражения — это отдельный язык. Они не взаимозаменяемы.

Ошибка 2. Забыть про регистр.

LIKE чувствителен к регистру. Если ищете 'москва', а в базе 'Москва' — LIKE не найдёт. Для поиска без учёта регистра используйте ILIKE.

Ошибка 3. Слишком сложный шаблон.

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

ТОНКОСТИ И НЮАНСЫ

— LIKE работает только с текстовыми типами данных. Если применить к числу, PostgreSQL выдаст ошибку.

— ILIKE — версия LIKE без учёта регистра. Работает так же, но не чувствительна к регистру.

— Чтобы найти сам символ % или _, их нужно экранировать. Например, LIKE '100\%' найдёт строку 100%. По умолчанию символ экранирования — обратная косая черта.

ИЗ ОПЫТА

Однажды я искал заказы, у которых номер телефона начинался на определённые цифры.

Я использовал LIKE и получил ошибку, потому что забыл, что LIKE работает только с текстом.

Пришлось преобразовывать колонку через::text.

С тех пор, когда вижу LIKE и числа, сразу вспоминаю про преобразование типов.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли использовать LIKE с числами?

О: Нет. Нужно сначала преобразовать число в текст: phone::text LIKE '9%'.

В: Что ищет шаблон '%'?

О: Все строки (пустые и непустые). Потому что % соответствует любому количеству символов, включая ноль.

В: Как найти строку, содержащую символ %?

О: Экранировать: LIKE '100\%'. По умолчанию символ экранирования — \.

ПОПРОБУЙ САМ

— Найди всех клиентов, у которых в имени есть буква 'а'.

— Найди города, название которых начинается на 'Н'.

— Найди города, которые заканчиваются на 'бург'.

— Найди всех клиентов, у которых в номере телефона есть две одинаковые цифры подряд (например, 11, 22, 33).

— Подсказка: используй LIKE с % несколько раз.

Шаг 12. Запросы и вычисления

Математика, математикой, а запросы пишутся всегда

ЖИВОЙ ПРИМЕР

У вас есть таблица заказов. Вам нужно посчитать общую стоимость заказа, умножив количество цветов на их цену.

Цена хранится в одной таблице (flowers), количество — в другой (orders).

Как вывести результат прямо в запросе?

В SQL можно вычислять новые значения прямо в запросе.

Для этого используются:

— арифметические операции (+, -, *, /, %)

— математические функции (ABS, ROUND, POWER и другие)

— логические выражения (AND, OR, NOT)

АРИФМЕТИЧЕСКИЕ ОПЕРАЦИИ

ПРИМЕРЫ

Пример 1. Увеличить цену цветка на 10%

sql

SELECT name,

price,

price * 1.1 AS price_with_vat

FROM flowers;

Вычисляем цену с наценкой 10% и выводим в отдельной колонке.

Результат (фрагмент):

text

name price price_with_vat

Роза 150 165.00

Тюльпан 90 99.00

Пример 2. Посчитать стоимость заказа

sql

SELECT o.id,

o. quantity,

f.price,

o. quantity * f.price AS total

FROM orders o

JOIN flowers f ON o. flower_id = f.id;

Умножаем количество цветов на цену и выводим как отдельную колонку.

Пример 3. Остаток от деления (проверка на чётность)

sql

SELECT id,

quantity,

quantity % 2 AS remainder

FROM orders;

Вычисляем остаток от деления количества на 2.

Результат (фрагмент):

text

id quantity remainder

1 5 1

2 3 1

3 7 1

4 2 0

Пример 4. Сложное выражение в WHERE

sql

SELECT name, price

FROM flowers

WHERE price * 1.2> 200;

Отбираем цветы, цена которых с наценкой 20% превышает 200 рублей.

Пример 5. Использование математических функций

sql

SELECT name,

price,

ROUND (price * 0.9, 2) AS price_with_discount,

ABS (price — 100) AS diff_from_100

FROM flowers;

ROUND — округляет до двух знаков. ABS — возвращает абсолютное значение (модуль).

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Деление целых чисел.

В PostgreSQL деление целых чисел даёт целый результат (остаток отбрасывается).

Неверно:

sql

SELECT 5 / 2; — вернёт 2, а не 2.5

Верно:

sql

SELECT 5 / 2.0; — вернёт 2.5

Ошибка 2. Смешивать типы данных в вычислениях.

Если сложить число и текст, PostgreSQL выдаст ошибку.

Неверно:

sql

SELECT price + '10' FROM flowers;

Верно:

sql

SELECT price +10 FROM flowers;

Ошибка 3. Забывать, что NULL в вычислениях даёт NULL.

Если в вычислении участвует NULL, результат будет NULL.

ТОНКОСТИ И НЮАНСЫ

— Порядок операций такой же, как в математике: сначала умножение и деление, потом сложение и вычитание. Скобки меняют порядок.

— Математических функций в PostgreSQL очень много: ABS, CEIL, FLOOR, ROUND, POWER, SQRT, EXP, LOG, SIN, COS, TAN и другие.

— Логические выражения возвращают TRUE, FALSE или NULL.

— Вычисляемые колонки можно использовать в ORDER BY и GROUP BY.

ИЗ ОПЫТА

Однажды я считал стоимость заказа, но забыл, что цена хранится в NUMERIC, а количество — в INTEGER.

Всё работало, но когда я попытался разделить сумму на количество, получил неожиданный результат из-за целочисленного деления.

С тех пор я всегда привожу числа к NUMERIC перед делением.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли использовать вычисляемую колонку в WHERE?

О: Да, но нужно повторить выражение. Алиас в WHERE использовать нельзя.

sql

SELECT price * 1.1 AS price_with_vat

FROM flowers

WHERE price * 1.1> 200;

В: Как получить остаток от деления?

О: Используйте оператор %.

В: Что такое ROUND (price, 2)?

О: Округляет число до двух знаков после запятой.

ПОПРОБУЙ САМ

— Выведи цену цветка, увеличенную на 20%.

— Выведи стоимость заказа (количество × цена) для каждого заказа.

— Выведи только те заказы, где количество цветов нечётное.

— Найди цветы, цена которых после скидки 15% меньше 100 рублей.

— Выведи квадратный корень из цены для каждого цветка.

Шаг 13. А еще мы можем использовать подзапросы WHERE IN и WHERE EXISTS

Куда же без маленького запроса внутри большого

ЖИВОЙ ПРИМЕР

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

Но имя клиента хранится в другой таблице. Как это сделать?

В SQL для этого есть подзапросы — запросы внутри запросов.

Они позволяют использовать результат одного запроса в условии другого.

ПОДЗАПРОС С IN

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

Задача: вывести клиентов, которые живут в городах с населением больше 5 миллионов.

sql

SELECT name

FROM clients

WHERE city_id IN (SELECT id FROM cities WHERE population> 5000000)

Внутренний запрос находит ID городов-миллионников (Москва, Санкт-Петербург). Внешний запрос выбирает клиентов, чей city_id есть в этом списке.

ПОДЗАПРОС С EXISTS

EXISTS проверяет наличие строк в подзапросе. Он не сравнивает значения, а отвечает на вопрос: Есть ли хоть одна строка, которая подходит под условие?

Задача: вывести клиентов, у которых есть хотя бы один отменённый заказ.

sql

SELECT name

FROM clients c

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.client_phone = c.phone AND o.status = 'отменён')

Для каждого клиента выполняется подзапрос к таблице заказов. Если находится хотя бы один заказ с его телефоном и статусом 'отменён' — EXISTS возвращает TRUE. Такой клиент попадает в результат.

ПРИМЕРЫ

Пример 1. IN с текстовыми значениями

sql

SELECT name

FROM flowers

WHERE name IN ('Роза', 'Тюльпан', 'Лилия')

Что тут происходит

— IN ('Роза', 'Тюльпан', 'Лилия') — проверяет, содержится ли значение колонки name в указанном списке

— Запрос вернёт только те цветы, название которых совпадает с одним из трёх значений

— Это краткая форма записи вместо нескольких условий через OR

Результат:

text

name

Роза

Тюльпан

Лилия

Пример 2. EXISTS с дополнительным условием

sql

SELECT name

FROM clients c

WHERE EXISTS (

SELECT 1

FROM orders o

WHERE o.client_phone = c.phone

AND o. quantity> 10

)

Что тут происходит

— Внешний запрос перебирает всех клиентов из таблицы clients

— Для каждого клиента выполняется подзапрос, который ищет заказы этого клиента с количеством цветов больше 10

— Если подзапрос находит хотя бы один такой заказ — EXISTS возвращает TRUE

— Клиент попадает в результат, если EXISTS вернул TRUE

Результат (фрагмент):

text

name

Николай

Дмитрий

Анна

Пример 3. NOT EXISTS — клиенты без заказов

sql

SELECT name

FROM clients c

WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.client_phone = c.phone)

Что тут происходит

— NOT EXISTS — противоположность EXISTS

— Для каждого клиента выполняется подзапрос, который ищет его заказы

— Если подзапрос не находит ни одного заказа — NOT EXISTS возвращает TRUE

— В результат попадают только клиенты, у которых нет ни одного заказа

Результат (фрагмент):

text

name

Светлана

Виктор

Пример 4. NOT IN — цветы, которые не заказывали

sql

SELECT name

FROM flowers

WHERE id NOT IN (SELECT DISTINCT flower_id FROM orders)

Что тут происходит

— Внутренний подзапрос (SELECT DISTINCT flower_id FROM orders) возвращает список ID цветов, которые хоть раз заказывали

— NOT IN проверяет, что ID цветка не входит в этот список

— В результат попадают только цветы, которые ни разу не заказывали

Результат (фрагмент):

text

name

Гербера

Ромашка

Пример 5. Вложенный IN (два уровня)

sql

SELECT name

FROM clients

WHERE city_id IN (

SELECT id FROM cities

WHERE population> 1000000 AND name LIKE 'М%'/* */

)

Что тут происходит

— Самый глубокий подзапрос (SELECT id FROM cities WHERE population> 1000000 AND name LIKE 'М%') находит ID городов-миллионников, названия которых начинаются на букву 'М' (например, Москва)

— Результат этого подзапроса — список ID городов

— Внешний запрос выбирает клиентов, чей city_id есть в этом списке

Результат (фрагмент):

text

name

Николай

Дмитрий

Татьяна

В ЧЁМ РАЗНИЦА МЕЖДУ IN И EXISTS

ТОНКОСТИ И НЮАНСЫ

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

— IN требует, чтобы подзапрос возвращал ровно один столбец — это принципиальное отличие.

— Планировщик PostgreSQL часто оптимизирует IN и EXISTS одинаково, но при больших объёмах данных EXISTS может оказаться быстрее, потому что он останавливается после первого найденного совпадения.

ИЗ ОПЫТА

Однажды я писал отчёт по заказам и использовал IN с подзапросом, который возвращал 50 000 ID клиентов. Запрос выполнялся больше минуты.

Я заменил на EXISTS — время упало до 0.3 секунды.

Почему? EXISTS останавливается после первого же совпадения. IN сначала собирает весь список, а потом его проверяет.

С тех пор моё правило:

— короткие списки — IN

— проверка наличия связей — EXISTS

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли использовать IN с текстовыми значениями?

О: Да. Главное, чтобы типы в подзапросе и в основном запросе совпадали.

В: А если подзапрос ничего не вернул?

О: Тогда IN вернёт FALSE, и ни одна строка не будет выбрана. EXISTS тоже вернёт FALSE.

В: Какой оператор быстрее?

О: Зависит от данных. В большинстве случаев современный PostgreSQL оптимизирует оба варианта хорошо. Но если один из них работает медленно — попробуйте другой.

ПОПРОБУЙ САМ

— Выведи всех клиентов, которые заказывали цветы с ценой больше 200 рублей.

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

— Выведи всех клиентов, у которых есть заказы со статусом 'в доставке'.

Подсказка: используй EXISTS.

— Найди города, в которых нет ни одного клиента.

Подсказка: используй NOT EXISTS.

— Выведи названия цветов, которые ни разу не заказывали.

Подсказка: используй NOT IN.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Выведите цену цветка, увеличенную на 20%. (шаг 12)

— Выведите стоимость заказа (количество × цена) для каждого заказа. (шаг 12)

Шаг 14. Поговорим о сортировке ORDER BY

Как навести порядок во вселенском хаосе, или почему без ORDER BY нельзя

ЖИВОЙ ПРИМЕР

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

'Покажи всех клиентов'.

База честно выводит всех. Но в каком порядке?

А никаком. Просто в том, в каком они были добавлены. Это как если бы вам выдали телефонную книгу, где страницы перемешаны.

Чтобы этого избежать, в SQL есть сортировка. Оператор ORDER BY наводит порядок.

ПРОСТАЯ СОРТИРОВКА

Задача: вывести всех клиентов, отсортировав их по имени.

sql

SELECT name, phone

FROM clients

ORDER BY name;

Сортирует по полю name в алфавитном порядке (A → Я).

Результат (фрагмент):

text

name phone

Алексей 115678901

Андрей 439012345

Анна 434567890

Виктор 834445566

Дмитрий 993456789

…
СОРТИРОВКА ПО УБЫВАНИЮ

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

Задача: вывести всех клиентов, отсортированных по дате рождения от самых молодых к самым старшим.

sql

SELECT name, birth_date

FROM clients

WHERE birth_date IS NOT NULL

ORDER BY birth_date DESC;

DESC означает 'по убыванию'. Самые поздние даты (молодые) будут первыми.

СОРТИРОВКА ПО НЕСКОЛЬКИМ ПОЛЯМ

Иногда одного поля мало. Например: сначала все клиенты из Москвы, а внутри Москвы — по алфавиту.

Задача: вывести клиентов, сначала из Москвы, потом из Санкт-Петербурга, а внутри каждого города — по имени.

sql

SELECT name, city_id

FROM clients

ORDER BY city_id, name;

*Сначала сортируем по city_id (Москва — 1, Санкт-Петербург — 2), а внутри каждого города — по имени.*

СОРТИРОВКА ПО НОМЕРУ СТОЛБЦА

Вместо имени колонки можно указать её номер в списке SELECT.

sql

SELECT name, phone

FROM clients

ORDER BY 2;

2 означает 'второй столбец в SELECT' — то есть phone.

Внимание! Такой способ иногда используют, чтобы не писать длинные имена. Но он делает запрос менее понятным, поэтому лучше так не злоупотреблять.

СОРТИРОВКА И LIMIT

Очень часто сортировку используют вместе с LIMIT, чтобы выбрать 'топ' или 'первые'.

Задача: вывести трёх самых молодых клиентов.

sql

SELECT name, birth_date

FROM clients

WHERE birth_date IS NOT NULL

ORDER BY birth_date DESC

LIMIT 3;

Сначала убираем клиентов без даты рождения. Сортируем по дате рождения от молодых к старшим. Берём только первые три строки.

Задача: вывести пять самых больших заказов по количеству цветов.

sql

SELECT id, quantity

FROM orders

ORDER BY quantity DESC

LIMIT 5;

Сортируем заказы по количеству от большего к меньшему и берём первые пять.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Путать ASC и DESC.

sql

ORDER BY name DESC — сортировка от Я до А

ORDER BY name ASC — сортировка от А до Я (по умолчанию)

Ошибка 2. Забыть ORDER BY перед LIMIT.

sql

SELECT * FROM orders LIMIT 5

Это просто первые пять строк в том порядке, как они лежат в базе. Если нужен топ, обязательно нужен ORDER BY.

Ошибка 3. Сортировка по номеру столбца при изменении SELECT.

Если вы измените порядок колонок в SELECT, сортировка по номеру может перестать работать так, как ожидалось.

ТОНКОСТИ И НЮАНСЫ

Сортировка и NULL

NULL — это 'неизвестно'. В PostgreSQL при сортировке по возрастанию NULL идут в конце, при убывании — в начале.

Если нужно изменить это поведение, используют NULLS FIRST или NULLS LAST.

sql

SELECT name, birth_date

FROM clients

ORDER BY birth_date NULLS LAST;

ИЗ ОПЫТА

Однажды я писал отчёт, где нужно было вывести самые популярные цветы. Я отсортировал по количеству заказов, но забыл DESC. Получился список самых НЕпопулярных цветов. Клиент был в замешательстве.

С тех пор, когда вижу ORDER BY, всегда проверяю: А точно ли я хочу от меньшего к большему?

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли сортировать по нескольким полям с разным направлением?

О: Да. Например: сначала по городу по возрастанию, а внутри города — по имени по убыванию.

sql

ORDER BY city_id ASC, name DESC

В: Сортировка влияет на производительность?

О: Да. Если данных много, сортировка требует ресурсов. Иногда имеет смысл сортировать уже на стороне приложения.

В: Что будет, если не указать ORDER BY?

О: Порядок строк будет непредсказуемым. Обычно это порядок добавления, но гарантии нет. Поэтому если порядок важен — всегда указывайте ORDER BY.

ПОПРОБУЙ САМ

— Выведи список клиентов, отсортированный по дате рождения (от молодых к старшим).

— Выведи список городов, отсортированный по названию (от А до Я).

— Выведи список заказов, отсортированный сначала по статусу (новые, в доставке, доставленные, отменённые), а внутри статуса — по дате заказа.

— Выведи трёх клиентов с самой ранней датой рождения.

— Выведи пять самых больших заказов по количеству цветов.

Шаг 15. Больше подзапросов хороших и разных

Как заглянуть внутрь запроса

ЖИВОЙ ПРИМЕР

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

Название города хранится в другой таблице — cities. Как это сделать одним запросом?

С помощью подзапроса в SELECT.

ПСЕВДОНИМЫ ДЛЯ ПОДЗАПРОСОВ

Когда мы используем подзапрос в FROM, ему обязательно нужно дать имя — псевдоним.

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

sql

SELECT * FROM (SELECT name FROM clients) temp

temp — это псевдоним временной таблицы. Без него PostgreSQL выдаст ошибку.

Правила создания псевдонимов:

Верно: Правильно: client_names, client_names_1, client_data

Если очень нужно использовать пробел или спецсимвол, можно взять псевдоним в двойные кавычки:

sql

SELECT *

FROM (SELECT name FROM clients) ’client names'/* */

Но на практике лучше так не делать — код становится менее читаемым.

ПОДЗАПРОСЫ В SELECT

Подзапрос в SELECT возвращает одно значение для каждой строки внешнего запроса.

Задача: вывести имя клиента и название его города.

sql

SELECT name,

(SELECT name FROM cities WHERE id = c.city_id) AS city_name

FROM clients c

Что тут происходит

— Внешний запрос выбирает всех клиентов

— Для каждого клиента выполняется подзапрос, который находит название города по city_id

— Результат подзапроса выводится в отдельной колонке

Результат (фрагмент):

text

name city_name

Николай Москва

Елена Санкт-Петербург

Дмитрий Москва

Анна Казань

…

ПОДЗАПРОСЫ В FROM

Подзапрос в FROM создаёт временную таблицу, с которой можно работать как с обычной.

Задача: вывести клиентов из Москвы, используя подзапрос.

sql

SELECT name, city_name

FROM (SELECT name,

(SELECT name FROM cities WHERE id = c.city_id) AS city_name

FROM clients c) client_city

WHERE city_name = 'Москва'/* */

Что тут происходит

— Внутренний запрос (подзапрос в FROM) создаёт временную таблицу client_city с колонками name и city_name

— Внешний запрос выбирает из этой временной таблицы строки, где city_name = 'Москва' /* */

Результат (фрагмент):

text

name city_name

Николай Москва

Дмитрий Москва

Татьяна Москва

ВЛОЖЕННЫЕ ПОДЗАПРОСЫ

Подзапросы можно вкладывать друг в друга. Главное — не запутаться.

Задача: вывести клиентов, которые живут в том же городе, что и клиент с телефоном 991234567 (Николай).

sql

SELECT name

FROM clients

WHERE city_id = (SELECT city_id

FROM clients

WHERE phone = '991234567')

Что тут происходит

— Самый глубокий подзапрос находит city_id Николая

— Внешний запрос выбирает всех клиентов с таким же city_id

— Сам Николай тоже попадёт в результат — это нормально

Результат (фрагмент):

text

name

Николай

Дмитрий

Татьяна

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Подзапрос в SELECT возвращает несколько строк.

Неверно:

sql

SELECT name, (SELECT name FROM cities) FROM clients

Подзапрос вернёт много строк — получим ошибку.

Верно:

sql

SELECT name, (SELECT name FROM cities WHERE id = c.city_id) FROM clients c

Подзапрос должен возвращать одно значение.

Ошибка 2. Забыть псевдоним для подзапроса в FROM.

Неверно:

sql

SELECT * FROM (SELECT name, phone FROM clients)

Верно:

sql

SELECT * FROM (SELECT name, phone FROM clients) client_data

Ошибка 3. Подзапрос в WHERE возвращает несколько строк, а вы используете =.

Неверно:

sql

SELECT name FROM clients WHERE city_id = (SELECT id FROM cities WHERE population> 5000000)

Если городов с населением больше 5 миллионов несколько — ошибка.

Верно:

sql

SELECT name FROM clients WHERE city_id IN (SELECT id FROM cities WHERE population> 5000000)

ТОНКОСТИ И НЮАНСЫ

— Подзапрос в SELECT должен возвращать ровно одно значение.

— Подзапрос в FROM всегда должен иметь псевдоним (имя временной таблицы).

— Подзапрос в WHERE может возвращать несколько значений, если используется с IN, ANY, ALL.

— Подзапросы могут быть вложенными, но это усложняет чтение.

ИЗ ОПЫТА

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

Я использовал подзапрос в SELECT — и всё заработало.

А когда понадобилось отфильтровать клиентов по городу, я обернул этот запрос в подзапрос в FROM и наложил условие. Получилось громоздко, но работало.

С тех пор я знаю: если JOIN не подходит, подзапросы выручат.

ВОПРОСЫ И ОТВЕТЫ

В: Подзапрос в SELECT — это же медленно?

О: Может быть, если данных много. Но на небольших объёмах вполне приемлемо. Для больших объёмов лучше использовать JOIN.

В: Почему подзапрос в FROM требует псевдонима?

О: Потому что база данных должна знать, как обращаться к этой временной таблице. Псевдоним — это её имя.

В: Можно ли использовать подзапрос в WHERE с IN?

О: Да, это мы уже проходили.

ПОПРОБУЙ САМ

— Выведи имя клиента и название его города (используй подзапрос в SELECT).

— Выведи имена клиентов, которые живут в Москве, используя подзапрос в FROM.

— Найди клиентов, которые живут в том же городе, что и клиент с телефоном 993456789 (Дмитрий).

— Найди клиентов, которые живут в городах с населением больше 5 000 000.

Шаг 16. Преобразование типов данных в PostgreSQL Превращаем одно в другое

Из мухи в слона, или буквы в цифры, а цифры в… непечатные буквы

ЖИВОЙ ПРИМЕР

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

Но вот беда: дата заказа хранится как TIMESTAMP, а в отчёте нужно выбрать только те, где дата ровно 1 января.

Вы пишете:

sql

SELECT * FROM orders WHERE

order_ts = '2026-01-01' /* */

И получаете пустой результат. Почему? Потому что order_ts хранит ещё и время: 2026-01-01 10:15:00, 2026-01-01 14:30:00 — они не равны просто дате.

Что делать? Нужно превратить TIMESTAMP в DATE, чтобы сравнить только дату.

В SQL есть преобразование типов. Это как взять данные и временно 'надеть' на них другую обёртку, чтобы сравнить или обработать по-другому.

ЧТО ТАКОЕ ПРЕОБРАЗОВАНИЕ ТИПОВ

В PostgreSQL каждый столбец имеет тип: INTEGER, TEXT, DATE, TIMESTAMP и другие.

Иногда нужно временно изменить тип, чтобы:

— сравнить дату без времени

— применить текстовую функцию к числу

— выполнить арифметику с текстовым числом

— найти все заказы по части даты (год, месяц) с помощью LIKE

Для этого используется приведение типов (casting).

КАК ЭТО ДЕЛАЕТСЯ

Синтаксис 1:

sql

SELECT выражение::новый_тип

Синтаксис 2:

sql

SELECT CAST (выражение AS новый_тип)

Оба способа работают одинаково.

ФУНКЦИИ TO_CHAR И TO_DATE

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

TO_CHAR — преобразует дату, время или число в строку по заданному формату.

sql

SELECT order_ts,

TO_CHAR (order_ts, 'DD.MM.YYYY') AS date_only,

TO_CHAR (order_ts, 'HH24:MI: SS') AS time_only

FROM orders;

Превращает дату и время в строку с нужным форматом.

Результат (фрагмент):

text

order_ts date_only time_only

2026-02-10 10:15:00 10.02.2026 10:15:00

2026-02-12 14:30:00 12.02.2026 14:30:00

TO_DATE — преобразует строку в дату по заданному формату.

sql

SELECT TO_DATE ('15.01.2025', 'DD.MM.YYYY') AS converted_date;

Превращает строку '15.01.2025' в дату.

Популярные форматы для TO_CHAR и TO_DATE:

ПРИМЕРЫ

Пример 1. Превратить TIMESTAMP в DATE, чтобы сравнить с датой.

sql

SELECT id, order_ts

FROM orders

WHERE order_ts::date = '2026-01-01' /* */

order_ts::date отбрасывает время, оставляя только дату. Теперь сравнение с '2026-01-01' работает.

Пример 2. Превратить дату в текст и найти заказы по году с помощью LIKE.

sql

SELECT id, order_ts

FROM orders

WHERE order_ts::text LIKE '2026%' /* */

order_ts::text превращает дату и время в строку вида '2026-02-10 10:15:00». LIKE '2026%' находит все заказы, где год начинается с 2026.

Пример 3. Превратить дату в текст и найти заказы за февраль любого года.

sql

SELECT id, order_ts

FROM orders

WHERE order_ts::text LIKE '%-02-%' /* */

Ищем в строковом представлении даты подстроку -02- (февраль).

Пример 4. Превратить число в текст и применить LIKE.

sql

SELECT phone

FROM clients

WHERE phone::text LIKE '9%' /* */

Если бы phone был числом,::text превратил бы его в строку, и LIKE начал бы работать.

Пример 5. Превратить текст в число и выполнить арифметику.

sql

SELECT price, price::integer +100 AS price_plus_100

FROM flowers

price хранится как NUMERIC.::integer превращает его в целое (отбрасывая копейки), и мы прибавляем 100.

Пример 6. Превратить текст в дату (если формат подходящий).

sql

SELECT '2025-01-15':: date AS new_date

Пример 7. Извлечь год из даты и сравнить с числом.

sql

SELECT id, order_ts

FROM orders

WHERE EXTRACT (YEAR FROM order_ts)::integer = 2026

EXTRACT (YEAR FROM order_ts) возвращает год как NUMERIC.::integer превращает его в целое число для сравнения.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Пытаться превратить текст в число, когда в тексте есть буквы.

sql

SELECT 'abc'::integer

Ошибка: неверный синтаксис для целого числа.

Ошибка 2. Пытаться превратить дату в текст и потом обратно, но с неправильным форматом.

sql

SELECT '2025-13-01':: date

Ошибка: месяца 13 не существует.

Ошибка 3. Забыть, что преобразование не меняет тип в таблице, а работает только для этого запроса.

ТОНКОСТИ И НЮАНСЫ

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

— PostgreSQL умный — иногда преобразует типы сам, но лучше делать явно.

— Для дат и времени есть специальные функции (EXTRACT, DATE_PART), которые часто удобнее, чем преобразование в текст.

— Превращение даты в текст + LIKE — мощный приём, когда нужно искать заказы по году, месяцу или любой части даты.

ИЗ ОПЫТА

Однажды я искал заказы, сделанные 1 января, и долго не мог понять, почему запрос ничего не находит. Потом вспомнил, что в TIMESTAMP есть время.

С тех пор, когда нужно сравнить только дату, я всегда пишу order_ts::date = '2026-01-01'.

А однажды мне понадобилось найти все заказы за февраль любого года. Я превратил дату в текст и использовал LIKE '%-02-%' — сработало идеально.

ВОПРОСЫ И ОТВЕТЫ

В: Чем отличаются::text и CAST (… AS text)?

О: Ничем. Это два способа сделать одно и то же.:: короче, CAST более явный.

В: Можно ли превратить текст в дату, если он в формате '15.01.2025'?

О: Да, но нужно указать формат с помощью TO_DATE:

sql

SELECT TO_DATE ('15.01.2025', 'DD.MM.YYYY')

В: Что будет, если преобразовать число с копейками в целое?

О: Дробная часть отбросится (не округлится, а просто отсечётся).

В: Зачем превращать дату в текст и искать с помощью LIKE?

О: Это удобно, когда нужно найти заказы по части даты: по году, месяцу, или когда дата хранится в нестандартном формате.

ПОПРОБУЙ САМ

— Преврати цену цветка в целое число и выведи вместе с исходной ценой.

— Найди заказы, сделанные 1 января 2026 года, используя преобразование TIMESTAMP в DATE.

— Преврати строку '2025-06-15' в дату и выведи.

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

— Найди заказы, сделанные в декабре любого года.

Шаг 17. Что такое регулярные выражения и с чем их готовить в PostgreSQL

Регулярный поиск — это регулярные выражения, а регулярные выражения — всегда регулярный поиск

ЖИВОЙ ПРИМЕР

Представьте, что вы ищете в базе данных записи, где есть слово 'срочно'.

Но люди пишут по-разному: 'СРОЧНО!', 'срочно доставить', 'Очень срочно, пожалуйста'.

Как найти всё это одним запросом?

LIKE здесь не справится — он умеет только простые шаблоны с % и _.

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

ЧТО ТАКОЕ РЕГУЛЯРНЫЕ ВЫРАЖЕНИЯ (ПРОСТЫМИ СЛОВАМИ)

LIKE говорит: 'найди текст, где есть эта подстрока, а вокруг может быть что угодно'.

Регулярное выражение говорит: 'найди текст, который соответствует вот такой структуре'.

— LIKE — грубая маска: % = что угодно, _ = один символ.

— Регулярка — точный конструктор: [abc] = один из символов a, b или c; {3} = ровно три раза; [0—9] {3} = три цифры подряд.

ПОЧЕМУ ОДНОГО LIKE НЕДОСТАТОЧНО

LIKE хорош для простых вещей:

— найти все имена, начинающиеся на 'А'.

— найти телефоны, заканчивающиеся на '33'.

Но LIKE не умеет:

— искать независимо от регистра

— искать по сложным правилам (например, 'три цифры подряд')

— проверять, что строка начинается с цифры и заканчивается буквой

— искать даты в тексте

Для этого нужны регулярные выражения.

КАК СТРОИТЬ РЕГУЛЯРНЫЕ ВЫРАЖЕНИЯ (КОРОТКО)

ПРИМЕРЫ

Пример 1. Найти всех клиентов, у которых в имени есть буква 'а' (без учёта регистра).

sql

SELECT name

FROM clients

WHERE name ~* 'a' /* */

Что тут происходит

— ~* — оператор регулярного выражения, который ищет совпадение без учёта регистра

— 'а' — шаблон: буква 'а' в любом регистре (а, А)

— Запрос вернёт всех клиентов, у которых в имени есть буква 'а' (например, Николай, Дмитрий, Мария, Ирина, Виктор)

Результат (фрагмент):

text

Николай

Дмитрий

Мария

Ирина

Виктор

Пример 2. Найти всех клиентов, у которых имя начинается с 'А' или 'а'.

sql

SELECT name

FROM clients

WHERE name ~* '^а'/* */

Что тут происходит

— ~* — без учёта регистра

— ^ — якорь начала строки (имя должно начинаться с указанного символа)

— '^а' — ищем имена, которые начинаются на букву 'а' в любом регистре

— Запрос вернёт: Алексей, Андрей, Анна

Результат:

text

Алексей

Андрей

Анна

Пример 3. Найти всех клиентов, у которых имя заканчивается на 'ий'.

sql

SELECT name

FROM clients

WHERE name ~ 'ий$' /* */

Что тут происходит

— ~ — оператор с учётом регистра (для русских букв это важно)

— $ — якорь конца строки

— 'ий$' — ищем имена, которые заканчиваются на ий

— Запрос вернёт: Дмитрий, Николай

Результат:

text

Дмитрий

Николай

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

sql

SELECT name

FROM cities

WHERE name ~'(.) \1'/* */

Что тут происходит

— (.) — точка в скобках означает 'любой символ'. Скобки запоминают этот символ

— \1 — ссылка на первый запомненный символ (означает 'такой же символ')

— Вместе (.) \1 означает: любой символ, за которым сразу идёт такой же символ

Результат (фрагмент):

text

Санкт-Петербург

Ростов-на-Дону

Пример 5. Найти клиентов, у которых в телефоне есть три одинаковые цифры подряд.

sql

SELECT name, phone

FROM clients

WHERE phone ~' ([0—9]) \1\1'/* */

Что тут происходит

— [0—9] — любая цифра от 0 до 9

— ([0—9]) — скобки запоминают эту цифру

— \1\1 — ссылка на запомненную цифру, повторённая дважды (всего три одинаковых цифры подряд)

— Например: 111, 222, 333 и т. д.

Результат (фрагмент):

text

Ирина 991112233

Виктор 834445566

ФУНКЦИИ ДЛЯ РАБОТЫ С РЕГУЛЯРНЫМИ ВЫРАЖЕНИЯМИ

Примеры с функциями:

1. regexp_match — извлечь первую цифру из строки

sql

SELECT regexp_match ('Заказ №12345','[0—9] +') AS first_number;

Возвращает первое совпадение с шаблоном (одну или несколько цифр).

Результат: {12345}

2. regexp_matches — извлечь все цифры из строки

sql

SELECT regexp_matches ('abc123def456','[0—9] +', 'g') AS all_numbers;

Флаг 'g’ означает 'глобальный поиск' — все совпадения.

Результат: {123}, {456}

3. regexp_replace — заменить все цифры на звёздочки

sql

SELECT regexp_replace ('abc123def456','[0—9]', '*', 'g') AS replaced;

Заменяет каждую цифру на *.

Результат: abc***def***

4. regexp_split_to_table — разбить строку по запятой

sql

SELECT regexp_split_to_table ('Роза, Тюльпан, Лилия',',') AS flower;

Результат:

text

flower

Роза

Тюльпан

Лилия

5. regexp_split_to_array — разбить строку на массив

sql

SELECT regexp_split_to_array ('Роза, Тюльпан, Лилия',',') AS flowers_array;

Результат: {Роза, Тюльпан, Лилия}

6. regexp_match — извлечь код города из телефонного номера (первые 3 цифры)

sql

SELECT regexp_match ('991234567', '^ [0—9] {3}') AS city_code;

^ — начало строки, [0—9] {3} — три цифры.

Результат: {991}

7. regexp_replace — отформатировать номер телефона

sql

SELECT regexp_replace ('991234567','([0—9] {3}) ([0—9] {3}) ([0—9] {3})', '+7-\1-\2-\3') AS formatted_phone;

Разбивает номер на группы и добавляет +7 и дефисы.

Результат: +7-991-234-567

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Путать LIKE и регулярные выражения.

LIKE ищет по простым шаблонам с % и _. Регулярные выражения — это отдельный язык. Они не взаимозаменяемы.

Ошибка 2. Забыть про регистр.

~ учитывает регистр, ~* — нет. Если ищете 'москва', а в базе 'Москва' — ~ не найдёт.

Ошибка 3. Слишком сложный шаблон.

Новички часто пишут огромные регулярные выражения, которые потом сложно читать и править. Лучше разбить задачу на несколько шагов.

ТОНКОСТИ И НЮАНСЫ

— Регулярные выражения в PostgreSQL используют синтаксис POSIX. Для поиска цифр используйте [0—9] или [:digit: ].

— Флаг 'g' в regexp_matches включает глобальный поиск (все совпадения). Без него возвращается только первое.

— regexp_match и regexp_matches возвращают массив. Чтобы получить текст, можно обратиться к первому элементу: (regexp_match (…)) [1].

ИЗ ОПЫТА

Однажды я искал заказы, где в комментарии был номер телефона. Я написал регулярное выражение'[0—9] {11}' — и нашёл всё. Но потом оказалось, что номера бывают и с пробелами, и с дефисами.

Пришлось переписать на'[0—9\-] {11,}' — это было сложно, но работало.

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

ВОПРОСЫ И ОТВЕТЫ

В: Чем отличаются ~ и ~*?

О: ~ учитывает регистр, ~* — нет.

В: Можно ли использовать регулярные выражения с LIKE?

О: Нет, это разные инструменты. Но есть оператор SIMILAR TO, который что-то среднее. Но его используют редко.

В: Как искать цифры?

О: Используйте [0—9] или [:digit: ].

В: Чем regexp_match отличается от regexp_matches?

О: regexp_match возвращает первое совпадение, regexp_matches — все. regexp_matches обычно используют с флагом 'g'.

ПОПРОБУЙ САМ

— Найди всех клиентов, у которых в имени есть буква 'о'.

— Найди всех клиентов, чьё имя начинается с 'А'.

— Найди всех клиентов, чьё имя заканчивается на 'ий'.

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

— Найди клиентов, у которых в телефоне есть три одинаковые цифры подряд.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Выведите имя клиента и название его города, используя подзапрос в SELECT. (шаг 15)

— Найдите клиентов, которые живут в городах с населением больше 5 000 000, используя подзапрос в WHERE с IN. (шаг 15)

— Преобразуйте дату заказа в текст в формате DD.MM.YYYY. Используйте TO_CHAR. (шаг 16)

Шаг 18. Строка — это не просто строка. Функции работы со строками

Инструкция как резать, клеить и заменять

ЖИВОЙ ПРИМЕР

Вы работаете с таблицей клиентов. Имена клиентов в таблице хранятся в разном регистре: 'НИКОЛАЙ', 'елена', 'Анна', 'дмитрий', и некоторые имена содержат лишние пробелы в конце.

Предположим, что вам нужно вывести список имён красиво — с большой буквы, остальные — маленькие, и убрать лишние пробелы в конце.

В языке SQL реализовано множество функций для удобной работы со строковыми данными, которые могут:

— менять регистр

— обрезать лишние пробелы

— соединять строки

— вырезать часть строки

— заменять одно на другое

ОСНОВНЫЕ СТРОКОВЫЕ ФУНКЦИИ

ПРИМЕРЫ

Пример 1. Привести имена клиентов к единому формату (первая буква заглавная), исключая тех, чьи имена начинаются на 'А'.

sql

SELECT name,

INITCAP (name) AS name_normalized

FROM clients

WHERE name NOT LIKE 'А%'/* */

INITCAP делает первую букву заглавной, остальные — строчными.

Результат (фрагмент):

text

name name_normalized

НИКОЛАЙ Николай

елена Елена

дмитрий Дмитрий

Пример 2. Вывести имена клиентов в верхнем и нижнем регистре для тех, у кого длина имени больше 5 символов.

sql

SELECT name,

UPPER (name) AS name_upper,

LOWER (name) AS name_lower

FROM clients

WHERE LENGTH (name)> 5

UPPER и LOWER меняют регистр строки. WHERE LENGTH (name)> 5 оставляет только имена длиннее 5 символов.

Результат (фрагмент):

text

name name_upper name_lower

Николай НИКОЛАЙ николай

Дмитрий ДМИТРИЙ дмитрий

Пример 3. Соединить имя клиента и его номер телефона, но только для клиентов из Москвы или Санкт-Петербурга.

sql

SELECT CONCAT (name, ' (тел:', phone,')') AS contact

FROM clients

WHERE city_id IN (1, 2)

CONCAT соединяет строки.

Результат (фрагмент):

text

contact

Николай (тел: 991234567)

Елена (тел: 112345678)

Дмитрий (тел: 993456789)

Пример 4. Найти длину имени каждого клиента, у которого цена любимого цветка (условно) между 100 и 200 руб.

sql

SELECT DISTINCT c.name,

LENGTH(c.name) AS name_length

FROM clients c

JOIN orders o ON c.phone = o.client_phone

JOIN flowers f ON o. flower_id = f.id

WHERE f.price BETWEEN 100 AND 200

LENGTH возвращает длину имени.

Пример 5. Обрезать лишние пробелы в именах клиентов (если бы они были).

sql

SELECT TRIM (name) AS trimmed_name

FROM clients

WHERE name LIKE '% ' OR name LIKE ' %'/* */

TRIM удаляет пробелы в начале и конце.

Пример 6. Вырезать первые три символа из названия города, но только для городов с населением больше 1 000 000.

sql

SELECT name,

SUBSTRING (name FROM 1 FOR 3) AS short_name

FROM cities

WHERE population> 1000000

Результат (фрагмент):

text

name short_name

Москва Мос

Санкт-Петербург Сан

Казань Каз

Новосибирск Нов

Пример 7. Заменить в названиях городов 'бург' на 'град', но только для городов, где население указано.

sql

SELECT name,

REPLACE (name, 'бург', 'град') AS new_name

FROM cities

WHERE name LIKE '%бург%' AND population IS NOT NULL

Результат (фрагмент):

text

name new_name

Санкт-Петербург Санкт-Петерград

Екатеринбург Екатеринград

Пример 8. Найти позицию буквы 'о' в названии города, но только для городов, название которых начинается с буквы 'Н'.

sql

SELECT name,

POSITION ('о' IN name) AS pos

FROM cities

WHERE name LIKE 'Н%' /* */

Результат (фрагмент):

text

name pos

Новосибирск 2

Нижний Новгород 2

Пример 9. Сформировать email из имени и фамилии (условно), заменив пробелы на точки и приведя к нижнему регистру.

sql

SELECT full_name,

LOWER (REPLACE (full_name, '','.')) || '@example.com' AS email

FROM clients

WHERE full_name IS NOT NULL

REPLACE заменяет пробел на точку. LOWER приводит всё к нижнему регистру. Оператор || добавляет домен.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Путать LENGTH и CHAR_LENGTH.

В PostgreSQL для строк в многобайтовых кодировках (например, UTF-8) LENGTH и CHAR_LENGTH возвращают одно и то же — количество символов.

Ошибка 2. Забывать, что строки в SQL чувствительны к регистру.

sql

SELECT * FROM clients WHERE name = 'николай' /* */

Не найдёт 'Николай'. Нужно либо привести к одному регистру:

sql

SELECT * FROM clients WHERE

LOWER (name) = 'николай' /* */

ТОНКОСТИ И НЮАНСЫ

— Соединение строк — оператор || работает быстрее, чем CONCAT, но CONCAT удобнее, когда нужно соединить много частей.

— Обрезка пробелов — TRIM удаляет только пробелы в начале и конце. Если нужно удалить все пробелы внутри строки, потребуется REPLACE.

— Регистр — INITCAP работает с пробелами, переводит первую букву каждого слова в заглавную.

sql

SELECT INITCAP ('пРИВЕТ МИР') AS capitalized

ИЗ ОПЫТА

В своей работе я часто встречаюсь с различными преобразованиями текста. В SQL есть удобная функция SUBSTRING, которую я использую чаще всего.

Пример 1. Выделить код города из телефонного номера.

sql

SELECT phone,

SUBSTRING (phone FROM 1 FOR 3) AS city_code

FROM clients

Пример 2. Извлечь имя из полного имени (условно).

sql

SELECT full_name,

SUBSTRING (full_name FROM POSITION (' ' IN full_name) +1) AS first_name

FROM clients

WHERE full_name LIKE '% %' /* */

Пример 3. Получить год из даты, если она хранится как строка.

sql

SELECT date_string,

SUBSTRING (date_string FROM 1 FOR 4) AS year

FROM orders

Пример 4. Выбрать последние 4 символа строки.

sql

SELECT phone,

SUBSTRING (phone FROM -4) AS last_four

FROM clients

*FROM -4 означает 'начать с 4-го символа с конца'. *

Пример 5. Выбрать символы с 3-го с конца по 5-й с конца.

sql

SELECT phone,

SUBSTRING (phone FROM -5 FOR 3) AS middle_from_end

FROM clients

ВОПРОСЫ И ОТВЕТЫ

В: Как удалить все пробелы из строки?

О: Используйте REPLACE (строка, '', '').

В: Как проверить, что строка начинается с определённого символа?

О: Используйте LIKE 'A%' или SUBSTRING (строка FROM 1 FOR 1) = 'A'.

В: Что быстрее — CONCAT или ||?

О: На практике разница незаметна. Выбирайте то, что вам понятнее.

ПОПРОБУЙ САМ

— Выведи имена клиентов в нижнем регистре.

— Соедини название цветка и его цену в одну строку (например, 'Роза — 150 руб.').

— Замени в названиях цветов букву 'о' на 'а' (например, Роза → Раза).

— Найди позицию буквы 'а' в названиях цветов.

— Извлеки последние 3 цифры из номера телефона клиента.

— Вырежи из названия города символы с 3-го по 5-й.

— Сформируй email из имени клиента, добавив @example.com и приведя всё к нижнему регистру.

Шаг 19. Разбираем функции работы с календарём, датой и временем

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

ЖИВОЙ ПРИМЕР

Вы работаете с таблицей заказов. Вам нужно найти все заказы, сделанные в прошлом месяце.

Дата заказа хранится в формате TIMESTAMP. Как вычислить 'прошлый месяц' без ручного ввода дат?

Такие задачи возникают в работе почти каждый день.

В SQL есть множество функций для работы с датами и временем:

— получать текущую дату и время

— извлекать год, месяц, день, час

— складывать и вычитать интервалы

— сравнивать даты

— форматировать вывод

ОСНОВНЫЕ ТИПЫ ДАТ И ВРЕМЕНИ

ОСНОВНЫЕ ФУНКЦИИ ДЛЯ РАБОТЫ С ДАТАМИ И ВРЕМЕНЕМ

РАЗНИЦА МЕЖДУ ДАТАМИ (В ДНЯХ, ЧАСАХ, МЕСЯЦАХ)

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

Разница в днях (простое вычитание):

sql

SELECT ('2026-03-23':: date — '2026-03-01':: date) AS days_diff;

Результат: 22 дня.

Разница в днях между TIMESTAMP (с учётом времени):

sql

SELECT ('2026-03-23 14:30:00':: timestamp — '2026-03-20 10:15:00':: timestamp) AS interval_diff;

Результат: 3 days 04:15:00.

Извлечение количества дней из интервала:

sql

SELECT EXTRACT (DAY FROM ('2026-03-23':: date — '2026-03-01':: date)) AS days;

Разница в часах:

sql

SELECT EXTRACT (EPOCH FROM ('2026-03-23 14:00:00':: timestamp — '2026-03-23 10:00:00':: timestamp)) / 3600 AS hours_diff;

EPOCH переводит интервал в секунды. Делим на 3600 — получаем часы.

Разница в месяцах с помощью AGE:

sql

SELECT AGE ('2026-12-31':: date, '2026-01-01':: date) AS age_diff;

Результат: 11 mons 30 days.

Разница только в месяцах (без дней):

sql

SELECT EXTRACT (YEAR FROM AGE ('2026-12-31', '2026-01-01')) * 12 +

EXTRACT (MONTH FROM AGE ('2026-12-31', '2026-01-01')) AS total_months;

Переводим разницу в полные месяцы.

Количество дней между датами (альтернативный способ):

sql

SELECT date_part ('day', '2026-03-23':: date — '2026-03-01':: date) AS days;

ПРИМЕРЫ

Пример 1. Найти заказы, сделанные в текущем месяце.

sql

SELECT id, order_ts

FROM orders

WHERE DATE_TRUNC ('month', order_ts) = DATE_TRUNC ('month', CURRENT_DATE)

DATE_TRUNC ('month', …) обрезает дату до первого дня месяца. Сравниваем месяц заказа с текущим месяцем.

Пример 2. Найти заказы, сделанные в прошлом месяце.

sql

SELECT id, order_ts

FROM orders

WHERE DATE_TRUNC ('month', order_ts) = DATE_TRUNC ('month', CURRENT_DATE — INTERVAL '1 month')

Вычитаем один месяц из текущей даты, затем обрезаем до первого дня месяца.

Пример 3. Найти заказы, сделанные в прошлом году.

sql

SELECT id, order_ts

FROM orders

WHERE EXTRACT (YEAR FROM order_ts) = EXTRACT (YEAR FROM CURRENT_DATE) — 1

EXTRACT (YEAR FROM …) извлекает год. Сравниваем с предыдущим годом.

Пример 4. Добавить к дате заказа 3 дня.

sql

SELECT id,

order_ts,

order_ts + INTERVAL '3 days' AS plus_3_days

FROM orders

Результат (фрагмент):

text

id order_ts plus_3_days

1 2026-02-10 10:15:00 2026-02-13 10:15:00

2 2026-02-12 14:30:00 2026-02-15 14:30:00

Пример 5. Вывести возраст клиентов на текущую дату.

sql

SELECT name,

birth_date,

AGE (birth_date) AS age

FROM clients

WHERE birth_date IS NOT NULL

AGE (birth_date) возвращает возраст в формате '45 years 10 mons 13 days’.

Результат (фрагмент):

text

name birth_date age

Николай 1980-05-10 45 years 10 mons 13 days

Елена 1992-08-22 33 years 7 mons 1 day

Пример 6. Вывести возраст клиентов в годах (целое число).

sql

SELECT name,

birth_date,

EXTRACT (YEAR FROM AGE (birth_date)) AS age_years

FROM clients

WHERE birth_date IS NOT NULL

Результат (фрагмент):

text

name birth_date age_years

Николай 1980-05-10 45

Елена 1992-08-22 33

Пример 7. Вывести заказы, сделанные в выходные дни.

sql

SELECT id, order_ts,

EXTRACT (DOW FROM order_ts) AS day_of_week

FROM orders

WHERE EXTRACT (DOW FROM order_ts) IN (0, 6)

EXTRACT (DOW FROM …)

возвращает день недели: 0 — воскресенье, 6 — суббота.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Путать DATE и TIMESTAMP.

Сравнение order_ts = '2026-03-23' не найдёт заказы, сделанные в этот день, если в order_ts есть время. Нужно либо использовать::date, либо диапазон:

sql

WHERE order_ts::date = '2026-03-23' /* */

— или

WHERE order_ts> = '2026-03-23' AND order_ts <'2026-03-24' /* */

Ошибка 2. Забывать, что EXTRACT (DOW FROM …) возвращает 0 для воскресенья (в PostgreSQL). В других СУБД может быть 1 для воскресенья.

Ошибка 3. Сравнивать интервалы с числами.

AGE (birth_date)> 18 не работает. Нужно сравнивать годы:

sql

WHERE EXTRACT (YEAR FROM AGE (birth_date))> 18

ТОНКОСТИ И НЮАНСЫ

— DATE_TRUNC удобна для группировок по дням, месяцам, годам.

— EXTRACT возвращает числа, их можно использовать в арифметике.

— INTERVAL можно складывать и вычитать из дат.

— TO_CHAR позволяет выводить даты в любом формате, включая русские названия месяцев (если настроена локаль).

ИЗ ОПЫТА

Однажды мне нужно было построить отчёт по заказам за последний квартал. Я написал:

sql

SELECT *

FROM orders

WHERE order_ts> = DATE_TRUNC ('quarter', CURRENT_DATE) — INTERVAL '3 months’

И забыл, что DATE_TRUNC ('quarter', CURRENT_DATE) возвращает начало текущего квартала, а не прошлого. Пришлось добавить ещё один — INTERVAL '3 months'.

С тех пор я тестирую такие запросы на небольшой выборке, прежде чем запускать на всех данных.

ВОПРОСЫ И ОТВЕТЫ

В: Как узнать возраст клиента в годах, не глядя на месяцы и дни?

О: Используйте EXTRACT (YEAR FROM AGE (birth_date)). Это вернёт целое число — сколько лет человеку.

В: Как посчитать, сколько дней прошло между двумя датами?

О: Просто вычтите одну дату из другой: ('2026-03-23':: date — '2026-03-01':: date). В ответе будут дни.

В: Мне нужно добавить к заказу 3 часа, как это сделать?

О: Прибавьте интервал: order_ts + INTERVAL '3 hours'.

В: Как получить первый день текущего месяца, чтобы, например, начать отчёт с него?

О: Используйте DATE_TRUNC ('month', CURRENT_DATE). Эта команда округлит текущую дату вниз до начала месяца.

ПОПРОБУЙ САМ

— Выведи все заказы, которые были сделаны в этом году.

Подсказка: тебе понадобятся EXTRACT (YEAR FROM …) и CURRENT_DATE.

— Найди клиентов, у которых день рождения наступает в текущем месяце.

Подсказка: сравни месяц рождения с текущим месяцем с помощью EXTRACT (MONTH FROM …).

— Выведи дату заказа в привычном для человека формате: 'Год-Месяц-День Час: Минуты' (например, 2026-03-23 14:30).

Подсказка: используй TO_CHAR с подходящим шаблоном.

— Найди заказы, которые были сделаны в понедельник.

Подсказка: EXTRACT (DOW FROM …) вернёт день недели. Понедельник — это 1, воскресенье — 0.

— Найди заказы, сделанные в первый день любого месяца.

Подсказка: извлеки день из даты (EXTRACT (DAY FROM …)) и сравни с 1.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Найдите всех клиентов, у которых в имени есть буква 'о'. (шаг 17)

— Найдите всех клиентов, чьё имя заканчивается на 'ий'. (шаг 17)

— Найдите города, в названии которых есть две одинаковые буквы подряд. (шаг 17)

Шаг 20. Интервалы и последовательности

Просто генерируем данные в PostgreSQL

ЖИВОЙ ПРИМЕР

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

В PostgreSQL для этого есть специальная генерация рядов:

— generate_series — создаёт последовательность чисел или дат

— generate_series с датами — генерирует все дни между двумя датами

— generate_series с шагом — можно получить каждый второй день, каждый месяц и т. д.

ГЕНЕРАЦИЯ ЧИСЕЛ

Функция generate_series умеет создавать последовательности чисел. Это удобно, когда нужно:

— получить список номеров строк для отчёта

— создать тестовые данные

— выполнить операцию заданное количество раз

Синтаксис:

sql

generate_series (start, stop, step)

Пример 1. Числа от 1 до 10.

sql

SELECT generate_series (1, 10) AS num;

Результат:

text

num

— — —

…

generate_series (1, 10) возвращает набор чисел от 1 до 10. Шаг по умолчанию — 1.

Пример 2. Нечётные числа от 1 до 9.

sql

SELECT generate_series (1, 10, 2) AS odd_num;

Результат:

text

odd_num

— — — —

Третий параметр — шаг. Получаем нечётные числа от 1 до 9.

Пример 3. Числа от 10 до 1 (обратный порядок).

sql

SELECT generate_series (10, 1, -1) AS reverse_num;

Результат:

text

reverse_num

— — — — — —

…

Шаг может быть отрицательным. Получаем числа в обратном порядке.

ГЕНЕРАЦИЯ ДАТ

generate_series умеет работать не только с числами, но и с датами и временем.

Синтаксис для дат:

sql

generate_series (start_date, end_date, step_interval)

Шаг обязательно указывается как INTERVAL: '1 day', '1 month', '1 hour' и т. д.

Пример 4. Даты с 1 по 10 марта 2026.

sql

SELECT generate_series ('2026-03-01':: date, '2026-03-10':: date, '1 day') AS day;

Результат:

text

day

— — — — — —

2026-03-01

2026-03-02

2026-03-03

…

2026-03-10

generate_series с датами генерирует все дни в указанном диапазоне. Шаг '1 day’ — каждый день.

Пример 5. Месяцы с января по декабрь 2026.

sql

SELECT generate_series ('2026-01-01':: date, '2026-12-01':: date, '1 month') AS month;

Результат:

text

month

— — — — — —

2026-01-01

2026-02-01

2026-03-01

…

2026-12-01

Шаг '1 month’ — каждый месяц. Дата всегда будет первым числом месяца.

Пример 6. Часы в течение дня.

sql

SELECT generate_series ('2026-03-23 00:00:00':: timestamp, '2026-03-23 23:00:00':: timestamp, '1 hour') AS hour;

Результат:

text

hour

— — — — — — — — — — —

2026-03-23 00:00:00

2026-03-23 01:00:00

…

2026-03-23 23:00:00

Пример 7. Интервал 15 минут.

sql

SELECT generate_series ('2026-03-23 00:00:00':: timestamp, '2026-03-23 03:00:00':: timestamp, '15 minutes') AS interval_15min;

Результат:

text

interval_15min

— — — — — — — — — — —

2026-03-23 00:00:00

2026-03-23 00:15:00

2026-03-23 00:30:00

…

2026-03-23 03:00:00

Шаг может быть любым интервалом: '15 minutes’, '2 hours’, '3 days’.

Пример 8. Рабочие дни марта 2026 (без суббот и воскресений).

sql

SELECT day

FROM generate_series ('2026-03-01':: date, '2026-03-31':: date, '1 day') AS day

WHERE EXTRACT (DOW FROM day) NOT IN (0, 6);

generate_series создаёт все дни марта. EXTRACT (DOW FROM day) возвращает день недели: 0 — воскресенье, 6 — суббота. NOT IN (0, 6) оставляет только рабочие дни.

Результат (фрагмент):

text

day

— — — — — —

2026-03-02

2026-03-03

2026-03-04

2026-03-05

2026-03-06

2026-03-09

…

Пример 9. 10 случайных чисел.

sql

SELECT random () AS rand_num

FROM generate_series (1, 10);

generate_series создаёт 10 строк, для каждой вычисляется случайное число.

Результат (пример):

text

rand_num

— — — — — — — — — —

0.723456789012345

0.182345678901234

…

0.998765432109876

ГЕНЕРАЦИЯ СЛУЧАЙНЫХ ЧИСЕЛ (RANDOM)

Функция random () возвращает случайное число в диапазоне от 0 до 1 (не включая 1).

sql

SELECT random ();

Возвращает случайное число, например 0.723456789012345.

Генерация целого случайного числа в заданном диапазоне:

sql

— Случайное число от 1 до 100

SELECT floor (random () * 100 +1)::int AS random_number;

random () * 100 даёт число от 0 до 100. floor округляет вниз. +1 сдвигает диапазон до 1–100.

Генерация случайного целого числа от 1 до 10:

sql

SELECT floor (random () * 10 +1)::int;

Генерация нескольких случайных чисел:

sql

SELECT random () AS rand_num

FROM generate_series (1, 5);

Генерирует 5 случайных чисел.

Использование random () с ORDER BY (случайная сортировка):

sql

SELECT * FROM flowers ORDER BY random () LIMIT 3;

Возвращает 3 случайных цветка.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Забывать, что generate_series можно использовать только в FROM.

Неверно:

sql

SELECT generate_series (1, 10) +5;

Верно:

sql

SELECT num +5

FROM generate_series (1, 10) AS num;

Ошибка 2. Неправильно указывать шаг для дат.

Неверно:

sql

SELECT generate_series ('2026-03-01', '2026-03-10', 1);

Верно:

sql

SELECT generate_series ('2026-03-01':: date, '2026-03-10':: date, '1 day');

Ошибка 3. Использовать generate_series без явного приведения типов.

Неверно:

sql

SELECT generate_series ('2026-03-01', '2026-03-10', '1 day');

Верно:

sql

SELECT generate_series ('2026-03-01':: date, '2026-03-10':: date, '1 day');

ТОНКОСТИ И НЮАНСЫ

— generate_series можно использовать не только с числами и датами, но и с TIMESTAMP и TIMESTAMPTZ.

— Для больших диапазонов generate_series может генерировать миллионы строк — будьте осторожны.

— generate_series часто используют для построения отчётов с пропущенными датами.

— Шаг может быть дробным для числовых последовательностей: generate_series (0, 1, 0.1).

ИЗ ОПЫТА

Однажды мне нужно было получить все рабочие дни месяца (без суббот и воскресений). Я использовал generate_series и EXTRACT:

sql

SELECT day

FROM generate_series ('2026-03-01':: date, '2026-03-31':: date, '1 day') AS day

WHERE EXTRACT (DOW FROM day) NOT IN (0, 6);

Без generate_series пришлось бы перебирать даты вручную или писать сложный скрипт.

С тех пор, когда нужно получить диапазон дат или чисел, я сразу думаю про generate_series.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли сгенерировать последовательность месяцев, начиная не с первого числа?

О: Да. generate_series ('2026-01-15':: date, '2026-12-15':: date, '1 month') — получите 15-е число каждого месяца.

В: Как сгенерировать последовательность с плавающей точкой?

О: Используйте generate_series (0, 1, 0.1).

В: Можно ли использовать generate_series внутри SELECT без FROM?

О: В PostgreSQL — да, начиная с версии 10, но лучше всегда использовать FROM для ясности.

В: Что будет, если указать конечную дату раньше начальной?

О: Если шаг положительный — результат пустой. Если отрицательный — последовательность пойдёт в обратном порядке.

ПОПРОБУЙ САМ

— Сгенерируй все числа от 5 до 15.

— Сгенерируй все нечётные числа от 1 до 20.

— Сгенерируй все дни текущего месяца.

— Сгенерируй все часы текущего дня.

— Сгенерируй последовательность из 10 случайных чисел.

— Выбери 3 случайных цветка из таблицы flowers.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Найдите всех клиентов, у которых в имени есть буква 'о'. Используйте регулярное выражение ~*. (шаг 17)

— Найдите всех клиентов, чьё имя начинается с 'А'. Используйте регулярное выражение ~* '^а'. (шаг 17)

— Выведите возраст клиентов на текущую дату. Используйте AGE (birth_date). (шаг 19)

Шаг 21. А если из нескольких таблиц? Объединение таблиц JOIN

Что бывает, если несколько таблиц объединяются

ЖИВОЙ ПРИМЕР

Вы работаете с таблицей заказов orders. В ней есть client_phone — номер телефона клиента, но нет его имени.

Но имя клиента хранится в другой таблице — clients.

Как же теперь показать список заказов вместе с именами клиентов из другой таблицы?

В SQL для этого есть оператор объединения таблиц (JOIN).

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

ВИДЫ JOIN

В SQL есть несколько типов объединения таблиц.

В этом шаге мы рассмотрим самый простой и часто используемый — INNER JOIN.

Остальные типы (LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN) мы изучим позже.

СИНТАКСИС JOIN

sql

SELECT список_колонок

FROM таблица1

JOIN таблица2 ON условие_связи

Ключевое слово JOIN — это сокращённая форма INNER JOIN. Условие связи (ON) определяет, по каким колонкам соединяются таблицы

Условие может быть не только равенством колонок, но и более сложным:

sql

ON таблица1.колонка = таблица2.колонка AND таблица1.другая_колонка> 100

Важно: таблицы можно объединять и без явного JOIN, через запятую в FROM с условием в WHERE:

sql

SELECT *

FROM таблица1, таблица2

WHERE таблица1.колонка = таблица2.колонка

Такой способ тоже работает, но он менее нагляден и чаще приводит к ошибкам. Поэтому мы будем использовать явный JOIN.

Пример явного JOIN:

sql

SELECT o.id,

c.name AS client_name

FROM orders o

JOIN clients c ON o.client_phone = c.phone

То же самое без явного JOIN (через запятую и WHERE):

sql

SELECT o.id,

c.name AS client_name

FROM orders o, clients c

WHERE o.client_phone = c.phone

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

ПРИМЕРЫ

Пример 1. Вывести заказы с именами клиентов (INNER JOIN).

sql

SELECT o.id,

o. order_ts,

c.name AS client_name

FROM orders o

INNER JOIN clients c ON o.client_phone = c.phone

INNER JOIN соединяет заказы и клиентов по номеру телефона. Если у заказа нет клиента (такого не бывает, но вдруг), такой заказ не попадёт в результат.

Результат (фрагмент):

text

id order_ts client_name

1 2026-02-10 10:15:00 Николай

2 2026-02-12 14:30:00 Елена

…

Пример 2. Вывести заказы с названиями цветов (INNER JOIN).

sql

SELECT o.id,

f.name AS flower_name,

o. quantity

FROM orders o

INNER JOIN flowers f ON o. flower_id = f.id

Соединяем заказы с цветами по flower_id. Получаем название цветка вместо числового идентификатора.

Результат (фрагмент):

text

id flower_name quantity

1 Тюльпан 5

2 Роза 3

…

Пример 3. Вывести заказы с названиями цветов и их оттенками (соединение трёх таблиц).

sql

SELECT o.id,

f.name AS flower_name,

col.name AS color_name,

o. quantity

FROM orders o

INNER JOIN flowers f ON o. flower_id = f.id

INNER JOIN colors col ON o.color_code = col.code

Соединяем три таблицы: orders → flowers → colors. Каждый заказ получает название цветка и его оттенок.

Результат (фрагмент):

text

id flower_name color_name quantity

1 Тюльпан красный 5

2 Роза розовый 3

…

Пример 4. Вывести заказы с именами клиентов и названиями цветов (соединение трёх таблиц).

sql

SELECT o.id,

c.name AS client_name,

f.name AS flower_name,

o. quantity

FROM orders o

INNER JOIN clients c ON o.client_phone = c.phone

INNER JOIN flowers f ON o. flower_id = f.id

Соединяем заказы с клиентами и цветами. Получаем полную информацию о каждом заказе.

Пример 5. Вывести заказы с цветами, цена которых больше 150 рублей (соединение с условием в WHERE).

sql

SELECT o.id,

f.name AS flower_name,

f.price,

o. quantity

FROM orders o

INNER JOIN flowers f ON o. flower_id = f.id

WHERE f.price> 150

Сначала соединяем заказы с цветами, затем отфильтровываем только те, где цена цветка больше 150.

Результат (фрагмент):

text

id flower_name price quantity

3 Пион 200 7

7 Орхидея 250 1

…

Пример 6. Вывести заказы с именами клиентов и названиями цветов, но только для клиентов из Москвы (city_id = 1).

sql

SELECT o.id,

c.name AS client_name,

f.name AS flower_name,

o. quantity

FROM orders o

INNER JOIN clients c ON o.client_phone = c.phone

INNER JOIN flowers f ON o. flower_id = f.id

WHERE c.city_id = 1

Соединяем три таблицы, затем оставляем только заказы клиентов из Москвы.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Забыть условие соединения (ON).

Неверно:

sql

SELECT * FROM orders o JOIN clients c

Ошибка: декартово произведение (каждый заказ соединится с каждым клиентом).

Верно:

sql

SELECT * FROM orders o JOIN clients c ON o.client_phone = c.phone

Ошибка 2. Соединять таблицы по колонкам с разными типами данных.

Если типы не совпадают, PostgreSQL может попытаться преобразовать их автоматически, но это может привести к ошибкам или медленным запросам. Лучше всегда соединять по колонкам одного типа.

ТОНКОСТИ И НЮАНСЫ

— INNER JOIN можно сократить до JOIN — это одно и то же.

— Условие соединения может быть составным (например, ON a.id = b.id AND a.status = b.status).

— Можно соединять таблицы по разным типам данных, но лучше, чтобы они совпадали.

— Псевдонимы (алиасы) таблиц делают запрос короче и читаемее.

— Можно соединять не две, а несколько таблиц.

ИЗ ОПЫТА

Однажды я писал отчёт и соединял заказы с клиентами через JOIN, думая, что у всех заказов есть клиенты.

Оказалось, что в тестовой базе были некорректные данные по заказам без клиентов. Отчёт показал не все данные.

С тех пор я всегда проверяю данные, прежде чем соединять таблицы, чтобы не потерять важную информацию.

ВОПРОСЫ И ОТВЕТЫ

В: Можно ли соединить больше двух таблиц?

О: Да. В примерах мы соединяли три таблицы: orders, clients, flowers.

В: Что такое декартово произведение?

О: Это когда каждая строка из первой таблицы соединяется с каждой строкой из второй. Если таблицы большие, результат может быть огромным.

В: Можно ли использовать JOIN с самим собой?

О: Да, это называется self-join. Мы рассмотрим это в одном из следующих шагов.

В: Нужно ли всегда указывать INNER перед JOIN?

О: Нет. JOIN сам по себе означает INNER JOIN. Это сокращение.

В: Что делать, если у двух таблиц одинаковые названия колонок?

О: Используйте псевдонимы таблиц, чтобы различать их. Например, orders.id и clients.id.

ПОПРОБУЙ САМ

— Выведи список заказов с именами клиентов (используй INNER JOIN).

— Выведи список заказов с названиями цветов (используй INNER JOIN).

— Выведи список заказов с названиями цветов и их оттенками (соедини три таблицы).

— Выведи список заказов с именами клиентов и названиями цветов (соедини три таблицы).

— Выведи заказы с цветами, цена которых больше 150 рублей (соединение с условием в WHERE).

— Выведи заказы с именами клиентов и названиями цветов, но только для клиентов из Москвы (city_id = 1).

Шаг 22. SQL и пустота. И причём здесь Правые и Левые (RIGHT JOIN и LEFT JOIN)

Когда данные справа и слева

ЖИВОЙ ПРИМЕР

Вы работаете с таблицей заказов orders. Вам нужно вывести все заказы, и если для заказа есть клиент — показать его имя. Если клиента нет — всё равно показать заказ.

В шаге 21 мы использовали INNER JOIN — он показывал только те строки, где есть совпадение в обеих таблицах.

Но что делать, если данные распределены по-разному: слева и справа?

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

Для этого в SQL есть LEFT JOIN и RIGHT JOIN. Они помогают работать с 'пустотой' — когда данных нет, на их месте появляется NULL.

ЧТО ТАКОЕ NULL И IS NULL

NULL в SQL означает 'нет данных', 'неизвестно' или 'значение отсутствует'. Это не ноль и не пустая строка — это именно отсутствие информации.

Чтобы проверять, является ли значение NULL, нельзя использовать обычные операторы сравнения (=, <>). Вместо этого используются специальные операторы:

— IS NULL — проверяет, что значение равно NULL

— IS NOT NULL — проверяет, что значение не равно NULL

Примеры:

sql

— найти заказы, у которых нет клиента

SELECT * FROM orders o

LEFT JOIN clients c ON o.client_phone = c.phone

WHERE c.phone IS NULL;

Проверяем, что колонка phone из таблицы clients не содержит значения (клиент не найден).

sql

— найти клиентов с незаполненной датой рождения

SELECT name, phone, birth_date

FROM clients

WHERE birth_date IS NULL;

Выбираем клиентов, у которых дата рождения не указана (NULL).

sql

— найти клиентов, у которых дата рождения заполнена

SELECT name, phone, birth_date

FROM clients

WHERE birth_date IS NOT NULL;

Выбираем клиентов, у которых дата рождения указана.

ФУНКЦИЯ COALESCE

COALESCE — это функция, которая возвращает первое не-NULL значение из списка аргументов. Она очень полезна для замены NULL на значение по умолчанию.

Синтаксис:

sql

COALESCE (значение1, значение2, значение3, …)

Возвращает первый аргумент, который не равен NULL. Если все аргументы равны NULL, возвращает NULL.

Примеры:

sql

— Замена NULL на значение по умолчанию

SELECT name,

COALESCE (birth_date, '1900-01-01') AS birth_date_filled

FROM clients;

*Если дата рождения не указана (NULL), подставляем '1900-01-01'. *

sql

— В запросах с LEFT JOIN

SELECT o.id,

COALESCE(c.name, 'Клиент не найден') AS client_name

FROM orders o

LEFT JOIN clients c ON o.client_phone = c.phone;

Если клиент не найден (c.name = NULL), показываем текст 'Клиент не найден'.

sql

— С несколькими аргументами

SELECT COALESCE (phone, email, 'Нет контактов') AS contact

FROM clients;

Сначала проверяем телефон, если его нет — берём email, если и email нет — выводим 'Нет контактов'.

Почему это важно: COALESCE помогает избежать неожиданных NULL в результатах запросов и делает вывод данных более предсказуемым.

ВИДЫ JOIN (ПОВТОРЕНИЕ И ДОПОЛНЕНИЕ)

Где левая и правая таблица в запросе:

sql

SELECT…

FROM таблица_слева

LEFT JOIN таблица_справа ON условие

— Левая таблица — та, что указана перед словом JOIN

— Правая таблица — та, что указана после слова JOIN

Пример:

sql

SELECT *

FROM orders o — это левая таблица

LEFT JOIN clients c — это правая таблица

ON o.client_phone = c.phone;

LEFT JOIN сохранит все строки из orders (левая таблица), даже если для них нет клиента в clients.

sql

SELECT *

FROM orders o — это левая таблица

RIGHT JOIN clients c — это правая таблица

ON o.client_phone = c.phone;

RIGHT JOIN сохранит все строки из clients (правая таблица), даже если для них нет заказов в orders.

LEFT JOIN

LEFT JOIN возвращает все строки из левой таблицы (той, что указана перед LEFT JOIN), и только совпадающие из правой. Если для строки из левой таблицы нет совпадения в правой, то все колонки из правой таблицы будут заполнены значением NULL.

sql

SELECT o.id,

o. order_ts,

c.name AS client_name

FROM orders o

LEFT JOIN clients c ON o.client_phone = c.phone;

Берём все заказы из таблицы orders. Для каждого заказа ищем клиента в таблице clients. Если клиент найден — показываем его имя. Если нет — в колонке client_name будет NULL.

Результат (фрагмент):

text

id order_ts client_name

1 2026-02-10 10:15:00 Николай

2 2026-02-12 14:30:00 Елена

…

100 2026-03-15 14:15:00 NULL

NULL И IS NULL В JOIN

NULL в результатах LEFT JOIN — это признак того, что для строки из левой таблицы не нашлось соответствующей строки в правой.

Чтобы найти такие строки, используют условие IS NULL:

sql

— найти заказы, для которых нет клиента

SELECT o.id, o. order_ts

FROM orders o

LEFT JOIN clients c ON o.client_phone = c.phone

WHERE c.phone IS NULL;

Сначала соединяем заказы с клиентами через LEFT JOIN, затем оставляем только те строки, где клиент не найден (c.phone IS NULL).

RIGHT JOIN

RIGHT JOIN работает так же, как LEFT JOIN, но все строки берутся из правой таблицы (той, что указана после RIGHT JOIN). Если для строки из правой таблицы нет совпадения в левой, то все колонки из левой таблицы будут заполнены значением NULL.

sql

SELECT c.name,

o.id AS order_id,

o. order_ts

FROM orders o

RIGHT JOIN clients c ON o.client_phone = c.phone;

Берём всех клиентов из таблицы clients. Для каждого клиента ищем заказы в таблице orders. Если заказы найдены — показываем их. Если нет — в колонках order_id и order_ts будет NULL.

Результат (фрагмент):

text

name order_id order_ts

Николай 1 2026-02-10 10:15:00

Николай 4 2026-02-18 16:20:00

Елена 2 2026-02-12 14:30:00

Елена 7 2026-02-23 11:40:00

Анна 5 2026-02-20 12:10:00

…

Светлана NULL NULL

RIGHT JOIN НА ПРАКТИКЕ

На практике RIGHT JOIN используют редко, потому что тот же результат можно получить, поменяв таблицы местами и использовав LEFT JOIN:

sql

— то же самое, что и RIGHT JOIN выше

SELECT c.name,

o.id AS order_id,

o. order_ts

FROM clients c

LEFT JOIN orders o ON c.phone = o.client_phone;

Мы просто поменяли таблицы местами и заменили RIGHT JOIN на LEFT JOIN. Результат будет точно таким же.

ПРИМЕРЫ

Пример 1. Вывести все заказы и имена клиентов (LEFT JOIN).

sql

SELECT o.id,

o. order_ts,

c.name AS client_name

FROM orders o

LEFT JOIN clients c ON o.client_phone = c.phone;

Все заказы остаются в результате. Если клиент найден — показываем его имя, если нет — NULL.

Пример 2. Вывести всех клиентов и их заказы (LEFT JOIN).

sql

SELECT c.name,

o.id AS order_id,

o. order_ts

FROM clients c

LEFT JOIN orders o ON c.phone = o.client_phone;

Все клиенты остаются в результате. Если у клиента есть заказы — показываем их. Если нет — в колонках order_id и order_ts будет NULL.

Результат (фрагмент):

name order_id order_ts

Николай 1 2026-02-10 10:15:00

Николай 4 2026-02-18 16:20:00

Елена 2 2026-02-12 14:30:00

Елена 7 2026-02-23 11:40:00

Анна 5 2026-02-20 12:10:00

…

Светлана NULL NULL

Пример 3. Вывести заказы, для которых нет клиента (LEFT JOIN + проверка на NULL).

sql

SELECT o.id,

o. order_ts

FROM orders o

LEFT JOIN clients c ON o.client_phone = c.phone

WHERE c.phone IS NULL;

Пример 4. Вывести клиентов, у которых нет заказов (LEFT JOIN + проверка на NULL).

sql

SELECT c.name,

c.phone

FROM clients c

LEFT JOIN orders o ON c.phone = o.client_phone

WHERE o.id IS NULL;

Соединяем клиентов с заказами через LEFT JOIN, затем оставляем только тех клиентов, у которых нет заказов (o.id IS NULL).

Результат (фрагмент):

name phone

Светлана 433334455

Виктор 834445566

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Забыть LEFT JOIN и использовать INNER JOIN когда нужно сохранить все строки из левой таблицы.

Неверно:

sql

SELECT * FROM orders o JOIN clients c ON o.client_phone = c.phone;

Верно:

sql

SELECT * FROM orders o LEFT JOIN clients c ON o.client_phone = c.phone;

Ошибка 2. Использовать RIGHT JOIN когда проще поменять таблицы местами и использовать LEFT JOIN.

Код с LEFT JOIN читается легче, так как логика идёт слева направо.

Ошибка 3. Путать IS NULL и = NULL.

Неверно:

sql

WHERE c.phone = NULL

Верно:

sql

WHERE c.phone IS NULL

ТОНКОСТИ И НЮАНСЫ

— NULL в SQL означает отсутствие значения. Это не ноль и не пустая строка.

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

— LEFT JOIN сохраняет все строки из левой таблицы, RIGHT JOIN — из правой.

— На практике RIGHT JOIN используется редко — его почти всегда можно заменить на LEFT JOIN, поменяв таблицы местами.

— COALESCE помогает заменить NULL на значение по умолчанию.

ИЗ ОПЫТА

Однажды я писал отчёт и соединял заказы с клиентами через JOIN, думая, что у всех заказов есть клиенты.

Оказалось, что в тестовой базе были некорректные данные — заказы без клиентов. Отчёт показал не все данные.

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

ВОПРОСЫ И ОТВЕТЫ

В: Чем отличается LEFT JOIN от INNER JOIN?

О: INNER JOIN возвращает только строки с совпадениями в обеих таблицах. LEFT JOIN возвращает все строки из левой таблицы, даже если совпадений нет.

В: Что такое NULL в результатах LEFT JOIN?

О: Это означает, что для строки из левой таблицы не нашлось соответствующей строки в правой таблице.

В: Как найти клиентов без заказов?

О: Использовать LEFT JOIN и проверить, что колонка из таблицы заказов равна NULL:

sql

SELECT * FROM clients c

LEFT JOIN orders o ON c.phone = o.client_phone

WHERE o.id IS NULL;

В: Зачем нужен RIGHT JOIN, если есть LEFT JOIN?

О: RIGHT JOIN редко используется на практике, потому что его всегда можно заменить на LEFT JOIN, поменяв таблицы местами. Но знать о его существовании полезно.

В: Для чего нужна функция COALESCE?

О: COALESCE возвращает первое не-NULL значение из списка аргументов. Она полезна для замены NULL на значение по умолчанию.

ПОПРОБУЙ САМ

— Выведите все заказы и имена клиентов, используя LEFT JOIN.

— Выведите всех клиентов и их заказы, используя LEFT JOIN.

— Найдите заказы, для которых нет клиента (используйте LEFT JOIN и IS NULL).

— Найдите клиентов, у которых нет заказов (используйте LEFT JOIN и IS NULL).

— Перепишите запрос с RIGHT JOIN на LEFT JOIN, поменяв таблицы местами.

— Используйте COALESCE, чтобы заменить NULL в именах клиентов на текст 'Клиент не найден'.

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Выведите список заказов с именами клиентов. Используйте INNER JOIN. (шаг 21)

— Выведите список заказов с названиями цветов. Используйте INNER JOIN. (шаг 21)

— Выведите список заказов с названиями цветов и их оттенками. Используйте соединение трёх таблиц: orders → flowers → colors. (шаг 21)

Шаг 23. Голова кусает хвост или объединяем таблицу с самой собой

Когда одной таблицы становится недостаточно

ЖИВОЙ ПРИМЕР

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

Как это сделать? Нам нужно сравнить каждого клиента с другими клиентами из той же таблицы.

Здесь нам нужна одна и та же таблица, но в двух ролях.

В SQL для этого есть самообъединение (self-join) — соединение таблицы с самой собой.

Таблица объединяется как бы со своей копией, со своим отражением, причем эти копиям даются разные названия — псевдонимы.

ЧТО ТАКОЕ САМООБЪЕДИНЕНИЕ

Самообъединение (self-join) — это когда таблица соединяется сама с собой через JOIN.

Чтобы различать 'копии' одной таблицы, используются псевдонимы (алиасы).

sql

SELECT *

FROM таблица A

JOIN таблица B ON A. колонка = B. колонка

Здесь A и B — это одна и та же таблица, но с разными псевдонимами

Пример: найти клиентов, которые родились в один и тот же день.

sql

SELECT c1.name AS client1,

c2.name AS client2,

c1.birth_date

FROM clients c1

JOIN clients c2 ON c1.birth_date = c2.birth_date

WHERE c1.phone!= c2.phone

Соединяем таблицу clients саму с собой по дате рождения. Условие c1.phone!= c2.phone убирает пары, где клиент сравнивается сам с собой.

ПРИМЕРЫ

Пример 1. Найти клиентов, которые родились в один и тот же день.

sql

SELECT c1.name AS client1,

c2.name AS client2,

c1.birth_date

FROM clients c1

JOIN clients c2 ON c1.birth_date = c2.birth_date

WHERE c1.phone!= c2.phone

Результат (фрагмент):

text

client1 client2 birth_date

Николай Дмитрий 1980-05-10

Дмитрий Николай 1980-05-10

Елена Алексей 1992-08-22

Алексей Елена 1992-08-22

Пример 2. Найти клиентов, которые живут в одном городе.

sql

SELECT c1.name AS client1,

c2.name AS client2,

c1.city_id

FROM clients c1

JOIN clients c2 ON c1.city_id = c2.city_id

WHERE c1.phone!= c2.phone

Соединяем таблицу clients саму с собой по city_id. c1.phone!= c2.phone исключает совпадение клиента с самим собой.

Пример 3. Найти клиентов, которые живут в одном городе, и вывести название города.

sql

SELECT c1.name AS client1,

c2.name AS client2,

ct.name AS city

FROM clients c1

JOIN clients c2 ON c1.city_id = c2.city_id

JOIN cities ct ON c1.city_id = ct.id

WHERE c1.phone!= c2.phone

Соединяем клиентов друг с другом по городу, затем присоединяем таблицу cities, чтобы получить название города.

ТИПИЧНЫЕ ОШИБКИ

Ошибка 1. Забыть псевдонимы.

Без псевдонимов PostgreSQL не сможет различить две копии одной таблицы.

Неверно:

sql

SELECT name FROM clients JOIN clients ON birth_date = birth_date

Верно:

sql

SELECT c1.name FROM clients c1 JOIN clients c2 ON c1.birth_date = c2.birth_date

Ошибка 2. Использовать INNER JOIN, когда нужен LEFT JOIN.

Если у некоторых строк нет связи (например, у клиента нет пары с той же датой рождения), INNER JOIN их исключит.

Чтобы сохранить все строки, используйте LEFT JOIN.

sql

— все клиенты, даже если нет пары

SELECT c1.name, c2.name

FROM clients c1

LEFT JOIN clients c2 ON c1.birth_date = c2.birth_date AND c1.phone!= c2.phone

Ошибка 3. Забыть условие c1.phone!= c2.phone при поиске пар.

Без этого условия клиент будет соединён сам с собой, и в результате появятся 'пары' с одинаковыми именами.

Неверно:

sql

SELECT c1.name, c2.name

FROM clients c1

JOIN clients c2 ON c1.birth_date = c2.birth_date

В результате будет, например, 'Николай — Николай'.

Верно:

sql

SELECT c1.name, c2.name

FROM clients c1

JOIN clients c2 ON c1.birth_date = c2.birth_date

WHERE c1.phone!= c2.phone

Ошибка 4. Путать порядок таблиц в LEFT JOIN при самообъединении.

Порядок важен: левая таблица — это та, все строки которой мы хотим видеть.

sql

— оставляем всех клиентов из левой таблицы (c1)

SELECT c1.name, c2.name

FROM clients c1

LEFT JOIN clients c2 ON c1.birth_date = c2.birth_date AND c1.phone!= c2.phone

ТОНКОСТИ И НЮАНСЫ

— Самообъединение — это обычный JOIN, со всеми его типами (INNER, LEFT, RIGHT). Выбор типа зависит от задачи.

— Псевдонимы обязательны — без них запрос не выполнится.

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

— В условиях соединения можно использовать не только равенство, но и операторы сравнения (>, <,> =, <=).

sql

— найти все пары клиентов, где один родился раньше другого

SELECT c1.name AS older,

c2.name AS younger,

c1.birth_date AS older_birth,

c2.birth_date AS younger_birth

FROM clients c1

JOIN clients c2 ON c1.birth_date <c2.birth_date

— Для поиска всех пар без дублирования (например, 'Николай — Дмитрий' и 'Дмитрий — Николай') можно добавить условие c1.phone> c2.phone вместо!=. Это оставит только одну пару.

sql

— уникальные пары без дублирования

SELECT c1.name, c2.name

FROM clients c1

JOIN clients c2 ON c1.birth_date = c2.birth_date

WHERE c1.phone> c2.phone

ИЗ ОПЫТА

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

Сначала я написал:

sql

SELECT c1.name, c2.name

FROM clients c1

JOIN clients c2 ON c1.birth_date = c2.birth_date

WHERE c1.phone!= c2.phone

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

Тогда я заменил условие на c1.phone> c2.phone:

sql

SELECT c1.name, c2.name

FROM clients c1

JOIN clients c2 ON c1.birth_date = c2.birth_date

WHERE c1.phone> c2.phone

Теперь каждая пара встречалась только один раз. Этот приём часто помогает избежать дублирования при самообъединении.

ВОПРОСЫ И ОТВЕТЫ

В: Что такое самообъединение?

О: Это соединение таблицы с самой собой с помощью JOIN. Чтобы различать 'копии', используют псевдонимы.

В: Зачем нужны псевдонимы в самообъединении?

О: Чтобы база данных понимала, о какой копии таблицы идёт речь.

В: Можно ли использовать LEFT JOIN в самообъединении?

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

В: Как избежать дублирования пар (когда пара встречается дважды)?

О: Добавьте условие с оператором сравнения, например c1.phone> c2.phone.

sql

SELECT c1.name, c2.name

FROM clients c1

JOIN clients c2 ON c1.birth_date = c2.birth_date

WHERE c1.phone> c2.phone

В: Можно ли использовать самообъединение с подзапросами?

О: Да, но это уже более сложный случай — рекурсивные запросы. Они рассмотрены в отдельном шаге.

ПОПРОБУЙ САМ

У нас есть таблица employees мы создали ее в начале книги:

— Выведи список сотрудников с именами их руководителей.

— Найди сотрудников, у которых один и тот же руководитель.

— Найди сотрудников, которые зарабатывают больше своего руководителя.

— Найди сотрудников, у которых нет руководителя.

— Выведи список сотрудников с именами их руководителей и руководителей руководителей (для этого понадобится два самообъединения).

ЗАДАЧИ НА ПОВТОРЕНИЕ

— Сгенерируйте все дни текущего месяца. Используйте generate_series. (шаг 20)

— Сгенерируйте последовательность из 10 случайных чисел. Используйте generate_series и random (). (шаг 20)

Шаг 24. Объединяем, пересекаем, вычитаем. Работа с множествами (UNION, INTERSECT, EXCEPT)

Как собрать данные из разных запросов в один результат

ЖИВОЙ ПРИМЕР

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

Вам нужен общий список — без дубликатов.

Для решения подобных задач в SQL есть специальные операторы для работы с множествами:

— UNION (объединение) — объединяет результаты двух запросов, убирая дубликаты строк

— UNION ALL (объединение) — объединяет результаты двух запросов, но дубликаты остаются

— INTERSECT (пересечение) — возвращает только те строки, которые есть в обоих запросах

— EXCEPT (исключение) — возвращает те данные, которые есть в первом запросе, но нет во втором

ПРАВИЛА РАБОТЫ С ОПЕРАТОРАМИ МНОЖЕСТВ

Чтобы два запроса можно было объединить, они должны соответствовать простым правилам:

— Одинаковое количество колонок в обоих запросах.

— Совместимые типы данных — колонки должны быть одного типа или приводиться к одному типу.

— Порядок колонок важен — первая колонка первого запроса соединяется с первой колонкой второго, и так далее.

При нарушении любого из этих условий запрос вернёт ошибку.

UNION И UNION ALL

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

UNION ALL объединяет, оставляя все строки, включая дубликаты (работает быстрее).

Задача: получить список телефонов всех клиентов и всех клиентов, которые делали заказы (объединённый список).

sql

SELECT phone FROM clients

UNION

SELECT client_phone FROM orders

Что тут происходит

— Первый запрос возвращает телефоны всех клиентов.

— Второй — телефоны всех, кто делал заказы.

— UNION убирает дубликаты (телефоны, которые есть в обеих таблицах).

С UNION ALL дубликаты останутся:

sql

SELECT phone FROM clients

UNION ALL

SELECT client_phone FROM orders

INTERSECT

INTERSECT возвращает только те строки, которые есть в обоих запросах.

Задача: найти клиентов, у которых есть отменённые заказы.

sql

SELECT phone FROM clients

INTERSECT

SELECT client_phone

FROM orders WHERE status = 'отменён' /* */

Что тут происходит

— Первый запрос возвращает телефоны всех клиентов.

— Второй — телефоны клиентов с отменёнными заказами.

— INTERSECT оставляет только телефоны, которые есть в обоих результатах.

EXCEPT

EXCEPT возвращает строки из первого запроса, которых нет во втором.

Задача: найти клиентов, которые не делали ни одного заказа.

sql

SELECT phone FROM clients

EXCEPT

SELECT client_phone FROM orders

Что тут происходит

— Первый запрос возвращает телефоны всех клиентов.

— Второй — телефоны всех, кто делал заказы.

— EXCEPT оставляет только тех, кто есть в первом списке, но отсутствует во втором.

ПРИМЕРЫ

Пример 1. Объединить имена клиентов из Москвы и Санкт-Петербурга (UNION).

sql

SELECT name FROM clients WHERE city_id = 1

UNION

SELECT name FROM clients WHERE city_id = 2

Выбираем имена клиентов из Москвы и Санкт-Петербурга, объединяем в один список, убираем дубликаты.

Результат (фрагмент):

text

name

Николай

Дмитрий

Татьяна

Елена

Алексей

Пример 2. Получить все названия цветов и все оттенки в одном списке (UNION).

sql

SELECT name FROM flowers

UNION

SELECT name FROM colors

В одном списке будут и названия цветов, и названия оттенков.

Результат (фрагмент):

text

name

Роза

Тюльпан

Хризантема

Лилия

Пион

Гербера

Орхидея

Ромашка

красный

белый

розовый

Пример 3. Найти клиентов, которые делали заказы в феврале и в марте (INTERSECT).

sql

SELECT client_phone FROM orders WHERE order_ts> = '2026-02-01' AND order_ts <'2026-03-01' /* */

INTERSECT

SELECT client_phone FROM orders WHERE order_ts> = '2026-03-01' AND order_ts <'2026-04-01' /* */

Находим телефоны клиентов, которые делали заказы в феврале, и пересекаем с теми, кто делал заказы в марте.

Результат (фрагмент):

text

client_phone

112345678

…

Пример 4. Найти клиентов, которые делали заказы в феврале, но не в марте (EXCEPT).

sql

SELECT client_phone FROM orders WHERE order_ts> = '2026-02-01' AND order_ts <'2026-03-01' /* */

EXCEPT

Бесплатный фрагмент закончился.

Купите книгу, чтобы продолжить чтение.