MySQL库的操作

目录

一 字符集和校验规则

为什么

是什么

怎么办

影响

二 操作的分类

三 创建数据库

本质

语句

显示操作

四 删除数据库

本质：

语句

四 查看数据库

本质（在做什么）

语句

其他

五 修改数据库

本质

语句

六   数据库的备份

为什么能被备份

条件

操作

为什么这样备份

七 数据库的恢复

为什么

操作

八 查看数据库连接

语句

具体信息

应用

---

 

一 字符集和校验规则

1.为什么

为什么在讲数据库的操作之前引入字符集和校验规则这样的一个概念呢？

数据库本质上是为了便于对文件数据进行高效安全操作而设计的一个软件。

我们在了解数据库的操作之前，应该要先明确这些存储在数据库中的数据都是有对应的编码方式的，这也就决定了我们从数据库中取数据的时候也是有一定的编码格式的。也就是说，数据的存取都是要遵循特定的编码格式的。相应的，数据库设置了一套对应的字符集和校验规则，字符集是数据存储的时候的格式，校验规则是取数据的时候的编码格式，他们是匹配的。

比如，同样的存储的二进制代码，按照不同的类型解释，得到的值就是不一样的。

数据在被存储的时候是有特定的编码的，编码决定了我们未来的数据如何被存取，识别，对比。

2 是什么

概念：

我们在使用MySQL进行数据的存储的时候，有多种输入的方式：比如采取xshell的命令行形式，图形化界面操作，或者程序员手动录入，但是无论是哪一种输入方式，输入的数据都是需要有特定的编码来存储并且解释的。这里用来实现这个功能的就是字符集

同理，我们将来在取出数据的时候，其实是在查找、筛选。举个例子，如果我们是按照关键字进行筛选查找的话，就需要将筛选条件进行特定的转换，之后在数据库中按行进行条件筛选。而筛选的本质就是比较。这种将读取出来的数据转换成特定的格式的方式，叫做对应的校验规则。

字符集： 数据库的数据存储在数据库中的一种编码形式。

校验规则：取出数据库的数据的时候，与字符集相匹配的一种取数据的编码方式。

 

常见类型：

如果我们想查看数据库支持哪些字符集和校验规则，可以用以下的命令进行查看

查看字符集

show charset;

以上的表格包含的属性:名称 字符集 与字符集匹配的校验规则 

 

查看对应的校验规则

show collation;

 我们观察这个表，可以看出的一个对应关系是：字符集对应校验规则是一对多，校验规则对应字符集是多对一。并且对应的字符集有系统与之匹配的校验规则。（换句话说，我们指定了字符集就会由系统保证对应的校验规则与之匹配）

如果我们想查看目前支持的编码集的话，可以通过这样的命令进行查询。

show variables like `character_set_%`;
show variables like `collation_%`;

 

MySQL启动的时候，会存储各种各样的variables，这其实也是在数据库表内部存储着的，当mysql启动的时候，常用的variables值就会被load到进程的上下文中间，一旦被设置好，进程或者子用户就能使用了。有点像环境变量

一旦被设置好，创建库，创建表，查找（比较 筛选）的时候，就会使用我们设置的变量

(%是模糊匹配的意思）

3 怎么办

我们如何设置自己的数据库对应的字符集和校验规则呢？

① 通过配置文件进行设置。MySQL的配置文件

 我们cd到对应的mysql目录下，之后进入/etc/my.cnf的配置文件进行配置，我这边采用的服务默认是utf8编码格式的。

如果在该配置文件下设置了对应的编码格式之后，我们创建的数据库，表等就是对应的默认就是以这种utf8的方式的字符集和校验集合。

② 创建数据库的时候指定

我们在创建数据库的时候可以带上对应的编码格式的条件。（关于优先级的一个问题：我们如果在配置文件中设置好了之后，即使创建数据库的时候不指定，也会默认去使用配置文件中的。但是如果自己指定了，优先使用自己指定的）

那么我们如何指定呢？

设置字符集

mysql> create database db1 character set gbk;

设置校验规则

mysql> create database db2 collate utf8_bin;

还可以这样进行设置

mysql> create database db3 charset=gbk;
mysql> create database db5 collate=utf8_bin;

配置了之后我们在mysql的对应的数据库的.opt目录下就可以查看。也就是说如果是配置文件的方式的话，默认的就是在对应的.opt目录下添加了编码相关的信息。我们对数据库设置，也是这样设置的。

4 影响

虽然说我们不设置对应的字符集和校验规则的话，MySQL数据库系统中也会有内置的规则，一般是以utf8为主流。但是我们自己在配置文件中设置之后，就使之具有了确定性。数据库的所有编码都是可预测，可衡量，可确认的，这样未来即使数据库出现了乱码的问题，也能够确保不是数据库这一层的问题。

创建的字符集和校验规则实际上是给数据库中的表使用的。换句话说，在特定格式的数据库下面建立表，表的格式也就确定了。因为数据库相当于目录，起到一个存储数据表的作用，数据库中的表相当于文件，其中有对数据的真正的存储。

举一个例子，校验规则可能会影响大小写匹配。比如utf_general_ci就是一种大小写模糊匹配的方式，而utf_bin就是要严格区分大小写的。

比如我们创建两个数据库，分别采用这两种方式，对这两个数据库进行数据的插入，之后进行查找或者排序得到的结果是不一样的。

创建两个数据库db1,db2，然后分别在两个数据库中创建一个perosn的表，只设置一个属性列为name。进行筛选：1 全部筛选 2 设置筛选条件 3 筛选的时候排序

 

 得到了这样的结果，我们可以看出来确实是有影响的。

不设置任何条件进行筛选两个结果是一致的

如果按照name=‘a';的条件进行筛选的话，会根据校验规则进行大小写模糊匹配处理

如果是排序的话，模糊匹配的，先排完a再b类；而大小写敏感的则不是这样的；（排序的本质也是比较）

校验规则的影响一般是在比较的时候起的效果，utf8_bin能够忽略大小写的原因是ASCII码不一致。

因此，校验规则会对选择和排序造成影响

总结：

字符集和校验规则实质是一种数据库存取数据时编码的方式。

在配置文件下设置对应的规则，会影响默认创建的时候的数据库（不指定对应的规则）

数据表又是在对应的数据库下创建的，也可以用SQL语句来进行设置对应的字符集和校验规则。也就是说数据表的字符集和校验规则受到SQL语句或者数据库的影响

这样我们就理解了对应的字符集和校验规则，字符集和校验规则与表的关系，与配置文件的关系，如何理解配置文件（全局），如何理解配置对库的影响，库对表的影响。

二 操作的分类

在了解数据库的具体操作之前，我们需要了解到有哪几类操作。这样才方便我们之后知道操作是在做什么。

主要的数据库的操作就分为这几类

DQL (Data Query Language-数据查询语言) - Select 查询语句不存在提交问题。
DML (Data Manipulation Language-数据操作语言) - Insert、Update、Delete，实现数据的“增删改。”
DDL (Data Definition Language-数据定义语言) - Create、Alter、Drop，实现表格的“增删改”。
DTL (Transaction Control Language-事务控制语言) - Commit、Rollback事务提交、回滚语句。
DCL (Data Control Language-数据控制语言) - Grant、Revoke 权限语句。

对于DDL：主要理解就是对于数据库和表格的定义的时候会使用到的，定义出了对应的结构之后，我们才会有对应的具体的数据的操作。可以理解成先“搭架子”

对于DML：就是对于具体的数据的操作。（DQL是DML中特殊的一类，主要是用来查找）

对于DCL：其实有了DDL和DML之后，数据库，表的创建和数据的增删查改这样的基本功能已经实现了，但是还是有DCL，主要是考量了数据库的安全性等方面的原因。(非功能性）比如各种对应的权限，只允许对应的用户执行对应的操作。否则就会被拒绝。保护了数据库的安全。

在了解了字符集和校验规则这样的编码以及对应的操作语句之后，我们来了解一下数据库最基础的一些操作。

 

首先有几点细节方面的说明：

1 数据库的操作语句是大小写不敏感的，大小写通用（注意：但这并不意味着用户创建的表名称也是大小写不敏感的，如果对于自己创建的表或者库的名称，创建时是什么就是什么，是重要的）

2 反引号：· · 有时候创建数据库指定对应的名称的时候，会带上这样的标志符号。这样做的原因是为了防止创建的数据库的名称和数据库关键词发生冲突。

三 创建数据库

1 本质

本质上就是在mysql中建立一个目录。其实在mysql中是会记录自己曾经建立的目录的，并且对于这么多的目录，mysql的内部会维护数据库的相关属性和字段等来进行识别是数据库的目录而非普通目录。相当于Linux命令行操作的mkdir。

2 语句

CREATE DATABASE [IF NOT EXISTS] <数据库名>

[[DEFAULT] CHARACTER SET <字符集名>] 

[[DEFAULT] COLLATE <校对规则名>];

理解：

create database +对应的数据库名称就是最基本的一条数据库创建的语句

在这个基础之上，我们可以选择[IF NOT EXISTS]。这个标识如果不存在这个表才创建，提高了效率

之后还可以跟上一些其他的条件，比如字符集和校验编码的规则

示例：分别举例子最常见的和带上对应条件的

mysql> create database test1;

mysql> create database test2 charset=utf8 collate utf8_bin;

3 显示操作

如果我们想查看我们在创建数据库的时候，数据库对应的操作该怎么办？

在历史创建的数据库命令前面带上show 

我们的数据库创建语句最终会被解释成

CREATE DATABASE ·db1` 

如果是4.01版本以上的数据库的话，就会被解释成/**/内的语句

 （因为我们曾经在配置文件中设置过了对应的编码规则，所以这里就是默认的utf8）

四 删除数据库

1 本质：

同理，本质就是在mysql对应的目录下，把之前对应的数据库目录删除掉。相当于Linux命令行中的rm -rf

2 语句

DROP DATABASE [ IF EXISTS ] <数据库名>

理解 

数据库的删除最简单的就是drop带上对应的数据库的名称（删除数据库需要指定对应的名称，才能知道要删除谁）

示例：

mysql> drop database test1;

这样我们就删除了对应的数据库，不过这个操作要谨慎操作。一旦数据库被删掉了，相当于数据库的目录没了，那么数据库的文件和数据也就递归式地被删除了。也就是说，数据库一旦被删掉，数据表和表内存储的数据也就没有了。

四 查看数据库

1 本质（在做什么）

就是显示当前权限下所有的数据库。row和一条条数据分别对应

相当于是在Linux命令行下的ll

2 语句

SHOW DATABASES [LIKE '数据库名'];

理解

后面可以带上对应的数据库的名称，查找特定的数据库。也可以进行模糊匹配等。但是如果不指定的话，默认就是查找所有的

示例

mysql> show databases;

 

其他

那么我们如何查看自己在哪个数据库下？

mysql> select database();

其实这个命令相当于是Linux的pwd。

 我们切换数据库之后再进行查看，验证一下。

 （use+对应的数据库名相当于cd到对应的目录下）

五 修改数据库

1本质

修改数据库的本质实际上是修改对应的数据库的属性。

数据库的属性有很多，但是哪些属性是允许我们进行修改的呢（root权限比较大，能修改的很多，所以我们是在root身份下进行操作的）

2语句

ALTER DATABASE [数据库名]

{ [ DEFAULT ] CHARACTER SET <字符集名> |[ DEFAULT ] COLLATE <校对规则名>}

 理解

同样的，我们可以修改对应的数据库的字符集和校验规则。

我们可以只修改字符集或者同时修改字符集和校验规则。字符集只要被修改了，对应的校验规则也会匹配修改

示例

mysql> alter database db1 charset=gbk;

需要注意的是，数据库名不允许修改。（表名可以）

六   数据库的备份

为什么能被备份

数据库已经默认把曾经历史输入过的指令都记录下来了，就是为了备份。一个MySQL既然能够被备份，就有随时随地能够被备份的可能性。因此曾经的指令就会被记录。但是由于这样的操作过于繁多，所以数据库会有一些优化的（比如几条插入语句合并成一行这样）

条件

数据库想要备份有两个条件：1 MySQL对应的服务是启动的 2我要备份的话，我要有对应的权限。（以root登录的话，权限是最大的）

操作

[root@VM-4-9-centos mysql]# mysqldump -P3306 -uroot -p -B db1>demo1

数据库的备份需要在mysqldump的目录下进行备份

我们最好不要绕过数据库的dump直接进行目录的拷贝，最好还是利用数据库的dump等工具进行备份。

理解

-P 当用mysqldump刻画段向MySQL发起备份请求的时候，指明了让3306（数据库）来执行

-u 哪一个用户进行执行的？

-p 对应的密码认证

-B 要备份哪一个数据库。一般是要写出数据库的名称的。如果没有B选项的话，后续我们在恢复数据库的时候，首先需要去创建一个空的数据库，之后使用我们创建的这个空的数据库，再用source还原。最好带上-B。相当于是默认有一个create database的操作

我们的数据库备份的一些数据之前是默认打到显示屏上的，但是这样做没什么意义，所以就重定向到了一个demo1的文件中。

我们可以查看一下这样的一个文件

其他的操作

如何备份一张表

mysqldump -uroot -p db1 person > demo2.sql

 

没有-B选项 相应的要跟上数据库名字和对应的表的名字

如何备份多个多个表？

mysqldump -uroot -p db1 person1 person2 > demo2.sql

这样数据库的备份，实际上完成了什么工作呢？

备份数据库，实质上是备份对应的操作，并且在数据库的帮助下有对应的一些语句的重新优化。（执行语句的时候，数据库默认会带上对应的variables 这样MySQL服务端启动的时候，这些变量也被备份了）

这样做实际上我们在后续就已经可以还原对应的数据库，数据表的结构还有数据了。

为什么这样备份

语句有上下文，可读性比较好

效率不低下

我们可以在执行备份操作的时候指定对应的时间，表示只备份数据库区间内的数据

七 数据库的恢复

为什么

我们之前已经备份了对应的数据库，就可以通过对应的备份文件进行恢复了

操作

mysql> source /root/mysql/demo1

理解

source+对应的备份文件的路径。

结果 

 

八 查看数据库连接

语句

mysql> show processlist;

 

具体信息

我们可以通过查看数据库链接了解到当前有谁访问我的数据库，她的主机是什么，访问的是什么数据库，当前是query（查询）还是sleep（休眠），次数是什么，状态是什么，当前的指令是什么

应用

我们可以通过查看数据库连接情况。我们数据库发生异常的时候，可以通过这个命令来查看数据库的连接情况，这样子就可以看当前有谁在访问我的数据库了。