Примечание

ЭТО АРХИВНАЯ ВЕРСИЯ КУРСА!

Материалы предназначаются для пересдающих дисциплину "ОП.08 - Основы проектирования баз данных" в соответствии с учебными планами СПО годов набора ДО 2024-го. Для того чтобы ознакомиться с актуальным курсом "ИТ.03 - Основы проектирования баз данных" включающим интерактивные примеры, перейдите по ссылке.

Материалы были перенесены со старого сайта с минимальной доработкой, поэтому не все возможности курса могут работать как ожидается, где-то может слететь форматирование.

Домашние задания в рамках курса проверяться не будут!

ОП.08 - 17 - `UPDATE SELECT` и `INSERT INTO SELECT` для запросов с `JOIN`-ами

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

car_dealership.sql

car_dealership.sql

MySQL 8 предоставляет возможность использовать операторы UPDATE SELECT и INSERT INTO SELECT для запросов с JOIN-ами. Это позволяет обновлять или вставлять данные в таблицу на основе данных из другой таблицы, используя соединение JOIN.

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

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

CREATE TABLE IF NOT EXISTS brands(
  id            INTEGER  NOT NULL  PRIMARY KEY  AUTO_INCREMENT
  ,name         VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS models(
  id            INTEGER  NOT NULL  PRIMARY KEY  AUTO_INCREMENT
  ,brand_id     INTEGER
  ,name         VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS cars(
  id            INTEGER  NOT NULL  PRIMARY KEY  AUTO_INCREMENT
  ,model_id     INTEGER
  ,year         INTEGER
  ,power        INTEGER
  ,drivetrain   VARCHAR(255)
  ,mileage      INTEGER
  ,cost         DECIMAL(10,2)
);

CREATE TABLE IF NOT EXISTS sales(
  id            INTEGER  NOT NULL  PRIMARY KEY  AUTO_INCREMENT
  ,cars_id   INTEGER
  ,sold_date    DATE
  ,client_name  VARCHAR(255)
);

Затем заполним таблицы данными из таблицы tmp с помощью оператора INSERT INTO SELECT и соединения JOIN:

-- Заполнение таблицы brands
INSERT INTO brands(name)
SELECT DISTINCT brand FROM tmp;

-- Заполнение таблицы models
INSERT INTO models(brand_id, name)
SELECT DISTINCT brands.id, tmp.model
FROM tmp
JOIN brands ON tmp.brand = brands.name;

-- Заполнение таблицы cars
INSERT INTO cars(id, model_id, year, power, drivetrain, mileage, cost)
SELECT tmp.id, models.id, tmp.year, tmp.power, tmp.drivetrain, tmp.mileage, tmp.cost
FROM tmp
JOIN models ON tmp.model = models.name;

-- Заполнение таблицы sales
INSERT INTO sales(cars_id, sold_date, client_name)
SELECT tmp.id, tmp.sold_date, tmp.client_name
FROM tmp
WHERE (sold_date IS NOT NULL) AND (client_name IS NOT NULL);

Если же например данные при первоначальном разборе не были добавлены в таблицу, тогда используем UPDATE, например, мы можем обновить данные в таблице cars на основе данных из таблицы tmp следующим образом:

UPDATE cars
JOIN tmp ON cars.model_id = (
  SELECT id
  FROM models
  WHERE
    name = tmp.model
    AND brand_id = (
      SELECT id
      FROM brands
      WHERE name = tmp.brand
    )
  )
SET
  cars.year = tmp.year,
  cars.power = tmp.power,
  cars.drivetrain = tmp.drivetrain,
  cars.mileage = tmp.mileage,
  cars.cost = tmp.cost
WHERE cars.id > 0;

Таким образом, операторы UPDATE SELECT и INSERT INTO SELECT позволяют удобно работать с данными в таблицах, используя соединение JOIN и обновляя или вставляя данные на основе данных из других таблиц.

ОП.08 - 17 - UPDATE SELECT и INSERT INTO SELECT для запросов с JOIN-ами

ОП.08 - 17 - `UPDATE SELECT` и `INSERT INTO SELECT` для запросов с `JOIN`-ами