Тема 3. Этапы проектирования баз данных — Практическое занятие №5: записи и логические условия
1. Роль DML и логических предикатов в СУБД
От схемы к управлению данными
Проектирование реляционной схемы определяет структуру таблиц и связи. Однако ценность базы раскрывается при наполнении и актуализации кортежей данных.
Операторы манипулирования данными (DML) требуют абсолютной точности предикатов: одна логическая ошибка способна исказить или безвозвратно стереть ценные записи предприятия.
Ключевые темы практикума
• Модификация кортежей: операторы UPDATE, DELETE, INSERT.
• Логические связки: AND, OR, NOT и приоритеты скобок.
• Предикаты SQL: BETWEEN, IN, LIKE / ILIKE, IS NULL.
• Трехзначная логика SQL: модель 3VL (TRUE, FALSE, UNKNOWN).
• Транзакционная безопасность и протоколирование до/после.
2. Модификация кортежей: синтаксис UPDATE
Базовый шаблон оператора UPDATE
UPDATE <имя_таблицы>
SET <столбец_1> = <новое_значение_или_выражение>,
<столбец_2> = <выражение_2>
WHERE <логический_предикат_фильтрации>;
Механизм исполнения СУБД:
1. СУБД сканирует таблицу (по индексам или последовательно) и для каждого кортежа вычисляет выражение в блоке WHERE.
2. Если результат предиката равен строго TRUE, кортеж помечается для обновления.
3. Применяются новые значения столбцов из блока SET.
4. Проверяются все ограничения целостности (CHECK, FOREIGN KEY, NOT NULL).
3. Главный кошмар инженера БД: UPDATE/DELETE без WHERE
Что происходит при отсутствии WHERE?
В реляционной теории операции применяются ко всему отношению! Если секция WHERE опущена, СУБД обновит или удалит ВСЕ кортежи таблицы без предупреждения.
-- КАТАСТРОФА: обнулит цены ВСЕМ товарам склада! UPDATE products SET price = 0;
Защитные меры и safe updates
1. Включение режима безопасных обновлений (sql_safe_updates = 1 в MySQL).
2. Обязательный предварительный просмотр выборки через SELECT.
3. Заворачивание любых модификаций в явные транзакции с предварительной проверкой числа затронутых строк.
4. Булева логика в SQL: приоритет операций
Иерархия логических операторов
В языке SQL связки имеют строгий приоритет исполнения:
1. Скобки: ( ... ) — наивысший приоритет 2. Отрицание: NOT — унарный оператор 3. Конъюнкция: AND — логическое И (умножение) 4. Дизъюнкция: OR — логическое ИЛИ (сложение)
Опасная ловушка без скобок
WHERE brand = 'Toyota' OR brand = 'Lexus' AND year >= 2023
Трактуется СУБД как: «Все Toyota любого года выпуска» ИЛИ «Lexus от 2023 года»!
Корректная скобочная форма
WHERE (brand = 'Toyota' OR brand = 'Lexus') AND year >= 2023
Строго автомобили Toyota либо Lexus, выпущенные не ранее 2023 года.
5. Предикат BETWEEN: тонкости граничных условий
Синтаксис и семантический эквивалент
WHERE stock_quantity BETWEEN 5 AND 50
В стандарте SQL оператор BETWEEN a AND b строго инклюзивен (включает обе границы):
-- Полный эквивалент: WHERE stock_quantity >= 5 AND stock_quantity <= 50
Критическое правило порядка аргументов
Первый операнд обязан быть меньше либо равен второму (a <= b). Выражение BETWEEN 50 AND 5 в SQL всегда возвратит FALSE, так как ни одно число не может быть одновременно >= 50 и <= 5!
6. Предикат множества IN
Компактная замена цепочке дизъюнкций (OR)
Вместо громоздких конструкций вида experience = 'Senior' OR experience = 'Lead' применяется лаконичный оператор IN:
WHERE experience_level IN ('Senior', 'Lead')
Коварство NOT IN при наличии NULL
WHERE role NOT IN ('Admin', NULL)
Если во множестве оказывается значение NULL, предикат NOT IN вычисляется как UNKNOWN для любой строки, и запрос возвращает 0 результатов!
Безопасная альтернатива
Использование NOT EXISTS либо фильтрация неопределенных значений: WHERE role NOT IN (...) AND role IS NOT NULL.
7. Текстовые шаблоны: LIKE и ILIKE
Спецсимволы сопоставления
• Символ процента % — любая последовательность из 0 и более символов.
• Символ подчеркивания _ — строго один произвольный символ.
-- Содержит 'PostgreSQL' в любом месте строки: WHERE skills LIKE '%PostgreSQL%'
Регистрозависимость
• В стандарте SQL и PostgreSQL: LIKE чувствителен к регистру.
• ILIKE (расширение PostgreSQL) выполняет регистронезависимый поиск.
• В MySQL с collation utf8mb4_general_ci оператор LIKE по умолчанию нечувствителен к регистру.
8. Трехзначная логика SQL: 3VL
Природа значения UNKNOWN
В отличие от классической булевой логики (TRUE/FALSE), в базах данных значение NULL означает отсутствие информации или неизвестность. Любое скалярное сравнение со значением NULL порождает третье логическое состояние — UNKNOWN.
NULL = NULL --> UNKNOWN (не TRUE!) NULL <> 5 --> UNKNOWN NULL = 0 --> UNKNOWN
В секциях WHERE и HAVING строка отбирается ТОЛЬКО тогда, когда итоговое логическое условие вычисляется строго в TRUE. Значения FALSE и UNKNOWN отбрасываются!
9. Таблицы истинности 3VL: AND, OR, NOT
Таблица истинности AND (И)
| AND | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | FALSE | UNKNOWN |
| FALSE | FALSE | FALSE | FALSE |
| UNKNOWN | UNKNOWN | FALSE | UNKNOWN |
Таблица истинности OR (ИЛИ)
| OR | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | UNKNOWN |
| UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
Отрицание: NOT TRUE = FALSE, NOT FALSE = TRUE, но NOT UNKNOWN = UNKNOWN!
10. Предикаты проверки отсутствия: IS NULL
Фатальная ошибка: WHERE col = NULL
-- ОШИБКА: всегда возвращает UNKNOWN! SELECT * FROM job_candidates WHERE hire_date = NULL;
Поскольку hire_date = NULL дает UNKNOWN для каждой строки, секция WHERE отбросит все кортежи. Запрос вернет пустую таблицу!
Единственно верный синтаксис
-- ПРАВИЛЬНО: предикат возвращает TRUE или FALSE SELECT * FROM job_candidates WHERE hire_date IS NULL;
Специализированные предикаты IS NULL и IS NOT NULL работают в двузначной логике и достоверно определяют факт отсутствия значения.
11. Золотой регламент безопасной модификации данных
5 обязательных шагов промышленного DBA
Шаг 1. Предварительный SELECT: пишем и выполняем точный SELECT с целевым предикатом. Визуально убеждаемся, что отобраны только нужные строки.
Шаг 2. Открытие транзакции: даем команду BEGIN TRANSACTION; (или START TRANSACTION;).
Шаг 3. Исполнение модификации: запускаем команду UPDATE или DELETE с протестированным условием.
Шаг 4. Контроль счетчика: проверяем сообщение СУБД (rows affected) и сверяем с ожидаемым количеством строк.
Шаг 5. Фиксация или откат: при совпадении даем COMMIT;, при малейшем сомнении или ошибке — немедленный ROLLBACK;.
12. Протоколирование состояний таблицы: До / После
Инженерный аудит изменений
Каждая операция массовой модификации должна сопровождаться протоколом, отражающим исходные и результирующие значения ключевых атрибутов:
| ID кортежа | Исходное состояние (ДО) | Критерий соответствия | Итоговое состояние (ПОСЛЕ) | Статус строки |
|---|---|---|---|---|
| 101 | Марка='Toyota', Год=2023, Статус='В наличии' | Подходит под фильтр | Марка='Toyota', Год=2023, Статус='Забронирован' | МОДИФИЦИРОВАНА |
| 103 | Марка='Toyota', Год=2021, Статус='В наличии' | Год 2021 < 2023 (FALSE) | Марка='Toyota', Год=2021, Статус='В наличии' | БЕЗ ИЗМЕНЕНИЙ |
13. Инструктаж к лабораторному практикуму
4 дифференцированных варианта
Вариант 1 (Аптека): пересчет цен (+10%) для витаминов с остатком в интервале BETWEEN и будущей датой годности.
Вариант 2 (Автосалон): перевод в статус 'Забронирован' автомобилей марок Toyota/Lexus с контролем года и пробега.
Вариант 3 (Кадры): актуализация статуса соискателей уровня Senior/Lead со знанием PostgreSQL без даты найма.
Вариант 4 (Склад): списание неликвида поставщика 'Логистик-Групп' по браку или сроку хранения.
Критерии успешной сдачи
• Написание проверочного SELECT-запроса.
• Составление эталонного UPDATE с транзакцией.
• Полное заполнение таблицы протокола «до/после».
• Верное решение контрольного вопроса по логике 3VL.
14. Архитектурная строгость: цена ошибок в предикатах и DML
Автор: Randall Munroe. Первоисточник: xkcd.com/327. Лицензия: Creative Commons Attribution-NonCommercial 2.5 Generic (CC BY-NC 2.5).
Связь с темой занятия: В реляционных базах данных небрежное отношение к условиям фильтрации, DML-запросам и санитарии данных приводит к катастрофическим последствиям — от непреднамеренной перезаписи всех кортежей из-за забытого
WHERE до классического удаления целых таблиц: Robert'); DROP TABLE Students;--. Строгая дисциплина транзакций, проверочных запросов и логических предикатов — первостепенная обязанность инженера БД.