MySQL必知必会14视图：简化查询

阅读整理自《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 |
+---------------------+------------+-----------+----------+-------------+

使用视图查询

1. 创建视图

   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 |
   +---------------------+------------+-----------+----------+------------+
   

2. 使用视图查询

   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 没办法精确定位实际数据表中的记录。

---

小结

视图优点：

• 把视图看成一张表来进行查询，把查询模块化、简单化，提高了开发和维护的效率。
• 视图跟实际数据表不一样，它存储的是查询语句。视图本身不存储数据，不占用数据存储的资源。
• 视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。用户不需要查询数据表，可以直接通过视图获取数据表中的信息。
• 视图的数据结构相对独立，即便实际数据表的结构发生变化，也可以通过修改定义视图的查询语句，让查询结果集里的字段保持不变。

缺点：

• 在创建视图、简化查询的同时，也要考虑到视图太多而导致的数据库维护成本的问题。
• 视图不是越多越好，特别是嵌套的视图（就是在视图的基础上创建视图），不建议你使用，因为逻辑复杂，可读性不好，容易变成系统的潜在隐患。