连接查询

一、聚集函数 count

1、统计领取了物资的抢修工程项目数

select count(distinct prj_num) 
from out_stock;

二、、分组函数 group by以及对应的领取总量。

1、按工程部门及物资编号统计其抢修的项目个数

select department,mat_num,count(distinct prj_num),sum(amount)
from out_Stock
group by department,mat_num

2、with rollup

select department,mat_num,count(distinct prj_num),sum(amount)
from out_Stock
group by department,mat_num
with rollup;

区别：增加了按第一个字段进行分组，在组内进行统计，最后给出总计。

3、with cube

select department,mat_num,count(distinct prj_num),sum(amount)
from out_Stock
group by department,mat_num
with cube;

区别：cube关键字对所有的分组字段进行统计，最后给出合计。

三、连接查询

主要包括等值连接查询，自然连接查询，非等值连接查询，自身连接查询，外连接查询和复合条件查询。
1、等值连接查询
（“=”，在连接条件中列名对应属性的类型必须是可比的，但不必是相同的）
查询每个抢修工程及其领料出库的情况

select Salvaging.*,out_stock.*
from salvaging,out_stock
where Salvaging.prj_num=out_stock.prj_num;

2、把目标列中重复的属性列去掉，则为自然连接

select Salvaging.prj_num,prj_name,start_date,end_date,pri_status,mat_num,amount,get_date,department
from salvaging,out_stock
where Salvaging.prj_num=out_stock.prj_num;

3、外连接查询
（1）左外连接：列出左边关系中所有的元组

select Salvaging.prj_num,prj_name,start_date,end_date,pri_status,mat_num,amount,get_date,department
from  Salvaging left outer join out_stock on (salvaging.prj_num=out_stock.prj_num);

（2）右外连接：列出右边关系中所有元组
（3）全外连接：列出左边关系和右边关系中的所有元组
4、复合条件连接查询
where子句中可以包含多个连接条件
查询项目号为“2010015”的抢修项目所使用的物资编号、物资名称、规格和使用数量

select out_stock.mat_num,mat_name,speci,out_stock.amount
from  stock,out_stock
where stock.mat_num=out_stock.mat_num and prj_num='20100015';

查询使用了护套绝缘电线的所有抢修项目编号及名称

select salvaging.prj_num,prj_name
from  Salvaging,stock,out_stock
where salvaging.prj_num=out_stock.prj_num 
		and out_stock.mat_num=stock.mat_num 
			and speci='护套绝缘电线';

5、自身连接查询
查询同时使用了m001和m002的抢修工程的工程号

select a.prj_num
from  out_stock a,out_stock b
where a.prj_num=b.prj_num 
		and a.mat_num='m001' and b.mat_num='m002';

四、嵌套查询

1、带谓词in的嵌套查询
查询与规格“BVV-120”的“护套绝缘电线”在同一个仓库存放的物资的名称，规格，数量。
首先确定规格为“BVV-120”的“护套绝缘电线”的物资所存放仓库的物资。

select warehouse from Stock
where speci='BVV-120' and mat_name='护套绝缘电线';

查找所有存放在供电局1#仓库的物资。

use master
select mat_name,speci,amount from Stock
where warehouse='供电局1#仓库';

构成嵌套语句

select mat_name,speci,amount from Stock
where warehouse in
(select warehouse from Stock
	where speci='BVV-120' and mat_name='护套绝缘电线');

或用自身连接来完成

select s1.mat_name,s1.speci,s1.amount from Stock s1,Stock s2
where s1.warehouse = s2.warehouse and s2.speci='BVV-120' and s2.mat_name='护套绝缘电线';

2、查询工程项目“观澜站光缆抢修”所使用的物资编号和物资名称。

select mat_num,mat_name from Stock 
where mat_num in 
(select mat_num from out_stock
	where prj_num in
	(select prj_num from Salvaging
		where prj_name='观澜站光缆抢修'));

或连接查询

select stock.mat_num,mat_name  from stock,out_stock,Salvaging
where stock.mat_num = out_stock.mat_num and
	out_stock.prj_num = Salvaging.prj_num and prj_name= '观澜站光缆抢修';

3、带有比较运算的子查询
如例1：

select mat_name,speci,amount from Stock
where warehouse =
(select warehouse from Stock
	where speci='BVV-120' and mat_name='护套绝缘电线');

4、查询出库存量超过该仓库物资平均库存量的物资编号、名称、规格及数量。

select mat_num,mat_name,speci,amount from Stock s1
where amount >
(select avg(amount) from Stock s2
	where s2.warehouse=s1.warehouse);

5、带有any或all谓词的子查询
查询其他仓库中比供电局1#仓库的某一物资库存量少的物资名称，规格和数量。

select mat_name,speci,amount from Stock 
where warehouse <> '供电局1#仓库'
	and amount <any
	(select amount from Stock 
		where warehouse='供电局1#仓库');

或使用聚集函数

select mat_name,speci,amount from Stock 
where warehouse <> '供电局1#仓库'
	and amount < (select max(amount) from Stock 
		where warehouse='供电局1#仓库');

查询其他仓库中比供电局1#仓库的所有物资库存量少的物资名称，规格和数量。

select mat_name,speci,amount from Stock 
where warehouse <> '供电局1#仓库'
	and amount <all
	(select amount from Stock 
		where warehouse='供电局1#仓库');

或使用聚集函数

select mat_name,speci,amount from Stock 
where warehouse <> '供电局1#仓库'
	and amount < (select min(amount) from Stock 
		where warehouse='供电局1#仓库');

6、带有exists谓词的子查询
带有exists谓词的子查询不返回任何数据，只产生逻辑真值true或逻辑假值false
查询所有使用了m001物资的工程项目编号

select prj_num
from Salvaging
where exists (select * from out_stock where prj_num=salvaging.prj_num and mat_num='m001');

由exists引出的子查询，其目标属性列表达式一般用*表示，因为带exists的子查询只返回真值或假值。
not exists
查询没有使用了m001物资的工程项目编号

select prj_num
from Salvaging
where not exists (select * from out_stock where prj_num=salvaging.prj_num and mat_num='m001');

一些带exists 或not exists谓词的子查询不能被其他形式的子查询等价替换，但所有带in谓词、比较运算符、any和all谓词的子查询都能用exists谓词的子查询等价替换。
如：

select prj_name
from Salvaging
where prj_num in (select prj_num from out_stock where  mat_num='m001');

7、查询被所有的抢修工程项目都是用了的物资名称和规格。
（查询这样的物资，没有一个抢修工程没有使用过它）

select mat_name,speci from stock
where not exists
	(select * from salvaging
		where not exists
		 (select * from out_stock
			where mat_name=stock.mat_name and prj_num=Salvaging.prj_num));

查询所有物资包括抢修工程为“20100016”所用物资的抢修工程号。

select distinct prj_num from out_stock sx
where not exists
	(select * from out_stock sy where prj_num=20100016 and not exists
		(select * from out_stock sz 
			where sz.mat_num=sy.mat_num and sz.prj_num=sx.prj_num));