欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

5 复杂查询

程序员文章站 2022-05-03 17:01:47
5 复杂查询 5-1 视图 究竟视图是什么呢?如果用一句话概述的话,就是“从SQL的角度来看视图就是一张表”。实际上,在SQL语句中并不需要区分哪些是表,哪些是视图。 那么视图和表到底右什么不同呢?区别只有一个,那就是“是否保存了实际的数据”。 通常,我们在创建表时,会通过INSERT语句将数据保存 ......

 

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

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