返回文章列表 →

Note

DDL-数据定义

记录操作数据库的常见语法

发布于 更新于

DDL

数据定义语言，主要用来操作数据库对象（数据库、表、字段）

数据库操作

查询

查询所有数据库：

SHOW DATABASES;

查询当前数据库：

SELECT DATABASE();

创建

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

语法中的 [] 表示可选部分：

  • [IF NOT EXISTS]：仅当指定数据库不存在时才创建，避免数据库已存在时出现错误。
  • [DEFAULT CHARSET 字符集]：指定数据库的默认字符集。
  • [COLLATE 排序规则]：指定数据库的默认排序规则，影响字符串的比较和排序方式。

删除

DROP DATABASE [IF EXISTS] 数据库名;

[IF EXISTS] 表示仅当指定数据库存在时才删除，避免数据库不存在时出现错误。

使用

USE 数据库名;

表操作

执行大多数表操作前，需要先使用 USE 数据库名; 选择目标数据库。部分语法也可以使用 数据库名.表名 显式指定目标数据库。

查询

查询当前数据库中的所有表：

SHOW TABLES;

查询表结构，DESC 是 DESCRIBE 的简写：

DESC 表名;

查询指定表的建表语句：

SHOW CREATE TABLE 表名;

创建

CREATE TABLE [IF NOT EXISTS] 表名 (
    字段1 数据类型 [COMMENT '字段1注释'],
    字段2 数据类型 [COMMENT '字段2注释'],
    ...
    字段n 数据类型 [COMMENT '字段n注释']
) [COMMENT = '表注释'];

语法中的 [] 表示可选部分：

  • [IF NOT EXISTS]：仅当指定表不存在时才创建，避免表已存在时出现错误。
  • 字段名 数据类型：定义字段的名称和数据类型；多个字段定义之间使用逗号分隔。
  • [COMMENT '字段注释']：为字段添加可选注释，注释内容需要使用引号包裹。
  • [COMMENT = '表注释']：为表添加可选注释，注释内容需要使用引号包裹。

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

删除

删除表
DROP TABLE [IF EXISTS] 表名;

DROP TABLE 会删除表结构和表中的全部数据。[IF EXISTS] 表示仅当指定表存在时才删除，避免表不存在时出现错误。

清空表数据
TRUNCATE TABLE 表名;

TRUNCATE TABLE 会清空表中的全部数据并保留表结构。MySQL 通常通过删除并重新创建表来实现这一操作，因此执行速度通常比逐行删除更快，并会将 AUTO_INCREMENT 计数器重置为初始值。

TRUNCATE TABLE 不能使用 WHERE 只清空部分数据，并且会隐式提交，不能像普通 DML 操作一样回滚。执行前应确认整张表的数据都可以被清除。

表的数据类型

MySQL 支持多种数据类型，常用类型主要分为数值类型、字符串类型和日期时间类型。选择字段类型时，需要同时考虑取值范围、精度、存储空间和实际业务含义。

数值类型

整数类型默认使用有符号范围，添加 UNSIGNED 后只能存储非负数，并获得更大的正数上限。

类型 大小 有符号范围 无符号范围 描述 举例
TINYINT 1 字节 -128～127 0～255 很小的整数 age TINYINT UNSIGNED：年龄可取 0～255
SMALLINT 2 字节 -32768～32767 0～65535 较小的整数 port SMALLINT UNSIGNED：端口号可取 0～65535
MEDIUMINT 3 字节 -8388608～8388607 0～16777215 中等大小的整数 view_count MEDIUMINT UNSIGNED：记录不超过 16777215 的浏览量
INT 或 INTEGER 4 字节 -2147483648～2147483647 0～4294967295 常用整数 user_id INT UNSIGNED：存储非负用户编号
BIGINT 8 字节 -2^63～2^63-1 0～2^64-1 很大的整数 order_id BIGINT UNSIGNED：存储数量很大的订单编号
FLOAT 4 字节 约 ±3.402823466E+38 不建议使用 UNSIGNED 单精度近似浮点数 temperature FLOAT：存储允许少量精度误差的测量值
DOUBLE 8 字节 约 ±1.7976931348623157E+308 不建议使用 UNSIGNED 双精度近似浮点数 longitude DOUBLE：存储需要较高精度的近似坐标值
DECIMAL(M,D) 由 M 和 D 决定 由 M 和 D 决定 不建议使用 UNSIGNED 精确小数，适合金额 amount DECIMAL(10,2)：共 10 位数字，其中小数占 2 位，可存储 -99999999.99～99999999.99

FLOAT 和 DOUBLE 存储的是近似值，不适合金额等要求精确计算的场景。DECIMAL(M,D) 中，M 表示总有效位数，D 表示小数位数。

字符串类型

字符类型的实际存储空间会受到字符集影响。CHAR、VARCHAR 和 TEXT 按字符解释长度，BLOB 按字节解释长度。

类型 最大长度 描述 举例
CHAR(M) 0～255 个字符 定长字符串 country_code CHAR(2)：最多存储 2 个字符，适合国家代码等固定长度内容
VARCHAR(M) 最多 65535 个字符，实际受字符集和单行大小限制 变长字符串 username VARCHAR(50)：最多存储 50 个字符，按实际内容长度占用空间
TINYBLOB 255 字节 很小的二进制数据 thumbnail TINYBLOB：存储不超过 255 字节的二进制缩略数据
TINYTEXT 255 字节 很短的文本 summary TINYTEXT：存储短摘要，实际可存字符数取决于字符集
BLOB 65535 字节 较长的二进制数据 file_data BLOB：存储不超过约 64 KiB 的二进制内容
TEXT 65535 字节 较长的文本 content TEXT：存储不超过约 64 KiB 的文章内容
MEDIUMBLOB 16777215 字节 中等大小的二进制数据 audio_data MEDIUMBLOB：存储不超过约 16 MiB 的二进制内容
MEDIUMTEXT 16777215 字节 中等长度的文本 article MEDIUMTEXT：存储不超过约 16 MiB 的文本内容
LONGBLOB 4294967295 字节 很大的二进制数据 video_data LONGBLOB：类型上限约为 4 GiB，实际还受服务器配置等限制
LONGTEXT 4294967295 字节 很长的文本 document LONGTEXT：类型上限约为 4 GiB，实际可存字符数取决于字符集和服务器配置

CHAR(M) 和 VARCHAR(M) 中的 M 都表示最多可存储的字符数，不是字节数。例如，code CHAR(10) 和 nickname VARCHAR(10) 都最多存储 10 个字符。

CHAR 按固定长度处理，较短的值会在右侧补空格，读取时通常会移除尾部空格，适合国家代码、状态码等长度固定的数据。VARCHAR 按实际内容长度存储，并额外使用 1 或 2 字节记录长度，适合用户名、邮箱等长度变化的数据。例如存储 abc 时，CHAR(10) 按 10 个字符的定长语义处理，而 VARCHAR(10) 只保存 3 个字符及长度信息。

日期类型

类型 大小 范围 格式 描述 举例
DATE 3 字节 1000-01-01～9999-12-31 YYYY-MM-DD 仅表示日期 birthday DATE：存储 1995-08-20
TIME 3 字节，可因小数秒增加 0～3 字节 -838:59:59～838:59:59 HH:MM:SS 表示时间或持续时间 duration TIME：可存储 36:30:00，表示持续 36 小时 30 分钟
YEAR 1 字节 1901～2155，以及 0000 YYYY 表示年份 graduation_year YEAR：存储 2026
DATETIME 5 字节，可因小数秒增加 0～3 字节 1000-01-01 00:00:00～9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 日期和时间，不进行时区转换 appointment_at DATETIME：存储 2026-08-10 14:30:00
TIMESTAMP 4 字节，可因小数秒增加 0～3 字节 1970-01-01 00:00:01 UTC～2038-01-19 03:14:07 UTC YYYY-MM-DD HH:MM:SS 时间戳，会根据会话时区进行转换 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP：自动记录最近更新时间

TIME、DATETIME 和 TIMESTAMP 可以通过 类型(fsp) 指定 0～6 位小数秒精度，例如 DATETIME(3) 可以保存到毫秒。DATETIME 适合记录与时区无关的本地日期时间，TIMESTAMP 更适合记录需要随会话时区转换的时间点。

表的字段操作

添加字段

ALTER TABLE 表名
ADD COLUMN 字段名 数据类型 [COMMENT '字段注释'] [约束] [FIRST | AFTER 已有字段名];
  • [COMMENT '字段注释']：为字段添加可选注释。
  • [约束]：为字段添加 NOT NULL、DEFAULT 或 UNIQUE 等可选约束。
  • [FIRST | AFTER 已有字段名]：将字段添加到第一列，或添加到指定字段之后；省略时添加到末尾。

修改数据类型

ALTER TABLE 表名
MODIFY COLUMN 字段名 新数据类型 [COMMENT '字段注释'] [约束];

MODIFY COLUMN 可以修改字段定义，但不能修改字段名。修改时需要重新写出希望保留的 NOT NULL、DEFAULT、COMMENT 等属性，否则原有属性可能丢失。缩短字段长度或改变为不兼容的数据类型时，还可能造成数据截断或转换失败。

修改字段名和字段类型

ALTER TABLE 表名
CHANGE COLUMN 旧字段名 新字段名 新数据类型 [COMMENT '字段注释'] [约束];

CHANGE COLUMN 可以同时修改字段名和字段定义，同样需要重新写出希望保留的字段属性。如果只修改字段名、不修改字段定义，使用下面的语法更直接：

ALTER TABLE 表名 RENAME COLUMN 旧字段名 TO 新字段名;

删除字段

ALTER TABLE 表名 DROP COLUMN 字段名;

删除字段会同时删除该字段中的全部数据；如果字段属于索引，相关索引也可能受到影响。

示例

数据准备

创建示例数据库并将其设置为当前数据库：

DROP DATABASE IF EXISTS ddl_demo;

CREATE DATABASE ddl_demo
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ddl_demo;

数据库操作

查询数据库
SHOW DATABASES;
SELECT DATABASE();

表操作

创建员工表

创建 emp 员工表。示例同时使用了数值、字符串和日期类型：

CREATE TABLE emp (
    id INT PRIMARY KEY COMMENT '编号',
    workno VARCHAR(10) COMMENT '工号',
    name VARCHAR(10) COMMENT '姓名',
    gender CHAR(1) COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    idcard CHAR(18) COMMENT '身份证号',
    workaddress VARCHAR(50) COMMENT '工作地址',
    entrydate DATE COMMENT '入职时间'
) COMMENT = '员工表';
查询表
SHOW TABLES;
DESC emp;
SHOW CREATE TABLE emp;
修改表名
ALTER TABLE emp RENAME TO employee;
SHOW TABLES;

字段操作

添加字段

在 idcard 字段后添加邮箱字段：

ALTER TABLE employee
ADD COLUMN email VARCHAR(100) COMMENT '邮箱' AFTER idcard;
修改数据类型

将姓名字段的最大长度从 10 个字符扩展为 20 个字符，并保留字段注释：

ALTER TABLE employee
MODIFY COLUMN name VARCHAR(20) COMMENT '姓名';
修改字段名和字段类型

将 workaddress 重命名为 address，同时将最大长度扩展为 100 个字符：

ALTER TABLE employee
CHANGE COLUMN workaddress address VARCHAR(100) COMMENT '工作地址';

完成修改后，可以再次查询表结构验证结果：

DESC employee;
删除字段

删除刚才添加的邮箱字段：

ALTER TABLE employee DROP COLUMN email;

清理示例对象

确认不再需要示例对象后，依次清空数据、删除表并删除数据库：

TRUNCATE TABLE employee;
DROP TABLE IF EXISTS employee;
DROP DATABASE IF EXISTS ddl_demo;