MySQL基础之练习题

╰半橙微兮° 2023-06-01 11:13 198阅读 0赞

## 题目 ##

> 现有班级、学生以及成绩三张表：
> 
> 备注：表名称和字段名称可以参考表格内单词设置

![5c2f368f000123c003330156.png][]

![5c2f3698000182c804320271.png][]

![5c2f36ab000130a404350707.png][]

> 根据表格信息，按要求完成下面SQL语句的编写：
> 
> 1、使用SQL分别创建班级表、学生表以及成绩表的表结构，表内数据可以一条一条的插入也可以批量插入
> 
> 2、查询每个班级中每一科的平均成绩，显示数据包括班级名称，课程以及平均分数，并按照班ID升序排列
> 
> 3、查询所有同学的学生ID，姓名，性别以及总分，并按照成绩从高到低排序
> 
> 4、查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数
> 
> 5、将李米米的数学成绩修改为88分
> 
> 6、计算重点班中每一科的平均成绩，显示数据包括：重点班级ID，班级名称，课程，平均分数，按照降序排列

## 解答 ##

create database if not exists mooc default character set 'utf8'; use mooc; --班级表 create table if not exists class( c_id int(3) unsigned zerofill auto_increment key comment '编号', name varchar(20) not null comment '班级', descrip varchar(20) not null comment '备注' )engine=innodb charset=utf8; --插入班级数据 insert class(name,descrip) values('一年级一班','重点班'), ('一年级二班','重点班'), ('二年级一班','重点班'), ('二年级二班','普通班'); --学生表 create table if not exists student( s_id int unsigned auto_increment key comment '编号', name varchar(20) not null comment '姓名', gender varchar(5) not null comment '性别', class int(3) unsigned zerofill not null comment '班级' )engine=innodb charset=utf8; alter table student auto_increment=1001; --修改主键初始值 --插入学生数据 insert student(name,gender,class) values('赵晓明','男','001'), ('王晓红','女','001'), ('张晓晓','女','001'), ('孙琪琪','女','003'), ('李米米','女','004'), ('赵晓刚','男','003'), ('张大宝','男','002'), ('张兰','女','004'), ('孙好','男','001'); --成绩表 create table if not exists score( sc_id int(3) unsigned zerofill auto_increment key comment '编号', s_id int unsigned not null comment '学生ID', course varchar(10) not null comment '课程', mark int unsigned not null comment '分数' )engine=innodb charset=utf8; --插入成绩数据 insert score(s_id,course,mark) values(1001,'数学',98), (1001,'语文',90), (1001,'英语',97), (1002,'数学',96), (1002,'语文',88), (1003,'语文',88), (1002,'英语',91), (1003,'数学',96), (1003,'英语',86), (1004,'数学',89), (1004,'语文',82), (1004,'英语',83), (1005,'数学',75), (1005,'语文',86), (1005,'英语',77), (1006,'数学',81), (1006,'语文',77), (1006,'英语',60), (1007,'数学',89), (1007,'语文',56), (1007,'英语',70), (1008,'数学',87), (1008,'语文',55), (1008,'英语',66), (1009,'数学',78), (1009,'语文',60), (1009,'英语',52); --添加外键约束 alter table student add foreign key(class) references class(c_id); alter table score add foreign key(s_id) references student(s_id); --查询每个班级中每一科的平均成绩，显示数据包括班级名称， --课程以及平均分数，并按照班ID升序排列 select c.name as 班级名称,sc.course as 课程,avg(mark) as 平均分 from class as c inner join student as st on c.c_id = st.class inner join score as sc on st.s_id = sc.s_id group by 班级名称,课程 order by 班级名称; +------------+------+---------+ | 班级名称 | 课程 | 平均分 | +------------+------+---------+ | 一年级一班 | 数学 | 92.0000 | | 一年级一班 | 英语 | 81.5000 | | 一年级一班 | 语文 | 81.5000 | | 一年级二班 | 数学 | 89.0000 | | 一年级二班 | 英语 | 70.0000 | | 一年级二班 | 语文 | 56.0000 | | 二年级一班 | 数学 | 85.0000 | | 二年级一班 | 英语 | 71.5000 | | 二年级一班 | 语文 | 79.5000 | | 二年级二班 | 数学 | 81.0000 | | 二年级二班 | 英语 | 71.5000 | | 二年级二班 | 语文 | 70.5000 | +------------+------+---------+ --查询所有同学的学生ID，姓名， --性别以及总分，并按照成绩从高到低排序 select st.s_id as 学生ID,st.name as 姓名,st.gender as 性别,sum(mark) as 总分 from student as st inner join score as sc on st.s_id = sc.s_id group by 学生ID order by 总分 desc; +--------+--------+------+------+ | 学生ID | 姓名 | 性别 | 总分 | +--------+--------+------+------+ | 1001 | 赵晓明 | 男 | 285 | | 1002 | 王晓红 | 女 | 275 | | 1003 | 张晓晓 | 女 | 270 | | 1004 | 孙琪琪 | 女 | 254 | | 1005 | 李米米 | 女 | 238 | | 1006 | 赵晓刚 | 男 | 218 | | 1007 | 张大宝 | 男 | 215 | | 1008 | 张兰 | 女 | 208 | | 1009 | 孙好 | 男 | 190 | +--------+--------+------+------+ --查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数 select st.s_id as 学生ID,st.name as 姓名,st.class as 班级,sc.course as 课程,sc.mark as 分数 from student as st inner join class as c on c.c_id = st.class inner join score as sc on sc.s_id = st.s_id where sc.mark < 75; +--------+--------+------+------+------+ | 学生ID | 姓名 | 班级 | 课程 | 分数 | +--------+--------+------+------+------+ | 1009 | 孙好 | 001 | 语文 | 60 | | 1009 | 孙好 | 001 | 英语 | 52 | | 1007 | 张大宝 | 002 | 语文 | 56 | | 1007 | 张大宝 | 002 | 英语 | 70 | | 1006 | 赵晓刚 | 003 | 英语 | 60 | | 1008 | 张兰 | 004 | 语文 | 55 | | 1008 | 张兰 | 004 | 英语 | 66 | +--------+--------+------+------+------+ --将李米米的数学成绩修改为88分 update score set mark = 88 where s_id = (select s_id from student where name = '李米米') and course = '数学'; --计算重点班中每一科的平均成绩，显示数据包括： --重点班级ID，班级名称，课程，平均分数，按照降序排列 select c.c_id as 重点班级ID,c.name as 班级名称,sc.course as 课程,avg(mark) as 平均分 from class as c inner join student as st on c.c_id = st.class inner join score as sc on st.s_id = sc.s_id where c.descrip = '重点班' group by 班级名称,课程; +------------+------------+------+---------+ | 重点班级ID | 班级名称 | 课程 | 平均分 | +------------+------------+------+---------+ | 001 | 一年级一班 | 数学 | 92.0000 | | 001 | 一年级一班 | 语文 | 81.5000 | | 001 | 一年级一班 | 英语 | 81.5000 | | 002 | 一年级二班 | 数学 | 89.0000 | | 002 | 一年级二班 | 语文 | 56.0000 | | 002 | 一年级二班 | 英语 | 70.0000 | | 003 | 二年级一班 | 数学 | 85.0000 | | 003 | 二年级一班 | 语文 | 79.5000 | | 003 | 二年级一班 | 英语 | 71.5000 | +------------+------------+------+---------+

转载于:https://www.cnblogs.com/huowuyan/p/11616897.html

[5c2f368f000123c003330156.png]: https://raw.githubusercontent.com/huowuyan/cloudimg/master/blog/5c2f368f000123c003330156.png
[5c2f3698000182c804320271.png]: https://raw.githubusercontent.com/huowuyan/cloudimg/master/blog/5c2f3698000182c804320271.png
[5c2f36ab000130a404350707.png]: https://raw.githubusercontent.com/huowuyan/cloudimg/master/blog/5c2f36ab000130a404350707.png