SQL

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

Основные команды SQL

Команда SELECT выбирает столбцы из таблицы. Минимальная форма: указать столбцы и источник данных.

SELECT column1, column2
FROM tablename;

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

INSERT INTO tablename (column1, column2) 
VALUES ('value1', 'value2');

UPDATE изменяет существующие данные. Ограничивайте изменяемые строки условием WHERE.

UPDATE tablename 
SET column1 = 'new_value' 
WHERE column2 = 'condition';

DELETE удаляет строки, удовлетворяющие условию.

DELETE FROM tablename 
WHERE column1 = 'condition';

CREATE создаёт объекты (таблицы, базы), DROP — удаляет.

CREATE TABLE tablename (
    column1 datatype,
    column2 datatype
);

DROP TABLE tablename;

Типы данных в PostgreSQL

Корректный выбор типов повышает производительность и экономит место. В PostgreSQL доступны:

Числовые: smallint, integer, bigint, decimal, numeric.
Строковые: varchar, text, char.
Дата и время: date, time, timestamp.
Логический: boolean.
Структурированные: json, jsonb, массивы [].

Операции с данными

Фильтрация: Используется оператор WHERE.
SELECT * FROM tablename WHERE column1 = 'condition';
Сортировка: Используется оператор ORDER BY.
SELECT * FROM tablename ORDER BY column1 ASC/DESC;
Группировка: Используется оператор GROUP BY.
SELECT column1, SUM(column2) FROM tablename GROUP BY column1;

Транзакции и индексы

Транзакция объединяет несколько операций в одно целое: либо все успешны, либо ни одна. Это сохраняет целостность данных.

BEGIN;
-- операции
COMMIT;

Индекс ускоряет поиск и сортировку по столбцам.

CREATE INDEX indexname
 ON tablename (columnname);

Практические примеры

Создание базы данных и таблицы:
CREATE DATABASE mydatabase; \c mydatabase CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) );
Добавление данных:
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
Выборка данных:
SELECT * FROM users WHERE name = 'John Doe';

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

Подзапросы

Подзапрос — вложенный SELECT, который возвращает значение(я) для условия во внешнем запросе.

SELECT *
FROM users
WHERE salary > (SELECT AVG(salary) FROM users);

Соединения

Соединения объединяют строки из двух таблиц по условию соответствия ключей.

INNER JOIN

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

SELECT *
FROM users
         INNER JOIN orders
                    ON users.id = orders.user_id;

LEFT JOIN

Возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если нет совпадений, возвращает NULL.

SELECT *
FROM users
         LEFT JOIN orders
                   ON users.id = orders.user_id;

RIGHT JOIN

Аналогично LEFT JOIN, но возвращает все строки из правой таблицы.

SELECT *
FROM users
         RIGHT JOIN orders
                    ON users.id = orders.user_id;

FULL OUTER JOIN

Возвращает все строки из обеих таблиц, заполняя NULL там, где нет совпадений.

SELECT *
FROM users
         FULL OUTER JOIN orders
                         ON users.id = orders.user_id;

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

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

SUM: Сумма значений.
AVG: Среднее значение.
MAX: Максимальное значение.
MIN: Минимальное значение.
COUNT: Количество строк.

SELECT SUM(salary) AS total_salary
FROM users;

Индексирование и оптимизация запросов

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

Создание индекса

CREATE INDEX idx_name
    ON users (name);

Оптимизация запросов

Используйте EXPLAIN для анализа плана выполнения запроса.
Избегайте использования SELECT *, если не нужно все поля.
Используйте индексы для полей, участвующих в фильтрации и сортировке.

EXPLAIN
SELECT *
FROM users
WHERE name = 'John Doe';

Безопасность и права доступа

Создание пользователей и назначение прав

В PostgreSQL пользователи — это роли. Привилегии выдаются на объекты (таблицы, схемы, БД).

CREATE ROLE myuser WITH PASSWORD 'mypassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users TO myuser;

Роли и привилегии

SUPERUSER: Полный доступ к базе данных.
CREATEDB: Право создавать базы данных.
CREATEROLE: Право создавать роли.

ALTER ROLE myuser WITH SUPERUSER;

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

Создание резервной копии

pg_dump -U myuser mydatabase > backup.sql

Восстановление из резервной копии

psql -U myuser mydatabase < backup.sql

Триггеры и функции

Триггер запускает процедуру при событиях INSERT/UPDATE/DELETE.

CREATE OR REPLACE FUNCTION update_timestamp()
    RETURNS TRIGGER AS
$$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_timestamp_trigger
    BEFORE UPDATE
    ON users
    FOR EACH ROW
EXECUTE PROCEDURE update_timestamp();

Функции позволяют группировать повторяющиеся операции и использовать их в запросах.

CREATE OR REPLACE FUNCTION get_user_name(p_id INTEGER)
    RETURNS VARCHAR AS
$$
DECLARE
    v_name VARCHAR;
BEGIN
    SELECT name
    INTO v_name
    FROM users
    WHERE id = p_id;
    RETURN v_name;
END;
$$ LANGUAGE plpgsql;

SELECT get_user_name(1);

Последовательности и идентификаторы

Последовательности

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

CREATE SEQUENCE user_id_seq;

CREATE TABLE users
(
    id   INTEGER DEFAULT nextval('user_id_seq'),
    name VARCHAR(50)
);

Идентификаторы (SERIAL)

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

CREATE TABLE users
(
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

Вью и материализованные вью

Вью

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

CREATE VIEW user_info AS
SELECT id, name, email
FROM users;

SELECT *
FROM user_info;

Материализованные вью

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

CREATE MATERIALIZED VIEW user_info AS
SELECT id, name, email
FROM users;

REFRESH MATERIALIZED VIEW user_info;

Пример материализованного представления

1. Создание исходных таблиц

-- Таблица продаж
CREATE TABLE sales
(
    id         SERIAL PRIMARY KEY,
    product_id INT,
    sale_date  DATE,
    quantity   INT,
    price      DECIMAL(10, 2)
);

-- Таблица продуктов
CREATE TABLE products
(
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100),
    category VARCHAR(50)
);

-- Наполнение тестовыми данными
INSERT INTO products (name, category)
VALUES ('Ноутбук', 'Электроника'),
       ('Смартфон', 'Электроника'),
       ('Книга', 'Литература');

INSERT INTO sales (product_id, sale_date, quantity, price)
VALUES (1, '2025-01-01', 5, 100000),
       (2, '2025-01-02', 10, 50000),
       (3, '2025-01-03', 20, 1000);

2. Создание материализованного представления

CREATE MATERIALIZED VIEW sales_summary AS
SELECT p.category,
       COUNT(s.id)               AS total_sales,
       SUM(s.quantity * s.price) AS total_revenue,
       MAX(s.sale_date)          AS last_sale_date
FROM sales s
         JOIN products p ON s.product_id = p.id
GROUP BY p.category
WITH DATA;

3. Запрос к материализованному представлению

SELECT *
FROM sales_summary;

category	total_sales	total_revenue	last_sale_date
Электроника	2	1000000	2025-01-02
Литература	1	20000	2025-01-03

Сравнение с обычным запросом

Обычный агрегирующий запрос:

EXPLAIN ANALYZE
SELECT p.category,
       COUNT(s.id),
       SUM(s.quantity * s.price),
       MAX(s.sale_date)
FROM sales s
         JOIN products p ON s.product_id = p.id
GROUP BY p.category;

План выполнения:

GroupAggregate  (cost=30.76..30.79 rows=2 width=68)
Planning Time: 0.153 ms
Execution Time: 0.045 ms

Запрос к материализованному представлению:

EXPLAIN ANALYZE
SELECT *
FROM sales_summary;

План выполнения:

Seq Scan on sales_summary  (cost=0.00..1.03 rows=3 width=68)
Planning Time: 0.014 ms
Execution Time: 0.012 ms

Ключевые преимущества:

Производительность Время выполнения сократилось с 0.045 ms до 0.012 ms (в 3.75 раза быстрее). Для больших данных разница будет более существенной.
Снижение нагрузки на БД Материализованное представление:
- Не требует выполнения JOIN операций
- Избегает повторных вычислений агрегатных функций
- Минимизирует блокировки таблиц
Возможность индексирования

CREATE INDEX idx_sales_summary_category ON sales_summary (category);

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

Оптимизация сложных запросов

Особенно эффективны для:
- Многотабличных JOIN-ов
- Вложенных подзапросов
- Рекурсивных запросов
- Геопространственных вычислений

Ограничения и особенности:

Обновление данных Требует явного обновления:

REFRESH MATERIALIZED VIEW sales_summary; -- Полное обновление
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary; -- Без блокировок (PostgreSQL 9.4+)

Хранение данных Занимает место на диске (как обычная таблица). Размер можно проверить:

SELECT pg_size_pretty(pg_total_relation_size('sales_summary'));

Актуальность данных Не отражает изменения в реальном времени. Частота обновления зависит от требований приложения.

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

Отчеты и аналитика, где допустима небольшая задержка данных
Сложные дашборды с агрегацией
Часто используемые ресурсоемкие запросы
Системы с высокой read-нагрузкой

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

Common Table Expressions (CTE)

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

Общие табличные выражения (CTE) позволяют сохранять результаты запроса на этапе построения запроса. CTE объявляются с помощью ключевого слова WITH и могут быть использованы в основном запросе.

WITH user_data AS (SELECT id, name
                   FROM users)
SELECT *
FROM user_data
WHERE name = 'John Doe';

Регулярные выражения

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

SELECT *
FROM users
WHERE name ~ 'John.*';

Окна (Window Functions)

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

Преимущества

Ранжирование и сортировка: Окна позволяют ранжировать строки по определенным критериям.
Агрегация: Выполняют агрегационные операции над набором строк.
Сравнение значений: Позволяют сравнивать значения между соседними строками.

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

1. ROW_NUMBER()

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

SELECT id,
       name,
       ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM users;

2. RANK() и DENSE_RANK()

Используются для ранжирования строк. RANK() оставляет пробелы в ранжировании, если есть равные значения, а DENSE_RANK() не оставляет пробелов.

SELECT id,
       score,
       RANK() OVER (ORDER BY score DESC)       AS rank,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

3. LAG() и LEAD()

Позволяют получить доступ к предыдущей (LAG()) или следующей (LEAD()) строке.

SELECT id,
       name,
       LAG(name) OVER (ORDER BY id)  AS prev_name,
       LEAD(name) OVER (ORDER BY id) AS next_name
FROM users;

4. SUM(), AVG(), MAX(), MIN()

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

SELECT id,
       name,
       SUM(score) OVER (PARTITION BY category) AS total_score
FROM scores;

Ключевые понятия

OVER: Определяет набор строк, над которым выполняется оконная функция.
PARTITION BY: Разделяет результат на группы по указанным столбцам.
ORDER BY: Определяет порядок строк внутри каждой группы.
ROWS или RANGE: Определяет границы окна (например, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING).

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

Ранжирование студентов по баллам

SELECT student_id,
       score,
       RANK() OVER (ORDER BY score DESC) AS rank
FROM exam_results;

Агрегация продаж по категориям

SELECT category,
       SUM(amount) OVER (PARTITION BY category) AS total_sales
FROM sales;

Сравнение значений между соседними строками

SELECT id,
       value,
       LAG(value) OVER (ORDER BY id)  AS prev_value,
       LEAD(value) OVER (ORDER BY id) AS next_value
FROM data;

Last modified: 01 October 2025