19.视图

视图概述

限制其它用户对数据库表的访问,因为视图可以有选择性的显示数据库表的一部分； 容易实现复杂的查询； 对于相同的数据可以产生不同的视图；

视图分类

视图分为简单视图和复杂视图，最基本差别在DML操作上

创建试图

create view 查询语句

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW view_name [(column_list)] 
AS select_statement
 [WITH [CASCADED | LOCAL] CHECK OPTION]

OR REPLACE：如果所创建的视图已经存在，该选项表示修改原视图的定义；

view_name ：视图的名称；

column_list ：列名，列名的数量必须和视图所对应查询语句的列数量相等；

select_statement ：一条完整的SELECT语句；

WITH CHECK OPTION：一个约束条件，通过视图所插入或修改的数据行必须满足视图所定义的查询；

ALGORITHM子句是可选的，它表示使用何种算法来处理视图。此外，它并不属于标准SQL的一部分，而是MySQL对标准SQL进行的功能扩展。ALGORITHM可以设置三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，则默认值为UNDEFINED(未定义的)。

对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。 对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。 对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。 [WITH [CASCADED | LOCAL] CHECK OPTION]是可选的。该选项中的CASCADED为默认值，LOCAL CHECK OPTION用于在可更新视图中防止插入或更新行，此选项一般不使用。

示例：

表请参考，18.11章节

CREATE  OR REPLACE VIEW  v_queryorder (a,b) AS
SELECT 
  myorder.order_num,
  SUM(COUNT) 
FROM
  detail 
  INNER JOIN myorder 
    ON detail.order_num = myorder.order_num 
GROUP BY myorder.order_num ;

SELECT  *  FROM v_queryorder;

例：创建一个视图v_emp10，通过该视图只能查看10号部门的员工编号，员工姓名，职位。

-- 例：创建一个视图v_emp10，通过该视图只能查看10号部门的员工编号，员工姓名，职位。
CREATE OR REPLACE VIEW v_emp10 (NO, NAME, WORK) AS 
SELECT 
  empno,
  ename,
  job 
FROM
  emp 
WHERE deptno = 10 ;

DESC v_emp10
Field	Type		Null	Key	Default	Extra
no		int(4)		NO		\N	
name	varchar(10)	YES		\N	
work	varchar(9)	YES		\N

创建试图练习

1.创建一个视图，通过该视图可以查询到工资在2000-5000内并且姓名中包含有A的员工编号，姓名，工资。

2.通过上述创建的视图查询数据

/***

1.创建一个视图，通过该视图可以查询到工资在2000-5000内并且姓名中包含有A的员工编号，姓名，工资。

2.通过上述创建的视图查询数据
**/
CREATE OR REPLACE VIEW  emp_sal (eno,NAME,salary)  AS 
SELECT empno ,ename ,sal FROM emp WHERE sal BETWEEN 2000 AND  5000 AND ename LIKE '%A%'

SELECT * FROM emp_sal;

复杂试图的创建

创建视图时，在子查询中使用列的别名

例：创建一个视图，通过该视图可以查看每个部门的名称，最低工资，最高工资，平均工资

复杂试图的创建练习

1.创建一个视图，通过该视图可以查询到工作在NEW YORK和CHICAGO的员工编号，姓名，部门编号，入职日期。

2.创建一个视图，通过该视图可以查询到每个部门的部门名称及最低工资。

3.通过如上视图，查询每个部门工资最低的员工姓名及部门名称

修改试图

用 CREATE OR REPLACE VIEW子句修改视图empvu10，为每个列添加别名。

CREATE VIEW子句中别名的顺序必须和内部查询中的列的顺序一一对应。

视图上执行DML操作

在简单视图上可以执行 DML 操作；

您可以通过视图删除基表中数据，只要视图中不出现以下情况：

• Group 函数；
• GROUP BY 子句；
• DISTINCT 关键字；

您可以通过视图修改基表中数据，只要视图中不出现以下情况：

• GROUP函数、
• GROUP BY子句，
• DISTINCT关键字；
• 使用表达式定义的列；

您可以通过视图向基表插入数据，只要视图中不出现以下情况：

• GROUP函数、GROUP BY子句，DISTINCT关键字；
• 使用表达式定义的列；
• 基表中未在视图中选择的其它列定义为非空并且没有默认值；

如果要确保在视图上执行的DML操作仅限于一定的范围，便可使用WITH CHECK OPTION子句；

在视图中任何修改部门编号的操作都会失败，因为这违反了 WITH CHECK OPTION约束。

删除试图

删除视图并不会删除数据，因为视图是基于数据库中的基表的虚表。

DROP VIEW view;