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 |+---------+------------------+ | |
表: employees | 表: departments |
假设您要检索雇员的ID和姓名以及他们的部门名称,那么您需要执行左加入操作,如下所示:
示例
SELECT t1.emp_id, t1.emp_name, t2.dept_nameFROM employees AS t1 LEFT JOIN departments AS t2ON 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 ASSELECT t1.emp_id, t1.emp_name, t2.dept_nameFROM employees AS t1 LEFT JOIN departments AS t2ON 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 ASSELECT t1.emp_id, t1.emp_name, t1.salary, t2.dept_nameFROM employees AS t1 LEFT JOIN departments AS t2ON 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语句外,您还可以在视图上执行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;