我要评论前言：
在上一篇 mysql 基础入门中，我们了解了数据库的基本概念和简单操作。而在实际开发中，数据库和表的创建、修改、备份、删除等操作是日常高频需求，掌握这些精准操作能避免数据丢失、提升开发效率。本文将基于 mysql 实战场景，详细拆解库与表的完整操作流程，包括字符集选择、表结构设计、备份恢复等核心知识点，带你从 “会用” 进阶到 “活用” mysql。
一. 数据库（库）的核心操作
数据库是表的容器，合理的库操作是数据管理的基础。下面涵盖库的创建、查询、修改、删除、备份恢复等关键操作，同时详解字符集和校验规则的影响。
1.1 创建数据库：指定字符集与校验规则
创建数据库时，不仅要定义库名，还需根据业务场景指定字符集（如支持中文的utf8）和校验规则（如是否区分大小写），避免后续出现乱码或查询异常。
1.1.1 语法格式
create database [if not exists] db_name [default] character set charset_name [default] collate collation_name;
if not exists：避免重复创建数据库报错（可以不加但是这里推荐加）；character set：指定数据库字符集（默认utf8）；collate：指定字符集的校验规则（默认utf8_general_ci）。
1.1.2 实战案例
-- 1. 创建默认字符集的数据库db1 create database if not exists db1; -- 2. 创建指定utf8字符集的数据库db2 create database if not exists db2 character set utf8; -- 3. 创建指定字符集和校验规则的数据库db3 create database if not exists db3 character set utf8 collate utf8_general_ci;
1.2 字符集与校验规则：影响查询和排序
字符集决定了数据的存储编码（如是否支持中文），校验规则则影响字符串的比较和排序（如是否区分大小写），这是容易被忽略但关键的细节。
1.2.1 查看系统默认配置
-- 查看默认字符集 show variables like 'character_set_database'; -- 查看默认校验规则 show variables like 'collation_database';
1.2.2 查看支持的字符集和校验规则
-- 查看所有支持的字符集 show charset; -- 查看所有支持的校验规则 show collation;
1.2.3 校验规则的实际影响
以 “是否区分大小写” 为例，对比两种常用校验规则：
utf8_general_ci：不区分大小写（ci=case insensitive）；utf8_bin：区分大小写（bin=binary，按二进制比较）。
案例演示：
-- 1. 创建不区分大小写的数据库test1
create database test1 collate utf8_general_ci;
use test1;
create table person(name varchar(20));
insert into person values('a'),('a'),('b'),('b');
-- 查询name='a'：返回'a'和'a'（不区分大小写）
select * from person where name='a';
-- 排序：按字母顺序排序（不区分大小写）
select * from person order by name;
-- 2. 创建区分大小写的数据库test2
create database test2 collate utf8_bin;
use test2;
create table person(name varchar(20));
insert into person values('a'),('a'),('b'),('b');
-- 查询name='a'：仅返回'a'（区分大小写）
select * from person where name='a';
-- 排序：按二进制ascii码排序（大写在前，小写在后）
select * from person order by name;
1.3 操纵数据库：查询、修改、删除
1.3.1 查看所有数据库
show databases;
1.3.2 查看数据库创建语句
验证数据库的字符集、校验规则等配置：
show create database db3;
输出样例：
+----------+----------------------------------------------------------------+ | database | create database | +----------+----------------------------------------------------------------+ | db3 | create database `db3` /*!40100 default character set utf8 collate utf8_general_ci */ | +----------+----------------------------------------------------------------+
- 反引号 `：防止库名与关键字冲突；
/*!40100 ... */：条件执行，mysql 版本≥4.0.10 时生效。
1.3.3 修改数据库（仅字符集和校验规则）
数据库创建后，仅支持修改字符集和校验规则，不支持修改库名（需通过备份恢复间接修改）：
-- 将db3的字符集改为gbk alter database db3 character set gbk;
1.3.4 删除数据库（谨慎操作！）
删除数据库会级联删除所有表和数据，且无法恢复：
drop database if exists db3;
1.4 数据库备份与恢复：避免数据丢失
备份恢复是数据库运维的核心技能，支持全库备份、单表备份、多库备份。
1.4.1 备份（退出 mysql 客户端执行）
- 语法：
mysqldump -p端口 -u用户名 -p密码 -b 数据库名 > 备份文件路径
- 补充说明：
- 备份单表：
mysqldump -uroot -p 数据库名 表名1 表名2 > 备份文件路径； - 备份多库：
mysqldump -uroot -p -b 数据库名1 数据库名2 ... > 备份文件路径。
- 备份单表：
1.4.2 恢复（在 mysql 客户端执行）
-- 恢复整个数据库 source 备份文件路径;
注意：若备份时未加-b参数，恢复前需先创建空数据库并切换：
create database if not exists mytest; use mytest; source 备份文件路径;
1.5 查看数据库连接：排查并发问题
当数据库响应缓慢时，可查看当前连接情况，排查异常连接（如被入侵）：
show processlist;
输出样例：
+----+------+-----------+------+---------+------+-------+------------------+ | id | user | host | db | command | time | state | info | +----+------+-----------+------+---------+------+-------+------------------+ | 2 | root | localhost | test1| sleep | 120 | | null | | 3 | root | localhost | null | query | 0 | null | show processlist | +----+------+-----------+------+---------+------+-------+------------------+
command：连接状态（sleep为空闲，query为执行中）；time：连接持续时间（秒）；info：执行的 sql 语句。
二. 数据表（表）的核心操作
表是存储数据的核心载体，表结构的设计和修改直接影响业务开发，下面涵盖表的创建、查看、修改、删除全流程。
2.1 创建表：指定字段、类型、存储引擎
创建表时需明确字段名、数据类型、字符集、存储引擎等，同时可通过comment添加字段说明。
2.1.1 语法格式
create table table_name ( field1 datatype [comment '字段说明'], field2 datatype [comment '字段说明'], ... ) character set 字符集 collate 校验规则 engine 存储引擎;
2.1.2 实战案例
use mytest; create table users ( id int comment '用户id', name varchar(20) comment '用户名', password char(32) comment '密码是32位的md5加密值', birthday date comment '生日' ) character set utf8 engine myisam;
2.1.3 不同存储引擎的文件差异
mysql 支持插件式存储引擎，不同引擎的表文件存储格式不同：
- myisam（示例中使用）：
users.frm：表结构文件；users.myd：表数据文件；users.myi：表索引文件；
- innodb（默认引擎）：
users.frm：表结构文件；users.ibd：表数据 + 索引文件（聚簇索引结构）。
2.2 查看表结构：验证表设计
-- 简洁查看表结构 desc users; -- 详细查看表结构（含注释） show create table users;
2.3 修改表：适配业务需求变更
项目开发中，表结构需频繁适配业务变更（如添加字段、修改字段类型等），alter table是核心指令。
2.3.1 常用修改操作语法
| 操作类型 | 语法示例 |
|---|---|
| 添加字段 | alter table 表名 add 字段名 类型 [comment ‘说明’] [after 已有字段名] |
| 修改字段类型 | alter table 表名 modify 字段名 新类型 |
| 修改字段名 + 类型 | alter table 表名 change 旧字段名 新字段名 新类型 |
| 删除字段 | alter table 表名 drop 字段名 |
| 修改表名 | alter table 旧表名 rename to 新表名（to可省略） |
2.3.2 实战案例
use mytest; -- 1. 给users表添加字段assets（图片路径），放在birthday之后 alter table users add assets varchar(100) comment '图片路径' after birthday; -- 2. 修改name字段长度为60（适配更长的用户名） alter table users modify name varchar(60); -- 3. 删除password字段（假设密码存储方式变更） alter table users drop password; -- 4. 修改表名为employee alter table users rename employee; -- 5. 将name字段改为xingming（适配中文命名习惯） alter table employee change name xingming varchar(60);
2.3.3 注意事项
- 添加字段：新字段默认允许为
null，不会影响原有数据； - 修改字段类型：若字段已有数据，需确保新类型兼容旧数据（如
varchar转int可能失败）； - 删除字段：字段及对应数据会永久删除，需提前备份。
2.4 删除表（谨慎操作！）
删除表会删除表结构和所有数据，无法恢复：
drop table if exists employee;
temporary：仅删除临时表（create temporary table创建的表）：
drop temporary table if exists temp_table;
三. 总结与避坑指南
本文覆盖了 mysql 库与表的全流程操作，核心要点总结如下：
- 创建数据库时，建议明确指定
character set utf8和校验规则，避免乱码(也可以提前去自己配置好)； - 校验规则决定字符串比较逻辑，需根据业务场景选择（如用户名是否区分大小写）；
- 备份恢复是数据安全的保障，重要数据库需定期备份，备份时建议添加
-b参数； - 修改表结构时，删除字段和修改字段类型需格外谨慎，避免数据丢失；
- 存储引擎选择：innodb 支持事务和行级锁（默认推荐），myisam 查询速度快（适合只读场景）。
常见避坑点：
- 库名、表名、字段名避免使用 mysql 关键字（如
order、user），若必须使用需加反引号 `； - 备份时未加
-b参数，恢复前需手动创建数据库并切换； - 数据库不支持直接修改库名，需通过 “备份→删除旧库→恢复为新库名” 实现；
- 字段类型选择需合理（如密码用
char(32)存储 md5 值，生日用date类型），避免浪费空间或存储异常，关于类型问题我们后面还会进行更加详细的学习。
到此这篇关于mysql库与表的ddl核心操作实战案例的文章就介绍到这了,更多相关mysql库与表ddl操作内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论