子查询

​ 子查询是出现在其他语句中的 select 语句，内部嵌套 select 语句的查询，叫做外查询或主查询

结果集可能的情况：

• 标量子查询（单行子查询）：结果集只有一行一列
• 列子查询（多行子查询）：结果集只有一列多行
• 行子查询：结果集只有一行多列
• 表子查询：结果集通常为多行多列

子查询出现的位置：

• select 后面：仅支持标量子查询
• from 后面：支持表子查询
• where 或 having 后面：标量子查询、列子查询、行子查询
• exists 后面（相关子查询）：表子查询

特点：

• 子查询写在 () 内，通常放在条件的后面
• 子查询的执行优先于主查询，主查询的条件用到了子查询返回的结果

where 或 having 中的子查询

标量子查询

• 标量子查询通常搭配单行操作符使用：>、<、>=、<=、=、<>
• 非法使用标量子查询：指子查询的结果不是一行一列，即结果无法与单行操作符进行操作

(1).where
-- 查询 classid 与 A 相同， score 比 B 高的所有 student
select classid
from student
where name = 'A';
-- 查询指定 student 的 classid

select score
from student
where name = 'B';
-- 查询指定 student 的 score

select name,score,classid
from student 
where classid = (
    select classid
    from student 
    where name = 'A'
)
and score > (
    select score
    from student
    where name = 'B'
);

(2).having
-- 查询最低 score 大于 class3 的最低 score 的 classid 和其最低 score
select min(score)
from student 
-- 查询 class3 的最低 score

select classid,min(score)
from student
group by classid
having min(score) > (
    select min(score)
    from student
);

select calssid,avg(score)
from student 
group by classid

(3).查询每个 class 中 score 高于本 avg(score) 的 student
select *
from student s 
where s.score > (
    select avg(score) a
    from student 
    group by classid
    having s.classid = student.classid)
order by classid,score desc;

列子查询

• 列子查询通常搭配多行操作符使用：in、any/some、all
• in 等价于 =any() ,not in 等价于 != all
• 比任意低/高使用 any，比所有低/高使用 all

操作符	含义
in / not in	等于列表中的任意一个
any / some	和子查询返回的某一个值比较
all	和子查询返回的所有值比较

(1).in
-- 查询 avg(score) 大于 x 的 class 和其中所有 student
select classid
from student 
group by classid
having avg(score) >= 72.5;
-- 查询 avg(score) 大于 72.5 的class

select name,score 
from student
where classid in (
    select classid
    from student
    group by classid
    having avg(score) >= 72.5
);

(2).any、some / all
-- 查询其他 class 中，比 class2 任意/所有 score 低的 student 和其 score
select distinct score
from student
where classid = 2;
-- 查询 class2 中所有出现过的 score

select name,classid,score
from student
where classid != 2
and score < any(
    select distinct score
    from student
    where classid = 2
);

行子查询

select *
from student
where (字段1，字段2) = (
    select min(字段1),max(字段2)
    from student
)

select 中的子查询

• 仅支持标量子查询

select c.*,(
    select count(*) 
    from student s
    where s.classid = c.id
) 
from class c;

from 中的子查询

• 把子查询的结果集当成一张新的表
• 新的表必须设置别名

select classid,avg(score)
from student 
group by classid;
-- 查询每个 class 的 avg(score)

select * from score_level
-- 查询 score_level 表

select a_s.*,l.name
from (
    select classid,avg(score) a
    from student 
    group by classid) a_s
inner join score_level l
on a_s.a between l.min_score and l.max_score;

exists 中的子查询（相关子查询）

-- 查询参数中是否有值，结果为0或1
exists(完整的查询语句)

(1).使用 exists 实现
select *
from class c
where exists(
    select *
    from student s
    where s.classid = c.id
);

(2).使用 in 实现 exists 
select *
from class c
where c.id in (
    select s.classid
    from student s
);