数据库子查询

所用案例涉及的四个表：

一、子查询定义

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')