MySQL--视图详解

介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表（称为基表），并且是在使用视图时动态生成的。

简而言之：视图只保存了查询的SQL逻辑，不保存查询结果，所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法：

• 创建

 create [or replace] view 视图名称[(列表名称)] as select [with [cascaded | local] check option]

• 查询

 查看创建视图语句： show create view 视图名称；查询视图数据： select * from 视图名称

• 修改

 方式一： create [or replace] view 视图名称[(列表名称)] as select [with [cascaded | local] check option]方式二： alter view 视图名称[(列表名称)] as select [with [cascaded | local] check option]

• 删除

 drop view [if exists] 视图名称 [视图名称]

代码展示：

 -- 创建视图create or replace view `stu_v_1` as select id,name from student where id <= 10; -- or replace 在创建视图时可以不加-- 查看创建视图语句show create view `stu_v_1`;-- CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_v_1`-- AS select `student`.`id` AS `id`,`student`.`name` AS `name` from `student` where (`student`.`id` <= 10)-- 查看视图数据select * from stu_v_1;select * from stu_v_1 where id < 3;-- 修改视图create or replace view `stu_v_1` as select id,name,no from student where id < 10;  -- 修改视图时必须有 or replacealter view `stu_v_1` as select id,name from student where id < 10; -- 第二种方式-- 删除视图drop view if exists `stu_v_1`;

注意事项：

虽然创建视图和修改视图的语句一样，但是创建视图时 or replace可以不加，修改视图主要在于or replace

检查选项

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

... with cascaded check option 
... with local check option 

默认为cascade

代码展示：

 create or replace view `stu_v_1` as select id,name from student where id <= 20;-- 添加数据insert into `stu_v_1` values(6,'Tom'); -- 视图是虚拟存在的表，在视图上添加数据实际上是在视图的基表中添加数据-- 查询数据select * from `stu_v_1`;

如果添加的数据不符合创建视图时的语法会怎样？

代码展示：

-- 再次添加数据
insert into `stu_v_1` values(21,'Tom'); -- 插入id>20的数据
-- 再次查询
select * from `stu_v_1`;-- 无法查询到id为21的值，原因：在创建视图时添加了条件 id <= 20;
-- 为了防止这种情况的发生，在视图创建时添加检查选项。

无法查询到id为21的值，原因：在创建视图时添加了条件 id <= 20; 为了防止这种情况的发生，我们可以在视图创建时添加检查选项。

-- 修改视图
create or replace view `stu_v_1` as select id,name from student where id <= 20 with cascaded check option ;
-- 再次测试
insert into `stu_v_1` values(21,'Tom'); -- 报错 [HY000][1369] CHECK OPTION failed 'demo.stu_v_1'

选项区别

• cascade（级联）：

举例说明：

首先创建v1视图，不添加检查选项。

create or replace view `v1` as select id,name from student where id <= 20;

此时，对该视图进行增删改操作不会去检查视图默认条件的。会出现视图不允许的情况

再添加一个基于v1视图的v2视图并在后面添加检查选项。

create or replace view `v2` as select id,name from v1 where id >= 10 with cascade check option;

此时，在操作v2视图时，底层会先去检查操作语句是否满足v2的条件（id>= 10），由于检查选项为cascade，数据库底层不仅要满足v2的条件，还要满足v1的条件。就相当于在v1后面添加了检查选项

create or replace view `v1` as select id,name from student where id <= 20 with cascade check option;

假如说v1又关联了某一个视图，也就是在v1关联的视图后面添加了检查选项。

在创建一个v3视图基于v2视图，不添加检查选项

create or replace view `v3` as select id,name from v2 where id <= 15;

则操作v3视图时，不会检查v3的视图条件，会去检查v2和v1的视图条件。

代码展示：

 -- 创建没有检查选项的视图v1create or replace view `stu_v_1` as select id,name from student where id <= 20;-- 测试插入数据insert into `stu_v_1` values(8,'Tom'); -- 插入成功，id<20insert into `stu_v_1` values(25,'Tom'); -- 插入成功，id>20，但没有添加检查选项，因此成功​-- 创建基于v1的视图v2,并添加检查选项create or replace view `stu_v_2` as select id,name from `stu_v_1` where id >= 10 with cascaded check option ;-- 测试插入数据insert into `stu_v_2` values(7,'Tom'); -- 插入失败，不满足v2视图条件 [HY000][1369] CHECK OPTION failed 'demo.stu_v_2'insert into `stu_v_2` values(26,'Tom'); -- 插入失败，不满足v1视图条件 ，因为检查选项也需要去检查当前视图所依赖的底层的所有视图。[HY000][1369] CHECK OPTION failed 'demo.stu_v_2'insert into `stu_v_2` values(15,'Tom'); -- 插入成功，满足v1，v2视图条件。​-- 在创建一个v3视图基于v2视图，不添加检查选项create or replace view `stu_v_3` as select id,name from `stu_v_2` where id <= 15 ;-- 测试插入数据insert into `stu_v_3` values(11,'Tom'); -- 执行成功 满足v1,v2,v3视图条件insert into `stu_v_3` values(17,'Tom'); -- 执行成功 因为v3视图没有添加选项，就没有检查该视图的条件，直接去检查v3关联视图v2的视图条件，v2添加了检查选项，而又顺着v2关联的视图v1，检查v1的视图条件insert into `stu_v_3` values(28,'Tom'); -- 执行失败 [HY000][1369] CHECK OPTION failed 'demo.stu_v_3' 满足v2视图条件，但没有满足v1视图条件，因此报错。

• local（本地）

举例说明

首先创建v4视图，不添加检查选项。

create or replace view `v4` as select id,name from student where id <= 15;

此时，对该视图进行增删改操作不会去检查视图默认条件的。会出现视图不允许的情况

再添加一个基于v4视图的v5视图并在后面添加检查选项。

create or replace view `v5` as select id,name from v4 where id >= 10 with local check option;

此时，在操作v5视图时，底层会先去检查操作语句是否满足v5的条件（id>= 10），由于检查选项为local，数据库底层不仅要满足v5的条件，还要检查v4是否添加了检查选项。如果v4视图中添加则检查，如果没有添加，则不检查。

在创建一个v6视图基于v5视图，不添加检查选项

create or replace view `v6` as select id,name from v5 where id < 20;

则操作v6视图时，不会检查v6的视图条件，会去检查v5的视图条件，再去检查v4是否添加了检查选项。如果v4视图中添加则检查，如果没有添加，则不检查。

代码展示：

 -- 创建没有检查选项的视图v4create or replace view `stu_v_4` as select id,name from student where id <= 15;-- 测试插入数据insert into `stu_v_4` values(8,'Tom'); -- 插入成功，id<15insert into `stu_v_4` values(25,'Tom'); -- 插入成功，id>15，但没有添加检查选项，因此成功-- 创建基于v4的视图v5,并添加检查选项create or replace view `stu_v_5` as select id,name from `stu_v_4` where id >= 10 with local check option ;-- 测试插入数据insert into `stu_v_5` values(13,'Tom'); -- 执行成功，id>10且id<15，满足v5条件v5会递归到关联视图v4，去检查v4是否添加了检查选项，如果添加了，则满足v4条件，如果没有，则不检查insert into `stu_v_5` values(17,'Tom'); -- 执行成功 id>10 id>15，满足v5条件，不满足v4条件，但v4没有检查选项，所以可以执行-- 在创建一个v6视图基于v5视图，不添加检查选项create or replace view `stu_v_6` as select id,name from `stu_v_5` where id < 20 ;-- 测试插入数据insert into `stu_v_6` values(14,'Tom'); -- 执行成功insert into `stu_v_6` values(9,'Tom'); -- 执行失败 不满足v5条件。-- 修改v4视图 添加检查选项create or replace view `stu_v_4` as select id,name from student where id <= 15 with local check option ;-- 测试插入数据insert into `stu_v_5` values(18,'Tom');-- 执行失败，id>10 id>15，满足v5条件v5会递归到关联视图v4，去检查v4是否添加了检查选项，如果添加了，则满足v4条件，如果没有，则不检查，-- 此时v4视图有检查选项，所以需要满足v4视图条件，且插入语句不满足v4视图条件，所以插入失败-- [HY000][1369] CHECK OPTION failed 'demo.stu_v_5'

区别：

• cascaded 比 local更加严格,前者只要是上级关联的都会检查,后者是上级关联的要看有没有规则再决定检查

• CASCADED 加的限制会向上辐射，而LOCAL只限制当前视图

更新

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

  • 聚合函数或者窗口函数（sum（），min（），max（），count（）等）

  • distinct关键字（去重）

  • group by（分组操作）

  • having（分组之后的过滤）

  • union 或者union all

  代码展示（依聚合函数为例）：

   -- 创建视图，使用聚合函数create view `stu_v_count` as select count(*) from student;-- 插入数据insert into stu_v_count values (10); -- 插入失败 [HY000][1471] The target table stu_v_count of the INSERT is not insertable-into-- 原因：视图使用聚合函数，导致视图中的行数据与表中的行数据不是一一对应的关系，因此，视图不被允许更新。

作用

• 操作简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作，那些经常被使用的查询可以被定义为视图，从而使客户不必为以后的操作每次都要指定全部的条件。

• 保证数据安全：对于数据库当中的操作，我们可以做到用户授权，通过数据库的用户授权来决定，每一个用户登上MySQL后能看到和操作的数据库或者表，但是对于MySQL系统的授权，只能操作到表，不能够在精细了。无法控制到表中的字段，但是我们可以通过视图做到，比如我们只想让某一个用户只看到哪张表中的部分字段，此时我们就可以创建一个以该表为基表只有部分字段的视图，对于查看该视图的用户而言，其他字段不可见，这样就保证了一些敏感数据的安全性 。

• 数据独立：视图可以帮助用户屏蔽真实表结构变化带来的影响。

  视图只是一张虚拟表，当该视图的基表的字段发生修改时，视图可以通过起别名来杜绝基表字段的改变对业务的影响

总结案例

案例需求：

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

• 查询每个学生所选修的课程（三张表联查），这个功能很多业务都会用到，为了简化操作，定义一个视图。

代码展示：

 -- ======综合案例=====create view `user_view_operation` as select id,name,phone,profession,age,gender,status,createTime from user;create view `s_sc_c_view` as select s.name '学生姓名',c.name '课程姓名' from student s,student_course sc,course c where s.id = sc.studentid && sc.courseid = c.id;create or replace view `s_sc_c_view` as select s.name '学生姓名',s.no '学生学号',c.name '课程姓名' from student s left join student_course sc on s.id = sc.studentid  inner join course c on sc.courseid = c.id;

希望对大家有所帮助！