MySQL进阶篇之视图（view）

04、视图/存储过程/触发器

4.1、视图(view)

4.1.1、简介及基本语法

1、介绍

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

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

2、语法

• 创建

  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] 视图名称 [,视图名称]...
  

3、演示

-- 创建视图
create or replace view stu_view_1 as select id,name from student where id <= 10;

-- 查询视图
show create view stu_view_1;            -- 查看视图stu_view_1的创建语句
select * from stu_view_1;               -- 查看stu_view_1视图数据
select * from stu_view_1 where id = 3;  -- 通过设置条件id=3来查看stu_view_1视图数据

-- 修改视图
create or replace view stu_view_1 as select id,name,no from student where id <= 10;

alter view stu_view_1 as select id,name from student where id <= 10;

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

4.1.2、检查选项

4.1.2.1、简介

1、案例

-- 创建视图
create or replace view stu_view_1 as select id,name from student where id <= 20;

-- 查看stu_view_1视图数据
select * from stu_view_1;

-- 向视图中插入两条数据
insert into stu_view_1 values (6,'小昭'); -- view中的数据并不在数据库中实际存在，因此插入的数据存放在student表中
insert into stu_view_1 values (30,'张无忌'); 

其中，第一条数据插入到student表中，并且查看stu_view_1视图数据，能够查到该条数据；

第二条数据同样能够插入到student表中，但是查看stu_view_1视图数据，不能够查到该条数据，这是因为在创建view的时候通过where限制了查询条件id<=20，于是这条数据插入到student之后，view中也不会显示，因为该数据不符合视图创建的查询语句。

针对第二条数据的问题，于是在创建视图时使用检查选项：

## 下述语句二选一
create or replace view stu_view_1 as select id,name from student where id <= 20 with cascaded check option;
create or replace view stu_view_1 as select id,name from student where id <= 20 with local check option;

需要先将student中插入的两条数据删除，再重新执行上述两条插入语句：

其中，第一条数据插入到student表中，并且查看stu_view_1视图数据，能够查到该条数据；

第二条数据插入时，报错：CHECK OPTION failed 'itcast.stu_view_1'

2、视图的检查选项

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

4.1.2.2、CASCADED

1、CASCADED：

• v1视图创建时没有使用级联(cascaded)检查选项

  此时对v1进行更改，由于v1创建视图时未使用检查选项，则不需要检查是否满足id<=20的条件。

• v2基于v1视图进行创建，并且使用级联(cascaded)检查选项

  此时对v2进行更改，由于v2创建视图时使用了检查选项，需要先检查是否满足id>=10的条件；

  然后由于v2是基于v1创建的，且使用的是级联(cascaded)检查选项，则还需要检查是否满足v1的条件，即检查是否满足id<=20的条件。

• v3基于v2视图进行创建，没有使用级联级联(cascaded)检查选项

  此时对v3进行更改，由于v3创建视图时未使用检查选项，则不需要检查id<=15的查询条件；

  由于v3是基于v2创建的，且v2创建视图时使用了检查选项，则需要检查是否满足id>=10的条件；

  然后由于v2是基于v1创建的，且使用的是级联(cascaded)检查选项，则还需要检查是否满足v1的条件，即检查是否满足id<=20的条件。

2、案例：

-- cascaded
-- 创建stu_view_1视图
create or replace view stu_view_1 as select id,name from student where id <= 20;

-- 向stu_view_1视图中插入数据
insert into stu_view_1 values (5,'赵敏');-- 能够插入到student表中
insert into stu_view_1 values (25,'周芷若');-- 能够插入到student表中

解释：

• 第一条插入数据：由于创建stu_view_1时没有使用检查选项，所以不需要检查5是否满足stu_view_1的查询条件，则5能够插入到student表中。
• 第二条插入数据：由于创建stu_view_1时没有使用检查选项，所以不需要检查25是否满足stu_view_1的查询条件，则25能够插入到student表中。

-- 创建stu_view_2视图（基于stu_view_1视图）
create or replace view stu_view_2 as select id,name from stu_view_1 where id >= 10 with cascaded check option;

-- 向视图stu_view_2中插入数据
insert into stu_view_2 values (7,'Tom');    -- 不能够插入到student表中，不满足stu_view_2查询条件；
insert into stu_view_2 values (26,'周芷若'); -- 不能插入到student表中，满足stu_view_2的条件，不满足tu_view_1的条件
insert into stu_view_2 values (15,'Tom');   -- 能够插入到student表中，满足stu_view_2，stu_view_1的查询条件；

解释：

• 第一条插入数据：由于stu_view_2创建视图时，使用了级联检查选项，首先需要检查7是否满足stu_view_2的查询条件，因为7<=10，并不符合stu_view_2的查询条件，因此7不能够插入到student表中。
• 第二条插入数据：由于stu_view_2创建视图时，使用了级联检查选项，首先需要检查26是否满足stu_view_2的查询条件，因为26>=10，符合stu_view_2的查询条件；然后由于stu_view_2是基于stu_view_1创建的，且根据级联检查选项的要求，需要检查26是否满足stu_view_1的查询条件，因为26>=20，并不符合stu_view_1的查询条件，因此26不能够插入到student表中。
• 第三条插入数据：由于stu_view_2创建视图时，使用了级联检查选项，首先需要检查15是否满足stu_view_2的查询条件，因为15>=10，符合stu_view_2的查询条件；然后由于stu_view_2是基于stu_view_1创建的，且根据级联检查选项的要求，需要检查26是否满足stu_view_1的查询条件，因为15<=20，符合stu_view_1的查询条件，因此15能够插入到student表中。

-- 创建stu_view_3视图（基于stu_view_2视图）
create or replace view stu_view_3 as select id,name from stu_view_2 where id <= 15;

-- 向视图stu_view_3中插入数据
insert into stu_view_3 values (11,'Tom');  -- 能够插入到student表中，满足stu_view_2，stu_view_1的条件 
insert into stu_view_3 values (17,'Tom');  -- 能够插入到student表中，满足stu_view_2，stu_view_1的查询条件；
insert into stu_view_3 values (28,'Tom');  -- 不能够插入到student表中，不满足stu_view_1的条件

解释：

• 第一条插入数据：由于stu_view_3创建时没有使用检查选项，因此不需要检查stu_view_3的查询条件；由于stu_view_3是基于stu_view_2创建的，需要检查stu_view_2的查询条件，根据stu_view_2使用的级联检查选项，同样需要检查stu_view_1的查询条件；11均满足stu_view_2和stu_view_1的检查条件，因此11能够插入到student表中。
• 第二条插入数据：由于stu_view_3创建时没有使用检查选项，因此不需要检查stu_view_3的查询条件；由于stu_view_3是基于stu_view_2创建的，需要检查stu_view_2的查询条件，根据stu_view_2使用的级联检查选项，同样需要检查stu_view_1的查询条件；17均满足stu_view_2和stu_view_1的检查条件，因此17能够插入到student表中。
• 第三条插入数据：由于stu_view_3创建时没有使用检查选项，因此不需要检查stu_view_3的查询条件；由于stu_view_3是基于stu_view_2创建的，需要检查stu_view_2的查询条件，根据stu_view_2使用的级联检查选项，同样需要检查stu_view_1的查询条件；28满足stu_view_2的查询条件，但是不满足stu_view_1的检查条件，因此28不能够插入到student表中。

上述无法插入数据时，报错：CHECK OPTION failed '数据库名.视图名'

如：CHECK OPTION failed 'itcast.stu_view_3'

4.1.2.3、LOCAL

1、LOCAL

• v1视图创建时没有使用级联(local)检查选项

  此时对v1进行更改，由于v1创建视图时未使用检查选项，则不需要检查是否满足id<=20的条件。

• v2基于v1视图进行创建，并且使用级联(local)检查选项

  此时对v2进行更改，由于v2创建视图时使用了检查选项，需要先检查是否满足id>=10的条件；

  然后由于v2是基于v1创建的，则需要递归到v1，但是由于v2使用的是local检查选项，且v1没有检查选项，则不需要检查是否满足v1的条件。

• v3基于v2视图进行创建，没有使用级联级联(local)检查选项

  此时对v3进行更改，由于v3创建视图时未使用检查选项，则不需要检查id<=15的查询条件；

  由于v3是基于v2创建的，且v2创建视图时使用了检查选项，则需要检查是否满足id>=10的条件；

  然后由于v2是基于v1创建的，则需要递归到v1，但是由于v2使用的是local检查选项，且v1没有检查选项，则不需要检查是否满足v1的条件。

2、案例：

• 案例1

  -- local
  -- 创建stu_view_4视图
  create or replace view stu_view_4 as select id,name from student where id <= 15;
  
  -- 向stu_view_4视图中插入数据
  insert into stu_view_4 values (5,'Tom'); -- 能够插入到student表中
  insert into stu_view_4 values (16,'Tom');-- 能够插入到student表中
  

  解释：

  • 第一条插入数据：由于创建stu_view_4时没有使用检查选项，所以不需要检查5是否满足stu_view_4的查询条件，则5能够插入到student表中。
  • 第二条插入数据：由于创建stu_view_4时没有使用检查选项，所以不需要检查16是否满足stu_view_4的查询条件，则16能够插入到student表中。

  -- 创建stu_view_5视图
  create or replace view stu_view_5 as select id,name from stu_view_4 where id >= 10 with local check option;
  
  -- 向视图stu_view_5中插入数据
  insert into stu_view_5 values (13,'Tom'); -- 能够插入到student表中
  insert into stu_view_5 values (17,'Tom'); -- 能够插入到student表中
  

  解释：

  • 第一条插入数据：由于创建stu_view_5时使用检查选项，所以需要检查13是否满足stu_view_5的查询条件，即13>=10，则满足stu_view_5的检查条件；stu_view_5是基于stu_view_4创建的，则需要递归到stu_view_4，且由于stu_view_5使用的是local检查选项，且stu_view_4没有使用检查选项，则不需要检查13是否满足stu_view_4的查询条件，于是13能够插入到student表中。
  • 第二条插入数据：由于创建stu_view_5时使用检查选项，所以需要检查17是否满足stu_view_5的查询条件，即17>=10，则满足stu_view_5的检查条件；stu_view_5是基于stu_view_4创建的，则需要递归到stu_view_4，且由于stu_view_5使用的是local检查选项，且stu_view_4没有使用检查选项，则不需要检查17是否满足stu_view_4的查询条件，于是17能够插入到student表中。

  -- 创建stu_view_6视图
  create or replace view stu_view_6 as select id,name from stu_view_5 where id < 20;
  
  -- 向stu_view_6视图中插入数据
  insert into stu_view_6 values (14,'Tom');  -- 能够插入到student表中
  

  解释：

  • 由于创建stu_view_6时没有使用检查选项，所以不需要检查14是否满足stu_view_6的查询条件；由于stu_view_6是基于stu_view_5创建的，则需要递归到stu_view_5，由于stu_view_5使用检查选项，所以需要检查14是否满足stu_view_5的查询条件，即14>=10，则满足stu_view_5的检查条件；stu_view_5是基于stu_view_4创建的，则需要递归到stu_view_4，且由于stu_view_5使用的是local检查选项，且stu_view_4没有使用检查选项，则不需要检查14是否满足stu_view_4的查询条件，于是14能够插入到student表中。

• 案例2

  -- local 案例2
  -- 创建stu_view_4视图
  create or replace view stu_view_4 as select id,name from student where id <= 15 with local check option;
  
  -- 创建stu_view_5视图
  create or replace view stu_view_5 as select id,name from stu_view_4 where id >= 10 with local check option;
  
  -- 向视图stu_view_5中插入数据
  insert into stu_view_5 values (18,'Tom'); -- 不能插入到student表中
  

  解释：

  • 由于创建stu_view_5时使用检查选项，所以需要检查18是否满足stu_view_5的查询条件，即18>=10，则满足stu_view_5的检查条件；stu_view_5是基于stu_view_4创建的，则需要递归到stu_view_4，且由于stu_view_5使用的是local检查选项，同时stu_view_4也使用了检查选项，则需要检查18是否满足stu_view_4的查询条件，即18<=15，于是18不能够插入到student表中。

总结：

cascaded：视图1使用cascaded检查选项，需要先检查插入数据是否满足视图1的查询条件；其次需要其次需要递归到视图1所依赖的视图2，检查插入数据是否满足视图1所依赖的视图2的查询条件（此时无论视图2是否使用了检查选项，都需要被检查），以此类推。

local：视图1使用了local检查选项，需要先检查插入数据是否满足视图1的查询条件；其次需要递归到视图1所依赖的视图2，若视图2使用了检查选项，则需要检查插入数据是否满足视图2的查询条件，以此类推。

4.1.3、更新及作用

1、视图的更新

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

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

2、作用

• 简单

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

• 安全

  数据库可以授权，但不能授权到数据库特定行和特定列上。通过视图用户只能查询和修改他们所能见到的数据。

• 数据独立

  视图可帮助用户屏蔽真实表结构变化带来的影响。

4.1.4、案例

根据如下要求，定义视图

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

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

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

create view student_view_course as select s.name student_name, s.no student_no, c.name course_name from student s, course c,student_course sc where s.id = sc.studentid and c.id = sc.courseid;

4.2、存储过程(procedure)

4.3、触发器(trigger)