数据库子查询

所用案例涉及的四个表：

一、子查询定义

1、概念：（what）

子查询： 将一个查询语句嵌套在另一个查询语句中，在特定情况下，一个查询语句的条 件需要另一个查询语句来获取，内层查询语句的查询结果可以为外层查询语句提供查询条 件。

2、子查询使用方式 ：

可用于 where 子句中;

还能够用于 from 子句中作为一个临时表;

以字段的形式出现在 select 语句的选择列中

3、分类

子查询中只查一个字段的值；

根据子查询所返回的结果行数，可以将其分为单行子查询和多行子查询。

二、单行子查询应用

1、概念

单行子查询指子查询的返回结果只有一行数据。

当在主查询的条件语句中引用子查询的结果时， 可使用单行比较符（如＝、>、

<、>=、<= 和 < >）进行比较 。

2、使用例子(how)

从内到外，逐层分析

有连接关系的两个表进行子查询：

  第一例子：

1  ‐‐ 用子查询实现查询所有张三下过的订单 3 A 第一步：

2  SELECT WORKER_ID FROM T_WORKER w WHERE w . NAME = ' 张三 '

3  B 第二步：

4  SELECT * FROM T_ORDER o where o . CASHIER_ID = (

5  SELECT WORKER_ID FROM T_WORKER w

6  WHERE w . NAME = ' 张三 '

7  )

  第二例子（子查询使用聚合函数）：

1  ‐‐ 用子查询实现查询价格高于或等于所有订单平均价格的订单

2  A 第一步：

3  SELECT AVG ( MONEY ) FROM T_ORDER

4  B 第二步：

5 SELECT * FROM T_ORDER o

6 WHERE o . MONEY > (

7 SELECT AVG ( MONEY ) FROM T_ORDER

8 )

3、子查询使用归纳：

子查询一般用于 select 语句的 where 子句中，且可以嵌套。

编写复杂的子查询的解决思路是 逐层分解查询 ，即从最内层的子查询开始分解， 将嵌套的 SQL 语句拆分为一个个独立的 SQL 语句。

子查询的执行过程遵循“由里及外”的原则，即先执行最内层的子查询语句，然 后将执行结果与外层的语句进行合并，依次逐层向外扩展并最终形成完整的 SQL 语 句。

一般情况下，连接查询可改为子查询实现；但子查询却不一定可改为连接查询实 现。

子查询与连接查询执行效率的比较：当子查询执行结果的行数较大，而主查询执 行结果的行数较小时，子查询执行效率较高；反之，则连接查询执行效率较高。

三、多行子查询应用

1.in 比较符

(1)概念(what)

使用多行比较符 in 时，主查询会与子查询中的每一个值进行 比较，如果与其中的任意一个值相同，则返回。

not in 与 in 的含义恰好相反。

单行子查询也是多行子查询的一种特殊情况 ，所以单行子查询 的“=”比较符可以替换为多行子查询的“in”比较符。但不能将多行子查询的“in”比较符替换为单行子查询的“=”比较符。

（2）使用例子(how)

从内到外，逐层分析

1 ‐‐ 用子查询实现查询所有张三和李四下过的订单

2 SELECT * FROM T_ORDER o

3 where o . CASHIER_ID in (

4 SELECT WORKER_ID FROM T_WORKER w

5 WHERE w . NAME = ' 张三 ' OR w . NAME = ' 李四 '

6 )

2.all 关键字子查询

（1）概念：(what)

通过 all 关键字将一个表达式或列的值与子查询所返回的 一列值 中的 每一行 进行比较， 只要有一次比较的结果为 false（假），则 all 测试返回 false，主查询不执行；否则返 回 true，执行主查询。

(2)语法结构

表达式或字段   单行比较运算符   all( 子查询 )

（3）all运算符含义

all 关键字位于单行比较运算符之后；

当 <all 时，表示小于最小值 （all后面跟着的子查询里的）,等 价于“<(子查询里取最小值)” 。

当 >all 时，表示大于最大值 （all后面跟着的子查询里的）,等 价于“>(子查询里取最大值)” 。

（4）例子

1 ‐‐ 查询李四下的订单中高于张三下的所有订单金额的订单

2 SELECT * FROM T_ORDER o

3 WHERE o . CASHIER_ID in (

4 SELECT WORKER_ID FROM T_WORKER w

5 WHERE w . NAME = ' 李四 '

6 ) AND

7 o . MONEY > ALL (

8 SELECT MONEY FROM T_ORDER WHERE CASHIER_ID in (

9 SELECT WORKER_ID FROM T_WORKER wo

10 WHERE wo . NAME = ' 张三 '

11 )

12 )

13 等价于： 14 SELECT * FROM T_ORDER o

15 WHERE o . CASHIER_ID in (

16 SELECT WORKER_ID FROM T_WORKER w

17 WHERE w . NAME = ' 李四 '

18 ) AND

19 o . MONEY > (

20 SELECT MAX ( MONEY ) FROM T_ORDER WHERE CASHIER_ID in (

21 SELECT WORKER_ID FROM T_WORKER wo

22 WHERE wo . NAME = ' 张三 '

23 )

24 )

3.any|some 关键字子查询

（1）概念：(what)

any 与 some 的查询功能相同。

any 或 some 用于子查询之前，通过 any|some 比较运算符， 将一个表达式或列的值与子查询所返回的一列值中的每一行进行比 较， 只要有一次比较的结果为 true，则 any 或 some 测试返回

true，主查询执行 ；否则结果为false，主查询不执行。

(2)语法结构

表达式或字段  多行比较运算符  any（子查询）

表达式或字段  多行比较运算符  some（子查询）

（3） any|some 运算符含义

any|some 关键字位于比较运算符之后；

当 <any|some 时，表示小于最大值（后面跟着的子查询里 的）, 等价于“<(子查询里取最大值)” 。

当 =any|some 时，表示与 in 运算符等价 当 >any|some 时，表示大于最小值（后面跟着的子查询里 的）, 等价于“>(子查询里取最小值) ”。

（4）例子

1 需求：

2 ‐‐ 查询李四下的订单中高于张三下的任意一个订单金额的订单

3 SELECT * FROM T_ORDER o

4 WHERE o . CASHIER_ID in (

5 SELECT WORKER_ID FROM T_WORKER w

6 WHERE w . NAME = ' 李四 ' 7 ) AND

8 o . MONEY > SOME (

9 SELECT MONEY FROM T_ORDER WHERE CASHIER_ID in (

10 SELECT WORKER_ID FROM T_WORKER wo

11 WHERE wo . NAME = ' 张三 '

12 )

13 )

四、子查询特殊应用

1.from 子句中的子查询

（1）概念：from 子句中的子查询主要作为查询过程中的临时数据表

（2）例子：

1 ‐‐ 查询每个收银员的下单总额

2 select w . name , c . sale from t_worker w ,

3 ( select CASHIER_ID , sum ( money ) as sale

4 from t_order o group by CASHIER_ID

5 ) c

6 where w . worker_id = c . CASHIER_ID

2.select 子句中的子查询

（1）概念：

select 子句中使用子查询，其实质是将子查询的执行结果作为 select 子句的列，可以

起到与连接查询异曲同工的作用。 且此处的子查询只能查一列，多于一列执行出错

（2）例子：

1 ‐‐ 为了了解张三的业绩情况，查所有订单数及张三下的订单数所对比

2 select count ( * ) as ' 所有订单数 ' ,

3 ( select count ( * ) from t_order ord where ord . CASHIER_ID = (

4 SELECT WORKER_ID FROM T_WORKER w

5 WHERE w . NAME = ' 张三 '

6 )

7 ) as ' 张三下单数 '

8 from t_order

3.exists 子查询的用法

（1）概念：

exists 用于检查子查询是否至少会返回一行数据。 该子查询实际上并不返回任何数据，而是返回值 true 或 false。

exists 子查询的返回结果若为 true，则执行主查询获得所有预 订电影票的客户姓名；exists 子查询的返回结果若为 false，则不执 行主查询。

（2）语法结构：

主查询表达式 [not] exists ( 子查询 )

（3）例子：

1 ‐‐ 查询被点过的餐品信息

2 SELECT f . * FROM t_food f

3 where EXISTS (

4 select * from

5 t_orderdetail od

6 where f . FOOD_ID = od . FOOD_ID

7 )

8

9 ‐‐ 查询没有被点过的餐品信息

10 SELECT f . * FROM t_food f

11 where not EXISTS (

12 select * from

13 t_orderdetail od

14 where f . FOOD_ID = od . FOOD_ID

15 )

五、DML 语句中的子查询

1.update 子句中的子查询

（1）概念：

在 DML 语句中使用子查询与在 select 语句中使用子查询的原理是一致的，均

为将内层子查询的结果作为外层主查询中 where 条件的参考值来使用。

在 DML 的 update 语句、delete 语句和 insert 语句中使用子查询，可以维护

数据，完成复杂的更新、删除和插入功能。

（2）例子：

1 ‐‐ 修改张三下的所有订单金额为 200

2 update t_order set

3 money = 200

4 where CASHIER_ID = ( 5 SELECT WORKER_ID FROM T_WORKER w

6 WHERE w . NAME = ' 张三 '

7 )

2.delete 子句中的子查询

（1）注意：删除数据时需要考虑表的主从关系，正确的做法是先删除从表数据，再删除主

表数据。

（2）例子：

1 需求：删除张三下的所有订单

2 delete from ticket_sell where customerID in

3 ( select id from customer where username = 'chen01' )