SQL 创建视图(CREATE VIEW 语句)

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

创建视图以简化表访问

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

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

语法

使用CREATE VIEW语句创建视图。

CREATE VIEW view_name AS select_statement;

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

+--------+--------------+--------+---------+
| 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            |
+---------+------------------+
表: employees
表: departments

假设您要检索雇员的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 Server中,视图定义不能包含ORDER BY子句,除非SELECT语句的选择列表中也有TOP子句。

替换现有视图

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

CREATE OR REPLACE VIEW view_name AS select_statement;

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

下面的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            |
+--------+--------------+--------+-----------------+

注意: SQL Server不支持OR REPLACE子句,因此,要替换视图,您可以直接删除该视图并从stretch中创建一个新视图。

通过视图更新数据

从理论上讲,除了SELECT语句外,您还可以在视图上执行INSERTUPDATEDELETE。 但是,并非所有视图都是可更新的,即能够修改基础源表的数据。 对可更新性有一些限制。

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

  • 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;