MySQL全面瓦解15:视图

时间:2024-01-27 20:14:38

概述

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

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

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

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

概念

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

使用场景

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

视图和表比较

  语法 实际中是否占用物理空间 使用
视图 create view 否,仅保存sql的逻辑 增删改查,实际上我们只使用查询
create table 是,保存了数据 增删改查

视图的作用

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

1、简单性

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

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

2、安全性

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

3、逻辑数据独立性

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

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

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

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

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

数据准备

 

表名 描述
departments 部门表
employees 员工信息表
jobs 职位信息表
locations 位置表(部门表中会用到)
job_grades 薪资等级表

创建视图

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

语法

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

参数说明:

(1)ALGORITHM:可选项,表示视图选择的算法。

(2)ViewName 视图名:表示要创建的视图名称。

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

(4)SELECT语句:表示一个完整的查询语句,将查询记录导入视图中。

(5)WITH CHECK OPTION:可选项,表示更新视图时要保证在该视图的权限范围之内。

视图的使用步骤

(1)创建视图

(2)对视图执行查询操作

对单表的操作

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

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

 

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

对多表的操作

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

 1 mysql> select * from classes;
 2 +---------+-----------+
 3 | classid | classname |
 4 +---------+-----------+
 5 |       1 | 初三一班  |
 6 |       2 | 初三二班  |
 7 |       3 | 初三三班  |
 8 +---------+-----------+
 9 3 rows in set
10 
11 mysql> select * from students;
12 +-----------+-------------+-------+---------+
13 | studentid | studentname | score | classid |
14 +-----------+-------------+-------+---------+
15 |         1 | brand       | 97.5  |       1 |
16 |         2 | helen       | 96.5  |       1 |
17 |         3 | lyn         | 96    |       1 |
18 |         4 | sol         | 97    |       1 |
19 |         7 | b1          | 81    |       2 |
20 |         8 | b2          | 82    |       2 |
21 |        13 | c1          | 71    |       3 |
22 |        14 | c2          | 72.5  |       3 |
23 |        19 | lala        | 51    |       0 |
24 +-----------+-------------+-------+---------+
25 9 rows in set 
 1 mysql> create view view2 as select a.classname as '班级名称',count(b.studentid) as '总人数',sum(b.score) as '总分',avg(b.score) as '平均分'
 2 from classes a inner join students b on a.classid = b.classid 
 3 group by a.classid,a.classname;
 4 Query OK, 0 rows affected
 5 
 6 mysql> select * from view2;
 7 +----------+--------+--------+-----------+
 8 | 班级名称 | 总人数 | 总分   | 平均分    |
 9 +----------+--------+--------+-----------+
10 | 初三一班 |      4 | 387.00 | 96.750000 |
11 | 初三二班 |      2 | 163.00 | 81.500000 |
12 | 初三三班 |      2 | 143.50 | 71.750000 |
13 +----------+--------+--------+-----------+
14 3 rows in set

查询视图结构

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

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

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

1 mysql> show create view view2;
2 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
3 | View  | Create View                                                                                                                                                                                                                                                                                                                                    | character_set_client | collation_connection |
4 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
5 | 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      |
6 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
7 1 row in set

修改视图

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

create or replace方式

当视图存在时,修改,不存在时,创建新的视图。

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

alter方式

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

删除视图

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

语法

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

 可以删除多个视图,多个视图名称使用都好隔开。

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

更新视图数据

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

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

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

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

with check option

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

关键字类型 描述
update 有with check option,要保证update后,数据能被视图查询出来
delete 有无with check option都一样
insert 有with check option,要保证insert后,数据要被视图查询出来

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

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

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

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

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

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

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

总结

  1. 了解视图的用途及与表的区别。

  2. 掌握视图的创建、使用、修改、删除。

  3.  了解视图的优点:简单简洁(隐藏细节,输出结果)、安全性(用户只能看到表中特定行的方法)、逻辑数据独立性。