/* Navicat Premium Dump SQL Source Server : localhost Source Server Type : MySQL Source Server Version : 80044 (8.0.44) Source Host : localhost:3306 Source Schema : course_selection_system Target Server Type : MySQL Target Server Version : 80044 (8.0.44) File Encoding : 65001 Date: 29/12/2025 17:28:56 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for admin_registration_request -- ---------------------------- DROP TABLE IF EXISTS `admin_registration_request`; CREATE TABLE `admin_registration_request` ( `request_id` int NOT NULL AUTO_INCREMENT COMMENT '请求ID', `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '申请用户名', `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '加密后的密码', `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名', `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱', `status` enum('pending','approved','rejected') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'pending' COMMENT '申请状态：pending-待审核，approved-已批准，rejected-已拒绝', `request_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间', `approved_by` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '批准人', `approved_time` datetime NULL DEFAULT NULL COMMENT '批准时间', `reason` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '拒绝原因', PRIMARY KEY (`request_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '管理员注册申请表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of admin_registration_request -- ---------------------------- INSERT INTO `admin_registration_request` VALUES (1, 'admin', '53747564656e7453797374656d32303234:7d8643b174832c7ffc557f6f1b8a80219c6afd6a8ac76ed5210d4e3fdab24757', '管理员', 'adf012@qq.com', 'approved', '2025-12-27 20:39:19', 'super_admin', '2025-12-27 20:51:09', NULL); -- ---------------------------- -- Table structure for college -- ---------------------------- DROP TABLE IF EXISTS `college`; CREATE TABLE `college` ( `college_id` int NOT NULL AUTO_INCREMENT COMMENT '学院ID', `college_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学院名称', `college_leader` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学院院长', `college_vice_leader` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学院副院长', `college_intro` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '学院介绍', `college_major` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '开设专业', `min_credit` int NOT NULL COMMENT '最低学分', PRIMARY KEY (`college_id`) USING BTREE, UNIQUE INDEX `college_name`(`college_name` ASC) USING BTREE, UNIQUE INDEX `college_leader`(`college_leader` ASC) USING BTREE, UNIQUE INDEX `college_vice_leader`(`college_vice_leader` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学院信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of college -- ---------------------------- INSERT INTO `college` VALUES (2, '信息工程学院', '李建华', '李华', '特瑞完全封闭环境下出v觉得睡不好菲达环保v', '软件工程、大数据技术', 100); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `course_id` int NOT NULL AUTO_INCREMENT COMMENT '课程ID', `course_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程名称', `course_intro` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '课程介绍', `credit` int NOT NULL COMMENT '学分', `college_id` int NOT NULL COMMENT '所属学院ID', `teacher_username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '授课教师用户名', `class_size` int NOT NULL COMMENT '开班人数', `class_time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '上课时段', `class_place` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '上课地点', `selected_count` int NULL DEFAULT 0 COMMENT '已选人数', PRIMARY KEY (`course_id`) USING BTREE, INDEX `college_id`(`college_id` ASC) USING BTREE, INDEX `teacher_username`(`teacher_username` ASC) USING BTREE, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`college_id`) REFERENCES `college` (`college_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '课程信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (1, 'java高级程序设计', NULL, 2, 2, '教师1', 30, '周一 34节', 'A101', 1); -- ---------------------------- -- Table structure for major -- ---------------------------- DROP TABLE IF EXISTS `major`; CREATE TABLE `major` ( `major_id` int NOT NULL AUTO_INCREMENT COMMENT '专业ID', `major_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '专业名称', `department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '系名', `college_id` int NOT NULL COMMENT '所属学院ID', PRIMARY KEY (`major_id`) USING BTREE, INDEX `college_id`(`college_id` ASC) USING BTREE, CONSTRAINT `major_ibfk_1` FOREIGN KEY (`college_id`) REFERENCES `college` (`college_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '专业信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of major -- ---------------------------- INSERT INTO `major` VALUES (1, '软件工程', '信息工程学院', 2); -- ---------------------------- -- Table structure for sc -- ---------------------------- DROP TABLE IF EXISTS `sc`; CREATE TABLE `sc` ( `sc_id` int NOT NULL AUTO_INCREMENT COMMENT '选课记录ID', `course_id` int NOT NULL COMMENT '课程ID', `sc_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程名', `teacher_username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '授课教师用户名', `student_username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '选课学生用户名', PRIMARY KEY (`sc_id`) USING BTREE, UNIQUE INDEX `uk_course_student`(`course_id` ASC, `student_username` ASC) USING BTREE COMMENT '学生不能重复选同一门课', INDEX `sc_ibfk_2`(`teacher_username` ASC) USING BTREE, INDEX `sc_ibfk_4`(`sc_name` ASC) USING BTREE, INDEX `sc_ibfk_5`(`student_username` ASC) USING BTREE, CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`teacher_username`) REFERENCES `user` (`name`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `sc_ibfk_3` FOREIGN KEY (`student_username`) REFERENCES `user` (`name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '选课信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of sc -- ---------------------------- INSERT INTO `sc` VALUES (7, 1, 'java高级程序设计', '教师1', '张三'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `course_id` int NOT NULL COMMENT '课程id', `course_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程名', `class_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '班级', `score` int NOT NULL COMMENT '课程成绩', `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生名字', PRIMARY KEY (`course_id`, `username`) USING BTREE, UNIQUE INDEX `uk_course_student`(`course_id` ASC, `username` ASC) USING BTREE, INDEX `score2`(`username` ASC) USING BTREE, CONSTRAINT `score1` FOREIGN KEY (`username`) REFERENCES `user` (`name`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `score2` FOREIGN KEY (`username`) REFERENCES `student_info` (`username`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生成绩表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES (1, 'java高级程序设计', '23513003', 78, '张三'); -- ---------------------------- -- Table structure for student_info -- ---------------------------- DROP TABLE IF EXISTS `student_info`; CREATE TABLE `student_info` ( `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生用户名（关联user表）', `gender` enum('男','女','其他') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别', `age` int NOT NULL COMMENT '年龄', `school_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学校', `class_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '班级', `student_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学号', `total_credit` int NULL DEFAULT 0 COMMENT '总学分', `college_id` int NOT NULL COMMENT '所属学院ID', `college_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '所属学院', `major_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '专业名称', `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '家庭地址', `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '个人电话', PRIMARY KEY (`username`) USING BTREE, UNIQUE INDEX `student_id`(`student_id` ASC) USING BTREE, INDEX `college_id`(`college_id` ASC) USING BTREE, CONSTRAINT `fk_student_id_idcard` FOREIGN KEY (`student_id`) REFERENCES `user` (`idcard`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `student_info_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`name`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `student_info_ibfk_2` FOREIGN KEY (`college_id`) REFERENCES `college` (`college_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生详细信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student_info -- ---------------------------- INSERT INTO `student_info` VALUES ('张三', '男', 21, '桂林信息科技学院', '23513003', '23513002', 186, 2, '信息工程学院', '软件工程', '北京市海淀区', '19999999999'); -- ---------------------------- -- Table structure for system_permission -- ---------------------------- DROP TABLE IF EXISTS `system_permission`; CREATE TABLE `system_permission` ( `id` int NOT NULL AUTO_INCREMENT, `permission_key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限键', `permission_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权限名称', `permission_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '权限描述', `enabled` tinyint(1) NULL DEFAULT 0 COMMENT '是否启用', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `permission_key`(`permission_key` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '系统权限配置表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of system_permission -- ---------------------------- INSERT INTO `system_permission` VALUES (1, 'student_edit_profile', '学生修改个人信息', '允许学生修改自己的个人信息', 1); INSERT INTO `system_permission` VALUES (2, 'teacher_edit_profile', '教师修改个人信息', '允许教师修改自己的个人信息', 1); INSERT INTO `system_permission` VALUES (8, 'course_selection_enabled', '选课功能开关', '控制学生是否可以进入选课中心进行选课操作，关闭后学生无法选课', 1); -- ---------------------------- -- Table structure for teacher_info -- ---------------------------- DROP TABLE IF EXISTS `teacher_info`; CREATE TABLE `teacher_info` ( `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '教师用户名（关联user表）', `gender` enum('男','女','其他') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '性别', `age` int NOT NULL COMMENT '年龄', `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '家庭住址', `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '电话', `school_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '任教学校', `teaching_time` int NOT NULL COMMENT '任教时间', `title` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '职称', `major_id` int NOT NULL COMMENT '所属专业ID', PRIMARY KEY (`username`) USING BTREE, INDEX `major_id`(`major_id` ASC) USING BTREE, CONSTRAINT `teacher_info_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`name`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `teacher_info_ibfk_2` FOREIGN KEY (`major_id`) REFERENCES `major` (`major_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '教师详细信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of teacher_info -- ---------------------------- INSERT INTO `teacher_info` VALUES ('教师1', '男', 28, '桂林市临桂区', '19999999999', '桂林信息科技学院', 2, '初级教师', 1); -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名', `idcard` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学号、工号等', `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码（建议加密存储）', `role` enum('admin','teacher','student') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '角色：管理员/教师/学生', `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名', `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '头像路径', `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱', `status` enum('active','pending','inactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'active' COMMENT '账户状态：active-激活，pending-待审核，inactive-未激活', PRIMARY KEY (`username`) USING BTREE, INDEX `name`(`name` ASC) USING BTREE, INDEX `idcard`(`idcard` ASC) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户基础信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES ('admin', '', '53747564656e7453797374656d32303234:7d8643b174832c7ffc557f6f1b8a80219c6afd6a8ac76ed5210d4e3fdab24757', 'admin', '管理员', NULL, 'adf012@qq.com', 'active'); INSERT INTO `user` VALUES ('superadmin', '', '53747564656e7453797374656d32303234:7d8643b174832c7ffc557f6f1b8a80219c6afd6a8ac76ed5210d4e3fdab24757', 'admin', '超级管理员', NULL, 'asd012@qq.com', 'active'); INSERT INTO `user` VALUES ('tea1', '23513001', '53747564656e7453797374656d32303234:2203c15ecf3e686f60b88374c66e2a87e72108fafa07be5249a4d06ac18d5e62', 'teacher', '教师1', NULL, 'asd3@qq.com', 'active'); INSERT INTO `user` VALUES ('zhangsan', '23513002', '53747564656e7453797374656d32303234:2203c15ecf3e686f60b88374c66e2a87e72108fafa07be5249a4d06ac18d5e62', 'student', '张三', NULL, 'adf012@qq.com', 'active'); SET FOREIGN_KEY_CHECKS = 1;