/*
 Navicat Premium Dump SQL

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80405 (8.4.5)
 Source Host           : localhost:3306
 Source Schema         : school

 Target Server Type    : MySQL
 Target Server Version : 80405 (8.4.5)
 File Encoding         : 65001

 Date: 23/08/2025 23:26:17
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`  (
  `score_id` int NOT NULL AUTO_INCREMENT COMMENT '成绩ID',
  `student_id` int NOT NULL COMMENT '学生ID',
  `subject_id` int NOT NULL COMMENT '科目ID',
  `exam_date` date NOT NULL COMMENT '考试日期',
  `exam_type` enum('期中','期末','单元测试','月考') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '期末' COMMENT '考试类型',
  `score` decimal(5, 2) NOT NULL COMMENT '分数',
  `grade_level` enum('优秀','良好','及格','不及格') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '等级',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`score_id`) USING BTREE,
  UNIQUE INDEX `uk_student_subject_exam`(`student_id` ASC, `subject_id` ASC, `exam_date` ASC, `exam_type` ASC) USING BTREE,
  INDEX `subject_id`(`subject_id` ASC) USING BTREE,
  CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `scores_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '成绩信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES (1, 1, 1, '2024-01-15', '期末', 95.50, '优秀', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (2, 1, 2, '2024-01-15', '期末', 88.00, '良好', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (3, 1, 3, '2024-01-15', '期末', 92.50, '优秀', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (4, 2, 1, '2024-01-15', '期末', 85.00, '良好', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (5, 2, 2, '2024-01-15', '期末', 78.50, '及格', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (6, 2, 3, '2024-01-15', '期末', 91.00, '优秀', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (7, 3, 1, '2024-01-15', '期末', 92.00, '优秀', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (8, 3, 2, '2024-01-15', '期末', 95.50, '优秀', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (9, 3, 3, '2024-01-15', '期末', 87.00, '良好', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (10, 4, 1, '2024-01-15', '期末', 76.00, '及格', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (11, 4, 2, '2024-01-15', '期末', 82.50, '良好', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (12, 4, 3, '2024-01-15', '期末', 79.00, '及格', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (13, 5, 1, '2024-01-15', '期末', 89.00, '良好', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (14, 5, 2, '2024-01-15', '期末', 93.50, '优秀', '2025-08-22 12:45:09');
INSERT INTO `scores` VALUES (15, 5, 3, '2024-01-15', '期末', 86.00, '良好', '2025-08-22 12:45:09');

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`  (
  `student_id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID',
  `student_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学号',
  `student_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学生姓名',
  `gender` enum('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '男' COMMENT '性别',
  `class_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '班级名称',
  `birth_date` date NULL DEFAULT NULL COMMENT '出生日期',
  `parent_phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '家长电话',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`student_id`) USING BTREE,
  UNIQUE INDEX `uk_student_no`(`student_no` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '20230101', '小明', '男', '三年级一班', '2015-03-15', '13800000001', '2025-08-22 12:45:09');
INSERT INTO `students` VALUES (2, '20230102', '小红', '女', '三年级一班', '2015-05-20', '13800000002', '2025-08-22 12:45:09');
INSERT INTO `students` VALUES (3, '20230103', '小刚', '男', '三年级二班', '2015-02-10', '13800000003', '2025-08-22 12:45:09');
INSERT INTO `students` VALUES (4, '20230104', '小丽', '女', '三年级二班', '2015-07-25', '13800000004', '2025-08-22 12:45:09');
INSERT INTO `students` VALUES (5, '20230105', '小华', '男', '三年级一班', '2015-04-18', '13800000005', '2025-08-22 12:45:09');

-- ----------------------------
-- Table structure for subjects
-- ----------------------------
DROP TABLE IF EXISTS `subjects`;
CREATE TABLE `subjects`  (
  `subject_id` int NOT NULL AUTO_INCREMENT COMMENT '科目ID',
  `subject_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '科目名称',
  `teacher_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '任课老师',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`subject_id`) USING BTREE,
  UNIQUE INDEX `uk_subject_name`(`subject_name` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '科目信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of subjects
-- ----------------------------
INSERT INTO `subjects` VALUES (1, '语文', '张老师', '2025-08-22 12:45:09');
INSERT INTO `subjects` VALUES (2, '数学', '李老师', '2025-08-22 12:45:09');
INSERT INTO `subjects` VALUES (3, '英语', '王老师', '2025-08-22 12:45:09');
INSERT INTO `subjects` VALUES (4, '科学', '赵老师', '2025-08-22 12:45:09');

SET FOREIGN_KEY_CHECKS = 1;
