SQL

SQL (англ. Structured Query Language — «язык структурированных запросов»; произносится «эс-кью-эль» или «сиквел») — декларативный язык для работы с реляционными базами данных. На SQL создают таблицы, добавляют и изменяют данные, пишут запросы и управляют правами доступа. Запрос описывает желаемый результат, а не алгоритм: как именно его получить, решает оптимизатор СУБД.

SQL — один из самых долгоживущих языков программирования: ему больше 50 лет, и его понимают PostgreSQL, MySQL, SQLite, Oracle, SQL Server, аналитические системы вроде ClickHouse, а также многие инструменты обработки данных.

История

Язык разработали Дональд Чемберлин и Рэймонд Бойс в IBM для экспериментальной СУБД System R. Первая публикация вышла в 1974 году под названием SEQUEL (Structured English Query Language); из-за конфликта с торговой маркой его сократили до SQL.^[1]

Ни одна СУБД не реализует стандарт полностью; у каждой есть свой диалект и расширения. PostgreSQL публикует таблицу соответствия стандарту по каждой возможности.^[3]

Части языка

• DDL (Data Definition Language) — описание структуры: CREATE, ALTER, DROP для таблиц, индексов, представлений.
• DML (Data Manipulation Language) — работа с данными: SELECT, INSERT, UPDATE, DELETE, MERGE.
• DCL (Data Control Language) — права: GRANT, REVOKE.
• TCL (Transaction Control) — управление транзакциями: BEGIN, COMMIT, ROLLBACK, SAVEPOINT.

Запрос SELECT

Записанный порядок предложений не совпадает с логическим порядком выполнения — это частый источник путаницы у новичков:

SELECT   department, count(*) AS staff, avg(salary) AS avg_salary  -- 5
FROM     employees                                                 -- 1
WHERE    hired_at >= '2020-01-01'                                  -- 2
GROUP BY department                                                -- 3
HAVING   count(*) > 5                                              -- 4
ORDER BY avg_salary DESC                                           -- 6
LIMIT    10;                                                       -- 7

Сначала выбираются строки источника (FROM и соединения), затем фильтруются (WHERE), группируются (GROUP BY), фильтруются группы (HAVING), вычисляются выражения списка выборки, и лишь потом идут сортировка и ограничение. Поэтому псевдоним avg_salary нельзя использовать в WHERE, но можно в ORDER BY.

Соединения

JOIN объединяет строки разных таблиц по условию: INNER JOIN оставляет только совпавшие пары, LEFT JOIN сохраняет все строки левой таблицы (подставляя NULL при отсутствии пары), FULL JOIN — строки обеих, CROSS JOIN даёт декартово произведение.

Оконные функции и CTE

Оконные функции вычисляют значение по группе строк, не схлопывая их, — например, нарастающий итог или ранг внутри категории.^[4] Общие табличные выражения (WITH) дают имена промежуточным результатам и позволяют писать рекурсивные запросы — например, обход дерева категорий:

WITH monthly AS (
    SELECT date_trunc('month', created_at) AS month, sum(total) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT month,
       revenue,
       sum(revenue) OVER (ORDER BY month) AS running_total,
       revenue - lag(revenue) OVER (ORDER BY month) AS delta
FROM monthly;

NULL и трёхзначная логика

NULL в SQL означает «значение неизвестно». Любое сравнение с NULL даёт не true или false, а NULL, поэтому WHERE x = NULL не находит ничего — нужно писать x IS NULL. Агрегаты вроде count(column) и avg пропускают NULL, а count(*) считает все строки.

Безопасность: SQL-инъекции

Если подставлять пользовательский ввод в текст запроса склейкой строк, злоумышленник может изменить смысл запроса — это SQL-инъекция, одна из самых известных уязвимостей веб-приложений. Защита — параметризованные запросы, в которых значения передаются отдельно от текста SQL:^[5]

# плохо: f"SELECT * FROM users WHERE email = '{email}'"
cur.execute("SELECT * FROM users WHERE email = %s", (email,))

ORM и построители запросов используют параметры автоматически.

Диалекты

Синтаксис различается в деталях: ограничение выборки (LIMIT в PostgreSQL, MySQL и SQLite против TOP в T-SQL и стандартного FETCH FIRST n ROWS ONLY), автоинкремент, функции работы с датами и строками, процедурные расширения (PL/pgSQL, T-SQL, PL/SQL). SQLite использует динамическую типизацию и поддерживает подмножество стандарта.^[6] Посмотреть, как СУБД выполнит конкретный запрос, позволяет команда EXPLAIN.^[2]

См. также

• Реляционная база данных
• PostgreSQL
• Индекс (базы данных)
• EXPLAIN и план запроса
• ORM

Источники

1. D. D. Chamberlin, R. F. Boyce — SEQUEL: A Structured English Query Language (1974)
2. PostgreSQL Documentation — The SQL Language
3. PostgreSQL Documentation — SQL Conformance
4. PostgreSQL Documentation — Window Functions
5. OWASP — SQL Injection Prevention Cheat Sheet
6. SQLite — SQL As Understood By SQLite