原题目数据库请查看作者资源免费下载
(https://download.csdn.net/download/MeiJin_/86403712)
多表查询的思路
类似于生活日常中解决问题方式 第一步干什么第二步干什么
连表操作就是 先将所有涉及到结果的表全部拼接到一起形成一张大表 然后从大表中查询数据
1、查询所有的课程的名称以及对应的任课老师姓名
(看题 首先看题目我们会需要两张表 一个课程 一个教师 只需要拼接在一起获取到信息即可)
select course.cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.tid
2、查询平均成绩大于八十分的同学的姓名和平均成绩
select student_id,avg(num) from score GROUP BY student_id; # 拿到分数表获取每个学生的平均分
select student_id,avg(num) from score GROUP BY student_id HAVING AVG(num) > 80; # 获取平均分大于80的同学ID
select * from student INNER JOIN (select student_id,avg(num)as avg_num from score # 注意如果不命名则无法取值
GROUP BY student_id HAVING AVG(num) > 80)as t1 ON student.sid = student_id; # 拿学生的分数id去跟学生表对接
select student.sname, t1.avg_num from student inner join(select student_id,avg(num))as avg_num
FROM score GROUP BY student_id HAVING AVG( num ) > 80 ) AS t1 ON student.sid = student_id; # 我们只需要学生的姓名以及成绩即可
3、查询没有报李平老师课的学生姓名
select tid from teacher where tname ='李平老师'; # 先获取到老师的编号 2
select cid from course WHERE teacher_id = (select tid from teacher where tname ='李平老师') # 再通过老师的编号去获取课程编号 2 4
select DISTINCT student_id FROM score WHERE course_id in # 根据课程编号 去分数表中筛选出选择了课程的学生编号 DISTINCT 去重复
(select cid from course WHERE teacher_id = (select tid from teacher where tname ='李平老师'))
select sname from student WHERE sid not IN
(select DISTINCT student_id FROM score WHERE course_id in # 通过学生的编号 在分数表中取反 看没有选择李平老师的课程学生姓名!
(select cid from course WHERE teacher_id = (select tid from teacher where tname ='李平老师')))
4、查询没有同时选修物理课程和体育课程的学生姓名
select cid from course WHERE cname in ('物理','体育') # 先获取到课程的ID 23
select * from score where course_id in (select cid from course WHERE cname in ('物理','体育')) # 在分数表中 获取到选择了课程的id
select score.student_id from score WHERE course_id in (select cid from course WHERE cname in ('物理','体育'))
GROUP BY score.student_id HAVING COUNT(score.course_id) = 1 # 通过分数表 获取到选择课程的学生id 只要课程只出现过一个的ID
select sname from student where sid IN(select score.student_id from score WHERE course_id in # 通过学生表获取名称
(select cid from course WHERE cname in ('物理','体育')) GROUP BY score.student_id HAVING COUNT(score.course_id) = 1)
5、查询挂科超过两门(包括两门)的学生姓名和班级
select * from score WHERE num < 60 # 先获取分数少于60分不及格的
select student_id from score WHERE num < 60 GROUP BY student_id HAVING COUNT(course_id) >=2 # 按照学生的ID 分组出超过两门或者等于两门的
select * from class INNER JOIN student on class.cid = student.class_id # 链接班级表和学生表
SELECT class.caption, student.sname FROM class INNER JOIN student on class.cid = student.class_id WHERE student.sid in
(SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count(course_id) >=2) # 通过拼接的表 拿到学分少于60不及格的班级以及姓名
技术小白记录学习过程,有错误或不解的地方请指出,如果这篇文章对你有所帮助请
点点赞收藏+关注
谢谢支持 !!!