# 具体遇到的问题

# 报错信息的截图

# 相关课程内容截图

# 尝试过的解决思路和结果

# 粘贴全部相关代码，切记添加代码注释（请勿截图）

-- 1、使用SQL分别创建班级表、学生表以及成绩表的表结构，表内数据可以一条一条的插入也可以批量插入

-- 创建班级表class

create table if not exists class(

    id tinyint unsigned auto_increment key comment '班级编号',

classname varchar(30) not null unique comment '班级名称',

descrip varchar(20) not null comment '班级备注'

)engine = innodb charset = utf8;

desc class;

-- 创建学生表student

create table if not exists student(

   id smallint(4) unsigned auto_increment key comment '学生编号',

stuname varchar(20) not null unique comment '学生姓名',

gender enum('男','女') not null default '男' comment '学生性别',

class tinyint unsigned not null comment '所在班级'

)engine=innodb charset = utf8;

desc student;

-- 创建成绩表score

create table if not exists score (

    sc_id int unsigned auto_increment key comment '学生成绩编号',

s_id smallint(4) unsigned not null comment '学生编号',

course varchar(20) not null comment'课程名称',

mark tinyint not null default '0' comment '学生成绩分数'

)engine = innodb charset = utf8;

desc score;

-- 插入班级信息

insert class(id,classname,descrip) values(001,'一年级一班','重点班'),

(002,'一年级二班','重点班'),

(003,'二年级一班','重点班'),

(004,'二年级二班','重点班');

select * from class;

-- 插入学生信息

insert student(id,stuname,gender,class) values(1001,'赵晓红','男',001),

(1002,'王晓红','女',001),

(1003,'张晓晓','女',001),

(1004,'孙琪琪','女',003),

(1005,'李米米','女',004),

(1006,'赵晓刚','男',003),

(1007,'张大宝','男',002),

(1008,'张兰','女',004),

(1009,'孙好','男',001);

select * from student;

-- 插入学生成绩

insert score(s_id,course,mark)values(1001,'数学',98),

(1001,'语文',90),

(1001,'英语',97),

(1002,'数学',96),

(1002,'语文',88),

(1002,'英语',91),

(1003,'数学',96),

(1003,'语文',88),

(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);

select * from score;

-- 2、查询每个班级中每一科的平均成绩，显示数据包括班级名称，课程以及平均分数，并按照班ID升序排列

select c.classname as '班级名称',sc.course as '课程',AVG(sc.mark) as '平均分' 

from class as c 

inner join student as s on c.id = s.class 

inner join score as sc on sc.s_id= s.id

group by c.classname,sc.course

order by c.id asc;

-- 3、查询所有同学的学生ID，姓名，性别以及总分，并按照成绩从高到低排序

set SESSION sql_mode = '';

select s.id as '学生id',

s.stuname as '学生姓名',

s.gender as '学生性别',

SUM(sc.mark) as '学生总分'

from student as s 

inner join score as sc

on s.id=sc.s_id

group by s.id

order by sc.mark desc; 

-- 4、查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数

select s.id,s.stuname,c.classname,sc.course,sc.mark from class as c inner join student as s

on c.id=s.class inner join score as sc on s.id = sc.s_id where sc.mark<=70;

-- 5、将李米米的数学成绩修改为88分

update score set mark= 88 

where s_id = (select id from student where stuname = '李米米')and course = '数学';

-- 6、计算重点班中每一科的平均成绩，显示数据包括：重点班级ID，班级名称，课程，平均分数，按照降序排列

set SESSION sql_mode = '';

select c.id as '重点班id',

c.classname as '班级名称',

sc.course as '课程',

AVG(sc.mark) as '平均分数' 

from class as c inner join student as s 

on c.id=s.class 

inner join score as sc 

on s.id = sc.s_id

where c.id < 4

group by c.id,sc.course

order by sc.mark desc;