目录
子查询:嵌套在SQL查询语句中间的查询,就叫做子查询
select * from student_info where id >= (select id from student_info order by id limit 2,1) limit 10;
上面的SQL语句是查询:从投影下标2开始,分页获取学生表的10条数据 (MySQL分页时,推荐使用这种子查询模式,性能远高于直接使用limit分页)
需要注意的是:在单个列使用比较运算符时,子查询的结果应该是单条单列的数据
例如:
select id from student_info order by id limit 2,1;/* id 代表只返回1列 1 代表只返回1条数据*/
注意:一般在子查询中,程序先运行在嵌套在最内层的子查询语句,再运行外层查询。因此在写子查询语句时,可以先测试下内层的子查询语句是否输出了想要的内容,再一层层往外测试,增加子查询正确率。否则多层的嵌套使语句可读性很低
子查询几乎可以出现SQL查询语句的任何地方:
select 列名…… from 表的表名 [where 查询条件] [group by 分组字段] having [聚合条件] [order by 排序字段 desc/asc] [limit 分页条件]
但一般最多出现在:
-
select 后
-
from 后
-
where后
一、作用在select语句
特点:放置在select语句中的子查询,只能返回单行以及单列数据
select s.id,s.student_name,s.age,(select class_name from class_info where id = s.fk_class_id) as class_name from student_info as s;
二、作用在where语句
特点:放置在where语句之后,主要用于筛选数据
select * from student_info where fk_class_id in (select id from class_info where class_name like 'J%') and age > 20;
上述SQL语句:查询J开头的班级,年龄大于20岁的所有学生
select * from student_info where age > (select avg(ifnull(age,0)) from student_info);
上述SQL语句:查询年龄大于平均年龄的所有学生
三、作用在from语句
特点:放置在from语句中的子查询,大家可以把它想象成一张表的数据,可以多行多列
select * from (select s.id,s.student_name from student_info as s where s.fk_class_id = (select id from class_info where class_name = 'J76')) as stu,score_info as sco where stu.id = sco.fk_student_id;
上述SQL语句:查询J76班级学生的学生信息,以及考试成绩
上述SQL语句,子查询有:
select id from class_info where class_name = 'J76';select s.id,s.student_name from student_info as s where s.fk_class_id = (select id from class_info where class_name = 'J76')
子查询分为:相关子查询,非相关子查询
相关子查询:子查询在查找时,需要使用外部查询的数据。外部查询查询一次,子查询就需要执行一次
非相关子查询:子查询在查找时,不需要使用外部查询的数据,子查询总共执行一次,执行完毕后后将值传递给外部查询
在子查询过程中,使用较为频繁的关键字有in和exists,而且这2个也是面试常问到的点。
四、in 与exists 的使用
1、in的使用
IN 操作符允许我们在 WHERE 子句中规定多个值
select * from student_info where fk_class_id in (select id from class_info where class_name like 'J%');
上述语句,表示:查询以”J”开头的班级名,班级中的所有学生
效果等同于:
fk_class_id in (1,2,3,4) === fk_class_id = 1 or fk_class_id = 2 or fk_class_id = 3 or fk_class_id = 4;
IN 操作符执行顺序:
(1)首先查询子查询的表
(2)将内表和外表做一个笛卡尔积
(3)使用where条件进行筛选
所以相对而言,内表比较小的时候,in的速度较快!
2、exists的使用
EXISTS 运算符用于判断查询子句是否有记录,如果有一条或多条记录存在返回 True,否则返回 False。
select * from student_info as s where exists (select id from class_info as c where c.id = s.fk_class_id and c.class_name like 'J%');
上述语句,表示:查询以”J”开头的班级名,班级中的所有学生
EXISTS 操作符执行顺序:
(1)查询外表,遍历循环外表
(2)将外表的数据代入到子查询中,判断子查询返回True还是False
(3)如果返回True,则将外表循环的数据加入到返回结果集中;否则,不加入
(4)最后,将结果集中数据返回给用户
所以相对而言,内表比较多的时候,exists的速度较快!
in 和 exists 谁性能最高?
当内部表 和 外部表 数据量一样,两者几乎差不多
当内部表 数据量 大于 外部表时 exists 的性能高于in
当内部表 数据量 小于 外部表时 in 的性能高于exists