MySQL学习笔记之MySQL视图（view）
一、基本概念
视图(view)是一种虚拟存在的表，作为一个select语句保存在数据字典中，其本身并不包含任何数据。视图的数据来自定义视图的查询中使用的表，使用视图动态获取数据。
基表：创建视图时使用到的表叫基表。
视图数据的变化会影响基表的数据，基表数据的变化也会影响视图的数据。
视图有什么用？为什么要使用视图呢？
视图主要有以下几点优点：
1）简单：由于视图的是由基表创建产生的，所以你不需要关心后面对应的表的结构、关联条件和筛选条件，基表的数据是已经是过滤好的复合条件的结果集。
2）安全：视图中的数据字段只是使用者被允许访问查询的字段，而表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，基表增加列对视图没有影响；基表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
使用视图的大部分情况是为了保障数据安全性，提高查询效率。
二、视图操作
1）基本语法：create view view_name as 查询语句
例：创建一个获取所有学生各科成绩的视图
mysql> create view students_grade as select st.*,sc.score,su.name as 科目 from students st left join score sc on st.id=sc.student_id left join subject su on sc.subject_id=su.id;
Query OK, 0 rows affected (0.01 sec)
2）查看视图信息：
mysql> show create view students_grade \G;
*************************** 1. row ***************************
View: students_grade
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `students_grade` AS select `st`.`id` AS `id`,`st`.`name` AS `name`,`st`.`gender` AS `gender`,`st`.`age` AS `age`,`sc`.`score` AS `score`,`su`.`name` AS `科目` from ((`students` `st` left join `score` `sc` on((`st`.`id` = `sc`.`student_id`))) left join `subject` `su` on((`sc`.`subject_id` = `su`.`id`)))
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
有关视图的信息保存在information_schema数据库中的views表中，可以通过SQL语句查看视图信息
mysql> select * from information_schema.views where table_name='stu'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: testdb2
TABLE_NAME: stu
VIEW_DEFINITION: select `testdb2`.`students`.`id` AS `id`,`testdb2`.`students`.`name` AS `name`,`testdb2`.`students`.`gender` AS `gender`,`testdb2`.`students`.`age` AS `age` from `testdb2`.`students` where (`testdb2`.`students`.`id` > 15)
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
3）使用视图：
视图使用和使用数据表相同。
mysql> select * from students_grade;
+----+-----------+--------+------+-------+--------+
| id | name | gender | age | score | 科目 |
+----+-----------+--------+------+-------+--------+
| 1 | 张三 | 1 | 18 | 69 | NULL |
| 1 | 张三 | 1 | 18 | 90 | NULL |
| 1 | 张三 | 1 | 18 | NULL | 英语 |
| 1 | 张三 | 1 | 18 | 79 | 数学 |
| 1 | 张三 | 1 | 18 | 80 | 语文 |
| 2 | 李四 | 1 | 19 | 57 | NULL |
| 2 | 李四 | 1 | 19 | NULL | NULL |
| 2 | 李四 | 1 | 19 | 85 | 英语 |
| 2 | 李四 | 1 | 19 | 97 | 数学 |
| 2 | 李四 | 1 | 19 | 86 | 语文 |
| 3 | 周芷若 | 2 | 18 | 60 | NULL |
| 3 | 周芷若 | 2 | 18 | NULL | NULL |
| 3 | 周芷若 | 2 | 18 | NULL | 英语 |
| 3 | 周芷若 | 2 | 18 | NULL | 数学 |
| 3 | 周芷若 | 2 | 18 | 90 | 语文 |
| 4 | 赵敏 | 2 | 18 | 68 | NULL |
| 4 | 赵敏 | 2 | 18 | NULL | NULL |
| 4 | 赵敏 | 2 | 18 | 45 | 英语 |
| 4 | 赵敏 | 2 | 18 | 87 | 数学 |
| 4 | 赵敏 | 2 | 18 | 86 | 语文 |
| 5 | Lucy | 2 | 19 | NULL | NULL |
| 6 | Tony | 1 | 20 | NULL | NULL |
| 7 | Lucy | 2 | 20 | NULL | NULL |
+----+-----------+--------+------+-------+--------+
23 rows in set (0.01 sec)
4）查看所有视图
mysql> show table status where comment='view';
5）修改视图
a）修改视图创建语句
语法：create or replace view view_name as select 查询语句
例：mysql> create or replace view stu as select name,gender from students where id > 10;
Query OK, 0 rows affected (0.02 sec)
b）使用alter修改视图
alter [algorithm={undefined|merge|temptable}] [definer={'用户名'@'主机'|current_user}] [sql security {definer|invoker}] view view_name as select 查询语句 [with [local|cascaded] check option]
例：mysql> alter algorithm=undefined definer = current_user sql security definer view stu as select name,gender from students where id > 15 with cascaded check option;
Query OK, 0 rows affected (0.01 sec)
c）视图DML操作
视图的DML操作将会影响到基表中的数据，基表中数据的更改也会影响视图数据。
mysql> select * from stu;
+-----------+--------+
| name | gender |
+-----------+--------+
| Lily | 2 |
| Lily | 2 |
| 令狐冲 | 1 |
| 王语嫣 | 2 |
+-----------+--------+
4 rows in set (0.01 sec)
mysql> update stu set name='LiLei' where name='Lily';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from stu;
+-----------+--------+
| name | gender |
+-----------+--------+
| LiLei | 2 |
| LiLei | 2 |
| 令狐冲 | 1 |
| 王语嫣 | 2 |
+-----------+--------+
4 rows in set (0.01 sec)
mysql> alter view stu as select * from students;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into stu(name,gender) values('小红',2);
Query OK, 1 row affected (0.01 sec)
mysql> delete from stu where id = 1;
Query OK, 1 row affected (0.00 sec)
6）drop删除视图
语法：drop view [if exists] view_name[,view_name,...];
mysql> drop view if exists stu,stu1,stu2,stu3,stu4;
Query OK, 0 rows affected, 1 warning (0.02 sec)
7）带可选项的视图创建
语法：
CREATE [or replace] [algorithm = {undefined | merge | temptable}] DEFINER=`用户名`@`主机` SQL SECURITY DEFINER VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
可选项说明：
a）or replace：创建视图时已经存在则替换，不存在创建。
b）algorithm：在定义视图时select语句中使用的算法，默认undefined：
① undefined：MySQL自动选择所需要的算法。
② merge：将视图的语句与视图定义合并，使得视图定义的某一部分取代语句的对应部分。
③ temptable：将视图的结果存入临时表，然后使用临时表执行语句。
merge和temptable的区别是merge最终查找的是基表，而temptable最终查询的是临时表。
c）definer：指定创建视图的用户和登录的主机。默认创建视图的用户就是定义者。
d）SQL security：创建视图时，创建者必须对创建视图时使用到的所有表具有select权限。sql security指定视图查询数据时的安全验证方式。
① SQL SECURITY DEFINER：默认，表示访问视图时以创建者的权限来执行。
② SQL SECURITY INVOKER：表示以访问者的权限来执行。
e）with check option 约束：对视图所做的DML操作的结果，不能违反视图的where条件的限制。
例1：当不使用with check option约束时：
mysql> create or replace view stu as select * from students where id > 15;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into stu(id,name,gender) values(11,'Lily',2);
Query OK, 1 row affected (0.01 sec)
例2：基于视图stu创建新的视图stu1，并使用with cascaded checked option
mysql> create or replace view stu1 as select * from stu with cascaded check option;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into stu1(id,name,gender) values(12,'Lily',2);
ERROR 1369 (HY000): CHECK OPTION failed 'testdb2.stu1' #插入失败
例3：基于视图stu1创建新的视图stu2，不使用约束
mysql> create or replace view stu2 as select * from stu1 where id < 30;
Query OK, 0 rows affected (0.01 sec
mysql> insert into stu2(id,name,gender) values(12,'Lily',2);
ERROR 1369 (HY000): CHECK OPTION failed 'testdb2.stu2' #依然插入失败，因为stu2是基于stu1创建的，所以stu2取决于stu1
mysql> insert into stu2(id,name,gender) values(21,'Lily',2);
Query OK, 1 row affected (0.00 sec) #插入成功，因为stu2没有做约束
总而言之，当视图使用WITH CASCADED CHECK OPTION时，mysql会循环检查视图的规则以及底层视图的规则。
来看下使用with local check option约束的情况：
mysql> alter view stu1 as select * from stu with local check option;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into stu2(id,name,gender) values(12,'Tomy',2);
Query OK, 1 row affected (0.01 sec)
在这种情况下可以执行成功，因为MySQL视图中的WITH LOCAL CHECK OPTIONS选项没有检查stu视图的规则。不过，在使用WITH CASCADED CHECK OPTION创建的stu1视图示例中，此语句执行失败。
mysql> insert into stu2(id,name,gender) values(33,'令狐冲',1);
Query OK, 1 row affected (0.01 sec)
mysql> alter view stu2 as select * from stu1 where id<30 with local check option;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu2(id,name,gender) values(32,'任盈盈',2);
ERROR 1369 (HY000): CHECK OPTION failed 'testdb2.stu2'
因此，如果视图使用WITH LOCAL CHECK OPTION，mysql会检查WITH LOCAL CHECK OPTION和WITH CASCADED CHECK OPTION选项的视图规则。与使用WITH CASCADED CHECK OPTION的视图不同，mysql检查所有依赖视图的规则。不过我们要注意，在mysql5.7.6之前，如果我们使用带有WITH LOCAL CHECK OPTION的视图，mysql只会检查当前视图的规则，并且不会检查底层视图的规则。
如果视图中的限制条件是基表中自增ID，使用视图insert数据时，不指定ID则将无视with check option限制。
mysql> insert into stu2(name,gender) values('王语嫣',2);
Query OK, 1 row affected (0.02 sec)
mysql> select * from students where name='王语嫣';
+----+-----------+--------+------+
| id | name | gender | age |
+----+-----------+--------+------+
| 34 | 王语嫣 | 2 | NULL |
+----+-----------+--------+------+
1 row in set (0.00 sec)
