Создание и использование представлений

Вводный урок

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

Требования к этому уроку

Для выполнения SQL-запросов в этом руководстве вам потребуется доступ к базе данных MariaDB и разрешения CREATE TABLE и CREATE VIEW для этой таблицы.

База данных сотрудников

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

Сначала создадим таблицу, которая будет содержать всех сотрудников и их контактную информацию:

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM;

Далее добавим нескольких сотрудников в таблицу:

INSERT INTO `Employees` (`First_Name`, `Last_Name`, `Position`, `Home_Address`, `Home_Phone`)
VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478'); 

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

CREATE TABLE `Hours` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `Clock_In` DATETIME NOT NULL,
  `Clock_Out` DATETIME NOT NULL
) ENGINE=MyISAM;

И, наконец, хотя это много информации, добавим полную неделю часов для каждого сотрудника во вторую созданную таблицу:

INSERT INTO `Hours`
VALUES ('1', '2005-08-08 07:00:42', '2005-08-08 17:01:36'),
  ('1', '2005-08-09 07:01:34', '2005-08-09 17:10:11'),
  ('1', '2005-08-10 06:59:56', '2005-08-10 17:09:29'),
  ('1', '2005-08-11 07:00:17', '2005-08-11 17:00:47'),
  ('1', '2005-08-12 07:02:29', '2005-08-12 16:59:12'),
  ('2', '2005-08-08 07:00:25', '2005-08-08 17:03:13'),
  ('2', '2005-08-09 07:00:57', '2005-08-09 17:05:09'),
  ('2', '2005-08-10 06:58:43', '2005-08-10 16:58:24'),
  ('2', '2005-08-11 07:01:58', '2005-08-11 17:00:45'),
  ('2', '2005-08-12 07:02:12', '2005-08-12 16:58:57'),
  ('3', '2005-08-08 07:00:12', '2005-08-08 17:01:32'),
  ('3', '2005-08-09 07:01:10', '2005-08-09 17:00:26'),
  ('3', '2005-08-10 06:59:53', '2005-08-10 17:02:53'),
  ('3', '2005-08-11 07:01:15', '2005-08-11 17:04:23'),
  ('3', '2005-08-12 07:00:51', '2005-08-12 16:57:52'),
  ('4', '2005-08-08 06:54:37', '2005-08-08 17:01:23'),
  ('4', '2005-08-09 06:58:23', '2005-08-09 17:00:54'),
  ('4', '2005-08-10 06:59:14', '2005-08-10 17:00:12'),
  ('4', '2005-08-11 07:00:49', '2005-08-11 17:00:34'),
  ('4', '2005-08-12 07:01:09', '2005-08-12 16:58:29'),
  ('5', '2005-08-08 07:00:04', '2005-08-08 17:01:43'),
  ('5', '2005-08-09 07:02:12', '2005-08-09 17:02:13'),
  ('5', '2005-08-10 06:59:39', '2005-08-10 17:03:37'),
  ('5', '2005-08-11 07:01:26', '2005-08-11 17:00:03'),
  ('5', '2005-08-12 07:02:15', '2005-08-12 16:59:02'),
  ('6', '2005-08-08 07:00:12', '2005-08-08 17:01:02'),
  ('6', '2005-08-09 07:03:44', '2005-08-09 17:00:00'),
  ('6', '2005-08-10 06:54:19', '2005-08-10 17:03:31'),
  ('6', '2005-08-11 07:00:05', '2005-08-11 17:02:57'),
  ('6', '2005-08-12 07:02:07', '2005-08-12 16:58:23');

Работа с базой данных сотрудников

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

Фильтрация по имени, дате и времени

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

Наш предыдущий запрос выглядел так:

SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`
FROM `Employees`
INNER JOIN `Hours` ON `Employees`.`ID` = `Hours`.`ID`
WHERE `Employees`.`First_Name` = 'Helmholtz'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59';

Результат:

+------------+-----------+---------------------+---------------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           |
+------------+-----------+---------------------+---------------------+
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |
+------------+-----------+---------------------+---------------------+

Уточнение запроса

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

SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`,
  (TIMESTAMPDIFF(MINUTE,`Hours`.`Clock_Out`,`Hours`.`Clock_In`) + 601) as Difference
FROM `Employees`
INNER JOIN `Hours` USING (`ID`)
WHERE DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59'
AND TIMESTAMPDIFF(MINUTE,`Hours`.`Clock_Out`,`Hours`.`Clock_In`) > -601;

Это дает нам следующий список людей, нарушивших политику посещаемости:

+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | Difference |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |          4 |
| Henry      | Foster    | 2005-08-11 07:01:58 | 2005-08-11 17:00:45 |          2 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |          4 |
| Bernard    | Marx      | 2005-08-09 07:01:10 | 2005-08-09 17:00:26 |          1 |
| Lenina     | Crowne    | 2005-08-12 07:01:09 | 2005-08-12 16:58:29 |          3 |
| Fanny      | Crowne    | 2005-08-11 07:01:26 | 2005-08-11 17:00:03 |          2 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |          4 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |          4 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |          4 |
+------------+-----------+---------------------+---------------------+------------+

Полезность представлений

В предыдущем примере мы видим несколько случаев опозданий и ранних уходов сотрудников. К сожалению, мы также видим, что этот запрос становится излишне сложным. Наличие всего этого SQL в нашем приложении не только создает более сложный код приложения, но также означает, что если мы когда-либо изменим структуру этой таблицы, нам придется изменить этот довольно запутанный запрос. Вот где представления начинают демонстрировать свою полезность.

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

Создание представления почти идентично созданию оператора SELECT, поэтому мы можем использовать наш предыдущий оператор SELECT для создания нового представления:

CREATE SQL SECURITY INVOKER VIEW Employee_Tardiness AS 
SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`,
(TIMESTAMPDIFF(MINUTE,`Hours`.`Clock_Out`,`Hours`.`Clock_In`) + 601) as Difference
FROM `Employees`
INNER JOIN `Hours` USING (`ID`)
WHERE DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59'
AND TIMESTAMPDIFF(MINUTE,`Hours`.`Clock_Out`,`Hours`.`Clock_In`) > -601;

Обратите внимание, что первая строка нашего запроса содержит оператор 'SQL SECURITY INVOKER' - это означает, что при доступе к представлению он выполняется с теми же привилегиями, что и у лица, которое получает доступ к представлению. Таким образом, если кто-то без доступа к нашей таблице сотрудников попытается получить доступ к этому представлению, он получит ошибку.

Помимо параметра безопасности, остальная часть запроса довольно понятна. Мы просто выполняем 'CREATE VIEW <имя-представления> AS' и затем добавляем любой допустимый оператор SELECT, и наше представление создается. Теперь, если мы выполним SELECT из представления, мы получим те же результаты, что и раньше, но с гораздо меньшим объемом SQL:

SELECT * FROM Employee_Tardiness;

+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | Difference |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |          5 |
| Henry      | Foster    | 2005-08-11 07:01:58 | 2005-08-11 17:00:45 |          3 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |          5 |
| Bernard    | Marx      | 2005-08-09 07:01:10 | 2005-08-09 17:00:26 |          2 |
| Lenina     | Crowne    | 2005-08-12 07:01:09 | 2005-08-12 16:58:29 |          4 |
| Fanny      | Crowne    | 2005-08-09 07:02:12 | 2005-08-09 17:02:13 |          1 |
| Fanny      | Crowne    | 2005-08-11 07:01:26 | 2005-08-11 17:00:03 |          3 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |          5 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |          5 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |          5 |
+------------+-----------+---------------------+---------------------+------------+

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

SELECT * FROM Employee_Tardiness WHERE Difference >=5;

+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | Difference |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |          5 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |          5 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |          5 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |          5 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |          5 |
+------------+-----------+---------------------+---------------------+------------+

Другие применения представлений

Помимо упрощения SQL-запросов нашего приложения, представления также предоставляют другие преимущества, некоторые из которых возможны только с помощью представлений.

Ограничение доступа к данным

Например, даже если наша база данных сотрудников содержит поля для Должности, Домашнего адреса и Домашнего телефона, наш запрос не позволяет отображать эти поля. Это означает, что в случае проблемы безопасности в приложении (например, атаки SQL-инъекции или даже злонамеренного программиста) нет риска раскрытия личной информации сотрудника.

Безопасность на уровне строк

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

Предупредительная оптимизация

Мы также можем определить наши представления таким образом, чтобы принудительно использовать индексы, чтобы другие, менее опытные разработчики не рисковали выполнением не оптимизированных запросов или JOIN, которые приводят к полному сканированию таблицы и длительным блокировкам. Сложные запросы, запросы, которые выбирают все столбцы (*) и плохо продуманные JOINы могут не только значительно замедлить работу базы данных, но и вызвать сбои при вставке, таймауты клиентов и ошибки в отчетах. Создавая представление, которое уже оптимизировано, и позволяя пользователям выполнять свои запросы на этом представлении, вы можете гарантировать, что они не станут причиной значительной производительности без необходимости.

Абстрагирование таблиц

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

К сожалению, перепроектирование базы данных, как правило, нарушает обратную совместимость со старыми приложениями, что может вызвать очевидные проблемы.

Используя представления, мы можем изменить формат базовых таблиц, сохраняя при этом тот же формат таблицы для устаревшего приложения. Таким образом, приложение, которое требует имени пользователя, имени хоста и времени доступа в строковом формате, может получить доступ к тем же данным, что и приложение, которое требует имени, фамилии, пользователя@хоста и времени доступа в формате Unix-времени.

Заключение

Представления – это функция SQL, которая может обеспечить большую гибкость в более крупных приложениях и даже упростить более мелкие приложения. Так же как хранимые процедуры могут помочь нам абстрагировать логику базы данных, представления могут упростить доступ к данным в базе данных и помочь упростить наши запросы, чтобы отладка приложения была проще и эффективнее.