GitHub

УП.11 - 3 - Модификация данных в базе world

Цель работы

Освоить операции модификации данных в реляционной базе данных: добавление новых записей (INSERT), обновление существующих (UPDATE), удаление записей (DELETE). Научиться применять эти операции с учётом ограничений целостности (первичные и внешние ключи, проверочные ограничения). Получить практический опыт изменения данных в демонстрационной базе world.

Теоретическая часть

В предыдущих работах вы изучали структуру базы данных world и выполняли сложные запросы SELECT для извлечения информации. Теперь мы переходим к операциям, которые изменяют состояние базы данных:

  1. INSERT – добавление новых строк в таблицу.
  2. UPDATE – изменение значений в существующих строках.
  3. DELETE – удаление строк из таблицы.

Особенности модификации данных в базе world

База world имеет следующие ограничения целостности, которые необходимо учитывать при выполнении операций модификации:

  • Первичные ключи – гарантируют уникальность записей. При INSERT нельзя дублировать значения первичного ключа.
  • Внешние ключи – обеспечивают ссылочную целостность:
    • city.CountryCode ссылается на country.Code
    • country.Capital ссылается на city.ID
    • countrylanguage.CountryCode ссылается на country.Code
  • Ограничения NOT NULL – некоторые поля обязательны для заполнения.
  • Типы данных – значения должны соответствовать объявленным типам (например, Continent может быть только из определённого списка ENUM).

Безопасность операций модификации

Перед выполнением операций UPDATE и DELETE рекомендуется:

  1. Сначала выполнить SELECT с теми же условиями, чтобы убедиться, что будут затронуты нужные строки.
  2. Использовать транзакции (BEGIN, COMMIT, ROLLBACK), чтобы иметь возможность отката изменений в случае ошибки.
  3. Делать резервные копии данных перед массовыми изменениями.

Практические задания

1. Добавление новой страны

Добавьте в таблицу country новую страну с кодом 'ZZZ', названием "Новая Земля", континентом "Europe", регионом "Eastern Europe", площадью 1000.00, населением 50000, формой правления "Republic", местным названием "New Land", двухбуквенным кодом "NZ". Остальные поля могут быть NULL.

Решение

INSERT INTO world.country (
    Code, Name, Continent, Region, SurfaceArea,
    Population, GovernmentForm, LocalName, Code2
) VALUES (
    'ZZZ', 'Новая Земля', 'Europe', 'Eastern Europe',
    1000.00, 50000, 'Republic', 'New Land', 'NZ'
);

Проверка добавления

SELECT * FROM world.country WHERE Code = 'ZZZ';

Пример результата:

+------+----------------+-----------+---------------+-------------+------------+-----------------+----------------+------+--------+-----------+----------------+----------------+------------+--------+-------+
| Code | Name           | Continent | Region        | SurfaceArea | IndepYear  | Population      | LifeExpectancy | GNP  | GNPOld | LocalName | GovernmentForm | HeadOfState    | Capital    | Code2  |
+------+----------------+-----------+---------------+-------------+------------+-----------------+----------------+------+--------+-----------+----------------+----------------+------------+--------+-------+
| ZZZ  | Новая Земля    | Europe    | Eastern Europe|     1000.00 | NULL       |           50000 | NULL           | NULL | NULL   | New Land  | Republic       | NULL           | NULL       | NZ     |
+------+----------------+-----------+---------------+-------------+------------+-----------------+----------------+------+--------+-----------+----------------+----------------+------------+--------+-------+

2. Добавление городов для новой страны

Добавьте два города для страны 'ZZZ' в таблицу city:

  • Город "Столица" с населением 20000, район "Central"
  • Город "Порт" с населением 15000, район "Coastal"

Обратите внимание, что поле ID является автоинкрементным, поэтому его указывать не нужно.

Решение

INSERT INTO world.city (Name, CountryCode, District, Population) VALUES
    ('Столица', 'ZZZ', 'Central', 20000),
    ('Порт', 'ZZZ', 'Coastal', 15000);

Проверка добавления

SELECT * FROM world.city WHERE CountryCode = 'ZZZ';

Пример результата:

+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
| 4080 | Столица       | ZZZ         | Central  |      20000 |
| 4081 | Порт          | ZZZ         | Coastal  |      15000 |
+----+----------------+-------------+----------+------------+

3. Обновление столицы страны

Обновите запись страны 'ZZZ', установив в качестве столицы город "Столица" (его ID можно получить из предыдущего запроса).

Решение

-- Получаем ID города "Столица"
SET @capital_id = (SELECT ID FROM world.city WHERE Name = 'Столица' AND CountryCode = 'ZZZ');

-- Обновляем страну
UPDATE world.country 
SET Capital = @capital_id 
WHERE Code = 'ZZZ';

Проверка обновления

SELECT 
    c.Name AS country_name,
    c.Capital,
    ci.Name AS capital_name
FROM world.country c
LEFT JOIN world.city ci ON c.Capital = ci.ID
WHERE c.Code = 'ZZZ';

4. Добавление языков для новой страны

Добавьте в таблицу countrylanguage два языка для страны 'ZZZ':

  • Язык "Новоземельский" является официальным (IsOfficial = 'T'), процент носителей 80%
  • Язык "Английский" не является официальным (IsOfficial = 'F'), процент носителей 20%

Решение

INSERT INTO world.countrylanguage (CountryCode, Language, IsOfficial, Percentage) VALUES
    ('ZZZ', 'Новоземельский', 'T', 80.0),
    ('ZZZ', 'Английский', 'F', 20.0);

Проверка добавления

SELECT * FROM world.countrylanguage WHERE CountryCode = 'ZZZ';

5. Обновление данных существующей страны

Измените население страны "Россия" (код 'RUS'), увеличив его на 1 миллион человек. Также обновите ожидаемую продолжительность жизни, установив значение 73.5.

Решение

UPDATE world.country 
SET 
    Population = Population + 1000000,
    LifeExpectancy = 73.5
WHERE Code = 'RUS';

Проверка обновления

SELECT Name, Population, LifeExpectancy 
FROM world.country 
WHERE Code = 'RUS';

6. Обновление данных с использованием JOIN

Увеличьте население всех городов страны "Германия" (код 'DEU') на 10%. Используйте UPDATE с JOIN (или подзапрос) для определения городов Германии.

Решение

UPDATE world.city 
SET Population = Population * 1.1
WHERE CountryCode = 'DEU';

Проверка обновления

SELECT Name, Population 
FROM world.city 
WHERE CountryCode = 'DEU' 
ORDER BY Population DESC 
LIMIT 5;

7. Удаление записей с проверкой ограничений

Попробуйте удалить страну 'ZZZ' из таблицы country. Объясните, что произойдёт и почему.

Решение

DELETE FROM world.country WHERE Code = 'ZZZ';

Результат: Операция завершится ошибкой из-за нарушения ограничения внешнего ключа. В таблицах city и countrylanguage есть записи, ссылающиеся на страну 'ZZZ'. Чтобы удалить страну, нужно сначала удалить зависимые записи.

8. Каскадное удаление (теоретическое задание)

Предположим, что внешние ключи в базе world были созданы с опцией ON DELETE CASCADE. Опишите, что произойдёт при удалении страны 'ZZZ' в этом случае.

Решение

При наличии ON DELETE CASCADE удаление записи в таблице country автоматически приведёт к удалению всех связанных записей в таблицах city и countrylanguage, где CountryCode = 'ZZZ'. Это позволяет поддерживать целостность базы данных без необходимости явного удаления зависимых записей.

9. Удаление зависимых записей

Удалите все города и языки, связанные со страной 'ZZZ', а затем удалите саму страну.

Решение

-- Удаляем города
DELETE FROM world.city WHERE CountryCode = 'ZZZ';

-- Удаляем языки
DELETE FROM world.countrylanguage WHERE CountryCode = 'ZZZ';

-- Теперь можно удалить страну
DELETE FROM world.country WHERE Code = 'ZZZ';

Проверка удаления

SELECT COUNT(*) AS country_count FROM world.country WHERE Code = 'ZZZ';
SELECT COUNT(*) AS city_count FROM world.city WHERE CountryCode = 'ZZZ';
SELECT COUNT(*) AS language_count FROM world.countrylanguage WHERE CountryCode = 'ZZZ';

10. Обновление с использованием подзапросов

Для всех стран, где официальным языком является "English", увеличьте ВНП (GNP) на 5%.

Решение

UPDATE world.country c
SET c.GNP = c.GNP * 1.05
WHERE c.Code IN (
    SELECT cl.CountryCode 
    FROM world.countrylanguage cl 
    WHERE cl.Language = 'English' AND cl.IsOfficial = 'T'
);

Проверка обновления

SELECT 
    c.Name,
    c.GNP,
    c.GNP / 1.05 AS old_gnp
FROM world.country c
WHERE c.Code IN (
    SELECT cl.CountryCode 
    FROM world.countrylanguage cl 
    WHERE cl.Language = 'English' AND cl.IsOfficial = 'T'
)
LIMIT 5;

11. Использование транзакций

Выполните несколько операций модификации в рамках одной транзакции:

  1. Добавьте новый город "Тестовый" для страны 'RUS'.
  2. Обновите население этого города до 5000.
  3. Удалите этот город.

Все операции должны быть выполнены как единое целое. Используйте ROLLBACK, чтобы отменить изменения в конце.

Решение

-- Начало транзакции
START TRANSACTION;

-- 1. Добавление города
INSERT INTO world.city (Name, CountryCode, District, Population) 
VALUES ('Тестовый', 'RUS', 'Test District', 1000);

-- Сохраняем ID добавленного города
SET @test_city_id = LAST_INSERT_ID();

-- 2. Обновление населения
UPDATE world.city 
SET Population = 5000 
WHERE ID = @test_city_id;

-- 3. Удаление города
DELETE FROM world.city WHERE ID = @test_city_id;

-- Откат транзакции (чтобы не сохранять изменения)
ROLLBACK;

-- Проверка, что город не остался в базе
SELECT * FROM world.city WHERE Name = 'Тестовый';

12. Массовое обновление с условием CASE

Обновите форму правления в таблице country в зависимости от текущего значения:

  • Если GovernmentForm содержит слово "Republic", измените на "Democratic Republic"
  • Если GovernmentForm содержит слово "Monarchy", измените на "Constitutional Monarchy"
  • Для всех остальных форм добавьте префикс "Former: "

Решение

UPDATE world.country 
SET GovernmentForm = 
    CASE 
        WHEN GovernmentForm LIKE '%Republic%' THEN 'Democratic Republic'
        WHEN GovernmentForm LIKE '%Monarchy%' THEN 'Constitutional Monarchy'
        ELSE CONCAT('Former: ', GovernmentForm)
    END
WHERE GovernmentForm IS NOT NULL;

Проверка обновления

SELECT DISTINCT GovernmentForm 
FROM world.country 
ORDER BY GovernmentForm 
LIMIT 10;

Заключение

В ходе выполнения данной практики вы освоили основные операции модификации данных в SQL: INSERT, UPDATE и DELETE. Вы научились учитывать ограничения целостности, использовать транзакции для безопасного выполнения изменений, а также применять подзапросы и JOIN в операциях обновления.

Рекомендации для самостоятельной работы:

  1. Попробуйте создать резервную копию базы данных перед выполнением операций модификации.
  2. Экспериментируйте с оператором MERGE (если поддерживается вашей СУБД) для сложных сценариев "вставки или обновления".
  3. Изучите работу с триггерами, которые автоматически выполняют действия при модификации данных.
  4. Попрактикуйтесь в написании скриптов миграции данных, которые преобразуют структуру и содержимое базы данных.

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

Последнее обновление:
© Кафедра информационных технологий ЧУВО «ВШП», 2026. Версия: 0.35.30
Материалы доступны в соответствии с лицензией: