关系模型

-- 推荐：字段设置为 NOT NULL
CREATE TABLE students (
    id BIGINT NOT NULL,
    name VARCHAR(50) NOT NULL,
    score INT NOT NULL DEFAULT 0
);

-- 不推荐：允许 NULL 会增加查询复杂度
CREATE TABLE students (
    id BIGINT,
    name VARCHAR(50),
    score INT
);

-- 方案一：合并到一张表（适合简单场景）
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    teacher_name VARCHAR(50),
    teacher_age INT
);

-- 方案二：拆分为两张表（适合复杂场景）
-- 优点：把经常读取和不经常读取的字段分开，提高查询性能
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    teacher_id VARCHAR(10)
);

CREATE TABLE teachers (
    id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    phone VARCHAR(20),
    email VARCHAR(100)
);

CREATE TABLE students (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE students (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- Java 生成 GUID
// UUID.randomUUID().toString()
// 示例：8f55d96b-8acc-4636-8cb8-76bf8abc2f57

CREATE TABLE user_credentials (
    id_num INT NOT NULL,
    id_type VARCHAR(10) NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id_num, id_type)
);

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

-- 插入成功：classes 表存在 id=1 的记录
INSERT INTO students (id, class_id, name) VALUES (1, 1, '小明');

-- 插入失败：classes 表不存在 id=99 的记录
INSERT INTO students (id, class_id, name) VALUES (2, 99, '小红');
-- ERROR: Cannot add or update a child row: a foreign key constraint fails

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

-- 方案一：使用外键约束（数据一致性由数据库保证）
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id) REFERENCES classes (id);

-- 方案二：不使用外键约束（数据一致性由应用程序保证）
-- class_id 只是普通列，需要应用层确保数据有效性

-- 创建中间表
CREATE TABLE teacher_class (
    id INT PRIMARY KEY AUTO_INCREMENT,
    teacher_id INT NOT NULL,
    class_id INT NOT NULL,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id),
    FOREIGN KEY (class_id) REFERENCES classes(id),
    UNIQUE KEY uk_teacher_class (teacher_id, class_id)
);

-- 查询某老师教授的所有班级
SELECT c.*
FROM classes c
JOIN teacher_class tc ON c.id = tc.class_id
WHERE tc.teacher_id = 1;

-- 查询某班级的所有老师
SELECT t.*
FROM teachers t
JOIN teacher_class tc ON t.id = tc.teacher_id
WHERE tc.class_id = 1;

-- 创建单列索引
ALTER TABLE students
ADD INDEX idx_score (score);

-- 创建多列索引（联合索引）
ALTER TABLE students
ADD INDEX idx_name_score (name, score);

-- 选择性 = 不同值的数量 / 总记录数
-- 选择性越接近 1，索引效率越高

SELECT 
    COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity,
    COUNT(DISTINCT name) / COUNT(*) AS name_selectivity,
    COUNT(DISTINCT id) / COUNT(*) AS id_selectivity
FROM students;

-- 主键自动创建索引，无需手动创建
CREATE TABLE students (
    id BIGINT PRIMARY KEY,  -- 自动创建主键索引
    name VARCHAR(50)
);

-- 方式一：创建唯一索引
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

-- 方式二：添加唯一约束（不创建索引）
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

-- 适合创建索引的场景
-- 1. WHERE 条件中频繁使用的列
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- 2. JOIN 关联的列
ALTER TABLE order_items ADD INDEX idx_order_id (order_id);

-- 3. ORDER BY 排序的列
ALTER TABLE products ADD INDEX idx_price (price);

-- 4. 联合索引（遵循最左前缀原则）
ALTER TABLE users ADD INDEX idx_name_age (name, age);
-- 可以使用：WHERE name = '张三'
-- 可以使用：WHERE name = '张三' AND age = 20
-- 不能使用：WHERE age = 20

-- 班级表
CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL COMMENT '班级名称',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) COMMENT '班级信息表';

-- 教师表
CREATE TABLE teachers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL COMMENT '教师姓名',
    age INT COMMENT '年龄',
    phone VARCHAR(20) COMMENT '联系电话'
) COMMENT '教师信息表';

-- 学生表
CREATE TABLE students (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
    class_id INT NOT NULL COMMENT '班级ID',
    gender CHAR(1) DEFAULT 'M' COMMENT '性别：M-男，F-女',
    score INT DEFAULT 0 COMMENT '分数',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_class_id (class_id),
    INDEX idx_score (score)
) COMMENT '学生信息表';

-- 班级-教师关联表（多对多）
CREATE TABLE class_teacher (
    id INT PRIMARY KEY AUTO_INCREMENT,
    class_id INT NOT NULL,
    teacher_id INT NOT NULL,
    UNIQUE KEY uk_class_teacher (class_id, teacher_id)
) COMMENT '班级教师关联表';