31 lines
1.4 KiB
SQL
31 lines
1.4 KiB
SQL
-- ===========================================
|
||
-- 班级操行分管理系统 - v1.4 → v1.5 升级脚本
|
||
-- 字符集: utf8mb4
|
||
--
|
||
-- 变更内容:
|
||
-- 1. 创建 semester_archives（学期归档快照表）
|
||
--
|
||
-- 兼容性: 使用 IF NOT EXISTS 实现幂等，phpMyAdmin 可直接执行
|
||
-- ===========================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `semester_archives` (
|
||
`archive_id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`semester_id` INT NOT NULL,
|
||
`student_id` INT NOT NULL,
|
||
`student_no` VARCHAR(20) NOT NULL,
|
||
`student_name` VARCHAR(50) NOT NULL,
|
||
`final_points` INT NOT NULL COMMENT '学期最终操行分',
|
||
`rank_position` INT DEFAULT NULL COMMENT '排名',
|
||
`total_students` INT DEFAULT NULL COMMENT '班级总人数',
|
||
`attendance_present` INT DEFAULT 0 COMMENT '出勤次数',
|
||
`attendance_absent` INT DEFAULT 0 COMMENT '缺勤次数',
|
||
`attendance_late` INT DEFAULT 0 COMMENT '迟到次数',
|
||
`attendance_leave` INT DEFAULT 0 COMMENT '请假次数',
|
||
`homework_submitted` INT DEFAULT 0 COMMENT '已交作业数',
|
||
`homework_not_submitted` INT DEFAULT 0 COMMENT '未交作业数',
|
||
`homework_late` INT DEFAULT 0 COMMENT '迟交作业数',
|
||
`archived_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (`semester_id`) REFERENCES `semesters`(`semester_id`),
|
||
FOREIGN KEY (`student_id`) REFERENCES `students`(`student_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|