数据库 drop database if exists mysql_test cascade;create database mysql_test;use mysql_test;
学生表 student
创建学生表 student
create table student (s_id int,s_name varchar(8),s_birth date,s_sex varchar(4));插入学生数据
insert into student values(1,'赵雷','1990-01-01','男'),(2,'钱电','1990-12-21','男'),(3,'孙风','1990-05-20','男'),(4,'李云','1990-08-06','男'),(5,'周梅','1991-12-01','女'),(6,'吴兰','1992-03-01','女'),(7,'郑竹','1989-07-01','女'),(8,'王菊','1990-01-20','女');课程表 course
创建课程表 course
create table course (c_id int,c_name varchar(8),t_id int);插入课程数据
insert into course values(1,'语文',2),(2,'数学',1),(3,'英语',3);教师表 teacher
创建教师表 teacher
create table teacher (t_id int,t_name varchar(8));插入教师数据
insert into teacher values(1,'张三'),(2,'李四'),(3,'王五');成绩表 score
创建成绩表 score
create table score (s_id int,c_id int,s_score int);插入成绩数据
insert into score values(1,1,80),(1,2,90),(1,3,99),(2,1,70),(2,2,60),(2,3,65),(3,1,80),(3,2,80),(3,3,80),(4,1,50),(4,2,30),(4,3,40),(5,1,76),(5,2,87),(6,1,31),(6,3,34),(7,2,89),(7,3,98);表关系
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 select s.*,sc1.s_score as score_01,sc2.s_score as score_02from student sinner join (select * from score where c_id = 1) sc1on s.s_id = sc1.s_idinner join (select * from score where c_id = 2) sc2on s.s_id = sc2.s_idwhere sc1.s_score > sc2.s_score; +------+--------+------------+-------+----------+----------+| s_id | s_name | s_birth| s_sex | score_01 | score_02 |+------+--------+------------+-------+----------+----------+|2 | 钱电| 1990-12-21 | 男|70 |60 ||4 | 李云| 1990-08-06 | 男|50 |30 |+------+--------+------------+-------+----------+----------+
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 select s.*,sc1.s_score as score_01,sc2.s_score as score_02from student sinner join (select * from score where c_id = 1) sc1on s.s_id = sc1.s_idinner join (select * from score where c_id = 2) sc2on s.s_id = sc2.s_idwhere sc1.s_score = 60) t1on s.s_id = t1.s_id; +------+--------+-----------+| s_id | s_name | avg_score |+------+--------+-----------+|1 | 赵雷| 89.67 ||2 | 钱电| 65.00 ||3 | 孙风| 80.00 ||5 | 周梅| 81.50 ||7 | 郑竹| 93.50 |+------+--------+-----------+
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的) select s.s_id,s.s_name,ifnull(round(avg_score, 2), 0) as avg_scorefrom student sleft join (select s_id,avg(s_score) as avg_scorefrom scoregroup by s_id) t1on s.s_id = t1.s_idwhere avg_score is null or avg_score =90 selectc.c_id as 课程ID,c.c_name as 课程name,max(s_score) as 最高分,min(s_score) as 最低分,round(avg(s_score), 2) as 平均分,concat(round(sum(case when s_score >= 60 then 1 else 0 end) / count(*) * 100, 2), '%') as 及格率,concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end) / count(*) * 100, 2), '%') as 中等率,concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优良率,concat(round(sum(case when s_score >= 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优秀率from course cinner join score s on c.c_id = s.c_idgroup by c.c_id; +--------+----------+--------+--------+--------+--------+--------+--------+--------+| 课程ID | 课程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |+--------+----------+--------+--------+--------+--------+--------+--------+--------+| 1 | 语文 | 80 | 31 | 64.50 | 66.67% | 66.67% | 33.33% | 0.00% || 2 | 数学 | 90 | 30 | 72.67 | 83.33% | 16.67% | 66.67% | 16.67% || 3 | 英语 | 99 | 34 | 69.33 | 66.67% | 16.67% | 16.67% | 33.33% |+--------+----------+--------+--------+--------+--------+--------+--------+--------+
19、按各科成绩进行排序，并显示排名 select(@i := case when @pre_group_id = c_id then @i + 1 else 1 end) as rank,(@pre_group_id := c_id) as c_id,c_name,s_id,s_name,s_scorefrom (select @i := 0, @pre_group_id := 1) varcross join (select c.c_id,c.c_name,s.s_id,s.s_name,s_scorefrom score scinner join student s on sc.s_id = s.s_idinner join course c on sc.c_id = c.c_idgroup by c.c_id,s.s_idorder by c.c_id,s_score desc) t1; +------+------+--------+------+--------+---------+| rank | c_id | c_name | s_id | s_name | s_score |+------+------+--------+------+--------+---------+|1 |1 | 语文|1 | 赵雷| 80 ||2 |1 | 语文|3 | 孙风| 80 ||3 |1 | 语文|5 | 周梅| 76 ||4 |1 | 语文|2 | 钱电| 70 ||5 |1 | 语文|4 | 李云| 50 ||6 |1 | 语文|6 | 吴兰| 31 ||1 |2 | 数学|1 | 赵雷| 90 ||2 |2 | 数学|7 | 郑竹| 89 ||3 |2 | 数学|5 | 周梅| 87 ||4 |2 | 数学|3 | 孙风| 80 ||5 |2 | 数学|2 | 钱电| 60 ||6 |2 | 数学|4 | 李云| 30 ||1 |3 | 英语|1 | 赵雷| 99 ||2 |3 | 英语|7 | 郑竹| 98 ||3 |3 | 英语|3 | 孙风| 80 ||4 |3 | 英语|2 | 钱电| 65 ||5 |3 | 英语|4 | 李云| 40 ||6 |3 | 英语|6 | 吴兰| 34 |+------+------+--------+------+--------+---------+
20、查询学生的总成绩并进行排名 select (@i := @i + 1) as rank,t2.*from (select @i := 0) varcross join (select s.s_id,s.s_name,sum_scorefrom student sinner join (select s_id,sum(s_score) as sum_scorefrom scoregroup by s_id) t1on s.s_id = t1.s_idorder by sum_score desc) t2; +------+------+--------+-----------+| rank | s_id | s_name | sum_score |+------+------+--------+-----------+|1 |1 | 赵雷| 269||2 |3 | 孙风| 240||3 |2 | 钱电| 195||4 |7 | 郑竹| 187||5 |5 | 周梅| 163||6 |4 | 李云| 120||7 |6 | 吴兰| 65|+------+------+--------+-----------+
21、查询不同老师所教不同课程平均分从高到低显示 select t.t_id,t.t_name,c.c_id,c.c_name,round(avg(s_score), 2) as avg_scorefrom score scinner join course c on sc.c_id = c.c_idinner join teacher t on t.t_id = c.t_idgroup by t.t_id,c.c_idorder by t.t_id,avg_score desc; +------+--------+------+--------+-----------+| t_id | t_name | c_id | c_name | avg_score |+------+--------+------+--------+-----------+|1 | 张三|2 | 数学| 72.67 ||2 | 李四|1 | 语文| 64.50 ||3 | 王五|3 | 英语| 69.33 |+------+--------+------+--------+-----------+
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 select *from (select(@i := case when @pre_group_id = c_id then @i + 1 else 1 end) as rank,(@pre_group_id := c_id) as c_id,c_name,s_id,s_name,s_birth,s_sex,s_scorefrom (select @i := 0, @pre_group_id := 1) varcross join (select c.c_id,c.c_name,s.*,s_scorefrom score scinner join student s on sc.s_id = s.s_idinner join course c on sc.c_id = c.c_idgroup by c.c_id,s.s_idorder by c.c_id,s_score desc) t1) t2where rank in (2,3); +------+------+--------+------+--------+------------+-------+---------+| rank | c_id | c_name | s_id | s_name | s_birth| s_sex | s_score |+------+------+--------+------+--------+------------+-------+---------+|2 |1 | 语文|3 | 孙风| 1990-05-20 | 男| 80 ||3 |1 | 语文|5 | 周梅| 1991-12-01 | 女| 76 ||2 |2 | 数学|7 | 郑竹| 1989-07-01 | 女| 89 ||3 |2 | 数学|5 | 周梅| 1991-12-01 | 女| 87 ||2 |3 | 英语|7 | 郑竹| 1989-07-01 | 女| 98 ||3 |3 | 英语|3 | 孙风| 1990-05-20 | 男| 80 |+------+------+--------+------+--------+------------+-------+---------+
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 selectc.c_id as 课程编号,c.c_name as 课程名称,sum(case when s_score between 85 and 100 then 1 else 0 end) as '[100-85]',concat(round(sum(case when s_score between 85 and 100 then 1 else 0 end) / count(*) * 100, 2), '%') as 百分比,sum(case when s_score between 70 and 85 then 1 else 0 end) as '[85-70]',concat(round(sum(case when s_score between 70 and 85 then 1 else 0 end) / count(*) * 100, 2), '%') as 百分比,sum(case when s_score between 60 and 70 then 1 else 0 end) as '[70-60]',concat(round(sum(case when s_score between 60 and 70 then 1 else 0 end) / count(*) * 100, 2), '%') as 百分比,sum(case when s_score between 0 and 60 then 1 else 0 end) as '[0-60]',concat(round(sum(case when s_score between 0 and 60 then 1 else 0 end) / count(*) * 100, 2), '%') as 百分比from course cinner join score scon c.c_id = sc.c_idgroup by c.c_id; +----------+----------+----------+--------+---------+--------+---------+--------+--------+--------+| 课程编号 | 课程名称 | [100-85] | 百分比 | [85-70] | 百分比 | [70-60] | 百分比 | [0-60] | 百分比 |+----------+----------+----------+--------+---------+--------+---------+--------+--------+--------+|1 | 语文 | 0| 0.00% | 4| 66.67% | 1| 16.67% | 2 | 33.33% ||2 | 数学 | 3| 50.00% | 1| 16.67% | 1| 16.67% | 2 | 33.33% ||3 | 英语 | 2| 33.33% | 1| 16.67% | 1| 16.67% | 2 | 33.33% |+----------+----------+----------+--------+---------+--------+---------+--------+--------+--------+
24、查询学生平均成绩及其名次 select (@i := @i + 1) as rank,t2.*from (select @i := 0) varcross join (select s.s_id,s.s_name,avg_scorefrom student sinner join (select s_id,round(avg(s_score), 2) as avg_scorefrom scoregroup by s_id) t1on s.s_id = t1.s_idorder by avg_score desc) t2; +------+------+--------+-----------+| rank | s_id | s_name | avg_score |+------+------+--------+-----------+|1 |7 | 郑竹| 93.50 ||2 |1 | 赵雷| 89.67 ||3 |5 | 周梅| 81.50 ||4 |3 | 孙风| 80.00 ||5 |2 | 钱电| 65.00 ||6 |4 | 李云| 40.00 ||7 |6 | 吴兰| 32.50 |+------+------+--------+-----------+
25、查询各科成绩前三名的记录
方法 1：内外关联
select c.c_id,c.c_name,s.s_id,s.s_name,s_scorefrom (select *from score scwhere (select count(*)from score sc1where sc.c_id = sc1.c_idand sc.s_score 1); +------+------+---------+| s_id | c_id | s_score |+------+------+---------+|1 |1 | 80 ||3 |1 | 80 ||3 |2 | 80 ||3 |3 | 80 |+------+------+---------+42、查询每门课程成绩最好的前三名
方法 1：内外关联
select c.c_id,c.c_name,s.s_id,s.s_name,s_scorefrom (select *from score scwhere (select count(*)from score sc1where sc.c_id = sc1.c_idand sc.s_score