阅读整理自《MySQL 必知必会》- 朱晓峰,详细内容请登录 极客时间 官网购买专栏。
视图是一种 虚拟表,可以把一段查询语句作为视图存储在数据库中,在需要的时候,可以把视图看做一个表,对里面的数据进行查询。
视图没有实际存储数据,还避免了数据存储过程中可能产生的冗余,提高了存储的效率。
视图创建
create [or replace]
view 视图名称 [(字段列表)]
as 查询语句
数据准备:
mysql> select * from test.trans;
+---------+------------+---------------+------------+---------------------+
| transno | itemnumber | salesquantity | salesvalue | transdate |
+---------+------------+---------------+------------+---------------------+
| 3451 | 1 | 1 | 89 | 2020-12-01 00:00:00 |
| 3451 | 2 | 1 | 5 | 2020-12-01 00:00:00 |
| 3452 | 3 | 2 | 20 | 2020-12-02 00:00:00 |
+---------+------------+---------------+------------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from test.goodsmaster;
+------------+---------+-----------+------------+
| itemnumber | barcode | goodsname | salesprice |
+------------+---------+-----------+------------+
| 1 | 001 | 书 | 89 |
| 2 | 002 | 笔 | 5 |
| 3 | 003 | 胶水 | 10 |
+------------+---------+-----------+------------+
3 rows in set (0.00 sec)
mysql> select * from test.inventoryhist;
+------------+-------------+---------------------+
| itemnumber | invquantity | invdate |
+------------+-------------+---------------------+
| 1 | 100 | 2020-12-01 00:00:00 |
| 2 | 99 | 2020-12-01 00:00:00 |
| 3 | 88 | 2020-12-01 00:00:00 |
| 1 | 149 | 2020-12-02 00:00:00 |
| 2 | 105 | 2020-12-02 00:00:00 |
| 3 | 200 | 2020-12-02 00:00:00 |
+------------+-------------+---------------------+
在不使用视图的情况下,通过对销售流水表和商品信息表进行关联查询,得到每天商品销售统计的结果,包括销售日期、商品名称、每天销售数量的合计和每天销售金额的合计,如下所示:
mysql> select a.transdate, a.itemnumber, b.goodsname, sum(a.salesquantity) as quantity, sum(a.salesvalue) as salesvalue
-> from test.trans as a
-> left join test.goodsmaster as b on (b.itemnumber = a.itemnumber)
-> group by a.transdate, b.goodsname;
+---------------------+------------+-----------+----------+------------+
| transdate | itemnumber | goodsname | quantity | salesvalue |
+---------------------+------------+-----------+----------+------------+
| 2020-12-01 00:00:00 | 1 | 书 | 1 | 89 |
| 2020-12-01 00:00:00 | 2 | 笔 | 1 | 5 |
| 2020-12-02 00:00:00 | 3 | 胶水 | 2 | 20 |
+---------------------+------------+-----------+----------+------------+
在实际项目中发现,每日商品销售查询使用的频次很高,而且经常需要以这个查询的结果为基础,进行更进一步的统计。
举例,超市经营者要查一下“每天商品的销售数量和当天库存数量的对比”,如果用一个 SQL 语句查询,就会比较复杂。
- 子查询:就是嵌套在另一个查询中的查询
- 派生表:在查询中把子查询的结果作为一个表来使用,这个表就是派生表
select a.transdate, a.itemnumber, a.goodsname, a.quantity as quantity, b.invquantity as invquantity
from
(select a.transdate, a.itemnumber, b.goodsname,
sum(a.salesquantity) as quantity,
sum(a.salesvalue) as salesvalue
from test.trans as a
left join test.goodsmaster as b on (b.itemnumber = a.itemnumber)
group by a.transdate, b.goodsname) as a -- 派生表,与库存表进行连接
left join test.inventoryhist as b on (b.itemnumber = a.itemnumber and b.invdate = a.transdate);
+---------------------+------------+-----------+----------+-------------+
| transdate | itemnumber | goodsname | quantity | invquantity |
+---------------------+------------+-----------+----------+-------------+
| 2020-12-01 00:00:00 | 1 | 书 | 1 | 100 |
| 2020-12-01 00:00:00 | 2 | 笔 | 1 | 99 |
| 2020-12-02 00:00:00 | 3 | 胶水 | 2 | 200 |
+---------------------+------------+-----------+----------+-------------+
使用视图查询
-
创建视图
create view test.trans_goodsmaster as -- 创建视图 select a.transdate, a.itemnumber, b.goodsname, sum(a.salesquantity) as quantity, sum(a.salesvalue) as salesvalue from test.trans as a left join test.goodsmaster as b on (b.itemnumber = a.itemnumber) group by a.transdate, a.itemnumber;
mysql> select * from test.trans_goodsmaster; +---------------------+------------+-----------+----------+------------+ | transdate | itemnumber | goodsname | quantity | salesvalue | +---------------------+------------+-----------+----------+------------+ | 2020-12-01 00:00:00 | 1 | 书 | 1 | 89 | | 2020-12-01 00:00:00 | 2 | 笔 | 1 | 5 | | 2020-12-02 00:00:00 | 3 | 胶水 | 2 | 20 | +---------------------+------------+-----------+----------+------------+
-
使用视图查询
select a.transdate, a.itemnumber, a.goodsname, a.quantity as quantity, b.invquantity as invquantity from test.trans_goodsmaster as a left join test.inventoryhist as b on (b.itemnumber = a.itemnumber and b.invdate = a.transdate) group by a.transdate, a.itemnumber;
+---------------------+------------+-----------+----------+-------------+ | transdate | itemnumber | goodsname | quantity | invquantity | +---------------------+------------+-----------+----------+-------------+ | 2020-12-01 00:00:00 | 1 | 书 | 1 | 100 | | 2020-12-01 00:00:00 | 2 | 笔 | 1 | 99 | | 2020-12-02 00:00:00 | 3 | 胶水 | 2 | 200 | +---------------------+------------+-----------+----------+-------------+
查询结果和使用派生表的查询结果是一样的。但是,使用视图的查询语句明显简单多了,可读性更好,也更容易维护。
操作视图和数据
修改视图:
alter view 视图名
as 查询语句;
查看视图:
describe 视图名;
删除视图:
drop view 视图名;
插入数据
mysql> create view test.view_goodsmaster as
-> select itemnumber,barcode,goodsname,specification,saleprice
-> from demo.goodsmaster;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test.view_goodsmaster (itemnumber, barcode, goodsname, saleprice)
-> values (5, '0005', 'test', 100);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.view_goodsmaster;
+------------+---------+-----------+---------------+-----------+
| itemnumber | barcode | goodsname | specification | saleprice |
+------------+---------+-----------+---------------+-----------+
| 1 | 0001 | 书 | 16开 | 89.00 |
| 2 | 0002 | 笔 | NULL | 5.00 |
| 5 | 0005 | test | NULL | 100.00 |
+------------+---------+-----------+---------------+-----------+
mysql> select * from demo.goodsmaster; -- 查看实际数据表
+------------+---------+-----------+---------------+------+-----------+
| itemnumber | barcode | goodsname | specification | unit | saleprice |
+------------+---------+-----------+---------------+------+-----------+
| 1 | 0001 | 书 | 16开 | 本 | 89.00 |
| 2 | 0002 | 笔 | NULL | 支 | 5.00 |
| 5 | 0005 | test | NULL | NULL | 100.00 |
+------------+---------+-----------+---------------+------+-----------+
要注意的是,只有视图中的字段跟实际数据表中的字段完全一样,MySQL 才允许通过视图插入数据。
删除数据
mysql> delete from test.view_goodsmaster
-> where itemnumber = 5;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.view_goodsmaster;
+------------+---------+-----------+---------------+-----------+
| itemnumber | barcode | goodsname | specification | saleprice |
+------------+---------+-----------+---------------+-----------+
| 1 | 0001 | 书 | 16开 | 89.00 |
| 2 | 0002 | 笔 | NULL | 5.00 |
+------------+---------+-----------+---------------+-----------+
3 rows in set (0.00 sec)
mysql> select * from demo.goodsmaster;
+------------+---------+-----------+---------------+------+-----------+
| itemnumber | barcode | goodsname | specification | unit | saleprice |
+------------+---------+-----------+---------------+------+-----------+
| 1 | 0001 | 书 | 16开 | 本 | 89.00 |
| 2 | 0002 | 笔 | NULL | 支 | 5.00 |
+------------+---------+-----------+---------------+------+-----------+
2 rows in set (0.00 sec)
视图和原来的数据表都被删除了。
修改数据
mysql> update test.view_goodsmaster
-> set saleprice = 90 where itemnumber = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test.view_goodsmaster;
+------------+---------+-----------+---------------+-----------+
| itemnumber | barcode | goodsname | specification | saleprice |
+------------+---------+-----------+---------------+-----------+
| 1 | 0001 | 书 | 16开 | 90.00 |
| 2 | 0002 | 笔 | NULL | 5.00 |
| 6 | 0006 | test | NULL | 100.00 |
+------------+---------+-----------+---------------+-----------+
3 rows in set (0.00 sec)
mysql> select * from demo.goodsmaster;
+------------+---------+-----------+---------------+------+-----------+
| itemnumber | barcode | goodsname | specification | unit | saleprice |
+------------+---------+-----------+---------------+------+-----------+
| 1 | 0001 | 书 | 16开 | 本 | 90.00 |
| 2 | 0002 | 笔 | NULL | 支 | 5.00 |
| 6 | 0006 | test | NULL | NULL | 100.00 |
+------------+---------+-----------+---------------+------+-----------+
视图和原来的数据表都已经改过来了。
不建议对视图的数据进行更新操作,因为 MySQL 允许用比较复杂的 SQL 查询语句来创建视图(比如 SQL 查询语句中使用了分组和聚合函数,或者是 UION 和 DISTINCT 关键字),所以,要通过对这个结果集的更新来更新实际数据表,有可能不被允许,因为 MySQL 没办法精确定位实际数据表中的记录。
小结
视图优点:
- 把视图看成一张表来进行查询,把查询模块化、简单化,提高了开发和维护的效率。
- 视图跟实际数据表不一样,它存储的是查询语句。视图本身不存储数据,不占用数据存储的资源。
- 视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。用户不需要查询数据表,可以直接通过视图获取数据表中的信息。
- 视图的数据结构相对独立,即便实际数据表的结构发生变化,也可以通过修改定义视图的查询语句,让查询结果集里的字段保持不变。
缺点:
- 在创建视图、简化查询的同时,也要考虑到视图太多而导致的数据库维护成本的问题。
- 视图不是越多越好,特别是嵌套的视图(就是在视图的基础上创建视图),不建议你使用,因为逻辑复杂,可读性不好,容易变成系统的潜在隐患。