CREATE TABLE `class`(

  cid TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

  cname CHAR(5) NOT NULL,

  descrip CHAR(3) NOT NULL

);

INSERT class(cname,descrip) VALUES('一年级一班','重点班'),

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

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

('二年级二班','普通班');

CREATE TABLE student(

  id TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

  name VARCHAR(20) NOT NULL,

  sex ENUM("男",'女')  DEFAULT '男',

  cid TINYINT UNSIGNED NOT NULL

);

INSERT student(name,sex,cid) VALUES('赵晓明','男','001'),

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

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

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

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

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

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

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

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

CREATE TABLE scores(

  scid TINYINT UNSIGNED  KEY AUTO_INCREMENT,

  sid  TINYINT UNSIGNED NOT NULL,

  course CHAR(2)   NOT NULL,

  mark TINYINT UNSIGNED

);

INSERT score(sid,course,mark) VALUES(1,'数学','98'),

(1,'语文','90'),

(1,'英语','97'),

(2,'数学','96'),

(2,'语文','88'),

(3,'语文','88'),

(2,'英语','91'),

(3,'数学','96'),

(3,'英语','86'),

(4,'数学','89'),

(4,'语文','82'),

(4,'英语','83'),

(5,'数学','75'),

(5,'语文','86'),

(5,'英语','77'),

(6,'数学','81'),

(6,'语文','77'),

(6,'英语','60'),

(7,'数学','89'),

(7,'语文','56'),

(7,'英语','70'),

(8,'数学','87'),

(8,'语文','55'),

(8,'英语','66'),

(9,'数学','78'),

(9,'语文','60'),

(9,'英语','52');

--select不能放在函数里面,下面这个是错误的

SELECT c.name,AVG(SELECT sc.mark FROM sc WHERE sc.course='数学') AS '数学平均分',

AVG(SELECT sc.MARK FROM sc WHERE sc.course='语文') AS '语文平均分',

AVG(SELECT sc.MARK FROM sc WHERE sc.course='英语') AS '英语平均分'

FROM class AS c

INNER JOIN student AS s

ON c.cid=s.id

INNER JOIN score AS sc

ON s.id=sc.id

GROUP BY c.name

ORDER BY c.cid ;

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

SELECT c.cname AS '班级名称', sc.course AS '课程',  AVG(sc.mark) AS '平均分'

FROM class AS c

INNER JOIN student AS s

ON c.cid=s.cid

INNER JOIN score AS sc

ON s.id=sc.sid

GROUP BY c.cname,sc.course

ORDER BY c.cid ;

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

SELECT s.id AS '学生id',s.name AS '学生姓名',s.sex AS '性别',SUM(sc.mark) AS '总分'

FROM student AS s

INNER JOIN score AS sc

ON s.id=sc.sid

GROUP BY s.id

ORDER BY SUM(sc.mark) DESC;

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

SELECT s.id AS '学生id',s.name AS '学生姓名',c.cname AS '班级名称',sc.course AS '课程',sc.mark

FROM class AS c

INNER JOIN student AS s

ON c.cid=s.cid

INNER JOIN score AS sc

ON s.id=sc.sid

WHERE sc.mark<75;

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

UPDATE score AS sc INNER JOIN  student AS s

ON s.id=sc.sid 

SET sc.mark=88 

WHERE s.name='李米米' AND sc.course='数学';

--查询李米米的信息

SELECT s.name AS '学生姓名',sc.course AS '课程',sc.mark

FROM class AS c

INNER JOIN student AS s

ON c.cid=s.cid

INNER JOIN score AS sc

ON s.id=sc.sid

WHERE s.name='李米米';

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

SELECT c.cid AS '班级ID',c.cname AS '班级名称', sc.course AS '课程',  AVG(sc.mark) AS '平均分'

FROM class AS c

INNER JOIN student AS s

ON c.cid=s.cid

INNER JOIN score AS sc

ON s.id=sc.sid

WHERE  c.descrip='重点班'

GROUP BY c.cname,sc.course

ORDER BY c.cid DESC;