1.子查询
😆 实例1
查询选修了课程的学生姓名
select a.studentName from tb_student a where a.studentNo in
(select b.studentNo from tb_score b);
😆实例2
查询没有选修过课程的学生姓名。
select a.studentName from tb_student a where a.studentNo not in
(select b.studentNo from tb_score b);
😆实例3
查询班级‘计算机14-1班’所有学生的学号和姓名。
select a.studentNo,a.studentName from tb_student a where a.classNo in
(select b.classNo from tb_class b where b.className =‘计算机14-1班’);
😆实例4
查询与‘李明’同班的学生学号、姓名和班级编号。
select * from tb_student a
where a.classNo in
(select b.classNo from tb_student b where b.studentName=‘李明’)
and a.studentName != ‘李明’;
😆实例5
查询男生中比任意一个女生出生年份都晚的学生姓名和出生年份。
select * from tb_student a where a.birthday > any
(select birthday from tb_student b where b.sex =‘女’)
and a.sex =‘男’;
😆实例6
查询选修了课程号为‘31002’的学生姓名。
select a.studentName from tb_student a
where a.studentNo in
(select b.studentNo from tb_score b where b.courseNo=‘31002’);
😆实例7
查询没有选修课程号为‘31002’的学生姓名。
select a.studentName from tb_student a
where a.studentNo not in
(select b.studentNo from tb_score b where b.courseNo=‘31002’);
😆实例8
查询选修了全部课程的学生姓名
select a.studentName from tb_student a
where a.studentNo in (
select studentNo from tb_score group by studentNo
having count() = (select count() from tb_course)
);
⛳️2.联合查询
😆实例1
使用UNION查询选修了‘管理学’或者‘计算机基础’的学生学号
select b.studentNo
from tb_score a,tb_student b,tb_course c
where a.studentNo = b.studentNo
and a.courseNo = c.courseNo
and c.courseName=‘管理学’
union
select b.studentNo
from tb_score a,tb_student b,tb_course c
where a.studentNo = b.studentNo
and a.courseNo = c.courseNo
and c.courseName=‘计算机基础’;
😆实例2
使用UNION ALL查询选修了‘管理学’或者‘计算机基础’的学生学号。
select b.studentNo
from tb_score a,tb_student b,tb_course c
where a.studentNo = b.studentNo
and a.courseNo = c.courseNo
and c.courseName=‘管理学’
union all
select b.studentNo
from tb_score a,tb_student b,tb_course c
where a.studentNo = b.studentNo
and a.courseNo = c.courseNo
and c.courseName=‘计算机基础’;
😆实例3
查询选修了‘计算机基础’，但没有选修‘管理学’的学生学号。
select b.studentNo
from tb_score a,tb_student b,tb_course c
where a.studentNo = b.studentNo
and a.courseNo = c.courseNo
and c.courseName=‘计算机基础’
union
select b.studentNo
from tb_score a,tb_student b,tb_course c
where a.studentNo = b.studentNo
and a.courseNo = c.courseNo
and c.courseName<>‘管理学’;
