个人主页Cx330❄️个人专栏《C语言》《LeetCode刷题集》《数据结构-初阶》《C知识分享》《优选算法指南-必刷经典100题》《Linux操作系统》:从入门到入魔《Git深度解析》:版本管理实战全解 《Qt 极境架构》心向往之行必能Cx330的简介目录前言一、 数据库Database操作深度解析1.1 创建数据库 实战案例1.2 校验规则的“深水区”区分大小写的影响实验对比1.3 数据库日常维护指令1.3.1 查看所有数据库1.3.2 查看数据库创建语句1.3.3 修改数据库仅字符集和校验规则1.3.4 删除数据库谨慎操作1.4 数据库备份与还原核心运维技能1.4.1 备份整个数据库1.4.2 还原数据库1.4.3 进阶备份场景1.5 数据库监控查看连接情况二、 数据表Table操作全攻关2.1 创建数据表️ 实战演练 底层探秘不同的存储引擎物理文件大不相同2.2 查看表结构2.3 动态修改表结构DDL 操作1. 添加新字段ADD2. 修改字段属性MODIFY3. 删除字段DROP4. 修改表名RENAME5. 修改字段名与定义CHANGE2.4 删除数据表 结语前言作为 C 开发者我们在编写高性能后端服务时不可避免地要与底层存储打交道。MySQL 作为主流的关系型数据库其基础的“库操作”与“表操作”是每一位程序员的看家本领。今天博主将结合底层原理带大家一次性吃透 MySQL 库与表的核心操纵技巧。一、 数据库Database操作深度解析数据库是表的载体。理解数据库的字符集与校验规则是避免中文乱码和检索异常的关键。1.1 创建数据库创建数据库的标准语法如下CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification]...];其中create_specification可以指定字符集CHARACTER SET和校验规则COLLATE。 实战案例默认创建系统默认采用utf8字符集校验规则为utf8_general_ciCREATE DATABASE db1;指定utf8字符集CREATE DATABASE db2 CHARSETutf8;同时指定字符集与校验规则CREATE DATABASE db3 CHARSETutf8 COLLATE utf8_general_ci;1.2 校验规则的“深水区”区分大小写的影响字符集决定了存储哪些语言而校验规则Collation决定了如何对字符进行比较和排序。MySQL 中常见的两种utf8校验规则utf8_general_ci不区分大小写Case-Insensitive。utf8_bin区分大小写Binary按二进制比对。实验对比数据库校验规则插入数据查询where namea的结果order by name排序结果test1utf8_general_cia,A,b,B返回a和A(共2行)a - A - b - Btest2utf8_bina,A,b,B仅返回a(共1行)A - B - a - b(大写在前)博主敲黑板在设计用户登录、唯一标识等字段时一定要根据业务逻辑选对校验规则。如果需要密码或账号严格区分大小写请务必使用_bin结尾的二进制校验规则1.3 数据库日常维护指令查看系统默认字符集与校验规则SHOW VARIABLES LIKE character_set_database; SHOW VARIABLES LIKE collation_database;查看系统支持的所有字符集及校验规则SHOW CHARSET; SHOW COLLATION;1.3.1 查看所有数据库SHOW DATABASES;1.3.2 查看数据库创建语句验证数据库的字符集、校验规则等配置SHOW CREATE DATABASE mytest;注返回的语句中数据库名会被加上反引号 这是为了防止数据库名与系统保留关键字冲突同时形如/*!40100 DEFAULT ... */的代码并不是普通的注释而是条件注释表示当 MySQL 版本大于 4.01 时将执行该语句。1.3.3 修改数据库仅字符集和校验规则数据库创建后仅支持修改字符集和校验规则不支持修改库名需通过备份恢复间接修改ALTER DATABASE mytest CHARSETgbk;1.3.4 删除数据库谨慎操作删除数据库会级联删除所有表和数据且无法恢复DROP DATABASE [IF EXISTS] db_name;⚠️极其重要提示数据库删除是级联删除里面的所有数据表和数据都会被一并抹除。千万不要在生产环境下轻易尝试1.4 数据库备份与还原核心运维技能在退出 MySQL 连接的终端下我们可以使用mysqldump进行备份。1.4.1 备份整个数据库# 注意 -B 参数会带上创建数据库的语句 mysqldump -P3306 -u root -p密码 -B 数据库名 备份存储的路径.sql # 示例 mysqldump -P3306 -u root -p123456 -B mytest D:/mytest.sql1.4.2 还原数据库进入 MySQL 客户端后执行source指令mysql SOURCE D:/mytest.sql;1.4.3 进阶备份场景仅备份数据库中的特定表mysqldump -u root -p 数据库名 表名1 表名2 D:/mytest.sql同时备份多个数据库mysqldump -u root -p -B 数据库名1 数据库名2... D:/multi_db.sql避坑指南如果备份时没有带-B参数那么在还原时必须先在目标 MySQL 中手动CREATE DATABASE创建空库然后使用USE 数据库名切换进去最后才能执行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 | -------------------------------------------------------------------该命令可以实时显示当前有哪些用户连接到了 MySQL以及他们正在执行的命令、所处的状态和消耗的时间。二、 数据表Table操作全攻关学完了库我们来啃最硬的骨头——表结构的操作。2.1 创建数据表标准语法CREATE TABLE table_name ( field1 datatype, field2 datatype, ... ) CHARACTER SET 字符集 COLLATE 校验规则 ENGINE 存储引擎;️ 实战演练CREATE TABLE users ( id INT, name VARCHAR(20) COMMENT 用户名, password CHAR(32) COMMENT 密码是32位的md5值, birthday DATE COMMENT 生日 ) CHARACTER SET utf8 ENGINE MyISAM; 底层探秘不同的存储引擎物理文件大不相同当我们创建了上面的user1表引擎为MyISAM以及一个InnoDB引擎的表时切换到 MySQL 的数据目录下你会发现MyISAM 引擎的表会生成三个文件user1.frm表结构定义user1.MYD表数据Datauser1.MYI表索引IndexInnoDB 引擎的表会生成两个文件user2.frm表结构定义user2.ibd表数据与索引合并存储的文件2.2 查看表结构使用desc或者是describe命令可以快速查看表的字段属性DESC users;输出字段包括Field(字段名)、Type(类型)、Null(是否允许为空)、Key(索引类型)、Default(默认值)、Extra(扩充属性)。2.3 动态修改表结构DDL 操作在实际项目迭代中需求的变更往往伴随着表结构的调整。以下是高频使用的ALTER TABLE命令1. 添加新字段ADD在birthday字段后面添加一个名为image_path的图片路径字段ALTER TABLE users ADD image_path VARCHAR(100) COMMENT 图片路径 AFTER birthday;注意插入新字段后原来表中已有的历史数据的该字段值会自动填充为NULL。2. 修改字段属性MODIFY将name字段的长度从 20 扩展到 60ALTER TABLE users MODIFY name VARCHAR(60);3. 删除字段DROP⚠️注意删除字段会同时将该列对应的所有历史数据删除请谨慎操作ALTER TABLE users DROP password;4. 修改表名RENAME将users表改名为xingmingTO关键字可以省略ALTER TABLE users RENAME TO employee;5. 修改字段名与定义CHANGE如果要将name列的名字修改为xingming需要写出完整的字段新定义ALTER TABLE employee CHANGE name xingming VARCHAR(60);2.4 删除数据表DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name;示例DROP TABLE IF EXISTS t1; 结语掌握了 MySQL 库与表的基本功我们就拿到了通往高并发、分布式后端开发的第一张入场券。规范的 SQL 编写习惯与合理的引擎、字符集选择能够帮助我们在项目初期避开 90% 的底层性能和乱码深坑。如果你觉得这篇博文对你有帮助别忘了三连点赞收藏关注支持一下博主我们下期再见
【MySQL基础】库与表的全面操纵指南
发布时间：2026/6/13 19:17:49
个人主页Cx330❄️个人专栏《C语言》《LeetCode刷题集》《数据结构-初阶》《C知识分享》《优选算法指南-必刷经典100题》《Linux操作系统》:从入门到入魔《Git深度解析》:版本管理实战全解 《Qt 极境架构》心向往之行必能Cx330的简介目录前言一、 数据库Database操作深度解析1.1 创建数据库 实战案例1.2 校验规则的“深水区”区分大小写的影响实验对比1.3 数据库日常维护指令1.3.1 查看所有数据库1.3.2 查看数据库创建语句1.3.3 修改数据库仅字符集和校验规则1.3.4 删除数据库谨慎操作1.4 数据库备份与还原核心运维技能1.4.1 备份整个数据库1.4.2 还原数据库1.4.3 进阶备份场景1.5 数据库监控查看连接情况二、 数据表Table操作全攻关2.1 创建数据表️ 实战演练 底层探秘不同的存储引擎物理文件大不相同2.2 查看表结构2.3 动态修改表结构DDL 操作1. 添加新字段ADD2. 修改字段属性MODIFY3. 删除字段DROP4. 修改表名RENAME5. 修改字段名与定义CHANGE2.4 删除数据表 结语前言作为 C 开发者我们在编写高性能后端服务时不可避免地要与底层存储打交道。MySQL 作为主流的关系型数据库其基础的“库操作”与“表操作”是每一位程序员的看家本领。今天博主将结合底层原理带大家一次性吃透 MySQL 库与表的核心操纵技巧。一、 数据库Database操作深度解析数据库是表的载体。理解数据库的字符集与校验规则是避免中文乱码和检索异常的关键。1.1 创建数据库创建数据库的标准语法如下CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification]...];其中create_specification可以指定字符集CHARACTER SET和校验规则COLLATE。 实战案例默认创建系统默认采用utf8字符集校验规则为utf8_general_ciCREATE DATABASE db1;指定utf8字符集CREATE DATABASE db2 CHARSETutf8;同时指定字符集与校验规则CREATE DATABASE db3 CHARSETutf8 COLLATE utf8_general_ci;1.2 校验规则的“深水区”区分大小写的影响字符集决定了存储哪些语言而校验规则Collation决定了如何对字符进行比较和排序。MySQL 中常见的两种utf8校验规则utf8_general_ci不区分大小写Case-Insensitive。utf8_bin区分大小写Binary按二进制比对。实验对比数据库校验规则插入数据查询where namea的结果order by name排序结果test1utf8_general_cia,A,b,B返回a和A(共2行)a - A - b - Btest2utf8_bina,A,b,B仅返回a(共1行)A - B - a - b(大写在前)博主敲黑板在设计用户登录、唯一标识等字段时一定要根据业务逻辑选对校验规则。如果需要密码或账号严格区分大小写请务必使用_bin结尾的二进制校验规则1.3 数据库日常维护指令查看系统默认字符集与校验规则SHOW VARIABLES LIKE character_set_database; SHOW VARIABLES LIKE collation_database;查看系统支持的所有字符集及校验规则SHOW CHARSET; SHOW COLLATION;1.3.1 查看所有数据库SHOW DATABASES;1.3.2 查看数据库创建语句验证数据库的字符集、校验规则等配置SHOW CREATE DATABASE mytest;注返回的语句中数据库名会被加上反引号 这是为了防止数据库名与系统保留关键字冲突同时形如/*!40100 DEFAULT ... */的代码并不是普通的注释而是条件注释表示当 MySQL 版本大于 4.01 时将执行该语句。1.3.3 修改数据库仅字符集和校验规则数据库创建后仅支持修改字符集和校验规则不支持修改库名需通过备份恢复间接修改ALTER DATABASE mytest CHARSETgbk;1.3.4 删除数据库谨慎操作删除数据库会级联删除所有表和数据且无法恢复DROP DATABASE [IF EXISTS] db_name;⚠️极其重要提示数据库删除是级联删除里面的所有数据表和数据都会被一并抹除。千万不要在生产环境下轻易尝试1.4 数据库备份与还原核心运维技能在退出 MySQL 连接的终端下我们可以使用mysqldump进行备份。1.4.1 备份整个数据库# 注意 -B 参数会带上创建数据库的语句 mysqldump -P3306 -u root -p密码 -B 数据库名 备份存储的路径.sql # 示例 mysqldump -P3306 -u root -p123456 -B mytest D:/mytest.sql1.4.2 还原数据库进入 MySQL 客户端后执行source指令mysql SOURCE D:/mytest.sql;1.4.3 进阶备份场景仅备份数据库中的特定表mysqldump -u root -p 数据库名 表名1 表名2 D:/mytest.sql同时备份多个数据库mysqldump -u root -p -B 数据库名1 数据库名2... D:/multi_db.sql避坑指南如果备份时没有带-B参数那么在还原时必须先在目标 MySQL 中手动CREATE DATABASE创建空库然后使用USE 数据库名切换进去最后才能执行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 | -------------------------------------------------------------------该命令可以实时显示当前有哪些用户连接到了 MySQL以及他们正在执行的命令、所处的状态和消耗的时间。二、 数据表Table操作全攻关学完了库我们来啃最硬的骨头——表结构的操作。2.1 创建数据表标准语法CREATE TABLE table_name ( field1 datatype, field2 datatype, ... ) CHARACTER SET 字符集 COLLATE 校验规则 ENGINE 存储引擎;️ 实战演练CREATE TABLE users ( id INT, name VARCHAR(20) COMMENT 用户名, password CHAR(32) COMMENT 密码是32位的md5值, birthday DATE COMMENT 生日 ) CHARACTER SET utf8 ENGINE MyISAM; 底层探秘不同的存储引擎物理文件大不相同当我们创建了上面的user1表引擎为MyISAM以及一个InnoDB引擎的表时切换到 MySQL 的数据目录下你会发现MyISAM 引擎的表会生成三个文件user1.frm表结构定义user1.MYD表数据Datauser1.MYI表索引IndexInnoDB 引擎的表会生成两个文件user2.frm表结构定义user2.ibd表数据与索引合并存储的文件2.2 查看表结构使用desc或者是describe命令可以快速查看表的字段属性DESC users;输出字段包括Field(字段名)、Type(类型)、Null(是否允许为空)、Key(索引类型)、Default(默认值)、Extra(扩充属性)。2.3 动态修改表结构DDL 操作在实际项目迭代中需求的变更往往伴随着表结构的调整。以下是高频使用的ALTER TABLE命令1. 添加新字段ADD在birthday字段后面添加一个名为image_path的图片路径字段ALTER TABLE users ADD image_path VARCHAR(100) COMMENT 图片路径 AFTER birthday;注意插入新字段后原来表中已有的历史数据的该字段值会自动填充为NULL。2. 修改字段属性MODIFY将name字段的长度从 20 扩展到 60ALTER TABLE users MODIFY name VARCHAR(60);3. 删除字段DROP⚠️注意删除字段会同时将该列对应的所有历史数据删除请谨慎操作ALTER TABLE users DROP password;4. 修改表名RENAME将users表改名为xingmingTO关键字可以省略ALTER TABLE users RENAME TO employee;5. 修改字段名与定义CHANGE如果要将name列的名字修改为xingming需要写出完整的字段新定义ALTER TABLE employee CHANGE name xingming VARCHAR(60);2.4 删除数据表DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name;示例DROP TABLE IF EXISTS t1; 结语掌握了 MySQL 库与表的基本功我们就拿到了通往高并发、分布式后端开发的第一张入场券。规范的 SQL 编写习惯与合理的引擎、字符集选择能够帮助我们在项目初期避开 90% 的底层性能和乱码深坑。如果你觉得这篇博文对你有帮助别忘了三连点赞收藏关注支持一下博主我们下期再见
的演进与通信机制)
