MySql基础-子查询

2020-12-10 本文已影响0人小飞船1号

一、子查询定义

定义：

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