04. 子查询

目标

• 能够写出子查询的SQL语句

---

1. 子查询的介绍

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询，外层的第一条 select 语句为主查询

• 主查询和子查询的关系
  1. 子查询是嵌入到主查询中
  2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
  3. 子查询是可以独立存在的语句,是一条完整的 select 语句

2. 子查询的使用

​ 标量子查询: 子查询得到的结果是一行一列

​ 列子查询：子查询得到的结果是一列数据(一列多行)

​ 行子查询：子查询返回的结果是一行多列

1. 子查询充当条件

例1. 查询王昭君的成绩，要求显示成绩（标量子查询）

select * from scores where studentNo = (select studentNo from students where name='王昭君')

image-20230210155851705

例2. 查询18岁的学生的成绩，要求显示成绩（列子查询）

select * from scores where studentNo in (select studentNo from students where age=18)

image-20230210155941355

例3：查询和王昭君同班、同龄的学生信息（行子查询）

select * from students where (class, age) = (select class, age from students where name='王昭君')

image-20230210160009289

例4：查询大于平均年龄的学生

SELECT * FROM students where age > (select avg(age) from students)

image-20230210161158081

2. 子查询充当数据源

例1：查询数据库和系统测试的课程成绩

select * from scores s 
inner join (select * from courses where name in ('数据库 ','系统测试')) c 
on s.courseNo = c.courseNo

image-20230210160218339

# 另一种写法
SELECT * FROM courses AS cu LEFT JOIN scores AS sc ON cu.`courseNo`=sc.`courseNo` WHERE cu.`name` IN('数据库','系统测试')

image-20230512195209144

例2: 查询年龄在18-20之间的学生的成绩

SELECT st.name as st_name,st.age,co.name as co_name,sc.score 
FROM (select * from students where age BETWEEN 18 and 20)  AS st
LEFT JOIN scores AS sc ON st.studentNo = sc.studentno 
LEFT JOIN courses AS co ON co.courseNo = sc.courseNo

image-20230210161044450

# 另一种写法
select st.`name`,st.`age`,cu.`name`,sc.`score` from students as st 
left join scores as sc on st.`studentNo`=sc.`studentno` 
left join courses as cu on sc.`courseNo`=cu.`courseNo` 
where st.`age` between 18 and 20

image-20230512195629868

3. 子查询-特定关键字

• in 范围
  • 格式: 主查询 where 条件 in (列子查询)
• any|some 任意
  • 格式: 主查询 where 列 = any(列子查询)
  • 必须与一个比较操作符一起使用
  • any关键词可以理解为“对于子查询返回的列中的任一数值，如果比较结果为true，则返回true”
  • any 可以与=、>、>=、结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
• all 所有
  • 格式: 主查询 where 列 = all(列子查询)
  • 必须与一个比较操作符一起使用
  • all 关键词可以理解为“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”
  • all可以与=、>、>=、结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

例1.查询18岁的学生的成绩，要求显示成绩（列子查询）

-- 查询18岁的学生的成绩，要求显示成绩（列子查询）
select studentNo from students where age=18;
select score from scores where studentNo in (select studentNo from students where age=18);

select st.*,sc.* from scores as sc 
right join students  st on sc.studentNo=st.studentNo where sc.studentNo 
in(select studentNo from students where age=18) 

-- =any 和 in 等价
select score from scores where studentNo = any(select studentNo from students where age=18);

-- SOME: 是 any 的别称, 很少用
select score from scores where studentNo = some(select studentNo from students where age=18);

-- != all 和 not in 等价
select score from scores where studentNo not in (select studentNo from students where age=18);
select score from scores where studentNo != all(select studentNo from students where age=18);

4. 小结

• 子查询是一个完整的SQL语句，子查询被嵌入到一对小括号里面