Mysql高级6-视图

一、视图介绍

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

 

二、创建视图

  2.1 语法

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

  2.2 示例

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 <10;是将id小于10的,emp表中的数据的id,name,age字段的数据作为视图的源数据。

 

三、查询视图

  3.1 查看创建视图语句

show create view 视图名称;

    示例

mysql> 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)

 

  3.2 查看视图数据

select * from 视图名称 ...;

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

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

    说明2:完全可以将视图当做一张表来使用

 

四、修改视图

  4.1 方式一

create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
mysql> create or replace view emp_view1 as select id,name,age, job from emp where id < 10;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp_view1;
+----+--------+------+--------------+
| id | name   | age  | job          |
+----+--------+------+--------------+
|  1 | 张三   |   43 | 董事长       |
|  2 | 李四   |   38 | 项目经理     |
|  3 | 问问   |   22 | 开发         |
|  4 | 芳芳   |   24 | 开发         |
|  5 | 珊珊   |   22 | 开发         |
|  6 | 娜娜   |   25 | 财务         |
|  7 | 咔咔   |   25 | 出纳         |
|  8 | 静静   |   27 | 人事         |
+----+--------+------+--------------+
8 rows in set (0.00 sec)

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

 

  4.2 方式二

alter view 视图名称[(列名列表)]  as select语句 [with [cascaded | local] check option]
mysql> alter view emp_view1 as select id, name, job from emp where id <10;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from emp_view1;
+----+--------+--------------+
| id | name   | job          |
+----+--------+--------------+
|  1 | 张三   | 董事长       |
|  2 | 李四   | 项目经理     |
|  3 | 问问   | 开发         |
|  4 | 芳芳   | 开发         |
|  5 | 珊珊   | 开发         |
|  6 | 娜娜   | 财务         |
|  7 | 咔咔   | 出纳         |
|  8 | 静静   | 人事         |
+----+--------+--------------+
8 rows in set (0.00 sec)

    说明1:通过上面的修改,将age字段的数据,删除了。

 

五、删除视图

  5.1 语法

drop view [if exists] 视图名称 [,视图名称] ...
mysql> drop view emp_view1;
Query OK, 0 rows affected (0.00 sec)

 

六、检查选项

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

mysql> create or replace view emp_view1 as select id,name,age from emp where id < 10;
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> insert into emp_view1 values(9, "九号",30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+----+--------+------+--------------+--------+------------+-----------+---------+
| id | name   | age  | job          | salary | entrydate  | managerid | dept_id |
+----+--------+------+--------------+--------+------------+-----------+---------+
|  1 | 张三   |   43 | 董事长       |  48000 | 2017-07-20 |      NULL |       5 |
|  2 | 李四   |   38 | 项目经理     |  23900 | 2016-08-20 |         1 |       1 |
|  3 | 问问   |   22 | 开发         |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳   |   24 | 开发         |  21000 | 2019-08-18 |         2 |       1 |
|  5 | 珊珊   |   22 | 开发         |  15000 | 2021-04-10 |         3 |       1 |
|  6 | 娜娜   |   25 | 财务         |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔   |   25 | 出纳         |   8000 | 2021-07-10 |         6 |       3 |
|  8 | 静静   |   27 | 人事         |   5000 | 2021-07-11 |         1 |    NULL |
|  9 | 九号   |   30 | NULL         |   NULL | NULL       |      NULL |    NULL |
+----+--------+------+--------------+--------+------------+-----------+---------+
9 rows in set (0.00 sec)

mysql> select * from emp_view1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   43 |
|  2 | 李四   |   38 |
|  3 | 问问   |   22 |
|  4 | 芳芳   |   24 |
|  5 | 珊珊   |   22 |
|  6 | 娜娜   |   25 |
|  7 | 咔咔   |   25 |
|  8 | 静静   |   27 |
|  9 | 九号   |   30 |
+----+--------+------+
9 rows in set (0.00 sec)

  说明1:插入id = 9 的数据,因为视图本身不存储数据,所以通过视图添加的数据会被添加到原始数据表中。

  

  案例2:再次通过视图添加一个id=15的数据,验证是否成功

mysql> insert into emp_view1 values(15, "十五 号",15);
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+----+-----------+------+--------------+--------+------------+-----------+---------+
| id | name      | age  | job          | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------------+--------+------------+-----------+---------+
|  1 | 张三      |   43 | 董事长       |  48000 | 2017-07-20 |      NULL |       5 |
|  2 | 李四      |   38 | 项目经理     |  23900 | 2016-08-20 |         1 |       1 |
|  3 | 问问      |   22 | 开发         |  18000 | 2022-07-20 |         2 |       1 |
|  4 | 芳芳      |   24 | 开发         |  21000 | 2019-08-18 |         2 |       1 |
|  5 | 珊珊      |   22 | 开发         |  15000 | 2021-04-10 |         3 |       1 |
|  6 | 娜娜      |   25 | 财务         |  24000 | 2023-07-16 |         1 |       3 |
|  7 | 咔咔      |   25 | 出纳         |   8000 | 2021-07-10 |         6 |       3 |
|  8 | 静静      |   27 | 人事         |   5000 | 2021-07-11 |         1 |    NULL |
|  9 | 九号      |   30 | NULL         |   NULL | NULL       |      NULL |    NULL |
| 15 | 十五号    |  315 | NULL         |   NULL | NULL       |      NULL |    NULL |
+----+-----------+------+--------------+--------+------------+-----------+---------+
10 rows in set (0.00 sec)

mysql> select * from emp_view1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   43 |
|  2 | 李四   |   38 |
|  3 | 问问   |   22 |
|  4 | 芳芳   |   24 |
|  5 | 珊珊   |   22 |
|  6 | 娜娜   |   25 |
|  7 | 咔咔   |   25 |
|  8 | 静静   |   27 |
|  9 | 九号   |   30 |
+----+--------+------+
9 rows in set (0.00 sec)

    说明1:十五号数据添加成功,在emp的原始数据表中添加成功

    说明2:但是emp_view1中未查询到15号数据,说明数据未在视图中

    说明3:因为创建视图的时候有限制id<10,所以id=15的数据能在原始表中添加成功,但是并未添加是视图中

    说明4:其中这种视图操作和视图创建的条件已经相违背了,这个时候我们就可以使用视图的检查选项来规避这种项目

 

七、cascaded级联检查

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

  cascaded:级联检查选项,即如果我们创建的视图,依赖于其他视图,则在我们做本视图的检查之外,还要向上继续检查依赖视图的检查项,如果依赖视图没有开启检查选项,则会在做级联是自动开启其依赖视图的检查选项。

  案例1:cascaded的级联使用

    1、基于tb_user表创建视图tb_user_view1

mysql> 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)

    说明1:tb_user_view1的视图条件是 id < 20

    说明2:创建视图 tb_user_view1 的时候并没有开启选项检查

    

    2、基于 tb_user_view1 视图创建 tb_user_view2 视图

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

    说明3:视图 tb_user_view2 的条件是id > 10

    说明4:视图 tb_user_view2 创建时同样未开启检查选项

 

    3、向视图 tb_user_view2 中插入数据

mysql> insert into tb_user_view2 values (11, "kkk", "kk");
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_user_view2 values (26, "zzz", "zz");
Query OK, 1 row affected (0.00 sec)

    说明5:插入id=11的数据,其id满足tb_user_view1 的 id<20 和 tb_user_view2 的 id>10 的要求

    说明6:插入id=26的数据,其id不满足 tb_user_view1 的 id<20的要求,但是满足 tb_user_view2 的id >10的要求,但是 tb_user_view1 和tb_user_view2 都未开启检查选项,所以仍然能插入成功。

 

    4、基于 tb_user_view1 视图创建 tb_user_view3,并创建检查选项

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

    说明7:视图 tb_user_view3 的条件是 id > 10;

    说明8:视图 tb_user_view3 开启了cascaded 的检查选项

 

    5、向视图 tb_user_view3 中插入测试数据

mysql> insert into tb_user_view3 values (13, "nnn", "nn");
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_user_view3 values (24, "yyy", "yy");
ERROR 1369 (HY000): CHECK OPTION failed 'mysql_test.tb_user_view3'

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

    说明10:id = 24 的数据,满足 tb_user_view3 的id>10的但求,但是并不满足tb_user_view1 id<20的要求,所以插入失败。虽然tb_user_view 并没有开启检查选项,但是tb_user_view3是依赖tb_user_view1的,所以这种情况下,被级联的视图 tb_user_view1 就会默认被开启视图检查。

 

八、local级联检查

  8.1、基于tb_user表创建视图tb_user_view_local1 视图

mysql> create or replace view tb_user_view_local1 as select id, name, password from tb_user where id <20;
Query OK, 0 rows affected (0.00 sec)

    说明1:tb_user_view_loacl1 的条件是 id<20;

    说明2:tb_user_view_local1 未设置检查选项

  

  8.2 基于tb_user_view_local1 创建 tb_user_view_local2 视图

mysql> create or replace view tb_user_view_local2 as select id, name, password from tb_user_view_local1 where id > 10 with local check option;
Query OK, 0 rows affected (0.01 sec)  

    说明1:tb_user_view_local2 的条件是 id > 10

    说明2:tb_user_view_local2 开启了检查选项

  

  8.3 基于视图 tb_user_view_local2 做数据测试

mysql> insert into tb_user_view_local2 values (14, "ooo", "oo");
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_user_view_local2 values (24, "xxx", "xx");
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 视图,并设置检查选项

mysql> create or replace view tb_user_view_local3 as select id, name, password from tb_user where id <20 with cascaded check option;
Query OK, 0 rows affected (0.00 sec)

    说明5:tb_user_view_local3 的条件是id <20, 并且开启了检查选项

  

  8.5 基于tb_user_view_local3 创建 tb_user_view_local4 视图

mysql> create or replace view tb_user_view_local4  as select id,name, password from tb_user_view_local3 where id > 10 with cascaded check option;
Query OK, 0 rows affected (0.00 sec)

    说明6:tb_user_view_local4 的条件是id > 10, 并且开启了检查选项

 

  8.6 基于视图 tb_user_view_local4 做数据测试

mysql> insert into tb_user_view_local4 values (15, "ppp", "pp");
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_user_view_local4 values (23, "www", "ww");
ERROR 1369 (HY000): CHECK OPTION failed 'mysql_test.tb_user_view_local4'

    说明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、数据独立

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

 

热门相关:流鱼无恙   新无主之花   裙上之臣   不科学御兽   大妆