教务管理系统

数据库环境

MySQL版本：5.7.31-log

数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。

字符集：utf8

排序规则：utf8_general_ci

DDL+DML：

​
​



CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    email VARCHAR(100) UNIQUE COMMENT '邮箱'
);
 
INSERT INTO Users (username, password, gender, email) VALUES
('小明', '123', '男', 'xiaoming@apple.com'),
('小红', '456', '女', 'xiaohong@apple.com'),
('小绿', '789', '男', 'xiaolv@apple.com'),
('于文', '123', '男', 'yuwen@apple.com'),
('舒雪', '852', '女', 'shuxue@apple.com'),
('武力', '857', '男', 'wuli@apple.com'),
('华雪', '886', '女', 'huaxue@apple.com');





CREATE TABLE Roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称'
);
 
-- 插入角色数据
 
-- 假设用户ID 6是管理员，用户ID 8 是教师，用户ID 9 是学生

INSERT INTO Roles (role_id,role_name) VALUES
('1','校长'),
('2','副校长'),
('3','主任'),
('6','管理员'),
('8','教师'),
('9','学生');

CREATE TABLE UserRoles (
    user_id INT COMMENT '用户ID',
    role_id INT COMMENT '角色ID',
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (role_id) REFERENCES Roles(role_id)
);
 -- 插入用户角色关联数据

INSERT INTO UserRoles (user_id, role_id) VALUES
(6,6), -- 管理员
(8,8), -- 教师
(9,9); -- 学生


CREATE TABLE Courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '课程ID',
    course_name VARCHAR(100) NOT NULL UNIQUE COMMENT '课程名称',
    course_code VARCHAR(50) NOT NULL UNIQUE COMMENT '课程代码',
    description TEXT COMMENT '课程描述'
);
 
-- 插入课程数据
INSERT INTO Courses (course_name, course_code, description) VALUES
('数学', 'S111', '数学课程'),
('英语', 'E222', '英语课程'),
('化学', 'C333', '化学课程'),
('物理', 'P555', '物理课程');


CREATE TABLE Teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '教师ID',
    user_id INT NOT NULL COMMENT '关联的用户ID',
    subject VARCHAR(100) NOT NULL COMMENT '教学科目',
    qualification VARCHAR(255) COMMENT '教师资质',
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
 
-- 插入教师数据
 
-- 假设用户ID 6 是关联的教师用户
INSERT INTO Teachers (user_id, subject, qualification) VALUES
(6, '语文', '特级教师'),
(6, '物理', '高级教师'),
(6, '英语', '中级教师'),
(6, '数学', '初级教师');


CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
    user_id INT NOT NULL COMMENT '关联的用户ID',
    class VARCHAR(50) NOT NULL COMMENT '班级',
    year_of_entry YEAR NOT NULL COMMENT '入学年份',
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
 
-- 插入学生数据
 
-- 假设用户ID 9 是关联的学生用户
INSERT INTO Students (user_id, class, year_of_entry) VALUES
(9, '一班', 2025),
(9, '二班', 2027),
(9, '三班', 2028),
(9, '四班', 2029);


CREATE TABLE Grades (
    grade_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩ID',
    student_id INT NOT NULL COMMENT '学生ID',
    course_id INT NOT NULL COMMENT '课程ID',
    grade DECIMAL(5, 2) NOT NULL COMMENT '成绩',
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

-- 插入成绩数据
 
-- 假设学生ID 6 在课程ID 6 上获得88分，学生ID 8 在课程ID 8 上获得66分，学生ID 9 在课程ID 9 上获得1000分
INSERT INTO Grades (student_id, course_id, grade) VALUES
(6, 6, 88.00),
(1, 8, 66.00),
(5, 6, 33.00),
(1, 8, 50.00),
(4, 3, 68.00),
(1, 6, 99.00),
(8, 9, 100.00);

​

​

运行结果展示： Navicat数据库的ER图：

ER图：

数据库模型图：

简单查询：

SELECT student_id AS '学生ID', CLASS AS '班级' FROM students;
SELECT role_id AS '角色ID', role_name AS '角色名称' FROM roles;
SELECT subject AS '教学科目',qualification AS '教师资质' FROM teachers;

 运行结果展示：

复杂查询：

-- 查询所有学生的姓名、性别、班级、入学年份以及他们所选课程的名称和成绩
SELECT 
    u.username AS 学生姓名,
    u.gender AS 性别,
    s.class AS 班级,
    s.year_of_entry AS 入学年份,
    c.course_name AS 课程名称,
    g.grade AS 成绩
FROM 
    Users u
JOIN 
    Students s ON u.user_id = s.user_id -- 连接Users表和Students表，基于user_id
JOIN 
    Grades g ON s.student_id = g.student_id -- 连接Students表和Grades表，基于student_id
JOIN 
    Courses c ON g.course_id = c.course_id -- 连接Grades表和Courses表，基于course_id
​

运行结果展示： 

触发器：（3个）

DELIMITER $$

CREATE TRIGGER trg_check_score_before_insert
BEFORE INSERT ON grades
FOR EACH ROW
BEGIN
    -- 检查新插入的成绩是否在0-100范围内
    IF NEW.grade NOT BETWEEN 0 AND 100 THEN
        -- 如果不在范围内，则报错
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误！请插入合理成绩';
    END IF;
END $$


DELIMITER ;

DELIMITER $$

CREATE TRIGGER trg_check_score_before_update
BEFORE UPDATE ON grades
FOR EACH ROW
BEGIN
    -- 检查更新后的成绩是否在0-100范围内
    IF NEW.grade NOT BETWEEN 0 AND 100 THEN
        -- 如果不在范围内，则报错
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误！请插入合理成绩';
    END IF;
END $$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER trg_check_score_update_percentage
BEFORE UPDATE ON grades
FOR EACH ROW
BEGIN
    -- 检查新成绩与旧成绩之间的差值是否超过旧成绩的20%
    IF ABS(NEW.grade - OLD.grade) > (OLD.grade * 0.2) THEN
        -- 如果超过20%，则报错
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误！成绩更改上下浮动不允许超过20%';
    END IF;
END $$

DELIMITER ;

运行结果展示：

测试1，当插入新数据的成绩为101时：

测试2，当更改某一成绩为101时 ：

测试3，更新成绩上下浮动不得超过20%，将6号成绩从99改为50时：

 存储过程创建：

​
DELIMITER $$
CREATE PROCEDURE RegisterUser
( IN p_username VARCHAR(50), 
IN p_password VARCHAR(255), 
IN p_gender ENUM('男', '女'), 
IN p_email VARCHAR(100) ) 
BEGIN
    DECLARE EXISTS_USERNAME INT;
    DECLARE EXISTS_EMAIL INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
        -- 如果发生错误，回滚事务并返回错误消息
        ROLLBACK;
        RESIGNAL; -- 可以选择性地使用RESIGNAL来重新抛出捕获的异常，或者设置自定义的错误消息
    END;
    -- 开始事务
    START TRANSACTION;

    -- 检查用户名是否已存在
    SELECT COUNT(*) INTO EXISTS_USERNAME FROM Users WHERE username = p_username;
    IF EXISTS_USERNAME > 0 THEN
        -- 用户名已存在，返回错误
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误！用户名已存在。';
    END IF;

    -- 检查电子邮件是否已存在
    SELECT COUNT(*) INTO EXISTS_EMAIL FROM Users WHERE email = p_email;
    IF EXISTS_EMAIL > 0 THEN
        -- 电子邮件已存在，返回错误
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误！电子邮件已存在。';
    END IF;

    -- 如果没有错误，插入新用户
    INSERT INTO Users (username, password, gender, email) VALUES (p_username, p_password, p_gender, p_email);

    -- 提交事务
    COMMIT;

END $$

DELIMITER ;

​

运行结果展示：

测试语句：

​
call RegisterUser('小明','789','男','hhh@apple.com');
call RegisterUser('害害害','789','男','xiaoming@apple.com');

​

测试1，当插入数据中用户名已存在时：

测试2，当插入数据中邮箱重复时：