一、子查询定义
定义：
- 子查询允许把一个查询嵌套在另一个查询当中。
- 子查询用()括起来
- 子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。
- 子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之一：select、insert、update、delete、set或 者do。
子查询的位置：
- select后(标量子查询)、from 后(表子查询)、where或having条件中(标量,行,列子查询) 、exists后面（相关子查询,表子查询）
- group by 和order by 中无实用意义。
二、子查询分类:
按结果集的行列数分类：
- 标量子查询：返回单一值的标量，最简单的形式。
- 列子查询：返回的结果集是 N 行一列。
- 行子查询：返回的结果集是一行 N 列。
- 表子查询：返回的结果集是 N 行 N 列。
可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS
按子查询返回值的数量分类：
- 标量子查询 ：会返回一个值，一般搭配着
< >= <= = <>使用 - 多值子查询：会返回一列、一行或者一个表，它们组成一个集合。
我们一般使用的any、in、all、some等词，将外部查询与子查询的结果进行判断、不排除特殊情况比如()=()。
如果将any、in、all、some等词与标量子查询，就会得到空的结果。
按子查询位置分类：
- where型子查询：把内层查询结果当作外层查询的比较条件）
- from型子查询：把内层的查询结果供外层再次查询
- exists型子查询：把外层查询结果拿到内层，看内层的查询是否成立
按子查询与外部查询的依赖关系分类：
- 独立子查询：不依赖外部查询而运行的子查询
- 相关子查询：是指引用了外部查询列的子查询，即子查询会对外部查询的每行进行一次计算。
1、标量子查询：
- 是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式
- 可以使用
= > < >= <= <>这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧
#查询大于平均年龄的学生
select * from stu age>(select avg(age) from stu)
#查询王昭君的成绩，并显示成绩
select * from scores where s_no=(select sid from stu where name='王昭君')
注意子查询结果只能是一个值
2、列级子查询
- 指子查询返回的结果集是 一列N 行
- 因为子查询返回的结果是多个值，不是一个结果，
所以不能直接使用= > < >= <= <>这些比较标量结果的操作符 - 需要
IN、ANY、SOME 、ALL操作符，搭配= > < >= <= <>使用
#查询18岁的学生成绩，显示成绩
select * from score where stuid in (select stuid from stu where age=18)
select * from score where stuid > any (select stuid from stu where age=18)
select * from score where stuid > all (select stuid from stu where age=18)
注意：对于 子查询中的表是空表的情况，语句均返回 NULL；
3、行级子查询
- 指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集
select * from stu where sex='男' and age=26
#可以写成
select * from stu where (sex,age)=('男',26)
#因此 ('男',26)里面两个值可以写成语句
select * from stu where (sex,age)=
(select sex,age from stu where sex='男' order by age limit 1)
#()与()字段个数要对应
4、表子查询
- 指子查询返回的结果集是 N 行 N 列的一个表数据
- 将子查询结果充当
一张表，要求必须起别名
select * from (select * from stu) as s
select* from article where (title,content,uid) in (select title,content,uid from blog)
select * from 表1
inner join (可以是查询出来的结果当作数据源使用) as 别名
on 表1.字段=表2.字段
where 条件
#查询数据库和系统测试的课程成绩
select * from score
inner join (select * from course where cname in ('数据库','系统测试')) as c
on score.cid=c.cid
#比起
select * from score
inner join course on score.cid=c.cid
where course.cname in ('数据库','系统测试')
#性能高一点
三、子查询中特定的关键词使用
1、 in 、not in
in 、 =any 、 =some ：表示一个范围内
not in 、 <>all：表示不在一个范围内
2、 any| some 任意一个，只要条件满足任意的一个，就返回TRUE
=any、 =some、 in：等于子查询返回的结果
>any、 >some ：大于子查询返回的结果中任意一个值，就是大于最小值
<any 、<some ：小于子查询返回的结果中任意一个值，就是小于最大值
!=any 、!=some：没有意义，就是查了所有
3、 all 所有
=all：没有意义，一个值不可能同时等于多个值
>all：大于子查询返回的结果中所有值，就是大于最大值
<all：小于子查询返回的结果中所有值，就是小于最小值
!=all 、<>all 、not in：不等于子查询返回的结果中所有值，就是非
4、 exists、not exists
- 用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False，检测 行 的存在
- 与其它谓词和逻辑表达式不同的是，
无论输入子查询是否返回行，exists都不会返回unknown，
对于exists来说，unknown就是false
select... fromtable where exists (select........)
将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留
#获得城市为hangzhou，并且存在订单的用户
select *
from table1
where city='hangzhou' and exists
(select *
from table2
where table1.customer_id=table2.customer_id);
四、子查询优化
很多查询中需要使用子查询。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死。子查询可以使查询语 句很灵活，但子查询的执行效率不高。
子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕 后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。
在MySQL中可以使用连接查 询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。
如
例子1：
SELECT * FROM t1
WHERE t1.a1 NOT in (SELECT a2 FROM t2 )
优化后：
SELECT * FROM t1
LEFT JOIN t2 ON t1.a1=t2.a2
WHERE t2.a2 IS NULL
例子2：
SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
优化后：
SELECT * FROM article
inner join blog
on (article.title=blog.title AND article.content=blog.content AND article.uid=blog.uid)
练习：
1.查询所有价格大于平均价格(保留2位小数)的商品，并按价格降序排序
select * from goods where price>
(select round(avg(price),2) from goods ) order by price desc
- 查询价格大于或等于“超极本”价格的商品，并按价格降序排序
子查询返回的是一列多行
select * from goods where price >=any
(select price from goods where cate='超极本' ) order by price desc
网友评论