MySQL 从入门到精通 9：视图
MySQL 从入门到精通 9：视图
视图是一种通过查询语句构建的虚拟表，它并不真正存储数据，它的数据来源于创建视图的基础表。
语法
创建视图
CREATE [OR REPLACE] VIEW 视图名称（列名列表） AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION]
示例：
mysql> create view student_v_1 as-> select id,name from student where id<10;
查询视图
查看视图创建语句：
mysql> show create view student_v_1;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| student_v_1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `student_v_1` AS select `student`.`id` AS `id`,`student`.`name` AS `name` from `student` where (`student`.`id` < 10) | utf8mb4 | utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
对于视图，可以像查询表那样通过 SELECT语句进行查询：
mysql> select * from student_v_1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 黛绮丝 |
| 2 | 谢逊 |
| 3 | 殷天正 |
| 4 | 韦一笑 |
+----+-----------+
修改视图
可以使用创建视图的语句添加 OR REPLACE 修改已有视图：
mysql> create or replace view student_v_1 as select id,name,no from student where id<10;
Query OK, 0 rows affected (0.03 sec)mysql> select * from student_v_1;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 黛绮丝 | 2000100101 |
| 2 | 谢逊 | 2000100102 |
| 3 | 殷天正 | 2000100103 |
| 4 | 韦一笑 | 2000100104 |
+----+-----------+------------+
还可以通过 ALTER 语句：
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION]
比如：
mysql> alter view student_v_1 as-> select id,name from student where id<10;
Query OK, 0 rows affected (0.02 sec)mysql> select * from student_v_1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 黛绮丝 |
| 2 | 谢逊 |
| 3 | 殷天正 |
| 4 | 韦一笑 |
+----+-----------+
删除视图
DROP VIEW [IF EXISTS] 视图名称1[,视图名称2,...]
比如：
mysql> drop view if exists student_v_1;
Query OK, 0 rows affected (0.02 sec)mysql> select * from student_v_1;
ERROR 1146 (42S02): Table 'itcast.student_v_1' doesn't exist
CHECK OPTION
视图不仅可以用于数据查询，还可以通过视图添加/修改/删除数据，比如存在视图：
create view student_v_1 asselect id,name from student where id<=20;
通过视图插入一条数据：
insert into student_v_1(id, name) values (5,'Tom');
基础表和视图中都可以看到这条新数据。
但如果插入的数据超过视图条件限制，比如：
insert into student_v_1(id, name) values (21,'Tom');
就会发生基础表中可以看到新数据，但视图中没有的情况。这是因为视图有条件限制（id<=20），而通过视图插入的数据并不满足视图的条件限制。
可以在创建视图时添加一个选项（WITH CHECK OPTION）进行限制：
create or replace view student_v_1 asselect id,name from student where id<=20 with check option ;
此时再通过视图添加不满足条件的数据：
insert into student_v_1(id, name) values (22,'Tom');
执行失败，报错：
[HY000][1369] CHECK OPTION failed 'itcast.student_v_1'
添加了 WITH CHECK OPTION 后，通过视图更新数据时会进行检查，如果更新后的数据不满足视图条件，就不能更新。
WITH CHECK OPTION 有两种类型：
- WITH CASCADED CHECK OPTION
- WITH LOCAL CHECK OPTION
默认是 WITH CASCADED CHECK OPTION，此时 CASCADED 可以省略，即 WITH CHECK OPTION。
它们的区别是，如果是基于视图创建的多级视图，其对于每层视图的条件检查行为不同。
CASCADED
假设有三个视图：
create or replace view student_v_1 asselect id,name from student where id<=20;create or replace view student_v_2 asselect id,name from student_v_1 where id>=10 with cascaded check option ;create or replace view student_v_3 asselect id,name from student_v_2 where id<=30;
最上层和最下层的视图都没有 CHECK OPTION，中间的视图有 CASCADED CHECK OPTION。
最内层的视图添加不符合条件的数据：
insert into student_v_1(id, name) values (22,'Tom');
可以正常插入，因为没有 CHECK OPTION。
通过中间的视图添加数据：
insert into student_v_2(id, name) values (11,'Tom');
正常插入。
再次通过中间视图插入数据：
insert into student_v_2(id, name) values (23,'Tom');
失败，因为虽然满足中间视图的条件，但是向上检索依赖的视图 student_v_1 的条件，发现不满足，所以失败。
通过最外层的视图插入数据：
insert into student_v_3(id, name) values (31,'Tom');
-- 失败
insert into student_v_3(id, name) values (24,'Tom');
-- 失败
insert into student_v_3(id, name) values (12,'Tom');
-- 成功
虽然最外层的视图本身没有 CHECK OPTION，但依然会检查其依赖的 student_v_2 的条件，发现其是 CASCADED CHECK OPTION，因此所有插入的数据需要满足 student_v_2 和 student_v_1 的条件。
可以发现，只要视图使用了 CASCADED CHECK OPTION，该视图和其依赖的视图的条件都会被检查，无论其依赖的视图是否使用了 CHECK OPTION 选项。
LOCAL
LOCAL CHECK OPTION 的行为与 CASCADED CHECK OPTION 类似，区别是其依赖的视图条件是否会被检查取决于视图本身，而不是像 CASCADED CHECK OPTION 那样强制检查所有依赖视图的条件。
假设存在视图：
create or replace view student_v_1 asselect id,name from student where id<=20;create or replace view student_v_2 asselect id,name from student_v_1 where id>=10 with local check option ;create or replace view student_v_3 asselect id,name from student_v_2 where id<=30;
同样，最内层和最外层都没有使用 CHECK OPTION，中间的视图使用 LOCAL CHECK OPTION。
通过最内层添加数据：
insert into student_v_1(id, name) values (22,'Tom');
即使不满足条件也能添加，因为没有 CHECK OPTION。
通过中间视图添加：
insert into student_v_2(id, name) values (11,'Tom');
-- ok
insert into student_v_2(id, name) values (23,'Tom');
-- ok
可以看到，只要满足 student_v_2 的条件就可以插入成功，即使不满足其依赖的视图 student_v_1 也可以，因为 LOCAL CHECK OPTION 是否会检查依赖的视图条件，取决于视图本身，而视图student_v_1本身没有 CHECK OPTION，所以不会被检查。
通过最外层视图插入数据：
insert into student_v_3(id, name) values (31,'Tom');
-- ok
insert into student_v_3(id, name) values (24,'Tom');
-- ok
insert into student_v_3(id, name) values (12,'Tom');
-- ok
insert into student_v_3(id, name) values (9,'Tom');
-- fail
可以看到，只要满足其依赖的视图student_v_2的数据都可以成功插入。
更新视图
并不是所有视图都可以用于数据更新，只有视图中的行和基础表中的行存在一一对应关系时才能通过视图更新数据。
比如创建一个统计不同专业学生数量的视图：
create view user_v_pro_count asselect tb_user.profession,count(*)from tb_usergroup by profession;
如果通过这个视图插入数据：
insert into user_v_pro_count values ('MySQL', 10);
会报错：
[HY000][1471] The target table user_v_pro_count of the INSERT is not insertable-into
作用
- 简单
- 视图可以简化用户对数据的理解，那些经常使用的复杂查询可以被定义为视图，可以避免用户每次查询时构建复杂条件的查询语句。
- 安全
- MySQL 不支持将用户权限细分到表的特定列和行上，可以通过视图控制用户仅对表的部分列和行可见。
- 数据独立
- 视图可以帮助用户屏蔽真实表结构变化带来的影响。
参考资料
- 黑马程序员 MySQL数据库入门到精通