mysql查询语⽂⽐数学成绩⾼的学号_MySQL数据库⼊门（九）多表复杂查询练习及讲解-中...
建表与数据准备
j建库和建表的操作请参考“多表复杂查询练习及讲解-上”的内容，在此不再赘述。
use day5; # 切换day5为当前数据库
show tables;
+----------------+
| Tables_in_day5 |
+----------------+
| course |
| score |
| student |
| teacher |
+----------------+
练习题
第六题：
查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分：
这题很简单，不需要连表，只要课程id、最⾼分和最低分，⽤分组函数就能搞定：
select course_id 课程id,max(num) 最⾼分,min(num) 最低分
from score
group by course_id;
第七题：
查询⾄少有⼀门课与学号1所学课程相同的学⽣学号和姓名：
1.从socre表中查出学号1学的全部课程id：
select course_id
from score
where student_id=1;
2.再从score表中含有学号1课程的记录，记得要去重⽽且要去掉学号1：
select distinct student_id
from score
where student_id!=1 and course_id in(
select course_id
from score
where student_id=1);while循环基本形式
3.最后与student表拼接，显⽰学号和姓名，注意要right join(因为右表记录不能遗漏的)，另外可以加上order by学号更加美观：select sid 学号,sname 姓名
from student
as a right join
(select distinct student_id
from score
where student_id!=1 and course_id in(
select course_id
from score
where student_id=1))
as b on a.sid=b.student_id
order by 学号;
第⼋题：
查询语⽂成绩⽐数学成绩好的学⽣学号及名字：
这道题这⾥讲⼀个新东西，⽤变量保存查询结果及调⽤变量保存的查询结果。请注意变量的⽤法——@变量名字：
1.⾸先查询course表中语⽂的课程id和数学课程的id：
select @course1:=cid # 查询语⽂课的id，保存到变量course1中
from course
where cname='语⽂';
select @course2:=cid # 查询数学课的id，保存到变量course2中
from course
where cname='数学';
mysql面试题及讲解
2.做⼀个学号、语⽂课成绩、数学课成绩的连表：
select a.学号,a.课程1,b.课程2
from(
select student_id 学号,num 课程1
from score
where course_id=@course1) a
西门子smart编程软件inner join(
select student_id 学号,num 课程2
from score
where course_id=@course2) b
on a.学号=b.学号
where 课程1>课程2;
3.最后与student表拼接，显⽰学号和姓名，注意要right join(因为右表记录不能遗漏的)，另外可以加上order by学号更加美观：
select sid 学号,sname 姓名
from student
right join(
select a.学号,a.课程1,b.课程2
from(
select student_id 学号,num 课程1
from scorephp入门工作好难
where course_id=@course1) a
inner join(
select student_id 学号,num 课程2
from score
where course_id=@course2) b
java配置classpathon a.学号=b.学号
where 课程1>课程2) c
on student.sid=c.学号
order by 学号;
注意：这⼀题运⽤了⼀个新技巧，临时变量保存临时查询结果。为了提⾼代码的复⽤率，在经常变更查询内容但不变更查询条件的情况下，⽤临时变量单独保存查询内容会很⽅便。另外就是如果发现某些查询的结果需要反复⽤到，那么可以先将该结果保存到临时变量中，这样可以减少查表次数从⽽提⾼查询效率。
第九题：
查平均成绩⼤于60分的学⽣学号、姓名、平均成绩，结果按成绩从⾼到低排序：
这⼀题写SQL代码难度不⼤，难的是要出题中隐含条件。在这道题中要出平均成绩⼤于60分的学⽣，但在数据库存在有些学⽣部分课程缺考的情况，缺考的课程应该按0计算！所以不能直接⽤avg(num)>60来作为having条件。
1.⾸先查course表得出总共有⼏门课程：
select @total:=count(*)*60 # 计算平均成绩⼤于60考分的总数
from course;
2.再从score表查总成绩⼤于@total的学⽣，获取学号和平均成绩：
select student_id 学号,avg(num) 平均成绩
from score
group by student_id
having sum(num)>@total
order by 平均成绩 desc;
3.最后连接student表，显⽰学⽣学号、姓名、平均成绩：
select 学号,sname 姓名，平均成绩
from student a
inner join(
select student_id 学号,avg(num) 平均成绩
from score
group by student_id
having sum(num)>@total
order by 平均成绩 desc) b
on a.sid=b.学号;
第⼗题：
查询所有学⽣的学号、姓名、总成绩、课程数，按总成绩从⾼到低排序：
这⼀题写SQL代码难度不⼤，但还是有很多⼈会做错。请注意题⽬要求是查询所有学⽣的信息，连表的时候必须使⽤student左连接右查询⼦表，这样才能查到所有学⽣的信息！
请实际运⾏⼀下代码，就会发现有⼀个学⽣是没成绩的。假如⽤的是inner join那么答题结果就不会包含没成绩的学⽣。
select student.sid 学号,student.sname 姓名,b.总成绩,b.课程数
from student
left join
(select student_id 学号,count(num) 课程数,sum(num) 总成绩
from score
group by student_id)
as b on student.sid=b.学号
order by 总成绩 desc;
第⼗⼀题：
查询没上过“江成”⽼师课的学⽣学号和姓名；
这⼀题有难度，要避开两个坑：⼀是要想到⼀名⽼师可能教多门课；⼆是要会⽤逆向思维，从score表中出所有上过“江成”⽼师课的学⽣，然后再从studeng表中排除那些学⽣，剩下的就是没上过“江成”⽼师课的学⽣。
1.从teacher表中出“江成”⽼师的id，再去course表中出“江成”⽼师教的全部课程：
select cid
from course
where teacher_id=(
select tid
from teacher
where tname='江成');
eclipse怎么改字体2.从score表中出所有学过“江成”⽼师’的学⽣id，另外要记得⽤distinct去重：
select distinct student_id
from score
where course_id in (
select cid
from course
where teacher_id=(
select tid
from teacher
where tname='江成'));
3.从student表中出所有不在第⼆步表中的学⽣：
select sid 学号,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='江成')));
第⼗⼆题：
查询学过“江成“⽼师所有课程的学⽣学号和姓名：
这⼀题⽐上⼀题还要难，解题思路是⾸先要出这位⽼师教的所有课程(可能不⽌⼀门课)，其次计算这位⽼师教了⼏门课；然后出学过这位⽼师课程的学⽣另外排除掉课程数量不⾜的学⽣；最后再拼接学⽣表列出学号和姓名。
1.出“江成”⽼师教的全部课程：
select cid
from course
where teacher_id=(
select tid
from teacher
where tname='江成');
2.算出“江成”⽼师教⼏门课：
select @total:=count(*)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。