教务系统管理

 存储过程

​
​
DELIMITER $$
 # 声明一个名称为create_new_infos
CREATE PROCEDURE create_new_infos(
    IN user_name VARCHAR(50),       -- 学生用户名
    IN user_password VARCHAR(255),  -- 学生密码
    IN user_gender ENUM('女','男'), -- 学生性别
    IN user_email VARCHAR(100),     --  学生邮件
    IN student_class VARCHAR(50),   -- 学生班级
    IN course_name VARCHAR(100),    -- 课程名称
    IN grade DECIMAL(5, 2),         -- 学生成绩
    IN student_year_of_entry INT,   -- 学生入学年份
    IN user_id INT                  -- 学生id
)
 # 开始操作
BEGIN
    DECLARE user_id INT;
    DECLARE student_id INT;
    DECLARE course_id INT;
    DECLARE role_id_var INT;
    DECLARE user_id_var INT;
    DECLARE old_grade_var INT;

    
    -- 检查用户是否具有教师角色
    SELECT user_id INTO user_id_var FROM users 
    JOIN userroles ur ON u.user_id = ur.user_id
    JOIN roles  ON ur.role_id = r.role_id
    WHERE u.user_id = user() AND r.role_name = 'Teacher';
    
    IF user_id_var IS NULL THEN

        -- 如果用户不是教师，则抛出错误
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Only teachers can update grades.';
    END IF;

    -- 更新成绩
    UPDATE grades SET grade = grade_in WHERE student_id = student_id_in AND course_id = course_id_in;
    
    -- 获取更新前的成绩（如果存在）
    SELECT grade INTO old_grade_var FROM grades
    WHERE student_id = student_id_in AND course_id = course_id_in;

    -- 插入新用户
    INSERT INTO users (username, password, gender, email)
    VALUES (username, password, gender, email);

   -- 使用获取的用户ID插入学生信息到 students 表
    INSERT INTO students (user_id, class, year_of_entry)
    VALUES (user_id, student_class, student_year_of_entry);
   
    -- 提交事务
    COMMIT;

 END $$

 DELIMITER;

 # 调用存储过程
call create_new_infos('李','123','女','li@example.com');

select * from users where username='李';

​

​

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);