ИТ.03 - 22 - Операции JOIN и представления (VIEW) в MySQL.

Введение

Из прошлых лекций мы изучили как работает комманда JOIN в SQLite. Посмотрим их различия и примеры. Так-же узнаем как работают предстовления (VIEW) в MySQL.

В этой лекции:

• отличия комманды JOIN в MySQL и SQLite;
• примеры использования JOIN в MySQL;
• что такое предстовления (VIEW) в MySQL;
• как пользоваться представлениями (VIEW) в MySQL;

Отличия комманды JOIN в MySQL и SQLite

Примеры комманд JOIN в SQLite можете найти в 12-ой лекции.

Пример RIGHT JOIN в MySQL.

SELECT таблица1.столбец1, таблица2.столбец2
FROM таблица1
RIGHT JOIN таблица2 ON таблица1.общий_столбец = таблица2.общий_столбец;

Пример FULL OUTER JOIN В MySQL.

SELECT столбцы
FROM таблица1
LEFT JOIN таблица2 ON таблица1.общий_столбец = таблица2.общий_столбец
UNION
SELECT столбцы
FROM таблица1
RIGHT JOIN таблица2 ON таблица1.общий_столбец = таблица2.общий_столбец;

Что такое представления

Представление (VIEW) — это виртуальная таблица, которая не хранит данные физически, а динамически формирует их на основе запроса SELECT к реальным таблицам базы данных. По сути, это сохранённый SQL‑запрос, который можно использовать как обычную таблицу.

Зачем нужны представления

Основные сценарии использования:

• Упрощение сложных запросов. Вместо того чтобы каждый раз писать длинный запрос с JOIN, можно создать представление.

• Безопасность. Можно ограничить доступ пользователей к определённым столбцам или строкам таблицы.

• Согласованность данных. Если структура базы данных изменится, достаточно обновить представление — приложения продолжат работать без изменений.

• Логическое разделение данных. Представление может показывать только часть данных таблицы, скрывая остальное.

• Повторное использование кода. Один раз созданное представление можно использовать многократно.

Виды представлений

Основные особенности и типы представлений в MySQL:

• Виртуальные представления (по умолчанию): При каждом обращении к VIEW MySQL выполняет базовый запрос.

• Обновляемые представления: Представления, через которые можно выполнять UPDATE, INSERT, DELETE (если соблюдены ограничения, например, представление основано на одной таблице без агрегации).

• Необновляемые представления: Представления, содержащие GROUP BY, HAVING, UNION, DISTINCT, агрегатные функции (SUM, COUNT) или объединения (JOIN), которые нельзя обновлять напрямую.

• Представления с проверкой: Гарантируют, что при обновлении данных через VIEW новые данные будут соответствовать условиям WHERE в определении представления.

База для примеров

SET foreign_key_checks = 0;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS transactions;

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) DEFAULT NULL,
  `user_id` INT NOT NULL,
  `balance` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE `transactions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `date_time` DATETIME NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `transaction_type` ENUM('deposit', 'withdrawal', 'transfer') NOT NULL,
  `user_id` INT NOT NULL,
  `account_id` INT NOT NULL,
  `transfer_to_account_id` INT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  KEY `account_id_idx` (`account_id`),
  CONSTRAINT `transaction_user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `account_id`
    FOREIGN KEY (`account_id`)
    REFERENCES `accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES
(1,'Иванов Иван Иванович', 31),
(2,'Петров Петр Петрович', 27),
(3,'Сидоров Сидор Сидорович', 35);
UNLOCK TABLES;

LOCK TABLES `accounts` WRITE;
INSERT INTO `accounts` VALUES (1,'Основной',1,10500.00),(2,'Резервный',1,3412.57),(3,'Основной',2,20750.00),(4,'Основной',3,25000.00),(5,'Накопительный',2,5401.75);
UNLOCK TABLES;

LOCK TABLES `transactions` WRITE;
INSERT INTO `transactions` (`date_time`, `amount`, `transaction_type`, `user_id`, `account_id`, `transfer_to_account_id`)
VALUES
('2022-01-01 12:00:00', 100.00, 'deposit', 1, 1, NULL),
('2022-01-02 15:30:00', 50.00, 'withdrawal', 1, 1, NULL),
('2022-01-03 09:45:00', 200.00, 'deposit', 2, 3, NULL),
('2022-01-04 14:20:00', 75.00, 'withdrawal', 2, 3, NULL),
('2022-01-05 11:10:00', 150.00, 'transfer', 3, 4, 5);
UNLOCK TABLES;

SET foreign_key_checks = 1;

Базовый синтаксис создания представления

CREATE VIEW имя_представления AS
SELECT столбцы
FROM таблицы
[WHERE условия]
[GROUP BY столбцы]
[HAVING условия]
[ORDER BY столбцы];

Пример 1: Создание простого представления

Предположим, у нас есть таблица users с полями id, name и age. Мы хотим создать представление, которое будет отображать только имена пользователей старше 30 лет.

CREATE VIEW older_users AS
SELECT name, age
FROM users
WHERE age > 30;

Теперь мы можем использовать представление older_users вместо написания запроса каждый раз:

SELECT * FROM older_users;

Пример 2: Обновление данных через представление

Представления в MySQL 8 могут быть не только для чтения, но и для обновления данных. Рассмотрим пример с таблицей accounts, где у каждого счета есть поле balance. Мы хотим создать представление, которое будет отображать только счета на которых больше хранится больше 10000.

CREATE VIEW high_balance AS
SELECT *
FROM accounts
WHERE balance > 10000;

Теперь мы можем обновлять данные через представление. Например, допустим в силу вступила новая бонусная программа в рамках которой на все счета на которых больше 10000 начисляется бонус в размере 5% от соответствующей суммы, однако со счетами на которых хранится меньше указанной суммы, бонусная программа не распространяется. Тогда можно это решить через представление:

UPDATE high_balance
SET balance = balance + (balance * 0.05)
WHERE id > 0;

Таким образом, запрос UPDATE сработает только для того набора данных, который входит в данное представление.

Пример 3: Использование JOIN в представлениях

Представления могут быть основаны на нескольких таблицах и использовать операторы JOIN. Рассмотрим пример создания представления, которое будет включать данные о транзакции и имя владельца счета для всех транзакций типа deposit. Можно использовать следующий запрос:

CREATE VIEW deposit_transactions AS
SELECT
   transactions.id,
   transactions.date_time,
   transactions.amount,
   transactions.transaction_type,
   users.name AS account_owner
FROM transactions
JOIN accounts ON transactions.account_id = accounts.id
JOIN users ON accounts.user_id = users.id
WHERE transactions.transaction_type = 'deposit';

После выполнения этого запроса будет создано представление с именем deposit_transactions, которое будет содержать следующие данные:

• id - идентификатор транзакции
• date_time - дата и время транзакции
• amount - сумма транзакции
• transaction_type - тип транзакции (в данном случае будет равен deposit)
• account_owner - имя владельца счета

Теперь можно использовать это представление для получения данных о всех транзакциях типа deposit вместе с именем владельца счета:

SELECT * FROM deposit_transactions;

Этот запрос вернет список всех транзакций типа deposit вместе с именем владельца счета для каждой транзакции.

Пример 4: Удаление представления

Чтобы удалить представление, можно использовать следующий запрос:

DROP VIEW deposit_transactions;

После выполнения данного запроса представление deposit_transactions будет удалено.

В заключение, представления в MySQL 8 — это мощный инструмент для упрощения работы с данными и повышения производительности запросов. Они позволяют создавать логические структуры данных и использовать их как обычные таблицы.

Задание 1.

CREATE VIEW withdrawal_transactions AS
SELECT
   transactions.id,
   transactions.date_time,
   transactions.amount,
   users.name AS account_owner
FROM transactions
JOIN accounts ON transactions.account_id = accounts.id
JOIN users ON accounts.user_id = users.id
WHERE transactions.transaction_type = 'withdrawal';