MySQL 数据库设计-学生成绩管理系统

1. 项目背景及需求分析
1.1 项目背景
为了深刻的理解MySQL数据库，以学生成绩信息管理为例，设计一个简单、规范、高效的学生成绩信息管理系统数据库。

1.2 需求分析
1.2.1 信息需求
对学校而言，学生成绩管理是管理工作中重要的一环，但是高校学生的成绩管理工作量大、繁杂，人工处理非常困难。因此，借助于强大计算机的处理能力，能够把人从繁重的成绩管理工作中解脱出来，并且更加准确、安全、清晰的管理环境。

1.2.2 功能需求
能够进行数据库的数据定义、数据操纵、数据控制等处理功能。具体功能应包括：可提供课程安排、课程成绩数据的添加、插入、删除、更新、查询，学生及教职工基本信息查询的功能。

1.2.3 安全性与完整性要求
对于学生成绩管理系统数据库来讲，由于其主要数据是学生成绩，只能由本人以及所教老师及教务处知道，因此做好数据安全性是重中之重。另外，要求所有在校学生的信息都要录入其中，并且要设计好个别情况。

2. 概念结构设计
概念结构设计是整个数据库设计的关键，它通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。
根据学生成绩信息管理数据库设计需求抽象出学生、教师、课程、成绩四个实体，对四个实体做简化处理，默认一门课程仅被一位老师讲授。因简化后关系结构比较简单，故省略了局部E-R图。对4个实体之间的关系进行分析如下：
一位学生会被多位老师教导，一位老师会教导多位学生，所有学生与教师之间是多对多（m:n）的关系；
一位学生可能会选修多门课程，一门课程会被多位学生选修，所以学生与课程之间是多对多（m:n）的关系；
一位学生会有多项成绩（具体指某学生一门课程的分数），一项成绩仅被一位学生拥有，所以学生与成绩是一对多（1:n）的关系；
一位教师会讲授多门课程，一门课程会被一位教师讲授，所以教师与课程的关系是一对多（1:n）的关系；
一门课程拥有多项成绩，一项成绩仅被一门课程拥有，所以课程与成绩的关系是一对多（1:n）的关系；

2.1 抽象出系统实体
学生（学号、姓名、班级、性别、专业、出生日期、学分）；
老师（教师编号、姓名、学院）；
课程（课程编号、教师编号、课程名称、课程学分）；
成绩（学号、课程编号、分数）；

2.2 全局E-R图

3. 逻辑结构设计
3.1 关系模式
E-R图向关系模型转化要解决的问题是如何将实体型和实体间的联系转化为关系模式，如何确定这些关系模式的属性和码。
设计学生成绩管理数据库，包括学生（students）、老师（teachers）、课程（courses）、成绩（scores）四个实体，其关系模式中对每个实体定义属性如下：

students 表：学号（sid）、姓名（sname）、班级（sclass）、性别（sgender）、专业（smajor）、出生日期（sbirthday）、学分（credit_points），此为联系“students表”所对应的关系模式，学号为该关系的候选码，满足第三范式。

teachers表：教师编号（tid）、姓名（tname）、学院（tschool），此为联系“teachers表”所对应的关系模式，教师编号为该关系的候选码，满足第三范式。

courses表：课程编号（cid）、教师编号（tid）、课程名称（cname）、学分（credit_point），此为联系“courses表”所对应的关系模式，课程编号和教师编号为该关系的候选码，满足第三范式。

scores表：学号（sid）、课程编号（cid）、分数（score），此为联系“scores表”所对应的关系模式，学号和课程编号为该关系的候选码，满足第三范式。

3.2 函数依赖识别
后续更新补充

3.3 范式
后续更新补充

3.4 表结构
数据库中包含4个表，即学生（students）、老师（teachers）、课程（courses）、成绩（scores）。

students表的表结构

teachers表的表结构

courses表的表结构

scores表的表结构

4. 物理设计和实施
4.1 数据库及表创建
4.1.1 创建数据库
— 如果已有该数据库，则删除
DROP DATABASE IF EXISTS StudentScore;

— 创建数据库
CREATE DATABASE StudentScore CHARSET=UTF8;

4.1.2 创建数据表
— 使用数据库
USE StudentScore;

— 创建数据表
— table 1: students
DROP TABLE IF EXISTS students;
CREATE TABLE students(
sid INT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
sname VARCHAR(20),
sclass INT(10),
sgender VARCHAR(10),
smajor VARCHAR(20),
sbirthday DATE,
credit_points INT(5) — 学生已修学分
);

— table 2: teachers
DROP TABLE IF EXISTS teachers;
CREATE TABLE teachers(
tid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
tname VARCHAR(20),
tschool VARCHAR(20)
);

— table 3: courses
DROP TABLE IF EXISTS courses;
CREATE TABLE courses(
cid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
cname VARCHAR(20),
credit_point INT(5), — 课程学分
tid INT(10) UNSIGNED NOT NULL,
FOREIGN KEY(tid) REFERENCES teachers(tid)
);

— table 4: scores
DROP TABLE IF EXISTS scores;
CREATE TABLE scores(
sid INT(10) UNSIGNED NOT NULL,
cid INT(10) UNSIGNED NOT NULL,
score DECIMAL(5, 2),
FOREIGN KEY(sid) REFERENCES students(sid),
FOREIGN KEY(cid) REFERENCES courses(cid)
);

4.2 表数据增删改查测试
4.2.1 MySQL基础知识点总结

4.2.2 精选MySQL练习题数据及解析
点击查看
4.2.2 精选MySQL练习题数据及答案解析

4.2.2.1. 连接查询 – 4题
1.1 查询同时选修了课程 1 和 课程 2 的学生的信息

1.2 查询课程 1 比 课程 2 成绩高的学生的信息及课程分数

1.3 查询课程 1 分数小于 60 的学生信息和课程分数，按分数降序排列

1.4 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

4.2.2.2. 子查询、连接查询 – 4题
2.1 查询有成绩的学生信息

2.2 查询学过 孙悟空 老师所授课程的学生信息

2.3 查询至少有一门课与学号为 1 的同学所学相同的学生信息

2.4 查询选修了课程 2 但是没有选修课程 1 的学生信息

4.2.2.3. 聚合分组、连接查询 – 8题
3.1 查询同名学生名单，并统计同名人数

3.2 查询选修了 3 门课程的学生信息

3.3 查询平均成绩大于等于 85 的所有学生的学号、姓名、平均成绩（保留2位小数）

3.4 查询平均成绩大于等于 60 分的学生学号、姓名、平均成绩（保留2位小数）

3.5 查询两门及以上课程分数小于60分的学生学号、姓名及平均成绩（保留2位小数）

3.6 查询姓 赵 的同学的学生信息、总分，若没选课则总分显示为 0

3.7 查询所有同学的学号、姓名、选课总数、总成绩，没选课的学生要求显示选课总数和总成绩为 0

3.8 查询所有学生学号、姓名、选课名称、总成绩，按总成绩降序排序，没选课的学生显示总成绩为 0

4.2.2.4. if 或 case 语句 – 2题
4.1 若学号sid为学生座位编号，现开始对座位号调整，奇数号和偶数号对调，如1和2对调、3和4对调…等， 如果最后一位为奇数，则不调换座位，查询调换后的学生座位号(sid)、姓名，按sid排序

4.2 查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程id、课程名、选修人数、最高分、最低分、平均分、及格率、中等率、优良率、优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求查询结果按人数降序排列，若人数相同，按课程号升序排列，平均分、及格率等保留2位小数

4.2.2.5. 时间函数 – 6题
5.1 查询 1990 年出生的学生信息

5.2 查询各学生的年龄，分别按年份和按出生日期来算

5.3 查询本周或下周过生日的学生

5.4 查询本月或下月过生日的学生

5.5 查询学生信息，要求：学号和年龄同时至少比一位学生的学号和年龄大

5.6 查询连续相邻3年出生的学生中，学生性别相同的学生信息

4.2.2.6.综合应用 – 12题
6.1 查询和学号为 1 的同学学习的课程完全相同的其他同学的信息

6.2 查询每科均及格的人的平均成绩：学号、姓名、平均成绩（保留2位小数）

6.3 查询选修 张若尘 老师所授课程的学生中，该门课成绩最高的学生信息及成绩(成绩可能重复)

6.4 查询各科成绩，按各科成绩进行排序，并显示排名 分数重复时保留名次空缺，即名次不连续

6.5 查询各科成绩，按各科成绩进行排序，并显示排名 分数重复时不保留名次空缺，即名次连续

6.6 查询学生 赵雷 的 变形 课程成绩的排名：学生信息，分数，排名 分数重复时不保留名次空缺，即名次连续

6.7 查询课程 时空穿梭 成绩在第2-4名的学生，要求显示字段：学号、姓名、课程名、成绩 分数重复时不保留名次空缺，即名次连续

6.8 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺，即名次连续

6.9 查询学生的总成绩，并进行排名，总分重复时保留名次空缺，及名次不连续 排名名次不连续，不需要去重

6.10 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 分别所占百分比 结果：保留2位小数

6.11 查询各科成绩前三名的记录，按照课程编号和分数排序 分数重复时，重复分数按照一名算，即不保留名次空缺，及名次连续

6.12 查询各科成绩的前两名，列出学生信息、课程名、分数，按照课程名、分数排序 分数重复时，重复分数按照一名算，即不保留名次空缺，及名次连续

4.2.2.7 MySQL练习题数据及答案解析
点击查看
4.2.2 精选36道MySQL练习题数据及答案解析

4.3 创建视图
4.3.1 创建一个学生视图，要求显示学生学号、姓名、班级、性别、专业、各科成绩、平均分、总分
DROP VIEW IF EXISTS v_students_info;

CREATE VIEW v_students_info AS
SELECT stu.sid,
stu.sname,
stu.sclass,
stu.sgender,
stu.smajor,
sum(if(c.cname = “变形”, sc.score, 0)) AS “变形”,
sum(if(c.cname = “时空穿梭”, sc.score, 0)) AS “时空穿梭”,
sum(if(c.cname = “分解术”, sc.score, 0)) AS “分解术”,
sum(if(c.cname = “炼器”, sc.score, 0)) AS “炼器”,
sum(if(c.cname = “炼丹”, sc.score, 0)) AS “炼丹”,
sum(if(c.cname = “飞行”, sc.score, 0)) AS “飞行”,
round(ifnull(avg(sc.score), 0), 2) AS “平均分”,
ifnull(sum(sc.score), 0) AS “总分”
FROM students stu LEFT JOIN
scores sc ON stu.sid = sc.sid LEFT JOIN
courses c ON c.cid = sc.cid
GROUP BY stu.sid;

查看视图：

4.4 创建函数
4.4.1 创建一个通过学号sid获取学生信息的函数
DROP FUNCTION IF EXISTS get_student_info_by_sid;

DELIMITER //
CREATE DEFINER = CURRENT_USER FUNCTION get_student_info_by_sid(id INT)
RETURNS VARCHAR(300)
DETERMINISTIC
BEGIN
RETURN (SELECT CONCAT(‘姓名： ‘, sname, ‘ , ‘, ‘性别: ‘, sgender, ‘ , ‘, ‘专业：’, smajor)
FROM students WHERE sid = id);
END//
DELIMITER ;
— 调用函数
SELECT get_student_info_by_sid(8);
调用函数结果：

其中DELIMITER 先定义结束符为 // , 然后又将结束符改为mysql默认的分号结束符。

4.4.2 自定义函数 2：要求函数体中包含其中一种流程控制语句，要求输入学生学号sid、课程编号，显示学生姓名、课程名称、成绩是否及格（即成绩>=60)

DROP FUNCTION IF EXISTS get_student_scores_by_id;

DELIMITER //
CREATE DEFINER = CURRENT_USER FUNCTION get_student_scores_by_id(sid INT, cid INT)
RETURNS VARCHAR(300)
DETERMINISTIC
BEGIN
— 多个变量要分开声明，否则会报错
DECLARE score INT;
DECLARE name VARCHAR(20);
DECLARE course_name VARCHAR(20);
SELECT sc.score INTO score
FROM scores AS sc
WHERE sc.sid = sid AND sc.cid = cid;
SELECT sname INTO name FROM students AS stu WHERE stu.sid = sid;
SELECT cname INTO course_name FROM courses AS co WHERE co.cid = cid;
IF score >= 60 THEN
RETURN CONCAT(name, ‘–‘, course_name, ‘–‘, ‘及格’);
ELSEIF score > 0 AND score < 60 THEN
RETURN CONCAT(name, ‘–‘, course_name, ‘–‘, ‘不及格’);
ELSE
RETURN ‘找不到该学生、课程或该学生没有选课！’;
END IF;
END//
DELIMITER ;

— 调用函数
SELECT get_student_scores_by_id(1, 2);

调用函数结果：

4.5 创建存储过程
4.5.1 学生每选修一门课，如果该门课程成绩达到60分及以上，则把该门课程学分加到学生学分里面，输出该学生姓名、学分
DROP PROCEDURE IF EXISTS add_scores;

DELIMITER //
CREATE DEFINER = CURRENT_USER PROCEDURE add_scores(
IN stu_id INT,
IN co_id INT,
IN s_score INT,
OUT name VARCHAR(20),
OUT s_credit_point INT
)
DETERMINISTIC
BEGIN
— 多个变量要分开声明，否则会报错
DECLARE points INT;
INSERT INTO scores (sid, cid, score)
VALUES (stu_id, co_id, s_score);
SELECT credit_point INTO points FROM courses WHERE cid = co_id;
IF s_score >= 60 THEN
UPDATE students
SET credit_points = credit_points + points
WHERE sid = stu_id;
END IF;
— 注意：多个输出值一定要用多个SELECT来赋值，否则会报错
SELECT sname INTO name FROM students WHERE sid = stu_id;
SELECT credit_points INTO s_credit_point FROM students WHERE sid = stu_id;
COMMIT;
END//
DELIMITER ;

— 测试调用存储过程
SELECT * FROM students WHERE sid > 10;
CALL add_scores(11, 2, 33, @name, @s_credit_point);
SELECT @name, @s_credit_point;
CALL add_scores(12, 2, 88, @name, @s_credit_point);
SELECT @name, @s_credit_point;

调用存储过程结果：

4.6 创建触发器
4.6.1 创建一个更新学生学分的触发器，如果该学生分数>=60，则给该学生加上这门课的学分
语法
create trigger triggerName
after/before insert/update/delete on 表名 for each row #这句话在mysql是固定的 begin
sql语句;
end;

DROP TRIGGER IF EXISTS update_credit_point;

DELIMITER //
CREATE TRIGGER update_credit_point
AFTER INSERT ON scores FOR EACH ROW
BEGIN
DECLARE points INT;
SELECT co.credit_point INTO points FROM courses AS co WHERE co.cid = new.cid;
IF new.score >= 60 THEN
UPDATE students SET credit_points = credit_points + points
WHERE sid = new.sid;
END IF;
END//
DELIMITER ;

— 测试数据
SELECT * FROM students WHERE sid < 3;
INSERT INTO scores VALUES
(1, 4, 77),
(2, 4, 55);
SELECT * FROM students;

本文为原创文章，转载请注明出处！