266 lines
13 KiB
SQL
266 lines
13 KiB
SQL
-- 创建实训平台教学管理相关表
|
||
-- 作者: 王志维
|
||
-- 创建时间: 2026-04-30
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- 1. 用户角色关联表
|
||
DROP TABLE IF EXISTS `sys_user_role`;
|
||
CREATE TABLE `sys_user_role` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT(20) NOT NULL COMMENT '用户ID',
|
||
`role_code` VARCHAR(50) NOT NULL COMMENT '角色编码：student-学生，teacher-老师，admin-管理员',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_role` (`user_id`, `role_code`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_role_code` (`role_code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';
|
||
|
||
-- 2. 学生扩展表
|
||
DROP TABLE IF EXISTS `edu_student`;
|
||
CREATE TABLE `edu_student` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT(20) NOT NULL COMMENT '用户ID（关联sys_user）',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID',
|
||
`college_id` BIGINT(20) DEFAULT NULL COMMENT '学院ID',
|
||
`major_id` BIGINT(20) DEFAULT NULL COMMENT '专业ID',
|
||
`grade` VARCHAR(20) DEFAULT NULL COMMENT '年级',
|
||
`class_name` VARCHAR(50) DEFAULT NULL COMMENT '班级',
|
||
`student_no` VARCHAR(50) DEFAULT NULL COMMENT '学号',
|
||
`real_name` VARCHAR(50) DEFAULT NULL COMMENT '真实姓名',
|
||
`activation_code` VARCHAR(50) DEFAULT NULL COMMENT '激活码',
|
||
`binding_status` TINYINT(1) DEFAULT '0' COMMENT '绑定状态：0-未绑定，1-已绑定',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_id` (`user_id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_college_id` (`college_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生扩展表';
|
||
|
||
-- 3. 教师扩展表
|
||
DROP TABLE IF EXISTS `edu_teacher`;
|
||
CREATE TABLE `edu_teacher` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT(20) NOT NULL COMMENT '用户ID（关联sys_user）',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID',
|
||
`college_id` BIGINT(20) DEFAULT NULL COMMENT '学院ID',
|
||
`real_name` VARCHAR(50) DEFAULT NULL COMMENT '真实姓名',
|
||
`teacher_no` VARCHAR(50) DEFAULT NULL COMMENT '教师编号',
|
||
`title` VARCHAR(50) DEFAULT NULL COMMENT '职称',
|
||
`activation_code` VARCHAR(50) DEFAULT NULL COMMENT '激活码',
|
||
`binding_status` TINYINT(1) DEFAULT '0' COMMENT '绑定状态：0-未绑定，1-已绑定',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_id` (`user_id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_college_id` (`college_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教师扩展表';
|
||
|
||
-- 4. 通知表
|
||
DROP TABLE IF EXISTS `sys_notification`;
|
||
CREATE TABLE `sys_notification` (
|
||
`id` BIGINT NOT NULL COMMENT '主键ID',
|
||
`school_id` BIGINT DEFAULT NULL COMMENT '学校ID',
|
||
`user_id` BIGINT DEFAULT NULL COMMENT '接收用户ID',
|
||
`title` VARCHAR(255) DEFAULT NULL COMMENT '通知标题',
|
||
`content` TEXT COMMENT '通知内容',
|
||
`type` TINYINT DEFAULT NULL COMMENT '通知类型',
|
||
`is_read` TINYINT DEFAULT '0' COMMENT '是否已读 0未读 1已读',
|
||
`sender_id` BIGINT DEFAULT NULL COMMENT '发送人ID',
|
||
`sender_name` VARCHAR(64) DEFAULT NULL COMMENT '发送人姓名',
|
||
`target_type` TINYINT DEFAULT NULL COMMENT '目标类型',
|
||
`create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_school_id` (`school_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知表';
|
||
|
||
-- 5. 课程表
|
||
DROP TABLE IF EXISTS `edu_course`;
|
||
CREATE TABLE `edu_course` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '课程名称',
|
||
`cover` VARCHAR(255) DEFAULT NULL COMMENT '课程封面',
|
||
`description` TEXT DEFAULT NULL COMMENT '课程简介',
|
||
`class_time` VARCHAR(100) DEFAULT NULL COMMENT '上课时间',
|
||
`teaching_method` TINYINT(1) DEFAULT '1' COMMENT '授课方式：1-线上，2-线下，3-混合',
|
||
`teacher_id` BIGINT(20) NOT NULL COMMENT '创建老师ID',
|
||
`status` TINYINT(1) DEFAULT '1' COMMENT '课程状态：1-进行中，2-已结课',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_teacher_id` (`teacher_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';
|
||
|
||
-- 6. 课程学生关联表
|
||
DROP TABLE IF EXISTS `edu_course_student`;
|
||
CREATE TABLE `edu_course_student` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`course_id` BIGINT(20) NOT NULL COMMENT '课程ID',
|
||
`student_id` BIGINT(20) NOT NULL COMMENT '学生ID',
|
||
`join_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
|
||
`is_kicked` TINYINT(1) DEFAULT '0' COMMENT '是否被踢出：0-正常，1-已踢出',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_course_student` (`course_id`, `student_id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_course_id` (`course_id`),
|
||
KEY `idx_student_id` (`student_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程学生关联表';
|
||
|
||
-- 7. 作业表
|
||
DROP TABLE IF EXISTS `edu_homework`;
|
||
CREATE TABLE `edu_homework` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`course_id` BIGINT(20) NOT NULL COMMENT '课程ID',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '作业名称',
|
||
`requirement` TEXT DEFAULT NULL COMMENT '作业要求',
|
||
`questions` TEXT NOT NULL COMMENT '题目JSON',
|
||
`allow_late` TINYINT(1) DEFAULT '0' COMMENT '是否允许迟交',
|
||
`start_time` DATETIME NOT NULL COMMENT '开始时间',
|
||
`end_time` DATETIME NOT NULL COMMENT '截止时间',
|
||
`status` TINYINT(1) DEFAULT '1' COMMENT '状态：1-草稿，2-已发布',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_course_id` (`course_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='作业表';
|
||
|
||
-- 8. 作业提交表
|
||
DROP TABLE IF EXISTS `edu_homework_submit`;
|
||
CREATE TABLE `edu_homework_submit` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`homework_id` BIGINT(20) NOT NULL COMMENT '作业ID',
|
||
`student_id` BIGINT(20) NOT NULL COMMENT '学生ID',
|
||
`answers` TEXT NOT NULL COMMENT '答案JSON',
|
||
`submit_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '提交时间',
|
||
`is_late` TINYINT(1) DEFAULT '0' COMMENT '是否迟交',
|
||
`status` TINYINT(1) DEFAULT '1' COMMENT '状态：1-待批改，2-已批改，3-已退回',
|
||
`score` DECIMAL(5,2) DEFAULT NULL COMMENT '分数',
|
||
`comment` TEXT DEFAULT NULL COMMENT '评语',
|
||
`is_excellent` TINYINT(1) DEFAULT '0' COMMENT '是否优秀',
|
||
`redo_end_time` DATETIME DEFAULT NULL COMMENT '重做截止时间',
|
||
`redo_count` INT(11) DEFAULT '0' COMMENT '重做次数',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_homework_id` (`homework_id`),
|
||
KEY `idx_student_id` (`student_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='作业提交表';
|
||
|
||
-- 9. 考试表
|
||
DROP TABLE IF EXISTS `edu_exam`;
|
||
CREATE TABLE `edu_exam` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`course_id` BIGINT(20) NOT NULL COMMENT '课程ID',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '考试名称',
|
||
`requirement` TEXT DEFAULT NULL COMMENT '考试要求',
|
||
`questions` TEXT NOT NULL COMMENT '考卷JSON',
|
||
`duration` INT(11) NOT NULL COMMENT '考试时长（分钟）',
|
||
`start_time` DATETIME NOT NULL COMMENT '开始时间',
|
||
`end_time` DATETIME NOT NULL COMMENT '截止时间',
|
||
`status` TINYINT(1) DEFAULT '1' COMMENT '状态：1-草稿，2-已发布，3-已结束',
|
||
`is_makeup` TINYINT(1) DEFAULT '0' COMMENT '是否补考',
|
||
`parent_exam_id` BIGINT(20) DEFAULT NULL COMMENT '关联主考ID',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_course_id` (`course_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试表';
|
||
|
||
-- 10. 考试答卷表
|
||
DROP TABLE IF EXISTS `edu_exam_paper`;
|
||
CREATE TABLE `edu_exam_paper` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`exam_id` BIGINT(20) NOT NULL COMMENT '考试ID',
|
||
`student_id` BIGINT(20) NOT NULL COMMENT '学生ID',
|
||
`answers` TEXT NOT NULL COMMENT '答案JSON',
|
||
`submit_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '提交时间',
|
||
`status` TINYINT(1) DEFAULT '1' COMMENT '状态：1-待阅卷，2-已阅卷',
|
||
`score` DECIMAL(5,2) DEFAULT NULL COMMENT '分数',
|
||
`comment` TEXT DEFAULT NULL COMMENT '评语',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_exam_student` (`exam_id`, `student_id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_exam_id` (`exam_id`),
|
||
KEY `idx_student_id` (`student_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试答卷表';
|
||
|
||
-- 11. 考勤表
|
||
DROP TABLE IF EXISTS `edu_attendance`;
|
||
CREATE TABLE `edu_attendance` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`course_id` BIGINT(20) NOT NULL COMMENT '课程ID',
|
||
`name` VARCHAR(100) DEFAULT NULL COMMENT '签到名称（按时间自动生成）',
|
||
`start_time` DATETIME NOT NULL COMMENT '开始时间',
|
||
`duration` INT(11) DEFAULT '15' COMMENT '签到时长（分钟）',
|
||
`type` TINYINT(1) DEFAULT '1' COMMENT '签到类型：1-二维码签到，2-手动点名',
|
||
`status` TINYINT(1) DEFAULT '1' COMMENT '状态：1-进行中，2-已结束',
|
||
`qr_code` VARCHAR(255) DEFAULT NULL COMMENT '二维码内容',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_course_id` (`course_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤表';
|
||
|
||
-- 12. 考勤记录表
|
||
DROP TABLE IF EXISTS `edu_attendance_record`;
|
||
CREATE TABLE `edu_attendance_record` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`attendance_id` BIGINT(20) NOT NULL COMMENT '考勤ID',
|
||
`student_id` BIGINT(20) NOT NULL COMMENT '学生ID',
|
||
`status` TINYINT(1) DEFAULT '1' COMMENT '考勤状态：1-出勤，2-迟到，3-缺勤，4-请假',
|
||
`sign_time` DATETIME DEFAULT NULL COMMENT '签到时间',
|
||
`remark` VARCHAR(255) DEFAULT NULL COMMENT '备注（手动点名时填写）',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_attendance_student` (`attendance_id`, `student_id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_attendance_id` (`attendance_id`),
|
||
KEY `idx_student_id` (`student_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤记录表';
|
||
|
||
-- 13. 优秀作品表
|
||
DROP TABLE IF EXISTS `edu_excellent_work`;
|
||
CREATE TABLE `edu_excellent_work` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`work_type` TINYINT(1) NOT NULL COMMENT '作品类型：1-作业，2-考试',
|
||
`work_id` BIGINT(20) NOT NULL COMMENT '关联作业/考试ID',
|
||
`submit_id` BIGINT(20) NOT NULL COMMENT '关联提交ID',
|
||
`student_id` BIGINT(20) NOT NULL COMMENT '学生ID',
|
||
`score` DECIMAL(5,2) DEFAULT NULL COMMENT '分数',
|
||
`comment` TEXT DEFAULT NULL COMMENT '评语',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '标记时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_school_id` (`school_id`),
|
||
KEY `idx_work_type_work_id` (`work_type`, `work_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优秀作品表';
|
||
|
||
-- 14. 作品点赞表
|
||
DROP TABLE IF EXISTS `edu_work_like`;
|
||
CREATE TABLE `edu_work_like` (
|
||
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`school_id` BIGINT(20) NOT NULL COMMENT '学校ID（数据隔离）',
|
||
`work_id` BIGINT(20) NOT NULL COMMENT '优秀作品ID',
|
||
`user_id` BIGINT(20) NOT NULL COMMENT '点赞用户ID',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '点赞时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_work_user` (`work_id`, `user_id`),
|
||
KEY `idx_school_id` (`school_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='作品点赞表';
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1; |