--创建班级表
create table class(
	c_id tinyint(3) unsigned zerofill auto_increment key comment '班级ID',
	name varchar(10) not null  unique comment '名称',
	descrip varchar(5) not null unique default '普通班' comment '备注'
)engine=innodb charset=utf8;

alter table class drop descrip;

alter table class add descrip varchar(5) not null
default '普通班' comment '备注' after name ;

alter table class drop name;

alter table class add name varchar(10) not null
 comment '名称' after c_id ;

insert class(name,descrip) values('一年级一班','重点班'),
('一年级二班','重点班'),
('一年级一班','重点班'),
('一年级二班',default);

--更改记录
update class set name='二年级一班' where c_id =3;

update class set name='二年级二班' where c_id =4;

--创建学生表
create table student (
	s_id smallint(4) unsigned auto_increment key comment '学生ID',
	name varchar(10) not null comment '姓名',
	gender enum('男','女') not null default '男' comment '性别',
	classId tinyint(3) unsigned zerofill comment '班级'
)engine=innodb charset=utf8;

--添加外键
alter table student add constraint classId_fk_class foreign key(classId) references class(c_id)
on delete cascade on update cascade;

--删除外键
alter table student drop foreign key classId_fk_class;
alter table student modify classId tinyint(3) unsigned zerofill not null comment '班级';

alter table student auto_increment = 1001;

insert student(name,gender,classId) values('赵晓明',default,1),
('王晓红',default,1),
('张娆娆',default,1),
('孙琪琪',default,3),
('李米米',default,4),
('赵晓刚',default,3),
('张大宝',default,2),
('张兰',default,4),
('孙好',default,1);

update student set gender='女' where s_id=1008;

update student set gender='女' where s_id between 1002 and 1005;

--创建成绩表
create table score(
	sc_id tinyint(3) unsigned auto_increment key comment '成绩ID',
	s_id smallint(4) unsigned not null comment '学生ID',
	course varchar(10) not null comment '课程',
	mark tinyint unsigned not null default 0 comment '分数'
)engine=innodb charset=utf8;

--添加外键
alter table score add constraint s_id_fk_student foreign key(s_id) references student(s_id) 
on delete cascade on update cascade;

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 score drop sc_id;

alter table score add sc_id tinyint(3) unsigned  zerofill auto_increment key comment '成绩ID' first;

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

select c.name as '班级',s.course as '课程名称',avg(s.mark) '平均成绩' from score as s
join student as stu on s.s_id = stu.s_id
join class as c on c.c_id = stu.classId
group by c.c_id,s.course
order by c.c_id asc;

--2.查询所有同学的学生ID，姓名，性别以及总分，并按照成绩从高到低排序
select stu.s_id as '学生ID',stu.name as '学生姓名',stu.gender as '性别',sum(s.mark) as '总分'
from score as s 
join student as stu on s.s_id=stu.s_id
join class as c on stu.classId=c.c_id
group by stu.s_id
order by '总分' desc;

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

select stu.s_id as '学生ID',stu.name as '学生姓名',c.name as '班级', s.course as '课程',s.mark as '分数'
from score as s
join student as stu on stu.s_id = s.s_id
join class as c on c.c_id=stu.classId
where s.mark < 75;

update score set mark=88 where sc_id=13;

--4.将李米米的数学成绩修改为88分
--下面的SQL语句会报错（ You can't specify target table 'score' for update in FROM clause）,不能查询出同一表的
--某些值，在改动这个表
/*
update score set mark=88
where sc_id=(select s.sc_id from score as s 
join student as stu on stu.s_id=s.s_id
join class as c on c.c_id=stu.classId
where c.c_id=4 and stu.s_id=1005 and s.course='数学');
*/

update score set mark=88
where  sc_id=(select sc_id_result.sc_id 
	from
	(select s.sc_id from score as s
	join student as stu on stu.s_id=s.s_id
	where stu.s_id=1005 and s.course='数学')
	as 
	sc_id_result);
	
--5.计算重点班中每一科的平均成绩，显示数据包括：重点班级ID，班级名称，课程，平均分数，按照降序排列
select c.c_id as '重点班级ID',c.name as '班级名称',s.course as 课程,avg(s.mark) as '平均分数' from score as s 
join student as stu on stu.s_id=s.s_id
join class as c on c.c_id=stu.classId
where c.descrip in ('重点班')
group by c.c_id,s.course
order by avg(s.mark) desc;