Mysql 经典练习

学生成绩表(stuscore)：

姓名：name	课程：subject	分数：score	学号：stuid
张三	数学	89	1
张三	语文	80	1
张三	英语	70	1
李四	数学	90	2
李四	语文	70	2
李四	英语	80	2

创表代码

CREATE TABLEstuscore(
s_idint(11) NOT NULL AUTO_INCREMENT,
s_namevarchar(10) DEFAULT NULL,
s_subjectvarchar(10) DEFAULT NULL,
s_scoreint(11) DEFAULT NULL,
s_stuidint(10) unsigned DEFAULT NULL,
PRIMARY KEY (s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO stuscore VALUES (‘1’, ‘张三’, ‘数学’, ‘89’, ‘1’);
INSERT INTO stuscore VALUES (‘2’, ‘张三’, ‘语文’, ‘80’, ‘1’);
INSERT INTO stuscore VALUES (‘3’, ‘张三’, ‘英语’, ‘70’, ‘1’);
INSERT INTO stuscore VALUES (‘4’, ‘李四’, ‘数学’, ‘90’, ‘2’);
INSERT INTO stuscore VALUES (‘5’, ‘李四’, ‘语文’, ‘70’, ‘2’);
INSERT INTO stuscore VALUES (‘6’, ‘李四’, ‘英语’, ‘80’, ‘2’);
INSERT INTO stuscore VALUES (‘7’, ‘王五’, ‘数学’, ‘59’, ‘3’);
INSERT INTO stuscore VALUES (‘8’, ‘王五’, ‘语文’, ‘65’, ‘3’);
INSERT INTO stuscore VALUES (‘9’, ‘王五’, ‘英语’, ‘92’, ‘3’);
INSERT INTO stuscore VALUES (‘10’, ‘赵六’, ‘数学’, ‘10’, ‘4’);
INSERT INTO stuscore VALUES (‘11’, ‘赵六’, ‘语文’, ‘95’, ‘4’);
INSERT INTO stuscore VALUES (‘12’, ‘赵六’, ‘英语’, ‘75’, ‘4’);

1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

SELECT  s_name,SUM(s_score) from stuscore GROUP BY s_stuid ORDER BY  SUM(s_score) DESC;

2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)

SELECT s_stuid,s_name,SUM(s_score) from stuscore GROUP BY s_stuid ORDER BY  SUM(s_score) DESC;

3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

SELECT s_stuid,s_name,s_subject,MAX(s_score) from stuscore GROUP BY s_stuid ORDER BY  SUM(s_score) DESC;

4.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

SELECT s_stuid,s_name,Avg(s_score) as '平均成绩' from stuscore GROUP BY s_stuid ORDER BY  SUM(s_score) DESC;

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

SELECT s_stuid,s1.s_subject,s_name,s_score as '最高分'  FROM stuscore s1,(SELECT s_subject,MAX(s_score) as maxScore from stuscore GROUP BY s_subject) s2 where  s1.s_subject=s2.s_subject and s1.s_score=s2.maxScore;

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

7.统计如下：学号 姓名 语文 数学 英语 总分 平均分

SELECT
s1.s_stuid '学号',
s1.s_name '姓名',
(SELECT case when s_subject='语文' then s_score else 0 end FROM stuscore s2 WHERE s1.s_stuid =s2.s_stuid and s2.s_subject='语文') as '语文',
(SELECT case when s_subject='数学' then s_score else 0 end FROM stuscore s3 WHERE s1.s_stuid =s3.s_stuid and s3.s_subject='数学') as '数学',
(SELECT case when s_subject='英语' then s_score else 0 end FROM stuscore s4 WHERE s1.s_stuid =s4.s_stuid and s4.s_subject='英语') as '英语',
SUM(s_score) as '总分' ,
AVG(s_score) as '平均分'
 FROM stuscore s1 group by s_stuid order by '总分' desc;

8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

SELECT s_subject,AVG(s_score) from stuscore GROUP BY s_subject; 

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

set @id=0;
SELECT s_stuid '学号',s_name '姓名',s_score '成绩',@id:=@id+1 '排名' FROM stuscore WHERE s_subject='数学' ORDER BY s_score DESC;

10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

set @id=0;
SELECT s_stuid '学号',s_name '姓名',s_score '成绩',@id:=@id+1 '排名' FROM stuscore WHERE s_subject='数学'  ORDER BY s_score DESC LIMIT 1,2;

11．求出李四的数学成绩的排名

set @id=0;
SELECT * FROM (SELECT s_stuid '学号',s_name '姓名',s_score '成绩',@id:=@id+1 '排名' FROM stuscore WHERE s_subject='数学'  ORDER BY s_score DESC) s1 WHERE s1.姓名='李四';

12．统计如下：课程 不及格（0-59）个 良（60-80）个 优（81-100）个

select s1.s_subject as '课程',(SELECT COUNT(s2.s_score) from stuscore s2 where s2.s_subject=s1.s_subject and s2.s_score<60 ) as '未及格' ,
(SELECT COUNT(s2.s_score) from stuscore s2 where s2.s_subject=s1.s_subject and s2.s_score>=60 and s2.s_score<=80 ) as '良好' ,
(SELECT COUNT(s2.s_score) from stuscore s2 where s2.s_subject=s1.s_subject and s2.s_score>81 and s2.s_score<100 ) as '优' 
  FROM stuscore s1 GROUP BY s1.s_subject;

13．统计如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)

SELECT s1.s_name as '课程',(SELECT s2.s_score from stuscore s2 where s2.s_name=s1.s_name and s1.s_subject=s2.s_subject) as '数学' from stuscore s1 WHERE s1.s_subject="数学" ;

14.计算科科及格的人的平均成绩

SELECT s1.s_name as '姓名',AVG(s1.s_score)  from stuscore s1 WHERE (SELECT COUNT(s2.s_subject) FROM stuscore s2 WHERE s1.s_stuid=s2.s_stuid and s_score>=60)>=3 GROUP BY s1.s_stuid;