MySQL基础之练习题

题目

现有班级、学生以及成绩三张表：

备注：表名称和字段名称可以参考表格内单词设置

根据表格信息，按要求完成下面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 |
+------------+------------+------+---------+