【MySQL】库的相关操作 + 库的备份和还原

前言

如果你对于数据库的一些基本概念还不太理解，可以看看我上一篇：
【MySQL】想不想知道数据库到底是啥？想了就进来看看

本篇主要讲解：
创建、删除、修改数据库。
对库做备份和还原。

正式开始

本来是不向给出下面这张图的：

因为我觉得新手根本就没法理解，而且还容易被这种一大堆括号的东西吓到，但是这里还是给出来，不过我不会照着这个讲，我按照更容易理解的方式来走。

创建数据库

先简单介绍一下基本操作，等会再讲细节。

create database xxx;

注意，sql语句大写小写都是可以的，不要认为只能大写，我后面全用小写来讲。

我前一篇说过，创建一个数据库就会在配置文件给的datadir所给的路径下创建一个目录：

看：

创建数据库的时候还可以加上if not exists，作用就是判断一下有没有，没有就创建，有了就不起作用：

删除数据库

drop database xxx;

对应的也就是删除目录：

那么我在datadir路径下创建一个目录，那么在show databases的时候也是能看到的：

再删掉这个目录，对应show 的时候也就消失了：

但是通过手动创建目录的方式是创建不出来数据库的，虽然能用use选中数据库，但是创建不出来表。必须通过sql语句来创建数据库。

注意，删除数据库就是删目录，目录只要一删除，其中的表和数据就都没了，所以特别不推荐直接对数据库做删除操作，如果要删，必须先确定其中的数据还有没有用，以防万一也可以做一下备份（备份怎么做等会会讲到）。

编码集

创建数据库的时候会有两个编码集：字符集和校验规则。

只要有数据，存储的时候一定是要有编码格式的，数据库就主要是与数据打交道的，所以就必须非常注重编码集，编码集必须是统一的，存的时候用什么编码，取的时候就要用与之对应的编码来取。

比如说一张纸上写着一堆中文，那就必须懂中文才能懂纸上写的是啥东西，用英文来读中文是不可能读懂的。

存数据的时候看的是字符集，取数据的时候看的是校验规则。上面的这个简单的例子中，中文就是存放的数据，所以中文的语法规则就指的是字符集，用什么语言来读就是用什么校验规则，英文的校验规则（语法、单词啥的）和中文对不上，那就读不懂。

查看系统默认字符集以及校验规则

show variables like 'character_set_database'; # 查看字符集
show variables like 'collation_database'; # 查看校验规则

字符集

可能你执行这条语句后结果是latin1，没关系，因为我前面配置文件中设置了我的mysqld默认的编码格式为utf8：

再来看一下当前数据库8个变量所用的字符集：

关于这几个character_set，想要详细了解的可以看这篇：
关于MySQL中的8个 character_set 变量说明

校验规则

这里的utf8_general_ci是和utf8对应的，存的时候用utf8，取的时候用utf8_general_ci。

当前数据库中连接、创建数据库、服务的校验规则：

所有支持的字符集和校验规则

所有字符集

其中就有utf8，对应的默认校验规则就是utf8_genral_ci。

所有校验规则

非常多：

我也把utf8_general_ci圈出来了。

这些所有的编码集都是可选的，不过我这里默认情况下用的是utf8，等会创建数据库的时候也可以做修改。

指明字符集和校验规则创建数据库

先来一个默认字符集的：

我前一篇说过创建一个数据库，其中会有一个文件，但没有说这个文件中的内容是啥，这里就能知道了，就是默认的字符集和校验规则，因为创建数据库d1的时候并没有指明字符集和校验规则，所以就用的默认字符集和校验规则。

再来指明字符集和校验规则来创建：

• 指明字符集为utf8：
  

有多种写法：

中间的等号可以不加：

character set也是：

• 指明校验规则为utf8_general_ci：
  我这里把刚刚创建出来的5个全删掉，不然影响观察。

也是可以不加等号，换成utf8_bin试试：

换成和utf8完全不相关的：

可以看到mysqld还是很聪明的，会自动换成匹配的字符集。

• 指明字符集为utf8，校验规则为utf8_general_ci：
  再把刚刚的三个删掉。

指明utf8 和 utf8_bin

指明 latin1 和 latin1_bin：

如果指明的字符集和校验规则不匹配：

会出错。

相同的字符集用不同的校验规则读取会出现什么情况

下面的两个d1和d2字符集用的都是utf8，但d1的校验规则是utf8_general_ci，d2的校验规则用的是utf8_bin。

utf8_general_ci是不区分大小写的，但utf8_bin区分，但是光说也体会不出来，得创建两个表看看。

对数据库操作时必须先选中数据库：use db_name。

先选定d1建一张表：

表的相关操作我后续博客会详谈，这里先认识一下。

这里创建了一个person表，其中有一列，往里面插入点数据：

我往里面插入了小写的a、b、c，又插入了大写的A、B、C。

插入的本质就是存，那么存的时候用的就是字符集，select查询的时候是取，取的时候要进行对比库中的数据和你想取的数据是否一致，所以用的就是编码集。

查看一下其中的a：

这里查出来了两个，一个a，一个A。这就可以说明utf8_general_ci是不区分大小写的。

再来按照name升序排序：

排序的时候要进行比较，比较就要对其编码进行校验，所以用的是校验集，可以看到小写和大写是放在一块的，都是一样的。

再用d2做同样的工作：

d2却只查出来了a。所以utf8_bin区分大小写。

排序：

大写在前，小写在后，其实这里就是按照ASCII排的。

后面我创建库默认的字符集就是utf8，默认校验规则就是utf8_general_ci。

数据库的字符集和校验规则会间接（并非直接，你也可以再改）影响库中表的字符集和校验规则，不过这都是后话了，等我下一篇讲表的操作的时候再细说。

alter修改数据库

show create database

不过说alter之前先说一下这个：

show create database xxx; # xxx为数据库名 

这条语句可以查看你在创建xxx数据库的时候的细节（其实就是编码的细节，没有别的）。

比如说刚刚的d1和d2：

alter

再来说alter，一般alter操作是为了修改数据库编码的。

来改一下d1：

改了之后的db.opt文件：

show：

再来看看d2，改成gbk：

这里gbk_chinese_ci就是gbk的默认校验规则，所以没有显示。

到这里库的增(create)删(drop)查(show)改(alter)工作就已经搞完了。

下面说说库的备份和恢复

库的备份和恢复

如何在Linux中对一个文件做备份？
很简单，cp一下再重命名或者直接重命名就行。

那数据库文件也是一个文件，能不能也像普通文件那样备份呢？
不行。mysql专门给出了备份相关的操作的。

mysqldump备份整个库

 mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

就拿刚刚的d1来做备份。

注意这个路径下是空的。就备份到这个路径下面。

可以看到其中多了一个文件。

用vim打开，里面的内容是这样的：

里面保存了历史上做过的一些操作，不光是存放数据。

现在我删除掉d1：

现在datadir中就不存在d1这个目录了。

现在想要快速恢复出d1，怎么搞呢？
source。

直接source后面跟路径，就能将历史中记录的sql命令全部执行一次。

此时就d1就还原了：

其实上面的操作还可以搞成远端的，现在本地搞个备份，然后发送到远端的另一台主机上，这样那台主机专门用来做备份，等出现问题的时候再将备份通过远端发回来就好。

注意备份整个库的时候一定要加上-B，不然就没有备份上，备份出来的是空的。

mysqldump备份某张（某几张）表

如果备份的不是整个数据库，而是其中的一张表，怎么做？

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

我先来再创建三张表：

删掉person表，此时只有这三个：

备份：

解释一下，这里因为我配置文件中设置了不需要密码，所以没有加-p。
d1就是数据库名，person、t1、t3就是表名。

如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据
库，再使用source来还原。

删掉d1数据库：

再创建一个空的d1：

注意一定要先选中数据库再进行还原，不然就出错：

选中后：

因为刚刚只备份了t1和t3，所以还原出来的只有这两张表：

注意，如果你备份整个数据库的时候没有加上-B，那还原的时候也得要先创建一个数据库，然后再选中这个数据库才能还原：

如果备份整个数据库没有加B，可以实现对数据库重命名：

上面用的还是刚刚d1备份的数据，但是可以直接还原到test中。

其实mysql以前是支持重命名的，但是现在不支持了。

库的操作该讲的都讲了，下一篇说表的操作。

补充一个小点：
show processlist可以显示当前数据库的连接情况。

这里只有我一个root用户登录。

到此结束。。。