【MySQL基础】（3）：MySQL库与表的操作

优质文章学习记录

06 Apr 2026 — 20 min read

前言

经过前面两篇系列文章的讲解，我相信大家已经成功安装了MySQL，并已经对数据库这个概念有了一个基本的认识。

本篇文章，我将会给大家演示一下MySQL中，关于数据库与数据表的相关操作，方便大家在后面的内容中熟练的运用这些操作完成学习。

库的操作

创建数据库

CREATEDATABASE[IFNOTEXISTS] database_name [CHARACTERSET charset_name][COLLATE collation_name];

所有方括号 [ ] 中的内容都是可选的，但强烈建议指定字符集！

CREATE DATABASE

固定关键字，用于创建一个新的数据库（在 MySQL 中也叫 schema）

IF NOT EXISTS（推荐加上！）

作用：如果数据库已存在，不报错，静默跳过

避免错误：

CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];

database_name

数据库名称，需符合以下规则：
- 不能是 MySQL 保留字（如 select, order 等）
- 可包含字母、数字、下划线 _、美元符 $ 、井号 #
- 建议：全小写 + 下划线（如 user_center_db）
- 长度一般不超过 64 字符

如果名称包含特殊字符或空格，需用反引号包裹：

 CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];

CHARACTER SET（字符集）

必须指定！ 否则可能使用默认的 latin1（不支持中文！）
推荐值：utf8mb4（不是 utf8！）
- utf8mb4 = 完整 UTF-8，支持 emoji（如 😂）、中文四字词（如 “𠮷”）
- MySQL 的 utf8 是残缺版（只支持 3 字节），已废弃

COLLATE（排序规则）

决定字符串如何比较和排序（大小写敏感？重音敏感？）
推荐值：utf8mb4_unicode_ci
- ci = case-insensitive（大小写不敏感）
- 比 utf8mb4_general_ci 更准确（尤其多语言场景）

创建数据库案例

这是直接创建的语句，如果此时已经存在test1数据库。那么此次创建就会报错。

如果不想报错，就可以在创建语句中加上IF NOT EXISTS语句：

这是最为常见的两种创建语句。

在上一篇文章中，我们知道，创建一个数据库，其实就是在对应路径下新建了一个目录。

那如果我们手动的在对应路径下创建一个目录，会出现什么情况呢？

在mysql的早期版本中，采用的默认存储引擎是 MyISAM，而 MyISAM 的数据库 = 文件系统中的一个目录。所以会出现在路径下手动创建一个目录，就相当于新建了一个数据库的情况，虽然现在的新版本已经避免，但是我们仍然要知道这些风险。

字符集和校验规则

字符集	最大字节数/字符	支持中文？	支持 Emoji？	是否推荐
`latin1`	1	❌	❌	❌（默认但危险）
`utf8`（MySQL 特有）	3	✅	❌（如 😊 存不了）	⚠️ 已废弃
`utf8mb4`	4	✅	✅	✅ 强烈推荐

实际影响

数据库的检验规则会影响我们对数据库的操作结果，最直观的例子就是查找。

比如我们先分别创建两种检验规则不同的数据库：

mysql>createdatabaseifnotexists test1 collate utf8mb4_unicode_ci ; Query OK,1row affected (0.01 sec) mysql>createdatabaseifnotexists test2 collate utf8mb4_bin ; Query OK,1row affected (0.03 sec) mysql>use test1;Database changed mysql>createtableifnotexists person (name varchar(20)); Query OK,0rows affected (0.57 sec) mysql>use test2;Database changed mysql>createtableifnotexists person (name varchar(20)); Query OK,0rows affected (0.20 sec) mysql>use test1; Reading table information for completion oftableandcolumn names You can turn off this feature to get a quicker startup with-A Database changed mysql>showtables;+-----------------+| Tables_in_test1 |+-----------------+| person |+-----------------+1rowinset(0.00 sec) mysql>insertinto person (name)values('a'); Query OK,1row affected (0.02 sec) mysql>insertinto person (name)values('A'); Query OK,1row affected (0.04 sec) mysql>insertinto person (name)values('b'); Query OK,1row affected (0.01 sec) mysql>insertinto person (name)values('B'); Query OK,1row affected (0.01 sec) mysql>insertinto person (name)values('c'); Query OK,1row affected (0.02 sec) mysql>insertinto person (name)values('C'); Query OK,1row affected (0.01 sec) mysql>select*from person;+------+| name |+------+| a || A || b || B || c || C |+------+6rowsinset(0.00 sec)

此时我们查找的时候进行一个筛选，只查找等于’a’的数据：

mysql>select*from person where name='a';+------+| name |+------+| a || A |+------+2rowsinset(0.00 sec)

你会发现打印的数据中是包含’A’的，这是因为我们的test1数据库的默认检验规则是utf8mb4_unicode_ci,它不对大小写敏感，所以不管是大A还是小a在它眼里都是一样的。

那我们再来试试test2数据库呢？

mysql>use test2; Reading table information for completion oftableandcolumn names You can turn off this feature to get a quicker startup with-A Database changed mysql>insertinto person (name)values('a'); Query OK,1row affected (0.04 sec) mysql>insertinto person (name)values('A'); Query OK,1row affected (0.02 sec) mysql>insertinto person (name)values('b'); Query OK,1row affected (0.01 sec) mysql>insertinto person (name)values('B'); Query OK,1row affected (0.02 sec) mysql>insertinto person (name)values('c'); Query OK,1row affected (0.01 sec) mysql>insertinto person (name)values('C'); Query OK,1row affected (0.02 sec) mysql>select*from person ->;+------+| name |+------+| a || A || b || B || c || C |+------+6rowsinset(0.00 sec) mysql>select*from person where name='a';+------+| name |+------+| a |+------+1rowinset(0.00 sec)

可以看到，在检验规则为utf8mb4_bin的数据库中，关于大小写的匹配与检索都是严格匹配的。

包括排序也是一样的：

mysql>select*from person orderby name;+------+| name |+------+| A || B || C || a || b || c |+------+6rowsinset(0.00 sec) mysql>use test1; Reading table information for completion oftableandcolumn names You can turn off this feature to get a quicker startup with-A Database changed mysql>select*from person orderby name;+------+| name |+------+| a || A || b || B || c || C |+------+6rowsinset(0.00 sec)

升序排序，可以看得出来test2是按照ASCII码大小写严格排序。

删除数据库

执行删除之后的结果：
数据库内部看不到对应的数据库，对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。

所以不要随意删除数据库

DROPDATABASE[IFEXISTS] db_name;

比如我们之前创建的 mytest 数据库，可以通过以下命令删除：

mysql>createdatabase mytest; Query OK,1row affected (0.02 sec) mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema || mysql || mytest || performance_schema || sys || test1 || test2 |+--------------------+7rowsinset(0.00 sec) mysql>dropdatabase mytest; Query OK,0rows affected (0.07 sec)

删除后，再次执行 SHOW DATABASES; 就不会再看到 mytest 了。同时，在 MySQL 的数据目录下（如 /var/lib/mysql/），名为 mytest 的文件夹也会被自动移除。这意味着该数据库中所有的表、数据、索引等信息都会被彻底清除，无法恢复。

因此，在执行 DROP DATABASE 之前，请务必确认你真的不再需要这个数据库中的任何内容。在生产环境中，建议先进行完整备份，再考虑删除操作。

备份和恢复

在实际使用中，为了避免误操作、硬件故障或系统崩溃导致数据丢失，我们需要对数据库进行定期备份。MySQL 提供了一个非常实用的命令行工具叫做 mysqldump，它可以将整个数据库（包括结构和数据）导出为一个纯文本的 SQL 脚本文件。这个文件不仅便于存储和传输，还可以在其他 MySQL 实例中直接还原。

# mysqldump -P3306 -u root -p密码 -B 数据库名 > 数据库备份存储的文件路径

例如，将 mytest 库备份到 D 盘：

# mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql

这时，可以打开看看 mytest.sql 文件里的内容，其实把我们整个创建数据库、建表、导入数据的语句都装载在这个文件中。也就是说，这个文件本质上就是一个完整的“重建脚本”，只要执行它，就能还原出一模一样的数据库环境。

当需要恢复数据时，可以在 MySQL 客户端中使用 source 命令：

mysql> source D:/mysql-5.7.22/mytest.sql;

执行后，数据库、表结构和数据都会被重新创建。整个过程就像重新运行了一遍当初建库建表的过程，非常方便。

除了备份整个数据库，mysqldump 还支持更灵活的备份方式。

如果备份的不是整个数据库，而是其中的一张表，可以这样操作：

# mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql

这样生成的 SQL 文件只包含指定表的结构和数据，适合用于局部迁移或调试。

同时备份多个数据库也很简单：

# mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

这里的关键是 -B 参数（全称 --databases），它会让 mysqldump 在输出中自动加入 CREATE DATABASE 和 USE 语句，使得恢复时无需手动创建数据库。

注意：如果备份一个数据库时，没有带上 -B 参数，那么生成的 .sql 文件里不会包含建库语句。在这种情况下，恢复数据库时，就需要我们先手动创建一个空数据库，然后切换到该库（USE db_name;），最后再用 source 命令导入数据。

查看连接情况

有时候我们会发现数据库响应变慢，或者担心有未知用户正在访问我们的服务，这时可以查看当前有哪些客户端连接到了 MySQL。

SHOW PROCESSLIST;

比如执行后可能看到如下结果：

mysql>show processlist;+----+-----------------+-----------+-------+---------+-------+------------------------+------------------+| Id |User| Host | db | Command |Time| State | Info |+----+-----------------+-----------+-------+---------+-------+------------------------+------------------+|5| event_scheduler | localhost |NULL| Daemon |84668| Waiting on empty queue |NULL||11| root | localhost | test1 | Query |0| init |show processlist |+----+-----------------+-----------+-------+---------+-------+------------------------+------------------+2rowsinset,1 warning (0.02 sec)

这个命令会列出当前所有活跃的连接会话，包括每个连接的用户、来源主机、当前使用的数据库、正在执行的命令、已执行时间等关键信息。

这个命令可以告诉我们当前有哪些用户连接到我们的 MySQL。如果查出某个用户不是你正常登录的，比如来自陌生 IP 地址，或者用户名看起来可疑，那很有可能你的数据库已经被他人非法访问了。以后大家发现自己数据库比较慢时，也可以用这个指令来查看是否有大量闲置或异常连接占用了资源，从而帮助我们快速定位性能瓶颈或安全风险。

表的操作

表的创建

在数据库中，表（Table）是真正存储数据的地方。一个数据库可以包含多个表，每个表由若干列（字段）组成，每一行代表一条记录。
基本语法：

CREATETABLE table_name ( field1 datatype [约束], field2 datatype [约束], field3 datatype [约束],...)CHARACTERSET 字符集 COLLATE 校验规则 ENGINE= 存储引擎;

所有方括号 [ ] 中的内容都是可选的，但为了程序的健壮性和可维护性，强烈建议显式指定字符集、校验规则和存储引擎。

CREATE TABLE table_name

固定关键字，用于创建一张新表。
table_name 是表的名称，命名规则与数据库名类似：
- 不能是 MySQL 保留字（如 order, group, select 等）
- 可包含字母、数字、下划线 _、美元符 $ 、井号 #
- 建议：全小写 + 下划线（如 user_info, order_detail）
- 长度一般不超过 64 个字符

如果表名包含特殊字符或空格，需用反引号包裹：

CREATETABLE table_name ( field1 datatype [约束], field2 datatype [约束], field3 datatype [约束],...)CHARACTERSET 字符集 COLLATE 校验规则 ENGINE= 存储引擎;

字段定义（field datatype [约束]）

每一列都需要指定：

字段名（如 id, name, age）
数据类型（如 INT, VARCHAR(50), DATETIME）
可选约束（如 NOT NULL, PRIMARY KEY, DEFAULT 'xxx'）

常见数据类型示例：

类型	说明
`INT`	整数（通常用于 ID）
`VARCHAR(N)`	可变长度字符串，N 最大为 65535（实际受行大小限制）
`CHAR(N)`	定长字符串（适合固定长度，如手机号、身份证后四位）
`TEXT`	长文本（不支持默认值）
`DATETIME`	日期时间（格式：`YYYY-MM-DD HH:MM:SS`）
`TIMESTAMP`	时间戳（自动更新特性，范围较小）

初学者建议优先掌握 INT、VARCHAR、DATETIME 这三种类型。

CHARACTER SET 和 COLLATE

这两个选项用于指定该表的字符编码和排序规则，作用与创建数据库时类似：

推荐字符集：utf8mb4（支持中文、Emoji、生僻字）
推荐校验规则：utf8mb4_unicode_ci（大小写不敏感，多语言友好）

如果不指定，表会继承所在数据库的字符集和校验规则。但为了确保一致性，建议显式写出。

ENGINE = 存储引擎

MySQL 支持多种存储引擎，最常用的是：

引擎	特点	是否推荐
`InnoDB`	支持事务、行级锁、外键、崩溃恢复	✅ 默认且强烈推荐
`MyISAM`	不支持事务，但全文索引快（旧版常用）	❌ 已逐步淘汰
`MEMORY`	数据存内存，速度快但重启丢失	⚠️ 仅用于临时表

自 MySQL 5.5 起，默认存储引擎就是 InnoDB。即便如此，显式写出 ENGINE=InnoDB 仍是良好习惯。

创建表案例

mysql>createtable users (-> id int,-> name varchar(20)comment'用户名',-> password char(32)comment'密码是32位的md5值',-> birthday datecomment'生日'->)characterset utf8mb4 collate utf8mb4_unicode_ci engineInnoDB; Query OK,0rows affected (0.24 sec)

除此之外，我们还可以使用desc命令来查看一个表的结构：

mysql>desc users;+----------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+----------+-------------+------+-----+---------+-------+| id |int| YES ||NULL||| name |varchar(20)| YES ||NULL||| password |char(32)| YES ||NULL||| birthday |date| YES ||NULL||+----------+-------------+------+-----+---------+-------+4rowsinset(0.06 sec)

修改表结构

在实际项目开发中，数据库表的结构调整是常见操作。MySQL 提供了灵活的 ALTER TABLE 语句来满足这些需求，包括但不限于添加字段、修改字段类型或长度、删除字段、重命名字段或表等。

添加字段

要向现有的表中添加一个或多个字段，可以使用 ALTER TABLE ... ADD 语法。例如，在 users 表中添加一个名为 assets 的字段用于保存图片路径：

ALTERTABLE users ADD assets VARCHAR(100)COMMENT'图片路径'AFTER birthday;

这条命令会在 birthday 字段之后添加一个新的 assets 字段，其数据类型为可变长字符串（最大长度100），并附带注释说明该字段用途。

mysql>desc users;+----------+-------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+----------+-------------+------+-----+---------+-------+| id |int| YES ||NULL||| name |varchar(20)| YES ||NULL||| password |char(32)| YES ||NULL||| birthday |date| YES ||NULL||+----------+-------------+------+-----+---------+-------+4rowsinset(0.06 sec) mysql>altertable users add assets varchar(100)comment'图片路径'after birthday; Query OK,0rows affected (0.49 sec) Records: 0 Duplicates: 0Warnings: 0 mysql>desc users;+----------+--------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+----------+--------------+------+-----+---------+-------+| id |int| YES ||NULL||| name |varchar(20)| YES ||NULL||| password |char(32)| YES ||NULL||| birthday |date| YES ||NULL||| assets |varchar(100)| YES ||NULL||+----------+--------------+------+-----+---------+-------+5rowsinset(0.00 sec)

插入新记录

在进行表结构调整之前或之后，可能需要插入新的记录。可以通过 INSERT INTO 语句完成：

INSERTINTO users (id, name, password, birthday)VALUES(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');

修改字段属性

如果需要调整现有字段的属性，如改变字段长度或者数据类型，可以使用 ALTER TABLE ... MODIFY 语法。比如，将 name 字段的数据类型改为 VARCHAR(60)：

ALTERTABLE users MODIFY name VARCHAR(60);

删除字段

删除不需要的字段时，可以使用 ALTER TABLE ... DROP COLUMN 语法。注意，一旦执行删除操作，该字段及其所有数据都将永久丢失。例如，从 users 表中删除 password 字段：

ALTERTABLE users DROPCOLUMN password;

重命名字段

若需更改字段名称但保留其属性，可以使用 ALTER TABLE ... CHANGE 语法。这要求重新定义字段的所有属性，即使保持不变也需要明确指定。例如，将 name 字段重命名为 xingming：

ALTERTABLE users CHANGE name xingming VARCHAR(60);

重命名表

最后，当需要对整个表进行重命名时，可以使用 ALTER TABLE ... RENAME TO 语法。例如，将 users 表重命名为 employee：

ALTERTABLE users RENAMETO employee;

通过上述示例，可以看到 MySQL 提供了强大的工具集来管理数据库表结构的变化。无论是添加新字段还是调整现有字段，或是彻底改变表名，都可以高效地完成。然而，在执行这些操作前，请务必确保已充分考虑其影响，并在必要时备份相关数据以防不测。(以上的这些指令都是不区分大小写的，你完全可以直接写成alter table xxx)

虽然 ALTER TABLE ... RENAME TO 和 ALTER TABLE ... CHANGE 可以轻松修改表名或字段名，但在已有项目上线后，这类操作往往隐藏着巨大风险。

一旦重命名，所有依赖原名称的代码都会失效！

比如：

后端代码中的 SQL 语句（如 SELECT name FROM users;）
ORM 框架映射的实体类属性（如 Java 的 User.name）
前端接口返回的字段名
数据报表、BI 工具、ETL 脚本等外部系统

只要有一处未同步更新，就会导致程序报错、数据异常甚至服务中断。

此外，如果表或字段被其他表通过外键引用，重命名还可能破坏关联关系，引发更复杂的连锁问题。

因此，在真实项目中，我们通常遵循以下原则：

上线后尽量避免重命名
如果字段含义变了，优先考虑新增字段 + 废弃旧字段，而不是直接改名。
必须重命名时，务必走完整流程
- 在测试环境充分验证
- 更新所有关联代码（前后端、脚本、配置）
- 制定回滚方案（如备份原表结构）
- 选择业务低峰期执行
使用别名过渡（推荐）
比如保留原字段 name，同时添加新字段 xingming，并在应用层逐步切换。待确认无误后，再下个版本删除旧字段。

在建表初期就采用清晰、稳定的命名规范（如 user_id, created_at），能极大减少后期重命名的需求。

总之，RENAME 看似简单，实则牵一发而动全身。“能不改就不改，能新增就不替换” 是数据库变更的黄金准则。

结语：

通过本篇文章，我们系统地学习了 MySQL 中数据库与数据表的核心操作：从创建、查看、修改到删除，再到备份恢复与连接监控，每一步都是后续开发和运维工作的基石。

需要特别强调的是：
数据库不是“随便玩玩”的地方。
无论是 DROP DATABASE、ALTER TABLE DROP COLUMN，还是重命名字段或表，这些操作一旦执行，往往不可逆，且可能对整个系统造成连锁影响。因此，在真实项目中，务必做到：

先备份，再操作
先测试，再上线
能新增，不修改；能保留，不删除

良好的命名习惯、清晰的注释、规范的字符集设置，看似是“小事”，却能在未来节省大量排查和重构的时间。

希望你不仅能记住这些命令，更能理解它们背后的安全意识与工程思维。接下来的文章中，我们将正式进入数据的增删改查（CRUD），真正开始与数据打交道。请继续保持谨慎与好奇，一起深入 MySQL 的世界！