多表连接及子查询
本节要点：
- 连接查询
- l 笛卡尔连接
- l 内连接
- l 外连接
- 子查询
- l 单行子查询
- l 多行子查询
数据查询语句是最常用也是最复杂的语句，在介绍查询语句之前，我们先定义三个表，以供示例使用：
- 供应商表(t_supplier)：
存储供应商编号( f_sid)，名称(f_sname)和产地(f_city) 的表。
- 零件表(t_part)：
存储零件的编号(f_pid)名(f_pname)和价格(f_price) 的表。
- 销售表(t_sells)：
存储关于某个供应商(f_sid)销售的零件(f_pid)的信息。它是联 接其他两个表的结点。
现有数据：
t_supplier：
|
f_sid |
f_sname |
f_city |
|
1 |
Smith |
London |
|
2 |
Jones |
Paris |
|
3 |
Adams |
Rome |
|
4 |
Blake |
Peking |
|
5 |
Zyx |
Jinan |
t_part：
|
f_pid |
f_pname |
f_price |
|
1 |
Screw |
10 |
|
2 |
Nut |
8 |
|
3 |
Bolt |
15 |
|
4 |
Gear |
25 |
|
5 |
Ball |
5 |
t_sells：
|
f_id |
f_date |
f_sid |
f_pid |
f_count |
|
1 |
2006-01-01 |
1 |
1 |
10 |
|
2 |
2006-01-02 |
1 |
2 |
15 |
|
3 |
2006-01-03 |
2 |
4 |
5 |
|
4 |
2006-01-04 |
3 |
1 |
100 |
|
5 |
2006-01-05 |
3 |
3 |
50 |
|
6 |
2006-01-06 |
4 |
2 |
23 |
|
7 |
2006-01-07 |
4 |
3 |
78 |
|
8 |
2006-01-08 |
4 |
4 |
110 |
1 连接查询
1.1 笛卡尔连接
查找每个供应商可能供应零件情况 ：
SELECT f_sid,f_pid FROM t_supplier,t_part;
这是查询所有可能的情况，但是这么做意义不大。
1.2 内连接
“内连接”或“简单连接”，它会把两个或多个表通过公共列进行连接，只能查询出匹配的记录，不匹配的记录将无法查询出来。公共列是两个或更多表中存在相同数据的列，比如， t_supplier表和t_sells表都包含一个f_sid的公共列。内连接中最常用的就是等值连接和不等值连接。
1) 等值连接
连接条件中使用“=”（或者使用关键字inner join）连接两个条件列表 。
例：查询每个供应商编号，姓名，供应零件编号，零件名称， 零件数量。
select s.f_sid, s.f_sname, p.f_pid, p.f_pname,sell.f_count
from t_supplier s , t_sells sell, t_part p
where s.f_sid = sell.f_sid
and sell.f_pid = p.f_pid
等价于
select s.f_sid, s.f_sname, p.f_pid, p.f_pname,sell.f_count
from (t_supplier s inner join t_sells sell on s.f_sid = sell.f_sid ) inner join t_part p on sell.f_pid = p.f_pid
2) 不等值连接
不等值连接就是指连接条件中使用“>”、“>=”、“<=”、“<”、“!=”、“<>”、“BETWEEN…AND…”、“IN”等连接两个条件列表，但是这种方式通常需要和其他等值运算一起使用。
例：查询供应商售出数量超过10的零件编号和名称
select p.f_pid, p.f_pname
from t_supplier s, t_part p, t_sells sell
where s.f_sid = sell.f_sid
and sell.f_pid = p.f_pid
and sell.count>10
1.3 外连接
外部连接类型：
- l 左外连接: LEFT [OUTER] JOIN
- l 右外连接： RIGHT [OUTER] JOIN
- l 全外连接： FULL [OUTER] JOIN
1) 左外连接-left join
使用左外连接的查询，返回的结果不仅仅是符合连接条件的行记录，还包含了左边表的全部记录。也就是说，如果左表的某行记录在右边表中没有匹配项，则在返回结果中右表的所有选择列表列均为空。
统计所有供应商各自的销售记录 ：
select t_supplier.f_sid,f_pid,f_id,f_count
from t_supplier left join t_sells on t_supplier.f_sid=t_sells.f_sid;
2) 右外联接-rightjoin
使用右外连接的查询，返回的结果不仅仅是符合连接条件的行记录，还包含了右边表的全部记录。也就是说，如果右表的某行记录在左边表中没有匹配项，则在返回结果中左表的所有选择列表列均为空。
对于上面的例子，同样我们用RIGHT [OUTER] JOIN可以写成下面的语句：
select t_supplier.f_sid,f_pid,f_id,f_count
from t_sells right join t_supplier on t_sells.f_sid=t_supplier.f_sid;
3) 全外联接-fulljoin
返回所有匹配成功的记录，并返回左表未匹配成功的记录，也返回右表未成功匹配的记录。注：使用较少。
我们可以简单的理解为：FULL [OUTER] JOIN 相当于LEFT [OUTER] JOIN和RIGHT [OUTER] JOIN查询的结果集再通过UNION(不是UNION ALL)连接在一起。
2 子查询
子查询就是嵌套查询，它是嵌套在另一个语句中的select语句。 可分为下面几类：
|
子查询 |
说明 |
|
单行子查询 |
将包含一列的一行结果返回到外部查询 |
|
多行子查询 |
将多行结果返回到外部查询 |
|
相关(关联)子查询 |
引用外部查询中的一列，对外部查询中的每一行执行一次子查询 |
|
不相关（非关联）子查询 |
首先执行子查询，然后将值传递给外部查询 |
规则：
- l 子查询必须“自身就是一个完整的查询”——也就是说至少包括一个SELECT子句和一个FROM子句
- l 子查询不能包括ORDER BY子句。如果显示输出需要按照特定顺序显示，那么ORDER BY子句应该作为外部查询的最后一个子句列出
- l 子查询“必须包括在一组括号中”，以便将它与外部查询分开。
- l 如果将子查询在外部查询的WHERE或HAVING子句中，那么该子句一般位于比较运算符的“右边”。
2.1 单行子查询
当我们需要一个中间结果集/值时，我们就需要使用子查询。返回单值的子查询通常用在WHERE和HAVING子句里
统计出所有比名为 'Screw' 的零件贵的零件
select * from t_part
where f_price>(select f_price from t_part where f_pname='Screw');
注意：对于本例而言，子查询仅执行一次，因为它不依赖于外层查询。
2.2 多行子查询
- l 向父查询返回多行结果的嵌套查询
- l 最常用在WHERE和HAVING子句中
- l 包括IN，ALL和ANY运算符
1) IN运算符示例：在books表中查询每一个种类中最贵的图书的价格
SELECT isbn,bretail,bcategory
FROM books
WHERE bretail IN
(SELECT MAX(bretail) FROM books GROUP BY bcategory)
ORDER BY bcategory;
- l 子查询确定每一个种类中最贵的图书的价格。
- l 将每一种类中的最高零售价格传递给外部查询的WHERE子句。
- l 外部查询将每一本书的价格与子查询生成的价格进行比较。
- l 如果一本书的零售价与子查询返回的其中一个价格相同，则在查询的输出中显示这本书的书名，零售价 ，种类。
2) EXISTS子查询：形式为where [not] exists (子查询)
统计哪些不销售任何零件的供应商 （比如说，我们想把这些供应商从数据库中删除）
select * from t_supplier
where not exists
(select * from t_sells where t_sells.f_sid=t_supplier.f_sid);
注意：对于本例而言，我们在WHERE子句的内层SELECT里使用了来自外层SELECT的t_supplier.f_sid。 正如前面所说的，子查询为每个外层查询计算一次，也就是说，t_sells.f_sid的值总是从外层SELECT的实际记录中(t_supplier.f_sid)取得的。
3) [NOT] IN子查询：形式为where fieldname [not] in (子查询)
这种情况适用于子查询中返回多个值时的情况 。
同上例，统计哪些不销售任何零件的供应商，我们也可以用IN子查询
select * from t_supplier
where f_id not in
(select distinct f_id from t_sells);
另一个例子：查询出所在城市为Lindon、Rome和Jinan的供应商的名称
select f_sname from t_supplier where f_city in (‘Lindon’,’Rome’,’Jinan’);
4) ALL和ANY运算符
|
运算符 |
说明 |
|
>ALL |
大于子查询返回的最大值 |
|
<ALL |
小于子查询返回的最小值 |
|
<ANY |
小于子查询返回的最大值 |
|
>ANY |
大于子查询返回的最小值 |
|
=ANY |
等于子查询返回的任何值（于IN相同） |
示例：查找零售价超过cooking种类最贵的图书(>ALL)
SELECT title,retail
FROM books
WHERE retail>ALL
(SELECT retail FROM books WHERE category = 'COOKING');
示例：查找价格低于cooking种类的中最便宜的图书(<ALL)
SELECT title,retail
FROM books
WHERE retail<ALL
(SELECT retail FROM books WHERE category ='COOKING');
示例：<ANY,查找其值小于子查询返回的最大值的记录，确定哪些书的售价比cooking种类中最贵的图书更低
SELECT title,retail
FROM books
WHERE retail<ANY
(SELECT retail FROM books WHERE category ='COOKING');
示例：>ANY，返回其值大于子查询中返回的最小值的记录
SELECT title,retail
FROM books
WHERE retail>ANY
(SELECT retail FROM books WHERE category ='COOKING');
5) FROM里面的子查询
当子查询返回一个结果集时，那么它就相当于一个普通的表，因此，在FROM子句我们同样可以使用子查询。这个特性很有用，FROM里的子查询还可以让我们获得多于一个回合的分组/聚集特性，而不需要求助于临时表。这种嵌套可以使用多次，大大提高了查询的功能。
查询出所有供应商中的最大的销售量：
select max(product_count) as avgPrice
from (select sum(f_count) as product_count
from t_sells
group by f_sid) subtable;
