本章将学习如何使用SQL创建、更新和删除视图。
创建视图以简化表的访问
视图是一个虚拟表,它的定义存储在数据库中。但是,与表不同,视图实际上不存储任何实际数据。视图提供了一种在数据库中存储常用复杂查询的途经。
可以使用SELECT
语句访问视图,就像访问普通表一样。
视图还可以用作一种安全机制,允许用户通过视图访问数据,而不是让他们直接访问表。
语法
视图是使用CREATE VIEW
语句创建的。
CREATE VIEW view_name AS select_statement;
让我们看看下面的employees和departments表。
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 |
+--------+--------------+------------+---------+
departments
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
假设您需要检索员工的id、姓名、部门名称,那么您需要执行LEFT JOIN
操作,如下所示:
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中,如果想替换已有视图,当然可以删除该视图并创建新的视图,但也可以在CREATE VIEW
语句中使用OR REPLACE
子句来达到同样效果,如下所示:
CREATE OR REPLACE VIEW view_name AS select_statement;
当在CREATE VIEW
语句中使用OR REPLACE
子句时,如果视图不存在,它将创建新视图,否则将替换现有视图。
下面的SQL语句,将通过向视图emp_dept_view
添加新的列salary
,来更改视图定义。
-- 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
、CROSS JOIN
、EXCEPT
或INTERSECT
运算符 WHERE
子查询中引用了FROM
子句中的表
下面的语句将更新emp_id
等于1
的员工的工资。
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;