--1、使用SQL分别创建班级表、学生表以及成绩表的表结构，表内数据可以一条一条的插入也可以批量插入
----------------------班级表
CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET 'UTF8';
use test2;
CREATE TABLE IF NOT EXISTS class(
  c_id TINYINT ZEROFILL UNSIGNED AUTO_INCREMENT KEY COMMENT '班级ID：c_id',
  name VARCHAR(20) NOT NULL UNIQUE COMMENT '名称：name',
  descrip VARCHAR(20) NOT NULL DEFAULT '' COMMENT '备注：descrip'
)ENGINE=INNODB CHARSET=UTF8;

INSERT class(name,descrip)VALUES('一年级一班','重点班'),
('一年级二班','重点班'),
('二年级一班','重点班'),
('二年级二班','普通班');


---------------------学生表
CREATE TABLE IF NOT EXISTS student(
  s_id SMALLINT(4) ZEROFILL UNSIGNED AUTO_INCREMENT KEY COMMENT '学生ID：c_id',
  name VARCHAR(20) NOT NULL UNIQUE COMMENT '姓名：name',
  gender ENUM('男','女','保密') DEFAULT '保密' COMMENT '性别：gender',
  class TINYINT ZEROFILL UNSIGNED NOT NULL COMMENT '班级:class',
  CONSTRAINT fk1 FOREIGN KEY(class) REFERENCES class(c_id)
)ENGINE=INNODB AUTO_INCREMENT=1001 CHARSET=UTF8;



INSERT student(name,gender,class)VALUES('赵晓明','男',001),
('王晓红','女',001),
('张晓晓','女',001),
('孙琪琪','女',003),
('李米米','女',004),
('赵晓刚','男',003),
('张大宝','男',002),
('张兰','女',004),
('孙好','男',001);


------------------------------成绩表
CREATE TABLE IF NOT EXISTS score(
  sc_id TINYINT ZEROFILL UNSIGNED AUTO_INCREMENT KEY COMMENT '成绩ID：sc_id',
  s_id SMALLINT(4) ZEROFILL UNSIGNED NOT NULL COMMENT '学生ID：s_id',
  CONSTRAINT fk2 FOREIGN KEY(s_id) REFERENCES student(s_id),
  course VARCHAR(20) NOT NULL COMMENT '课程：course',
  mark TINYINT UNSIGNED NOT NULL COMMENT '分数:mark'
)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');

--2、查询每个班级中每一科的平均成绩，显示数据包括班级名称，课程以及平均分数，并按照班ID升序排列
SELECT c.name AS className,sc.course AS courseName,AVG(sc.mark) AS avg_age
FROM class AS c
JOIN student as s
ON c.c_id=s.class
JOIN score AS sc
ON sc.s_id=s.s_id
GROUP BY className,courseName
ORDER BY c.c_id ASC;

--3、查询所有同学的学生ID，姓名，性别以及总分，并按照成绩从高到低排序
SELECT s.s_id AS studentId , s.name AS studentName , s.gender AS studentGender, SUM(sc.mark) AS sum
FROM student as s
JOIN score as sc
ON sc.s_id=s.s_id
GROUP BY studentId,studentName,studentGender
ORDER BY SUM(sc.mark) DESC;

--4、查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数
SELECT s.s_id AS studentId , s.name AS studentName , c.name AS className ,sc.course AS courseName ,sc.mark AS mark
From student as s
JOIN class as c
ON c.c_id=s.class
JOIN score as sc
ON sc.s_id=s.s_id
WHERE sc.mark<75;

--5、将李米米的数学成绩修改为88分
UPDATE score SET mark=88 WHERE s_id=1005 AND course='数学';

--6、计算重点班中每一科的平均成绩，显示数据包括：重点班级ID，班级名称，课程，平均分数，按照降序排列
SELECT c.c_id AS classID ,c.name AS className ,sc.course AS courseName ,AVG(sc.mark) as avg 
FROM class as c
JOIN student as s
ON s.class=c.c_id
JOIN score as sc
ON sc.s_id=s.s_id
WHERE c.descrip='重点班'
GROUP BY classID,className,courseName
ORDER BY AVG(sc.mark) DESC;