Mysql高级6-视图

灬静悄悄灬

一、视图介绍

　　视图（View）：是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自，定义视图时查询使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询的结果。
 
二、创建视图

　　2.1 语法

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

复制代码

　　2.2 示例

1. create or replace view emp_view1 as select id,name,age from emp where id < 10;

复制代码

　　　　说明1：create or replace view 创建或者替换一个视图
　　　　说明2：emp_view1 视图名
　　　　说明3：as 关键字，as后面是视图的数据来源语句

　　　　说明4：select id,name,age from emp where id  show create view emp_view1;+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| View      | Create View                                                                                                                                                                                             | character_set_client | collation_connection |+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| emp_view1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `emp_view1` AS select `emp`.`id` AS `id`,`emp`.`name` AS `name`,`emp`.`age` AS `age` from `emp` where (`emp`.`id` < 10) | utf8mb4              | utf8mb4_0900_ai_ci   |+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+1 row in set (0.00 sec)[/code] 
　　3.2 查看视图数据

1. show create view 视图名称;

复制代码

　　　　说明1：...是查询条件，视图是一张虚拟的表，完全可以使用表的语法

1. mysql> <strong>show create view emp_view1;
2. </strong>+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
3. | View      | Create View                                                                                                                                                                                             | character_set_client | collation_connection |
4. +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
5. | emp_view1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `emp_view1` AS select `emp`.`id` AS `id`,`emp`.`name` AS `name`,`emp`.`age` AS `age` from `emp` where (`emp`.`id` < 10) | utf8mb4              | utf8mb4_0900_ai_ci   |
6. +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
7. 1 row in set (0.00 sec)

复制代码

　　　　说明2：完全可以将视图当做一张表来使用
 
四、修改视图

　　4.1 方式一

1. select * from 视图名称 ...;

复制代码

1. mysql> <strong>select * from emp_view1;
2. </strong>+----+--------+------+
3. | id | name   | age  |
4. +----+--------+------+
5. |  1 | 张三   |   43 |
6. |  2 | 李四   |   38 |
7. |  3 | 问问   |   22 |
8. |  4 | 芳芳   |   24 |
9. |  5 | 珊珊   |   22 |
10. |  6 | 娜娜   |   25 |
11. |  7 | 咔咔   |   25 |
12. |  8 | 静静   |   27 |
13. +----+--------+------+
14. 8 rows in set (0.00 sec)

复制代码

　　　　说明1：原来的 emp_view1 里面没有 job 字段，现在通过 or replace 的操作，新增加了 job 字段，达到了修改的效果
 
　　4.2 方式二

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

复制代码

1. mysql> <strong>create or replace view emp_view1 as select id,name,age, job from emp where id < 10</strong><strong>;</strong>
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> select * from emp_view1;
5. +----+--------+------+--------------+
6. | id | name   | age  | job          |
7. +----+--------+------+--------------+
8. |  1 | 张三   |   43 | 董事长       |
9. |  2 | 李四   |   38 | 项目经理     |
10. |  3 | 问问   |   22 | 开发         |
11. |  4 | 芳芳   |   24 | 开发         |
12. |  5 | 珊珊   |   22 | 开发         |
13. |  6 | 娜娜   |   25 | 财务         |
14. |  7 | 咔咔   |   25 | 出纳         |
15. |  8 | 静静   |   27 | 人事         |
16. +----+--------+------+--------------+
17. 8 rows in set (0.00 sec)

复制代码

　　　　说明1：通过上面的修改，将age字段的数据，删除了。
 
五、删除视图

　　5.1 语法

1. alter view 视图名称[(列名列表)]  as select语句 [with [cascaded | local] check option]

复制代码

1. mysql> <strong>alter view emp_view1 as select id, name, job from emp where id <10</strong><strong>;</strong>
2. Query OK, 0 rows affected (0.01 sec)
4. mysql> select * from emp_view1;
5. +----+--------+--------------+
6. | id | name   | job          |
7. +----+--------+--------------+
8. |  1 | 张三   | 董事长       |
9. |  2 | 李四   | 项目经理     |
10. |  3 | 问问   | 开发         |
11. |  4 | 芳芳   | 开发         |
12. |  5 | 珊珊   | 开发         |
13. |  6 | 娜娜   | 财务         |
14. |  7 | 咔咔   | 出纳         |
15. |  8 | 静静   | 人事         |
16. +----+--------+--------------+
17. 8 rows in set (0.00 sec)

复制代码

 
六、检查选项

　　视图是一张虚拟的表，除了创建和查询之后还可以往里面添加数据，重新创建视图 emp_view1

1. drop view [if exists] 视图名称 [,视图名称] ...

复制代码

　　案例1：往视图中insert 插入数据

1. mysql> <strong>drop view</strong><strong> emp_view1;</strong>
2. Query OK, 0 rows affected (0.00 sec)

复制代码

　　说明1：插入id = 9 的数据，因为视图本身不存储数据，所以通过视图添加的数据会被添加到原始数据表中。
　　
　　案例2：再次通过视图添加一个id=15的数据，验证是否成功

1. mysql> <strong>create or replace view emp_view1 as select id,name,age from emp where id < 10</strong><strong>;</strong>
2. Query OK, 0 rows affected (0.01 sec)
4. mysql> select * from emp_view1;
5. +----+--------+------+
6. | id | name   | age  |
7. +----+--------+------+
8. |  1 | 张三   |   43 |
9. |  2 | 李四   |   38 |
10. |  3 | 问问   |   22 |
11. |  4 | 芳芳   |   24 |
12. |  5 | 珊珊   |   22 |
13. |  6 | 娜娜   |   25 |
14. |  7 | 咔咔   |   25 |
15. |  8 | 静静   |   27 |
16. +----+--------+------+
17. 8 rows in set (0.00 sec)

复制代码

　　　　说明1：十五号数据添加成功，在emp的原始数据表中添加成功
　　　　说明2：但是emp_view1中未查询到15号数据，说明数据未在视图中

　　　　说明3：因为创建视图的时候有限制id create or replace view tb_user_view1 as select id,name, password from tb_user where id < 20;Query OK, 0 rows affected (0.01 sec)[/code]　　　　说明1：tb_user_view1的视图条件是 id < 20
　　　　说明2：创建视图 tb_user_view1 的时候并没有开启选项检查
　　　　
　　　　2、基于 tb_user_view1 视图创建 tb_user_view2 视图

1. mysql> <strong>insert into emp_view1 values(9, "九号",30</strong><strong>);</strong>
2. Query OK, 1 row affected (0.00 sec)
4. mysql> <strong>select * from emp;
5. </strong>+----+--------+------+--------------+--------+------------+-----------+---------+
6. | id | name   | age  | job          | salary | entrydate  | managerid | dept_id |
7. +----+--------+------+--------------+--------+------------+-----------+---------+
8. |  1 | 张三   |   43 | 董事长       |  48000 | 2017-07-20 |      NULL |       5 |
9. |  2 | 李四   |   38 | 项目经理     |  23900 | 2016-08-20 |         1 |       1 |
10. |  3 | 问问   |   22 | 开发         |  18000 | 2022-07-20 |         2 |       1 |
11. |  4 | 芳芳   |   24 | 开发         |  21000 | 2019-08-18 |         2 |       1 |
12. |  5 | 珊珊   |   22 | 开发         |  15000 | 2021-04-10 |         3 |       1 |
13. |  6 | 娜娜   |   25 | 财务         |  24000 | 2023-07-16 |         1 |       3 |
14. |  7 | 咔咔   |   25 | 出纳         |   8000 | 2021-07-10 |         6 |       3 |
15. |  8 | 静静   |   27 | 人事         |   5000 | 2021-07-11 |         1 |    NULL |
16. |  9 | 九号   |   30 | NULL         |   NULL | NULL       |      NULL |    NULL |
17. +----+--------+------+--------------+--------+------------+-----------+---------+
18. 9 rows in set (0.00 sec)
20. mysql> <strong>select * from emp_view1;
21. </strong>+----+--------+------+
22. | id | name   | age  |
23. +----+--------+------+
24. |  1 | 张三   |   43 |
25. |  2 | 李四   |   38 |
26. |  3 | 问问   |   22 |
27. |  4 | 芳芳   |   24 |
28. |  5 | 珊珊   |   22 |
29. |  6 | 娜娜   |   25 |
30. |  7 | 咔咔   |   25 |
31. |  8 | 静静   |   27 |
32. |  9 | 九号   |   30 |
33. +----+--------+------+
34. 9 rows in set (0.00 sec)

复制代码

　　　　说明3：视图 tb_user_view2 的条件是id > 10
　　　　说明4：视图 tb_user_view2 创建时同样未开启检查选项
 
　　　　3、向视图 tb_user_view2 中插入数据

1. mysql> <strong>insert into emp_view1 values(15, "十五 号",15</strong><strong>);</strong>
2. Query OK, 1 row affected (0.00 sec)
4. mysql> select * from emp;
5. +----+-----------+------+--------------+--------+------------+-----------+---------+
6. | id | name      | age  | job          | salary | entrydate  | managerid | dept_id |
7. +----+-----------+------+--------------+--------+------------+-----------+---------+
8. |  1 | 张三      |   43 | 董事长       |  48000 | 2017-07-20 |      NULL |       5 |
9. |  2 | 李四      |   38 | 项目经理     |  23900 | 2016-08-20 |         1 |       1 |
10. |  3 | 问问      |   22 | 开发         |  18000 | 2022-07-20 |         2 |       1 |
11. |  4 | 芳芳      |   24 | 开发         |  21000 | 2019-08-18 |         2 |       1 |
12. |  5 | 珊珊      |   22 | 开发         |  15000 | 2021-04-10 |         3 |       1 |
13. |  6 | 娜娜      |   25 | 财务         |  24000 | 2023-07-16 |         1 |       3 |
14. |  7 | 咔咔      |   25 | 出纳         |   8000 | 2021-07-10 |         6 |       3 |
15. |  8 | 静静      |   27 | 人事         |   5000 | 2021-07-11 |         1 |    NULL |
16. |  9 | 九号      |   30 | NULL         |   NULL | NULL       |      NULL |    NULL |
17. | 15 | 十五号    |  315 | NULL         |   NULL | NULL       |      NULL |    NULL |
18. +----+-----------+------+--------------+--------+------------+-----------+---------+
19. 10 rows in set (0.00 sec)
21. mysql> <strong>select * from emp_view1;
22. </strong>+----+--------+------+
23. | id | name   | age  |
24. +----+--------+------+
25. |  1 | 张三   |   43 |
26. |  2 | 李四   |   38 |
27. |  3 | 问问   |   22 |
28. |  4 | 芳芳   |   24 |
29. |  5 | 珊珊   |   22 |
30. |  6 | 娜娜   |   25 |
31. |  7 | 咔咔   |   25 |
32. |  8 | 静静   |   27 |
33. |  9 | 九号   |   30 |
34. +----+--------+------+
35. 9 rows in set (0.00 sec)

复制代码

　　　　说明5：插入id=11的数据，其id满足tb_user_view1 的 id10 的要求
　　　　说明6：插入id=26的数据，其id不满足 tb_user_view1 的 id10的要求，但是 tb_user_view1 和tb_user_view2 都未开启检查选项，所以仍然能插入成功。
 
　　　　4、基于 tb_user_view1 视图创建 tb_user_view3,并创建检查选项

1. mysql> <strong>create or replace view tb_user_view1 as select id,name, password from tb_user where id < 20</strong><strong>;</strong>
2. Query OK, 0 rows affected (0.01 sec)

复制代码

　　　　说明7：视图 tb_user_view3 的条件是 id > 10;
　　　　说明8：视图 tb_user_view3 开启了cascaded 的检查选项
 
　　　　5、向视图 tb_user_view3 中插入测试数据

1. mysql> <strong>create or replace view tb_user_view2 as select id,name, password from tb_user_view1 where id > 10</strong><strong>;</strong>
2. Query OK, 0 rows affected (0.01 sec)

复制代码

　　　　说明9：id=13 的数据，满足 tb_user_view1 的视图要求，并且也满足视图 tb_user_view3 的视图要求，所以添加成功

　　　　说明10：id = 24 的数据，满足 tb_user_view3 的id>10的但求，但是并不满足tb_user_view1 id create or replace view tb_user_view_local1 as select id, name, password from tb_user where id  10 with local check option;Query OK, 0 rows affected (0.01 sec)　　[/code]　　　　说明1：tb_user_view_local2 的条件是 id > 10
　　　　说明2：tb_user_view_local2 开启了检查选项
　　
　　8.3 基于视图 tb_user_view_local2 做数据测试

1. mysql> <strong>insert into tb_user_view2 values (11</strong><strong>, "kkk", "kk");</strong>
2. Query OK, 1 row affected (0.00 sec)
4. mysql> <strong>insert into tb_user_view2 values (26</strong><strong>, "zzz", "zz");</strong>
5. Query OK, 1 row affected (0.00 sec)

复制代码

　　　　说明3：id=14的数据，满足tb_user_view_local1 的要求id < 20, 并且满足tb_user_view_local2 的id>10的要求，所以插入成功
　　　　说明4：id=24的数据，不满足 tb_user_view_local1 的要求 id < 20, 但是满足 tb_user_view_local2 的 id>10的要求，因为local会向上级联依赖的视图，但是并不会强制给未添加检查的视图添加检查选项，所以在添加数据执行到 tb_user_view_loacl1 的时候，虽然不满足情况，但是 tb_user_view_loacl1 也没有设置检查选项，所以该插入语句依然能插入成功，
　
　　8.4 基于tb_user表创建视图tb_user_view_local3 视图，并设置检查选项

1. mysql> <strong>create or replace view tb_user_view3 as select id,name, password from tb_user_view1 where id > 10 with cascaded check option</strong><strong>;</strong>
2. Query OK, 0 rows affected (0.00 sec)

复制代码

　　　　说明6：tb_user_view_local4 的条件是id > 10, 并且开启了检查选项
 
　　8.6 基于视图 tb_user_view_local4 做数据测试

1. mysql> <strong>insert into tb_user_view3 values (13</strong><strong>, "nnn", "nn");</strong>
2. Query OK, 1 row affected (0.00 sec)
4. mysql> <strong>insert into tb_user_view3 values (24</strong><strong>, "yyy", "yy");</strong>
5. ERROR 1369 (HY000): CHECK OPTION failed 'mysql_test.tb_user_view3'

复制代码

　　　　说明7：id =15 的数据满足 tb_user_view_local4的要求，同时也满足 tb_user_view_local3的要求，所以插入成功。
　　　　说明8：id=23 的数据满足 tb_user_view_local4的要求，但是不满足 tb_user_view_loacal3的要求，又因为tb_user_view_local3开启了检查选项，所以插入不通过
　　
　　8.7 cascaded 和 local 对比

• 
  
  
  
  • caseaded和local都会向上递归检查其级联的依赖视图的条件个检查选项
    
  • caseaded 如果级联的依赖视图没有开启检查选项而其本身又开启了检查选项，则其级联的依赖视图也会被默认自动开启检查选项。
    
  • local 如果级联的依赖视图没有开始检查选项而其本身又开启了检查选项，则保持其级联的依赖视图原始的检查选项状态，即原来开了就检查，原来没开就不检查。   
    
  
  

 
九、视图更新的条件

　　要使视图可更新，视图中的行与基础行之间必须存在一对一的关系，如果视图包含一下任何一项，则该视图不可更新
　　　　1、聚合函数或窗口函数：sum()、min()、max()、count()等
　　　　2、distinct
　　　　3、group by
　　　　4、having
　　　　5、union 或者 union all
 
十、视图的作用

　　1、简单

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

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

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

来源:https://www.cnblogs.com/Se7eN-HOU/p/17593461.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！