5 复杂查询

5-1 视图

 

究竟视图是什么呢？如果用一句话概述的话，就是“从sql的角度来看视图就是一张表”。实际上，在sql语句中并不需要区分哪些是表，哪些是视图。

 

那么视图和表到底右什么不同呢？区别只有一个，那就是“是否保存了实际的数据”。

通常，我们在创建表时，会通过insert语句将数据保存到数据库之中。而数据库中的数据实际上会被保存到计算机的存储设备（通常是硬盘）中。因此，我们通过select语句查询数据时，实际上就是从存储设备（硬盘）中读取数据，进行各种计算之后，再将结果返回给用户这样一个过程。

但是使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。实际上视图保存的是select语句。我们从视图中读取数据时，视图会在内部执行该select语句并创建出一张临时表。

 

视图的优点大体有两点：第一点是由于视图无需保存数据，因此可以节省存储设备的容量。第二个优点就是可以将频繁使用的select语句保存成视图，这样就不用每次都重新书写了。而且还可以大大提高效率。而且视图中的数据会随着原表的变化自动更新。

 

法则5-1

表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的select语句。

 

 

法则5-2

应该将经常使用的select语句做成视图。

 

 

 

创建视图的方法

/*

create view 视图名称(<视图列名1>, <视图列名2>, ......) as <select语句>

*/

 

delete from shohin;

 

select * from shohin;

 

insert into shohin values ('0001', 't恤衫', '衣服', 1000, 500, '2009-09-20');

insert into shohin values ('0002', '打孔器', '办公用品', 500, 300, '2009-09-11');

insert into shohin values ('0003', '运动t恤', '衣服', 4000, 2800, null);

insert into shohin values ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

insert into shohin values ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

insert into shohin values ('0006', '叉子', '厨房用具', 500, null, '2009-09-20');

insert into shohin values ('0007', '擦菜饭', '厨房用具', 880, 790, '2008-04-28');

insert into shohin values ('0008', '圆珠笔', '办公用品', 100, null, '2009-11-11');

 

create view shohinsum (shohin_bunrui, cnt_shohin) as

select shohin_bunrui, count(*) from shohin group by shohin_bunrui;

 

select * from shohinsum;

 

 

在from子句中使用视图的查询，通常有如下两个步骤：

首先执行定义视图的select语句，根据得到的结果，再执行在from子句中使用视图的select语句。

也就是说，使用视图的查询通常需要执行2条以上的select语句。

 

多重视图。

 

使用视图的查询

create view shohinsumjim (shohin_bunrui, cnt_shohin) as select shohin_bunrui, cnt_shohin

from shohinsum where shohin_bunrui = '办公用品';

 

虽然语法上没有错误，但是我们还是应该尽量避免在视图的基础上创建视图。这是因为对于多数dbms来说，多重视图会降低sql的性能。

 

法则5-3

应该避免在视图的基础上创建视图

 

 

 

为什么不能使用order by子句呢？这是因为视图和表一样，数据行都是没有顺序的。实际上，有些dbms在定义视图的语句中是可以使用order by子句的，但是这并不是通用的语法。因此，在定义视图时请不要使用order by。

 

法则5-4

定义视图时不要使用order by子句。

 

 

法则5-5

视图和表需要同时进行更新，因此通过聚合得到的视图无法进行更新。

 

 

 

删除视图

-- drop view 视图名称(<视图列名1>, <视图列名2>, ......);

 

drop view shohinsum;

 

 

 

 

5-2 子查询

 

我们先来复习一下视图的概念，视图并不是用来保存数据的，而是通过保存读取数据的select语句的方法来为用户提供便利的工具。反之，子查询就是将用来定义视图的select语句直接用于from子句当中。

 

子查询和视图

create view shohinsum (shohin_bunrui, cnt_shohin) as select shohin_bunrui, count(*) from shohin group by shohin_bunrui;

 

-- 下面代码也能实现此功能  这里的内层 select子句为 from子句中的select子句

select shohin_bunrui, cnt_shohin from (select shohin_bunrui, count(*) as cnt_shohin from shohin group by shohin_bunrui) as shohinsum;

 

/*

首先执行from子句中的select语句（子查询）

select shohin_bunrui, count(*) as cnt_shohin from shohin group by shohin_bunrui;

根据上面的结果执行外层的select语句

select shohin_bunrui, cnt_shohin from shohinsum;

*/

 

select shohin_bunrui, cnt_shohin from shohinsum;

 

 

select shohin_bunrui, cnt_shohin from (select * from (select shohin_bunrui, count(*) as cnt_shohin from shohin group by shohin_bunrui) as shohinsum where cnt_shohin = 4) as shohinsum2;

 

 

法则5-6

子查询作为内层查询会首先执行。

 

 

 

 

 

 

子查询的名称：原则上子查询必须设定名称，因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称。为子查询设定名称时需要使用as关键字，该关键字有时也可以省略。

 

 

 

标量就是单一的意思，在数据库之外的领域也经常使用。

 

标量子查询则有一个特殊的限制，那就是必须而且只能返回1行1列的结果。

标量子查询（scalar subquery）

-- 在where子句中使用标量子查询

 

-- 在where子句中不能使用聚合函数

-- 虽然这样的select语句看上去能够满足我们的要求，但是由于在where子句中不能使用聚合函数，因此这样的select语句是错误的。

select shohin_id, shohin_mei, hanbai_tanka from shohin where hanbai_tanka > avg(hanbai_tanka);

 

-- 可以使用下面的代码实现

-- 计算平均销售单价的标量子查询

select avg(hanbai_tanka) from shohin;

select shohin_id, shohin_mei, hanbai_tanka from shohin where hanbai_tanka > (select avg(hanbai_tanka) from shohin);

 

 

-- 在select子句中使用标量子查询

select shohin_id, shohin_mei, hanbai_tanka, (select avg(hanbai_tanka) from shohin) as avg_tanka from shohin;

 

select shohin_bunrui, avg(hanbai_tanka) from shohin group by shohin_bunrui;

 

-- 在having子句中使用标量子查询

select shohin_bunrui, avg(hanbai_tanka) from shohin group by shohin_bunrui having avg(hanbai_tanka) > (select avg(hanbai_tanka) from shohin);

 

 

法则5-7

标量子查询就是返回单一值的子查询

 

 

标量子查询的书写位置并不仅仅局限于where子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是select子句，group by子句，having子句，还是order by子句，几乎所有的地方都可以使用。

 

特别需要注意的是该子查询绝对不能返回多行结果。也就是说如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了。

 

 

 

5-3 关联子查询

 

在where子句中使用子查询时，该子查询的结果必须是单一的。

 

普通的子查询和关联子查询的区别

-- 普通的子查询和关联子查询的区别

select avg(hanbai_tanka) from shohin group by shohin_bunrui;

 

-- 发生错误的子查询

select shohin_id, shohin_mei, hanbai_tanka from shohin where hanbai_tanka > (select avg(hanbai_tanka)

from shohin group by shohin_bunrui);

 

-- 正确的关联子查询书写方法

select shohin_id, shohin_mei, hanbai_tanka from shohin as s1 where hanbai_tanka >

(select avg(hanbai_tanka) from shohin as s2 where s1.shohin_bunrui = s2.shohin_bunrui group by shohin_bunrui);

 

-- 错误的关联子查询书写方法：将关联条件移到子查询之外 该书写方法究竟违法了什么规则呢？那就是关联名称的作用域。也就是说关联名称存在一个有效作用域的限制。

select shohin_bunrui, shohin_mei, hanbai_tanka from shohin as s1 where s1.shohin_bunrui = s2.shohin_bunrui and hanbai_tanka >

(select avg(hanbai_tanka) from shohin as s2 group by shohin_bunrui);

-- 正确的关联子查询书写方法

select shohin_bunrui, shohin_mei, hanbai_tanka from shohin as s1 where hanbai_tanka >

(select avg(hanbai_tanka) from shohin as s2 where s1.shohin_bunrui = s2.shohin_bunrui group by shohin_bunrui);

 

这里起到关键作用的就是在子查询中添加的where子句的条件。

 

请大家一定不要忘记关联名称具有一定的有效作用域。如前所述，sql是按照先内层子查询后外层查询的顺序来执行的。这样，子查询执行结束时会留下执行结果，作为抽出源的s2表其实已经不存在了。因此，在执行外层查询时，由于s2表已经不存在了，就会返回“不存在使用该名称的表”这样的错误。

 

法则5-8

在细分的组内进行比较时，需要使用关联子查询。