视图

以例子来说明：

SELECT salaries.emp_no, dept_emp.dept_no, salary, dept_name FROM salaries, dept_emp, departments WHERE salaries.emp_no = dept_emp.emp_no AND dept_emp.dept_no = departments.dept_no AND salary = 158220;

从三张表中获取薪水为158220的员工的id，部门id，薪水和部门名称。

如果使用视图，这段语句就会变成：

SELECT salaries.emp_no, dept_emp.dept_no, salary, dept_name FROM salary_for_department WHERE salary = 158220;

视图的作用相当于是对整个查询语句中复杂部分做了封装，只需要修改过滤部分和查询的关键字。

为什么使用视图

  • 重用SQL语句
  • 简化复杂的SQL操作，在编写完查询后可以方便的重用它。
  • 使用表的组成部分而不是整个表
  • 保护数据。可以授予用户表的特定部分的访问权限而不是整个表的访问权限。
  • 更改数据格式和表示。视图可以返回与底层表的表示和格式不同的数据。

创建完视图后，它的用法和表数据几乎相同，可以对视图执行SELECT操作，排序和过滤数据，也可以进行联结到其他视图或表，甚至能添加和更新数据。

视图的规则和限制

  • 与表一样，视图必须唯一命名
  • 对于可以创建的视图没有数量限制
  • 为了创建视图，必须具有足够的访问权限
  • 视图可以嵌套，即可以利用其它视图中检索数据的查询来构造一个视图，类似于子查询
  • ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也包含ORDER BY,那么该视图中的ORDER BY将会被覆盖
  • 视图不能索引，也不能有关联的触发器或默认值
  • 视图可以和表一起使用

使用视图

视图的用法：

  • 视图用CREATE VIEW语句来创建
  • 使用SHOW CREATE VIEW viewname来查看创建视图的语句
  • 使用DROP删除视图，其语法为DROP VIEW viewname
  • 更新视图时，可以先使用DROP在使用CREATE,也可以用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第二条语句会创建一个视图。如果要更新的视图存在，第二条语句会替换该视图。

利用视图简化复杂的联结

以例子说明：

CREATE VIEW salary_for_department AS SELECT salaries.emp_no, dept_emp.dept_no, salary, dept_name FROM salaries, dept_emp, departments WHERE salaries.emp_no = dept_emp.emp_no AND dept_emp.dept_no = departments.dept_no;

该语句创建了名为salary_for_department的视图，它将返回所有员工的id，部门id，薪水和部门名称。
如果检索薪水为158220的员工：

SELECT emp_no, dept_no, salary, dept_name FROM salary_for_department WHERE salary = 158220;

会发现，在使用视图时候不需要指定emp_no,dept_no属于哪张表。

+--------+---------+--------+-----------+
| emp_no | dept_no | salary | dept_name |
+--------+---------+--------+-----------+
|  43624 | d007    | 158220 | Sales     |
+--------+---------+--------+-----------+
1 row in set (1.77 sec)

用视图过滤不想要的数据

CREATE VIEW sales_departments_salary AS SELECT salaries.emp_no, salary, dept_name FROM salaries, dept_emp, departments WHERE salaries.emp_no = dept_emp.emp_no AND dept_emp.dept_no = departments.dept_no AND dept_name = 'Sales';

创建一个销售部门的员工id，部门id，薪水列表。

SELECT * FROM sales_departments_salary;

+--------+--------+-----------+
| emp_no | salary | dept_name |
+--------+--------+-----------+
|  10002 |  65828 | Sales     |
|  10002 |  65909 | Sales     |
|  10002 |  67534 | Sales     |
|  10002 |  69366 | Sales     |
|  10002 |  71963 | Sales     |
+--------+--------+-----------+
5 rows in set (0.00 sec)

如果在使用视图的时候额外添加了WHERE子句，那么视图内的子句会与视图外的子句自动组合。

更新视图

更新视图有一个前提条件，如果在视图中无法确定源数据表，那么将无法更新。也就是说如果视图中包含以下语句将不允许更新：

  • 分组（使用GROUP BY和HAVING）
  • 联结
  • 子查询
  • 并
  • 聚集函数(MIN(),SUM(),COUNT()等)
  • DISTINCT
  • 导出列

小结：

  • 视图相当于是对MySQL的SELECT层语句做了次封装，可以复用。相当于是一个虚拟的表，功能与数据表无异。
  • 视图的更新条件还是蛮苛刻的，很少有能更新的场景。所以一般视图是用于检索，基本不用于更新。