-- =============================================
-- Week 3：学生管理系统数据库初始化脚本
-- 使用方式：
--   1. 打开 MySQL 客户端（命令行或 Navicat/DataGrip）
--   2. 复制本文件全部内容并执行
--   3. 或：mysql -u root -p < init.sql
-- =============================================

-- 创建数据库
CREATE DATABASE IF NOT EXISTS week3_student
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE week3_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',
    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) VALUES
('张三', 20, 'zhangsan@mail.com', 85),
('李四', 22, 'lisi@mail.com',    92),
('王五', 19, 'wangwu@mail.com',  78),
('赵六', 21, 'zhaoliu@mail.com', 88),
('孙七', 23, 'sunqi@mail.com',   95),
('周八', 20, 'zhouba@mail.com',  73),
('吴九', 22, 'wujiu@mail.com',   81),
('郑十', 21, 'zhengshi@mail.com',90),
('钱十一', 19, 'qianshiyi@mail.com', 67),
('陈十二', 23, 'chenshier@mail.com', 88);

-- 验证数据
SELECT COUNT(*) AS total_students FROM student;

SELECT * FROM student ORDER BY id;