-- 基本语法
CREATE DATABASE 数据库名;

-- 指定字符集和排序规则
CREATE DATABASE mydb
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS mydb;

-- 列出所有数据库
SHOW DATABASES;

-- 查看数据库创建语句
SHOW CREATE DATABASE mydb;

USE mydb;

-- 删除数据库（谨慎操作！）
DROP DATABASE mydb;

-- 如果存在则删除
DROP DATABASE IF EXISTS mydb;

-- 修改数据库字符集和排序规则
ALTER DATABASE mydb
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

-- 基本语法
CREATE TABLE 表名 (
    字段1  数据类型  [约束]  [COMMENT '注释'],
    字段2  数据类型  [约束]  [COMMENT '注释'],
    ...
    [表级约束]
) ENGINE=存储引擎 DEFAULT CHARSET=utf8mb4 COMMENT='表注释';

-- 创建用户表
CREATE TABLE users (
    id          INT AUTO_INCREMENT COMMENT '用户ID',
    username    VARCHAR(50) NOT NULL COMMENT '用户名',
    email       VARCHAR(100) NOT NULL COMMENT '邮箱',
    password    VARCHAR(255) NOT NULL COMMENT '密码',
    phone       VARCHAR(20) DEFAULT NULL COMMENT '手机号',
    age         TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    gender      ENUM('M', 'F', 'Unknown') DEFAULT 'Unknown' COMMENT '性别',
    status      TINYINT DEFAULT 1 COMMENT '状态：0禁用 1启用',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    KEY idx_status (status),
    KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 复制表结构（不复制数据）
CREATE TABLE users_copy LIKE users;

-- 复制表结构和数据
CREATE TABLE users_backup AS SELECT * FROM users;

-- 复制部分数据和字段
CREATE TABLE young_users AS 
SELECT id, username, email, age 
FROM users WHERE age < 30;

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC users;
DESCRIBE users;

-- 查看建表语句
SHOW CREATE TABLE users;

-- 查看表状态
SHOW TABLE STATUS FROM mydb;

-- 添加字段
ALTER TABLE users ADD COLUMN avatar VARCHAR(255) DEFAULT NULL COMMENT '头像URL';
ALTER TABLE users ADD COLUMN (nickname VARCHAR(50), bio TEXT);

-- 添加字段到指定位置
ALTER TABLE users ADD COLUMN introduction TEXT AFTER bio;
ALTER TABLE users ADD COLUMN id_card VARCHAR(18) FIRST;

-- 修改字段
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);  -- 只修改类型
ALTER TABLE users CHANGE COLUMN phone phone_no VARCHAR(30);  -- 修改名称和类型

-- 删除字段
ALTER TABLE users DROP COLUMN avatar;
ALTER TABLE users DROP COLUMN nickname, DROP COLUMN bio;

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE KEY uk_phone (phone);

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_age (age);

-- 添加外键
ALTER TABLE orders ADD CONSTRAINT fk_user_id 
    FOREIGN KEY (user_id) REFERENCES users(id) 
    ON DELETE CASCADE ON UPDATE CASCADE;

-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_user_id;

-- 修改表名
ALTER TABLE users RENAME TO user_info;
RENAME TABLE users TO user_info, orders TO order_info;

-- 删除表（谨慎操作！）
DROP TABLE users;

-- 如果存在则删除
DROP TABLE IF EXISTS users;

-- 删除多个表
DROP TABLE users, orders, products;

-- 创建表时添加索引
CREATE TABLE products (
    id          INT PRIMARY KEY,
    name        VARCHAR(100),
    price       DECIMAL(10,2),
    description TEXT,
    FULLTEXT KEY ft_name_desc (name, description)  -- 全文索引
);

-- 单独创建索引
CREATE INDEX idx_name ON products(name);
CREATE UNIQUE INDEX uk_name ON products(name);
CREATE FULLTEXT INDEX ft_desc ON products(description);

-- 组合索引
CREATE INDEX idx_price_name ON products(price, name);

SHOW INDEX FROM products;

-- 或使用 SHOW CREATE TABLE
SHOW CREATE TABLE products\G

DROP INDEX idx_name ON products;
DROP INDEX uk_name ON products;

-- 删除主键索引（需要先删除自增）
ALTER TABLE products MODIFY id INT;
ALTER TABLE products DROP PRIMARY KEY;

CREATE TABLE orders (
    order_id    INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    total_amount DECIMAL(10,2),
    created_at  DATETIME,
    
    CONSTRAINT fk_orders_users
        FOREIGN KEY (user_id) 
        REFERENCES users(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

ALTER TABLE orders DROP FOREIGN KEY fk_orders_users;

-- 指定存储引擎
CREATE TABLE innodb_table (...) ENGINE=InnoDB;
CREATE TABLE myisam_table (...) ENGINE=MyISAM;

-- 修改存储引擎
ALTER TABLE myisam_table ENGINE=InnoDB;

-- RANGE 分区
CREATE TABLE sales (
    id          INT,
    sale_date   DATE,
    amount      DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- LIST 分区
CREATE TABLE employees (
    id          INT,
    dept_id     INT,
    name        VARCHAR(50)
)
PARTITION BY LIST (dept_id) (
    PARTITION p_sales VALUES IN (1, 2, 3),
    PARTITION p_tech VALUES IN (4, 5, 6),
    PARTITION p_hr VALUES IN (7, 8)
);

-- HASH 分区
CREATE TABLE orders (
    id          INT,
    amount      DECIMAL(10,2)
)
PARTITION BY HASH(id)
PARTITIONS 4;

-- KEY 分区
CREATE TABLE user_sessions (
    id          INT,
    user_id     INT,
    login_time  DATETIME
)
PARTITION BY KEY(user_id)
PARTITIONS 8;

-- 查看分区
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sales';

-- 重新组织分区
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- 删除分区
ALTER TABLE sales DROP PARTITION p2020;

✅ 掌握数据库的创建、查看、删除操作
✅ 熟练使用各种表约束：主键、唯一、非空、外键
✅ 理解并应用各类索引提升查询性能
✅ 了解 InnoDB 和 MyISAM 存储引擎的特点
✅ 学会使用分区表优化大数据表