mysql-50道-经典测试题

• 题目快速导航
• • 题1：查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
  • 题2：查询"01"课程比"02"课程成绩低的学生的信息及课程分数（-类比重复-题1了）
  • 题3：查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
  • 题4：查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的)
  • 题5：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
  • 题6：查询"李"姓老师的数量
  • 题7：查询学过"张三"老师授课的同学的信息
  • 题8：查询没学过"张三"老师授课的同学的信息
  • 题9：查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
  • 题10：查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
  • 题11：查询没有学全所有课程的同学的信息

select student.*,t1.score as score01,t2.score as score02 
from student, 
(select stuId,score from stuScore where stuScore.couId=1) as t1,
(select stuId,score from stuScore where stuScore.couId=2) as t2 
where t1.stuId = t2.stuId and t1.score>t2.score 
and student.stuId=t1.stuId;

select student.*,t1.score as score01,t2.score as score02 
from student, 
(select stuId,score from stuScore where stuScore.couId=1) as t1,
(select stuId,score from stuScore where stuScore.couId=2) as t2 
where t1.stuId = t2.stuId and t1.score 
题3：查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 
select student.stuId,student.stuName,round(avg(stuScore.score),2) as Averge from 
student inner join stuScore 
on student.stuId=stuScore.stuId 
group by student.stuId 
having AVG(stuScore.score)>=60;

 
题4：查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 – (包括有成绩的和无成绩的) 
select student.stuId,student.stuName,
(case when AVG(sc1.score) is null then 0 else AVG(sc1.score)end) from 
student
left join stuScore as sc1 
on student.stuId=sc1.stuId 
group by student.stuId 
having AVG(sc1.score)<60 or AVGG(sc1.score) is null;

 
题5：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 
#土方法，排序上有点小问题
select student.stuId,student.stuName,t1.score as score01,t2.score as score02,t3.score as score03,
((case when t1.score is null then 0 else t1.score end)+(case when t2.score is null then 0 else t2.score end)+(case when t3.score is null then 0 else t3.score end)) as sumScore,
((case when t1.score is null then 0 else1 end)+(case when t2.score is null then 0 else 1 end)+(case when t3.score is null then 0 else 1 end)) as count 
from student 
left join (select * from stuScore where couId=1) as t1 
on student.stuId=t1.stuId 
left join (select * from stuScore where couId=2) as t2 
on t1.stuId = t2.stuId 
left join (select * from stuScore where couId=3) as t3 
on t1.stuId=t3.stuId;

#新方法，排序上无问题
select student.stuId,student.stuName,COUNT(stuScore.couId) as count,SUM(stuScore.score) as sumScore 
from student 
left join stuScore 
on student.stuId=stuScore.stuId 
group by student.stuId;
#优化一下，可以避免SUM结果出现null的情况，使用分支语法就可
select student.stuId,student.stuName,COUNT(stuScore.couId) as count,(case when SUM(stuScore.score) is null then 0 else SUM(stuScore.score) end) as sumScore 
from student 
left join stuScore 
on student.stuId=stuScore.stuId 
group by student.stuId;

 
题6：查询"李"姓老师的数量 
select count(*) as count from teacher where teaName like '李%';

 
题7：查询学过"张三"老师授课的同学的信息 
select student.*,course.couId,teacher.teaName 
from teacher 
left join course on teacher.teaId=course.teaIdteaId 
left join stuScore on stuScore.couId=course.couId 
left join student on student.stuId=stuScore.stuId 
where teacher.teaName='张三';

 
题8：查询没学过"张三"老师授课的同学的信息 
select student.* from student 
where student.stuId 
not in (select stuScore.stuId from stuScore 
where stuScore.couId 
in (select course.couId from course left join teacher on course.teaId=teacher.teaId where teacher.teaName='张老师'));

 
题9：查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 
#法一：偏算术算法
select student.* from student 
where stuId 
in (select stuId from stuScore 
where couId in (1,2) 
group by stuId 
having count(*)!=1);
#法二：偏表表联系
select student.* from student 
where stuId 
in(select t1.stuId from (select * from stuScore where couId=1) as t1 
inner join (select * from stuScore where couId=2) as t2 
on t1.stuId=t2.stuId );

 
题10：查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 
#法一：偏分表联系
select student.* from student 
where stuId 
in (select stuId from stuScore where couId=1) 
and stuId 
not in (select stuId from stuScore where couId=2);
#法二：group by 分组往下探头
#（每个学生都不存在只有一个成绩，在经典50题的表适用）
select student.* 
from student 
left join stuScore 
on student.stuId=stuScore.stuId 
group by student.stuId 
having count(*)<=2 and sum(couId)=4;#（1+3=4）
#（当存在只有一个成绩的时候，就会冲昏‘3’=‘1+2’）
#这里给couId加1，加强和运算后的独立
#当一位数时：1-2-3--+1-->2-3-4
#当二位数时：1+2-2+3-1+3--+1-->2+3-3+4-2+4-->5-7-6
#此时一位和二位成绩存在已经无关，不能冲昏，【完成】
select student.* 
from student 
left join stuScore 
on student.stuId=stuScore.stuId 
group by student.stuId 
having count(*)<=2 and sum(stuScore.couId+1) in (2,4,6);


 
题11：查询没有学全所有课程的同学的信息 
#比较优的方法
select student.* 
from student 
left join stuScore 
on student.stuId=stuScore.stuId 
group by student.stuId having count(couId)<(select count(*) from course);
# having count(couId)<(select count(*) from course); 
# having SUM(couId)<(select SUM(couId) from course);
#为什么不能直接count(course.couId),因为course表没在最外层的from范围内
#