MySQL 数据库与表的管理 (DDL) 操作命令大全

MySQL 数据库与表的管理 (DDL) 操作命令大全

说明：DDL（Data Definition Language，数据定义语言）用于定义或修改数据库对象的结构，包括数据库、表、索引、视图等。DDL语句在执行时默认会隐式提交当前事务，执行后无法回滚。

---

一、数据库管理

1.1 创建数据库

语法：

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

示例：

-- 最基本创建
CREATE DATABASE company;

-- 存在则不报错（推荐）
CREATE DATABASE IF NOT EXISTS company;

-- 指定字符集和排序规则
CREATE DATABASE IF NOT EXISTS company
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- 使用默认字符集创建（取决于服务器配置）
CREATE DATABASE test_db;

1.2 修改数据库

可以修改数据库的默认字符集和排序规则。

语法：

ALTER DATABASE 数据库名
    [CHARACTER SET 字符集名]
    [COLLATE 排序规则名];

示例：

-- 修改字符集为 utf8mb4
ALTER DATABASE company CHARACTER SET utf8mb4;

-- 同时修改字符集和排序规则
ALTER DATABASE company
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

1.3 删除数据库

语法：

DROP DATABASE [IF EXISTS] 数据库名;

示例：

-- 删除指定数据库（不存在会报错）
DROP DATABASE temp_db;

-- 推荐：存在则删除，不存在也不报错
DROP DATABASE IF EXISTS old_company;

⚠️ 警告：删除数据库会永久删除该库内的所有表和数据，无法恢复。

1.4 切换/选择数据库

语法：

USE 数据库名;

示例：

USE company;

1.5 查看所有数据库

SHOW DATABASES;

1.6 查看当前使用的数据库

SELECT DATABASE();

---

二、表管理

2.1 创建表

2.1.1 基本创建

语法：

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [约束] [COMMENT '注释'],
    列名2 数据类型 [约束] [COMMENT '注释'],
    ...
    [表级约束]
) [表选项];

表选项：

• ENGINE = 存储引擎（如InnoDB、MyISAM）
• AUTO_INCREMENT = 初始值
• CHARACTER SET = 字符集名
• COLLATE = 排序规则名
• COMMENT = '表注释'
• ROW_FORMAT = 行格式

示例：

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

2.1.2 通过复制现有表结构创建（不复制数据）

语法：

CREATE TABLE 新表名 LIKE 源表名;

示例：

CREATE TABLE users_backup LIKE users;

2.1.3 通过查询结果创建表（复制结构和数据）

语法：

CREATE TABLE 新表名 [AS] SELECT 查询语句;

示例：

-- 复制部分列和数据
CREATE TABLE active_users AS
SELECT id, username, email FROM users WHERE status = 'active';

-- 复制全部列和数据
CREATE TABLE users_copy AS SELECT * FROM users;

⚠️ 注意：使用AS SELECT方式创建的表不会复制原表的索引、自增属性、默认值等约束，只会复制列的数据类型和NULL属性。如果需要完整结构，请使用LIKE方式。

2.2 查看表结构

多种方式：

-- 方法1：DESCRIBE / DESC
DESCRIBE 表名;
DESC 表名;

-- 方法2：SHOW COLUMNS
SHOW COLUMNS FROM 表名;
SHOW FULL COLUMNS FROM 表名;   -- 显示更多信息如注释

-- 方法3：查看建表语句（最完整）
SHOW CREATE TABLE 表名;

示例：

DESC users;
SHOW FULL COLUMNS FROM users;
SHOW CREATE TABLE users\G

2.3 查看当前数据库中的所有表

SHOW TABLES;

2.4 修改表结构（ALTER TABLE）

2.4.1 添加列

语法：

ALTER TABLE 表名 ADD [COLUMN] 列名 数据类型 [约束] [FIRST | AFTER 列名];

示例：

-- 添加列到末尾
ALTER TABLE users ADD phone VARCHAR(20);

-- 添加列到最前面
ALTER TABLE users ADD user_id INT FIRST;

-- 添加到指定列之后
ALTER TABLE users ADD address VARCHAR(255) AFTER email;

-- 添加带有默认值的列
ALTER TABLE users ADD login_count INT DEFAULT 0;

-- 一次性添加多列
ALTER TABLE users 
    ADD city VARCHAR(50),
    ADD country VARCHAR(50) DEFAULT 'China';

2.4.2 修改列数据类型 / 约束（MODIFY）

语法：

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

示例：

-- 修改 age 列的数据类型
ALTER TABLE users MODIFY age SMALLINT UNSIGNED;

-- 修改列定义，同时添加 NOT NULL 约束
ALTER TABLE users MODIFY email VARCHAR(150) NOT NULL;

-- 修改列并改变位置
ALTER TABLE users MODIFY phone VARCHAR(20) AFTER username;

2.4.3 重命名列（CHANGE）

ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 数据类型 [约束];

示例：

-- 重命名列并修改数据类型
ALTER TABLE users CHANGE phone mobile VARCHAR(15);

-- 仅重命名，不改变定义（数据类型必须完整写出）
ALTER TABLE users CHANGE age user_age INT UNSIGNED DEFAULT 0;

2.4.4 删除列

ALTER TABLE 表名 DROP [COLUMN] 列名;

示例：

ALTER TABLE users DROP COLUMN address;
ALTER TABLE users DROP city;   -- COLUMN 关键字可省略

2.4.5 重命名表（使用 ALTER TABLE）

ALTER TABLE 旧表名 RENAME TO 新表名;

此方式可同时跨数据库移动表（需有权限）：ALTER TABLE db1.old RENAME TO db2.new

示例：

ALTER TABLE users RENAME TO app_users;

2.4.6 修改表选项

语法：

ALTER TABLE 表名 表选项;

示例：

-- 修改存储引擎
ALTER TABLE users ENGINE = MyISAM;

-- 修改自增起始值
ALTER TABLE users AUTO_INCREMENT = 1000;

-- 修改字符集和排序规则
ALTER TABLE users CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表注释
ALTER TABLE users COMMENT = '应用用户表';

2.4.7 设置/删除列的默认值

-- 设置默认值
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;

-- 删除默认值
ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT;

示例：

ALTER TABLE users ALTER COLUMN age SET DEFAULT 18;
ALTER TABLE users ALTER COLUMN age DROP DEFAULT;

2.5 删除表

语法：

DROP TABLE [IF EXISTS] 表名1 [, 表名2, ...];

示例：

-- 删除单表
DROP TABLE temp_table;

-- 安全删除（推荐）
DROP TABLE IF EXISTS old_users;

-- 一次删除多个表
DROP TABLE IF EXISTS backup1, backup2, backup3;

2.6 截断表（清空所有数据，重置自增）

语法：

TRUNCATE TABLE 表名;

示例：

TRUNCATE TABLE logs;   -- 删除所有行，自增计数器重置为1

与 DELETE FROM 表名的区别：

• TRUNCATE 是DDL，无法回滚，速度更快，重置自增；DELETE是DML，可回滚，不重置自增。
• TRUNCATE 不会触发删除触发器。

2.7 重命名表（RENAME TABLE）

语法：

RENAME TABLE 旧表名 TO 新表名 [, 旧表名2 TO 新表名2, ...];

示例：

-- 单表重命名
RENAME TABLE users TO user_accounts;

-- 多表重命名（原子操作）
RENAME TABLE old_customers TO customers, old_orders TO orders;

-- 跨数据库移动表（需要权限）
RENAME TABLE db1.employees TO db2.employees;

2.8 临时表管理

2.8.1 创建临时表

语法：

CREATE TEMPORARY TABLE 表名 (列定义) [表选项];

示例：

CREATE TEMPORARY TABLE temp_users SELECT * FROM users WHERE status = 'active';
CREATE TEMPORARY TABLE temp_data (id INT, name VARCHAR(100));

特点：临时表仅在当前会话可见，会话结束后自动删除。

2.8.2 删除临时表

DROP TEMPORARY TABLE [IF EXISTS] 临时表名;

---

三、约束管理（属于表管理的一部分）

约束的分类：PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, DEFAULT。

3.1 主键约束

添加主键

-- 建表时定义
CREATE TABLE t (id INT PRIMARY KEY, ...);

-- 表级定义
CREATE TABLE t (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

-- 复合主键
CREATE TABLE t (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

-- 为已有表添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 启用自增（主键常搭配）
ALTER TABLE users MODIFY id INT AUTO_INCREMENT;

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

⚠️ 如果主键是自增列，需先取消自增属性再删除主键。

3.2 外键约束

添加外键

-- 建表时定义
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 表级定义并命名约束
CREATE TABLE orders (
    ...
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 为已有表添加外键
ALTER TABLE orders ADD CONSTRAINT fk_user_id 
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT;

外键选项：

• ON DELETE CASCADE：删除主表记录时，子表相关记录自动删除
• ON DELETE SET NULL：子表外键列设为NULL
• ON DELETE RESTRICT/NO ACTION：拒绝删除（默认）
• ON UPDATE CASCADE：更新主表主键时，子表同步更新

删除外键

-- 需要先知道外键约束名
SHOW CREATE TABLE orders;

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

3.3 唯一约束

添加唯一约束

-- 建表时
CREATE TABLE users (email VARCHAR(100) UNIQUE);

-- 表级命名
CREATE TABLE users (
    email VARCHAR(100),
    CONSTRAINT uk_email UNIQUE (email)
);

-- 复合唯一
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE KEY uk_user_role (user_id, role_id)
);

-- 为已有表添加
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);
ALTER TABLE users ADD CONSTRAINT uk_username UNIQUE (username);

删除唯一约束

-- 通过索引名删除（唯一约束自动创建索引）
ALTER TABLE users DROP INDEX uk_email;

3.4 CHECK 约束（MySQL 8.0.16+ 完整支持）

添加 CHECK

-- 列级CHECK
CREATE TABLE users (
    age INT CHECK (age >= 0 AND age <= 150)
);

-- 表级命名CHECK
CREATE TABLE products (
    price DECIMAL(10,2),
    CONSTRAINT chk_price CHECK (price > 0)
);

-- 为已有表添加
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

删除 CHECK

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

3.5 NOT NULL 约束

添加/删除 NOT NULL

-- 添加 NOT NULL
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;

-- 删除 NOT NULL（允许NULL）
ALTER TABLE users MODIFY email VARCHAR(100) NULL;

3.6 DEFAULT 约束

设置/修改默认值

-- 建表时
CREATE TABLE users (status VARCHAR(20) DEFAULT 'active');

-- 修改已有表
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'inactive';
ALTER TABLE users MODIFY status VARCHAR(20) DEFAULT 'active';

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

3.7 查看约束信息

-- 查看表中的约束（通过 INFORMATION_SCHEMA）
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'users';

-- 查看外键详情
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'orders';

---

四、索引管理（DDL部分）

4.1 创建索引

语法：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
    ON 表名 (列名 [(长度)] [ASC | DESC], ...)
    [INDEX选项];

示例：

-- 普通索引
CREATE INDEX idx_username ON users(username);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 复合索引
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- 前缀索引（针对字符串列）
CREATE INDEX idx_title ON articles(title(20));

-- 全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);

-- 降序索引（MySQL 8.0支持）
CREATE INDEX idx_created_desc ON orders(created_at DESC);

4.2 删除索引

三种等价方式：

-- 方式1
DROP INDEX 索引名 ON 表名;

-- 方式2
ALTER TABLE 表名 DROP INDEX 索引名;

-- 方式3（删除主键索引）
ALTER TABLE 表名 DROP PRIMARY KEY;

示例：

DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_email;

4.3 查看索引

SHOW INDEX FROM 表名;
SHOW INDEX FROM 表名\G   -- 垂直显示更清晰

---

五、视图管理（DDL部分）

视图是基于SQL查询的虚拟表。

5.1 创建视图

语法：

CREATE [OR REPLACE] VIEW 视图名 [(列名列表)] AS 查询语句
    [WITH [CASCADED | LOCAL] CHECK OPTION];

示例：

-- 简单视图
CREATE VIEW active_users_view AS
SELECT id, username, email FROM users WHERE status = 'active';

-- 带列别名
CREATE VIEW user_summary (user_id, user_name, user_email) AS
SELECT id, username, email FROM users;

-- 使用连接和聚合的复杂视图
CREATE VIEW order_stats AS
SELECT u.username, COUNT(o.id) AS order_count, SUM(o.amount) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 如果视图已存在则替换
CREATE OR REPLACE VIEW active_users_view AS 
SELECT id, username FROM users WHERE status = 'active';

5.2 修改视图定义

ALTER VIEW 视图名 [(列名列表)] AS 查询语句 [WITH CHECK OPTION];

示例：

ALTER VIEW active_users_view AS
SELECT id, username, email, age FROM users WHERE status = 'active';

5.3 删除视图

DROP VIEW [IF EXISTS] 视图名 [, 视图名2, ...] [RESTRICT | CASCADE];

示例：

DROP VIEW IF EXISTS active_users_view;

5.4 查看视图定义

SHOW CREATE VIEW 视图名;

---

六、表注释与列注释

6.1 添加/修改表注释

-- 建表时
CREATE TABLE users (...) COMMENT = '用户信息表';

-- 修改已有表
ALTER TABLE users COMMENT = '系统用户主表';

6.2 添加/修改列注释

-- 建表时
CREATE TABLE users (id INT COMMENT '主键ID');

-- 修改已有列注释（使用 MODIFY 或 CHANGE）
ALTER TABLE users MODIFY id INT COMMENT '用户唯一标识';
ALTER TABLE users CHANGE id id INT COMMENT '用户主键ID';

6.3 查看注释

SHOW FULL COLUMNS FROM users;
SHOW TABLE STATUS WHERE Name = 'users';
SELECT table_comment FROM information_schema.tables WHERE table_name = 'users';

---

七、表空间与分区管理（高级DDL）

7.1 表空间（MySQL 8.0）

-- 创建通用表空间
CREATE TABLESPACE ts_name ADD DATAFILE 'file.ibd' ENGINE=InnoDB;

-- 创建表时指定表空间
CREATE TABLE t (id INT) TABLESPACE ts_name;

-- 修改表的表空间
ALTER TABLE users TABLESPACE ts_name;

7.2 分区表管理

-- 创建范围分区表
CREATE TABLE sales (
    id INT,
    sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 添加分区
ALTER TABLE sales ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

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

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

---

八、实用信息查询命令（辅助DDL）

8.1 查看表的存储引擎

SHOW TABLE STATUS LIKE 'users';
SELECT ENGINE FROM information_schema.tables WHERE table_name = 'users';

8.2 查看表的自增值当前值

SELECT AUTO_INCREMENT FROM information_schema.tables 
WHERE table_name = 'users';

8.3 查看表所占空间

SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'company' AND table_name = 'users';

---

九、DDL操作注意事项总结

注意点	说明
隐式提交	大部分DDL语句执行前会自动提交当前事务，无法回滚
锁表	ALTER TABLE、DROP TABLE等操作可能持有元数据锁，影响并发
数据风险	DROP、TRUNCATE操作不可撤销，务必谨慎
外键依赖	删除被外键引用的表或列前，需先删除外键约束
在线DDL	MySQL 5.6+支持部分在线DDL（如添加索引、添加列），可减少锁表时间
权限要求	执行DDL通常需要相应的CREATE、ALTER、DROP权限

---

十、综合示例：完整的数据库与表设计流程

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS ecommerce
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE ecommerce;

-- 2. 创建表（带约束、索引、注释）
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
    sku VARCHAR(50) NOT NULL UNIQUE COMMENT '商品编码',
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0) COMMENT '价格',
    stock INT NOT NULL DEFAULT 0 COMMENT '库存',
    category_id INT COMMENT '分类ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    FULLTEXT INDEX ft_name (name)
) ENGINE=InnoDB COMMENT='商品表';

-- 3. 添加外键约束
ALTER TABLE products ADD CONSTRAINT fk_category 
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL;

-- 4. 添加新列
ALTER TABLE products ADD COLUMN status ENUM('on','off') DEFAULT 'on' AFTER stock;

-- 5. 修改列定义
ALTER TABLE products MODIFY price DECIMAL(12,2) NOT NULL;

-- 6. 创建视图
CREATE VIEW product_inventory AS
SELECT id, sku, name, stock FROM products WHERE stock > 0;

-- 7. 创建索引优化查询
CREATE INDEX idx_price ON products(price);

-- 8. 重命名表（如果需要）
-- RENAME TABLE products TO merchandise;

-- 9. 查看最终结构
SHOW CREATE TABLE products\G

-- 10. 清理测试表（可选）
-- DROP TABLE IF EXISTS temp_products;

以上覆盖了MySQL中数据库与表管理的几乎所有DDL操作命令，如需更深入的内容（如触发器、存储过程等），那些属于DCL或编程对象，不在此DDL范围之内。