教务管理系统

[H*]

已于 2024-06-27 22:19:07 修改

阅读量126

点赞数 1

文章标签：数据库

于 2024-06-27 15:35:41 首次发布

本文链接：https://blog.csdn.net/H18703199283/article/details/139968263

版权

ER图

数据库模型

DDL

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 '邮箱'
);
 
CREATE TABLE Roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称'
);
 
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)
);
 
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 '课程描述'
);
 
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)
);
 
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)
);
 
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)
);

DML

-- 插入用户数据
INSERT INTO Users (username, password, gender, email) VALUES
('小刘 ', '123', '女', 'xiaoliu@example.com'),
('小王', '123', '男', 'xiaowang@example.com'),
('小赵', '123', '女', 'xiaozhao@example.com'),
('小张', '123', '男', 'xiaozhang@example.com'),
('小李', '123', '女', 'xiaoli@example.com');
 
 
-- 插入角色数据
INSERT INTO Roles (role_name) VALUES
('管理员'),
('教师'),
('学生'),
('辅导员'),
('领导');
 
 
-- 插入用户角色关联数据
 
-- 假设用户ID 1 是管理员，用户ID 2 是教师1，用户ID 3 是学生1，用户ID 4 是辅导员，用户ID 5 是领导
INSERT INTO UserRoles (user_id, role_id) VALUES
(1, 1), -- 管理员
(2, 2), -- 教师
(3, 3), -- 学生
(4, 4), -- 辅导员
(5, 5); -- 领导
 
 
 -- 插入课程数据
INSERT INTO Courses (course_name, course_code, description) VALUES
('数学', 'MATH101', '基础数学课程'),
('英语', 'ENGLISH201', '基础英语课程'),
('物理', 'PHYSICS301', '基础物理课程'),
('语文', 'CHINESE401', '基础语文课程'),
('化学', 'CHEMISTRY501', '基础化学课程');
 
 
-- 插入教师数据
 
-- 假设用户ID 2 是关联的教师用户
INSERT INTO Teachers (user_id, subject, qualification) VALUES
(2, '数学', '高级教师'),
(2, '物理', '中级教师'),
(2, '英语', '初级教师'),
(2, '语文', '中级教师'),
(2, '化学', '高级教师');
 
 
-- 插入学生数据
 
-- 假设用户ID 3 是关联的学生用户
INSERT INTO Students (user_id, class, year_of_entry) VALUES
(3, '一班', 2020),
(3, '二班', 2021),
(3, '三班', 2022),
(3, '二班', 2021),
(3, '三班', 2022);
 
 
-- 插入成绩数据
 
-- 假设学生ID 1 在课程ID 1 上获得90分，学生ID 2 在课程ID 2 上获得85分，学生ID 3 在课程ID 3 上获得88分,学生ID 4 在课程ID 4 上获得83分,学生ID 5 在课程ID 5 上获得91分
INSERT INTO Grades (student_id, course_id, grade) VALUES
(1, 1, 90.00),
(1, 2, 85.00),
(2, 3, 88.00),
(4, 4, 83.00),
(5, 5, 91.00);

基础查询与复杂查询

- 简单查询1（查询所有学生信息，只显示名字和邮箱）
SELECT username '学生名字',email '邮箱' FROM users;
-- 简单查询2（去重学生名字）
SELECT DISTINCT  username '去重后学生名字' FROM users ;
-- 简单查询3(查询学生成绩，并进行倒序排列)
SELECT grade,student_id FROM grades ORDER BY grade DESC ;
-- 复杂查询1(查询分数所对应的科目名)
SELECT g.grade,c.course_name FROM courses c JOIN grades g ON g.course_id=c.course_id;
-- 复杂查询2（查询成绩最高分的学生所有信息）
SELECT u.*,t.grade FROM users u JOIN(SELECT grade,student_id FROM grades ORDER BY grade DESC LIMIT 1) t
ON u.user_id=t.student_id;
-- 复杂查询3（查询每个学生的平均分）
SELECT u.*,t.avgg FROM users u JOIN (SELECT avg(grade) avgg,student_id FROM grades GROUP BY student_id) t ON u.user_id=t.student_id ;

触发器

#问题1： 当从users表中插入一条新记录时，需要检查该用户是否是教师角色，如果是，则自动在teachers表中插入一条相应的记录。
DELIMITER $$  -- 更改默认的语句分隔符为$$，这样可以在触发器内部使用分号
CREATE TRIGGER after_user_insert  -- 创建一个名为after_user_insert的触发器
AFTER INSERT ON users -- 触发器在users表发生INSERT操作之后触发
FOR EACH ROW -- 触发器对每一行插入操作都执行一次
BEGIN 
		-- 触发器开始
    -- 检查新用户是否有对应的教师记录
    IF NOT EXISTS (SELECT 1 FROM teacher WHERE user_id = NEW.user_id) THEN
        -- 如果教师记录不存在，则创建一个新的教师记录
        INSERT INTO teachers (teacher_id,user_id, subject, qualification)  -- 在teachers表中插入一行数据
        VALUES (6,2, '美术', '高级教师'); -- 使用6作为新插入行的teachers_id,2作为新插入行的user_id，subject为美术，qualification为高级教师
    END IF;
END; -- 触发器结束
$$ -- 触发器定义结束，使用新的分隔符
DELIMITER ; -- 将语句分隔符改回为分号
#测试语句：
INSERT INTO users (username, password, gender, email) VALUES ('小武', '123', '女', 'xiaowu@example.com');

#问题2： 当courses表中的一条记录被更新时，需要检查新课程代码是否已经存在，如果存在，则发出警告。
DELIMITER $$ -- 更改默认的语句分隔符为$$，这样可以在触发器内部使用分号
CREATE TRIGGER before_course_update -- 创建一个名为before_course_update的触发器
BEFORE UPDATE ON courses -- 触发器在courses表发生INSERT操作之后触发
FOR EACH ROW -- 触发器对每一行插入操作都执行一次
BEGIN
		-- 触发器开始
    IF EXISTS (SELECT 1 FROM courses WHERE course_code = NEW.course_code AND course_id <> NEW.course_id) THEN
        -- 这里可以是一个信号，也可以是发送警告给管理员
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Course code already exists!';
    END IF;
END;-- 触发器结束
$$ -- 触发器定义结束，使用新的分隔符
DELIMITER ; -- 将语句分隔符改回为分号
#测试语句：
UPDATE courses SET course_code = 'CS101' WHERE course_id = 1; -- 假设课程ID为1的课程尝试更新课程代码为已存在的'CS101'

#问题3： 当从students表中删除一条记录时，需要同时从grades表中删除该学生所有的成绩记录。
DELIMITER $$ -- 更改默认的语句分隔符为$$，这样可以在触发器内部使用分号
CREATE TRIGGER after_student_delete -- 创建一个名为after_student_delete的触发器
AFTER DELETE ON students -- 触发器在students表发生INSERT操作之后触发
FOR EACH ROW -- 触发器对每一行插入操作都执行一次
BEGIN
		-- 触发器开始
    DELETE FROM grades WHERE student_id = OLD.student_id; -- 删除该学生所有的成绩记录
END;-- 触发器结束
$$ -- 触发器定义结束，使用新的分隔符
DELIMITER ; -- 将语句分隔符改回为分号
#测试语句：
DELETE FROM students WHERE student_id = 1; -- 假设删除学生ID为1的记录

存储过程

DELIMITER $$

CREATE PROCEDURE InsertUserWithRole(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255),
    IN p_gender ENUM('男', '女'),
    IN p_email VARCHAR(100),
    IN p_role_name VARCHAR(50)
)
BEGIN
    DECLARE user_id_var INT;
    DECLARE role_id_var INT;

    -- 检查角色是否存在，如果不存在则插入
    SELECT role_id INTO role_id_var FROM Roles WHERE role_name = p_role_name;
    IF role_id_var IS NULL THEN
        INSERT INTO Roles (role_name) VALUES (p_role_name);
        SET role_id_var = LAST_INSERT_ID();
    END IF;

    -- 插入新用户
    INSERT INTO Users (username, password, gender, email)
    VALUES (p_username, p_password, p_gender, p_email);
    SET user_id_var = LAST_INSERT_ID();

    -- 插入用户角色关联
    INSERT INTO UserRoles (user_id, role_id)
    VALUES (user_id_var, role_id_var);

    -- 输出或处理结果（如果需要）
    SELECT CONCAT('User with ID: ', user_id_var, ' has been assigned role: ', p_role_name) AS message;
END $$

DELIMITER ;
CALL InsertUserWithRole('小红', '123', '女', 'xiaohong@example.com', '校长');