文章目录

表的操作：数据的容器

一、前言

💬 这一篇讲什么：学会操纵表的结构

🚀 核心内容：

  • 如何创建表？
  • 表的存储引擎如何选择？
  • 如何修改表的结构？
  • 如何删除表？

在前一篇中，我们学会了创建和管理数据库。现在需要在数据库中创建表。表是数据库中最重要的对象，它以行和列的形式存储实际数据。这一篇将深入讲解表的创建、修改和删除。

二、创建表

2.1 基本语法

创建表的标准语法如下：

CREATE TABLE table_name (
    field1 datatype [DEFAULT expr] [COMMENT 'comment'],
    field2 datatype [DEFAULT expr] [COMMENT 'comment'],
    field3 datatype [DEFAULT expr] [COMMENT 'comment']
) [CHARACTER SET charset_name] 
  [COLLATE collation_name] 
  [ENGINE=engine_name];

各参数说明：

参数说明
table_name表的名称
field列（字段）的名称
datatype列的数据类型（如 INT、VARCHAR 等）
DEFAULT expr可选。列的默认值
COMMENT可选。列的注释说明
CHARACTER SET可选。表的字符集，不指定则继承数据库的字符集
COLLATE可选。表的校验规则，不指定则继承数据库的校验规则
ENGINE可选。存储引擎，不指定则使用 MySQL 默认引擎（通常是 InnoDB）

2.2 创建表的实例

最简单的创建
CREATE TABLE users (
    id INT,
    name VARCHAR(20),
    email VARCHAR(50)
);

这样创建的表会使用数据库的默认字符集、校验规则和存储引擎。

完整的创建（推荐）
CREATE TABLE users (
    id INT COMMENT '用户ID',
    name VARCHAR(20) COMMENT '用户名',
    password CHAR(32) COMMENT '密码是32位的MD5值',
    birthday DATE COMMENT '生日',
    email VARCHAR(50) COMMENT '邮箱'
) CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci 
  ENGINE=InnoDB;

这个例子中：

  • 每个字段都有 COMMENT，便于理解表的结构。
  • 指定了字符集为 utf8mb4，支持所有语言和表情。
  • 指定了校验规则为 utf8mb4_unicode_ci，不区分大小写。
  • 指定了存储引擎为 InnoDB，支持事务和外键。
带默认值的创建
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    name VARCHAR(20) NOT NULL COMMENT '用户名',
    status INT DEFAULT 1 COMMENT '状态：1=活跃，0=禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB;

这个例子中：

  • AUTO_INCREMENT：id 自动递增。
  • PRIMARY KEY：id 是主键（后面会详细讲）。
  • NOT NULL：name 不能为空。
  • DEFAULT 1：status 的默认值是 1。
  • DEFAULT CURRENT_TIMESTAMP：created_at 默认为当前时间。

2.3 存储引擎与文件结构

不同的存储引擎在磁盘上存储数据的方式不同。

MyISAM 存储引擎

如果创建表时指定 ENGINE=MyISAM：

CREATE TABLE users (
    id INT,
    name VARCHAR(20)
) ENGINE=MyISAM;

在 MySQL 数据目录中会生成三个文件：

文件说明
users.frm表结构定义文件
users.MYD表数据文件（MyISAM Data）
users.MYI表索引文件（MyISAM Index）
InnoDB 存储引擎

如果创建表时指定 ENGINE=InnoDB：

CREATE TABLE users (
    id INT,
    name VARCHAR(20)
) ENGINE=InnoDB;

在 MySQL 数据目录中会生成：

文件说明
users.frm表结构定义文件
users.ibd表数据和索引文件（InnoDB Data）

InnoDB 将数据和索引存储在同一个文件中，这是它的特点。

2.4 查看表文件

在 Ubuntu 中，MySQL 的数据目录通常是 /var/lib/mysql。你可以查看表文件：

sudo ls -la /var/lib/mysql/database_name/

例如，查看 db1 数据库中的表文件：

sudo ls -la /var/lib/mysql/db1/

输出示例（假设有 InnoDB 表 users 和 MyISAM 表 products）：

-rw-r-----  1 mysql mysql  8586 Apr  7 14:23 users.frm
-rw-r-----  1 mysql mysql 98304 Apr  7 14:23 users.ibd
-rw-r-----  1 mysql mysql  8586 Apr  7 14:24 products.frm
-rw-r-----  1 mysql mysql 16384 Apr  7 14:24 products.MYD
-rw-r-----  1 mysql mysql  2048 Apr  7 14:24 products.MYI

三、查看表结构

3.1 DESC 命令

查看表结构最常用的命令是 DESC（DESCRIBE 的缩写）：

DESC table_name;

示例：

DESC users;

输出：

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)  | YES  |     | NULL    |       |
| password | char(32)     | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

字段说明：

字段说明
Field列名
Type列的数据类型
Null是否允许为空（YES/NO）
Key是否是键（PRI=主键，UNI=唯一键，MUL=普通索引）
Default默认值
Extra额外信息（如 auto_increment）

3.2 SHOW CREATE TABLE 命令

查看创建表时的完整 SQL 语句：

SHOW CREATE TABLE table_name;

示例：

SHOW CREATE TABLE users;

输出：

+-------+-----------------------------------------------------+
| Table | Create Table                                        |
+-------+-----------------------------------------------------+
| users | CREATE TABLE `users` (                              |
|       |   `id` int(11) NOT NULL AUTO_INCREMENT,             |
|       |   `name` varchar(20) DEFAULT NULL,                  |
|       |   `password` char(32) DEFAULT NULL,                 |
|       |   `birthday` date DEFAULT NULL,                     |
|       |   PRIMARY KEY (`id`)                                |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4             |
+-------+-----------------------------------------------------+

这个命令显示了表的完整定义，包括所有约束和引擎信息。

3.3 SHOW TABLE STATUS 命令

查看表的详细统计信息：

SHOW TABLE STATUS LIKE 'table_name'\G

\G 表示竖向显示结果，便于查看。

示例：

SHOW TABLE STATUS LIKE 'users'\G

输出：

Name            : users
Engine          : InnoDB
Version         : 10
Row_format      : Dynamic
Rows            : 2
Avg_row_length  : 8192
Data_length     : 16384
Index_length    : 0
Data_free       : 0
Auto_increment  : 3
Create_time     : 2024-04-07 14:23:45
Update_time     : 2024-04-07 14:25:10
Check_time      : NULL
Collation       : utf8mb4_unicode_ci
Checksum        : NULL
Create_options  :
Comment         :

这个命令显示了表的行数、大小、创建时间等信息。

四、修改表结构

在实际开发中，经常需要修改表的结构。比如添加新字段、修改字段类型、删除不需要的字段等。

4.1 添加字段

语法
ALTER TABLE table_name ADD (
    column1 datatype [DEFAULT expr] [COMMENT 'comment'],
    column2 datatype [DEFAULT expr] [COMMENT 'comment']
) [FIRST | AFTER column_name];

参数说明：

参数说明
FIRST可选。新字段添加到表的最前面
AFTER column_name可选。新字段添加到指定字段之后

如果不指定位置，新字段默认添加到表的最后。

示例一：添加单个字段到最后
ALTER TABLE users ADD phone VARCHAR(20) COMMENT '电话号码';
示例二：添加字段到指定位置
ALTER TABLE users ADD avatar VARCHAR(200) COMMENT '头像URL' AFTER name;

这样 avatar 字段会插入到 name 字段之后。

示例三：同时添加多个字段
ALTER TABLE users ADD (
    phone VARCHAR(20) COMMENT '电话号码',
    address VARCHAR(100) COMMENT '地址',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);
验证添加结果
DESC users;

4.2 修改字段

语法
ALTER TABLE table_name MODIFY column_name datatype [DEFAULT expr] [COMMENT 'comment'];

MODIFY 用来修改字段的类型、默认值或注释，但不能改字段名。

示例一：修改字段类型
ALTER TABLE users MODIFY name VARCHAR(60);

这样 name 字段的长度从 20 改成 60。

示例二：修改字段默认值
ALTER TABLE users MODIFY status INT DEFAULT 0 COMMENT '用户状态';
验证修改结果
DESC users;

4.3 重命名字段

语法
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype [DEFAULT expr] [COMMENT 'comment'];

CHANGE 用来重命名字段。注意：必须完整定义新字段的类型和其他属性。

示例
ALTER TABLE users CHANGE name username VARCHAR(60) COMMENT '用户名';

这样 name 字段被重命名为 username，同时修改了类型和注释。

验证重命名结果
DESC users;

输出中 Field 列会显示 username 而不是 name。

4.4 删除字段

语法
ALTER TABLE table_name DROP column_name;
示例
ALTER TABLE users DROP password;
警告

删除字段是不可逆的操作。一旦删除，该字段及其所有数据都会丢失。删除前务必确认：

  1. 该字段确实不再需要。
  2. 已备份数据库。
  3. 没有其他应用依赖这个字段。

4.5 修改表名

语法
ALTER TABLE old_table_name RENAME [TO] new_table_name;

TO 关键字是可选的。

示例
ALTER TABLE users RENAME TO employees;

或者：

ALTER TABLE users RENAME employees;
验证重命名结果
SHOW TABLES;
DESC employees;

4.6 修改表的存储引擎

语法
ALTER TABLE table_name ENGINE=engine_name;
示例
ALTER TABLE users ENGINE=InnoDB;

这样会将表从当前引擎转换为 InnoDB。转换过程中，MySQL 会重新组织数据，可能需要一些时间。

验证修改结果
SHOW CREATE TABLE users;

4.7 修改表的字符集

语法
ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
示例
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

这样会将表中所有字符类型的字段转换为新的字符集。

五、删除表

5.1 基本语法

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name ...];

参数说明：

参数说明
TEMPORARY可选。只删除临时表
IF EXISTS可选。表不存在时不报错
table_name要删除的表名，可以同时删除多个表

5.2 删除单个表

DROP TABLE users;

如果表不存在，会报错。

5.3 安全删除（推荐）

DROP TABLE IF EXISTS users;

如果表存在就删除，不存在也不报错。这在脚本中很有用。

5.4 同时删除多个表

DROP TABLE IF EXISTS users, products, orders;

5.5 删除后的影响

  • 表的结构被删除。
  • 表中的所有数据被删除。
  • 表相关的索引被删除。
  • 表对应的磁盘文件被删除。

警告：删除表是不可逆的操作。确保数据已备份后再执行删除。

5.6 TRUNCATE vs DROP

有时候需要清空表中的数据但保留表结构，这时可以用 TRUNCATE：

TRUNCATE TABLE users;

TRUNCATE 和 DROP 的区别：

特性TRUNCATEDROP
删除内容只删除数据删除表结构和数据
保留结构✅ 保留❌ 删除
速度快（不记录日志）较慢
恢复难以恢复无法恢复
自增ID重置为 1无影响

六、实践案例

6.1 完整的表操作流程

现在通过一个完整的例子，演示表的创建、修改和删除。

第一步：创建数据库
CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4;
USE shop;
第二步：创建表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
    name VARCHAR(50) NOT NULL COMMENT '商品名称',
    price DECIMAL(10, 2) COMMENT '商品价格',
    stock INT DEFAULT 0 COMMENT '库存数量'
) ENGINE=InnoDB;
第三步：查看表结构
DESC products;
SHOW CREATE TABLE products;
第四步：插入测试数据
INSERT INTO products (name, price, stock) VALUES 
('iPhone 15', 5999.00, 100),
('MacBook Pro', 12999.00, 50);
第五步：修改表结构

添加分类字段：

ALTER TABLE products ADD category VARCHAR(30) COMMENT '商品分类' AFTER name;

修改价格字段精度：

ALTER TABLE products MODIFY price DECIMAL(12, 2);

添加创建时间字段：

ALTER TABLE products ADD created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';
第六步：验证修改
DESC products;
SELECT * FROM products;
第七步：删除表
DROP TABLE IF EXISTS products;

七、表操作的最佳实践

7.1 设计表时的建议

  • 总是指定字符集：避免继承数据库的默认值导致不一致。
  • 使用 InnoDB：除非有特殊原因，否则总是用 InnoDB。
  • 添加注释：为每个字段添加 COMMENT，便于维护。
  • 使用有意义的名称：字段名要清晰，避免缩写。
  • 考虑未来扩展：字段类型的长度要留有余量。

7.2 修改表时的建议

  • 备份数据：修改前总是备份数据库。
  • 在测试环境验证：先在测试库中修改，确认无误后再在生产库中执行。
  • 避免删除字段：如果字段不再使用，考虑添加一个标记字段（如 is_deleted）而不是直接删除。
  • 大表修改要谨慎：修改大表的结构可能需要很长时间，最好在业务低谷期执行。

7.3 命名规范

  • 表名：使用小写，多个单词用下划线分隔。如 user_profile、order_detail。
  • 字段名：同样使用小写和下划线。如 user_id、created_at。
  • 避免关键字：不要用 user、order 等 SQL 关键字作为表名。
  • 使用前缀：相关的表可以用相同前缀。如 user_profile、user_setting。

八、总结与下一步

现在你已经掌握了：

✅ 创建表：指定字段、类型、默认值、注释和存储引擎

✅ 查看表结构：使用 DESC、SHOW CREATE TABLE 等命令

✅ 修改表：添加、修改、重命名、删除字段，修改表名和引擎

✅ 删除表：安全地删除表及其数据

✅ 最佳实践：表设计和维护的建议

建议练习：

  1. 创建一个 users 表，包含 id、username、email、age、created_at 等字段。
  2. 添加一个 phone 字段到 name 字段之后。
  3. 修改 email 字段的长度为 100。
  4. 重命名 age 字段为 user_age。
  5. 删除 phone 字段。
  6. 查看最终的表结构。

下一篇，我们将学习数据类型。数据类型决定了字段能存储什么样的数据，是表设计的基础。我们会详细讲解整数、浮点数、字符串、日期等各种类型的特点和使用场景。

# mysql # oracle # 数据库 # linux
Logo
亚马逊云科技技术品牌专区

更多推荐

cover

基于STM32的全屋环境智能监测与预警终端设计

avatar 亚马逊云科技技术品牌专区

Keras：面向人类的深度学习库

Keras3支持多后端（TensorFlow、PyTorch、JAX），通过设置环境变量切换，兼容指定版本。pip安装后可用Sequential API快速构建CNN模型，如MNIST分类示例，实

avatar 亚马逊云科技技术品牌专区

AI行业日报｜2026-09-25：5个热点事件

本期行业动态聚焦于端侧算力重构、自主智能体工作流、开发工具经济模型、网络隔离架构设计与公共部门合规审查。以下结合一线技术场景进行结构化梳理，供 AI 研究员与软件工程团队参考。 PrismML 正将其轻量级语言模型适配至搭载高通处理器的智能眼镜终端 1 。该平台的核心战略指向开源权重模型的本地化运行，旨在最大化挖掘穿戴设备现有算力的利用效率 1 。对于嵌入式与边缘计算开发者而言，此类微型模型向低功

avatar 亚马逊云科技技术品牌专区