MySQL 经典练习 50 题（完美解答版）

2024-06-25 06:48| 来源: 网络整理| 查看: 265

MySQL 经典练习 50 题（完美解答版）创建数据库和表数据库学生表 student课程表 course教师表 teacher成绩表 score表关系经典练习 50 题1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩6、查询"李"姓老师的数量7、询学过"张三"老师授课的同学的信息8、查询没学过"张三"老师授课的同学的信息9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息11、查询没有学全所有课程的同学的信息12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息13、查询和"01"号的同学学习的课程完全相同的其他同学的信息14、查询没学过"张三"老师讲授的任一门课程的学生姓名15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩16、检索"01"课程分数小于60，按分数降序排列的学生信息17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩18、查询各科成绩最高分、最低分和平均分，以如下形式显示：19、按各科成绩进行排序，并显示排名20、查询学生的总成绩并进行排名21、查询不同老师所教不同课程平均分从高到低显示22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比24、查询学生平均成绩及其名次25、查询各科成绩前三名的记录26、查询每门课程被选修的学生数27、查询出只有两门课程的全部学生的学号和姓名28、查询男生、女生人数29、查询名字中含有"风"字的学生信息30、查询同名同性学生名单，并统计同名人数31、查询1990年出生的学生名单32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩34、查询课程名称为"数学"，且分数低于60的学生姓名和分数35、查询所有学生的课程及分数情况36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数37、查询课程不及格的学生38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名39、求每门课程的学生人数40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩42、查询每门课程成绩最好的前三名43、统计每门课程的学生选修人数（超过5人的课程才统计）44、检索至少选修两门课程的学生学号45、查询选修了全部课程的学生信息46、查询各学生的年龄(周岁)47、查询本周过生日的学生48、查询下周过生日的学生49、查询本月过生日的学生50、查询12月份过生日的学生

创建数据库和表

数据库 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);

表关系

在这里插入图片描述

经典练习 50 题

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 select s.*,sc1.s_score as score_01,sc2.s_score as score_02 from student s inner join ( select * from score where c_id = 1 ) sc1 on s.s_id = sc1.s_id inner join ( select * from score where c_id = 2 ) sc2 on s.s_id = sc2.s_id where 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_02 from student s inner join ( select * from score where c_id = 1 ) sc1 on s.s_id = sc1.s_id inner join ( select * from score where c_id = 2 ) sc2 on s.s_id = sc2.s_id where sc1.s_score = 60 ) t1 on 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_score from student s left join ( select s_id,avg(s_score) as avg_score from score group by s_id ) t1 on s.s_id = t1.s_id where avg_score is null or avg_score =90

select c.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 c inner join score s on c.c_id = s.c_id group 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_score from (select @i := 0, @pre_group_id := 1) var cross join ( select c.c_id,c.c_name,s.s_id,s.s_name,s_score from score sc inner join student s on sc.s_id = s.s_id inner join course c on sc.c_id = c.c_id group by c.c_id,s.s_id order 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) var cross join ( select s.s_id,s.s_name,sum_score from student s inner join ( select s_id,sum(s_score) as sum_score from score group by s_id ) t1 on s.s_id = t1.s_id order 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_score from score sc inner join course c on sc.c_id = c.c_id inner join teacher t on t.t_id = c.t_id group by t.t_id,c.c_id order 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_score from (select @i := 0, @pre_group_id := 1) var cross join ( select c.c_id,c.c_name,s.*,s_score from score sc inner join student s on sc.s_id = s.s_id inner join course c on sc.c_id = c.c_id group by c.c_id,s.s_id order by c.c_id,s_score desc ) t1 ) t2 where 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]及所占百分比 select c.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 c inner join score sc on c.c_id = sc.c_id group 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) var cross join ( select s.s_id,s.s_name,avg_score from student s inner join ( select s_id,round(avg(s_score), 2) as avg_score from score group by s_id ) t1 on s.s_id = t1.s_id order 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_score from ( select * from score sc where ( select count(*) from score sc1 where sc.c_id = sc1.c_id and 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_score from ( select * from score sc where ( select count(*) from score sc1 where sc.c_id = sc1.c_id and sc.s_score

【本文地址】

公司简介

联系我们