一、子查询定义
定义:
- 子查询允许把一个查询嵌套在另一个查询当中。
- 子查询用()括起来
- 子查询,又叫内部查询,相对于内部查询,包含内部查询的就称为外部查询。
- 子查询可以包含普通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