✨个人主页: 北 海
🎉所属专栏: MySQL 学习
🎃操作环境: CentOS 7.6 阿里云远程服务器
🎁软件版本: MySQL 5.7.44
文章目录
- 1.创建数据库
- 2.数据库中的编码问题
-
- 2.1.字符集与校验集
- 2.3.支持的字符集与校验集
- 2.4.自由设置字符集与校验集
- 2.2.校验集对查询的影响
- 3.查看数据库
- 4.修改数据库
- 5.删除数据库
- 6.数据库的备份与恢复
- 7.查看数据库的连接情况
1.创建数据库
创建数据库的语法为
CREATE DATABASE [IF NOT EXISTS] database_name;
[ ]
中的关键字可以选择不加,IF NOT EXISTS
的意思是如果不存在才创建 数据库
登录 MySQL
后输入指令进行创建
mysql> create database if not exists db1;
MySQL
中的语法不区分大小写,语法中通常 将关键字写成大写,方便学习和查看
注意: 如果数据库已存在,再次创建会报错,可以通过 if not exists
关键字将报错降低为警告
在 Linux
中创建数据库的本质就是 在 /var/lib/mysql
目录下创建了一个目录
进入 db1
目录,可以看到里面只有一个后缀为 .opt
的配置文件
通过 cat
指令查看其中的类型,可以发现默认只有 字符 character
和 校对 collation
相关的配置信息,这俩其实就是 字符集 和 检验集,用于设置 数据库 中的编码规则
为什么要设置编码?
因为 数据库 是用来存放 表 的,而 表 是用来存放内容的,内容又是以不同编码格式存储的,所以需要先确定 数据库 中的编码规则,才能确保后续能正常使用
2.数据库中的编码问题
2.1.字符集与校验集
MySQL
中与编码相关的主要是 字符集 与 校验集
字符集 用于规定未来存储时的编码格式
校验集 则是规定数据查询时的校验规则
查看 MySQL
所有关于默认 字符集 与 校验集 的使用情况
mysql> show variables like 'character_%';
mysql> show variables like 'collation_%';
注:%
在 MySQL
表示匹配一个或任意多个字符,类似于 Linux
中的 *
其中 字符集 的使用涵盖了 客户端、连接、数据库、文件系统、结果、服务端、系统、目录,校验集 则主要用于 连接、数据库、服务端
主要都是使用 utf8
作为 字符集,utf8_general_ci
作为 校验集
校验集
utf8_general_ci
是适用于utf8
的一种校验规则,除此之外还有其他校验规则(后面说)
接下来查看 数据库 默认使用的 字符集 与 编码集
mysql> show variables like 'character_set_database';
mysql> show variables like 'collation_database';
当前 MySQL
对于数据库的默认 字符集 是 utf8
,这是因为之前安装 MySQL
时在配置文件 my.cnf
设置的默认字符集就是 utf8
;默认的 校验集 则是 utf8_general_ci
以上是创建 数据库 时默认使用的 字符集 与 校验集,如果想要查看具体某个 数据库 的编码使用情况,可以 cd
到 /var/lib/mysql
目录中,在进入具体的数据库目录,查看后缀为 .opt
的配置文件信息即可
2.3.支持的字符集与校验集
MySQL
支持很多 字符集,通过指令查看
mysql> show charset;
几乎所有主流 编码 它都支持,接下来看看支持的 校验集
mysql> show collation;
截图过长,只截取了部分
一种 字符集 可以有多种 校验集,所以这张表会很大,不同的 校验集 的校验规则有所不同,比如 utf8_general_ci
与 utf8_bin
的区别在于 是否忽略大小写
utf8_general_ci
是忽略大小写的,而 MySQL
服务端使用的 校验集 正是 utf8_general_ci
,这也就是解释了为什么在 MySQL
中输入指令可以忽略大小写;至于 utf8_bin
区分大小写,这两种 校验集 的演示放在后面细谈
2.4.自由设置字符集与校验集
配置文件 my.cnf
中设置的 字符集 配置信息就像一个 “缺省值”,如果用户不指定,就使用默认的,如果用户指定了,就优先使用用户指定的 字符集,校验集 也是如此
注意: 校验集要能适用于字符集,否则会导致无法创建
创建 数据库 时指定编码相关信息的语法如下
CREATE DATABASE [IF NOT EXISTS] database_name [CHARSET=xxx | CHARACTER SET xxx] [COLLATE xxx]
|
表示存在多种写法,可以任意选择其中一种
接下来演示创建 数据库 时指定 字符集 与 校验集
这里指定字符集为
gbk
,校验集为gdk_chinese_ci
mysql> create database if not exists db2 charset=gbk collate gbk_chinese_ci;
// 或者
mysql> create database if not exists db2 character set gbk collate gbk_chinese_ci;
如果只指定 字符集 或者 校验集 可以成功创建吗?
可以的,会根据 字符集 或者 校验集 推导出它的另一半
那如果指定毫不相关的 字符集 与 校验集 能成功创建 数据库 吗?
不行,会直接报错的,既然校验规则不适用,创建了也没意义,这就好比你往语文卷子上写英语,语文老师大概率是看不懂的,并且不会批改你的卷子
总之,MySQL
支持在创建 数据库 时自由设置 字符集 与 校验集,只指定其中一方也能成功创建,因为 MySQL
可以进行推导,但如果指定的 字符集 与 校验集 没有关系,是无法创建 数据库 的,根据实际情况灵活设置
2.2.校验集对查询的影响
一个 字符集 拥有多个 校验集,也就意味着可以使用不同的校验规则,接下来演示 utf8_general_ci
与 utf8_bin
这两种不同的 校验集 对查询的影响
首先创建一个 校验集 为 utf8_general_ci
的 数据库,随后创建一张 表,并向 表 中插入多条数据,然后条件查询其中的结果
mysql> create database if not exists test1 collate utf8_general_ci;
mysql> use test1;
mysql> create table t (name varchar(32));
mysql> insert into t values ('a');
mysql> insert into t values ('A');
mysql> insert into t values ('b');
mysql> insert into t values ('B');
mysql> insert into t values ('c');
mysql> insert into t values ('C');
mysql> select * from t1 where name='a';
注意: 在对数据库进行操作前,需要先 use database_name
指明数据库
执行结果如下
注:where
是条件查询语句,可以用来指定查看规则,这里的 where name='a'
是查询 t
表中 name
为 a
的行信息
可以看到最终查询结果为 a
、A
,明明只要求 name='a'
,但最终查出来 A
,这是因为 utf8_general_ci
校验集 在进行校验时,是 不区分大小写 的
接下来看看 utf8_bin
校验集 对查询的影响
同样的创建数据库、创建表、插入数据、查询结果
mysql> create database if not exists test2 collate utf8_bin;
mysql> use test2;
mysql> create table t (name varchar(32));
mysql> insert into t values ('a');
mysql> insert into t values ('A');
mysql> insert into t values ('b');
mysql> insert into t values ('B');
mysql> insert into t values ('c');
mysql> insert into t values ('C');
mysql> select * from t1 where name='a';
可以看到,当 校验集 为 utf8_bin
时,查询结果是 区分大小写 的,最终查出了 a
这行数据
这里想说的是 不同校验集对查询结果是有差异的,需要结合具体业务场景选择校验集,编码集也是如此,不过一般情况下都不需要指定,使用默认的 utf8
和 utf8_general_ci
就行了
3.查看数据库
可以查看当前 MySQL
中已经创建了哪些 数据库
mysql> show databases;
当然也可以直接去 /var/lib/mysql
目录中查看
cd /var/lib/mysql
ll
存在这么多 数据库,如何知道自己当前处于哪个数据库中?
通过 database()
函数查看,这是一个函数,可以用于查看当前所处 数据库(个人猜测是调用了显示当前所处路径的相关接口实现)
mysql> select databases();
可以看到当前处于 test2
数据库中
如何查看 数据库 创建时的详细信息?
可以通过 show
进行查看,这里看看 test2
的创建信息
mysql> show create database test2;
// 或者
mysql> show create database test2 G
在后面加上 G
是为了格式化显示,让显示结果更加清晰
可以看到创建 test2
时的详细信息,比如指定的 字符集 和 编码集
/*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */
是什么意思?
这是句话是为了确保兼容性,因为在MySQL 4.1
之前,是无法在创建数据库时指定字符集和校验集的。所以这句话的意思是 如果MySQL
版本高于4.1
就加上这句话(创建字符集和校验集),否则就不加
4.修改数据库
数据库 可以修改很多东西(不只是编码格式),修改相关的语法如下
修改数据库名(MySQL 5.1.23
之前的版本适用)
RENAME DATABASE old_name TO new_name;
出于安全考虑,这个语法在当前的 MySQL
中已经不被支持了,因为 数据库 更名后,上层应用在使用 数据库 时会受到影响,比较稳妥的更名方法是 备份数据、新建数据库(更名)、导入数据(还是不推荐改名)
数据库轻易不要删除,数据库轻易不要改名
修改编码格式
ALTER DATABASE database_name [alter_spacification [,alter_spacification]...];
之前的 test2
数据使用的是 utf8
和 utf8_bin
,现在将其修改为 gbk
和 gbk_chinese_ci
mysql> alter database test2 charset=gbk collate gbk_chinese_ci;
除此之外,还可以修改 数据库所有者、启用/禁用数据库的自动提交等,这些东西需要结合后面的相关知识理解,这里就不再阐述
5.删除数据库
在 Linux
中,数据库 的本质就是一个 目录,因此 数据库 也是可以删除的,具体语法如下
DROP DATABASE [IF EXISTS] database_ name;
在这里 IF EXISTS
表示检查 数据库,存在才删除,删除不存在的 数据库 是会报错的,加了 IF EXISTS
后可以避免报错,转为警告
删除之前创建的数据库
执行删除之后的结果:
- 数据库内部看不到对应的数据库
- 对应的数据库文件夹被删除,级联删除(里面的数据表全部被删)
注意: 不要轻易删除数据库,数据库删除后很难恢复
为了避免误删 数据库,通常需要将 数据库 进行备份
6.数据库的备份与恢复
有一种简单粗暴的备份方式:直接将对应的目录打包,需要使用时解压至指定目录即可
这种方式 严重不推荐,原因是恢复时可能会出现问题,假设恢复至版本更低的 MySQL
中,会出现各种奇怪的问题,毕竟老版本没有新特性,还有就是这种做法太暴力了
推荐使用 MySQL
提供的备份工具 mysqldump
进行备份,具体语法为
mysqldump -u 用户 -p -P 端口 -B 待备份的数据库 > 目标路径(含目标文件)
比如把之前创建的 test2
数据库进行备份
注意: 如果有人在使用该数据库,需要先断开连接
mysqldump -u root -p -P 8080 -B test2 > /home/Yohifo/MySQL/test2.sql
回车并输入密码后,对应的数据库就备份成功了
看看备份文件是什么内容
cat test2.sql
其实就是一些 数据库 的配置信息 + 执行过的 SQL
语句
有备份就有恢复,恢复的语法如下
mysql> SOURCE 路径;
接下来先删除 MySQL
中的 test2
数据库
mysql> show databases;
mysql> drop database if exists test2;
mysql> show databases;
然后从 Linux
中读取备份文件,并进行恢复
mysql> source /home/Yohifo/MySQL/test2.sql
一瞬间执行了很多条语句,并且都是成功状态,接下来查看是否存在 test2
数据库
数据库 test2
已经成功恢复,包括其中的 表 和 表中的数据 都已经恢复了
所以备份与恢复 数据库 还是比较简单的,只需要从 MySQL
中获取备份后的 sql
文件,可将该备份文件进行传输,其他 MySQL
客户端只需要 source
即可轻松恢复整个 数据库
可以只备份 数据库 中的 表,语法如下
mysqldump -u root -p -P 端口 -B 数据库名 表名1 > 目标路径(含文件)
也可以同时备份多个 数据库
mysqldump -u 用户 -p -P 端口 -B 数据库1 数据库2 数据库3 > 目标路径(含目标文件)
语法中的
-B
含义是什么?
-B
表示备份时,将创建数据库的语句也进行了备份,恢复时可以一键恢复;但如果不加-B
选项,恢复时就需要先创建数据库,再use
数据库,然后才能source
7.查看数据库的连接情况
MySQL
支持多用户访问,可以通过指令查看当前的使用情况
mysql> show processlist;
支持 本地连接 和 远程连接(需要进行设置)