ОП.08 - 16 - Разнесение данных из одной таблицы. Конструкция INSERT INTO SELECT

Примеры данной темы используют учебную БД:

Разнесение данных из одной таблицы

Одной из важных задач при работе с базами данных является разнесение данных из одной таблицы в несколько. Это может быть необходимо, например, для удобства анализа данных или для более эффективного хранения информации. Разнесение таблиц в БД из одной таблицы по разным таблицам позволяет улучшить организацию данных и повысить производительность системы.

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

Разнесение таблиц по разным таблицам позволяет разделить данные на логически связанные группы и оптимизировать запросы к БД. Например, таблица с информацией о клиентах может содержать поля с их персональными данными, а таблица с информацией об автомобилях — поля с характеристиками автомобилей. Таким образом, при выполнении запроса на выборку информации о конкретном автомобиле не придется сканировать всю таблицу с информацией о клиентах.

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

Для выполнения данной задачи в MySQL 8 используется конструкция INSERT INTO SELECT, которая имеет следующий синтаксис:

Скопировать все столбцы из одной таблицы в другую таблицу:

INSERT INTO table_new
SELECT * FROM table_old
WHERE condition;

Скопировать только некоторые столбцы из одной таблицы в другую таблицу:

INSERT INTO table_new (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table_old
WHERE condition;

Рассмотрим несколько примеров.

Примеры использования конструкции INSERT INTO SELECT

[!INFO]
В данных примерах мы намеренно пренебрегаем предварительной организацией структуры связей создаваемых таблиц, т.к. это будет раскрыто подробнее в рамках отдельной темы. Здесь и далее, связи между создаваемыми таблицами отсутствуют, но подразумеваются.

Пример 1. Создание таблицы брендов и вынесение брендов в отдельную таблицу

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

Для этого создадим таблицу brands и выполним следующий запрос:

CREATE TABLE brands (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
);

После чего заполним таблицу значениями из tmp:

INSERT INTO brands(name)
SELECT DISTINCT brand
FROM tmp;

При этом помним что нам нужны только уникальные значения, поэтому используем модификатор DISTINCT.

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

Пример 2. Создание таблицы моделей и вынесение моделей в отдельную таблицу

Подобным образом можно вынести уникальные значения моделей в отдельную таблицу models. Для этого создадим таблицу models и выполним следующий запрос:

CREATE TABLE models (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
);

После чего заполним таблицу значениями из tmp:

INSERT INTO models(name)
SELECT DISTINCT model
FROM tmp;

Здесь также нам нужны только уникальные значения, поэтому используем модификатор DISTINCT.

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

[!INFO]
Конечно, учитывая что модели напрямую связаны с брендами, логичнее сразу учесть это при разборе данных, чему будет посвящена следующая тема, а в рамках данного примера мы рассмотрим как это сделать постфактум в конце лекции.

Пример 3. Создание таблицы характеристик и вынесение характеристик в отдельную таблицу

Подобным образом можно вынести характеристики в отдельную таблицу cars. Для этого создадим таблицу cars и выполним следующий запрос:

CREATE TABLE cars (
  id INT PRIMARY KEY AUTO_INCREMENT,
  year INT,
  power INT,
  drivetrain VARCHAR(255),
  mileage INT,
  cost DECIMAL(10,2)
);

После чего заполним таблицу значениями из tmp:

INSERT INTO cars(year,power,drivetrain,mileage,cost)
SELECT year,power,drivetrain,mileage,cost
FROM tmp;

В данном случае, наборы характеристик соответствуют КАЖДОМУ автомобилю, поэтому DISTINCT не используется.

В результате выполнения запроса мы получим таблицу cars со всеми значениями характеристик.

Пример 4. Создание таблицы продаж и вынесение продаж в отдельную таблицу

Ну и наконец, если мы хотим вынести данные о продажах в отдельную таблицу sales. Для этого создадим таблицу sales и выполним следующий запрос:

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sold_date DATE,
  client_name VARCHAR(255)
);

После чего заполним таблицу значениями из tmp:

INSERT INTO sales(sold_date,client_name)
SELECT sold_date,client_name
FROM tmp
WHERE (sold_date IS NOT NULL) AND (client_name IS NOT NULL)
;

В данном случае, с продажами всё несколько интереснее, т.к. не каждый автомобиль в автосалоне является проданным, но использовать DISTINCT мы не можем, т.к. даже если совпадет дата продажи и имя клиента, в реальность это могут быть две разные продажи, поэтому вместо DISTINCT мы используем условие.

В результате выполнения запроса мы получим таблицу sales с данными о продажах.

Конструкция INSERT INTO SELECT позволяет эффективно разносить данные из одной таблицы в несколько, что может быть полезно при работе с большими объемами информации.

Подготовка к разбору данных

Для добавления связи между таблицами брендов и моделей через внешние ключи можно выполнить следующий запрос:

ALTER TABLE models
ADD COLUMN brand_id INT,
ADD CONSTRAINT fk_brand
  FOREIGN KEY (brand_id)
  REFERENCES brands(id);

Этот запрос создаст внешний ключ brand_id в таблице models, который будет ссылаться на поле id в таблице brands.

[!WARNING]
При работе в режиме 'Safe mode' для обоих запросов, приведенных ниже, потребуется упомянуть в блоке WHERE первичный ключ обновляемой таблицы. То есть чтобы запросы сработали без отключения 'Safe mode', в конце обоих запросов после SET стоит добавить WHERE models.id > 0.

Далее, для обновления значений на основании данных из таблицы tmp, можно выполнить следующий запрос:

UPDATE models
SET brand_id = (
    SELECT id FROM brands
    WHERE brands.name IN (
        SELECT brand FROM tmp
        WHERE tmp.model = models.name
    )
);

В этом запросе внутренний подзапрос выбирает название бренда из таблицы tmp для каждой модели из таблицы models, а затем внешний подзапрос выбирает соответствующий id бренда из таблицы brands, а результат подзапроса присваивается полю brand_id в таблице models.

Но возможен и другой, намного более понятный и лаконичный вариант запроса, с использованием JOIN:

UPDATE models
JOIN tmp ON models.name = tmp.model
JOIN brands ON tmp.brand = brands.name
SET models.brand_id = brands.id;

Этот запрос обновит значения поля brand_id в таблице models на соответствующие значения из таблицы brands на основании данных из таблицы tmp.