3.1定义属性
3.1.1 子查询基本知识
- 子查询（subquery）是一个嵌套（nest）在 select、insert、update 和 delete 语句或其他子查询中的查询，任何允许使用表达式的地方均可使用子查询，但子查询通常位于 where 子句中
- 子查询的实质是一个 select 语句的查询结果能够作为另一个语句的输入值。子查询不仅可用于 where 子句中，还能够用于 from 子句中，此时，子查询的结果将作为一个临时表（temporary table）来使用
- 子查询还可以以字段的形式出现在 select 语句的选择列中
3.1.2 单行子查询应用
- 单行子查询是指子查询的返回结果只有一行数据
- 当在主查询的条件语句中引用子查询的结果时，可使用单行比较符＝、>、<、>=、<= 和 < > 进行比较
示例：查询“江汉路”地区的商品信息时，要求输出商品标题和商品团购价
select areaID from area where areaName=' 江汉路 ' 从地区表查询出地区名称为“江汉路”的地区编号
select title 商品标题 , currentPrice 商品团购价 from product where areaID= ( select areaID from area where areaName=' 江汉路 ' ) 由于 areaID 为 Area 表主键，所以子查询 SQL1 的查询结果为一条记录。单行子查询仅能够返回一条记录
示例：查询团购价高于平均团购价的商品信息时，要求输出商品标题和商品团购价
select avg(currentPrice) from product 查询商品平均团购价
select title 商品标题 , currentPrice 商品团购价 from product where currentprice>(select avg(currentPrice) from product) 查询团购价大于平均团购价的商品信息时，要求输出商品标题和商品团购价
示例：查询团购价高于“食品”类商品最高团购价的商品信息，要求输出商品标题和商品团购价
select categoryID from category where categoryName=' 食品 ' 查询商品类型名为“食品”的类型编号
select max(currentPrice) from product where categoryID=( select categoryID from category where categoryName=' 食品 ') 查询“食品”类商品的最高团购价,将上述SQL代入
select title 商品标题 , currentprice 商品团购价 from product where currentPrice>(select max(currentprice) from product where categoryID= (select categoryID from category where categoryName=' 食品 '))
3.1.3子查询使用经验
- 子查询通常用在位于 select 语句的 where 子句中，且可以嵌套
- 编写复杂的子查询的解决思路是：逐层分解查询。即从最内层的子查询开始分解，将嵌套的 SQL 语句拆分为一个个独立的 SQL 语句
- 子查询的执行过程遵循“由里及外”的原则，即先执行最内层的子查询语句，然后将执行结果与外层的语句进行合并，依次逐层向外扩展并最终形成完整的 SQL 语句
- 一般情况下，连接查询可改为子查询实现，但子查询却不一定可改为连接查询实现
- 子查询与连接查询执行效率的比较：当子查询执行结果的行数较大，而主查询执行结果的行数较小时，子查询执行效率较高；而情况相反时，则连接查询执行效率较高
3.2select 基本查询
3.2.1 in 比较符
- 多行子查询是指子查询的返回结果是多行数据。常见的多行比较符包括 in、all、any 和 some
- 使用多行比较符 in 时，主查询会与子查询中的每一个值进行比较，如果与其中的任意一个值相同，则返回。not in 与 in 的含义恰好相反
示例：查询营业地点在“江汉路”“武广”和“亚贸”地区的商品信息时，要求输出商品标题、商品团购价和区域编号
select areaID from area where areaName=' 江汉路 ' or areaName=' 武广 ' or areaName=' 亚贸 ' 查询出地区名为“江汉路”“武广”和“亚贸”的地区编号
select title 商品标题 , currentPrice 商品团购价 from product where areaID in (select areaID from area where areaName=' 江汉路 ' or areaName=' 武广 ' or areaName=' 亚贸 ') 查询“江汉路”“武广”和“亚贸”地区的商品信息
示例：查询所有男性顾客所购商品信息时，要求输出商品标题和商品团购价，并按照团购价升序排列
select customerID from customer where gender=' 男 ' 查询所有男性顾客的客户编号
select ordersID from orders where customerID in( select customerID from customer where gender=' 男 ') 查询所有男性顾客所生成订单的全部订单编号
示例：select productID from ordersDetail where ordersID in(select ordersID from orders where customerID in(select customerID from customer where gender=' 男 ')) 查询所有男性顾客所订购商品的全部商品编号
select title 商品标题 , currentPrice 团购价 from product where productID in( select productID from ordersDetail where ordersID in( select ordersID from orders where customerID in( select customerID from customer where gender=' 男 '))) order by currentPrice 查询所有男性顾客所订购商品的商品标题和团购价，并按团购价升序排列
3.2.2 all 关键字子查询
all 关键字的使用语法如下：
语法：表达式或字段 多行比较运算符 all( 子查询 )
语法说明：all 关键字在子查询之前，通过该关键字将一个表达式或列的值，与子查询所返回的一列值中的每一行进行比较，只要有一次比较的结果为 false（假），则 all 测试返回 false，不执行主查询；否则返回 true，执行主查询
- <all，表示小于最小值
- >all，表示大于最大值
示例：查询团购价比所有食品类商品团购价都高的商品信息时，要求输出商品标题和商品团购价
select categoryID from category where categoryName=' 食品 '
select currentPrice from product where categoryID=( select categoryID from category where categoryName=' 食品 ')
select title 商品标题 , currentPrice 团购价 from product where currentPrice > all (select currentPrice from product where categoryID=( select categoryID from category where categoryName=' 食品 '))
3.2.3 any|some 关键字子查询
any|some 关键字的使用语法如下：
语法：表达式或字段 多行比较运算符 any|some（子查询）
语法说明：any 与 some 的查询功能相同, any 或 some 用于子查询之前，通过 any|some 比较运算符，将一个表达式或列的值与子查询所返回的一列值中的每一行进行比较，只要有一次比较的结果为 true，则 any 或 some 测试返回 true，执行主查询；否则结果为false，不执行主查询
- <any|some，小于最大值
- =any|some，与 in 运算符等价
- >any|some，大于最小值
示例：查询团购价比任意一款食品类商品团购价高的商品信息时，要求输出商品标题和商品团购价
select title 商品标题 , currentPrice 商品团购价 from product where currentPrice > any(select currentPrice from product where categoryID =( select categoryID from category where categoryName=' 食品 '))
3.3子查询非典型应用
3.3.1 在 from 子句中使用子查询
子查询通常用于 where 子句中，但其也可在 from 子句和 select 子句中使用
为帮助商家提升定价能力，优化产品销售策略，“零聚网”平台在向每位商家提供产品信息的同时，还提供了该类商品的平均团购价
示例：select A.categoryID 商品类型编号 , A.title 商品标题 , A.currentPrice 商品团购价 , B.avgPrice 该类商品平均团购价 from product A,(select categoryID, avg(currentPrice) avgPrice from product group by categoryID) B where A.categoryID=B.categoryID
3.3.2 在 select 子句中使用子查询
在 select 子句中使用子查询，其实质是将子查询的执行结果作为 select 子句的列，由此可以产生与连接查询异曲同工的作用
查询商品数和已订购商品个数
示例：select count(productID) 商品个数 , (select count(distinct productID) from ordersDetail) 已订购商品个数 from product
3.3.3 exists 关键字
exists 关键字使用的语法如下：
语法：主查询表达式 [not] exists ( 子查询 )
语法说明：exists 用于检查子查询是否至少会返回一行数据，实际上该子查询并不返回任何数据，而是返回值 true 或 false。exists 指定一个子查询，用于检测行的存在。当子查询的行存在时，则执行主查询表达式，否则不执行
示例：查询所有订购过商品的客户姓名
select customerName 客户姓名 from customer c where exists (select * from orders where customerID=c.customerID)
3.4在 DML 语句中使用子查询
3.4.1在 update 子句中使用子查询
子查询不仅可在 select 语句中用于实现需要嵌套的查询功能，还可以维护数据，完成复杂的更新、删除和插入功能。为了完成上述数据维护功能，需要在 DML 的 update 语句、delete 语句和 insert 语句中使用子查询
在 DML 语句中使用子查询与在 select 语句中使用子查询的原理是一致的，均为将内层子查询的结果作为外层主查询中 where 条件的参考值来使用
示例：在夏天火锅淡季时节，“零聚网”为促销，决定将所有火锅类餐饮的团购价降低 10%
update product set currentPrice=currentPrice*0.9 where categoryID in( select categoryID from category where categoryName=' 火锅 ')
示例：统计所有商品的总订购数量，并用该数量更新相应商品的“销售数量”字段值
update product set salesCount=(select sum(quantity) from ordersDetail where productID= product.productID group by productID)
示例：删除客户“刘亚蒙”所有的订购信息
delete from orders where ordersID in (select ordersID from orders where customerID=( select customerID from customer where customerName=' 刘亚蒙 '))
总结 ：
- 子查询是一个嵌套在 select、insert、update 和 delete 语句或其他子查询中的查询
- 比较运算符 all 关键字用于子查询之前。通过该关键字将一个表达式或列的值，与子查询所返回的一列值中的每一行进行比较，只要有一次比较的结果为 false，则 all 测试返回 false，不执行主查询；否则返回 true，执行主查询
- exists 用于检查子查询是否至少会返回一行数据
- 子查询不仅可在 select 语句中使用，用于实现需要嵌套的查询功能，还可以维护数据，完成复杂的更新、删除和插入功能
