第四章：视图【mysql数据库-进阶】

1 视图简介

视图(View) 是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑,不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

2 视图语法

2.1 创建视图

【语法】

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句[ WITH [ CASCADED| LOCAL ] CHECK OPTION]

【例子】

create or replace view stu_v_1 as select id,name  from tb_stu where id<=10;

【结果】
在DataGrid的右侧就会有一个视图的列表中存储了刚刚创建的视图

1.2 查询视图

【语法】

查看创建视图语句: SHOW CREATE VIEW视图名称;
查看视图数据: SELECT * FROM视图名称......

【例子】

-- 查看指定视图
show create view stu_v_1;
-- 查看视图中的数据
select * from stu_v_1;

【结果】

1.3 修改视图

【语法】

方式一: CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH[ CASCADED | LOCAL] CHECK OPTION]
方式二: ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[ CASCADED | LOCAL] CHECK OPTION ]

【例子】

-- 方式一
create or replace view stu_v_1 as select id,name,age  from tb_stu where id<=10;
-- 方式二
alter view stu_v_1 as select id,name from tb_stu where age>10;

1.4 删除视图

【语法】

DROP VIEW [IF EXISTS]视图名称[视图名称]

【例子】

drop view stu_v_1;

1.5 视图检查选项

假如我们创建一个视图：

create  or replace  view stu_v_1 as select id,age,name from tb_stu where id<=20;
select * from stu_v_1;

查询结果如下：

我们可以通过insert语句往视图里面插入数据，实际上是往视图对应的基本表中插入数据

insert into stu_v_1 values(6,20,'Tom');
insert into stu_v_1 values (30,18,'Lucy');
select * from stu_v_1;

但是查询的结果中，查询不到id为30的元组，因为视图中限制了id要小于20。因此就需要一个检查选项语句，检查是否符合视图中的要求。将创建示视图的语句修改成下面格式：

create  or replace  view stu_v_1 as select id,age,name from tb_stu where id<=20 with cascaded check option ;
-- 再次插入id为30的数据

此时就会报错[HY000][1369] CHECK OPTION failed 'itcast.stu_v_1'，检查到你插入的数据不符合你的视图要求。

当使用WITH:CHECK OPTION子句创建视图时, MySQL会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其符合视图的定义。MySQL允许基于另 一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。 为了确定检查的范围，mysql提供了两个选项:CASCADED和LOCAL,默认值为CASCADED。

选项1：caseaded检查选项

-- 先创建一个视图
create  or replace  view stu_v_1 as select id,age,name from tb_stu where id<=20;
-- 往视图中插入数据
insert into stu_v_1 values(9,20,'Alan');
insert into stu_v_1 values (35,18,'Dive');

此时不会报错，因为没有加检查选项
假如基于视图1再次创建一个视图，会有什么情况发生呢？

-- 基于视图1再次创建一个视图
create  or replace  view stu_v_2 as select id,age,name from stu_v_1 where id>=10 with cascaded check option ;
-- 插入数据
insert into stu_v_2 values(12,21,'Juli');
insert into stu_v_2 values (32,18,'Mary');

此时就会报错[HY000][1369] CHECK OPTION failed 'itcast.stu_v_2',虽然第二条插入语句满足第二个视图，但是检查选项cascaded会去检查他所依赖的视图，第二条sql不满足依赖的视图，所以会报错。

假如基于视图2又创建一个视图，又会出现什么情况呢？

-- 基于视图2创建一个视图
create or replace  view stu_v_3 as select id,age,name from stu_v_2 where id<=15;
insert into stu_v_3 values(13,20,'Alan');
insert into stu_v_3 values (17,18,'Dive');
insert into stu_v_3 values (28,18,'Dive');

此时执行那个sql语句会报错呢？
此时执行第三条插入语句的时候会报错，因为视图3没有加检查选项，所以不会检查是否满足视图3，但是会检查视图2以及视图2所依赖的视图1,id=28满足视图2的条件，但是不满足视图1的条件，所以会报错。

选项2：local检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其符合视图的定义。MySQL允许基于另- -个视图创建视图,它还会检查依赖视图中的规则以保持一致性。 为了确定检查的范围, mysql提供了两个选项:CASCADED和LOCAL,默认值为CASCADED。

请看下面的案例，创建视图1，不对视图1添加任何检查选项

create  or replace  view stu_v_1 as select id,age,name from tb_stu where id<=15;
insert into stu_v_1 values(9,20,'Alan');
insert into stu_v_1 values (35,18,'Dive');

毫无疑问，所有的sql语句都可以执行，因为没有加检查选项。
下面基于视图1创建一个视图2：

create  or replace  view stu_v_2 as select id,age,name from stu_v_1 where id>=10 with local check option ;
insert into stu_v_2 values(12,21,'Juli');
insert into stu_v_2 values (32,18,'Mary');
insert into stu_v_2 values (5,18,'Mary');

上面的那个sql会报错呢？
lacal检查选项首先会对自己这个视图进行检查，然后再对自己依赖的视图检查它是否有检查选项，如果有就要按照依赖的视图进行检查，如果没有就不会管。
所以第二条插入语句满足了视图2但是不满足于视图3，所以不会报错。
第三条插入语句不满足视图2所以会报错。

create  or replace  view stu_v_3 as select id,age,name from stu_v_2 where id<=20 with local check option ;
insert into stu_v_3 values (14,18,'Mary');

首先插入的sql语句会检查视图3，但是视图3没有检查选项，于是就会检查视图2，视图2有检查选项，所以就会检查一下插入语句是否符合要求，然后再次递归查询视图1，但是视图1没有检查选项，所以就不检查，因此这个插入语句会执行成功。

3.视图更新及作用

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一 -的关系。如果视图包含以下任何一项,则该视图不可更新:

• 聚合函数或窗口函数(SUM()、 MIN()、 MAX()、 COUNT()等 )
• DISTINCT
• GROUP BY
• HAVING
• UNION或者UNION ALL

作用：

• 简单：视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
• 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
• 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。比如基本表上的某个字段已经被修改了，但是我可以在视图上对其重命名。

4.案例设计

需求1：为了保证数据库表的安全性,开发人员在操作tb_ user表时，只能看到的用户的基本字段,屏蔽手机号和邮箱两个字段。

create  view user_base as select id,name,profession,age,gender,status,createtime from tb_user;

需求2：查询每个学生所选修的课程 (三张表联查) ,这个功能在很多的业务中都有使用到，为了简化操作,定义一个视图。

create view tb_stu_course_view as
select s.name student_name,s.no student_no ,c.name course_name from student s,student_course sc,course c where s.id = sc.studentid and sc.courseid = c.id;
-- 下次就不用写这么长的sql了，直接执行查询这个视图就可以了
select * from tb_stu_course_view;