ОП.08 Основы проектирования баз данных — Тема 5. Организация запросов SQL — Операторы манипулирования данными
«Здравствуйте, уважаемые студенты! Рад всех видеть. Прошу старосту назвать отсутствующих. Проверьте готовность мониторов и терминалов. Сегодня мы переходим от проектирования статических таблиц к живой динамике баз данных — языку манипулирования данными, или DML.
Начнем наш урок с реального случая из жизни крупной финтех-компании. Несколько лет назад молодой разработчик получил задачу перевести статус одного тестового договора в состояние "архивный". Он открыл терминал базы данных и набрал команду: UPDATE contracts SET status = 'ARCHIVED'; и нажал клавишу Enter.
Через пять секунд в колл-центр банка поступило 12 000 звонков: все активные кредитные карты, депозиты и ипотечные договоры полутора миллионов клиентов моментально заблокировались. Почему это произошло? Потому что разработчик забыл написать секцию WHERE id = 42. Сегодня мы досконально разберем, как устроен синтаксис DML, почему DML требует высочайшей дисциплины, как работают операции пакетной вставки, возврата сгенерированных ключей RETURNING, атомарного слияния UPSERT и чем селективный DELETE отличается от мгновенного TRUNCATE».
«Перед тем как погрузиться в детали, давайте освежим базовые понятия. Мы уже умеем создавать таблицы через DDL. Вопрос к аудитории: какие ограничения целостности защищают таблицу от появления дубликатов строк?»
«Ограничения первичного ключа PRIMARY KEY и уникальности UNIQUE. Они строят уникальный B-Tree индекс и не позволяют вставить вторую строку с совпадающим значением ключевых колонок».
«Абсолютно верно! А теперь представим практическую ситуацию: интернет-магазин принимает заказы. Два пользователя одновременно нажимают кнопку "Купить последний экземпляр видеокарты". Наше веб-приложение делает запрос SELECT stock FROM warehouse WHERE product_id = 100, видит stock = 1, и оба параллельных потока пытаются сделать INSERT INTO orders. Что произойдет на стыке миллисекунд?»
«Возникнет состояние гонки — Race Condition! Если нет правильной блокировки или встроенного механизма обработки коллизий в самой базе, второй запрос либо выбросит исключение unique_violation и пользователь получит ошибку 500, либо произойдет овербукинг».
«Именно так! Решение этих проблем переносится с ненадежного уровня приложения внутрь транзакционного ядра реляционной СУБД. И сегодня мы узнаем, как СУБД делает это элегантно и атомарно».
«Переходим к глубокому изучению операторов. Первый оператор — INSERT INTO. Обратите внимание на слайд 3. Главное инженерное правило промышленного SQL: никогда не пишите INSERT без явного перечисления колонок! Если завтра аналитик добавит в таблицу служебный столбец created_at TIMESTAMP DEFAULT now(), ваш код без указания колонок немедленно сломается из-за несовпадения числа атрибутов.
Следующий критический момент — пакетная вставка (Multi-row INSERT). Когда вам нужно сохранить накладную из 100 позиций, никогда не вызывайте INSERT 100 раз в цикле бэкенда! Один multi-row запрос передает массив кортежей через запятую в секции VALUES. Это один сетевой пакет, один цикл компиляции запроса оптимизатором и один синхронный сброс журнала предзаписи WAL. Скорость возрастает в десятки раз!
Теперь обратите внимание на слайд 5: конструкция RETURNING. В PostgreSQL вам не нужно после вставки делать костыльный SELECT max(id) или вызывать функции сессий. Напишите в конце запроса RETURNING id, created_at, и сервер моментально вернет сгенерированные значения первичных ключей.
А теперь кульминация работы с INSERT — слайды 7 и 8: концепция UPSERT (атомарное слияние строк). В PostgreSQL она реализуется конструкцией ON CONFLICT (колонка_уникальности) DO UPDATE SET ... или DO NOTHING.
Посмотрите на код на слайде 8. Когда возникает коллизия уникальности по артикулу товара sku, СУБД активирует специальную виртуальную псевдотаблицу EXCLUDED. В EXCLUDED лежат значения, которые мы пытались вставить, а под именем самой таблицы — те значения, которые уже хранятся на диске. Формула SET quantity = warehouse.quantity + EXCLUDED.quantity берет старый остаток и прибавляет к нему свежий приход! Это выполняется строго атомарно, без блокировок приложения и риска коллизий.
Переходим к оператору UPDATE — слайды 9 и 10. В PostgreSQL очень мощная конструкция UPDATE ... FROM. Если вам нужно обновить статус приема пациента по данным таблицы оплаты, вы связываете таблицы через FROM payment_invoices p WHERE appointment.id = p.appointment_id. Но внимание: саму обновляемую таблицу в секцию FROM повторно писать запрещено, иначе вы получите декартово умножение таблицы саму на себя!
И, наконец, слайды 11 и 12: сравнение DELETE и TRUNCATE. Запомните навсегда: DELETE — это DML, он идет по строкам, проверяет предикат WHERE, помечает каждый кортеж мертвым в заголовке MVCC (ставит xmax), генерирует гигабайты записей в журнал предзаписи WAL и не освобождает дисковое пространство файловой системе. А TRUNCATE — это DDL! Он монопольно блокирует таблицу блокировкой ACCESS EXCLUSIVE, отбрасывает старый файл таблицы на диске и выделяет новый пустой файл (relfilenode) за 3 миллисекунды. Если вам нужно очистить всю таблицу или лог — используйте TRUNCATE, но помните, что у него нет условия WHERE!»
«Переключаемся на демонстрационный терминал psql. Давайте проверим наши знания на практике в реальной базе данных складского учета.
Шаг первый: выполняем пакетную вставку новой партии товаров. Смотрите на экран»:
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)
RETURNING id, sku, quantity;«А теперь моделируем повторный приход накладной с артикулом SKU-101 в количестве 10 штук по новой цене 17 900 рублей. Запускаем 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 id, sku, quantity, unit_price;«Посмотрите на результат! СУБД перехватила коллизию уникальности, обратилась к EXCLUDED.quantity, прибавила 10 к 15 и выдала 25. Без единой ошибки в приложении!»
«Теперь наступает ваша очередь продемонстрировать инженерное мастерство. Откройте в браузерах интерактивный рабочий лист zanyatie.html. Введите свои реальные ФИО и группу.
Вам предложено четыре прикладных варианта:
INSERT INTO ... SELECT и начислить проценты через массовый UPDATE.ON CONFLICT (студент_id, дисциплина_id) и очистить неподтвержденные профили.Все введенные данные автоматически сохраняются в вашем браузере. Приступайте к выполнению, я готов ответить на индивидуальные вопросы».
«Время самостоятельной работы подошло к концу. Давайте посмотрим на слайд 15 нашей презентации. Перед вами легендарный мем про "Little Bobby Tables". Мама записала имя сына в свидетельстве как Robert'); DROP TABLE Students;--.
Почему этот мем стал символом профессии? Потому что если вы конкатенируете строки в коде приложения для DML-запросов, злоумышленник может манипулировать вашими данными как угодно. Всегда используйте параметризованные запросы!
Подведем итоги: работа оценивается по 4-балльной критериальной рубрике: 2 балла за безошибочные операторы INSERT/UPDATE/DELETE, 1 балл за грамотное использование RETURNING и UPSERT, и 1 балл за обоснование архитектурных различий и самопроверку.
В локальной папке также размещено резервное дистанционное задание domashnee_zadanie.html. Обратите внимание на плашку: выполнять его нужно только в том случае, если вам назначена индивидуальная отработка или дистанционный формат. Срок сдачи будет сообщен дополнительно. Спасибо всем за отличную и продуктивную работу!»