前言
MySQL 作为当今最主流的关系型数据库之一，在日常开发和系统运维场景中，最基础也最频繁的操作便是数据库与数据表的增删改查。本文将从两大核心板块进行系统梳理：数据库操作与数据表操作。内容涵盖完整语法、实际案例、字符集与校验规则的深入剖析、备份恢复的关键要点、存储引擎的选择策略，以及那些需要特别留意的细节。本文偏向干货总结，适合对照练习或作为日常参考手册使用。

第一部分：MySQL 数据库（库）操作

1.1 创建数据库

1.1.1 标准语法

CREATE DATABASE [IF NOT EXISTS] 数据库名
    [DEFAULT CHARACTER SET 字符集]
    [DEFAULT COLLATE 校验规则];

语法解析

[IF NOT EXISTS] 为可选参数，强烈建议在生产环境中使用。其作用是：仅在数据库不存在时执行创建，否则跳过，从而避免重复执行脚本时因错误而中断运行。
CHARACTER SET 用于设定字符集，决定了数据库所能存储的文字类型——包括中文、英文、特殊符号乃至 emoji 表情符号。
COLLATE 用于指定校验规则，控制字符串比较与排序时是否区分大小写、是否按二进制进行对比。
方括号 [] 表示可选参数，建议将 MySQL 关键字统一大写，以提升代码的可读性。

1.1.2 实战案例（经典示例）

案例1：最简创建——使用系统默认配置

-- 创建数据库 test_db，使用MySQL默认字符集、校验规则
CREATE DATABASE test_db;

MySQL 5.7 默认字符集为 utf8；MySQL 8.0 默认字符集为 utf8mb4（推荐生产环境使用，支持 emoji）。

案例2：指定字符集创建数据库

-- 创建数据库 db_utf8，指定字符集为 utf8
CREATE DATABASE db_utf8 DEFAULT CHARACTER SET utf8;

案例3：同时指定字符集 + 校验规则

-- 字符集utf8，校验规则utf8_general_ci（不区分大小写）
CREATE DATABASE db_ci DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 字符集utf8，校验规则utf8_bin（区分大小写）
CREATE DATABASE db_bin DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

1.2 字符集 & 校验规则 深度解析

1.2.1 常用查询指令

-- 查看当前数据库默认字符集
SHOW VARIABLES LIKE 'character_set_database';

-- 查看当前数据库默认校验规则
SHOW VARIABLES LIKE 'collation_database';

-- 查看MySQL所有支持的字符集
SHOW CHARSET;

-- 查看MySQL所有支持的校验规则
SHOW COLLATION;

1.2.2 两大核心校验规则对比（重点）

校验规则直接影响查询与排序的结果，这是开发过程中极易引发问题的环节。下表对比了最常用的两种规则：

1.2.3 案例演示：大小写区分效果

步骤1：分别创建两个不同校验规则的库

-- 库1：不区分大小写
CREATE DATABASE db_ci COLLATE utf8_general_ci;
USE db_ci;
CREATE TABLE user(name VARCHAR(20));
INSERT INTO user VALUES ('a'),('A'),('b'),('B');

-- 库2：区分大小写
CREATE DATABASE db_bin COLLATE utf8_bin;
USE db_bin;
CREATE TABLE user(name VARCHAR(20));
INSERT INTO user VALUES ('a'),('A'),('b'),('B');

步骤2：查询测试

-- 切换到不区分大小写库，查询 name='a'
USE db_ci;
SELECT * FROM user WHERE name = 'a';
-- 结果：同时查出 a 和 A

-- 切换到区分大小写库，查询 name='a'
USE db_bin;
SELECT * FROM user WHERE name = 'a';
-- 结果：仅查出小写 a

步骤3：排序测试

-- 不区分大小写排序：a、A、b、B 混排
USE db_ci;
SELECT * FROM user ORDER BY name;

-- 区分大小写排序：先大写字母，后小写字母
USE db_bin;
SELECT * FROM user ORDER BY name;

? 易错点：团队协作或项目迁移时，如果库和表的校验规则不统一，查询结果容易出现意想不到的差异。因此，统一规范非常重要。

1.3 查看数据库

1.3.1 查看所有数据库

SHOW DATABASES;

1.3.2 查看数据库创建语句（查看字符集、校验规则）

SHOW CREATE DATABASE 数据库名;
-- 示例
SHOW CREATE DATABASE db_utf8;

结果解析

数据库名外层会带有反引号 `，用于区分名称与 MySQL 关键字。如果库名本身是关键字，反引号必不可少。
结果中可能出现 /*!40100 ... */ 形式的注释——这并非普通注释，它表示当 MySQL 版本大于 4.01 时才会执行内部的语句，属于版本兼容的写法。

1.4 修改数据库

只能修改字符集和校验规则，无法直接修改数据库名称（低版本 MySQL 不支持重命名操作）。

1.4.1 语法

ALTER DATABASE 数据库名
    [DEFAULT CHARACTER SET 新字符集]
    [DEFAULT COLLATE 新校验规则];

1.4.2 实战案例

-- 将 db_utf8 字符集修改为 gbk
ALTER DATABASE db_utf8 DEFAULT CHARACTER SET gbk;

-- 同时修改字符集+校验规则
ALTER DATABASE db_ci DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

? 补充知识点：修改数据库的字符集不会自动更新已有数据表的字符集，已有表需要单独处理。

1.5 删除数据库

1.5.1 语法

DROP DATABASE [IF EXISTS] 数据库名;

IF EXISTS 的作用是：数据库存在才执行删除，不存在则忽略，从而避免报错。

1.5.2 案例 & 风险提示

-- 安全删除数据库（推荐）
DROP DATABASE IF EXISTS test_db;

⚠️ 高危操作：删除数据库会级联删除库内所有数据表、数据及索引，且不可恢复（除非有备份）。生产环境中严禁随意执行 DROP DATABASE，删除前务必先完成备份。

1.6 数据库备份与恢复（运维必备）

备份恢复使用的是 mysqldump 工具，需注意此命令是在系统命令行中执行，而非 MySQL 客户端内部。

1.6.1 整库备份

语法

mysqldump -u用户名 -p密码 -B 数据库名 > 备份文件路径.sql

示例

# 备份 db_utf8 库到 D 盘
mysqldump -uroot -p123456 -B db_utf8 > D:/mysql_backup/db_utf8.sql

这里的 -B 参数表示备份整个数据库，恢复时无需手动建库，直接使用 source 命令即可。

1.6.2 单表/多表备份

# 仅备份库中特定几张表（不加 -B）
mysqldump -uroot -p123456 数据库名 表名1 表名2 > 备份路径.sql

1.6.3 多库同时备份

# 一次性备份多个数据库
mysqldump -uroot -p123456 -B 库1 库2 库3 > D:/all_db.sql

1.6.4 数据恢复（MySQL客户端内执行）

-- 导入sql文件完成恢复
SOURCE D:/mysql_backup/db_utf8.sql;

1.6.5 备份易错点总结

• 若不使用 -B 参数进行备份，恢复前需手动创建一个空数据库，并进入该库后再执行 source。
• 路径中避免包含中文或空格，否则恢复操作可能失败。
• 对于大型数据库，建议分表备份，防止单个文件过大导致操作不便。

1.7 查看MySQL连接状态

排查数据库卡顿或发现异常连接时，该指令非常实用：

SHOW PROCESSLIST;

字段说明

• Id：连接ID
• User：登录用户
• Host：客户端地址
• db：当前操作的数据库
• Command：执行的操作（Sleep 表示空闲连接，Query 表示正在执行SQL）

用途：若发现陌生 IP 或陌生用户连接，可能意味着数据库被入侵；若连接数过多，数据库容易产生卡顿。

第二部分：MySQL 数据表（表）操作

数据表归属于数据库，操作表之前需先用 USE 命令进入相应数据库：

USE 数据库名;

2.1 创建数据表

2.1.1 标准语法

CREATE TABLE 表名 (
    字段名1 数据类型 [字段约束/注释],
    字段名2 数据类型 [字段约束/注释],
    ...
)
[DEFAULT CHARACTER SET 字符集]
[COLLATE 校验规则]
[ENGINE 存储引擎]
[COMMENT '表注释'];

语法解析

• 每个字段由“列名 + 数据类型”构成，数据类型为必填项。
• COMMENT 用于添加字段或表的注释，开发规范中建议必加，可大幅降低后期维护成本。
• 字符集/校验规则若未指定，将继承所在数据库的配置。
• ENGINE 用于指定存储引擎，这是 MySQL 的核心特性之一，下文将详细说明。

2.1.2 经典实战案例

创建一个用户信息表，包含常用字段、注释，并指定存储引擎：

-- 先进入数据库
USE db_utf8;

-- 创建用户表 user_info
CREATE TABLE user_info (
    id INT COMMENT '用户ID，整型',
    username VARCHAR(30) COMMENT '用户名',
    password CHAR(32) COMMENT '密码，MD5加密后32位',
    age TINYINT COMMENT '年龄',
    create_time DATE COMMENT '账号创建日期'
) ENGINE = InnoDB
  DEFAULT CHARACTER SET utf8
  COMMENT = '系统用户信息表';

2.2 存储引擎简介（重点）

MySQL 中最主流的两个存储引擎是 InnoDB 和 MyISAM，建表时通过 ENGINE 指定。

2.2.1 文件存储区别

每个数据表在 MySQL 的数据目录下都会生成对应文件：

• MyISAM（旧版本默认）：
  • xxx.frm：表结构文件
  • xxx.MYD：数据文件
  • xxx.MYI：索引文件
• InnoDB（MySQL 5.5+ 默认，生产首选）：
  • xxx.frm：表结构文件
  • xxx.ibd：数据和索引合并存储于同一文件中

2.2.2 引擎对比 & 选型

2.3 查看表结构

2.3.1 精简查看（常用）

DESC 表名;
-- 等价写法
DESCRIBE 表名;

2.3.2 字段含义说明

执行 DESC user_info 后会展示以下字段信息：

• Field：字段名
• Type：数据类型
• Null：是否允许为空（YES/NO）
• Key：索引类型
• Default：默认值
• Extra：额外属性（自增、备注等）

2.3.3 查看完整建表语句

SHOW CREATE TABLE 表名;

2.4 修改数据表结构（高频操作）

实际开发中经常需要新增、修改或删除字段，以及重命名表或字段，这些操作均通过 ALTER TABLE 完成。

2.4.1 新增字段 ADD

语法：
ALTER TABLE 表名 ADD 字段名 数据类型 [注释] [位置];
位置可通过 AFTER 已有字段 指定，若不指定则默认添加至末尾。

-- 给 user_info 新增头像路径字段，放在 create_time 后面
ALTER TABLE user_info ADD a vatar VARCHAR(100) COMMENT '头像文件路径' AFTER create_time;

2.4.2 修改字段类型/约束 MODIFY

仅修改字段的类型、长度、是否为空等属性，不改字段名。

-- 将 username 字段长度从30修改为50
ALTER TABLE user_info MODIFY username VARCHAR(50) COMMENT '用户名';

⚠️ 注意：MODIFY 会完全覆盖字段的旧定义（包括 NULL/NOT NULL、DEFAULT、COMMENT 等），新语句中必须重新声明所有需要的属性。

2.4.3 修改字段名 + 类型 CHANGE

必须同时提供新字段名和完整数据类型，即便类型不变也要重复声明。

-- 将 username 改名为 nick_name，类型保持不变
ALTER TABLE user_info CHANGE username nick_name VARCHAR(50) COMMENT '昵称';

2.4.4 删除字段 DROP

⚠️ 高危操作：删除字段会清空该列的所有数据，执行前务必确认。

-- 删除 age 字段
ALTER TABLE user_info DROP COLUMN age;

2.4.5 修改表名 RENAME

-- 将 user_info 重命名为 sys_user
ALTER TABLE user_info RENAME TO sys_user;
-- TO 可以省略，等价写法
ALTER TABLE sys_user RENAME user_info;
RENAME TABLE user_info TO sys_user;

2.5 删除数据表

2.5.1 语法

-- 安全删除（推荐，表不存在不报错）
DROP TABLE [IF EXISTS] 表名1, 表名2;

-- 直接删除
DROP TABLE 表名;

2.5.2 示例 & 注意事项

-- 单表删除
DROP TABLE IF EXISTS user_info;

-- 一次性删除多张表
DROP TABLE IF EXISTS table_a, table_b;

⚠️ 易错点：删除表会清空表结构及所有数据，执行前务必确认并做好备份。

三、整体易错点 & 开发规范总结

3.1 数据库操作规范

• 建库时统一加上 IF NOT EXISTS，删库时统一加上 IF EXISTS，避免批量脚本因报错而中断。
• 生产环境优先选用 utf8mb4 字符集（支持 emoji 及全量中文）。
• 校验规则需保持一致：通用业务使用 utf8_general_ci，密码/验证码等敏感字段使用 utf8_bin。
• 禁止在线上直接执行 DROP DATABASE，删除前必须完成备份。

3.2 数据表操作规范

• 所有字段和表必须添加 COMMENT 注释，以便后期维护。
• 生产环境中的表默认使用 InnoDB 存储引擎。
• 修改或删除字段前，先在测试环境验证，防止线上数据丢失。
• 操作表之前务必执行 USE 数据库名;，避免误操作其他库。

3.3 备份恢复规范

• 重要数据库应定时自动备份，并保留多份历史文件。
• 备份文件名应包含时间戳，例如 db_20260610.sql。
• 跨版本迁移数据库时，优先采用 mysqldump 方式。

四、附录：常用指令速查表