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

1. Педагогическая концепция и целеполагание

Настоящее занятие открывает фундаментальный блок языка манипулирования данными (Data Manipulation Language — DML). Если предыдущие занятия были сфокусированы на проектировании статических схем и декларативных ограничениях целостности (DDL), то данное занятие переводит студентов в плоскость динамической обработки информации в промышленных реляционных СУБД.

Дидактические цели:

• Обучающая: сформировать систему глубоких теоретических знаний о синтаксисе и внутренней архитектуре исполнения команд INSERT, UPDATE, DELETE; изучить расширенные механизмы манипулирования данными: пакетную вставку (multi-row), подзапросы в DML, возврат генерируемых колонок через RETURNING, идемпотентное слияние (UPSERT через ON CONFLICT DO UPDATE / DO NOTHING); раскрыть фундаментальные различия между DELETE и TRUNCATE на уровне журнала предзаписи WAL, блокировок таблиц и MVCC.
• Развивающая: развить навыки проектирования транзакционно-безопасных запросов модификации, критическое мышление при анализе конкурентных модификаций и деградации производительности индексов при массовом обновлении строк.
• Воспитательная: воспитать культуру инженерной ответственности за сохранность данных, осознание недопустимости бесконтрольных модификаций без предикатов в промышленных базах данных и соблюдение стандартов ACID.

Формируемые профессиональные компетенции (ФГОС СПО 09.02.07):

• ОК 01: Выбирать способы решения задач профессиональной деятельности применительно к различным контекстам.
• ОК 02: Использовать современные средства поиска, анализа и интерпретации информации.
• ПК 1.3: Осуществлять разработку кода программного модуля на основе готовых спецификаций и схем баз данных.
• ПК 2.4: Реализовывать запросы к базам данных на языке SQL для выборки, вставки, обновления и удаления записей.

2. Детальный хронометраж учебного занятия (80 и 60 минут)

В зависимости от графика учебного процесса преподаватель применяет основной 80-минутный регламент пары (двойной академический час) либо сокращенный 60-минутный регламент. Структура этапов сохраняется, оптимизируется глубина разбора кейсов и длительность дискуссионной части.

3. Фундаментальный технологический стек: Язык манипулирования данными (DML)

3.1. Оператор INSERT INTO: Архитектура и синтаксические конструкции

Оператор INSERT INTO предназначен для добавления новых строк в таблицу. В современных высоконагруженных системах одиночные вставки уступают место пакетированным и декларативно-безопасным операциям.

• Одиночная вставка (Single-row INSERT): явное указание перечня целевых столбцов защищает код от падения при изменении структуры таблицы (добавлении новых столбцов со значением по умолчанию).

-- Одиночная вставка с явным указанием целевых атрибутов
INSERT INTO warehouse_inventory (sku, product_name, quantity, unit_price)
VALUES ('SKU-00124', 'SSD NVMe 1TB Samsung 980', 45, 8490.00);

• Пакетная вставка (Multi-row INSERT): передача нескольких наборов значений через запятую в одном выражении VALUES. В отличие от N отдельных запросов, многострочный INSERT генерирует одну транзакцию, один сетевой раунд-трип к серверу и существенно снижает накладные расходы на парсинг SQL и запись в журнал предзаписи WAL.

-- Пакетная вставка (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;

• Вставка результатов выборки (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';

• Конструкция RETURNING: расширение PostgreSQL, позволяющее вернуть значения только что вставленных, обновленных или удаленных строк без выполнения вторичного запроса SELECT. Критически важно для получения значений суррогатных первичных ключей (id), значений по умолчанию (DEFAULT) или вычисляемых триггерами колонок.

• Обработка конфликтов уникальности (UPSERT: ON CONFLICT):

  До стандарта SQL:2016 для реализации паттерна «вставить, если нет, или обновить, если уже существует» разработчики прибегали к сложным процедурам с проверками IF EXISTS, страдающим от состояния гонки (Race Condition). В PostgreSQL реализован механизм атомарного разрешения конфликтов через конструкцию ON CONFLICT (target_column):

  1. DO NOTHING — операция игнорирует строки, вызывающие нарушение уникальности первичного или альтернативного ключа. Строка не вставляется, ошибка unique_violation подавляется.
  2. DO UPDATE SET ... — при коллизии уникальности СУБД выполняет модификацию существующей строки. Доступ к значениям, которые предполагалось вставить, осуществляется через специальную виртуальную псевдотаблицу 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 
  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;

3.2. Оператор UPDATE: Модификация по предикату и реляционные соединения

Оператор UPDATE изменяет существующие кортежи. В реляционной архитектуре СУБД с моделью многоверсионности (MVCC) операция UPDATE физически представляет собой связку «пометить старую версию мертвой (dead tuple) + вставить новую версию (new tuple)».

• Модификация по предикату: обязательное наличие секции WHERE. Пропуск предиката приводит к немедленной перезаписи всей таблицы.
• Конструкция 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.doctor_id, a.status;

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

-- Массовое начисление процентов по кредитам на основе статуса профиля заемщика
UPDATE credit_contracts AS c
SET accrued_interest = accrued_interest + (c.principal_debt * c.interest_rate / 365)
WHERE c.status = 'ACTIVE'
  AND EXISTS (
    SELECT 1 FROM client_profiles p 
    WHERE p.id = c.client_id AND p.is_blocked = FALSE
  )
RETURNING c.id, c.client_id, c.accrued_interest;

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

Оператор DELETE удаляет строки, удовлетворяющие условию предиката WHERE. В PostgreSQL удаленные строки не освобождают место на диске немедленно: кортеж помечается удаленным в заголовке строки (устанавливается метка xmax, равная идентификатору текущей транзакции). Пространство очищается фоновым процессом VACUUM.

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

3.4. Архитектурное сравнение: DELETE vs TRUNCATE

Сравнение операторов DELETE и TRUNCATE — классический вопрос инженерных собеседований и важный раздел дисциплины. Студенты часто ошибочно считают TRUNCATE синтаксическим сахаром для «DELETE без WHERE».

4. Педагогические приемы и сценарии вовлечения

• Прием «Инцидент на продакшне»: в начале урока преподаватель показывает скриншот реального ретроспективного отчета, где junior-разработчик выполнил UPDATE users SET is_active = false; на рабочей базе в 10 миллионов клиентов, парализовав авторизацию. Этот прием моментально приковывает внимание и прививает привычку оборачивать любые ручные модификации в транзакции: BEGIN; ... ROLLBACK;.
• Сравнение производительности на проекторе (Live Demo): запуск двух скриптов на таблице в 500 000 записей — удаление через DELETE FROM test_data; (занимает 14.8 секунд, генерирует 120 Мб WAL) и через TRUNCATE test_data; (выполняется за 4 миллисекунды). Разница в 3500 раз производит неизгладимое впечатление на студентов.
• Анализ псевдотаблицы EXCLUDED: наглядная визуализация: «Представьте буфер обмена. Строка пришла на вставку — это EXCLUDED. Строка, которая уже лежала на диске — это имя_таблицы. В секции SET мы соединяем старое и новое!».

5. Классификатор типовых студенческих ошибок

6. Критерии оценивания практической работы (4-балльная шкала)

Контроль сформированности знаний и умений осуществляется по прозрачной 4-балльной шкале в соответствии с паспортом занятия:

• 2 балла — Составление базовых операторов манипулирования (INSERT, UPDATE, DELETE): студент безошибочно формулирует синтаксические конструкции модификации, указывает явные списки колонок, конструирует корректные предикаты фильтрации WHERE, исключающие случайную порчу соседних строк данных.
• 1 балл — Применение расширенных возможностей (RETURNING и UPSERT): студент грамотно использует конструкцию RETURNING для возврата ключевых атрибутов и реализует операцию ON CONFLICT ... DO UPDATE/DO NOTHING с корректным обращением к псевдотаблице EXCLUDED.
• 1 балл — Аналитическое обоснование, выводы и самопроверка: студент формулирует четкие аргументы различия DELETE и TRUNCATE, понимает транзакционные последствия (WAL, блокировки) и успешно отвечает на контрольные вопросы.

Итоговая дифференциация: 4 балла — отлично (материал освоен в полном объеме); 3 балла — хорошо (допущены незначительные синтаксические погрешности); 2 балла — удовлетворительно (выполнены только базовые команды); 1-0 баллов — не зачтено.