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

但是，无论何时要访问此记录，都需要再次键入整个查询。如果您经常执行此类操作，则将变得非常不便和烦人。

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

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;

正如您看到的，您可以在视图上节省多少时间和精力。

替换现有视图

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

CREATE OR REPLACE VIEW view_name AS select_statement;

下面的SQL语句将替换或更改现有视图emp_dept_view视图的定义，方法是向其添加一个新列salary。

-- MySQL数据库的语法
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            |
+--------+--------------+--------+-----------------+

通过视图更新数据

从理论上讲，除了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;