-- 学生表
CREATE TABLE student (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
email VARCHAR(128) NOT NULL UNIQUE,
city VARCHAR(32) NOT NULL,
vip TINYINT(1) NOT NULL DEFAULT 0,
balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- 课程表
CREATE TABLE course (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
slug VARCHAR(64) NOT NULL UNIQUE,
title VARCHAR(128) NOT NULL,
category VARCHAR(32) NOT NULL, -- java / frontend / database
level VARCHAR(16) NOT NULL DEFAULT 'beginner',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
status VARCHAR(16) NOT NULL DEFAULT 'draft', -- draft / published
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- 课时表（一门课有多节课）
CREATE TABLE lesson (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
course_id BIGINT NOT NULL,
title VARCHAR(128) NOT NULL,
order_index INT NOT NULL,
duration_min INT NOT NULL DEFAULT 20,
status VARCHAR(16) NOT NULL DEFAULT 'draft',
CONSTRAINT fk_lesson_course FOREIGN KEY (course_id)
REFERENCES course(id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- 学习记录表（谁在学哪节课、完成没有）
CREATE TABLE study_record (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
student_id BIGINT NOT NULL,
lesson_id BIGINT NOT NULL,
completed TINYINT(1) NOT NULL DEFAULT 0,
score INT DEFAULT NULL,
completed_at DATETIME DEFAULT NULL,
UNIQUE KEY uk_student_lesson (student_id, lesson_id),
CONSTRAINT fk_record_student FOREIGN KEY (student_id)
REFERENCES student(id) ON DELETE CASCADE,
CONSTRAINT fk_record_lesson FOREIGN KEY (lesson_id)
REFERENCES lesson(id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- 订单表（ORDER 是 SQL 保留字，所以表名叫 pay_order）
CREATE TABLE pay_order (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(16) NOT NULL DEFAULT 'PAID', -- PAID / REFUNDED
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_order_student FOREIGN KEY (student_id) REFERENCES student(id),
CONSTRAINT fk_order_course FOREIGN KEY (course_id) REFERENCES course(id)
) ENGINE=InnoDB;
-- 造数：7 个学生
INSERT INTO student (id, name, email, city, vip, balance, created_at) VALUES
(1,'老陈','chen@example.com','深圳',1,128.00,'2026-07-01 09:00:00'),
(2,'小林','lin@example.com','杭州',0, 39.00,'2026-07-03 10:00:00'),
(3,'阿珍','zhen@example.com','广州',1,256.50,'2026-07-05 11:00:00'),
(4,'大鹏','peng@example.com','北京',0, 0.00,'2026-07-08 14:00:00'),
(5,'小夏','xia@example.com','深圳',0, 88.00,'2026-07-10 16:00:00'),
(6,'老周','zhou@example.com','上海',1,500.00,'2026-07-12 18:00:00'),
(7,'小吴','wu@example.com','成都',0, 20.00,'2026-07-15 20:00:00');
-- 造数：7 门课程
INSERT INTO course (id, slug, title, category, level, price, status, created_at) VALUES
(1,'java-fundamentals','Java 基础入门','java','beginner',199.00,'published','2026-06-01 10:00:00'),
(2,'java-oop','Java 面向对象进阶','java','intermediate',299.00,'published','2026-06-03 10:00:00'),
(3,'sql-essentials','SQL 与关系型数据库','database','beginner',0.00,'published','2026-06-05 10:00:00'),
(4,'mysql-advanced','MySQL 性能优化实战','database','advanced',399.00,'published','2026-06-08 10:00:00'),
(5,'html-css','前端页面基础','frontend','beginner',99.00,'published','2026-06-10 10:00:00'),
(6,'redis-intro','Redis 缓存入门','database','intermediate',0.00,'draft','2026-06-12 10:00:00'),
(7,'git-essentials','Git 团队协作基础','frontend','beginner',49.00,'draft','2026-06-15 10:00:00');
-- 造数：14 节课时
INSERT INTO lesson (id, course_id, title, order_index, duration_min, status) VALUES
(1,1,'环境搭建与第一个程序',1,25,'published'),
(2,1,'变量与数据类型',2,30,'published'),
(3,1,'流程控制',3,35,'draft'),
(4,2,'类与对象',1,40,'published'),
(5,2,'继承与多态',2,45,'published'),
(6,3,'关系型数据库与 SQL 是什么',1,25,'published'),
(7,3,'SELECT 基本查询',2,30,'published'),
(8,3,'WHERE 过滤与排序',3,30,'published'),
(9,3,'JOIN 多表联结',4,35,'draft'),
(10,4,'索引原理与 B+ 树',1,50,'published'),
(11,4,'执行计划 EXPLAIN 详解',2,55,'published'),
(12,5,'HTML 文档结构',1,20,'published'),
(13,5,'CSS 盒模型',2,28,'published'),
(14,6,'Redis 是什么',1,15,'draft');
-- 造数：17 条学习记录
INSERT INTO study_record (id, student_id, lesson_id, completed, score, completed_at) VALUES
(1,1,6,1,92,'2026-08-10 10:00:00'),
(2,1,7,1,88,'2026-08-11 09:30:00'),
(3,1,1,1,95,'2026-08-05 14:00:00'),
(4,1,2,1,90,'2026-08-06 15:20:00'),
(5,2,6,1,75,'2026-08-15 20:10:00'),
(6,2,12,1,80,'2026-08-16 21:00:00'),
(7,3,6,1,98,'2026-08-01 08:40:00'),
(8,3,7,1,95,'2026-08-02 09:00:00'),
(9,3,8,1,91,'2026-08-03 10:00:00'),
(10,3,10,1,85,'2026-08-09 11:00:00'),
(11,4,1,1,60,'2026-08-20 19:00:00'),
(12,4,6,0,NULL,NULL),
(13,5,7,1,83,'2026-08-25 16:30:00'),
(14,6,6,1,90,'2026-08-12 12:00:00'),
(15,6,10,1,88,'2026-08-13 13:00:00'),
(16,6,11,1,92,'2026-08-14 14:00:00'),
(17,6,4,1,85,'2026-08-18 15:00:00');
-- 造数：6 笔订单
INSERT INTO pay_order (id, student_id, course_id, amount, status, created_at) VALUES
(1,3,1,199.00,'PAID','2026-08-01 09:00:00'),
(2,1,4,399.00,'PAID','2026-08-02 10:00:00'),
(3,3,4,399.00,'PAID','2026-08-03 11:00:00'),
(4,6,5, 99.00,'PAID','2026-08-05 12:00:00'),
(5,2,1,199.00,'REFUNDED','2026-08-06 13:00:00'),
(6,5,5, 99.00,'PAID','2026-08-08 14:00:00');