Тема 5. Организация запросов SQL — Операторы манипулирования данными

1. Место DML в архитектуре реляционной СУБД

Язык манипулирования данными (Data Manipulation Language) управляет фактическим состоянием кортежей таблиц в рамках транзакционной модели ACID.

2. Оператор INSERT INTO: Одиночная и пакетная вставка

Явное перечисление колонок защищает код от изменений схемы. Пакетная вставка кратно снижает накладные расходы.

-- Пакетная вставка (Batch Multi-row INSERT) 5 позиций оборудования
INSERT INTO warehouse_inventory (sku, product_name, quantity, unit_price)
VALUES 
  ('SKU-101', 'Процессор Intel Core i5-13400', 15, 18200.00),
  ('SKU-102', 'Материнская плата B760 DDR5', 20, 12500.00),
  ('SKU-103', 'Модуль памяти DDR5 32GB Kit', 35, 9800.00),
  ('SKU-104', 'Блок питания 750W Gold', 18, 8100.00),
  ('SKU-105', 'Корпус ATX Mesh Black', 12, 5400.00)
RETURNING id, sku, created_at;

3. Вставка результатов выборки (INSERT INTO ... SELECT)

Позволяет производить миграцию и трансформацию массивов данных исключительно силами ядра СУБД.

-- Перенос закрытых кредитных договоров в исторический архив
INSERT INTO contracts_archive (
  contract_id, client_id, close_date, final_balance
)
SELECT 
  c.id, c.client_id, c.closed_at, c.balance
FROM active_contracts c
WHERE c.status = 'CLOSED' 
  AND c.closed_at < CURRENT_DATE - INTERVAL '1 year';

4. Клауза RETURNING: Атомарный возврат данных

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

INSERT INTO orders (client_id, amount)
VALUES (42, 15990.00)
RETURNING 
  order_id, 
  created_at, 
  status;

5. Проблема коллизий и Race Condition при вставке

Классический антипаттерн «проверить существование в SELECT, а затем выполнить INSERT» приводит к коллизиям в продакшне.

-- Поток А: Проверяет наличие товара
SELECT id FROM warehouse WHERE sku = 'SKU-101'; -- (Пусто!)
-- Поток Б: Параллельно проверяет тот же товар
SELECT id FROM warehouse WHERE sku = 'SKU-101'; -- (Тоже пусто!)
-- Поток А выполняет: INSERT INTO warehouse ... -- УСПЕШНО!
-- Поток Б выполняет: INSERT INTO warehouse ... -- ОШИБКА: unique_violation!

Вывод: Проверка на уровне приложения не атомарна. Требуется нативная поддержка со стороны ядра СУБД — механизм UPSERT.

6. Концепция UPSERT: Атомарная обработка конфликтов

Конструкция ON CONFLICT (target_column) перехватывает ошибку уникальности прямо в момент вставки.

INSERT INTO tags (tag_name)
VALUES ('postgresql'), ('sql')
ON CONFLICT (tag_name) 
DO NOTHING;

INSERT INTO metrics (host, cpu)
VALUES ('srv-1', 78.4)
ON CONFLICT (host) 
DO UPDATE SET 
  cpu = EXCLUDED.cpu;

7. Архитектура псевдотаблицы EXCLUDED

Виртуальная таблица EXCLUDED содержит кортеж, который предлагался к вставке.

-- Идемпотентный UPSERT: суммирование остатков на складе при повторной накладной
INSERT INTO warehouse_inventory (sku, product_name, quantity, unit_price)
VALUES ('SKU-101', 'Процессор Intel Core i5-13400', 10, 17900.00)
ON CONFLICT (sku) 
DO UPDATE SET 
  -- склад.остаток (текущий) + EXCLUDED.остаток (новый приход)
  quantity = warehouse_inventory.quantity + EXCLUDED.quantity,
  unit_price = EXCLUDED.unit_price,
  updated_at = CURRENT_TIMESTAMP
RETURNING warehouse_inventory.id, warehouse_inventory.sku, warehouse_inventory.quantity;

8. Оператор UPDATE: Модификация по предикату

Оператор UPDATE обновляет существующие кортежи. Забытая секция WHERE приводит к катастрофическим последствиям.

-- Селективное обновление
UPDATE credit_contracts
SET 
  status = 'DELINQUENT',
  penalty_rate = 0.05
WHERE overdue_days > 30 
  AND status = 'ACTIVE'
RETURNING id, client_id;

9. Конструкция UPDATE ... FROM (Join-модификация)

Специфика PostgreSQL: объединение обновляемой таблицы с другими таблицами через секцию FROM.

-- Обновление статуса приема врача по внешнему соединению с таблицей Оплаты
UPDATE doctor_appointments AS a
SET 
  status = 'CONFIRMED',
  updated_at = CURRENT_TIMESTAMP
FROM payment_invoices AS p
WHERE a.id = p.appointment_id
  AND p.payment_status = 'PAID'
  AND a.status = 'PENDING_PAYMENT'
RETURNING a.id, a.patient_id, a.status;

10. Оператор DELETE: Селективное удаление и аудит

Удаление кортежей с возвратом удаленных строк для дальнейшей обработки.

-- Селективное удаление отмененных приемов старше 1 года
DELETE FROM doctor_appointments
WHERE status = 'CANCELLED' 
  AND appointment_date < CURRENT_DATE - INTERVAL '1 year'
RETURNING id, patient_fio, appointment_date, cancellation_reason;

11. Физическое сравнение: DELETE против TRUNCATE

12. Внутренняя архитектура: DML и журнал WAL

Журнал предзаписи WAL гарантирует сохранность данных (Crash Recovery), но создает нагрузку на дисковый ввод-вывод.

13. Влияние на B-Tree индексы и оптимизация HOT

Каждый дополнительный индекс на таблице ускоряет чтение, но замедляет DML-операции.

14. Безопасность DML: Цена небрежности при формировании запросов

Тематический интернет-мем: «Exploits of a Mom» (культовый веб-комикс про «Little Bobby Tables», xkcd №327).
Автор: Randall Munroe. Первоисточник: xkcd.com/327. Лицензия: Creative Commons Attribution-NonCommercial 2.5 Generic (CC BY-NC 2.5).
Связь с темой занятия: Мама назвала сына Robert'); DROP TABLE Students;--. Склейка неэкранированных строковых переменных в операторах INSERT, UPDATE и DELETE открывает критическую уязвимость внедрения SQL-инъекций (SQL Injection). В промышленной разработке манипулирование данными должно выполняться строго через параметризованные запросы (Prepared Statements) и механизмы безопасного экранирования.