SQL 视图

本章将学习如何使用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语句外,还可以对视图执行INSERTUPDATEDELETE。然而,并不是所有的视图都是可更新的,也就是说,能够修改底层源表的数据。

视图是否可更新有一些限制。一般而言,如果一个视图包含下列任何一项,则该视图不可更新:

  • DISTINCTGROUP BYHAVING子句
  • 聚合函数,如AVG()COUNT()SUM()MIN()MAX()
  • UNIONUNION ALLCROSS JOINEXCEPTINTERSECT运算符
  • 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;


浙ICP备17015664号 浙公网安备 33011002012336号 联系我们 网站地图  
@2019 qikegu.com 版权所有,禁止转载