МДК.11.01 - 22 - Представления

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

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;

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

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

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

Преимущества использования представлений в MySQL:

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

2. Обеспечение безопасности: представления могут быть использованы для ограничения доступа к определенным столбцам или строкам таблицы. Например, можно создать представление, которое будет содержать только определенные столбцы таблицы и предоставить доступ к этому представлению, скрывая остальные данные.

3. Улучшение производительности: представления могут быть использованы для оптимизации запросов. Например, можно создать представление, которое будет содержать сложный запрос с использованием JOIN и фильтрацией данных. Затем можно использовать это представление вместо повторного выполнения сложного запроса каждый раз, когда требуется получить данные.

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

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

Для создания представления в MySQL 8 используется конструкция CREATE VIEW. Рассмотрим несколько примеров.

Пример 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 вместе с именем владельца счета для каждой транзакции.

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