mysql学习训练记录及笔记（二）

接着训练一

mysql学习训练记录及笔记（一）

mysql学习训练记录及笔记（二）

mysql学习训练记录及笔记（三）

I、sql训练

21、查询不同老师所教不同课程平均分从高到低显示 

select
    t3.t_id,
    t3.t_name,
    avg(t1.s_score) as avg_score
from
    score t1 
    left join course t2 on t1.c_id = t2.c_id 
    left join teacher t3 on t2.t_id = t3.t_id
group by
    t3.t_id,
    t3.t_name
order by
    avg_score desc;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

这里学习到rank（）函数

select * from student t1 join
(select s_id,s_score,c_id, (rank() over(partition by c_id order by s_score desc)) as 排名
from score where 排名 between 2 and 3 ) as t2 on t1.s_id =t2.s_id

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select t2.c_id,t2.c_name,
(sum(case when s_score between 85 and 100 then 1 else 0 end)/count(s_score))*100 a,
(sum(case when s_score <85 and s_score>70 then 1 else 0 end)/count(s_score))*100 b,
(sum(case when s_score between 60 and 70 then 1 else 0 end)/count(s_score))*100 c,
(sum(case when s_score between 0 and 60 then 1 else 0 end)/count(s_score))*100 d
from score t1 join course t2 on t1.c_id=t2.c_id
group by t1.c_id

24、查询学生平均成绩及其名次

select avg(s_score), rank() over(order by avg(s_score)) from score group by s_id 

25、查询各科成绩前三名的记录 

select * 
from
(select c_id,s_id,dense_rank() over(partition by c_id order by s_score) as ran from score) t1
where t1.ran<4

26、查询每门课程被选修的学生数

select c_id,count(s_id) from score group by c_id

27、查询出只有两门课程的全部学生的学号和姓名 

select s_id,s_name from student where s_id in
(select s_id from score group by s_id having  count(s_score) = 2)

28、查询男生、女生人数 

select s_sex,count(s_id) from student group by s_sex;

29、查询名字中含有"风"字的学生信息

select * from student where s_name like '%风%'

30、查询同名同性学生名单，并统计同名人数

select s_name,count(s_id) as count_num from student group by s_name having count(s_id)>1

31、查询1990年出生的学生名单

select * from student where s_birth like '1990%'

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 

连续两个条件排序，只需要加个条件即可

select avg(s_score) from score group by c_id order by avg(s_score) desc,c_id asc;

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 

select t1.s_id,t1.s_name,avg(t2.s_score) as avg_score from student t1 join score t2 on t1.s_id=t2.s_id group by t1.s_id having avg_score >=85

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select t1.s_name,t2.s_score from student t1 join score t2 on t1.s_id =t2.s_id join course t3 on t2.c_id =t3.c_id where t3.c_name='数学' and t2.s_score <60

 35、查询所有学生的课程及分数情况；

select t1.s_id,t2.s_score as 语文,t3.s_score as 数学, t4.s_score as 英语,(select sum(s_score) from score group by s_id having s_id=t1.s_id) as 总分  
from student t1 
left join score t2 on t1.s_id=t2.s_id and t2.c_id='01' 
left join score t3 on t1.s_id=t3.s_id and t3.c_id='02'
left join score t4 on t1.s_id=t4.s_id and t4.c_id='03'

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select t3.s_name,t2.c_name,t1.s_score from score t1 join course  t2 on t1.c_id=t2.c_id join student t3 on t1.s_id = t3.s_id where s_score >70

37、查询不及格的课程

select * from course t1 join  score t2 on t1.c_id=t2.c_id where t2.s_score < 60

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select t2.s_id,t2.s_name from score t1 join student t2 on t1.s_id=t2.s_id where t1.c_id='01' and t1.s_score>80

39、求每门课程的学生人数 

select c_id,count(s_id) from score group by c_id

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩 

select * from
(
select t1.*,t2.s_score,rank() over(order by t2.s_score desc) ran
from student t1 join score t2 on t1.s_id=t2.s_id 
where t2.c_id in (select c_id from course  natural join teacher where t_name = '张三')
) ta where ta.ran =1

II、总结一下

1、前面二十道会了之后，后面几乎没有什么难度，主要在于思路，已经尽量减少多表关联，增加查询速度

2、开窗函数，这是mysql新特性，可以添加rank等，解决排序等问题