【MySQL】对数据库的操作以及数据库备份相关操作（DDL）

👦个人主页：@Weraphael
✍🏻作者简介：目前学习计网、mysql和算法
✈️专栏：MySQL学习
🐋 希望大家多多支持，咱一起进步！😁
如果文章对你有帮助的话
欢迎 评论💬 点赞👍🏻 收藏 📂 加关注✨

---

一、对数据库的操作

1.1 查看数据库（查）

【语法】

show databases

1.2 创建数据库（增）

【语法】

create database [数据库名]

• 可选项if not exists：不存在数据库就创建， 存在则会报警告

create datebase if not exists [数据库名]

1.3 修改数据库（改）

在对数据库修改之前，首先需要指定数据库

use [数据库]

但是有时我们会忘记正在哪个数据库工作，因此有个方法可以查看

select databases();
# databases() - 是一个函数

需要注意的是，不建议对数据库的名字进行修改，因为需要确保所有依赖原数据库名称的地方都做出相应的更改。这涉及到更新应用程序的配置文件、代码中的数据库连接信息等，而且还需要确保权限设置和数据迁移等工作都能够正确完成。这是非常麻烦的一件事儿~

但如果实在要改名，可以进行备份修改。点击跳转

1.4 删除数据库（删）

【语法】

drop database  [数据库名称]

• 可选项if exists：检查数据库，存在才删除，删除不存在的数据库是会报警告

drop database if exists [数据库名称]

我们看到，执行删除之后的结果:

• 数据库内部看不到对应的数据库
• 对应的数据库文件夹被删除

因此，不要轻易删除数据库，否数据库删除后很难恢复，为了避免误删数据库，通常需要将数据库进行备份

二、数据库的备份和恢复

2.1 备份

假设在数据库Test下，有表student，其内容如下：

现在我要对Test数据库进行备份，则要使用mysqldump命令

mysqldump -P 端口号 -u root -p -B 备份数据库 > 备份路径

2.2 恢复

在恢复之前，为了方便验证，我需要先将数据库Test删掉

drop database Test;

然后需要使用source命令进行恢复

source 备份路径;

接着再查询数据库Test中的表student的信息是否还存在

2.3 补充1：备份数据库表（多个表）

 mysqldump -u root -p 数据库名 表1 表名2 ... > 备份路径

2.4 补充2：同时备份多个数据库

 mysqldump -u root -p -B 数据库名1 数据库名2 ... > 备份路径	

2.5 补充3：修改数据库名

1. 先将你要修改名字的那个数据库进行备份（不用带-B）

mysqldump -P 3306 -u root -p 备份数据库 > 备份路径

2. 再创建一个空数据库（指定新名字）

create database [数据库];

3. 然后使用创建的数据库

use [数据库]

4. 最后再使用source

source [备份路径];

三、补充知识：数据库中的编码问题

3.1 字符集与校验集

当我们在创建数据库时，对应文件存储路径就会自动生成一个名为数据库的目录。（在安装MySQL时我将文件存储路径设置为/var/lib/mysql）

当我们查看d1目录时，发现里面有一个内置的文件db.opt

不妨可以看看文件内容：

以上就是数据库的字符集和检验集，用于设置数据库中的编码规则。

那什么是字符集和检验集呢？

• 字符集用于规定存储时的编码格式

• 校验集用于规定数据查询时的校验规则

接下来可以查看数据库默认使用的字符集与编码集

show variables like 'character_set_database';
show variables like 'collation_database';

当前MySQL对于数据库的默认字符集 是utf8，这是因为之前安装MySQL时我在配置文件my.cnf设置的默认字符集就是utf8；而默认的校验集则是 utf8_general_ci。这两个也是我们主流的的字符集和校验集之一。

3.2 MySQL支持多种字符集与校验集

• 字符集

show charset;

• 校验集

show collation;

• 创建数据库时指定编码相关信息的语法如下

# 第一种
create database [数据库名] charset=[字符集] collate [校验集];
# 第二种
create database [数据库名] character set [字符集] collate [校验集];

# 注意：
# 如果只指定字符集或者校验集其一
# 会根据字符集或者校验集推导出它的另一半

举例来说，对于UTF-8字符集，可以选择不同的校对规则，如utf8_general_ci（大小写不敏感）或utf8_bin（大小写敏感）。可以根据实际需求选择合适的字符集和校对规则，以确保数据的正确性、一致性和排序方式符合预期。

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

• 首先有一个问题：为什么在MySQL中有字符集和校验集的存在呢？

字符集和校对规则在数据库中的作用主要是确保存储和处理数据的正确性、一致性和兼容性。不同的字符集和校对规则适用于不同的语言和文本数据。就比方说，你要在数据库中查询字符a，而有些校验规则是不区分大小写的，那么就会找出A和a

---

接下来演示utf8_general_ci（大小写不敏感）与utf8_bin（大小写敏感） 这两种不同的校验集对查询的影响

• 创建一个数据库、建立表并往表中插入字符 【校验规则使用utf8_ general_ ci】

# 创建一个数据库
create database test1 collate utf8_general_ci;
# 在修改表前需要使用对应的数据库
use test1;
# 建表
create table person(name varchar(20));
# 插入
insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');

• 创建一个数据库，要求和以上一样【校验规则使用utf8_ bin】

create database test2 collate utf8_bin;
use test2;
create table stu(name varchar(20));

insert into stu values('a');
insert into stu values('A');
insert into stu values('b');
insert into stu values('B');

• 查询操作

【不区分大小写的查询以及结果】

use test1;
select * from person where name='a';

【区分大小写的查询以及结果】

use test2;
select * from stu where name='a';

• 对结果排序

【不区分大小写排序以及结果】

use test1;
select * from person order by name;

【区分大小写排序以及结果】

use test2;
select * from stu order by name;

不同的校验规则也就对应着不同的返回结果，因此需要对应用的需求来设置字符集和校对集，以便正确地存储和处理数据。

四、查看连接情况

当发现自己数据库比较慢时，可以用以下指令来查看数据库连接情况。它可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。

show processlist;