多表连接及子查询

本节要点：

  •   连接查询
    • 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;

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zhouyeqin/p/7299916.html