学生成绩管理系统

一、概要

作为《学生成绩管理系统》项目开发组的程序员，请按要求完成：
数据库的创建；
数据表的创建；
相关数据的操作。

二、实施步骤及内容

《学生成绩管理系统》的E-R图如图2.25所示，逻辑数据模型如图2.26所示，物理数据模型如图2.27所示, 数据表字段名定义见表2.11。请按要求完成数据库创建、数据表创建和数据操作任务：

三、代码

-- 创建数据库表
CREATE TABLE IF NOT EXISTS T_student_info(
	Student_no CHAR(6) NOT NULL COMMENT '学号，主键',
	Student_name VARCHAR(20) NOT NULL COMMENT '姓名',
	Sex CHAR(2) NOT NULL COMMENT '性别' DEFAULT '男',
	Birth DATETIME NOT NULL COMMENT '出生年月',
	Enter_date DATETIME NOT NULL COMMENT '入学年份',
	Address VARCHAR(50) COMMENT '家庭住址',
	PRIMARY KEY(Student_no)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS T_course_info(
	Course_no CHAR(8) NOT NULL COMMENT '课程编号，主键',
	Course_name VARCHAR(50) NOT NULL COMMENT '课程名',
	Credit TINYINT NOT NULL COMMENT '学分',
	Classhour TINYINT NOT NULL COMMENT '学时',
	PRIMARY KEY(Course_no)
)ENGINE=INNODB DEFAULT CHARSET=utf8;



CREATE TABLE IF NOT EXISTS T_student_scores(
	Student_no CHAR(6) NOT NULL COMMENT '学号',
	Course_no CHAR(8) NOT NULL COMMENT '课程编号',
	Ordinary_scores FLOAT COMMENT '平时成绩',
	End_scores FLOAT COMMENT '期末成绩',
	Total_scores FLOAT COMMENT '总评成绩',
	PRIMARY KEY(Student_no,Course_no)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO T_student_scores VALUES('102011','1020001',60,80,70);
INSERT INTO T_student_scores VALUES('102012','1020002',50,90,80);
INSERT INTO T_student_scores VALUES('102013','1020003',40,90,80);

-- 插入数据
INSERT INTO T_student_info VALUES('102011','刘德华','男','1991-03-23','2009-09-01','湖南长沙劳动东路168号');
INSERT INTO T_student_info VALUES('102012','张三','男','1990-03-23','2009-09-01','湖南长沙劳动东路169号');
INSERT INTO T_student_info VALUES('102013','李四','男','1992-03-13','2009-09-01','湖南长沙劳动东路170号');

INSERT INTO T_course_info VALUES('1020001','高等数学',25,64);
INSERT INTO T_course_info VALUES('1020002','大学语文',25,64);
INSERT INTO T_course_info VALUES('1020003','英语',25,64);

-- 查询出1991年出生的学生信息
SELECT * FROM T_student_info WHERE YEAR(Birth)='1991'

-- 查询选修了“高等数学”的学生姓名、平时成绩、期末成绩、总评成绩
SELECT Student_name,Ordinary_scores,End_scores,Total_scores FROM T_student_info INNER JOIN T_student_scores
ON T_student_info.`Student_no` = T_student_scores.`Student_no`
WHERE T_student_scores.`Course_no` = 
(SELECT Course_no FROM T_course_info WHERE Course_name='高等数学')

SELECT Student_name,Ordinary_scores,End_scores,Total_scores FROM T_student_info,T_student_scores,T_course_info
WHERE T_student_info.`Student_no` = T_student_scores.`Student_no`
AND T_student_scores.`Course_no` = T_course_info.`Course_no`
AND Course_name = '高等数学'

-- 创建视图查询学生的学号、姓名、课程名、总评成绩
CREATE VIEW Stu_cou_sco_view
AS
SELECT T_student_info.`Student_no`,Student_name,Course_name,Total_scores FROM T_student_info,T_student_scores,T_course_info
WHERE T_student_info.`Student_no` = T_student_scores.`Student_no`
AND T_student_scores.`Course_no` = T_course_info.`Course_no`

SELECT * FROM Stu_cou_sco_view

-- 统计每门课程的选课人数、最高分、平均分，并将统计结果保存在表T_total中
CREATE TABLE IF NOT EXISTS T_total(
	Course_no VARCHAR(50),
	People_num INT,
	Max_scores FLOAT,
	Avg_scores FLOAT
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO T_total
SELECT Course_no AS '课程编号' ,COUNT(*) AS '人数' ,MAX(Total_scores) AS '最高分',AVG(Total_scores) AS '平均分'
FROM T_student_scores
GROUP BY Course_no