324 lines
17 KiB
SQL
324 lines
17 KiB
SQL
-- ============================================================
|
|
-- 学生课程管理系统 - MySQL 数据库建表脚本
|
|
-- 使用方法: mysql -u root -p < mysql_tables.sql
|
|
-- 注意: 此脚本必须与 models.py 中的模型定义完全一致
|
|
-- ============================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS student_course DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE student_course;
|
|
|
|
-- 1. 角色表
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称',
|
|
description VARCHAR(200) COMMENT '角色描述',
|
|
permissions TEXT COMMENT '权限列表(JSON格式)',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';
|
|
|
|
-- 2. 用户表（管理员、老师等）
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE COMMENT '登录用户名',
|
|
password VARCHAR(200) NOT NULL COMMENT '密码(哈希)',
|
|
real_name VARCHAR(50) NOT NULL COMMENT '真实姓名',
|
|
role_id INT NOT NULL COMMENT '角色ID',
|
|
phone VARCHAR(20) COMMENT '手机号',
|
|
email VARCHAR(100) COMMENT '邮箱',
|
|
status TINYINT DEFAULT 1 COMMENT '1-启用, 0-禁用',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (role_id) REFERENCES roles(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
|
|
|
|
-- 3. 老师表
|
|
CREATE TABLE IF NOT EXISTS teachers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT COMMENT '关联用户ID',
|
|
name VARCHAR(50) NOT NULL COMMENT '老师姓名',
|
|
phone VARCHAR(20) COMMENT '联系电话',
|
|
specialty VARCHAR(200) COMMENT '擅长科目',
|
|
status TINYINT DEFAULT 1 COMMENT '1-在职, 0-离职',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='老师表';
|
|
|
|
-- 4. 学员表
|
|
CREATE TABLE IF NOT EXISTS students (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(50) NOT NULL COMMENT '学员姓名',
|
|
gender VARCHAR(10) COMMENT '性别',
|
|
phone VARCHAR(20) COMMENT '联系电话',
|
|
parent_phone VARCHAR(20) COMMENT '家长电话',
|
|
birthday DATE COMMENT '出生日期',
|
|
address VARCHAR(200) COMMENT '地址',
|
|
remark TEXT COMMENT '备注',
|
|
status TINYINT DEFAULT 1 COMMENT '1-在读, 0-停课/离校',
|
|
source VARCHAR(50) DEFAULT '新学员' COMMENT '来源: 新学员/老带新/团报',
|
|
referrer_id INT COMMENT '推荐人学员ID(老带新)',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学员表';
|
|
|
|
-- 5. 课程表
|
|
CREATE TABLE IF NOT EXISTS courses (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL COMMENT '课程名称',
|
|
level VARCHAR(50) COMMENT '课程阶段/等级',
|
|
description TEXT COMMENT '课程描述',
|
|
price_per_hour DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '每课时单价',
|
|
total_hours INT DEFAULT 0 COMMENT '总课时数',
|
|
material_fee DECIMAL(10,2) DEFAULT 0 COMMENT '材料费',
|
|
type VARCHAR(50) DEFAULT '一对一' COMMENT '课程类型: 一对一/小班/大班',
|
|
remark TEXT COMMENT '备注',
|
|
status TINYINT DEFAULT 1 COMMENT '1-启用, 0-停用',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';
|
|
|
|
-- 6. 班级表
|
|
CREATE TABLE IF NOT EXISTS classes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL COMMENT '班级名称',
|
|
course_id INT NOT NULL COMMENT '课程ID',
|
|
teacher_id INT NOT NULL COMMENT '授课老师ID',
|
|
start_date DATE COMMENT '开班日期',
|
|
end_date DATE COMMENT '结课日期',
|
|
schedule VARCHAR(200) COMMENT '上课时间安排',
|
|
max_students INT DEFAULT 20 COMMENT '最大学员数',
|
|
current_students INT DEFAULT 0 COMMENT '当前学员数',
|
|
status TINYINT DEFAULT 1 COMMENT '1-进行中, 2-已结束, 0-未开始',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (course_id) REFERENCES courses(id),
|
|
FOREIGN KEY (teacher_id) REFERENCES teachers(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级表';
|
|
|
|
-- 7. 班级学员关联表
|
|
CREATE TABLE IF NOT EXISTS class_students (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
class_id INT NOT NULL COMMENT '班级ID',
|
|
student_id INT NOT NULL COMMENT '学员ID',
|
|
join_date DATE NOT NULL COMMENT '入班日期',
|
|
status TINYINT DEFAULT 1 COMMENT '1-在读, 2-已转出, 3-已毕业',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (class_id) REFERENCES classes(id),
|
|
FOREIGN KEY (student_id) REFERENCES students(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级学员关联表';
|
|
|
|
-- 8. 学员课时账户表
|
|
CREATE TABLE IF NOT EXISTS student_accounts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
student_id INT NOT NULL COMMENT '学员ID',
|
|
course_id INT NOT NULL COMMENT '课程ID',
|
|
normal_hours DECIMAL(10,2) DEFAULT 0 COMMENT '正课剩余课时',
|
|
gifted_hours DECIMAL(10,2) DEFAULT 0 COMMENT '赠课剩余课时',
|
|
consumed_normal DECIMAL(10,2) DEFAULT 0 COMMENT '已消正课课时',
|
|
consumed_gifted DECIMAL(10,2) DEFAULT 0 COMMENT '已消赠课课时',
|
|
normal_validity VARCHAR(20) DEFAULT 'permanent' COMMENT '正课有效期: permanent/permanent',
|
|
normal_expiry_date DATE COMMENT '正课到期日期(NULL=永久)',
|
|
gifted_validity VARCHAR(20) DEFAULT 'permanent' COMMENT '赠课有效期: permanent/permanent',
|
|
gifted_expiry_date DATE COMMENT '赠课到期日期(NULL=永久)',
|
|
is_stopped TINYINT DEFAULT 0 COMMENT '是否停课保号: 0-否, 1-是',
|
|
stop_end_date DATE COMMENT '停课结束日期',
|
|
cumulative_amount DECIMAL(10,2) DEFAULT 0 COMMENT '累计充值金额(用于优惠触发)',
|
|
cumulative_start_date DATE COMMENT '累计周期起点日期',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (student_id) REFERENCES students(id),
|
|
FOREIGN KEY (course_id) REFERENCES courses(id),
|
|
UNIQUE KEY uk_student_course (student_id, course_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学员课时账户表';
|
|
|
|
-- 9. 充值优惠活动表
|
|
CREATE TABLE IF NOT EXISTS recharge_activities (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL COMMENT '活动名称',
|
|
trigger_amount DECIMAL(10,2) NOT NULL COMMENT '优惠触发金额',
|
|
gifted_hours DECIMAL(10,2) DEFAULT 0 COMMENT '基础赠送课时',
|
|
gift_type VARCHAR(50) DEFAULT '课时' COMMENT '优惠赠送类型',
|
|
is_cumulative TINYINT DEFAULT 0 COMMENT '是否支持累计充值: 0-否, 1-是',
|
|
cumulative_window INT DEFAULT 0 COMMENT '累计时间窗口(天),0=不限制',
|
|
cumulative_clear TINYINT DEFAULT 0 COMMENT '超出时间窗口是否清零: 0-不清零, 1-清零',
|
|
allow_multi_trigger TINYINT DEFAULT 0 COMMENT '是否允许多次触发: 0-否, 1-是',
|
|
is_time_limited TINYINT DEFAULT 0 COMMENT '是否限制活动有效期: 0-否, 1-是',
|
|
start_date DATE COMMENT '活动开始日期',
|
|
end_date DATE COMMENT '活动结束日期',
|
|
target_audience VARCHAR(50) DEFAULT 'all' COMMENT '参与人群: all/new/old',
|
|
can_stack TINYINT DEFAULT 1 COMMENT '是否可与其他优惠叠加: 0-否, 1-是',
|
|
gift_cap DECIMAL(10,2) DEFAULT 0 COMMENT '赠送权益封顶,0=不限制',
|
|
group_discount TINYINT DEFAULT 0 COMMENT '是否有团报优惠: 0-否, 1-是',
|
|
group_min_people INT DEFAULT 0 COMMENT '团报最低人数',
|
|
group_extra_hours DECIMAL(10,2) DEFAULT 0 COMMENT '团报额外赠课',
|
|
referral_reward TINYINT DEFAULT 0 COMMENT '是否有老带新奖励: 0-否, 1-是',
|
|
referral_reward_hours DECIMAL(10,2) DEFAULT 0 COMMENT '老带新奖励课时',
|
|
reward_timing VARCHAR(50) DEFAULT 'immediate' COMMENT '奖励到账时效: immediate/manual',
|
|
status TINYINT DEFAULT 1 COMMENT '1-启用, 0-停用',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值优惠活动表';
|
|
|
|
-- 10. 充值记录表
|
|
CREATE TABLE IF NOT EXISTS recharge_records (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
student_id INT NOT NULL COMMENT '学员ID',
|
|
course_id INT NOT NULL COMMENT '课程ID',
|
|
activity_id INT COMMENT '关联优惠活动ID',
|
|
amount DECIMAL(10,2) NOT NULL COMMENT '充值金额',
|
|
normal_hours DECIMAL(10,2) NOT NULL COMMENT '充值正课课时',
|
|
gifted_hours DECIMAL(10,2) DEFAULT 0 COMMENT '赠送课时',
|
|
payment_method VARCHAR(50) COMMENT '支付方式',
|
|
operator_id INT NOT NULL COMMENT '操作人ID',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (student_id) REFERENCES students(id),
|
|
FOREIGN KEY (course_id) REFERENCES courses(id),
|
|
FOREIGN KEY (activity_id) REFERENCES recharge_activities(id),
|
|
FOREIGN KEY (operator_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值记录表';
|
|
|
|
-- 11. 消课记录表
|
|
CREATE TABLE IF NOT EXISTS consumption_records (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
student_id INT NOT NULL COMMENT '学员ID',
|
|
class_id INT COMMENT '班级ID',
|
|
course_id INT NOT NULL COMMENT '课程ID',
|
|
hours_consumed DECIMAL(10,2) NOT NULL COMMENT '消耗课时总数',
|
|
consume_type VARCHAR(20) NOT NULL COMMENT '消耗类型: normal/gifted/mixed',
|
|
normal_consumed DECIMAL(10,2) DEFAULT 0 COMMENT '消耗正课课时',
|
|
gifted_consumed DECIMAL(10,2) DEFAULT 0 COMMENT '消耗赠课课时',
|
|
operator_id INT NOT NULL COMMENT '操作人ID',
|
|
consume_date DATE NOT NULL COMMENT '上课日期',
|
|
is_makeup TINYINT DEFAULT 0 COMMENT '是否补录: 0-否, 1-是',
|
|
is_trial TINYINT DEFAULT 0 COMMENT '是否试听(不扣课): 0-否, 1-是',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (student_id) REFERENCES students(id),
|
|
FOREIGN KEY (class_id) REFERENCES classes(id),
|
|
FOREIGN KEY (course_id) REFERENCES courses(id),
|
|
FOREIGN KEY (operator_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消课记录表';
|
|
|
|
-- 12. 退费记录表
|
|
CREATE TABLE IF NOT EXISTS refund_records (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
student_id INT NOT NULL COMMENT '学员ID',
|
|
course_id INT NOT NULL COMMENT '课程ID',
|
|
refund_amount DECIMAL(10,2) NOT NULL COMMENT '退费金额',
|
|
remaining_normal_hours DECIMAL(10,2) COMMENT '剩余正课课时',
|
|
remaining_gifted_hours DECIMAL(10,2) COMMENT '剩余赠课课时',
|
|
deduct_gifted_hours DECIMAL(10,2) DEFAULT 0 COMMENT '扣回赠课课时',
|
|
consumed_hours_value DECIMAL(10,2) DEFAULT 0 COMMENT '已消课时按原价核算金额',
|
|
material_fee_per_quarter DECIMAL(10,2) DEFAULT 250 COMMENT '每季度材料费',
|
|
quarters INT DEFAULT 0 COMMENT '学习季度数',
|
|
total_deduct DECIMAL(10,2) DEFAULT 0 COMMENT '总扣除金额',
|
|
reason TEXT COMMENT '退费原因',
|
|
operator_id INT NOT NULL COMMENT '操作人ID',
|
|
status TINYINT DEFAULT 1 COMMENT '1-已退, 0-待审核',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (student_id) REFERENCES students(id),
|
|
FOREIGN KEY (course_id) REFERENCES courses(id),
|
|
FOREIGN KEY (operator_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='退费记录表';
|
|
|
|
-- 13. 转课记录表
|
|
CREATE TABLE IF NOT EXISTS transfer_records (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
from_student_id INT NOT NULL COMMENT '转出学员ID',
|
|
to_student_id INT COMMENT '转入学员ID(转赠时)',
|
|
from_course_id INT NOT NULL COMMENT '转出课程ID',
|
|
to_course_id INT COMMENT '转入课程ID(转课时)',
|
|
transfer_type VARCHAR(20) NOT NULL COMMENT '类型: transfer-转课, gift-转赠',
|
|
transfer_hours DECIMAL(10,2) NOT NULL COMMENT '转出课时数',
|
|
transfer_amount DECIMAL(10,2) COMMENT '转出金额(按剩余金额换算)',
|
|
operator_id INT NOT NULL COMMENT '操作人ID',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (from_student_id) REFERENCES students(id),
|
|
FOREIGN KEY (to_student_id) REFERENCES students(id),
|
|
FOREIGN KEY (from_course_id) REFERENCES courses(id),
|
|
FOREIGN KEY (to_course_id) REFERENCES courses(id),
|
|
FOREIGN KEY (operator_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='转课记录表';
|
|
|
|
-- 14. 停课记录表
|
|
CREATE TABLE IF NOT EXISTS stop_records (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
student_id INT NOT NULL COMMENT '学员ID',
|
|
course_id INT NOT NULL COMMENT '课程ID',
|
|
start_date DATE NOT NULL COMMENT '停课开始日期',
|
|
end_date DATE NOT NULL COMMENT '停课结束日期',
|
|
reason TEXT COMMENT '停课原因',
|
|
operator_id INT NOT NULL COMMENT '操作人ID',
|
|
status TINYINT DEFAULT 1 COMMENT '1-停课中, 2-已复课, 0-已取消',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (student_id) REFERENCES students(id),
|
|
FOREIGN KEY (course_id) REFERENCES courses(id),
|
|
FOREIGN KEY (operator_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='停课记录表';
|
|
|
|
-- 15. 考勤记录表
|
|
CREATE TABLE IF NOT EXISTS attendance_records (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
student_id INT NOT NULL COMMENT '学员ID',
|
|
class_id INT NOT NULL COMMENT '班级ID',
|
|
date DATE NOT NULL COMMENT '考勤日期',
|
|
status VARCHAR(20) NOT NULL COMMENT '出勤状态: present/absent/leave',
|
|
remark TEXT COMMENT '备注',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (student_id) REFERENCES students(id),
|
|
FOREIGN KEY (class_id) REFERENCES classes(id),
|
|
UNIQUE KEY uk_attendance (student_id, class_id, date)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤记录表';
|
|
|
|
-- 16. 操作日志表
|
|
CREATE TABLE IF NOT EXISTS operation_logs (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL COMMENT '操作用户ID',
|
|
operation_type VARCHAR(50) NOT NULL COMMENT '操作类型',
|
|
operation_detail TEXT COMMENT '操作详情',
|
|
ip_address VARCHAR(50) COMMENT 'IP地址',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';
|
|
|
|
-- 17. 课程安排表
|
|
CREATE TABLE IF NOT EXISTS schedules (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
class_id INT NOT NULL COMMENT '班级ID',
|
|
date DATE NOT NULL COMMENT '上课日期',
|
|
start_time TIME NOT NULL COMMENT '开始时间',
|
|
end_time TIME NOT NULL COMMENT '结束时间',
|
|
teacher_id INT NOT NULL COMMENT '授课老师ID',
|
|
topic VARCHAR(200) COMMENT '课程主题',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (class_id) REFERENCES classes(id),
|
|
FOREIGN KEY (teacher_id) REFERENCES teachers(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程安排表';
|
|
|
|
-- ============================================================
|
|
-- 初始化数据
|
|
-- ============================================================
|
|
|
|
-- 默认角色
|
|
INSERT INTO roles (name, description, permissions) VALUES
|
|
('超级管理员', '拥有所有权限', 'all'),
|
|
('校区管理员', '管理本校区的所有业务', 'student,course,class,recharge,consumption,refund,transfer,statistics'),
|
|
('财务', '管理充值、退费等财务相关业务', 'recharge,refund,statistics'),
|
|
('授课老师', '管理消课、考勤等教学相关业务', 'consumption,attendance,class');
|
|
|
|
-- 默认管理员账号 (密码: admin123)
|
|
-- 注意: 此哈希值由 werkzeug.security.generate_password_hash('admin123') 生成
|
|
-- 如果登录失败，请删除 users 表中 admin 记录后重启应用，让程序自动重建
|
|
INSERT INTO users (username, password, real_name, role_id, phone, status) VALUES
|
|
('admin', 'scrypt:32768:8:1$Mlf6DtqLw5isLOHE$8e59ad3d57ed6379aba2304122a139c15aa06e086631bdd886ee8fe83db640ed41adb7f847be1574f86e9676939cc38826100f6c22058f552166be8cd59976f0', '系统管理员', 1, '13800000000', 1);
|
|
|
|
-- 默认课程
|
|
INSERT INTO courses (name, level, description, price_per_hour, total_hours, material_fee, type, status) VALUES
|
|
('美术基础班', '初级', '美术基础课程，适合零基础学员', 100.00, 40, 250.00, '小班', 1),
|
|
('美术进阶班', '中级', '美术进阶课程，适合有基础学员', 120.00, 40, 250.00, '小班', 1),
|
|
('美术高级班', '高级', '美术高级课程，适合进阶提升', 150.00, 40, 250.00, '一对一', 1);
|