SQL

Весь SQL — язык запросов к реляционным базам — на одной странице. Почти без прозы: всё объяснено прямо в комментариях рабочего кода. Примеры на диалекте, близком к PostgreSQL; мелкие отличия от MySQL/SQLite отмечены в комментариях.

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

Таблица — это набор строк со столбцами фиксированных типов. Комментарии в SQL начинаются с -- (однострочный) или заключаются в /* ... */ (блочный).

-- Однострочный комментарий
/* Блочный
   комментарий */

-- CREATE TABLE создаёт таблицу. Имя_столбца ТИП [ограничения]
CREATE TABLE authors (
    id          SERIAL PRIMARY KEY,   -- автоинкремент (MySQL: AUTO_INCREMENT)
    name        VARCHAR(100) NOT NULL, -- строка до 100 символов, обязательна
    country     VARCHAR(50),           -- может быть NULL (нет значения)
    born_year   INT                    -- целое число
);

CREATE TABLE books (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author_id   INT REFERENCES authors(id), -- внешний ключ на authors.id
    price       NUMERIC(8, 2),  -- число с фикс. точностью: до 8 цифр, 2 после точки
    pages       INT,
    published   DATE,           -- дата, напр. '2021-05-30'
    in_stock    BOOLEAN DEFAULT TRUE -- логический тип, по умолчанию TRUE
);

-- Частые типы: INTEGER/INT, BIGINT, NUMERIC/DECIMAL, REAL/DOUBLE,
--              VARCHAR(n), TEXT, BOOLEAN, DATE, TIMESTAMP
-- Частые ограничения: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK, REFERENCES

2. Вставка данных

-- INSERT INTO таблица (столбцы) VALUES (значения);
INSERT INTO authors (name, country, born_year) VALUES
    ('Джордж Оруэлл', 'Великобритания', 1903),
    ('Рэй Брэдбери',  'США',            1920),
    ('Фёдор Достоевский', 'Россия',     1821); -- несколько строк за раз

INSERT INTO books (title, author_id, price, pages, published) VALUES
    ('1984',                 1, 599.00, 328, '1949-06-08'),
    ('Скотный двор',         1, 399.50, 112, '1945-08-17'),
    ('451 градус по Фаренгейту', 2, 549.00, 256, '1953-10-19'),
    ('Преступление и наказание', 3, 720.00, 671, '1866-01-01'),
    ('Марсианские хроники',  2, 480.00, 222, '1950-05-04');

-- Можно опускать столбцы со значением по умолчанию или NULL:
INSERT INTO books (title, author_id) VALUES ('Черновик без цены', 3);
-- price, pages, published станут NULL; in_stock = TRUE (DEFAULT)

3. Выборка

-- SELECT столбцы FROM таблица;
SELECT id, title, price FROM books; -- только указанные столбцы

SELECT * FROM authors; -- * = все столбцы

-- AS задаёт псевдоним (alias) столбцу или таблице
SELECT title AS название,
       price AS цена_руб
FROM books;

-- Вычисляемые столбцы
SELECT title,
       price,
       price * 0.9 AS цена_со_скидкой -- арифметика прямо в выборке
FROM books;

-- DISTINCT убирает дубликаты
SELECT DISTINCT country FROM authors;
-- → Великобритания, США, Россия (каждая страна один раз)

-- Конкатенация строк: || (Postgres/SQLite), CONCAT(...) (MySQL)
SELECT name || ' (' || country || ')' AS подпись FROM authors;
-- → Джордж Оруэлл (Великобритания)

4. Фильтрация

-- WHERE отбирает строки по условию
SELECT title, price FROM books WHERE price > 500;

-- Операторы сравнения: = <> (или !=) < > <= >=
SELECT * FROM books WHERE pages >= 300;

-- Логические: AND, OR, NOT
SELECT * FROM books
WHERE price < 600 AND pages < 300;

SELECT * FROM books
WHERE author_id = 1 OR author_id = 2;

-- LIKE: шаблон. % = любое число символов, _ = ровно один символ
SELECT title FROM books WHERE title LIKE '1%';   -- начинается с '1' → '1984'
SELECT title FROM books WHERE title LIKE '%хроники'; -- кончается на 'хроники'
SELECT name  FROM authors WHERE name LIKE '_эй%'; -- 2-я буква 'э' → Рэй Брэдбери
-- ILIKE — регистронезависимый поиск (Postgres)

-- IN: значение из списка (короче, чем цепочка OR)
SELECT * FROM books WHERE author_id IN (1, 3);

-- BETWEEN: диапазон включительно (a <= x <= b)
SELECT title, price FROM books WHERE price BETWEEN 400 AND 600;

-- IS NULL / IS NOT NULL — проверка на отсутствие значения.
-- ВАЖНО: '= NULL' не работает, всегда используйте IS NULL
SELECT title FROM books WHERE published IS NULL;
SELECT title FROM books WHERE price IS NOT NULL;

5. Сортировка и лимит

-- ORDER BY сортирует. ASC — по возрастанию (по умолчанию), DESC — по убыванию
SELECT title, price FROM books ORDER BY price DESC;

-- Сортировка по нескольким столбцам (сначала по 1-му, при равенстве — по 2-му)
SELECT title, author_id, price
FROM books
ORDER BY author_id ASC, price DESC;

-- LIMIT ограничивает число строк. OFFSET пропускает первые N строк
SELECT title, price FROM books ORDER BY price DESC LIMIT 3; -- топ-3 дорогих

-- Пагинация: страница 2 по 2 записи (пропустить 2, взять 2)
SELECT title FROM books ORDER BY id LIMIT 2 OFFSET 2;
-- MySQL также допускает: LIMIT 2, 2  (offset, count)
-- SQL Server: OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY

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

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

SELECT COUNT(*)     AS всего_книг   FROM books; -- число строк → 6
SELECT COUNT(price) AS с_ценой      FROM books; -- COUNT(столбец) игнорирует NULL
SELECT SUM(price)   AS сумма_цен    FROM books; -- сумма
SELECT AVG(price)   AS средняя_цена FROM books; -- среднее (NULL пропускаются)
SELECT MIN(price)   AS самая_дешёвая FROM books; -- минимум
SELECT MAX(pages)   AS макс_страниц  FROM books; -- максимум

-- Несколько агрегатов в одном запросе
SELECT COUNT(*)            AS книг,
       ROUND(AVG(price),2) AS средняя,
       MAX(price)          AS дороже_всех
FROM books
WHERE in_stock = TRUE;

7. Группировка

-- GROUP BY разбивает строки на группы; агрегат считается для каждой группы
SELECT author_id,
       COUNT(*)   AS число_книг,
       AVG(price) AS средняя_цена
FROM books
GROUP BY author_id;
-- → по одной строке на каждого author_id с его статистикой

-- HAVING фильтрует УЖЕ СГРУППИРОВАННЫЕ данные (WHERE — до группировки)
SELECT author_id, COUNT(*) AS число_книг
FROM books
GROUP BY author_id
HAVING COUNT(*) >= 2; -- только авторы с 2+ книгами

-- Порядок выполнения: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
SELECT author_id, SUM(price) AS оборот
FROM books
WHERE in_stock = TRUE      -- фильтр строк ДО группировки
GROUP BY author_id
HAVING SUM(price) > 800     -- фильтр групп ПОСЛЕ группировки
ORDER BY оборот DESC;

8. Соединения

JOIN объединяет строки из разных таблиц по условию (обычно по внешнему ключу).

-- INNER JOIN: только совпавшие пары строк из обеих таблиц
SELECT b.title, a.name AS автор
FROM books AS b
INNER JOIN authors AS a ON b.author_id = a.id;
-- → '1984' | Джордж Оруэлл, '451 градус...' | Рэй Брэдбери, ...
-- Книга без author_id в результат НЕ попадёт

-- LEFT JOIN: ВСЕ строки слева + совпадения справа (нет пары → NULL справа)
SELECT a.name, b.title
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.id;
-- → автор без книг тоже попадёт, у него b.title = NULL

-- Найти авторов без книг: LEFT JOIN + проверка на NULL
SELECT a.name
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.id
WHERE b.id IS NULL;

-- RIGHT JOIN: зеркало LEFT — все строки СПРАВА + совпадения слева
SELECT a.name, b.title
FROM authors AS a
RIGHT JOIN books AS b ON b.author_id = a.id;
-- → все книги, даже если автор не найден (a.name = NULL)
-- SQLite не поддерживает RIGHT JOIN — поменяйте таблицы местами и используйте LEFT

-- JOIN + агрегаты: сколько книг у каждого автора
SELECT a.name, COUNT(b.id) AS книг
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.id
GROUP BY a.name
ORDER BY книг DESC;

9. Подзапросы и CTE

-- Подзапрос в WHERE: книги дороже средней цены
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books); -- внутренний запрос → одно число

-- Подзапрос с IN: книги авторов из США
SELECT title
FROM books
WHERE author_id IN (SELECT id FROM authors WHERE country = 'США');

-- Подзапрос в FROM (производная таблица): обязателен псевдоним
SELECT автор, книг
FROM (
    SELECT a.name AS автор, COUNT(b.id) AS книг
    FROM authors a LEFT JOIN books b ON b.author_id = a.id
    GROUP BY a.name
) AS сводка
WHERE книг > 1;

-- CTE (Common Table Expression) через WITH — то же, но читабельнее.
-- Именованный временный результат, на который можно ссылаться ниже
WITH stats AS (
    SELECT author_id, COUNT(*) AS книг, SUM(price) AS оборот
    FROM books
    GROUP BY author_id
)
SELECT a.name, s.книг, s.оборот
FROM stats s
JOIN authors a ON a.id = s.author_id
WHERE s.оборот > 500
ORDER BY s.оборот DESC;

-- Несколько CTE через запятую:
WITH дорогие AS (SELECT * FROM books WHERE price > 500),
     толстые AS (SELECT * FROM books WHERE pages > 300)
SELECT title FROM дорогие
UNION
SELECT title FROM толстые;

10. Объединение

-- UNION склеивает результаты двух SELECT (число и типы столбцов должны совпадать).
-- UNION убирает дубликаты строк
SELECT title FROM books WHERE price > 600
UNION
SELECT title FROM books WHERE pages > 300;

-- UNION ALL оставляет ВСЕ строки, включая дубли (быстрее — не дедуплицирует)
SELECT country FROM authors WHERE born_year < 1910
UNION ALL
SELECT country FROM authors WHERE born_year > 1915;

-- Удобно помечать источник строк литералом
SELECT title, 'дорогая' AS метка FROM books WHERE price > 600
UNION ALL
SELECT title, 'дешёвая' AS метка FROM books WHERE price <= 600;

-- Родственники: INTERSECT (пересечение), EXCEPT/MINUS (разность)

11. Изменение данных

-- UPDATE меняет существующие строки. БЕЗ WHERE обновятся ВСЕ строки!
UPDATE books
SET price = price * 1.1   -- поднять цену на 10%
WHERE author_id = 1;

-- Можно менять несколько столбцов сразу
UPDATE books
SET price = 999.00,
    in_stock = FALSE
WHERE title = '1984';

-- DELETE удаляет строки. БЕЗ WHERE удалятся ВСЕ строки!
DELETE FROM books WHERE price IS NULL;

DELETE FROM books WHERE author_id NOT IN (SELECT id FROM authors);

-- TRUNCATE быстро очищает всю таблицу (без условий, без журналирования каждой строки)
-- TRUNCATE TABLE books;

12. Изменение схемы и индексы

-- ALTER TABLE меняет структуру существующей таблицы
ALTER TABLE books ADD COLUMN genre VARCHAR(50); -- добавить столбец
ALTER TABLE books ALTER COLUMN price SET NOT NULL; -- сделать обязательным (Postgres)
ALTER TABLE books RENAME COLUMN genre TO category; -- переименовать столбец
ALTER TABLE books DROP COLUMN category;            -- удалить столбец
ALTER TABLE books RENAME TO catalog;               -- переименовать таблицу
ALTER TABLE catalog RENAME TO books;               -- вернём обратно

-- CREATE INDEX ускоряет поиск/сортировку по столбцу (ценой места и записи)
CREATE INDEX idx_books_author ON books(author_id);
CREATE INDEX idx_books_price  ON books(price);
CREATE UNIQUE INDEX idx_authors_name ON authors(name); -- ещё и запрещает дубли

-- DROP удаляет объекты целиком
DROP INDEX idx_books_price;        -- удалить индекс (Postgres/SQLite)
DROP TABLE IF EXISTS old_logs;     -- удалить таблицу, если существует
-- IF EXISTS подавляет ошибку, когда объекта нет

13. Оконные функции

Оконные функции считают агрегаты по «окну» строк, НЕ схлопывая их в одну (в отличие от GROUP BY каждая строка сохраняется).

-- OVER() задаёт окно. PARTITION BY делит на группы, ORDER BY задаёт порядок внутри.

-- ROW_NUMBER() — сквозной номер строки (1,2,3,...) внутри каждой партиции
SELECT title, author_id, price,
       ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY price DESC) AS номер
FROM books;
-- → у каждого автора своя нумерация книг от самой дорогой к дешёвой

-- RANK() — ранг с пропусками при равенстве (1,1,3); DENSE_RANK() — без пропусков (1,1,2)
SELECT title, price,
       RANK()       OVER (ORDER BY price DESC) AS ранг,
       DENSE_RANK() OVER (ORDER BY price DESC) AS плотный_ранг
FROM books;

-- Агрегат как оконная функция: видно и строку, и итог по её группе одновременно
SELECT title, author_id, price,
       SUM(price) OVER (PARTITION BY author_id) AS оборот_автора,
       AVG(price) OVER () AS средняя_по_всем
FROM books;

-- Типичный приём: «взять топ-1 в каждой группе» через нумерацию в CTE
WITH ranked AS (
    SELECT title, author_id, price,
           ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY price DESC) AS rn
    FROM books
)
SELECT title, author_id, price
FROM ranked
WHERE rn = 1; -- самая дорогая книга каждого автора