参考https://www.cnblogs.com/yoyoketang/
student表：
grade表：
1.查询所有学生的数学成绩，显示学生姓名name，分数由高到低
SELECT a.name,b.score from sentiment.student a left join sentiment.grade b on a.id = b.id where b.kemu=‘数学‘ order by b.score DESC; SELECT a.name,b.score from sentiment.student a,sentiment.grade b where a.id = b.id AND b.kemu=‘数学‘ order by b.score DESC;
2.统计每个学生的总成绩，显示字段：姓名，总成绩
SELECT a.name,sum(b.score) as SUM from sentiment.student a left join sentiment.grade b on a.id = b.id group by a.name; SELECT a.name,sum(b.score) as sum from sentiment.student a,sentiment.grade b where a.id=b.id group by a.name;
3.统计每个学生的总成绩（由于学生可能有重复名字），显示字段：学生id，姓名，总成绩
SELECT a.name,a.id,sum(b.score) as sum from sentiment.student a,sentiment.grade b where a.id=b.id group by a.name,a.id;
4.列出各门课程成绩最好的学生，要求显示字段: 学号，姓名，科目，成绩
SELECT kemu,MAX(score) as score from sentiment.grade group by kemu; SELECT a.name,b.id,b.kemu,b.score FROM sentiment.student a,sentiment.grade b, (SELECT kemu,MAX(score) as scorem from sentiment.grade group by kemu) t where b.kemu=t.kemu and b.score=t.scorem and a.id=b.id
5.计算每个人的平均成绩，要求显示字段: 学号，姓名，平均成绩
SELECT a.id,a.name,avg(b.score) as score_avg from sentiment.student a,sentiment.grade b where a.id=b.id group by a.id;
6.列出各门课程的平均成绩，要求显示字段：课程，平均成绩
SELECT kemu,AVG(score) from sentiment.grade group by kemu;
7.列出数学成绩的排名，要求显示字段：学号，姓名，成绩，排名
SELECT id,kemu,score from sentiment.grade where kemu= ‘数学‘ order by score DESC;
SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= ‘数学‘) c
where a.id = c.id order by c.score DESC;
8.列出数学成绩前3名的学生
SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= ‘数学‘) c
where a.id = c.id order by c.score DESC limit 3;
9.查询数学成绩第2和第3名的学生
SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= ‘数学‘) c
where a.id = c.id order by c.score DESC limit 1,2;
10.查找每科成绩前2名，显示id，姓名，科目，分数
SELECT id,kemu,score from sentiment.grade ORDER by kemu,id DESC;
SELECT COUNT(*) FROM sentiment.grade WHERE kemu =‘数学‘ and score>98;
SELECT t1.id,t1.kemu,t1.score FROM sentiment.grade t1
WHERE (SELECT COUNT(*) FROM sentiment.grade t2 WHERE t1.kemu=t2.kemu AND t2.score>t1.score)<2
ORDER BY t1.kemu,t1.score DESC;
说明：t2.score>t1.score，t1.score放入具体的数值。
t1.score=98时，count=0，该条记录取出；
t1.score=97时，count=1，该条记录取出；
t1.score=86时，count=2，该条记录不被取出；
...
所以取出的数据为前两位。
11.查询第3名到后面所有的学生数学成绩
SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= ‘数学‘) c
where a.id = c.id order by c.score DESC limit 2,999;
12.统计英语课程少于80分的，显示学号id，姓名，科目，分数
SELECT id,kemu,score from sentiment.grade where kemu= ‘英语‘ and score < 80;
SELECT a.id,a.name,c.score,c.kemu from sentiment.student a,(SELECT id,kemu,score from sentiment.grade where kemu= ‘英语‘ and score < 80) c
where a.id = c.id;
SELECT a.id, a.name, b.kemu, b.score FROM sentiment.student a, sentiment.grade b WHERE a.id = b.id
AND b.kemu = ‘英语‘ AND b.score < 80;
13.统计每门课程不及格[0,60)，一般[60,80]，优秀(80,100]
SELECT count(*) from sentiment.grade where score < 60 group by kemu;
SELECT count(*) from sentiment.grade where score <= 80 and score >= 60;
SELECT count(*) from sentiment.grade where score > 80 group BY kemu;
SELECT b.kemu,
(SELECT count(*) from sentiment.grade where score < 60 and kemu=b.kemu) as 不及格,
(SELECT count(*) from sentiment.grade where score <= 80 and score >= 60 and kemu=b.kemu) as 一般,
(SELECT count(*) from sentiment.grade where score > 80 and kemu=b.kemu) as 优秀
from sentiment.grade b
GROUP by b.kemu
gradea表：
1.查询表中每门课都大于80分的学生姓名
-不考虑课程
SELECT name from gradea GROUP by name HAVING min(score)>80;
SELECT DISTINCT name from gradea where name not in (SELECT DISTINCT name FROM gradea WHERE score <=80);
-考虑课程为3
SELECT name from gradea GROUP by name HAVING min(score)>80 and count(kemu)>=3;
2.用sql查询出“张”姓学生中平均成绩大于75分的学生信息
SELECT name from sentiment.gradea where name LIKE ‘张%‘ GROUP by name having avg(score)>75; select * from sentiment.gradea where name in (select name from sentiment.gradea where name like ‘张%‘ group by name having avg(score) > 75);
原文：https://www.cnblogs.com/shengyin/p/12168381.html