MySQL数据库入门与实践(五)：视图与各种子查询

---

一、视图

视图究竟是什么呢？如果用一句话概述的话，就是“从 SQL 的角度来看视图就是一张表”。实际上，在 SQL 语句中并不需要区分哪些是表，哪些是视图，只需要知道在更新时它们之间存在一些不同就可以了。
我们在创建表时，会通过 INSERT 语句将数据保存到数据库之中，而数据库中的数据实际上会被保存到计算机的存储设备（通常是硬盘）中。因此，我们通过 SELECT 语句查询数据时，实际上就是从存储设备（硬盘）中读取数据，进行各种计算之后，再将结果返回给用户这样一个过程。
但是使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。实际上视图保存的是 SELECT 语句，其实也就是把 select 语句封装了起来。我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表。
视图无需保存数据，因此可以节省存储设备的容量。将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了。
而且，视图中的数据会随着原表的变化自动更新。视图归根到底就是SELECT 语句，所谓“参照视图”也就是“执行SELECT 语句”的意思，因此可以保证数据的最新状态。这也是将数据保存在表中所不具备的优势 。
创建视图需要使用 create view 语句

--使用 create view 视图名（列1，列2，.....）  as select语句
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

可以看出创建视图的语句好像和创建一个表 create 的语句差不多，都是 create table 表名（列1，列2，…）。而且长的样子也和表差不多，甚至说好像就是个表。但是需要注意的是在 create 和 select 子句之间的 as 很重要，不可省略。在创建完视图之后，我们需要使用视图：

SELECT product_type, cnt_product FROM ProductSum;

查询的结果貌似也就是一个表。
但是可以看出，该视图将根据商品种类（product_type）汇总的商品数量（cnt_product）作为结果保存了起来。这样如果需要频繁进行汇总时，就不用每次都使用 GROUP BY 和 COUNT 函数写select语句来从 Product 表中取得数据了。
Product 表中的数据更新之后，视图也会自动更新，非常灵活方便。之所以能够实现上述功能，是因为视图就是保存好的 SELECT 语句。定义视图时可以使用任何 SELECT 语句，既可以使用 WHERE、 GROUPBY、 HAVING，也可以通过 SELECT * 来指定全部列。

• 视图也可以实行嵌套，做出多层视图，但是这样会增加代码的复杂性，并且多重视图会明显降低性能。

视图的使用带来了很大的便利性，但同时视图也有一定的局限性：定义视图时不可以使用 order by 子句，为什么不能使用 ORDER BY 子句呢？这是因为视图和表一样， 数据的行都是没有顺序的。实际上，有些 DBMS 在定义视图的语句中是可以使用 ORDER BY 子句的。

• 视图和表虽然长得差不多，但是在进行更新的时候，也就是 insert into，delete, update 时却有一定的限制。需要满足一定的条件，比如
• select 语句中未使用 distinct 去重。
• from 子句中只有一张表。
• 未使用 group by 或者是 having 子句。

除了第二个条件涉及到多表的连结，别的条件都是和聚合函数相关，使用视图来保存原表的汇总结果时，是无法判断如何将视图的更改反映到原表中的。
例如，对 ProductSum 视图执行如下 INSERT 语句。

INSERT INTO ProductSum VALUES ('电器制品', 5);

好像是可以对视图增加一行，但其实不会。会报错的。这是因为视图 ProductSum是通过 GROUP BY 子句对原表进行汇总而得到的。那为什么这样就不能更新呢？原因在于，视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。我觉得下图表示的十分准确了。

那么如果满足上述条件的视图怎么更新呢？先创建一个视图：

CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS
SELECT *
FROM Product
WHERE product_type = '办公用品';

这个视图中没有 group by, distinct, having ,所以可以进行以下更新：

INSERT INTO ProductJim 
VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');

这时候可以分别查询一下原表及视图，发现均发生了改变。视图和原表需要同时更新，从而汇总得到的视图无法更新。

• 删除视图：删除视图的格式和删除表的格式相同 drop view 视图名

二、子查询

子查询和视图有点类似，甚至说只有一点点差别。子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中，也就是 select 子句的嵌套。来看一个嵌套子查询的语句：

SELECT product_type, cnt_product
FROM 
( SELECT product_type, COUNT(*) AS cnt_product
  FROM Product
  GROUP BY product_type ) AS ProductSum;

需要说明的是嵌套子查询包括内层和外层，内层也就是 from（）括号中的的，并且需要指出的是子查询需要给出名称 as productsum 就是给子查询指定的名称。子查询不具备保存性，在外层 select 语句执行完之后内层的查询就没有了，包括子查询别名也没有了。
两层查询的顺序是先执行内层的 select 查询，在执行外层的 select 查询，其实也可以看成两步查询，先查内层，再查外层。

• 多层子查询的嵌套，子查询可以实现多层嵌套，类似于函数的复合，当然啦，有很多层的子查询会使代码变得很复杂，不建议使用。

标量子查询：所谓标量子查询就是单一子查询，标量子查询则有一个特殊的限制，那就是必须而且只能返回 1 行 1列的结果，也就是返回表中某一行的某一列的值。当然，它指的是内层查询完之后显示一行且一列，也就是一个数，并不是说在两层查询完之后显示的是一个数。
由于返回的是单一的值，因此标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。
比如在 where 子句中使用标量子查询，之前有说过，where 子句中不能使用聚合函数，但是如果不使用 having 子句而使用子查询的话 ，也可以这么查询：

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

对比一下之前错误的代码如下：

SELECT product_id, product＿name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

很神奇的事情发生了，使用子查询的 SQL 会从子查询开始执行。因此，这种情况下也会先执行下述计算平均单价的子查询，子查询的结果是 2097.5，因此会用该值替换子查询的部分。这就是标量子查询的精彩之处。那么其实外层的查询也就变成了：

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > 2097.5

• 标量子查询的位置：上面可以看到标量子查询已经出现在了 where子句中，事实上，任何使用单一值的地方都可以使用标量子查询，包括 select ,where, group by, having,order by。
• 标量子查询一定是返回一个数，如果返回的是多行数，那么这时候就不是标量子查询，而是一般的子查询，这时候就谈不是使用 等于或者大于小于进行比较了。也不能用在 select 等子句中。

三、关联子查询

所谓关联子查询就是先分组在进行子查询：

SELECT product_type, product_name, sale_price
FROM Product AS P1 
WHERE sale_price > (SELECT AVG(sale_price）FROM Product AS P2 
                    WHERE P1.product_type = P2.product_type
                    GROUP BY product_type);

这串代码表示的是把两个表中商品种类相同的行连了起来。
这样我们就能选取出办公用品、衣服和厨房用具三类商品中高于该类商品的平均销售单价的商品了。
这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件。该条件的意思就是， 在同一商品种类中对各商品的销售单价和平均单价进行比较。

• 在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“< 表名 >.< 列名 >”的形式记述。

使用关联子查询进行切分的图示也和 group by对表的切割相同。
我们首先需要计算各个商品种类中商品的平均销售单价。对于某一类商品，关联子查询实际只能返回1行结果。这也是关联子查询不出错的关键。

• 关联子查询中最重要的就是 where 中的结合条件，这个结合条件联系了内外两层查询和两个表，需要注意的是这个结合条件一定需要写在内层查询之中，这其实也很好理解，我们根据执行的顺序，先执行的是外层的 from，如果继续执行结合条件，这时发现还没有执行内查询，也就谈不上结合条件。因此必须是把结合条件写在内层查询中。

---

总结

本小节着重介绍了

• 视图，这是一种将 select 语句封装起来的，和表不一样的数据格式。view定义时不可使用 order by 子句；更新的时候有一定的限制，比如 from 后面不能有两张表，select 中不能有 distinct,不能有group by 和 having 子句。
• 子查询，实现查询的嵌套，注意一定使用别名；最重要的是标量子查询，也就是内层查询后只剩下一个值，可以使用在任何单一值得情形，包括 select, group by ,having 子句中和大小比较中。
• 关联子查询，用结合条件将两个子查询连在一起，并且结合条件必须写在内层查询中；并且要求满足每一组都返回一个值。但其实并不需要使用group by ,关联子查询自身也可以实现切割。