Операторы модификации данных и объединения таблиц
Мы продолжаем изучение SQL и переходим к трём оставшимся операторам:
вставке (INSERT),
удалению (DELETE) и
обновлению (UPDATE). Это самые простые операторы языка, однако они тесно связаны с
выборкой (SELECT) через подзапросы. Далее рассмотрим объединение наборов строк —
объединение (UNION) — и горизонтальное склеивание таблиц —
соединения (JOIN).
INSERT – вставка данных
Синтаксис выглядит просто:
INSERT INTO <таблица> (<поля>) VALUES (<значения>). Имена полей опциональны.
• Вставка всех полей. Если список полей не указан, подразумеваются все столбцы таблицы в том порядке, в котором они описаны.
Пример:
INSERT INTO Students VALUES (1, 'Иван', 'Иванов', 2, 24); — добавляется полная строка: идентификатор, имя, фамилия, код группы, возраст.
• Вставка части полей. Когда указывается конкретный перечень столбцов, остальные получают значение
NULL или
значение по умолчанию (DEFAULT), если оно задано. Вставка только идентификатора, имени и кода группы:
INSERT INTO Students (ID, FName, GroupID) VALUES (1, 'Иван', 2);
Пропущенные поля
LName и
Age заполнятся либо
NULL, либо значением по умолчанию.
Важно: если на столбец установлено ограничение
NOT NULL и нет умолчания, пропустить его нельзя.
Использование подзапросов в INSERT.
Значения не обязаны быть константами. Можно подставить результат подзапроса.
Допустим, мы хотим записать Ивана Иванова в группу с номером 481, но в таблице
Students хранится не номер, а идентификатор группы. Мы не знаем его явно, поэтому извлекаем подзапросом:
INSERT INTO Students (ID, FName, LName, GroupID, Age) VALUES (1, 'Иван', 'Иванов', (SELECT ID FROM Groups WHERE GroupNumber = '481 flash1'), 24);
Подзапрос может быть сколь угодно сложным.
Более хитрый пример: студент приписывается к группе с минимальным количеством учащихся. Подзапрос возвращает
TOP 1 идентификатор группы после соединения таблиц
Students и
Groups, группировки по
GroupID и сортировки по числу студентов.
DELETE – удаление данных
Синтаксис:
DELETE FROM <таблица> WHERE <условие>.
Условие задаёт, какие строки удалять. Без
WHERE очищается вся таблица.
• Удаление конкретной группы:
DELETE FROM Groups WHERE GroupNumber = '481 flash1';
• Удаление студентов с максимальным возрастом:
DELETE FROM Students WHERE Age = (SELECT MAX(Age) FROM Students);
• Удаление студентов со средней оценкой ниже 3.5. Здесь в подзапросе соединяются
Students и таблица с оценками, вычисляется среднее, и результат фильтруется:
DELETE FROM Students WHERE StudentID IN (SELECT StudentID FROM Results JOIN Students ON ... HAVING AVG(Mark) < 3.5);
Оператор
DELETE гибко интегрирует подзапросы на любом уровне вложенности.
UPDATE – обновление данных
Синтаксис:
UPDATE <таблица> SET <столбец> = <новое значение> WHERE <условие>.
• Глобальное обновление (редкий случай):
UPDATE Students SET GroupID = 1; — все студенты переводятся в группу 1.
• Целевое обновление:
UPDATE Students SET GroupID = 1 WHERE Age > 20;
• Обновление с подзапросом: выставить оценку 4 всем студентам, сдававшим экзамены в седьмом семестре. Подзапрос выбирает идентификаторы курсов, относящихся к семестру 7, и по ним фильтруются записи результатов:
UPDATE Results SET Mark = 4 WHERE CourseID IN (SELECT CourseID FROM Courses WHERE Semester = 7);
• Можно присвоить полю значение агрегата:
UPDATE Results SET Mark = (SELECT MAX(Mark) FROM Results) WHERE StudentID = 1;
Здесь конкретному студенту ставится максимальная оценка среди всех записей.
UNION – вертикальное объединение
Объединение (UNION) «склеивает» два набора строк с одинаковой структурой (одинаковое число и типы столбцов) в один длинный список.
Пример: собрать имена и фамилии как студентов, так и преподавателей.
SELECT FName, LName FROM Students UNION SELECT FName, LName FROM Teachers;
Результат — единый перечень имён и фамилий обеих ролей.
JOIN – горизонтальное соединение таблиц
Соединение (JOIN) связывает строки двух таблиц по общему столбцу (условию). Это горизонтальная склейка. Ранее мы фактически использовали соединение, перечисляя таблицы через запятую и задавая условие в
WHERE. Явный синтаксис
JOIN даёт больше возможностей.
Основные типы:
•
Внутреннее соединение (INNER JOIN) — возвращает только строки, для которых нашлось соответствие в обеих таблицах. Никаких
NULL вместо несопоставленных записей.
•
Внешнее соединение (OUTER JOIN) — сохраняет строки, не нашедшие пары, заполняя недостающие значения
NULL. Ключевое слово
OUTER можно опускать.
Разновидности:
o
Полное внешнее соединение (FULL OUTER JOIN) — берутся все строки из левой и правой таблиц. Если для строки левой таблицы нет соответствия в правой, поля правой таблицы получают
NULL, и наоборот.
Пример: соединить студентов и преподавателей по одинаковым имени и фамилии.
SELECT DISTINCT S.FName, S.LName, T.FName, T.LName FROM Students S FULL OUTER JOIN Teachers T ON S.FName = T.FName AND S.LName = T.LName;
Если есть тёзки — показываются оба имени, если нет пары — соответствующие поля
NULL.
DISTINCT убирает дубликаты тёзок.
o
Левое внешнее соединение (LEFT OUTER JOIN или LEFT JOIN) — возвращаются все строки левой таблицы. К ним по возможности присоединяются строки правой, несовпавшие правые отбрасываются.
o
Правое внешнее соединение (RIGHT OUTER JOIN или RIGHT JOIN) — наоборот, сохраняются все строки правой таблицы.
•
Перекрёстное соединение (CROSS JOIN) — декартово произведение (каждая строка левой таблицы сочетается с каждой строкой правой). Будет рассмотрено на практике.
Понимание поведения
JOIN — один из самых важных и нетривиальных аспектов SQL. Детальная проработка на примерах позволит полностью освоить этот механизм.
Далее в курсе: краткая лекция по оптимизации запросов, большой блок практики, завершающий раздел об индексах и планах выполнения.
Краткие итоги
Центральная тема — неразрывная связь операторов модификации данных с механизмом подзапросов. Это превращает INSERT, DELETE и UPDATE из простых средств построчной правки в мощные инструменты, способные оперировать производными и агрегированными данными. Благодаря подзапросам вставка новой записи может учитывать текущее состояние базы — например, автоматически распределять студентов по наименее заполненным группам, а удаление или обновление за один проход затрагивать множества, вычисляемые динамически. Такой подход избавляет от необходимости выносить логику на уровень приложения и снижает риск рассогласования данных.
Параллельно рассматривается фундаментальная для реляционной модели операция соединения таблиц. Разбор внешних соединений (LEFT, RIGHT, FULL) в сравнении с внутренним даёт ключ к корректному построению запросов, где важно не потерять информативные строки. Понимание того, в каких случаях появятся NULL-значения, а в каких записи будут исключены, напрямую влияет на достоверность аналитических выборок и отчётов. UNION, в свою очередь, решает обратную задачу — объединяет структурно однородные, но логически разные наборы в единый поток данных.
Практическое применение этих знаний охватывает широкий круг задач: от каскадной коррекции связанных сущностей до построения гибких витрин и миграции данных. Владение идиомой «подзапрос внутри модифицирующего оператора» позволяет лаконично выражать сложную бизнес-логику, а грамотный выбор типа JOIN — гарантировать полноту и непротиворечивость извлекаемой информации. Именно эта двойственность (модификация + чтение, вертикальное + горизонтальное комбинирование) формирует базу для дальнейшего перехода к вопросам оптимизации, где уже встанет задача не просто получить верный результат, но и сделать это эффективно.
1. INSERT позволяет вставлять как константы, так и результаты подзапросов.
2. При частичной вставке пропущенные столбцы получают NULL или значение по умолчанию; столбцы с NOT NULL без умолчания пропускать нельзя.
3. DELETE без WHERE очищает всю таблицу; с условием или подзапросом удаляет только выбранные строки.
4. UPDATE изменяет значения столбцов в строках, удовлетворяющих условию; без условия затронет все записи.
5. Подзапросы в INSERT, DELETE, UPDATE позволяют динамически вычислять вставляемые, удаляемые или обновляемые значения.
6. UNION объединяет строки двух таблиц с одинаковой структурой в один вертикальный набор.
7. JOIN выполняет горизонтальное соединение таблиц по условию, связывая связанные строки.
8. INNER JOIN возвращает только строки с совпадениями в обеих таблицах.
9. LEFT JOIN сохраняет все строки левой таблицы, заполняя пропуски из правой NULL.
10. RIGHT JOIN сохраняет все строки правой таблицы, заполняя пропуски из левой NULL.
11. FULL OUTER JOIN возвращает все строки обеих таблиц, проставляя NULL при отсутствии пары.
12. Понимание поведения JOIN и использование подзапросов — основа построения сложных корректных запросов.
1. Чем отличается синтаксис вставки полного набора значений от вставки только указанных столбцов?
2. Когда разрешается пропустить поле при вставке, а когда это приведёт к ошибке?
3. Каким образом подзапрос может участвовать в операции INSERT? Приведите общую схему.
4. Что произойдёт при выполнении DELETE FROM без условия WHERE?
5. Как удалить строки, удовлетворяющие вычисляемому в подзапросе критерию?
6. Для чего в UPDATE используется сочетание SET и WHERE?
7. Как с помощью UPDATE присвоить столбцу значение, полученное агрегатной функцией из другой части данных?
8. В чём суть операции UNION и какое требование предъявляется к структуре объединяемых таблиц?
9. Чем горизонтальное соединение JOIN отличается от UNION?
10. Какие строки попадут в результат INNER JOIN, если не для всех записей левой таблицы есть соответствие в правой?
11. Чем различаются LEFT JOIN и RIGHT JOIN по отношению к отсутствующим парам?
12. Как работает FULL OUTER JOIN и в каких случаях в результирующем наборе появляются NULL?
