MySQL 表操作完全指南：从创建到删除

1. 📚 引言

在 MySQL 的学习与面试中，对数据表的操作是重中之重。无论是创建、查看、修改还是删除表，都是日常开发与面试考察的核心技能。本文将基于实战案例，带你全面掌握 MySQL 表的操作，内容通俗易懂，代码完整可运行，助你轻松应对面试。

2. 🛠️ 创建表

2.1 📝 语法详解

创建表是使用数据库的第一步，其基本语法如下：

1. CREATE TABLE table_name (
2. field1 datatype,
3. field2 datatype,
4. field3 datatype
5. ) character set 字符集 collate 校验规则 engine 存储引擎;

复制代码

参数说明：

• field：表示列名（字段名）。
• datatype：表示列的数据类型（如 int、varchar、date 等）。
• character set：字符集。如果没有指定，则以所在数据库的默认字符集为准。
• collate：校验规则。如果没有指定，则以所在数据库的默认校验规则为准。
• engine：存储引擎（如 InnoDB、MyISAM）。

2.2 💡 创建表案例

下面我们创建一个 users 表，包含用户的基本信息：

1. create table users (
2. id int,
3. name varchar(20) comment '用户名',
4. password char(32) comment '密码是32位的md5值',
5. birthday date comment '生日'
6. ) character set utf8 engine MyISAM;

复制代码

💡 小贴士： comment 关键字用于添加字段注释，方便后期维护。

📂 存储引擎与文件结构：

不同的存储引擎，创建表时生成的文件也不同。以 MyISAM 引擎为例，在数据库目录下会生成三个文件：

• users.frm：表结构定义文件。
• users.MYD：表数据文件。
• users.MYI：表索引文件。

而 InnoDB 引擎则不同，它会在数据库目录下生成两个文件：

• users.frm：表结构定义文件。
• users.ibd：表数据和索引文件。

🔍 面试考点： 面试官常问 MyISAM 和 InnoDB 的区别，其中文件存储结构就是一个关键点。

3. 👀 查看表结构

创建好表之后，我们可以使用 desc 命令来查看表的结构：

1. desc 表名;

复制代码

示例：

1. mysql> desc users;
2. +----------+-------------+------+-----+---------+-------+
3. | Field | Type | Null | Key | Default | Extra |
4. +----------+-------------+------+-----+---------+-------+
5. | id | int(11) | YES | | NULL | |
6. | name | varchar(20) | YES | | NULL | |
7. | password | char(32) | YES | | NULL | |
8. | birthday | date | YES | | NULL | |
9. +----------+-------------+------+-----+---------+-------+

复制代码

💡 小贴士： desc 是 describe 的缩写，两者功能相同。

4. ✏️ 修改表（ALTER TABLE）

在实际项目开发中，经常需要修改表的结构，比如添加字段、修改字段类型、删除字段、重命名表等。这时就需要使用 ALTER TABLE 语句。

4.1 ➕ 添加字段

语法：

1. ALTER TABLE tablename ADD (column datatype [DEFAULT expr] [, column datatype] ...);

复制代码

案例： 在 users 表中添加一个 assets 字段，用于保存用户头像的图片路径，并将其放在 birthday 字段之后。

1. mysql> alter table users add assets varchar(100) comment '图片路径' after birthday;

复制代码

💡 小贴士： after birthday 表示将新字段插入到 birthday 字段之后。如果不指定位置，默认添加到最后一列。

验证结果：

1. mysql> desc users;
2. +----------+--------------+------+-----+---------+-------+
3. | Field | Type | Null | Key | Default | Extra |
4. +----------+--------------+------+-----+---------+-------+
5. | id | int(11) | YES | | NULL | |
6. | name | varchar(20) | YES | | NULL | |
7. | password | char(32) | YES | | NULL | |
8. | birthday | date | YES | | NULL | |
9. | assets | varchar(100) | YES | | NULL | |
10. +----------+--------------+------+-----+---------+-------+

复制代码

插入数据验证：

1. mysql> insert into users values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');

复制代码

查看数据：

1. mysql> select * from users;
2. +------+------+----------+------------+-------+
3. | id | name | password | birthday | assets |
4. +------+------+----------+------------+-------+
5. | 1 | a | b | 1982-01-04 | NULL |
6. | 2 | b | c | 1984-01-04 | NULL |
7. +------+------+----------+------------+-------+

复制代码

🔍 注意： 插入新字段后，对原来表中的数据没有影响，新字段的值默认为 NULL。

4.2 ✍️ 修改字段

语法：

1. ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr] [, column datatype] ...);

复制代码

案例： 将 name 字段的长度从 varchar(20) 修改为 varchar(60)。

1. mysql> alter table users modify name varchar(60);

复制代码

验证结果：

1. mysql> desc users;
2. +----------+--------------+------+-----+---------+-------+
3. | Field | Type | Null | Key | Default | Extra |
4. +----------+--------------+------+-----+---------+-------+
5. | id | int(11) | YES | | NULL | |
6. | name | varchar(60) | YES | | NULL | | <= 长度变成60
7. | password | char(32) | YES | | NULL | |
8. | birthday | date | YES | | NULL | |
9. | assets | varchar(100) | YES | | NULL | |
10. +----------+--------------+------+-----+---------+-------+

复制代码

4.3 ❌ 删除字段

语法：

1. ALTER TABLE tablename DROP (column);

复制代码

案例： 删除 password 列。

1. mysql> alter table users drop password;

复制代码

验证结果：

1. mysql> desc users;
2. +----------+--------------+------+-----+---------+-------+
3. | Field | Type | Null | Key | Default | Extra |
4. +----------+--------------+------+-----+---------+-------+
5. | id | int(11) | YES | | NULL | |
6. | name | varchar(60) | YES | | NULL | |
7. | birthday | date | YES | | NULL | |
8. | assets | varchar(100) | YES | | NULL | |
9. +----------+--------------+------+-----+---------+-------+

复制代码

⚠️ 警告： 删除字段一定要小心！删除字段及其对应的列数据都会永久丢失，无法恢复。

4.4 🔄 重命名表

语法：

1. ALTER TABLE 旧表名 RENAME [TO] 新表名;

复制代码

案例： 将 users 表重命名为 employee。

1. mysql> alter table users rename to employee;

复制代码

💡 小贴士： to 关键字可以省略。

验证结果：

1. mysql> select * from employee;
2. +------+------+------------+-------+
3. | id | name | birthday | assets |
4. +------+------+------------+-------+
5. | 1 | a | 1982-01-04 | NULL |
6. | 2 | b | 1984-01-04 | NULL |
7. +------+------+------------+-------+

复制代码

4.5 🔄 修改字段名

语法：

1. ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段类型;

复制代码

🔍 注意： 使用 CHANGE 修改字段名时，必须重新指定字段的完整定义（包括类型、约束等）。

案例： 将 name 列修改为 xingming，类型为 varchar(60)。

1. mysql> alter table employee change name xingming varchar(60);

复制代码

验证结果：

1. mysql> desc employee;
2. +----------+--------------+------+-----+---------+-------+
3. | Field | Type | Null | Key | Default | Extra |
4. +----------+--------------+------+-----+---------+-------+
5. | id | int(11) | YES | | NULL | |
6. | xingming | varchar(60) | YES | | NULL | |
7. | birthday | date | YES | | NULL | |
8. | assets | varchar(100) | YES | | NULL | |
9. +----------+--------------+------+-----+---------+-------+

复制代码

5. 🗑️ 删除表

语法：

1. DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

复制代码

示例： 删除 t1 表。

1. drop table t1;

复制代码

⚠️ 警告： 轻易不要修改和删除表！在生产环境中，删除表操作极其危险，务必三思而后行。

6. 📝 小总结

本文详细介绍了 MySQL 中关于表的核心操作，包括：

• 创建表：掌握 CREATE TABLE 语法，理解存储引擎对文件结构的影响。
• 查看表结构：使用 DESC 命令快速了解表定义。
• 修改表：灵活运用 ALTER TABLE 进行添加、修改、删除字段及重命名表等操作。
• 删除表：了解 DROP TABLE 的用法，并牢记其危险性。

这些操作是数据库面试中的高频考点，也是日常开发的基础。建议你亲手在本地 MySQL 环境中练习一遍，加深理解。

7. 🎯 经典面试题

1. 请简述 MyISAM 和 InnoDB 两种存储引擎在表文件存储上的区别？

参考答案：

• MyISAM 引擎的表会生成三个文件：.frm（表结构）、.MYD（数据）、.MYI（索引）。
• InnoDB 引擎的表会生成两个文件：.frm（表结构）、.ibd（数据和索引）。

2. 使用 ALTER TABLE 修改字段时，MODIFY 和 CHANGE 有什么区别？

参考答案：

• MODIFY 用于修改字段的数据类型或约束，但不能修改字段名。
• CHANGE 既可以修改字段名，也可以修改字段的数据类型和约束，但使用 CHANGE 时必须重新指定字段的完整定义。

3. 在 users 表中，如何在 birthday 字段之后添加一个 email 字段？

参考答案：

1. ALTER TABLE users ADD email varchar(100) AFTER birthday;

复制代码

4. 删除表中的某一列时，需要注意什么？

参考答案：
删除字段时，该字段及其对应的所有数据都会被永久删除，无法恢复。因此，在执行 ALTER TABLE ... DROP 操作前，务必确认该字段的数据不再需要，并建议先备份数据。

5. 如何安全地删除一个表？

参考答案：
使用 DROP TABLE IF EXISTS 表名; 语句。IF EXISTS 关键字可以防止因表不存在而报错。但在生产环境中，删除表前最好先确认是否有其他对象依赖该表，并做好数据备份。