学生成绩管理系统数据库设计--MySQLSQL Server_mysql创建学生成绩管理数据库

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

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

设计大纲

在这里插入图片描述

友情链接

1、医疗信息管理系统数据库–MySQL

医疗信息管理系统数据库–MySQL

2、邮件管理数据库设计–MySQL

邮件管理数据库设计–MySQL

3、点餐系统数据库设计–SQL Server

点餐系统数据库设计–SQL Server

4、商品管理系统数据库设计–SQL Server

商品管理系统数据库设计–SQL Server

5、SQL Server医疗信息管理系统数据库【英文版-源码】–（Medical Management System Database

SQL Server医疗信息管理系统数据库【英文版-源码】–（Medical Management System Database

6、SQL Server电影院数据库管理系统【英文版-源码】–（Movie Theatre Management System Database

SQL Server电影院数据库管理系统【英文版-源码】–（Movie Theatre Management System Database

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表的表结构
在这里插入图片描述

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,
		-- 需要完整代码请添加文章底部微信付费咨询
		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
-- 需要完整代码请添加文章底部微信付费咨询
END//
DELIMITER ;
-- 调用函数
SELECT get_student_info_by_sid(8);

调用函数结果
在这里插入图片描述
其中DELIMITER 先定义结束符为 // , 然后又将结束符改为mysql默认的分号结束符。

了解delimiter 关键字请点击
MySQL中 delimiter 关键字详解
如果出现报错1418

Error Code : 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

参考下面
MySQL ERROR 1418 的解决方法

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);
	-- 需要完整代码请添加文章底部微信付费咨询
	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;
-- 需要完整代码请添加文章底部微信付费咨询
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;
	-- 需要完整代码请添加文章底部微信付费咨询
	END IF;
END//
DELIMITER ;


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

测试结果
在这里插入图片描述
终于写完啦

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: mysqlSQL SERVER数据库
返回列表

上一篇：MySQL8.0.26安装配置教程(windows 64位)

下一篇：数据结构——二叉树2.0

“学生成绩管理系统数据库设计--MySQLSQL Server_mysql创建学生成绩管理数据库” 的相关文章

第41讲：MySQL内置的QL性能分析工具1年前 (2023-02-02)
Java实现简单的图书管理系统（讲解清晰，代码齐全，能正常运行）_数据库java图书管理系统1年前 (2023-02-02)
MySQL之my.cnf配置文件_mysql my.cnf配置1年前 (2023-02-02)
centos7安装mysql5.7步骤（图解版）_centos7安装mysql5.71年前 (2023-02-02)
C# 连接 MySQL 数据库1年前 (2023-02-02)
【MySQL进阶教程】SQL优化1年前 (2023-02-02)
MySQL最左匹配原则1年前 (2023-02-02)
如何使用mybatis处理数据库关系中的一对多关系呢？1年前 (2023-02-02)
linux环境安装mysql5.7版本1年前 (2023-02-02)
深入理解MySQL中的bin log、redo log、undo log1年前 (2023-02-02)

阿里云国际版