【半夜学习MySQL】库的操作（含库的创建、删除、修改、备份操作/查看mysql连接情况/字符集和校验规则详谈）

🏠关于专栏：半夜学习MySQL专栏用于记录MySQL数据相关内容。
🎯每天努力一点点，技术变化看得见

---

创建数据库

语法格式如下：

create database [if not exists] db_name [create_specification...]

create_specification:
	[default] character set charset_name
	[default] collate collation_name 

格式说明：
●上述显式为蓝色、黄色的单词表示关键字
●中括号内为可选项
●character set：指定数据库采用的字符集
●collate：指定数据库字符集的校验规则

创建数据库示例1：
创建名为db1的数据库

create database db1;

★ps：show create database [数据库名称] \G可以查看数据库创建信息

创建数据库示例2：
创建一个使用utd8字符集的db2数据库

create database db2 charset=gbk;

创建数据库示例3：
创建一个使用utf字符集，并带校验规则的db3数据库

create database db3 charset=gbk collate=gbk_chinese_ci;

★ps：当我们创建数据库没有指定字符集和校验规则（且用户没有在/etc/my.cnf配置默认字符集和校验规则）时，系统多数情况下默认使用utf8字符集、utf8_general_ci校验规则。

★ps：为了方便数据库、表的创建，可以在/etc/my.cnf直接配置默认的字符集和校验集合（配置完毕后，需要重启mysqld才能够生效）↓↓↓

字符集和校验规则

创建数据库的时候，有两个编码集：

1. 数据库编码集 – 数据库存储数据的格式
2. 数据库校验集 – 支持数据库进行字段比较使用的编码，本质是一种读取数据库中数据采用的编码格式

★ps：数据库无论对数据做任何操作，都必须保证操作和编码必须是编码一致的

★ps：进入某个数据库目录中后，该数据库一定会有一个存储该数据库字符集和校验规则的db.opt文件↓↓↓

查看字符集合校验规则

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

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

查看数据库支持的字符集

show charset;

查看数据库支持的校验规则

show collation;

校验规则对数据库的影响

这里对utf8_general_ci和utf8_bin做比较，说明校验规则对数库的影响。其中，utf8_general_ci不区分大小，而utf8_bin区分大小写↓↓↓

●不区分大小写
>创建一个数据库，使用utf8_general_ci校验规则：

create database test1 collate=utf8_genaral_ci;

>使用test1数据库，并在它的内部创建一个letters表：

use test1;
create table letters(lett varchar(6));

★ps：其中select database();可以查看当前处于哪个数据库中，show tables可以查看当前数据库中的表

>在letters表中存入’a’、‘A’、‘b’、‘B’、‘c’、‘C’，这6个字符：

insert into letters values('a');
insert into letters values('A');
insert into letters values('b');
insert into letters values('B');
insert into letters values('c');
insert into letters values('C');

>使用查询语句，查询表格中的’a’字符：

select * from letters where lett='a';

★ps：我们查询的明明是小写字母a，但它却查询出了大A和小a，就说明了utf8_general_ci是不区分大小写的。

●区分大小写
>创建一个名为test2，使用utf8_bin校验集的数据库，重复上述操作：

create database test2 collate=utf8_bin;
use test2;
create table letters(lett varchar(6));
insert into letters values('a');
insert into letters values('A');
insert into letters values('b');
insert into letters values('B');
insert into letters values('c');
insert into letters values('C');

>使用查询语句，查询表格中的’a’字符：

select * from letters where lett='a';

★ps：我们查询的是小写字母a，但它查询的也是小a，就说明了utf8_bin是区分大小写的。

>我们也可以对上述两个数据库中的letters的数据做排序，验证utf8_general_ci和utf8_bin的区别，两个表格排序结果如下所示：

select * from test1.letters order by lett;
select * from test2.letters order by lett;

★ps：由于test2是区分大小写的，所以它按字符的ASCII码进行排序。这也说明test2对应的校验规则utf8_bin是区分大小的。

操纵数据库

查看数据库

show databases;

显示创建语句

show create database [数据库名];

★ps：当show create database [数据库名];显示较为杂乱时，我们可以使用show create database [数据库名] \G来格式化显示

★ps：一些注意事项
> MySQL建议我们关键字使用大写，但是不是必须的。
> 上方显示的数据库创建语句中，数据库名称的反引号``是为了防止使用的数据库名刚好是关键字。
> /\*!40100 default...\*/这个不是注释，表示当前mysql版本大于4.01版本时，就执行这句话。

修改数据库

alter database db_name [alter_spacification...]

alter_spacification:
[default] character set chatset_name
[default] collate collation_name

★ps：对数据库的修改主要指的是修改数据库的字符集、校验集

下面的示例，对上述test1的数据库的字符集从utf8更改为gbk：

alter database test1 charset=gbk;

★ps：mysql数据库不支持对数据库名称做重命名。同时，在数据库创建后，通常不会轻易对数据库做任何修改，修改后可能影响上层已经实现的相关C++/Java等代码。

数据库删除

drop database [if exists] db_name;

执行删除之后的结果：
● 数据库内部看不到对应的数据库
●对应的数据库文件夹被删除，级联删除，里面的数据表全部被删除

下面对上述test1和test2数据库进行删除，在删除前，我们先看以下/var/lib/mysql目录。该目录下，目前有名为test1和test2的目录：

下面执行test1、test2数据库删除操作：

drop database test1;
drop database test2;

此时再查看/var/lib/mysql目录，名为test1和test2的目录被自动删除了：

★ps：数据库一旦创建，不要随意删除

★ps：我们可不可以通过删除/var/lib/mysql对应数据库的目录，来删除数据库呢？先试验以下：


虽然删除对应数据库目录，可以删除数据库，但不建议使用这种操作。

数据备份和恢复

备份
命令如下：

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

下面演示对jammingpro数据库进行备份，该数据库存在一张personInfo表：

执行备份命令：

mysqldump -p3306 -uroot -p -B jammingpro > /home/baymax

这时，可以打开看看 jam.sql 文件里的内容，其实把我们整个创建数据库，建表，导入数据的语句都装载这个文件中↓↓↓

还原
语法如下：

source 备份数据库文件目录

下面演示将上述jammingpro数据库还原的操作，首先，我们先将当前mysql中的jammingpro数据库删除↓↓↓

再执行还原指令：

source /home/baymax/jam.sql

此时再查看、使用jammingpro数据库，并查询原personInfo表，结果如下：

注意事项1
如果备份的不是整个数据库，而是某个数据库的某张表，该怎么做呢？

mysqldump -u root -p 数据库名 表名1 表名2 > 目录名

下面操作，将jammingpro数据库下的personInfo表进行备份，并还原到db2数据库中（该数据库原来没有personInfo表）：



注意事项2：
同时备份多个数据库

mysqldump -p3306 -uroot -p -B 数据库名1 数据库名2 > 目录名

该操作的备份与还原，与单个数据库备份还原相似，这里不再做出演示
注意事项3：
如果备份数据库时，没有使用-B选项指名数据库名称，需要先创建一个空的数据库，才能对数据库进行还原。

查看连接情况

语法：

show processlist;

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

🎈欢迎进入半夜学习MySQL专栏，查看更多文章。
如果上述内容有任何问题，欢迎在下方留言区指正b(￣▽￣)d