SQL视图

1、初识视图

1.1视图的定义：

视图是一张虚拟存在的表，视图中的数据并不在数据库中真实存在，行和列数据来自定义视图查询中使用的表，并在使用视图时动态生成的。

1.2视图的基本操作：

--创建视图
create or replace view stu_v_1 as select SId, Sname from student where SId <= 10;

--查询创建视图语句
show create view stu_v_1;
--查询数据内容
select * from stu_v_1 where sid = 7;

--修改视图方式1
create or replace view stu_v_1 as select SId, Sname,Sage from student where SId <= 10;
--修改视图方式2
alter view stu_v_1 as select SId, Sname from student where SId <= 10;

--删除视图
drop view if exists stu_v_1;

2、视图检查的必要性;

2.1没有视图检查时存在的问题：

不符合条件的数据可以通过视图插入到基表，但在视图中查询不到

举例：

create or replace view stu_v_1 as select SId, Sname from student where SId <= 20;

//插入的数据实际存到了student表中
insert into stu_v_1 VALUES(14, 'Tom');
select * from stu_v_1;
insert into stu_v_1 VALUES(30, 'lily');

执行select * from stu_v_1 查询视图表，发现id为30的查询不到，虽然通过视图插入了id为30的数据，但是id为30的记录在视图表中没有显示。这是因为在创建视图表时指定了sid

插入成功，下表是插入结果

student表中，id为 14和30的都插入成功了，且能够在student基表看到插入的数据：

查询视图stu_v_1;结果发现查询不到id为30的！

3、视图检查

3.1视图检查介绍：

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

举例：加上视图检查：不符合视图创建条件的 id为30的插入不进去

create or replace view stu_v_1 as select SId, Sname from student
where SId <= 20 with cascaded check option;

insert into stu_v_1 VALUES(14, 'Tom'); <!--执行成果->
insert into stu_v_1 VALUES(30, 'lily'); <!--执行报错，报如下的错-->

3.2视图 with check cascaded option和 with check local option的区别

在加上with check option时，如果不加cascaded或local时，默认是cascaded

	区别	共性
with check cascaded option	该检查会向其依赖的视图传递	都会递归检查其依赖，及子依赖
with check local option	该检查只会在此视图生效

3.2.1两层依赖举例：

操作sql语句：

视图2依赖视图1

create or replace view stu_v_1 as select SId, Sname from student 
where SId <= 20 ;

create or replace view stu_v_2 as select SId, Sname from stu_v_1 
where SId >= 10 with cascaded check option;

<!--执行失败，因为加了with check option不满足 stu_v_2的条件sid>=10->
insert into stu_v_2 VALUES(7, 'Tom');
<!--执行失败，因为加了with check option不满足 stu_v_2依赖的stu_v_1的条件sid<=20 ->
insert into stu_v_2 VALUES(26, 'lily');
<!--执行成功，因为加了with check option，会检查条件。stu_v_2的条件符合，且stu_v_2依赖的stu_v_1的条件也符合 -->
insert into stu_v_2 VALUES(15, 'success');

执行结果：

3.2.2三层依赖举例：

cascaded：

创建三个视图stu_v_1、stu_v_2、stu_v_3，视图3依赖2，视图2依赖1，当在视图3中插入一个id为28时

• 先看stu_v_3，stu_v_3没有加视图检查，因此满足视图3，
• 但是视图3依赖了视图2，向下递归检查，视图2加了条件with cascaded check option，因此会看视图2的条件，id为28满足 id>10的视图2的条件
• 视图2依赖了视图1，因此检查视图1，且视图2的with cascaded check option 会向下传递，在检查视图1时，发现id为28不满足视图1的条件，因此id 为28的插入不进去。具体效果如下图

local：

with local check option只会在当前视图起作用，不会向下传递，具体举例如下

• 视图5依赖视图4，视图6依赖视图5
• 插入id为28的记录到stu_v_6时，视图6没有加视图检查，因此视图6满足；
• 视图6依赖视图5，检查视图5，视图5加了视图检查with local check option，因此检查视图5的条件是否满足，id为28大于10，因此满足；
• 视图5依赖视图4，检查视图4，而视图4没加视图检查，且视图5位with local check option，不影响视图4，因此视图4的条件不生效。所以id为28的也可以插入视图6

create or replace view stu_v_4 as select SId, Sname from student 
where SId <= 20 ;

create or replace view stu_v_5 as select SId, Sname from stu_v_4 
where SId >= 10 with local check option;

create or replace view stu_v_6 as select SId, Sname from stu_v_5 
where SId <= 16;

insert into stu_v_6 VALUES(11, 'Tom');

insert into stu_v_6 VALUES(17, 'lily');

insert into stu_v_6 VALUES(28, 'aaa');

 以下是运行结果： 

4、视图优点

• 简单

        视图不仅可以简化用户对数据的理解，也可以简化他们的操作。经常使用的查询的列可以定义为视图，不用每次指定全部的条件。

• 安全

        数据库可以授权，但mysql只能控制到表，字段是做不到的，视图可以控制到表的字段。

• 数据独立

        数据库基础表的字段变了，只需对视图进行更新即可。这样可以屏蔽基表的变化对业务的影响。

        例如基表的sutdent的Sname要修改为stuName，那么只需要在创建视图时，稍作修改，修改如下。

create or replace view stu_v_2 as select SId, stuName as Sname from stu_v_1
where SId >= 10 with cascaded check option;