-- =============================================
-- Week 4：学生管理系统 v1 数据库初始化
-- =============================================

CREATE DATABASE IF NOT EXISTS week4_student
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE week4_student;

DROP TABLE IF EXISTS student;

CREATE TABLE student (
    id          BIGINT       NOT NULL AUTO_INCREMENT  COMMENT '主键ID',
    name        VARCHAR(20)  NOT NULL                 COMMENT '姓名',
    age         INT          NOT NULL                 COMMENT '年龄',
    email       VARCHAR(50)  NOT NULL                 COMMENT '邮箱',
    score       INT          NOT NULL DEFAULT 0       COMMENT '成绩 0-100',
    avatar      VARCHAR(200)                          COMMENT '头像文件名',
    create_time DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    INDEX idx_name (name),
    INDEX idx_score (score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

INSERT INTO student (name, age, email, score, avatar) VALUES
('张三', 20, 'zhangsan@mail.com', 85, NULL),
('李四', 22, 'lisi@mail.com',    92, NULL),
('王五', 19, 'wangwu@mail.com',  78, NULL),
('赵六', 21, 'zhaoliu@mail.com', 88, NULL),
('孙七', 23, 'sunqi@mail.com',   95, NULL),
('周八', 20, 'zhouba@mail.com',  73, NULL),
('吴九', 22, 'wujiu@mail.com',   81, NULL),
('郑十', 21, 'zhengshi@mail.com',90, NULL);

SELECT COUNT(*) AS total_students FROM student;