Язык структурированных запросов (Structured Query Language далее SQL) использует множество различных структур данных, причем таблицы являются одной из наиболее часто используемых. Однако таблицы имеют определенные ограничения. Например, нельзя ограничить доступ пользователей только к части таблицы. Пользователю должен быть предоставлен доступ ко всей таблице, а не только к нескольким столбцам в ней.

В качестве другого примера предположим, что мы хотим объединить данные из нескольких других таблиц в новую структуру, но при этом не хотите удалять исходные таблицы. Мы можем просто создать еще одну таблицу, но тогда у нас будут избыточные данные, хранящиеся в нескольких местах. Это может вызвать массу неудобств: если какие-то данные изменятся, нам придется обновлять их в нескольких местах. В таких случаях могут пригодиться представления (VIEW).

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

Подключаемся к MySQL:

mysql -u root -p

И создадим новую базу с именем views_db:

CREATE DATABASE views_db;

и выбираем ее:

USE views_db;

После того как выбрали базу, создадим в ней несколько таблиц.

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

• emp_id – идентификационный номер каждого работника по уходу за собаками, которого мы наняли, выраженный типом данных – int. Этот столбец будет служить первичным ключом таблицы, что означает – каждое значение будет функционировать как уникальный идентификатор соответствующей строки. Поскольку каждое значение в первичном ключе должно быть уникальным, к этому столбцу также будет применено ограничение – UNIQUE .
• emp_name – имя каждого сотрудника, установленное с использованием типа данных varchar, состоящего не более чем из 20 символов.

Создадим таблицу employees с указанными двумя столбцами:

CREATE TABLE employees (
emp_id INT UNIQUE,
emp_name VARCHAR(20),
PRIMARY KEY (emp_id)
);

Другая таблица, представляющая каждую собаку, будет содержать следующие шесть столбцов:

• dog_id – идентификационный номер для каждой собаки, проживающей в отеле, выраженный с помощью типа данных int. Как и столбец emp_id в таблице employees, этот столбец будет служить первичным ключом для таблицы dogs.
• dog_name – имя каждой собаки, выраженное с использованием типа данных – varchar, состоящего не более чем из 20 символов.
• walker – в этом столбце хранится идентификационный номер сотрудника, назначенного для ухода за каждой соответствующей собакой.
• walk_distance – расстояние, которое должна пройти каждая собака, когда ее выводят на прогулку, выраженное с помощью десятичного типа данных (decimal). В данном случае десятичное объявление определяет точность, равную трем, со шкалой равной двум, что означает, что любые значения в этом столбце могут иметь не более трех цифр, причем две из этих цифр находятся справа от десятичной точки.
• meals_perday – гостиница для собак обеспечивает каждую собаку определенным количеством еды каждый день. В этом столбце указывается количество еды в день, которое должна получать каждая собака по рекомендации ее владельца, для этого используется int – целое число.
• cups_permeal – в этой колонке указано, сколько мисок крупного корма должна получать каждая собака за один прием пищи. Как и walk_distance, этот столбец выражается десятичной дробью. Однако шкала здесь равна трем с точностью до двух, то есть значения в этом столбце могут содержать до трех цифр, две из которых находятся справа от десятичной точки.

Чтобы убедиться, что в столбце walker хранятся значения, представляющие действительные идентификационные номера сотрудников, было решено применить ограничение внешнего ключа к столбцу walker, который ссылается на столбец emp_ID таблицы employees. Ограничение внешнего ключа – это способ выразить связь между двумя таблицами, требуя, чтобы значения в столбце, к которому применяется внешний ключ, уже существовали в столбце, на который ссылается. В следующем примере ограничение FOREIGN KEY требует, чтобы любое значение, добавленное в столбец walker таблицы dogs, уже существовало в столбце emp_ID таблицы employees.

Создаем таблицу dogs:

CREATE TABLE dogs (
dog_id INT UNIQUE,
dog_name VARCHAR(20),
walker INT,
walk_distance DECIMAL(3,2),
meals_perday INT,
cups_permeal DECIMAL(3,2),
PRIMARY KEY (dog_id),
FOREIGN KEY (walker)
REFERENCES employees(emp_ID)
);

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

INSERT INTO employees
VALUES
(1, 'Peter'),
(2, 'Paul'),
(3, 'Mary');

Затем, заполним таблицу dogs, вставив в нее следующие семь строк:

INSERT INTO dogs
VALUES
(1, 'Dottie', 1, 5, 3, 1),
(2, 'Bronx', 3, 6.5, 3, 1.25),
(3, 'Harlem', 3, 1.25, 2, 0.25),
(4, 'Link', 2, 2.75, 2, 0.75),
(5, 'Otto', 1, 4.5, 3, 2),
(6, 'Juno', 1, 4.5, 3, 2),
(7, 'Zephyr', 3, 3, 2, 1.5);

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

Как уже упоминалось выше, представления являются виртуальными таблицами. Это означает, что хотя представление функционально похоже на таблицу, это другой тип структуры, поскольку представление не содержит никаких собственных данных. Вместо этого, данные извлекаются из одной или нескольких базовых таблиц, в которых, собственно, и хранятся данные. Единственная информация о представлении, которую хранит СУБД – это структура представления. Представления иногда называют сохраненными запросами, потому что по сути это то, чем они являются: запросы, сохраненные под определенным именем для удобства доступа.

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

Давайте используем свои скил SQL, для создания запроса, который позволит получить всю необходимую информацию для расписания. Обратим внимание, что запрос включает синтаксис JOIN, что даст возможность получить данные как из таблицы employees, так и из таблицы dogs:

SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal
FROM employees JOIN dogs ON emp_ID = walker;
 
+----------+----------+---------------+--------------+--------------+
| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+

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

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

Для создания представления большинство СУБД используют следующий синтаксис:

CREATE VIEW view_name
AS
SELECT statement;

После оператора CREATE VIEW определяется имя представления, которое будем использовать для ссылки на него позже. После имени вводится ключевое слово AS, а далее запрос SELECT, результаты которого хотим сохранить. Запрос, который используете для создания представления, может быть любым допустимым оператором SELECT. Включенный в него оператор может запрашивать одну или несколько базовых таблиц, если используется правильный синтаксис.

Попробуем создать представление, используя запрос из предыдущего примера. Операция CREATE VIEW именует представление – walking_schedule:

CREATE VIEW walking_schedule
AS
SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal 
FROM employees JOIN dogs
ON emp_ID = walker;

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

SELECT * FROM walking_schedule;
 
+----------+----------+---------------+--------------+--------------+
| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+

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

SELECT walker FROM walking_schedule;
 
ERROR 1054 (42S22): UNKNOWN COLUMN 'walker' IN 'field list'

Вывод возвращают сообщение об ошибке, поскольку столбец walker является частью таблицы dogs, но не был включен в созданное ранее представление.

Также, мы можем выполнять запросы, включающие агрегатные функции, управляющие данными в представлении. В следующем примере, агрегатная функция MAX используется вместе с GROUP BY, для определения наибольшего расстояния, которое каждый сотрудник должен будет пройти в заданный день:

SELECT emp_name, MAX(walk_distance) AS longest_walks
    -> FROM walking_schedule GROUP BY emp_name;
    -
+----------+---------------+
| emp_name | longest_walks |
+----------+---------------+
| Peter    |          5.00 |
| Paul     |          2.75 |
| Mary     |          6.50 |
+----------+---------------+

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

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

CREATE USER 'office_mgr'@'localhost' IDENTIFIED BY 'password';

Затем, с помощью оператора GRANT, мы предоставим новому пользователю доступ на чтение только к представлению walking_schedule:

GRANT SELECT ON views_db.walking_schedule to 'office_mgr'@'localhost';

После этого, любой, у кого есть доступ к учетной записи office_mgr, сможет выполнять SELECT запросы только к представлению walking_schedule.

Если мы добавим или изменим какие-либо данные в одной из таблиц, из которых получено представление, соответствующие данные будут автоматически добавлены или обновлены в представлении. Выполните следующий оператор INSERT INTO, чтобы добавить еще одну строку в таблицу dogs:

INSERT INTO dogs VALUES (8, 'Charlie', 2, 3.5, 3, 1);

И снова извлечем данные из представления walking_schedule:

SELECT * FROM walking_schedule;
+----------+----------+---------------+--------------+--------------+
| emp_name | dog_name | walk_distance | meals_perday | cups_permeal |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         1.00 |
| Peter    | Otto     |          4.50 |            3 |         2.00 |
| Peter    | Juno     |          4.50 |            3 |         2.00 |
| Paul     | Link     |          2.75 |            2 |         0.75 |
| Paul     | Charlie  |          3.50 |            3 |         1.00 |
| Mary     | Bronx    |          6.50 |            3 |         1.25 |
| Mary     | Harlem   |          1.25 |            2 |         0.25 |
| Mary     | Zephyr   |          3.00 |            2 |         1.50 |
+----------+----------+---------------+--------------+--------------+

И видим, что в выводе запроса есть еще одна строка, отражающая данные, которую добавили в таблицу dogs. Это представление по-прежнему извлекает те же данные, из тех же базовых таблиц, поскольку само представление не было изменено.

Многие СУБД позволяют обновлять структуру представления после его создания, с помощью синтаксиса CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW view_name
AS
NEW SELECT statement

В примере выше, если представление с именем view_name уже существует, система баз данных обновит его, чтобы оно представляло данные, возвращаемые new SELECT statement. Если представление с таким именем еще не существует, СУБД создаст новое.

Предположим, что мы хотим изменить представление walking_schedule, чтобы вместо списка количества мисок корма за один прием пищи, перечислялось общее количество корма, съеденного песелем за день. Давайте изменим представление:

CREATE OR REPLACE VIEW walking_schedule
AS
SELECT emp_name, dog_name, walk_distance, meals_perday, (cups_permeal * meals_perday) AS total_kibble 
FROM employees JOIN dogs ON emp_ID = walker;

Теперь, при запросе этого представления, мы будем получать другие данные:

SELECT * FROM walking_schedule;
 
+----------+----------+---------------+--------------+--------------+
| emp_name | dog_name | walk_distance | meals_perday | total_kibble |
+----------+----------+---------------+--------------+--------------+
| Peter    | Dottie   |          5.00 |            3 |         3.00 |
| Peter    | Otto     |          4.50 |            3 |         6.00 |
| Peter    | Juno     |          4.50 |            3 |         6.00 |
| Paul     | Link     |          2.75 |            2 |         1.50 |
| Paul     | Charlie  |          3.50 |            3 |         3.00 |
| Mary     | Bronx    |          6.50 |            3 |         3.75 |
| Mary     | Harlem   |          1.25 |            2 |         0.50 |
| Mary     | Zephyr   |          3.00 |            2 |         3.00 |
+----------+----------+---------------+--------------+--------------+

Как и большинство других структур, которые можно создать в SQL, мы можем удалить представления, используя оператор DROP:

DROP VIEW view_name;

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

DROP VIEW walking_schedule;

Это удалит представление walking_schedule из базы данных, но никакие данные используемые этим представлением удалены не будут.

Просмотр сохраненных представлений:

SHOW FULL TABLES  WHERE TABLE_TYPE LIKE '%VIEW%';
+--------------------+------------+
| Tables_in_views_db | Table_type |
+--------------------+------------+
| walking_schedule   | VIEW       |
+--------------------+------------+
 
# либо так
SHOW FULL TABLES IN youdatabasename WHERE TABLE_TYPE LIKE '%VIEW%';