MySQL 数据库 和 数据表 基础操作总结

一、前言

MySQL 是主流关系型数据库，日常开发、运维最基础的操作就是**数据库（库）和数据表（表）**的增删改查。本文分为两大模块：数据库操作、数据表操作，包含完整语法、场景案例、字符集/校验规则、备份恢复、存储引擎、易错点总结。

---

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

1.1 创建数据库

1.1.1 标准语法

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

语法解析

[IF NOT EXISTS]：可选，判断数据库不存在才创建，避免重复创建报错（生产必加）。CHARACTER SET：指定数据库字符集，决定数据库支持的文字（中文、英文、特殊符号等）。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，指定字符集为 utf8CREATE 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 两大核心校验规则对比（重点）

校验规则直接影响查询、排序结果，是高频易错点。

校验规则特点场景utf8_general_ci不区分大小写用户名、普通文本查询（最常用）utf8_bin二进制比较，区分大小写验证码、账号密码、敏感字符串

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 字符集修改为 gbkALTER 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：代表备份整个数据库，恢复时无需手动建库。

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：连接IDUser：登录用户Host：客户端地址db：当前操作的数据库Command：执行的操作（Sleep=空闲连接，Query=正在执行SQL）

用途：发现陌生IP、陌生用户连接，说明数据库可能被入侵；连接数过多会导致数据库卡顿。

---

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

数据表隶属于数据库，操作表前必须先进入对应数据库：

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_infoCREATE 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 = InnoDBDEFAULT CHARACTER SET utf8COMMENT = '系统用户信息表';

2.2 存储引擎简介（重点）

MySQL 主流两大存储引擎：InnoDB、MyISAM，创建表时通过 ENGINE 指定。

2.2.1 文件存储区别

数据表会在 MySQL 数据目录生成对应文件：

MyISAM（老版本默认）xxx.frm：表结构文件xxx.MYD：数据文件xxx.MYI：索引文件InnoDB（MySQL5.5+ 默认，生产首选）xxx.frm：表结构文件xxx.ibd：数据+索引合并文件

2.2.2 引擎对比 & 选型

存储引擎事务支持行锁/表锁外键适用场景InnoDB✅ 支持事务行锁✅ 支持外键电商、后台系统、需要事务的业务（生产首选）MyISAM❌ 不支持事务表锁❌ 不支持外键纯查询、静态数据、日志表（读写少）

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 avatar VARCHAR(100) COMMENT '头像文件路径' AFTER create_time;

2.4.2 修改字段类型/约束 MODIFY

只修改字段类型、长度、是否为空，不修改字段名。

-- 将 username 字段长度从30修改为50ALTER 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_userALTER 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 方式迁移。

---

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

功能SQL 指令创建库CREATE DATABASE 库名 CHARSET utf8;查看所有库SHOW DATABASES;进入库USE 库名;修改库字符集ALTER DATABASE 库名 CHARSET gbk;删除库DROP DATABASE IF EXISTS 库名;创建表CREATE TABLE 表名(...);查看表结构DESC 表名;新增字段ALTER TABLE 表名 ADD 字段 类型;删除字段ALTER TABLE 表名 DROP 字段;删除表DROP TABLE IF EXISTS 表名;查看连接SHOW PROCESSLIST;