目录
前言:这一篇介绍MySQL库的操作,包括对字符集和校验规则的介绍,库的创建、查看、修改、删除,备份和还原等。
一. 创建数据库
1.字符集和校验规则
字符集指明在存数据时,要按照哪种特定的编码来进行存储。
校验规则指明在取数据比较时,采取的校验编码。
(1)查看系统默认字符集以及校验规则
字符集:show variables like 'character_set_database';
校验规则:show variables like 'collation_database';
MySQL的默认字符集一般是utf8,默认校验规则就是utf8_general_ci,这两个是配套的。
下面再来看看以character_set_开头和以collation_开头的语句:
show variables like 'character_set_%';
show variables like 'collation_%';
%: 模糊匹配,以前面开头的语句都会显示出来
通过这个图可以发现,client客户端的编码是utf8、建立的链接connection也是utf8,剩下的那些也都是utf8(除了filesystem,这个不考虑)
再看校验规则,也都是与utf8所配套的默认校验规则。
一般指明了字符集,校验规则就会自动配置与其匹配的。
(2)查看数据库支持的字符集和校验规则
字符集:
show charset;
这里可以看到在对应的字符集后面有一个Default collaton,这个就是该字符集匹配的校验规则。
校验规则:
show collation;
2.创建数据库
字符集和校验规则是给数据库和数据库所对应的表来用的,根本就是给表用的。
mysql大小写不敏感,建库和建表,建议都带上``(Esc下面的键,这里不带这个经过mysql后也会自动带上的,不过如果建库的名字是关键字,比如create,那就必须带上``)。
创建数据库不指明任何的字符集和校验规则是主流。
这里为了为了学习,我们可以指明字符集和校验规则。
语法:
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name
说明:
① [] 是可选项
② CHARACTER SET: 指定数据库采用的字符集
③ COLLATE: 指定数据库字符集的校验规则
例子:
① 先不指明字符集和校验规则,创建一个数据库onetest.
然后我们进入/var/lib/mysql目录中,查看该创建的数据库:
这里可以看到在创建数据库onetest后,目录中多出了onetest这个目录。查看一下onetest内有什么,发现有一个db.opt,cat查看一下,就发现里面正好是其 字符集和校验规则。
② 再指明字符集为gbk,创建数据库twotest.
这里可以看到,twotest对应的db.opt的字符集就变成了gbk,而校验规则也变成了gbk配套的校验规则。
character set gbk
character set=gbk
charset gbk
charset=gbk
上面这四种写法的意思都是一样的,都是让创建的数据库字符集变为gbk。
③ 下面我们字符集和校验规则都设置一下:
这里我们可以发现,我们设置字符集为utf8,校验规则为utf8_bin,cat时就变成了这两个,但是要注意,这个字符集和校验规则也是配套的,一个字符集可以配套多个校验规则。
3.校验规则对数据库的影响
设置数据库的字符集和校验规则,本质上影响的是对应的数据库内部的表所对应的字符集和校验规则。
下面我们来测试utf8_general_ci和utf8_bin这两种与utf8匹配的校验规则:
(1)utf8_general_ci
首先创建gc_db,校验规则为utf8_general_ci:
进入该数据库,并创建一个叫person的表,有name的属性,desc查看一下该表:
接着,向表内插入小写和大写aAbBCc:
show查看表,select查看内部数据:
接下来我们分别选择a,b,c的数据:
这里我们发现,我们要小写的数据,但是大写的数据也得到了。
(2)utf8_bin
首先创建bin_db,校验规则为utf8_bin:
接下来,进入该数据库,与上一个gc_db进行相同的操作,创建相同的表,并插入相同的 数据。
下面,我们同样selec选择小写的数据:
这里,我们就发现只要小写的数据。
同样都是utf8的字符集,但是校验规则分别是utf8_general_ci和utf8_bin。utf8_general_ci这种校验规则筛选出来的数据是忽略大小写的,utf8_bin这种校验规则严格按照相等的方式筛选。
下面再来继续通过排序验证一下:
select database(); 可以看我们当前在哪个数据库里:
插入的顺序是aAbBCc,排序后变成ABCabc,这里很明显是升序排列:
接下来进入gc_db:
排序后变为aAbBCc:,这里C在c的前面是因为这种校验规则不区分大小写,而原表中C在c的前面,所以排序后C就在c的前面:
这两种校验规则的排序后也是不同的,因为排序也是需要比较的。
二.操纵数据库命令
查看数据库:
show databases;
显示创建语句:
show create database 数据库名;
说明:
① MySQL 建议我们关键字使用大写,但是不是必须的。
② 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
③ /*!40100 default.... */ 这个不是注释,表示当前mysql版本大于4.01版本,就执行这句话
删除数据库:
drop database 数据库名;
执行删除之后的结果:
对应的数据库文件夹被删除,级联删除,里面的数据表全部被删
注意:不要随意删除数据库
修改数据库:
alter database 数据库名 字符集(校验规则)
对数据库的修改主要指的是修改数据库的字符集,校验规则
gc_db数据库的字符集为utf8,这里我们修改它
修改为utf8mb4
cat就发现字符集变成utf8mb4了,校验规则也跟着变为与其配套的utf8mb4_general_ci
如果修改校验规则也是如此。
数据库备份:
mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径
-B:要备份整个数据库
如果备份一个数据库时,没有带上-B参数, 在恢复数据库时,需要先创建空数据库,然后使用数据库,再使用source来还原
这里-P3306也是可以忽略不写的,密码没有也可以不写:
这样写即可:mysqldump -uroot -p -B 数据库名 > 数据库备份存储的文件路径
如果备份的不是整个数据库,而是其中的一张表:
mysqldump -P3306 -u root -p 密码 数据库名 表名1 表名2 > 数据库备份存储的文件路径
只要去掉-B选项,并且加上要备份的表名即可
如果要同时备份多个数据库:
mysqldump -P3306 -u root -p 密码 -B 数据库名1 数据库名2 ... > 数据库存放路径
只要多增加几个数据库名即可。
如果直接mysqldump,不重定向,就只会显示出来,正确做法是重定向到一个文件中:
这里将bin_db重定向到bin_db.sql中,vim一下看到的是我们对其操作的命令的备份。
数据库备份有两种:① 对数据做备份 ② 对操作语句做备份
上面那个就是对操作语句做备份。
数据库还原:
source 备份的路径
下面我们删掉bin_db:
/root/mysql/bin_db.sql,这是我们刚才备份所存的路径,source一下还原:
这就可以看到我们刚刚删掉的bin_db经过备份的还原,还原出来了。
查看连接情况:
show processlist;
这里可以看到只有root用户连接该数据库,并且在使用bin_db这个数据库
下面我们再增加一个用户:
这里就多了一个用户。
下面再让该用户使用一个数据库:
这里该用户使用的数据库就从NULL变成了gc_db。
可以告诉我们当前有哪些用户连接到我们的MySQL,如果查出某个用户不是你正常登陆的,很有可能你的数据库被人入侵了。如果发现自己数据库比较慢时,可以用这个指令来查看数据库连接情况。