mysql语句训练_mysql查询语句训练

mysql查询练习题：用到了group by,case when

Part 1

学生表

成绩表

1.查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低

$select s.name,g.score from student,grade where s.id=g.id and kemu=math;

2.统计每个学生的总成绩,显示字段：姓名，总成绩

$select s.name,sum(score) from student s,grade g where s.id=g.id group by name;

3.统计每个学生的总成绩(由于学生可能有重复名字),显示字段：学生id，姓名，总成绩

select s.id,s,name,sum(g.score) from student s,grade g where s.id=g.id group by id;

4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩

SELECT c.id,a.name, c.kemu, c.score FROM grade c,student a,

(SELECT b.kemu, MAX(b.score) as max_score FROM grade b GROUP BY kemu) t WHERE c.kemu = t.kemu AND c.score = t.max_score AND a.id = c.id;

Part02

学生表

成绩表

5.计算学生平均分数:计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成绩

$select s.id,s.name,avg(g.score) from student s ,grade g where s.id=g.id group by s.id;

6.统计各科目成绩:计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学，英语，总分，平均分

#这里涉及的新知识为：case when以及sum(case when)，延伸包含count (case when)

使用case when 语法把科目字段分解成具体的科目：语文，数学， 英语

不加sum(case when)：统计的还是每一行记录

$select s.id as 学号,s.name as 姓名,(case when g.kemu='语文' then score else 0 end) as

语文,(case when g.kemu='数学' then score else 0 end) as 数学,(case when g.kemu='英语' then

score else 0 end) as 英语 from student s,grade g where s.id=g.id;

加sum (case when)，对应的要加group by按照人员分组，然后统计每一门的成绩

$select s.id as 学号,s.name as 姓名,sum(case when g.kemu='语文' then score else 0 end) as

语文,sum(case when g.kemu='数学' then score else 0 end) as 数学,sum(case when g.kemu='英语' then

score else 0 end) as 英语 from student s,grade g where s.id=g.id group by s.id;

tip :如果成绩表中有相同的两条记录时，比如一个学生考了语文两次的成绩，那么sun (case when)统计一个学生各个课程的成绩就会有问题了,如下图

整条查询要求对应的sql语句:

$SELECT a.id as 学号, a.name as 姓名,

sum(case when b.kemu='语文' then score else 0 end) as 语文,

sum(case when b.kemu='数学' then score else 0 end) as 数学,

sum(case when b.kemu='英语' then score else 0 end) as 英语,

sum(b.score) as 总分 , avg(b.score) as 平均分

FROM student a, grade b

where a.id = b.id GROUP BY b.id, b.id

7.每门课程平均成绩:列出各门课程的平均成绩，要求显示字段：课程，平均成绩

$select kemu,avg(score) from grade group by kemu;

8.成绩排名:列出数学成绩的排名， 要求显示字段：学号，姓名，成绩，排名

#在统计排名时，用到一个自增变量的写法，注意！

$select s.id,s.name,g.score,@paiming :=@paiming +1

from student s,grade g,(select @paiming:=0) r

where s.id=g.id and g.kemu='数学' order by g.score desc ;