Navicat Premium Data Transfer
Source Server : myconn
Source Server Type : MySQL
Source Server Version : 80043 (8.0.43)
Source Host : localhost:3306
Source Schema : course_selection
Target Server Type : MySQL
Target Server Version : 80043 (8.0.43)
File Encoding : 65001
Date: 12/11/2025 22:31:14
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '管理员id',
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员账号',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '管理员密码',
`privilege` int NOT NULL COMMENT '管理员权限',
`role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '角色',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_admin_username`(`username` ASC) USING BTREE COMMENT '账号唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '管理员表' ROW_FORMAT = Dynamic;
INSERT INTO `admin` VALUES (1, '张三', '123456', 255, 'ADMIN');
INSERT INTO `admin` VALUES (2, '李四', '123456', 3, 'ADMIN');
INSERT INTO `admin` VALUES (3, '王五', '123456', 102, 'ADMIN');
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '班级id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '班级名称',
`grade` year NOT NULL COMMENT '班级年级',
`major_id` int UNSIGNED NOT NULL COMMENT '关联到major.id，表示所属专业',
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_class_major_major_id`(`major_id` ASC) USING BTREE COMMENT '专业索引',
INDEX `idx_class_name`(`name` ASC) USING BTREE COMMENT '名字索引',
CONSTRAINT `fk_class_major_major_id` FOREIGN KEY (`major_id`) REFERENCES `major` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '班级表' ROW_FORMAT = Dynamic;
INSERT INTO `class` VALUES (1, '软件工程一班', 2025, 1);
INSERT INTO `class` VALUES (2, '软件工程二班', 2025, 1);
INSERT INTO `class` VALUES (3, '软件工程三班', 2025, 1);
INSERT INTO `class` VALUES (4, '化学一班', 2025, 2);
INSERT INTO `class` VALUES (5, '化学二班', 2025, 2);
INSERT INTO `class` VALUES (6, '化学三班', 2025, 2);
INSERT INTO `class` VALUES (7, '网络安全一班', 2025, 1);
INSERT INTO `class` VALUES (8, '软工一班', 2025, 1);
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '课程名称',
`location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '授课地点',
`credit` int UNSIGNED NOT NULL COMMENT '课程学分',
`max_size` int UNSIGNED NOT NULL COMMENT '最大选择人数',
`select_count` int UNSIGNED NOT NULL COMMENT '课程选择人数',
`exam_date` date NULL DEFAULT NULL COMMENT '考试日期',
`exam_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '考试地点',
`time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '授课时间',
`grade` year NOT NULL COMMENT '课程年级',
`teacher_id` int UNSIGNED NOT NULL COMMENT '关联teacher.id，表示任课教师',
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_course_teacher_id`(`teacher_id` ASC) USING BTREE COMMENT '任课教师索引',
INDEX `idx_course_name`(`name` ASC) USING BTREE COMMENT '课程名称索引',
CONSTRAINT `fk_course_teacher_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;
INSERT INTO `course` VALUES (1, 'java程序设计', '东三205', 2, 50, 34, '2025-11-23', '东三205', '2-2-2', 2025, 1);
INSERT INTO `course` VALUES (2, '操作系统', '东三109', 2, 55, 35, '2025-11-29', '东三109', '4-4-2', 2025, 1);
INSERT INTO `course` VALUES (3, '软件工程', '东三305', 2, 60, 0, NULL, '东三305', '2-4-2', 2025, 1);
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学院id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学院名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学院表' ROW_FORMAT = Dynamic;
INSERT INTO `department` VALUES (1, '计算机与大数据学院');
INSERT INTO `department` VALUES (2, '化工学院');
INSERT INTO `department` VALUES (3, '梅努斯国际工程学院');
DROP TABLE IF EXISTS `major`;
CREATE TABLE `major` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '专业id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '专业名称',
`department_id` int UNSIGNED NOT NULL COMMENT '关联到department.id，表示所属学院',
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_major_department_department_id`(`department_id` ASC) USING BTREE COMMENT '学院索引',
INDEX `idx_major_name`(`name` ASC) USING BTREE COMMENT '名字索引',
CONSTRAINT `fk_major_department_department_id` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '专业表' ROW_FORMAT = Dynamic;
INSERT INTO `major` VALUES (1, '软件工程', 1);
INSERT INTO `major` VALUES (2, '化学', 2);
INSERT INTO `major` VALUES (3, '网络安全', 1);
INSERT INTO `major` VALUES (4, '药剂', 2);
INSERT INTO `major` VALUES (5, '计算机', 1);
INSERT INTO `major` VALUES (6, '人工智能', 1);
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学号',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',
`sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '性别',
`birthday` date NULL DEFAULT NULL COMMENT '生日',
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '邮箱',
`last_login_time` date NULL DEFAULT NULL COMMENT '上次登录时间',
`class_id` int UNSIGNED NOT NULL COMMENT '关联到class.id，表示所属班级',
`role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '角色',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_student_number`(`username` ASC) USING BTREE COMMENT '学号索引',
INDEX `fk_student_class_class_id`(`class_id` ASC) USING BTREE COMMENT '班级索引',
INDEX `idx_student_name`(`name` ASC) USING BTREE COMMENT '学生姓名索引',
CONSTRAINT `fk_student_class_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES (1, '102300331', '123456', '叶润升', '男', '2005-05-26', '2086434490@qq.com', '2025-10-20', 1, 'STU');
DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '选课记录id',
`daily_score` int UNSIGNED NULL DEFAULT NULL COMMENT '学生选课平时分',
`exam_score` int UNSIGNED NULL DEFAULT NULL COMMENT '学生期末考试成绩',
`score` int UNSIGNED NULL DEFAULT NULL COMMENT '学生最终成绩',
`student_id` int UNSIGNED NOT NULL COMMENT '关联到student.id，表示选课对应的学生id',
`course_id` int UNSIGNED NOT NULL COMMENT '关联到course.id，表示学生对应选课id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_student_course_student_student_id`(`student_id` ASC) USING BTREE,
INDEX `fk_student_course_course_course_id`(`course_id` ASC) USING BTREE,
CONSTRAINT `fk_student_course_course_course_id` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_student_course_student_student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生选课信息表' ROW_FORMAT = Dynamic;
INSERT INTO `student_course` VALUES (1, 24, 60, 83, 1, 1);
INSERT INTO `student_course` VALUES (7, NULL, NULL, NULL, 1, 3);
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '教师id',
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '教师工号',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '教师密码',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '教师姓名',
`department_id` int UNSIGNED NOT NULL COMMENT '关联到department.id，表示所属学院',
`role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '角色',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_teacher_number`(`username` ASC) USING BTREE COMMENT '教师工号索引',
INDEX `fk_teacher_department_department_id`(`department_id` ASC) USING BTREE COMMENT '学院索引',
CONSTRAINT `fk_teacher_department_department_id` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '教师表' ROW_FORMAT = Dynamic;
INSERT INTO `teacher` VALUES (1, '3426', '123456', '张老师', 1, 'TEA');
INSERT INTO `teacher` VALUES (2, '165353', '123456', '王老师', 2, 'TEA');
INSERT INTO `teacher` VALUES (3, '5364527', '123456', '夏老师', 1, 'TEA');
SET FOREIGN_KEY_CHECKS = 1;