SQL CREATE VIEW 语句

Created: October-28, 2018

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

创建视图以简化表访问

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

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

语法

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

CREATE VIEW view_name AS select_statement ;

为了清楚地理解这一点，让我们看看以下 employees 和 departments 表。

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 外，还执行 INSERT、UPDATE 以及 DELETE 操作。但是，并非所有视图都是可更新的，即能够修改基础源表的数据。可更新性有一些限制。

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

DISTINCT ， GROUP BY 或 HAVING 子句。
聚合函数，例如 AVG() ， COUNT() ， SUM() ， MIN() ， MAX() 等等。
UNION ， UNION ALL ， CROSSJOIN ， EXCEPT 或 INTERSECT 运算符。
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;