--创建数据库

CREATE DATABASE IF NOT EXISTS test3 DEFAULT CHARACTER SET 'UTF8';

USE test3;

--创建班级表：class

CREATE TABLE class(

  c_id TINYINT UNSIGNED AUTO_INCREMENT KEY,

  c_name CHAR(10) NOT NULL UNIQUE,

  c_descrip CHAR(10) NOT NULL

);

INSERT class(c_id,c_name,c_descrip) VALUES(001,'一年级一班','重点班'),

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

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

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

CREATE TABLE student(

  s_id INT UNSIGNED AUTO_INCREMENT KEY,

  s_name VARCHAR(10) NOT NULL,

  s_gender ENUM('男','女','保密') NOT NULL,

  s_class TINYINT UNSIGNED

);

INSERT student(s_id,s_name,s_gender,s_class) VALUES(1001,'赵晓明','男',001),

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

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

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

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

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

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

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

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

CREATE TABLE score(

  sc_id TINYINT UNSIGNED AUTO_INCREMENT KEY,

  s_id INT UNSIGNED NOT NULL,

  sc_course CHAR(5) NOT NULL,

  sc_mark TINYINT UNSIGNED NOT NULL

);

INSERT score(sc_id,s_id,sc_course,sc_mark) VALUES(001,1001,'数学',98),

(002,1001,'语文',90),

(003,1001,'英语',97),

(004,1002,'数学',96),

(005,1002,'语文',88),

(006,1003,'语文',88),

(007,1002,'英语',91),

(008,1003,'数学',96),

(009,1003,'英语',86),

(010,1004,'数学',89),

(011,1004,'语文',82),

(012,1004,'英语',83),

(013,1005,'数学',75),

(014,1005,'语文',86),

(015,1005,'英语',77),

(016,1006,'数学',81),

(017,1006,'语文',77),

(018,1006,'英语',60),

(019,1007,'数学',89),

(020,1007,'语文',56),

(021,1007,'英语',70),

(022,1008,'数学',87),

(023,1008,'语文',55),

(024,1008,'英语',66),

(025,1009,'数学',78),

(026,1009,'语文',60),

(027,1009,'英语',52);

--查询每个班级中每一科的平均成绩

SELECT c.c_id,c.c_name,sc.sc_course,avg(sc.sc_mark) FROM

class AS c

JOIN student AS st

ON c.c_id=st.s_class

JOIN score AS sc

ON st.s_id=sc.s_id

GROUP BY c.c_name,sc.sc_course

ORDER BY c.c_id;

--查询所有同学的ID,姓名，性别以及总分

SELECT st.s_id,st.s_name,st.s_gender,sum(sc.sc_mark) FROM

student AS st

JOIN score AS sc

ON st.s_id=sc.s_id

GROUP BY st.s_name

ORDER BY SUM(sc.sc_mark) DESC;