SQL 数据定义（DDL）全解

我叫黑大帅
0 阅读15分钟

SQL 数据定义（DDL）全解

本文完全承接之前的 SQL 学习路径，聚焦 SQL 体系的基石 ——数据定义语言（DDL，Data Definition Language） 。所有 SQL 的增删改查操作，都必须建立在「先定义数据结构」的基础上。

什么是 SQL 数据定义（DDL）

SQL 数据定义，核心是通过 DDL 语句，对关系型数据库的结构化对象进行创建、修改、删除、重命名等操作，定义数据的存储结构、约束规则、关联关系，是整个数据库的「骨架设计」。

SQL 分类核心作用操作对象开发高频度
DDL（数据定义语言）定义数据库结构对象库、表、索引、约束、视图等★★★★☆
DQL（数据查询语言）查询数据表中的行和列★★★★★
DML（数据操纵语言）增删改数据表中的行数据★★★★★
TCL（事务控制语言）事务提交 / 回滚事务内的 DML 操作★★★☆☆
DCL（数据控制语言）权限管理用户、角色、权限★☆☆☆☆
  • 自动提交，不可回滚：绝大多数数据库中，DDL 语句执行后会自动提交事务，无法通过ROLLBACK回滚，一旦执行错误，只能通过备份恢复，生产环境操作必须极度谨慎。
  • 锁表风险：对大表执行 DDL 操作（如新增字段、修改字段类型），可能会触发表锁，导致业务读写阻塞，高并发场景极易引发线上故障。
  • 权限要求高：DDL 操作需要数据库的高阶权限，生产环境通常仅 DBA 拥有，开发人员需在测试环境验证后，提交 DBA 执行。

数据库级别 DDL 操作

数据库是表、索引等所有对象的容器，库级别操作是 DDL 的第一步，核心是对数据库本身进行创建、修改、删除、切换。

创建数据库：CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] 数据库名
[DEFAULT CHARACTER SET 字符集名]
[DEFAULT COLLATE 排序规则名]
[COMMENT '数据库注释'];
  • IF NOT EXISTS：避免数据库已存在时报错，生产环境必加；
  • CHARACTER SET：指定数据库默认字符集，所有表默认继承该字符集；
  • COLLATE：指定排序规则，影响字符串的比较、排序逻辑。
-- 创建电商业务数据库，完全符合生产规范
CREATE DATABASE IF NOT EXISTS ecommerce
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci
COMMENT '电商业务核心数据库';

  • 必须使用utf8mb4字符集：MySQL 的utf8最多支持 3 字节，无法存储 emoji、生僻字；utf8mb4是完整的 4 字节 UTF-8 编码，生产环境强制使用。

  • 排序规则选择：通用场景用utf8mb4_general_ci：兼容性好，性能优异，不区分大小写；MySQL 8.0+ 推荐utf8mb4_0900_ai_ci：新一代排序规则，精度更高，性能更优。

  • 数据库命名规范：全小写字母 + 下划线分隔，禁止驼峰、拼音、特殊字符，名称体现业务含义，如ecommerce、user_center。

修改数据库：ALTER DATABASE

仅用于修改数据库的全局属性，无法修改数据库名，常用场景为修改字符集、排序规则。

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

删除数据库：DROP DATABASE

极度危险操作，会删除数据库内所有表、数据、索引等对象，且无法回滚，生产环境严禁随意执行。

-- 安全写法：仅当数据库存在时才删除
DROP DATABASE IF EXISTS ecommerce;

查看与切换数据库

-- 查看所有数据库
SHOW DATABASES;

-- 查看当前数据库的创建信息
SHOW CREATE DATABASE ecommerce;

-- 切换到指定数据库（后续表操作默认在该库下执行）
USE ecommerce;

表级别 DDL 操作（开发核心）

表是关系型数据库中数据存储的核心载体，表级别 DDL 是开发人员日常接触最多的操作，核心包括表的创建、修改、删除、清空四大类。

创建表：CREATE TABLE

CREATE TABLE [IF NOT EXISTS] 表名 (
  字段1 字段类型 [字段约束] [COMMENT '字段注释'],
  字段2 字段类型 [字段约束] [COMMENT '字段注释'],
  -- 更多字段定义...
  [表级约束定义（主键、唯一键、外键等）]
) [ENGINE = 存储引擎]
[DEFAULT CHARACTER SET 字符集]
[COLLATE 排序规则]
[COMMENT '表注释'];
属性生产推荐配置核心说明
存储引擎InnoDBMySQL 默认引擎，支持事务、行级锁、外键、崩溃恢复，生产环境强制使用，禁止使用 MyISAM 等其他引擎
字符集utf8mb4继承数据库默认值，可单独为表指定，保证字符集统一
排序规则与库一致保证字符串比较、排序逻辑统一
表注释强制必填清晰说明表的业务含义，禁止无注释建表

整数类型

  • TINYINT：占用 1 字节，有符号取值范围 - 128 ~ 127，无符号 0 ~ 255，适用于状态、性别、是否删除、枚举值等场景，建议优先使用 UNSIGNED 无符号类型，避免出现负数。
  • INT：占用 4 字节，无符号取值 0~4294967295，是常规数值的常用类型，多用于主键、用户 ID、订单 ID、数量等场景，为绝大多数场景的主键首选类型。
  • BIGINT：占用 8 字节，无符号取值 0~18446744073709551615，适用于超大数量主键、雪花 ID、大数据量表主键，需避免滥用，INT 可满足的场景无需使用。

高精度数值类型

  • DECIMAL(M,D)： M 代表总位数，D 为小数位数，存储无精度丢失，专门用于金额、价格、积分等高精度数值场景，严禁使用 FLOAT/DOUBLE 存储金额，会产生精度丢失问题。

字符串类型

  • CHAR(N)： 固定长度 N，最大支持 255 字符，适合存储手机号、MD5 密码、身份证号等固定长度内容，固定长度场景下性能优于 VARCHAR。
  • VARCHAR(N)： 变长字符串，最大 65535 字节，用于用户名、邮箱、地址、商品名称等变长内容，长度 N 需按需设置，不要无意义设置为 VARCHAR (255)。
  • TEXT： 长文本类型，最大支持 64KB，适用于商品详情、备注、富文本等长内容，大表场景谨慎使用，会影响查询性能，且不建议在该字段建立索引。

日期时间类型

  • DATETIME：取值范围 1000-01-01~9999-12-31，精度可达秒 / 毫秒，与时区无关，是创建时间、更新时间、支付时间等所有业务时间的生产首选。
  • DATE：仅存储日期不包含时间，适用于生日、统计日期、下单日期等仅需日期的场景，占用存储空间更小。
  • TIMESTAMP：取值范围 1970-01-01~2038-01-19，受时区影响且存在 2038 年问题，核心业务时间字段禁止使用，实际生产极少应用。

注意事项

  • 必须有主键：每张表必须定义主键，优先使用无符号 INT/BIGINT 自增主键，禁止无主键建表。
  • 必须有核心通用字段：create_time（创建时间，默认当前时间）、update_time（更新时间，自动更新）、is_delete（逻辑删除标记，默认 0）。
  • 所有字段必须加注释：表、字段必须有清晰的业务注释，枚举值必须说明含义。
  • 优先设置非空约束：尽量给字段设置NOT NULL和默认值，避免 NULL 值带来的索引失效、查询异常问题。
  • 禁止使用外键物理约束：互联网高并发场景，仅保留逻辑外键（如 user_id），不创建物理外键，避免锁和性能问题。

修改表结构：ALTER TABLE

用于修改已存在表的结构，包括新增 / 修改 / 删除字段、约束、表属性等，是开发中最常用的表修改操作。

生产警告：大表执行 ALTER TABLE 前，必须评估数据量，在业务低峰期执行，避免锁表导致业务阻塞。

字段相关操作

-- 1. 新增字段：给users表新增gender字段，指定位置在age之后
ALTER TABLE users
ADD COLUMN gender TINYINT NOT NULL DEFAULT 0 COMMENT '性别：0-未知，1-男，2-女' AFTER age;

-- 2. 修改字段类型/属性：修改username字段的长度为60，保持非空
ALTER TABLE users
MODIFY COLUMN username VARCHAR(60) NOT NULL COMMENT '用户名';

-- 3. 修改字段名：将user_age字段重命名为age
ALTER TABLE users
CHANGE COLUMN user_age age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄';

-- 4. 删除字段：删除users表的avatar字段
ALTER TABLE users
DROP COLUMN avatar;

约束与索引相关操作

-- 1. 新增主键约束（极少用，建表时已定义）
ALTER TABLE users
ADD PRIMARY KEY (id);

-- 2. 新增唯一约束
ALTER TABLE users
ADD UNIQUE KEY uk_phone (phone);

-- 3. 新增普通索引
ALTER TABLE users
ADD INDEX idx_create_time (create_time);

-- 4. 删除索引
ALTER TABLE users
DROP INDEX idx_create_time;

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

表属性修改

-- 修改表的字符集
ALTER TABLE users
DEFAULT CHARACTER SET utf8mb4;

-- 修改表注释
ALTER TABLE users
COMMENT '用户信息主表';

-- 表重命名（谨慎操作，会影响业务代码）
ALTER TABLE users
RENAME TO user_info;

删除表：DROP TABLE

极度危险操作，删除表的结构、所有数据、索引、约束，且无法回滚，生产环境严禁随意执行。

-- 安全写法：仅当表存在时才删除
DROP TABLE IF EXISTS users;

-- 批量删除多张表
DROP TABLE IF EXISTS order_goods, orders;

清空表：TRUNCATE TABLE

用于清空表内所有数据，保留表的结构、索引、约束，本质是 DDL 操作，和 DML 的 DELETE 有本质区别。

-- 清空orders表所有数据
TRUNCATE TABLE orders;
特性TRUNCATE（DDL）DELETE（DML）
条件过滤不支持 WHERE，只能全表清空支持 WHERE，可精准删除指定行
自增主键重置为初始值不重置，保持原有自增序列
事务回滚不支持，执行后无法回滚支持，在事务内可回滚
执行效率大表极快，不记录单行日志大表极慢，逐行记录操作日志
触发器不会触发 DELETE 触发器会触发 DELETE 触发器

约束定义：数据完整性的核心保障

约束是定义在字段 / 表上的规则，用于限制数据的格式、范围、关联关系，从数据库层面保证数据的准确性、一致性，杜绝脏数据写入。

  • 列级约束：定义在字段后面，仅作用于单个字段；
  • 表级约束：定义在所有字段之后，可作用于多个字段（如联合主键、联合唯一键）。
主键约束（PRIMARY KEY）
-- 列级定义（单字段主键）
CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键'
);

-- 表级定义（支持联合主键）
CREATE TABLE user_role (
  user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
  role_id INT UNSIGNED NOT NULL COMMENT '角色ID',
  PRIMARY KEY (user_id, role_id) -- 联合主键，用户+角色唯一
);

  • 核心作用：唯一标识表中的每一行数据，非空、唯一，一张表只能有一个主键；

  • 特性：主键字段默认创建聚簇索引，查询性能最高；

非空约束（NOT NULL）
CREATE TABLE users (
  username VARCHAR(50) NOT NULL COMMENT '用户名，非空'
);

  • 核心作用：限制字段不能为 NULL 值，必须填写具体值；

  • 生产规范：所有业务字段尽量设置 NOT NULL + 默认值，避免 NULL 值导致的索引失效、查询异常；

唯一约束（UNIQUE）
-- 列级定义
CREATE TABLE users (
  email VARCHAR(100) UNIQUE COMMENT '邮箱，唯一'
);

-- 表级定义（支持联合唯一键）
CREATE TABLE users (
  id INT PRIMARY KEY,
  phone CHAR(11),
  UNIQUE KEY uk_phone (phone) -- 表级定义，可自定义约束名
);

  • 核心作用：限制字段的值在表中唯一，不能重复，允许有 NULL 值（多个 NULL 值不算重复）；

  • 特性：创建唯一约束时，会自动创建同名的唯一索引，查询性能极高；

默认值约束（DEFAULT）

  • 核心作用：字段未赋值时，自动使用默认值填充；

  • 常用场景：状态字段、逻辑删除标记、创建时间等；

CREATE TABLE users (
  gender TINYINT NOT NULL DEFAULT 0 COMMENT '性别，默认0-未知',
  is_delete TINYINT NOT NULL DEFAULT 0 COMMENT '逻辑删除，默认0-未删除',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，默认当前时间'
);
检查约束（CHECK）
CREATE TABLE users (
  age TINYINT UNSIGNED CHECK (age >= 0 AND age <= 120) COMMENT '年龄必须在0-120之间',
  total_amount DECIMAL(10,2) CHECK (total_amount >= 0) COMMENT '金额不能为负数'
);

  • 核心作用：自定义字段值的校验规则，只有满足条件的数据才能写入；

  • 注意：MySQL 8.0.16+ 才正式支持 CHECK 约束，之前的版本仅语法兼容，不生效；

外键约束（FOREIGN KEY）
CREATE TABLE orders (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL COMMENT '用户ID，外键关联users.id',
  -- 表级定义外键约束
  CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE RESTRICT
);

  • 核心作用：建立两张表的关联关系，保证从表的外键值必须存在于主表的主键中，杜绝无主数据；

  • 级联规则：支持ON DELETE RESTRICT/CASCADE/SET NULL等级联操作；

  • 生产提示：互联网高并发场景不推荐使用物理外键，改用代码层面保证数据一致性；

索引定义：查询性能的核心基石

索引是提升 SQL 查询性能的核心手段，本质是通过排序的数据结构，避免全表扫描，大幅提升查询效率。索引属于数据库对象，通过 DDL 语句进行创建、查看、删除。

索引的创建语法

-- 1. 创建普通索引
CREATE INDEX idx_city ON users(city);

-- 2. 创建唯一索引
CREATE UNIQUE INDEX uk_email ON users(email);

-- 3. 创建联合索引（最左前缀原则，高区分度字段放前面）
CREATE INDEX idx_city_age_gender ON users(city, age, gender);

-- 4. 创建前缀索引（长字符串字段，仅对前N个字符建索引，节省空间）
CREATE INDEX idx_email_prefix ON users(email(20));

-- 5. ALTER TABLE方式创建索引
ALTER TABLE users ADD INDEX idx_create_time(create_time);

索引的查看与删除

-- 查看表中所有索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_city ON users;
-- 或ALTER TABLE方式删除
ALTER TABLE users DROP INDEX idx_city;

索引定义生产最佳实践

  1. 优先为 WHERE、ORDER BY、GROUP BY 后的字段建索引，SELECT 后的字段无需单独建索引；
  2. 联合索引遵循最左前缀原则，高区分度字段放在最左侧，单表索引数量控制在 5 个以内；
  3. 小表、低区分度字段无需建索引（如 gender、is_delete 等仅 2-3 个值的字段）；
  4. 避免冗余索引：已有联合索引idx_a_b(a,b)，无需再为 a 单独建索引；
  5. 长字符串字段使用前缀索引，避免索引占用空间过大。

开发常用其他数据库对象定义

视图（VIEW）

视图是基于 SQL 查询结果创建的虚拟表，本身不存储数据，数据来自底层的基础表，仅保存查询逻辑。

  • 简化复杂查询：将多表关联、复杂聚合的查询封装为视图，直接查询视图即可；
  • 权限控制：限制用户只能访问视图中的字段，屏蔽底层表的敏感字段；
  • 数据隔离：固定查询逻辑，避免业务代码重复写复杂 SQL。
-- 创建视图：用户订单统计视图
CREATE VIEW v_user_order_stats AS
SELECT 
  u.id AS user_id,
  u.username,
  COUNT(o.id) AS total_order_num,
  SUM(o.total_amount) AS total_consume_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.is_pay = 1
WHERE u.is_delete = 0
GROUP BY u.id, u.username;

-- 查询视图（和查询普通表语法一致）
SELECT * FROM v_user_order_stats WHERE user_id = 1;

-- 修改视图
ALTER VIEW v_user_order_stats AS
-- 新的查询逻辑...

-- 删除视图
DROP VIEW IF EXISTS v_user_order_stats;
  • 禁止在视图中嵌套多层视图、多表复杂 JOIN，会严重影响查询性能；
  • 禁止通过视图更新底层表数据，仅用于查询场景；
  • 视图命名统一加v_前缀，和普通表区分。

临时表（TEMPORARY TABLE）

临时表是仅在当前数据库会话中有效的表，会话关闭后自动删除，不会占用持久化存储空间，常用于复杂查询的中间结果存储、批量数据处理。

-- 创建临时表：存储用户订单临时统计数据
CREATE TEMPORARY TABLE temp_user_order (
  user_id INT UNSIGNED PRIMARY KEY,
  total_order_num INT NOT NULL DEFAULT 0,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 向临时表插入数据
INSERT INTO temp_user_order(user_id, total_order_num, total_amount)
SELECT user_id, COUNT(*), SUM(total_amount) FROM orders WHERE is_pay = 1 GROUP BY user_id;

-- 查询临时表
SELECT * FROM temp_user_order;

-- 手动删除临时表（会话关闭自动删除，也可手动删除）
DROP TEMPORARY TABLE IF EXISTS temp_user_order;
  • 临时表仅当前会话可见，不同会话可创建同名临时表，互不影响；
  • 临时表支持索引、约束，和普通表用法一致；
  • 不支持跨库事务、主从同步，仅用于当前会话的临时数据处理。
avatar
文章
阅读
粉丝