数据库的查询操作（子查询）

目录

一、作用在select语句

二、作用在where语句

三、作用在from语句

四、in 与exists 的使用

1、in的使用

2、exists的使用

in 和 exists 谁性能最高?

---

子查询：嵌套在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