SQL создание представления (запрос CREATE VIEW)

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

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

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

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

Грамматика

Использование оператора CREATE VIEW для создания представления.

CREATE VIEW view_name AS select_statement;

Чтобы清楚地 понять это, давайте рассмотрим следующее:employeesиdepartmentsТаблица.

+--------+--------------+--------+---------+
|　emp_id　|　emp_name　　　　　|　salary　|　dept_id　|
+--------+--------------+--------+---------+
|　　　　　　1　|　Ethan　Hunt　　　|　　　5000　|　　　　　　　4　|
|　　　　　　2　|　Tony　Montana　|　　　6500　|　　　　　　　1　|
|　　　　　　3　|　Sarah　Connor　|　　　8000　|　　　　　　　5　|
|　　　　　　4　|　Rick　Deckard　|　　　7200　|　　　　　　　3　|
|　　　　　　5　|　Martin　Blank　|　　　5600　|　　　　NULL　|
+--------+--------------+--------+---------+

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

Предположим, что вы хотите найти ID сотрудника и его имя, а также имя его отдела, для этого вам нужно выполнить:Левое объединениеОперации, как показано ниже:

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                                         |
+--------+--------------+-----------------+

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

В этом случае вы можете создать представление, чтобы сделать результаты запроса более доступными, как показано ниже:

Создать представление emp_dept_view как
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;

Как вы видите, сколько времени и усилий можно сэкономить на представлении.

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

В MySQL, если необходимо обновить или заменить существующее представление, можно удалить это представление и создать новое, или использовать предложение CREATE VIEW с подпредложением OR REPLACE, как показано ниже:

Создать или заменить представление view_name как select_statement;

Ниже приведено SQL-выражение, которое заменит или изменит существующее представлениеemp_dept_viewОпределение представления, метод заключается в добавлении нового столбца salary.

-- Грамматика MySQL базы данных
Создать или заменить представление emp_dept_view как
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;

Вы увидите другую колонку в выводе результатовsalaryКак показано ниже:

+--------+--------------+--------+-----------------+
|　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, Вы также можете выполнятьINSERT,UPDATEиDELETEНо не все представления обновляемы, то есть они могут изменять данные исходной таблицы. Есть некоторые ограничения по обновляемости.

Обычно, если в представлении содержится любое из следующих элементов, представление не может быть обновлено:

• DISTINCT, предложение GROUP BY или HAVING.

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

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

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

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

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

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

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

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

DROP VIEW view_name;

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

DROP VIEW emp_dept_view;