240 lines
11 KiB
SQL
240 lines
11 KiB
SQL
-- ===========================================
|
||
-- 班级操行分管理系统 - 数据库初始化脚本
|
||
-- 数据库: classmanagerdb
|
||
-- 字符集: utf8mb4
|
||
--
|
||
-- 开发者: Canglan
|
||
-- 联系方式: admin@sea-studio.top
|
||
-- 版权归属: Sea Network Technology Studio
|
||
-- 许可证: MIT License
|
||
--
|
||
-- 版权所有 © Sea Network Technology Studio
|
||
-- ===========================================
|
||
|
||
CREATE DATABASE IF NOT EXISTS `classmanagerdb`
|
||
CHARACTER SET utf8mb4
|
||
COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE `classmanagerdb`;
|
||
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- 学期表
|
||
CREATE TABLE IF NOT EXISTS `semesters` (
|
||
`semester_id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`semester_name` VARCHAR(100) NOT NULL COMMENT '学期名称，如 2025春季学期',
|
||
`start_date` DATE DEFAULT NULL COMMENT '学期开始日期',
|
||
`end_date` DATE DEFAULT NULL COMMENT '学期结束日期',
|
||
`is_active` TINYINT DEFAULT 0 COMMENT '是否为当前活跃学期',
|
||
`is_archived` TINYINT DEFAULT 0 COMMENT '是否已归档',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 科目表（仅保留语数英）
|
||
CREATE TABLE IF NOT EXISTS `subjects` (
|
||
`subject_id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '科目ID',
|
||
`subject_name` VARCHAR(50) NOT NULL COMMENT '科目名称',
|
||
`subject_code` VARCHAR(20) DEFAULT NULL COMMENT '科目代码',
|
||
`is_active` TINYINT DEFAULT 1 COMMENT '是否启用',
|
||
`sort_order` INT DEFAULT 0 COMMENT '排序序号',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY `uk_subject_name` (`subject_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 学生表（无班级ID）
|
||
CREATE TABLE IF NOT EXISTS `students` (
|
||
`student_id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`student_no` VARCHAR(20) NOT NULL UNIQUE,
|
||
`name` VARCHAR(50) NOT NULL,
|
||
`total_points` INT DEFAULT 60,
|
||
`parent_phone` VARCHAR(20) DEFAULT NULL,
|
||
`dormitory_number` VARCHAR(20) DEFAULT NULL COMMENT '宿舍号',
|
||
`status` TINYINT DEFAULT 1,
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`points_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '分数最后更新时间',
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 用户表
|
||
CREATE TABLE IF NOT EXISTS `users` (
|
||
`user_id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`username` VARCHAR(50) NOT NULL UNIQUE,
|
||
`password_hash` VARCHAR(255) NOT NULL,
|
||
`real_name` VARCHAR(50) NOT NULL,
|
||
`user_type` ENUM('student', 'parent', 'admin') NOT NULL,
|
||
`student_id` INT DEFAULT NULL,
|
||
`status` TINYINT DEFAULT 1,
|
||
`need_change_password` TINYINT DEFAULT 1,
|
||
`last_login_time` DATETIME DEFAULT NULL,
|
||
`last_login_ip` VARCHAR(45) DEFAULT NULL,
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (`student_id`) REFERENCES `students`(`student_id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 管理员角色表（无班级ID）
|
||
CREATE TABLE IF NOT EXISTS `admin_roles` (
|
||
`admin_role_id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`user_id` INT NOT NULL,
|
||
`role_type` ENUM('班主任', '班长', '学习委员', '考勤委员', '劳动委员', '志愿委员') NOT NULL,
|
||
`subject_id` INT DEFAULT NULL,
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`subject_id`) ON DELETE CASCADE,
|
||
UNIQUE KEY `uk_user_subject` (`user_id`, `subject_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 操行分记录表
|
||
CREATE TABLE IF NOT EXISTS `conduct_records` (
|
||
`record_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
`student_id` INT NOT NULL,
|
||
`points_change` INT NOT NULL,
|
||
`reason` VARCHAR(255) NOT NULL,
|
||
`recorder_id` INT NOT NULL,
|
||
`recorder_name` VARCHAR(50) DEFAULT NULL,
|
||
`related_type` ENUM('manual', 'homework', 'attendance') DEFAULT 'manual',
|
||
`related_id` INT DEFAULT NULL,
|
||
`is_revoked` TINYINT DEFAULT 0,
|
||
`revoked_by` INT DEFAULT NULL,
|
||
`revoked_at` DATETIME DEFAULT NULL,
|
||
`semester_id` INT DEFAULT NULL COMMENT '所属学期ID',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (`student_id`) REFERENCES `students`(`student_id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`recorder_id`) REFERENCES `users`(`user_id`),
|
||
FOREIGN KEY (`revoked_by`) REFERENCES `users`(`user_id`),
|
||
FOREIGN KEY (`semester_id`) REFERENCES `semesters`(`semester_id`) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 作业表（无班级ID）
|
||
CREATE TABLE IF NOT EXISTS `assignments` (
|
||
`assignment_id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`subject_id` INT NOT NULL,
|
||
`title` VARCHAR(100) NOT NULL,
|
||
`description` TEXT,
|
||
`deadline` DATE NOT NULL,
|
||
`created_by` INT NOT NULL,
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`subject_id`),
|
||
FOREIGN KEY (`created_by`) REFERENCES `users`(`user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 作业提交记录表
|
||
CREATE TABLE IF NOT EXISTS `homework_submissions` (
|
||
`submission_id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`assignment_id` INT NOT NULL,
|
||
`student_id` INT NOT NULL,
|
||
`status` ENUM('submitted', 'not_submitted', 'late') DEFAULT 'not_submitted',
|
||
`submit_time` DATETIME DEFAULT NULL,
|
||
`comments` TEXT,
|
||
`deduction_applied` TINYINT DEFAULT 0,
|
||
`deduction_record_id` BIGINT DEFAULT NULL,
|
||
`updated_by` INT DEFAULT NULL,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (`assignment_id`) REFERENCES `assignments`(`assignment_id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`student_id`) REFERENCES `students`(`student_id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`deduction_record_id`) REFERENCES `conduct_records`(`record_id`) ON DELETE SET NULL,
|
||
UNIQUE KEY `uk_assignment_student` (`assignment_id`, `student_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 考勤记录表
|
||
CREATE TABLE IF NOT EXISTS `attendance_records` (
|
||
`attendance_id` INT PRIMARY KEY AUTO_INCREMENT,
|
||
`student_id` INT NOT NULL,
|
||
`date` DATE NOT NULL,
|
||
`slot` ENUM('morning', 'afternoon', 'evening') DEFAULT 'morning' COMMENT '考勤时段：早上/中午/晚修',
|
||
`status` ENUM('present', 'absent', 'late', 'leave') DEFAULT 'present',
|
||
`reason` VARCHAR(255) DEFAULT NULL,
|
||
`recorder_id` INT NOT NULL,
|
||
`deduction_applied` TINYINT DEFAULT 0,
|
||
`deduction_record_id` BIGINT DEFAULT NULL,
|
||
`semester_id` INT DEFAULT NULL COMMENT '所属学期ID',
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (`student_id`) REFERENCES `students`(`student_id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`recorder_id`) REFERENCES `users`(`user_id`),
|
||
FOREIGN KEY (`deduction_record_id`) REFERENCES `conduct_records`(`record_id`) ON DELETE SET NULL,
|
||
FOREIGN KEY (`semester_id`) REFERENCES `semesters`(`semester_id`) ON DELETE SET NULL,
|
||
UNIQUE KEY `uk_student_date_slot` (`student_id`, `date`, `slot`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 操作日志表
|
||
CREATE TABLE IF NOT EXISTS `operation_logs` (
|
||
`log_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
`operator_id` INT NOT NULL,
|
||
`operator_name` VARCHAR(50) DEFAULT NULL,
|
||
`operator_role` VARCHAR(50) DEFAULT NULL,
|
||
`operation_type` VARCHAR(50) NOT NULL,
|
||
`target_type` VARCHAR(50) DEFAULT NULL,
|
||
`target_id` INT DEFAULT NULL,
|
||
`details` TEXT,
|
||
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 登录日志表
|
||
CREATE TABLE IF NOT EXISTS `login_logs` (
|
||
`log_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
`username` VARCHAR(50) NOT NULL,
|
||
`login_result` TINYINT NOT NULL,
|
||
`fail_reason` VARCHAR(100) DEFAULT NULL,
|
||
`ip_address` VARCHAR(45) DEFAULT NULL,
|
||
`user_agent` VARCHAR(255) DEFAULT NULL,
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 学期归档快照表
|
||
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;
|
||
|
||
-- 系统设置表
|
||
CREATE TABLE IF NOT EXISTS `system_settings` (
|
||
`setting_key` VARCHAR(50) PRIMARY KEY,
|
||
`setting_value` VARCHAR(255) NOT NULL,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 性能索引（v2.0）
|
||
CREATE INDEX `idx_conduct_semester` ON `conduct_records`(`semester_id`);
|
||
CREATE INDEX `idx_attendance_semester` ON `attendance_records`(`semester_id`);
|
||
CREATE INDEX `idx_conduct_student` ON `conduct_records`(`student_id`);
|
||
|
||
-- 性能索引（v2.1）
|
||
CREATE INDEX `idx_student_created` ON `conduct_records`(`student_id`, `created_at`);
|
||
CREATE INDEX `idx_recorder_id` ON `conduct_records`(`recorder_id`);
|
||
CREATE INDEX `idx_date` ON `attendance_records`(`date`);
|
||
CREATE INDEX `idx_username_created` ON `login_logs`(`username`, `created_at`);
|
||
CREATE INDEX `idx_operator_created` ON `operation_logs`(`operator_id`, `created_at`);
|
||
CREATE INDEX `idx_semester_id` ON `semester_archives`(`semester_id`);
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- 插入初始科目（仅语数英，如不存在）
|
||
INSERT IGNORE INTO `subjects` (`subject_name`, `subject_code`, `sort_order`) VALUES
|
||
('语文', 'CHI', 1),
|
||
('数学', 'MATH', 2),
|
||
('英语', 'ENG', 3);
|
||
|
||
-- 初始化系统版本号
|
||
-- 初始化系统版本号
|
||
INSERT INTO `system_settings` (`setting_key`, `setting_value`)
|
||
VALUES ('db_version', '2.7')
|
||
ON DUPLICATE KEY UPDATE `setting_value` = '2.7';
|
||
-- 控制台输出初始化结果（含版本号）
|
||
SELECT CONCAT('数据库初始化完成！版本: v', (SELECT setting_value FROM system_settings WHERE setting_key = 'db_version')) AS message;
|