子查询的概念和分类
子查询
- 在一个select语句中,嵌入了另外一个select语句,那么被嵌入的select语句称之为子查询语句
主查询
- 主要查询的对象,第一条select语句
主查询和子查询的关系
- 子查询是嵌入到主查询中的
- 子查询是辅助主查询的,要么充当条件,要么充当数据源。
- 子查询是可以独立存在的语句,是一条完整的select语句。
子查询分类
标量子查询:子查询返回的结果是一个数据(一行一列)
- 查询大于平均身高的学生
# 先查询所有学生的平均身高
select avg(height) from students;
# 再查询身高高于平均身高的学生
select * from students where height > 172.25;
- 上面是分两次进行查询出来想要的数据的,也可以进行合并成一次查询
- 上面的查询是写死的,下面这种形式是灵活的,当再添加或者删除数据以后,都不用手动的改变:
select * from students where (select avg(height) from students);
列级子查询:返回的结果是一列(一列多行)
- 查询有学生的班级都有哪些:
# 普通查询
select classes.name from classes inner join students on classes.id = students.cls_id;
# 对查询的结果进行去重:
select distinct classes.name from classes inner join students on classes.id = students.cls_id;
# 子查询
select classes.name from classes where id in (select cls_id from students);
行子查询:返回的结果是一行(一行多列)
- 查找班级年龄最大,身高最高的学生
- 行元素:将多个字段合成一个行元素,在行级子查询中会使用到行元素
select max(age), max(height) from students;
select * from students where (age,height) = (select max(age), max(height) from students);
- 查询结果为空说明这两个没有出现在同一个人身上
表级子查询:返回结果是多行多列
- 查询学生与班级对应的信息
内连接查询实现
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
子查询实现:
select t.sname,t.cname from (select s.name as sname,c.name as cname from students as s inner join classes as c on s.cls_id = c.id) as t;
- 一般不使用,查询效率相对费时间
- 说明:发现很多表级子查询的语句,都是可以使用连接查询实现的,此时推荐使用连接查询,因为连接查询的语句更简洁,逻辑更清晰。
子查询中出现的关键字
带any关键字的子查询:
- any | some 任意一个
- 格式:主查询 where 列 = any(列子查询)
- 在条件查询的结果中匹配任意一个即可,等价与 in
- any 关键字表示满足其中任意一个条件,它允许创建一个表达式对子查询的返回值列进行比较,只要满足内层子查询中的任意一个比较条件,就返回一个结果作为外层查询条件。
带 all 关键字的子查询:
- all:
- 格式:主查询 where 列=all(列子查询):等于里面所有
- 格式:主查询 where 列<>all(列子查询):不等于其中所有
- all与any有点类似,只不过带all关键字的子查询返回的结果需同时满足所有内层查询条件。
带 in 关键字的子查询
- in 范围:
- 格式:主查询 where 条件 in (列子查询)
- 在查询过程中,首先汇之星内层子查询。
- select 语句中还可以使用not in关键字,其作用正好与in相反。
带 exists 关键字的子查询:
- exists关键字后面的参数可以是任意一个子查询,这个子查询的作用相当于测试。它不产生任何数据,只返回true或flase,当返回值为true时,外层查询才会执行。
- 需要注意的是:exists关键字比in关键字的运行效率高,所以,在实际开发中,特别是大数据量时,推荐使用exists关键字。
带比较运算符的子查询
- 在前面的子查询中使用了“>”比较运算符,子查询中还可以使用其他的比较运算符,如“<”“>=”“<=”“=”“!=”等。