基础sql整理

参考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 a.id,a.name,b.kemu,MAX(b.score) from sentiment.student a,sentiment.grade b where a.id = b.id group by b.kemu ORDER by b.score DESC;

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 a.id,a.name,b.kemu,b.score from sentiment.student a, sentiment.grade b where a.id = b.id and b.kemu ='数学' order by b.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,b.kemu,b.score from sentiment.student a, sentiment.grade b where a.id = b.id and b.kemu ='数学' order by b.score DESC LIMIT 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,b.kemu,b.score from sentiment.student a, sentiment.grade b where a.id = b.id and b.kemu ='数学' order by b.score DESC LIMIT 1,2;

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，该条记录不被取出；

...

所以取出的数据为前两位。 

SELECT t1.id,t1.kemu,t1.score,a.name from sentiment.grade t1,sentiment.student a
where t1.id=a.id
and (SELECT COUNT(*) from sentiment.grade t2 where t1.kemu=t2.kemu and t2.score>t1.score)<2
ORDER by t1.kemu DESC;

 

11.查询第3名到后面所有的学生数学成绩

SELECT a.id,a.name,b.kemu,b.score from sentiment.student a, sentiment.grade b where a.id = b.id and b.kemu ='数学' order by b.score DESC LIMIT 2,999;


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);