MySQL全面瓦解15：视图

爱新觉羅丶高

发布于 2022-7-27 15:48

浏览

0收藏

概述
很多时候，我们会有一些很复杂的数据库操作，比如整合用户的行为数据，那这些数据可能包含用户的餐饮、生活日用、充值消费、交通出行、通讯物流、交通出行、医疗保健、住房物业、运动健康...

基于此，我们可能要去关联很多的用户行为信息表，写一大堆的合并、统计，以及条件过滤的脚本，来输出最终的二维表，这才是用户真正需要的数据。如果把这些复杂的脚本写在业务程序中不是很合理，那有什么办法把

这些脚本细节隐藏，只显示只显示简洁的结果呢，那就要用到视图了。

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。

概念
视图是在mysql5之后出现的，是一种虚拟表，行和列的数据来自于定义视图时使用的一些表中，视图的数据是在使用视图的时候动态生成的，视图只保存了sql的逻辑，不保存查询的结果。

使用场景
多个业务场景使用到相同的查询结果，并且该查询结果比较复杂的时候，我们可以使用视图来隐藏复杂的实现细节。

视图和表比较

MySQL全面瓦解15：视图-鸿蒙开发者社区

视图的作用
对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。视图的作用归纳为如下几点。

1、简单性

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

经常需要联合查询的内容也可以指定为视图，这样就不需要每次都写大量的联表脚本。

2、安全性

视图的安全性可以防止未授权用户查看特定的行或列，使有权限用户只能看到表中特定行的方法，比如把只有某部分数据查询视图授权给某个用户。

3、逻辑数据独立性

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立。

（1）如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而使应用程序可以不动。

（2）如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。

（3）如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序可以不动。

（4）如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而使数据库可以不动。

数据准备

创建视图
MySQL中，创建视图是通过CREATE VIEW语句实现的。

语法

 CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
 VIEW ViewName[(ColumnList)]
 AS SELECT ...
 [WITH [CASCADED|LOCAL] CHECK OPTION];1.
2.
3.
4.

参数说明：

（1）ALGORITHM：可选项，表示视图选择的算法。

（2）ViewName 视图名：表示要创建的视图名称。

（3）ColumnList 属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。

（4）SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。

（5）WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。

视图的使用步骤
（1）创建视图

（2）对视图执行查询操作

对单表的操作
查询出订单表中，单笔订单额度超过100元的数据，下面依次展示创建视图和查询视图。

 mysql> select *  from t_order;
  +---------+-----+-------+--------+---------------------+------+
  | orderid | uid | uname | amount | time                | year |
  +---------+-----+-------+--------+---------------------+------+
  |      20 |   1 | brand | 91.23  | 2018-08-20 17:22:21 | 2018 |
  |      21 |   1 | brand | 87.54  | 2019-07-16 09:21:30 | 2019 |
  |      22 |   1 | brand | 166.88 | 2019-04-04 12:23:55 | 2019 |
  |      23 |   2 | helyn | 93.73  | 2019-09-15 10:11:11 | 2019 |
  |      24 |   2 | helyn | 102.32 | 2019-01-08 17:33:25 | 2019 |
 |      25 |   2 | helyn | 106.06 | 2019-12-24 12:25:25 | 2019 |
 |      26 |   2 | helyn | 73.42  | 2020-04-03 17:16:23 | 2020 |
 |      27 |   3 | sol   | 55.55  | 2019-08-05 19:16:23 | 2019 |
 |      28 |   3 | sol   | 69.96  | 2020-09-16 19:23:16 | 2020 |
 |      29 |   4 | weng  | 199.99 | 2020-06-08 19:55:06 | 2020 |
 +---------+-----+-------+--------+---------------------+------+
 10 rows in set
 
 mysql> create view view1
     -> as select * from t_order where amount >100;
 Query OK, 0 rows affected
 
 mysql> select * from view1;
 +---------+-----+-------+--------+---------------------+------+
 | orderid | uid | uname | amount | time                | year |
 +---------+-----+-------+--------+---------------------+------+
 |      22 |   1 | brand | 166.88 | 2019-04-04 12:23:55 | 2019 |
 |      24 |   2 | helyn | 102.32 | 2019-01-08 17:33:25 | 2019 |
 |      25 |   2 | helyn | 106.06 | 2019-12-24 12:25:25 | 2019 |
 |      29 |   4 | weng  | 199.99 | 2020-06-08 19:55:06 | 2020 |
 +---------+-----+-------+--------+---------------------+------+
 4 rows in set
 
 mysql>  select * from view1 where year = 2019;
 +---------+-----+-------+--------+---------------------+------+
 | orderid | uid | uname | amount | time                | year |
 +---------+-----+-------+--------+---------------------+------+
 |      22 |   1 | brand | 166.88 | 2019-04-04 12:23:55 | 2019 |
 |      24 |   2 | helyn | 102.32 | 2019-01-08 17:33:25 | 2019 |
 |      25 |   2 | helyn | 106.06 | 2019-12-24 12:25:25 | 2019 |
 +---------+-----+-------+--------+---------------------+------+
 3 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.

上面我们创建了一个视图：view1，我们需要看某个条件下的信息时，不用关心这个视图内部是什么样的，只需要查询视图就可以了，sql简单很多。

对多表的操作
有两个表：学生、班级，数据基数如下，需要创建一个视图，统计每个班级的人数和平均成绩和总成绩。

  mysql> select * from classes;
  +---------+-----------+
  | classid | classname |
  +---------+-----------+
  |       1 | 初三一班  |
  |       2 | 初三二班  |
  |       3 | 初三三班  |
  +---------+-----------+
  3 rows in set
 
 mysql> select * from students;
 +-----------+-------------+-------+---------+
 | studentid | studentname | score | classid |
 +-----------+-------------+-------+---------+
 |         1 | brand       | 97.5  |       1 |
 |         2 | helen       | 96.5  |       1 |
 |         3 | lyn         | 96    |       1 |
 |         4 | sol         | 97    |       1 |
 |         7 | b1          | 81    |       2 |
 |         8 | b2          | 82    |       2 |
 |        13 | c1          | 71    |       3 |
 |        14 | c2          | 72.5  |       3 |
 |        19 | lala        | 51    |       0 |
 +-----------+-------------+-------+---------+
 9 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

  mysql> create view view2 as select a.classname as '班级名称',count(b.studentid) as '总人数',sum(b.score) as '总分',avg(b.score) as '平均分'
  from classes a inner join students b on a.classid = b.classid 
  group by a.classid,a.classname;
  Query OK, 0 rows affected
  
  mysql> select * from view2;
  +----------+--------+--------+-----------+
  | 班级名称 | 总人数 | 总分   | 平均分    |
  +----------+--------+--------+-----------+
 | 初三一班 |      4 | 387.00 | 96.750000 |
 | 初三二班 |      2 | 163.00 | 81.500000 |
 | 初三三班 |      2 | 143.50 | 71.750000 |
 +----------+--------+--------+-----------+
 3 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

查询视图结构
使用 desc viewname 语法查看视图的结构

 mysql> desc view2;
 +----------+---------------+------+-----+---------+-------+
  | Field    | Type          | Null | Key | Default | Extra |
  +----------+---------------+------+-----+---------+-------+
  | 班级名称 | varchar(30)   | YES  |     | NULL    |       |
  | 总人数   | bigint(21)    | NO   |     | 0       |       |
  | 总分     | decimal(32,2) | YES  |     | NULL    |       |
  | 平均分   | decimal(14,6) | YES  |     | NULL    |       |
  +----------+---------------+------+-----+---------+-------+
 4 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

使用show create view语法查看视图创建信息

mysql> show create view view2;
 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 | View  | Create View                                                                                                                                                                                                                                                                                                                                    | character_set_client | collation_connection |
 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 | view2 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `a`.`classname` AS `班级名称`,count(`b`.`studentid`) AS `总人数`,sum(`b`.`score`) AS `总分`,avg(`b`.`score`) AS `平均分` from (`classes` `a` join `students` `b` on((`a`.`classid` = `b`.`classid`))) group by `a`.`classid`,`a`.`classname` | utf8                 | utf8_general_ci      |
 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 1 row in set1.
2.
3.
4.
5.
6.
7.

修改视图
修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。

create or replace方式
当视图存在时，修改，不存在时，创建新的视图。

 mysql> create or replace view view2 as select a.classname as '班级名称',count(b.studentid) as '总人数',sum(b.score) as '总分',avg(b.score) as '平均分'
  from classes a inner join students b on a.classid = b.classid 
  group by a.classid,a.classname;
  Query OK, 0 rows affected
  
  mysql> select * from view2;
  +----------+--------+--------+-----------+
  | 班级名称 | 总人数 | 总分   | 平均分    |
  +----------+--------+--------+-----------+
 | 初三一班 |      4 | 387.00 | 96.750000 |
 | 初三二班 |      2 | 163.00 | 81.500000 |
 | 初三三班 |      2 | 143.50 | 71.750000 |
 +----------+--------+--------+-----------+
 3 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

alter方式

mysql> alter view view2 as select a.classname as '班级名称',count(b.studentid) as '总人数',sum(b.score) as '总分',avg(b.score) as '平均分'
 from classes a inner join students b on a.classid = b.classid 
  group by a.classid,a.classname;
  Query OK, 0 rows affected
  
  mysql> select * from view2;
  +----------+--------+--------+-----------+
  | 班级名称 | 总人数 | 总分   | 平均分    |
  +----------+--------+--------+-----------+
 | 初三一班 |      4 | 387.00 | 96.750000 |
 | 初三二班 |      2 | 163.00 | 81.500000 |
 | 初三三班 |      2 | 143.50 | 71.750000 |
 +----------+--------+--------+-----------+
 3 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

删除视图
删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用DROP VIEW语句来删除视图。但是，用户必须拥有DROP权限。

语法

 DROP VIEW IF EXISTS vname1[,vname2][,vname3];
1.

可以删除多个视图，多个视图名称使用逗号隔开。

 mysql> DROP VIEW IF EXISTS view1,view2;
Query OK, 0 rows affected 1.
2.

更新视图数据
即更改视图中的数据，而不是更改视图中的sql逻辑。当对视图进行更新后，也会对原始表的数据进行更新。

为了防止对原始表的数据产生更新，可以为视图添加只读权限，只允许读视图，不允许对视图进行更新。一般比较少这么操作：

mysql> create view view1 as select * from classes;
Query OK, 0 rows affected
 
  mysql> select * from view1;
  +---------+-----------+
  | classid | classname |
  +---------+-----------+
  |       1 | 初三一班  |
  |       2 | 初三二班  |
 |       3 | 初三三班  |
 +---------+-----------+
 3 rows in set
 
 mysql> insert into view1 values(4,'初三四班');
 Query OK, 1 row affected
 
 mysql> update view1 set classname='初三四班' where classid=4;
 Query OK, 1 row affected
 Rows matched: 1  Changed: 1  Warnings: 0
 
 mysql> select * from view1;
 +---------+-----------+
 | classid | classname |
 +---------+-----------+
 |       1 | 初三一班  |
 |       2 | 初三二班  |
 |       3 | 初三三班  |
 |       4 | 初三四班  |
 +---------+-----------+
 4 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

视图的更新我们一般不使用，为了保护视图的某部分区间的数据不被修改，一般会使用WITH CHECK OPTION约束，甚至设置为只读视图。

with check option
视图只操作它能够查询出来的数据，对于查询不出的数据，即使基表中存在数据，也不可以通过视图来操作。针对这方面的限制有 with check option 表达式进行操作。他对delete、insert、update分别有如下约束：

MySQL全面瓦解15：视图-鸿蒙开发者社区

下面创建两个同样结果的视图，一个带with check option，一个不带。

mysql> create view v as select * from  t_order where amount>0;
  Query OK, 0 rows affected
   
  mysql> create view v_option as select * from  t_order where amount>0 with check option;
  Query OK, 0 rows affected
  
  mysql> select * from t_order;
  +---------+---------+---------+-------+
  | orderid | account | amount  | goods |
 +---------+---------+---------+-------+
 |       8 | brand   | 52.2    |     2 |
 |       9 | hen     | 1752.02 |     7 |
 |      10 | helyn   | 88.5    |     4 |
 |      11 | sol     | 1007.9  |    11 |
 |      12 | diny    | 12      |     1 |
 |      13 | weng    | 52.2    |     5 |
 |      14 | sally   | 99.71   |     9 |
 |      15 | brand1  | -99.99  |     6 |
 +---------+---------+---------+-------+
 8 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

原表有8条数据，因为条件限制，视图无法读取到最后一条数据，即只有前7条数据。那根据上面的规则，使用v_option视图进行修改，修改他查询范围之外的数据应该是不允许的，我们来试试：

mysql> update v_option set amount=-8 where orderid=12;
 1369 - CHECK OPTION failed 'test.v_option' 1.
2.

amout = -8，改完后数据会跑到视图可查询范围之外，所以被限制了。使用不限制的视图就可以了，如下：

mysql> update v set amount=-8 where orderid=12;
 Query OK, 1 row affected
 Rows matched: 1  Changed: 1  Warnings: 0
 
 mysql> select * from t_order;
  +---------+---------+---------+-------+
  | orderid | account | amount  | goods |
  +---------+---------+---------+-------+
  |       8 | brand   | 52.2    |     2 |
 |       9 | hen     | 1752.02 |     7 |
 |      10 | helyn   | 88.5    |     4 |
 |      11 | sol     | 1007.9  |    11 |
 |      12 | diny    | -8      |     1 |
 |      13 | weng    | 52.2    |     5 |
 |      14 | sally   | 99.71   |     9 |
 |      15 | brand1  | -99.99  |     6 |
 +---------+---------+---------+-------+
 8 rows in set 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

同理，delete、insert 也可以验证下。

总结

了解视图的用途及与表的区别。
掌握视图的创建、使用、修改、删除。
了解视图的优点：简单简洁（隐藏细节，输出结果）、安全性（用户只能看到表中特定行的方法）、逻辑数据独立性。

文章转载自公众号：架构与思维

分类

标签

已于2022-7-27 15:48:59修改

51CTO

51CTO博客

51CTO学堂

MySQL全面瓦解15：视图

订阅鸿蒙技术特刊，精选内容抢先看