MySQL查询篇——（三）子查询

1.=

#查询和‘陈樱’同学同龄的学生信息
SELECT * from student where age = (
select age from student WHERE sname = '陈樱'
)

2.in

#查询课程成绩不及格的选修课课程信息
select * from course where cno in(
select distinct cno from sc where degree < 60
)

#in或者not in 一般来说查询效率低，采用多表连接
select c.* from course c join sc on c.cno = sc.cno
where sc.degree < 60

3.all

#ALL表示必须满足子查询结果的所有记录
#查询sc表里成绩最高的记录
select * from sc where degree >= all (SELECT degree from sc)

#查询sc表里成绩最低的记录
select * from sc where degree <= all (SELECT degree from sc)

4.any

#any表示满足子查询结果的任意一条记录即可，和some一样
#查询选择'c01课程的插件高于'c02'的成绩的学生的学号
select * from sc where cno = 'c01' and degree >
ANY(select degree from sc where cno = 'c02')

select * from sc where cno = 'c01' and degree >
some(select degree from sc where cno = 'c02')

5.EXISTS

#显示已经选修了课程的学生信息
#多表查询
select distinct s.* from sc left join student  s on sc.sno = s.sno

#exists查询
select * from student s where exists (select distinct s.* from sc where sc.sno = s.sno)

#没有选修的同学
select * from student s where not exists (select distinct s.* from sc where sc.sno = s.sno)

#查询选修了c03课程的同学
select * from student s where exists (select distinct s.* from sc where sc.sno = s.sno and sc.cno = 'c03')

配套视频：0-查询篇-课程导读_哔哩哔哩_bilibili

sql文件：百度网盘 请输入提取码