Re：Mysql数据库基础篇（三）：全面掌握数据库与数据表操作：深度剖析底层文件差异与核心管理机制-平芜编程栈

◆ 博主名称：小此方-CSDN博客大家好，欢迎来到小此方的博客。

⭐️Linux系列个人专栏：【主题曲】Mysql

⭐️此方的GitHub： github_此方

⭐️Re系列专栏：我们思考 (Rethink) · 我们重建 (Rebuild) · 我们记录 (Record)

文章目录

概要&序論
一、 MySQL 数据库的基本操作
- 1.1 创建数据库
- - 1.1.1 创建数据库案例
- 1.2 字符集与校验规则的查看
- 1.3 校验规则对数据库的影响
- - 1.3.1 实验环境准备
  - 1.3.2 校验规则对查询与排序的影响对比
- 1.4 操纵与维护数据库
- - 1.4.1 查看数据库与显示创建语句
  - 1.4.2 修改数据库
  - 1.4.3 数据库的删除
- 1.5 数据库的备份与恢复
- - 1.5.1 数据库备份
  - 1.5.2 数据库恢复
  - 1.5.3 备份的进阶技巧与注意事项
- 1.6 查看数据库的连接情况
二、 MySQL 表的基本操作
- 2.1 创建数据表
- - 2.1.1 创建表综合案例
  - 2.1.2 存储引擎与底层文件的关系
- 2.1.3 查看表创建时的详细信息
- 2.2 查看表结构
- 2.3 修改数据表（ALTER TABLE）
- - 2.3.1 增加新字段（ADD）
  - 2.3.2 修改字段属性（MODIFY）
  - 2.3.3 删除字段（DROP）
  - 2.3.4 修改表名（RENAME）
  - 2.3.5 修改列名与完整重定义（CHANGE）
- 2.4 删除数据表

概要&序論

本文深入讲解 MySQL 中数据库与数据表的核心操作与底层原理。
阐述数据库创建的语法要素，对比utf8_general_ci与utf8_bin校验规则对查询和排序的底层影响；
详解数据库的修改、安全删除以及通过mysqldump进行级联备份与恢复的工程实践；
剖析数据表创建流程，揭示MyISAM与InnoDB存储引擎在磁盘文件结构上的本质差异；
演示ALTER TABLE在增加、修改、删除字段及重命名表时的应用与旧数据保留特性。
好的，我们直接开始。

一、 MySQL 数据库的基本操作

1.1 创建数据库

SQL和文件也是上下层的关系

SQL 语句	Linux 文件系统本质
创建数据库：`create database db_name;`	本质就是再`/var/lib/mysql`创建一个目录
删除数据库：`drop database db_name;`	删除目录

在 MySQL 中，创建数据库的基本语法如下：

CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...];create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name

语法要点说明：

大写字母表示 SQL 关键字。
方括号[]表示该选项是可选项。
CHARACTER SET：用于指定数据库采用的字符集，它决定了数据库控制和支持什么语言。例如，设置成utf8就可以支持中文。
COLLATE：用于指定数据库字符集的校验规则（或校对规则），它会直接影响字符串的排序和对比表现。

1.1.1 创建数据库案例

为了更好地理解，我们来看几个具体的创建案例：

基础创建：

createdatabasedb1;

注意：当我们创建数据库而没有显式指定字符集和校验规则时，系统会使用默认值。在 MySQL 5.7 中，默认字符集通常是utf8，默认校验规则是utf8_general_ci。

指定字符集创建：

createdatabasedb2charset=utf8;//或者：create database db2 character set = utf8;

同时指定字符集与校对规则创建：

createdatabasedb3charset=utf8collateutf8_general_ci;

1.2 字符集与校验规则的查看

我们可以通过以下指令来查看系统当前的状态或支持的配置：

查看当前系统默认的数据库字符集以及校验规则：

showvariableslike'character_set_database';showvariableslike'collation_database';

查看当前 MySQL 数据库支持的所有字符集：

showcharset;

查看当前 MySQL 数据库支持的所有字符集校验规则：

showcollation;

1.3 校验规则对数据库的影响

校验规则的不同会直接影响数据库在查询和排序时的表现。我们通过两组案例（不区分大小写与区分大小写）进行对比：

1.3.1 实验环境准备

首先分别创建不区分大小写和区分大小写的两个数据库，并插入相同的数据：

-- 1. 创建不区分大小写的数据库 testicreatedatabasetesticollateutf8_general_ci;usetesti;createtableperson(namevarchar(20));insertintopersonvalues('a'),('A'),('b'),('B');-- 2. 创建区分大小写的数据库 test2createdatabasetest2collateutf8_bin;usetest2;createtableperson(namevarchar(20));insertintopersonvalues('a'),('A'),('b'),('B');

1.3.2 校验规则对查询与排序的影响对比

校验规则类型	查询`where name='a'`的结果	排序`order by name`的结果
utf8_general_ci (不区分大小写)	会同时查询出a和A两条记录。	排序时，大小写字母交织在一起，不作严格区分（如：a -> A -> b -> B）。
utf8_bin (区分大小写)	只能精确查询出a这条记录。	依据 ASCII 码值进行严格排序，大写字母排在小写字母前面（如：A -> B -> a -> b）。

1.4 操纵与维护数据库

1.4.1 查看数据库与显示创建语句

查看当前服务中的所有数据库：

showdatabases;

显示特定数据库的创建语句：

showcreatedatabase数据库名;

说明：
MySQL 建议开发人员将关键字使用大写，但这并不是强制性的。
自动生成的语句中，数据库名字带有反引号（``），这是为了防止用户使用的数据库名刚好与 MySQL 系统关键字冲突。
返回结果中如果包含类似/*!40100 DEFAULT CHARACTER SET utf8 */的内容，这并不是普通注释。它表示当前 MySQL 的版本如果大于或等于 4.01，就会执行这句话。

1.4.2 修改数据库

对数据库的修改主要指的是修改数据库的字符集或校验规则。

ALTERDATABASEdb_name[alter_specification[,alter_specification]...];

实例：将mytest数据库的字符集改成gbk：

alterdatabasemytestcharset=gbk;

1.4.3 数据库的删除

DROPDATABASE[IFEXISTS]db_name;

执行删除后的级联影响：

数据库内部将看不到对应的数据库。
对应的数据库文件夹会被直接删除。这是一个级联删除行为，意味着里面的所有数据表和数据全都会被一起清空。

警告：在实际生产和开发环境中，绝对不要随意删除数据库！

1.5 数据库的备份与恢复

1.5.1 数据库备份

注意，进行数据库备份时，需要在Linux 命令行（Shell）中执行，而不是在 MySQL 交互式命令行内。

# 备份语法mysqldump-P3306-uroot -p密码-B数据库名>数据库备份存储的文件路径# 备份示例：将 mytest 库备份到 D 盘根目录mysqldump-P3306-uroot-p123456-Bmytest>D:/mytest.sql

原理解析：备份生成的.sql文件中，实际上装载的是我们对整个数据库进行创建、建表、导入数据的所有 SQL 语句的历史重现。

1.5.2 数据库恢复

登录进入 MySQL 后，使用source命令引入备份文件即可实现还原：

mysql>source D:/mytest.sql;

1.5.3 备份的进阶技巧与注意事项

只备份其中的某张或某几张表：

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

同时备份多个数据库：

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

关于-B参数的重要说明：如果备份一个数据库时没有加上-B参数，该文件内就不会包含CREATE DATABASE和USE语句。因此在恢复时，你必须自己手动先创建一个空数据库，并使用use选中它，然后才能运行source命令进行还原。

1.6 查看数据库的连接情况

showprocesslist;

该指令可以清晰地告诉我们当前有哪些用户和主机连接到了当前的 MySQL 服务。

安全小贴士：如果发现某个连接的用户或者 IP 不是你正常登录的，很有可能数据库已经被黑客入侵了。在以后的实际开发中，如果发现数据库运行比较缓慢，也可以通过该指令来排查是否有异常的死锁连接或大量堆积的查询。

二、 MySQL 表的基本操作

2.1 创建数据表

在选定的数据库中创建一张表的语法如下：

CREATETABLEtable_name(field1 datatype,field2 datatype,field3 datatype)characterset字符集collate校验规则engine存储引擎;

缺省规则：

如果没有显式指定表级别的字符集，则默认以其所在的数据库的字符集为准。
如果没有显式指定表级别的校验规则，则默认以其所在的数据库的校验规则为准。

2.1.1 创建表综合案例

createtableusers(idint,namevarchar(20)comment'用户名',passwordchar(32)comment'密码是32位的md5值',birthdaydatecomment'生日')charactersetutf8engineMyISAM;

2.1.2 存储引擎与底层文件的关系

不同的存储引擎，在操作系统的磁盘上创建的底层表结构文件是不一样的。

当我们创建上面的users表且引擎设置为MyISAM时，在 MySQL 的 Data 目录下会产生三个文件：
1. users.frm：用于保存表结构定义。
2. users.MYD：用于保存表数据。
3. users.MYI：用于保存表索引。
如果我们将引擎指定为InnoDB，通常会生成一个.frm（表结构文件）和一个.ibd（将数据和索引合二为一的独占表空间文件）。
没有指明存储引擎的时候默认用配置文件里面的。

2.1.3 查看表创建时的详细信息

使用show create table 表名;可查看最完整的建表脚本。在终端中结合\G选项（如show create table user1 \G）能实现垂直列格式化输出，大幅提升复杂表结构的可读性。
你当时写的不是很标准的SQL语句，在MYSQL内部的此法和语法分析之后，会补充完善成为标准的SQL语句然后保存。

mysql>showcreatetableqwe \G+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|qwe|CREATETABLE`qwe`(`name`varchar(20)DEFAULTNULL,`gender`varchar(60)DEFAULTNULL,`hjk`varchar(300)DEFAULTNULLCOMMENT'7978797',`asd`intDEFAULTNULLCOMMENT'123456')ENGINE=InnoDBDEFAULTCHARSET=utf8mb3|+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2.2 查看表结构

想要快速查看一张表包含了哪些字段以及各字段的属性，可以使用如下指令：

desc表名;

输出的信息中主要包含以下核心列：

Field：字段名字。
Type：字段的数据类型。
Null：是否允许为空（YES 或 NO）。
Key：索引类型（如主键 PRI 等）。
Default：默认值。
Extra：扩充附加属性（如自增 auto_increment 等）。

2.3 修改数据表（ALTER TABLE）

在项目的实际开发与迭代中，随着业务的发展，经常需要修改已经存在的表结构。

2.3.1 增加新字段（ADD）

ALTERTABLEtablenameADD(columndatatype[DEFAULTexpr][,columndatatype]...);

实例：在users表的birthday字段后面添加一个名为assets的新列，用来保存图片路径：

altertableusersaddassetsvarchar(100)comment'图片路径'afterbirthday;

核心特性：新字段插入后，对原表中的旧数据完全没有破坏性影响。原有的旧数据依然完好保存，新字段在旧数据中的值会自动被填充为NULL。

2.3.2 修改字段属性（MODIFY）

如果需要调整字段的类型或者大小，可以使用modify：

ALTERTABLEtablenameMODIFY(columndatatype[DEFAULTexpr]...);

实例：将users表中name字段的长度上限从 20 扩展修改为 60：

altertableusersmodifynamevarchar(60);

2.3.3 删除字段（DROP）

ALTERTABLEtablenameDROP(column);

实例：从users表中删除password列：

altertableusersdroppassword;

危险提示：执行删除字段的操作一定要极其慎重！一旦该列被删除，该字段以及该列下对应的所有历史数据也将全部随之烟灭，不可轻易逆转。

2.3.4 修改表名（RENAME）

altertableusersrenametoemployee;

提示：其中关键字to是可以省略不写的，例如alter table users rename employee;也能达到同样的效果。

2.3.5 修改列名与完整重定义（CHANGE）

如果你想彻底改掉某个字段的名字，应该使用change。

altertableemployee change old_column_name new_column_name datatype;

实例：将employee表中的name列修改为xingming：

altertableemployee change name xingmingvarchar(60);

注意：使用change时，即便不改变数据类型，在指定了新列名后依然需要完整地写一遍它的数据类型与约束定义。

2.4 删除数据表

DROP[TEMPORARY]TABLE[IFEXISTS]tbl_name[,tbl_name];

实例：删除掉不再使用的t1表：

droptablet1;

修改表和删除表非必要不要改。SQL是比较底层的东西，上层依赖底层，底层修改会让上层崩溃。

好的本期内容就到这里，如果对你有帮助，还不要忘记点赞三联支持。我是此方，我们下期再见。bye!

Re：Mysql数据库基础篇（三）：全面掌握数据库与数据表操作：深度剖析底层文件差异与核心管理机制