MySQL数据库基础练习系列35、教务管理系统

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

-- 插入用户数据
INSERT INTO Users (username, password, gender, email) VALUES
('孙悟空', '123', '男', 'sunwukong@example.com'),
('林黛玉', '123', '女', 'lindaiyu@example.com'),
('贾宝玉', '123', '男', 'jiaobaoyu@example.com');


-- 插入角色数据
INSERT INTO Roles (role_name) VALUES
('管理员'),
('教师'),
('学生');


-- 插入用户角色关联数据

-- 假设用户ID 1 是管理员，用户ID 2 是教师，用户ID 3 是学生
INSERT INTO UserRoles (user_id, role_id) VALUES
(1, 1), -- 管理员
(2, 2), -- 教师
(3, 3); -- 学生


-- 插入课程数据
INSERT INTO Courses (course_name, course_code, description) VALUES
('数学', 'MATH101', '基础数学课程'),
('英语', 'ENGLISH201', '基础英语课程'),
('物理', 'PHYSICS301', '基础物理课程');


-- 插入教师数据

-- 假设用户ID 2 是关联的教师用户
INSERT INTO Teachers (user_id, subject, qualification) VALUES
(2, '数学', '高级教师'),
(2, '物理', '中级教师'),
(2, '英语', '初级教师');


-- 插入学生数据

-- 假设用户ID 3 是关联的学生用户
INSERT INTO Students (user_id, class, year_of_entry) VALUES
(3, '一班', 2020),
(3, '二班', 2021),
(3, '三班', 2022);


-- 插入成绩数据

-- 假设学生ID 1 在课程ID 1 上获得90分，学生ID 2 在课程ID 2 上获得85分，学生ID 3 在课程ID 3 上获得88分
INSERT INTO Grades (student_id, course_id, grade) VALUES
(1, 1, 90.00),
(1, 2, 85.00),
(2, 3, 88.00);