Урок 05

CRUD-операции

Что такое CRUD

CRUD — Create, Read, Update, Delete. Четыре базовые операции.

CRUD	SQL
Create	`INSERT`
Read	`SELECT`
Update	`UPDATE`
Delete	`DELETE`

INSERT — создание

Одна строка:

INSERT INTO users (email, name) VALUES ('a@b.com', 'Алиса');

Несколько:

INSERT INTO users (email, name) VALUES
    ('a@b.com', 'Алиса'),
    ('c@d.com', 'Боб'),
    ('e@f.com', 'Катя');

Что даёт: один запрос, одна транзакция. В разы быстрее, чем в цикле.

Вернуть вставленное:

INSERT INTO users (email, name)
VALUES ('a@b.com', 'Алиса')
RETURNING id, email, created_at;

Что даёт: не нужен второй запрос за id.

При конфликте — игнор:

INSERT INTO users (email, name)
VALUES ('a@b.com', 'Алиса')
ON CONFLICT (email) DO NOTHING;

При конфликте — обновить (upsert):

INSERT INTO users (email, name)
VALUES ('a@b.com', 'Алиса')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()
RETURNING *;

EXCLUDED — то, что пытались вставить.

Правило: upsert через ON CONFLICT — стандарт для идемпотентных операций.

Массовый INSERT из SELECT:

INSERT INTO users_backup (email, name)
SELECT email, name FROM users WHERE created_at < NOW() - INTERVAL '1 year';

SELECT — чтение

Все колонки:

SELECT * FROM users;

Конкретные:

SELECT id, email FROM users;

⚠️ SELECT * в коде — плохо. При изменении схемы может сломаться.

Псевдонимы:

SELECT
    email AS user_email,
    price * quantity AS total
FROM order_items;

DISTINCT:

SELECT DISTINCT status FROM orders;
SELECT DISTINCT city, country FROM users;

LIMIT и OFFSET:

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

Пагинация:

LIMIT — сколько.
OFFSET — пропустить.

⚠️ OFFSET 1000000 медленно — на больших таблицах используйте cursor pagination (WHERE id > last_id).

UPDATE — обновление

Одна колонка:

UPDATE users SET name = 'Новое имя' WHERE id = 1;

Несколько:

UPDATE users
SET name = 'Алиса',
    email = 'alice@b.com',
    updated_at = NOW()
WHERE id = 1;

Из другой таблицы:

UPDATE orders o
SET total = sub.sum
FROM (
    SELECT order_id, SUM(price * quantity) AS sum
    FROM order_items
    GROUP BY order_id
) sub
WHERE o.id = sub.order_id;

Массово с условием:

UPDATE products
SET stock = 0
WHERE category_id = 5 AND stock < 10;

RETURNING:

UPDATE users SET name = 'Алиса' WHERE id = 1
RETURNING id, name, updated_at;

⚠️ Забыли WHERE — обновите всю таблицу. В продакшене сначала проверяйте на SELECT.

DELETE — удаление

DELETE FROM users WHERE id = 1;

DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < NOW() - INTERVAL '1 year';

RETURNING:

DELETE FROM users WHERE id = 1
RETURNING email, name;

Все строки:

DELETE FROM users;
TRUNCATE users;   -- быстрее

⚠️ DELETE без WHERE — снесёт всё. Проверяйте через SELECT сначала.

WHERE — условия

Сравнения:

WHERE age = 25
WHERE age != 25
WHERE age <> 25   -- то же
WHERE age > 18
WHERE age >= 18

Логические:

WHERE age >= 18 AND country = 'RU'
WHERE status = 'paid' OR status = 'shipped'
WHERE NOT is_active

IN:

WHERE status IN ('pending', 'paid')
WHERE id NOT IN (1, 2, 3)

BETWEEN:

WHERE age BETWEEN 18 AND 65
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'

LIKE / ILIKE:

WHERE email LIKE '%@gmail.com'      -- регистрозависимо
WHERE email ILIKE '%GMAIL%'          -- регистронезависимо
WHERE name LIKE 'А%'                 -- начинается с А
WHERE name LIKE '_лиса'              -- 2-й символ 'л', _ = один

NULL:

WHERE bio IS NULL
WHERE bio IS NOT NULL

⚠️ WHERE bio = NULL — не сработает. Только IS NULL.

ORDER BY

ORDER BY created_at DESC
ORDER BY name ASC, age DESC
ORDER BY LOWER(email)
ORDER BY id NULLS LAST

Что даёт: сортировка. NULLS FIRST / LAST — где NULL.

Агрегаты

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;
SELECT AVG(age) FROM users;
SELECT MIN(age), MAX(age) FROM users;
SELECT SUM(total) FROM orders;

⚠️ COUNT(col) не считает NULL. COUNT(*) считает все.

GROUP BY:

SELECT country, COUNT(*) AS users_count
FROM users
GROUP BY country
ORDER BY users_count DESC;

HAVING (фильтр после группировки):

SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country
HAVING COUNT(*) > 10;

Правило: WHERE — до группировки. HAVING — после.

Транзакции

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- или ROLLBACK; если что-то не так

Что даёт: либо всё, либо ничего.

SAVEPOINT:

BEGIN;
SAVEPOINT sp1;
-- ...
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Возврат данных

INSERT / UPDATE / DELETE поддерживают RETURNING:

INSERT INTO users (email) VALUES ('a@b.com') RETURNING id;
UPDATE users SET name = 'X' WHERE id = 1 RETURNING *;
DELETE FROM users WHERE id = 1 RETURNING email;

Что даёт:

Не нужен отдельный SELECT.
Атомарно.
Меньше запросов.

Пример: полный цикл

-- 1. Создание
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Алиса')
RETURNING id;
-- id = 1

-- 2. Чтение
SELECT id, email, name, created_at
FROM users
WHERE id = 1;

-- 3. Обновление
UPDATE users
SET name = 'Алиса П.'
WHERE id = 1
RETURNING *;

-- 4. Upsert
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Алиса Петрова')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING *;

-- 5. Удаление
DELETE FROM users WHERE id = 1
RETURNING email;

Безопасность

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

Node.js:

// ❌
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

// ✅
await pool.query('SELECT * FROM users WHERE email = $1', [email]);

Laravel:

// ❌
DB::select("SELECT * FROM users WHERE email = '{$email}'");

// ✅
DB::select('SELECT * FROM users WHERE email = ?', [$email]);

// ✅ Ещё лучше
User::where('email', $email)->first();

Что даёт: защита от SQL-инъекций.

⚠️ Никогда не конкатенируйте SQL со вводом пользователя.

Частые ошибки

1. UPDATE без WHERE:

UPDATE users SET is_active = FALSE;   -- обнулит всех

Решение: сначала SELECT с тем же WHERE, потом UPDATE.

2. SELECT * в коде:

Сломается при добавлении колонки

Решение: явно перечислять колонки.

3. INSERT в цикле:

foreach ($users as $u) {
    DB::insert(...);   // 1000 запросов
}

Решение: INSERT с несколькими VALUES или upsert.

4. Сравнение с NULL через =:

WHERE bio = NULL   -- всегда false

Решение: IS NULL.

5. Транзакция без try/catch:

BEGIN;
-- ошибка
COMMIT;   -- уже не откатится

Решение: откат при ошибке.

6. DELETE FROM table без бэкапа:

Ошибка стоит дорого

Решение: BEGIN; DELETE ...; SELECT для проверки; COMMIT; — сначала в транзакции.

Практика

1. Создайте таблицу и вставьте 3 строки одним INSERT.

2. Сделайте upsert — попробуйте вставить с существующим email.

3. Обновите с RETURNING.

4. Удалите с RETURNING и проверьте, что вернулось.

5. Транзакция: переведите 100 между двумя счетами и откатите.

Итог

INSERT: одна/несколько строк, RETURNING, ON CONFLICT.
SELECT: WHERE, ORDER BY, LIMIT.
UPDATE: всегда с WHERE, RETURNING.
DELETE: сначала SELECT, потом DELETE.
Транзакции: BEGIN / COMMIT / ROLLBACK.
Параметризация: обязательно.

Дальше — SELECT и фильтрация.