Представления в SQL

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

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

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

Синтаксис

Представления создаются при помощи выражения CREATE VIEW.

CREATE VIEW название_представления AS выражение_SELECT;

Чтобы понять принцип действия этого выражения, давайте обратимся к таблицам employees и departments.

+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+

Предположим, что вы хотите получить идентификатор и имя сотрудников вместе с названием их отдела. Для этого можно выполнить объединение LEFT JOIN, как показано ниже:

SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

После выполнения этого запроса вы получите следующий результат:

+--------+--------------+-----------------+
| emp_id | emp_name     | dept_name       |
+--------+--------------+-----------------+
|      1 | Ethan Hunt   | Human Resources |
|      2 | Tony Montana | Administration  |
|      3 | Sarah Connor | Sales           |
|      4 | Rick Deckard | Finance         |
|      5 | Martin Blank | NULL            |
+--------+--------------+-----------------+

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

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

CREATE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

Теперь можно получить доступ к тем же записям через представление emp_dept_view:

SELECT * FROM emp_dept_view;

Обратите внимание, сколько времени можно сэкономить с помощью представлений.

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

Примечание. В MySQL можно указать ORDER BY в определении представления. А в SQL Sever так не получится, если в select-списке нет предложения TOP.

Замена существующего представления

В MySQL, если вы хотите обновить или заменить существующее представление, есть два варианта:

• удалить это представление и создать новое,
• использовать выражение OR REPLACE в операторе CREATE VIEW, как показано ниже:

CREATE OR REPLACE VIEW название_представления AS выражение_SELECT;

Примечание. Когда в операторе CREATE VIEW используется оператор OR REPLACE, он создает новое представление — при условии, что оно не существует. В противном случае он просто заменяет существующее представление.

Следующий оператор SQL заменит или изменит определение существующего представления emp_dept_view, добавив в него новый столбец salary.

CREATE OR REPLACE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t1.salary, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

После обновления представления, если вы выполните следующий оператор:

SELECT * FROM emp_dept_view ORDER BY emp_id;

вы увидите еще один столбец зарплаты в выводе, как показано ниже:

+--------+--------------+--------+-----------------+
| emp_id | emp_name     | salary | dept_name       |
+--------+--------------+--------+-----------------+
|      1 | Ethan Hunt   |   5000 | Human Resources |
|      2 | Tony Montana |   6500 | Administration  |
|      3 | Sarah Connor |   8000 | Sales           |
|      4 | Rick Deckard |   7200 | Finance         |
|      5 | Martin Blank |   5600 | NULL            |
+--------+--------------+--------+-----------------+

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

Теоретически помимо SELECT для представлений можно использовать INSERT, UPDATE и DELETE. Однако не все представления являются обновляемыми, т.е. способными изменять данные базовой исходной таблицы. Существуют некоторые ограничения на возможность обновления.

Как правило, представление не является обновляемым, если оно содержит что-либо из перечисленного ниже:

• Выражения DISTINCT, GROUP BY или HAVING.

• Агрегатные функции, такие как AVG(), COUNT(), SUM(), MIN(), MAX() и так далее.

• Операторы UNION, UNION ALL, CROSSJOIN, EXCEPT или INTERSECT.

• Подзапрос в выражении WHERE, который ссылается на таблицу в выражении FROM.

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

Следующий оператор обновит зарплату сотрудника с emp_id равным 1.

UPDATE emp_dept_view SET salary = '6000' 
WHERE emp_id = 1;

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

Удаление представления

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

DROP VIEW имя_представления;

Следующая команда удалит представление emp_dept_view из базы данных:

DROP VIEW emp_dept_view;