DDL语句

---

在 MySQL 中，DDL（Data Definition Language，数据定义语言）是用于定义和管理数据库结构的 SQL 语句。它主要用于创建、修改和删除数据库对象（如表、索引、视图等）。

DDL 语句直接影响数据库的结构，而不操作数据本身。

1. 常用的 DDL 语句

1. CREATE：用于创建数据库、表、索引、视图等数据库对象。
2. ALTER：用于修改现有数据库对象的结构，例如修改表结构、修改列的类型等。
3. DROP：用于删除数据库对象，如删除表、删除索引等。
4. TRUNCATE：用于删除表中的所有数据，但不删除表结构。
5. RENAME：用于重命名数据库对象（如表名、列名）。

2. CREATE

2.1. 创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名
    [CHARACTER SET 字符集]
    [COLLATE 排序规则]
    [COMMENT = '数据库注释'];

注意：COMMENT 是 MySQL 8.0+ 支持的新特性，旧版本不支持。

2.2. 创建表

CREATE TABLE [IF NOT EXISTS] 表名 (
    字段1 数据类型 [约束] [自增] [COMMENT '字段注释'],
    字段2 数据类型 [约束] [默认值] [COMMENT '字段注释'],
    ...

    -- 主键（可以是单字段或多字段组合）
    PRIMARY KEY (字段1),

    -- 唯一约束（可命名）
    UNIQUE KEY 唯一索引名 (字段2),

    -- 外键约束（引用其他表）
    FOREIGN KEY (字段3) REFERENCES 其他表(其他字段)
        ON DELETE [级联删除 | 设为空 | 限制删除]
        ON UPDATE [级联更新 | 设为空 | 限制更新],

    -- 普通索引
    INDEX 普通索引名 (字段3),

    -- 全文索引（MySQL 特有，用于模糊搜索大文本）
    FULLTEXT INDEX 全文索引名 (字段名)

) ENGINE=存储引擎
  DEFAULT CHARSET=字符集
  COLLATE=排序规则
  COMMENT='表的中文注释';

创建一个新的表。每个列都必须指定数据类型，可以选择添加约束条件（如 NOT NULL、PRIMARY KEY 等）。

3. ALTER

ALTER 语句用于修改已有的表结构，比如添加列、修改列类型、删除列等。

3.1. 添加列

ALTER TABLE 表名 ADD COLUMN 新列名 数据类型 [约束] [COMMENT '注释'];

3.2. 修改列

ALTER TABLE 表名 MODIFY [COLUMN] 列名 新数据类型 [约束] [COMMENT '注释'] [FIRST | AFTER 某列];

MODIFY：只改类型/约束，不改列名。

ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 新数据类型 [约束] [COMMENT '注释'] [FIRST | AFTER 某列];

CHANGE：可以改列名 + 类型/约束（即使不改名也要写两次列名）。

3.3. 删除列

ALTER TABLE 表名 DROP COLUMN 列名;

4. DROP

4.1. 删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

IF EXISTS 是可选的，但强烈建议加上 —— 避免数据库不存在时报错，尤其在脚本中执行时。

4.2. 删除表

DROP TABLE [IF EXISTS] 表名;

IF EXISTS 是可选的，但强烈建议加上 —— 避免表不存在时报错，尤其在脚本、部署、自动化流程中非常实用。

可一次删除多个表

DROP TABLE IF EXISTS 表1, 表2, 表3;

4.3. 删除索引

DROP INDEX 索引名 ON 表名;

注意事项

主键索引不能直接用DROP INDEX 删除，需要用：

ALTER TABLE 表名 DROP PRIMARY KEY;

唯一索引、普通索引、全文索引都可以用 DROP INDEX删除，同理也可在 ALTER TABLE 中删除。

ALTER TABLE 表名 DROP INDEX 索引名;

查看表上所有索引：

SHOW INDEX FROM 表名;
-- 或
SHOW CREATE TABLE 表名;

5. TRUNCATE

TRUNCATE 是“毁灭性操作”，它会删除表中所有行，且通常不可回滚（无事务保护），操作前务必备份或确认无影响。相对比 DELETE FROM 表名; 更快、更高效（尤其对大表）。

TRUNCATE TABLE 用户表;
-- 或
TRUNCATE 用户表;

注意事项

1. 会重置自增主键（下次插入从 1 开始）;
2. 不能用于被外键引用的表（除非临时关闭外键检查）;

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE 用户表; SET FOREIGN_KEY_CHECKS = 1;

3. 默认**不可回滚**（即使在事务中）→ InnoDB 早期版本如此，新版本部分支持事务，但不推荐依赖。

6. RENAME

6.1. 重命名表

RENAME TABLE 旧表名 TO 新表名;

支持一次重命名多个表

RENAME TABLE 旧表1 TO 新表1, 旧表2 TO 新表2;

一次操作，重命名多个表，原子性执行（要么全成功，要么全失败）。

跨库重命名

RENAME TABLE test_db.users TO prod_db.users;

把 test_db 库的 users 表移动（重命名）到 prod_db 库。

6.2. 重命名用户

RENAME USER '旧用户名'@'主机' TO '新用户名'@'主机';