MySQL DDL 数据定义

数据库操作#

单行写法：创建数据库 CREATE DATABASE [IF NOT EXISTS] <库名> [CHARACTER SET <字符集>] [COLLATE <排序规则>]

-- 创建数据库并指定 utf8mb4 字符集
CREATE DATABASE IF NOT EXISTS mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

单行写法：查看所有数据库 SHOW DATABASES;

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

单行写法：查看建库语句 SHOW CREATE DATABASE <库名>;

-- 查看 mydb 的建库语句
SHOW CREATE DATABASE mydb;

单行写法：切换数据库 USE <库名>;

-- 切换到 mydb
USE mydb;

单行写法：修改数据库字符集 ALTER DATABASE <库名> CHARACTER SET <字符集> COLLATE <排序规则>;

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

单行写法：删除数据库 DROP DATABASE [IF EXISTS] <库名>;

-- 存在时才删除
DROP DATABASE IF EXISTS mydb;

创建表#

换行写法：创建完整表结构 CREATE TABLE [IF NOT EXISTS] <表名> (<列定义>[, <表约束>...]) [ENGINE=<引擎>] [DEFAULT CHARSET=<字符集>];

-- 创建用户表并指定存储引擎与字符集
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  email VARCHAR(100) NOT NULL COMMENT '邮箱',
  age INT UNSIGNED COMMENT '年龄',
  balance DECIMAL(10,2) DEFAULT 0.00 COMMENT '余额',
  status TINYINT DEFAULT 1 COMMENT '状态',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

单行写法：仅复制表结构 CREATE TABLE <新表> LIKE <源表>;

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

单行写法：复制结构和数据 CREATE TABLE <新表> AS SELECT * FROM <源表>;

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

单行写法：复制部分数据 CREATE TABLE <新表> AS SELECT * FROM <源表> WHERE <条件>;

-- 仅复制符合条件的数据
CREATE TABLE active_users AS SELECT * FROM users WHERE status = 1;

查看表结构#

单行写法：查看表字段 DESC <表名>;

-- 查看表字段信息
DESC users;

单行写法：查看列详细信息 SHOW COLUMNS FROM <表名>;

-- 查看列的详细定义
SHOW COLUMNS FROM users;

单行写法：查看建表语句 SHOW CREATE TABLE <表名>;

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

单行写法：查看当前库所有表 SHOW TABLES;

-- 列出当前数据库的所有表
SHOW TABLES;

单行写法：模糊查表 SHOW TABLES LIKE '<模式>';

-- 模糊查询表名
SHOW TABLES LIKE '%user%';

修改表 ALTER TABLE#

单行写法：添加列 ALTER TABLE <表名> ADD COLUMN <列定义> [AFTER <列名>];

-- 在 email 列后添加新列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

单行写法：快速添加列（8.0.12+ INSTANT） ALTER TABLE <表名> ADD COLUMN <列定义>, ALGORITHM=INSTANT;

-- 即时添加列，不修改数据行
ALTER TABLE users ADD COLUMN nickname VARCHAR(50), ALGORITHM=INSTANT;

单行写法：修改列定义 ALTER TABLE <表名> MODIFY COLUMN <列名> <新类型> [<约束>];

-- 修改列类型并加约束
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;

单行写法：重命名列 ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列名> <类型> [<约束>];

-- 重命名列并保留类型
ALTER TABLE users CHANGE COLUMN phone telephone VARCHAR(20) NOT NULL;

单行写法：删除列 ALTER TABLE <表名> DROP COLUMN <列名>;

-- 删除指定列
ALTER TABLE users DROP COLUMN nickname;

单行写法：重命名表 ALTER TABLE <旧表名> RENAME TO <新表名>;

-- 重命名表
ALTER TABLE users RENAME TO user_info;

单行写法：多表重命名 RENAME TABLE <旧1> TO <新1>, <旧2> TO <新2>;

-- 同时重命名多个表
RENAME TABLE users TO user_info, orders TO order_info;

约束管理#

单行写法：添加主键 ALTER TABLE <表名> ADD PRIMARY KEY (<列名>);

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

单行写法：添加唯一约束 ALTER TABLE <表名> ADD UNIQUE INDEX <索引名> (<列名>);

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

单行写法：添加外键 ALTER TABLE <表名> ADD CONSTRAINT <约束名> FOREIGN KEY (<列名>) REFERENCES <父表>(<父列>) [ON DELETE <动作>] [ON UPDATE <动作>];

-- 添加外键并设置级联更新
ALTER TABLE orders ADD CONSTRAINT fk_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE RESTRICT ON UPDATE CASCADE;

单行写法：添加 CHECK 约束（8.0.16+） ALTER TABLE <表名> ADD CONSTRAINT <约束名> CHECK (<条件>);

-- 添加检查约束
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0 AND age < 150);

单行写法：删除外键 ALTER TABLE <表名> DROP FOREIGN KEY <约束名>;

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

单行写法：删除主键 ALTER TABLE <表名> DROP PRIMARY KEY;

-- 删除主键约束
ALTER TABLE users DROP PRIMARY KEY;

删除表与清空#

单行写法：删除表 DROP TABLE [IF EXISTS] <表名>[, <表名>...];

-- 同时删除多个表
DROP TABLE IF EXISTS users, orders, products;

单行写法：清空表数据 TRUNCATE TABLE <表名>;

-- 清空表数据并重置自增ID
TRUNCATE TABLE users;

视图#

换行写法：创建视图 CREATE [OR REPLACE] VIEW <视图名> AS <SELECT 语句>;

-- 创建或替换活跃用户视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email FROM users WHERE status = 1;

单行写法：删除视图 DROP VIEW [IF EXISTS] <视图名>;

-- 删除视图
DROP VIEW IF EXISTS active_users;