一、视图
视图究竟是什么呢?如果用一句话概述的话,就是“从 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 ,关联子查询自身也可以实现切割。