mysql练习答案（前15题）
1、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
select * from(
(select * from score where course_id in (select cid from course where cname = '生物')) t1
left join
(select * from score where course_id in (select cid from course where cname = '物理')) t2
on t1.student_id = t2.student_id)
where t1.num > t2.num;
2、查询平均成绩大于60分的同学的学号和平均成绩;
# 先查看每个同学的平均分数
select student_id,avg(num) from score group by student_id;
# 在筛选成绩大于60分的同学的学号和平均成绩；
# select student_id,avg(num) from score group by student_id having avg(num) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩；
# 先查看每个同学的总成绩
select student_id,sum(num) from score group by student_id;
# 学生和课程的关系只有成绩表中存在，因此要获取每个学生选择的课程，需要通过score表
select count(sid),student_id from score group by student_id;
# 将上面两步合并
select sum(num),count(sid),student_id from score group by student_id;
# 将学生的信息和成绩选课情况拼在一起
select sid,sname,sum_num ,count_stu
from student
left join
(select sum(num) sum_num,count(sid) count_stu,student_id from score group by student_id) t2
on sid = student_id;
# 还可以更严谨，那些没有选课的同学选课数和总成绩应该是0
select sid,sname,
(
CASE
WHEN sum_num is null THEN 0
ELSE sum_num
END
) as sum_num ,
(
CASE
WHEN count_stu is null THEN 0
ELSE count_stu
END
) as count_stu
from student
left join
(select sum(num) sum_num,count(sid) count_stu,student_id from score group by student_id) t2
on sid = student_id;
4、查询姓“李”的老师的个数；
# 找到所有姓李的
# 方法一
# select * from teacher where tname like '李%';
# 方法二
# select * from teacher where tname regexp '^李';
# 统计个数
select count(tid) from teacher where tname regexp '^李';
或者
select count(id) from teacher where tname like '李%';
5、查询没学过“张磊老师”课的同学的学号、姓名；
# 找到张磊老师的id
select tid from teacher where tname == '张磊老师';
# 找到张磊老师所教课程
select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师');
# 找到所有学习这门课的学生id
select student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师'));
# 找到没有学过这门课的学生对应的学生学号、姓名
select sid,sname from student where sid not in
(select student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师'))
);
6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
# 先查询学习课程id为1的所有学生
select * from score where course_id = 1;
# 先查询学习课程id为2的所有学生
select * from score where course_id = 2;
# 把这两张表按照学生的id 内连接起来 去掉只学习某一门课程的学生
select t1.student_id from
(select student_id from score where course_id = 1) t1
inner join
(select student_id from score where course_id = 2) t2
on t1.student_id = t2.student_id
# 根据学号在学生表中找到对应的姓名
select sid,sname from student where sid in (select t1.student_id from (select student_id from score where course_id = 1) t1 inner join (select student_id from score where course_id = 2) t2 on t1.student_id = t2.student_id);
7、查询学过“李平老师”所教的所有课的同学的学号、姓名；
#找到李平老师的tid
select tid from teacher where tname ='李平老师';
# 找到李平老师教的所有课程cid
select cid from course where teacher_id in (select tid from teacher where tname ='李平老师');
# 找到李平老师教的所有课程数
select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师');
# 找到所有学习李平老师课程的学生
select * from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师'));
# 查看所有学习李平老师课程的学生选课数
select student_id,count(course_id) from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id;
# 找到所有选择了李平老师所有课程的学生id
select student_id from (
select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id) t1
where t1.course_count =
(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师'));
# 找到学生的其他信息
select sid,sname from student where sid in (
select student_id from (
select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id) t1
where t1.course_count =
(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师'))
);
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
# 先找到每个学生的课程编号“1”的和课程编号“2”的成绩组成一张表
select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id
# 再找到课程编号“2”的成绩比课程编号“1”课程低的所有学生的学号
select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1
# 再找到所有学生的学号、姓名
select sid,sname from student where sid in(select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1);
9、查询有课程成绩小于60分的同学的学号、姓名；
# 先查询成绩小于60分的同学的学号
select distinct student_id from score where num < 60;
# 再查询有课程成绩小于60分的同学的学号、姓名
select sid,sname from student where sid in (select distinct student_id from score where num < 60);
10、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
# 先看看学号为1的同学都学了哪些课程
select course_id from score where student_id = 1
# 找到学习 学号为1的同学所学课程 的学号
select distinct student_id from score where course_id in (select course_id from score where student_id = 1);
# 找到学习 学号为1的同学所学课程 的学号\姓名
select sid,sname from student where sid in (select distinct student_id from score where course_id in (select course_id from score where student_id = 1));
11、课程平均分从高到低显示
select course_id,avg(num) avg_num from score group by course_id order by avg_num desc;
12、查询出只选修了一门课程的全部学生的学号和姓名；
# 查询出只选修了一门课程的全部学生的学号
select student_id,count(student_id) from score group by student_id having count(student_id) =1;
# 查询出只选修了一门课程的全部学生的学号和姓名；
select sid,sname from student where sid in (select student_id from score group by student_id having count(student_id) =1);
13、查询男生、女生的人数；
select gender,count(sid) from student group by gender;
14、查询姓“张”的学生名单；
select * from student where sname like '张%';
15、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
# 查询成绩的最高分
select course_id c1,max(num) from score group by course_id
# 查询成绩的最低分
select course_id c1,min(num) from score group by course_id
# 查询成绩的最高分和最低分拼接
select * from ( (select course_id c1,max(num) from score group by course_id) t1 inner join (select course_id c2,min(num) from score group by course_id) t2 on t1.c1 = t2.c2 );
# 格式整理
select t1.c1,t1.max_num,t2.min_num from ( (select course_id c1,max(num) max_num from score group by course_id) t1 inner join (select course_id c2,min(num) min_num from score group by course_id) t2 on t1.c1 = t2.c2 );
