SQL CREATE VIEW 语句

在本教程中,你将学习如何使用 SQL 创建、更新和删除视图。

创建视图以简化表访问

视图是一个虚拟表,其定义存储在数据库中。但是,与表不同,视图实际上不包含任何数据。相反,它提供了一种在数据库中存储常用复杂查询的方法。但是,你可以使用 SQL SELECT 语句中的视图来访问数据,就像使用普通表或基表一样。

通过允许用户通过视图访问数据,而不是让他们直接访问整个基表,视图也可以用作安全机制。

语法

使用该 CREATE VIEW 语句创建视图。

CREATE VIEW view_name AS select_statement ; 

为了清楚地理解这一点,让我们看看以下 employeesdepartments 表。

Table: employees

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

Table: departments

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

但是,只要你想要访问此记录,就需要再次键入整个查询。如果你经常进行这样的操作,它会变得非常不方便和烦人。

在这种情况下,你可以创建一个视图以使查询结果更易于访问,如下所示:

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 中,视图定义不能包含 ORDER BY 子句,除非在 SELECT 语句的选择列表中也有一个 TOP 子句。

替换现有视图

在 MySQL 中,如果要更新或替换现有视图,可以删除该视图并创建新视图,或者只使用 OR REPLACE 语句中的子句 CREATE VIEW ,如下所示:

CREATE OR REPLACE VIEW view_name AS select_statement ; 

注意:OR REPLACE 语句中使用该 CREATE VIEW 子句时,如果视图不存在,它将创建一个新视图,否则将替换现有视图。

以下 SQL 语句将通过向其添加新列 salary 来替换或更改现有视图 emp_dept_view 的定义

-- Syntax for MySQL Database 
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;

你将在结果输出中看到另一个列 *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            |
+--------+--------------+--------+-----------------+

注意: SQL Server 不支持 OR REPLACE 子句,因此要替换视图,你只需删除该视图并重新创建一个新视图。

通过视图更新数据

从理论上讲,你也可以在视图中除了执行 SELECT 外,还执行 INSERTUPDATE 以及 DELETE 操作。但是,并非所有视图都是可更新的,即能够修改基础源表的数据。可更新性有一些限制。

通常,如果视图包含以下任何内容,则视图不可更新:

  • DISTINCTGROUP BYHAVING 子句。
  • 聚合函数,例如 AVG()COUNT()SUM()MIN()MAX() 等等。
  • UNIONUNION ALLCROSSJOINEXCEPTINTERSECT 运算符。
  • WHERE 子句中的子查询,它引用在 FROM 子句中的表。

如果视图满足这些条件,则可以使用该视图修改源表。

以下语句将更新 emp_id 等于 1 的员工的 *salary*。

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;