MySQL数据库基础练习系列8、成绩录入与分析系统

-- 创建学生信息表
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    class VARCHAR(50) NOT NULL COMMENT '班级',
    registration_date DATE COMMENT '注册日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';


-- 创建科目表
CREATE TABLE subjects (
    subject_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '科目ID',
    subject_name VARCHAR(100) NOT NULL UNIQUE COMMENT '科目名称',
    teacher_name VARCHAR(100) COMMENT '教师姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='科目信息表';


-- 创建成绩表
CREATE TABLE scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩ID',
    student_id INT NOT NULL COMMENT '学生ID',
    subject_id INT NOT NULL COMMENT '科目ID',
    score DECIMAL(5, 2) NOT NULL COMMENT '成绩',
    exam_date DATE COMMENT '考试日期',
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩表';


-- 创建成绩分析表（可选，用于存储分析结果）
CREATE TABLE score_analysis (
    analysis_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '分析ID',
    subject_id INT NOT NULL COMMENT '科目ID',
    avg_score DECIMAL(5, 2) NOT NULL COMMENT '平均分',
    max_score DECIMAL(5, 2) NOT NULL COMMENT '最高分',
    min_score DECIMAL(5, 2) NOT NULL COMMENT '最低分',
    analysis_date DATE COMMENT '分析日期',
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩分析表';


-- 创建系统配置表（可选，用于存储系统相关配置信息）
CREATE TABLE system_config (
    config_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '配置ID',
    config_key VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键',
    config_value VARCHAR(255) COMMENT '配置值',
    description TEXT COMMENT '配置描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

INSERT INTO students (name, gender, class, registration_date) VALUES
('张三', '男', '一班', '2023-09-01'),
('李四', '女', '二班', '2023-09-02'),
('王五', '男', '三班', '2023-09-03'),
('赵六', '女', '四班', '2023-09-04'),
('孙七', '男', '五班', '2023-09-05');
INSERT INTO subjects (subject_name, teacher_name) VALUES
('数学', '张老师'),
('英语', '李老师'),
('物理', '王老师'),
('化学', '赵老师'),
('生物', '孙老师');
INSERT INTO scores (student_id, subject_id, score, exam_date) VALUES
(1, 1, 85.50, '2023-10-15'), -- 张三的数学成绩
(1, 2, 90.25, '2023-10-15'), -- 张三的英语成绩
(2, 1, 78.75, '2023-10-15'), -- 李四的数学成绩
(2, 2, 88.00, '2023-10-15'), -- 李四的英语成绩
(5, 5, 92.00, '2023-10-15'); -- 孙七的生物成绩
INSERT INTO score_analysis (subject_id, avg_score, max_score, min_score, analysis_date) VALUES
(1, 82.38, 90.50, 75.00, '2023-10-17'), -- 数学成绩分析
(2, 86.75, 92.00, 80.00, '2023-10-17'), -- 英语成绩分析
(5, 89.25, 95.00, 85.00, '2023-10-17'); -- 生物成绩分析
INSERT INTO system_config (config_key, config_value, description) VALUES
('school_name', '阳光中学', '学校名称'),
('admin_email', 'admin@example.com', '管理员电子邮箱'),
('exam_schedule_url', 'https://example.com/exam-schedule', '考试安排网址');