-- 创建数据库
CREATE DATABASE stu;

-- 切换数据库
USE stu;

-- 创建班级表class表
CREATE TABLE class(
	c_id TINYINT UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY COMMENT '班级ID',
	name VARCHAR(10) NOT NULL UNIQUE KEY COMMENT '班级名称',
	descrip VARCHAR(10) NOT NULL DEFAULT '' COMMENT '备注'
)ENGINE=INNODB CHARSET=UTF8;

-- 创建学生表：student
CREATE TABLE student(
	s_id SMALLINT(4) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
	name VARCHAR(10) NOT NULL COMMENT '学生姓名',
	gender VARCHAR(10) NOT NULL DEFAULT '男' COMMENT '性别',
	class  TINYINT UNSIGNED  ZEROFILL NOT NULL COMMENT '班级',
	CONSTRAINT class_fk_class FOREIGN KEY (class) REFERENCES class(c_id)
)ENGINE=INNODB CHARSET=UTF8;

-- 创建成绩表score
CREATE TABLE score(
	sc_id TINYINT UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY COMMENT '成绩ID',
	s_id SMALLINT(4) UNSIGNED ZEROFILL NOT NULL  COMMENT '学生ID',
	course VARCHAR(10) NOT NULL  COMMENT '课程名称',
	mark TINYINT UNSIGNED NOT NULL COMMENT '分数',
	CONSTRAINT s_id_fk_student FOREIGN KEY (s_id) REFERENCES student(s_id)
)ENGINE=INNODB CHARSET=UTF8;

-- 使用SQL分别创建班级表、学生表以及成绩表的表结构，表内数据可以一条一条的插入也可以批量插入
-- 给class表添加数据
INSERT class(name,descrip) VALUES('一年级一班','重点班'),
('一年级二班','重点班'),
('二年级一班','重点班'),
('二年级二班','普通班');

-- 修改 student表中的AUTO_INCREMNET=1001;
ALTER TABLE student AUTO_INCREMENT = 1001;
-- 给student表添加数据
INSERT student(name,gender,class) VALUES ('赵晓明','男','001'),
('王晓红','女','001'),
('张晓晓','女','001'),
('孙琪琪','女','003'),
('李米米','女','004'),
('赵晓刚','男','003'),
('张大宝','男','002'),
('张兰','女','004'),
('孙好','男','001');

-- 给score表添加数据
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);


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

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

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

-- 查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数
SELECT stu.s_id AS '学生ID',stu.name AS '姓名',
c.name AS '班级',s.course AS '课程',s.mark AS '分数'
FROM class AS c
INNER JOIN student AS stu
ON c.c_id = stu.class
INNER JOIN score AS s
ON stu.s_id = s.s_id
WHERE s.mark < 75;

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

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

SELECT c.c_id AS '重点班级ID',c.name AS '班级名称',
s.course AS '课程',AVG(s.mark) AS '平均分'
FROM class AS c
INNER JOIN student AS stu
ON c.c_id = stu.class
INNER JOIN score AS s
ON stu.s_id = s.s_id
WHERE  c.descrip= '重点班'
GROUP BY 班级名称,课程
ORDER BY 平均分 DESC;


问题1：在insert数据的时候，整数类型的值要不要加单引号呢？我不加也可以，但是我看到很多人都加了。

问题2：在：查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数的操作中，我最后添加条件:
	  WHERE s.mark < 75; 的时候，用前面 s.mark 起的别名 '分数' 来作为条件的时候，得到的结果
	  确实一大堆错误的，这是为什么呀？

	  还有最后一个问题的时候，我也是在WHERE条件位： WHERE  '班级名称'= '重点班'
	  这样查不到一个数据，但是这个 '班级名称'不也是我起的别名吗？

麻烦老师在看看有哪里需要修改的，哪里不规范的。