【MySQL】库的操作

前言

        hi~，大家好呀！欢迎来到我的MySQL学习记录笔记~。注意，我使用的MySQL版本为5.7.41，平台为Linux平台上的哦。

        本篇笔记重点了解一下数据库的基本知识以及sql语句的分类，重点是对于数据库的定义操作的学习。（这其中的字符集和校验规则尤其注意）

目录

数据库基础

1.什么是数据库

2.MySQL的架构

3.SQL分类

1.创建数据库

数据库的字符集、校验规则：

1.存入数据格式-charset(character)

2.取出数据格式-collation

2.查找数据库

3.修改数据库

4.删除数据库&&备份和恢复数据库

备份数据库：

删除数据库：

恢复数据库：

---

数据库基础

1.什么是数据库

        以Linux下环境为例，我们需要理解什么是一个数据库。

简单的理解上面一个数据库之间的关系。（以Linux下为主）

        Client为mysql /bin/mysql 为客户端。

        MySQL为/sbin/mysqld 为服务端。

        DB为数据库，相当于Linux下的一个目录。

        表：相当于一个目录下的文件。

所以，mysql数据库本身就是一个网络服务器的体系。

         狭义上，我们以保存数据的文件为数据库。（Linux云服务器存储的默认地址为\var\lib\mysql）。但是广义上来说，mysql的客户端 + mysqld服务端 + 数据文件三者加起来才叫做数据库。

        因为这三种组合起来，他们会提供较为便捷数据存取服务的软件集合的解决方案。-mysql数据库。

        既然是存取文件，操作系统本身不是存在文件系统吗？为什么还需要数据库呢？

        文件或者数据库，都可以进行数据的存储。
        如果使用文件 ，数据内容的管理工作，需要程序员自己做！。-自己实时的编写代码去做。

        数据库的本质：是对文件的内容提供基本的内容操作，不用程序员（用户）手动的进行数据管理。-（我们的磁盘文件，在OS层面也是被管理的--管理的侧重点不同）

2.MySQL的架构

         上图可以简单概括如下：

mysql客户端连接mysql服务端。

        连接层：保证数据库系统的健全，安全策略等。

        根据传输的数据进行语法分析和性能优化。

        存储引擎层。关心要干啥，mysql支持插件式的。（所谓插件式就是类似C++中的定义的类，然后组织起来，利用比如继承的关心进行调用）

        操作系统提供API调用底层文件。

        对于存储引擎（engine）：数据库管理系统如何存储数据，如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。-实现

        站在操作系统理解MySQLServer，就是一个应用进程的代码，站在网络的角度就是一个应用层协议的一种。

3.SQL分类

        SQL是一种结构化查询语言。应用于各大数据库中，利用语法规则对文件数据进行操作。（会被mysqld进行一个解析使用）

        对于SQL的不同操作来说，通常会分为如下的三类：

DDL：数据定义语言-维护存储数据的结构

        通常涉及到建立某种数据的结构。比如建库，建表，修改库，修改表结构（没有涉及到任何用户数据）。涉及到的关键字有：CREATE DROP ALTER等。

DML：数据操作语言-对数据进行操作

        此时并没有发生改变表的结构或者库的结构，而是直接的对数据内容的操作。

        此时还衍生了另一种DQL：数据查询语言（SELECT）。DML涉及到的关键字有（INSERT, UPDATE, DELETE）

DCL：数据控制语言-负责管理权限和事务

        就好像Linux中的用户管理一样，我们也需要对用户的权限等进行控制等。涉及到的关键字有（GRANT, REVOKE）

        另外，除了上面的关键字外，SHOW也被经常使用，用于查看当前结构下的子结构。

        在创建数据库之前，我们需要连接到mysqld服务端，使用mysql客户端进行连接。

mysql [-h ip -P 3306] -uroot -p

        []中是可以省略的部分。ip是对应mysql服务端（mysqld）的公网IP。大写的P指向的是其默认端口（默认端口为3306），u是指明用户，小写p表示输入密码，密码可以跟在后面，但是就明文显示了，直接enter在下面输入密码即可。

         但是在本机通常直接执行下面这一步即可：

mysql -uroot -p

         如果没有连接上，确保自己的mysql服务是否启动。

Linux上是systemctl start mysqld进行启动（restart重启，stop停止服务）

windows上是net start mysql即可。

1.创建数据库

        创建数据库的语法格式：

create database [if not exists] db_name [create_specification [, create_sepcification] ......];

参数解释：

        database：表示操作的是数据库。

        if not exists：表示没有才进行创建

        db_name：数据库的名字

        create_specification:
                [default] character set charset_name(utf8)
                [default] collate collation_name(utf8_general_ci)

                其中character set可以变为charset= ？collate也可以变为collate=？

                表示手动设置编码（输入）校验规则（输出），如果不设置默认继承mysql配置的。

注意：[]里可以不用写，不用写后面的配置默认继承mysql环境中的配置。

        注意，这里针对Create_specification（创建规范）进行一个说明。

数据库的字符集、校验规则：

        我们知道，在计算机的底层保存的数据都是二进制。那么我们想要存入我们人类读得懂的数据（文字）或者取出就必须制定出一定的编码，规范二进制的输入和取出，从而得到想要的数据。

        现在世界上常见的编码比如utf-8。

        数据库自然也是一样。不过，由于市面上存在多种编码，并且可能对应不同的场景，所以设计了两套：编码（字符集）和校验规则的指定。

1.存入数据格式-charset(character)

        在mysql中，使用命令 show variables like 'character_set_database'就可以查看当前数据库默认支持的对数据库的编码格式。

注意，variables是一个变量，存在于mysql中，类似于程序环境中的环境变量，查看一些全局设置的变量。

       可以看到，当前在我的MySQL下默认支持的编码格式为utf8.（因为一开始在my.cnf文件里面进行了配置）。我们也可以经过模糊查询，查找当前MySQL其他部件所默认支持的编码格式。

        show variables like 'character_set%';

        我们也可以查看在当前MySQL下所能支持所有的编码格式，以及对应默认的校验规则。

        show charset;

2.取出数据格式-collation

        同理，使用指令 show variables like 'collation_database'就可以查看当前数据库的默认校验规则。（校验规则就是取出数据格式的规则）

         也可以进行模糊查询，查看其他默认的校验规则：show variables like 'collation_%';

        也可以查看当前数据库所支持的所有校验规则，以及对应的编码格式。

        show collation;

        对于编码相同但是校验规则不同之前是存在差异的。

        比如utf8的如下两个校验规则：

        可以看到utf8_general_ci是默认的，但是校验规则utf8_bin不是默认的。他们之间存在什么区别吗？

        utf8_general_ci校验的时候不区分大小写的，utf8_bin却区分大小写。

        可以以如下进行一个举例：（注意涉及建表操作，理解即可）

-创建数据库并且查看数据库优化语句：

 -建表简单插入数据并且查找：

2.查找数据库

        首先，我们创建完数据库的时候，可以看一下MySQL为我们做的一个优化后的语句，使用语句：

show create database 数据库名

        比如我们上面创建的数据库test_1：

        并且，我们也可以查看在当前账户下创建的所有数据库：

show databases;

        同样的，我们可以在数据库保存的文件中（默认为/var/lib/mysql目录下），查看到数据库实际上就是一个文件目录。 

3.修改数据库

        修改数据库就是修改数据库的创建时候的属性，属性的话只有编码和校验规则了。我们使用关键字alter即可。

alter database 数据库名 [charset=?(character set ?) collate=?(collate ?)]

        当然，也可以只设置编码格式，这样校验规则就是默认的那个一个，可以通过show charset进行查看编码默认的校验规则哦~

4.删除数据库&&备份和恢复数据库

        在备份和恢复数据库之前，我们可以先了解一下一个命令，可以查看当前有多少人进行连接mysqld，并且最后一次的操作具体是什么。

show processlist;

        比如一开始只有一台主机起来，现在两台进行连接，就识别出来了。 

备份数据库：

        实际上，从上面我们就了解到，我们的数据库实际在Linux就是一个目录。所以我们将此目录cp -r不久是备份了一份了吗。

        但是不要这么干，因为本身数据库服务程序就是在操作系统之上的，你绕开这一层那么数据库服务该怎么办呢？

        所以，在备份前，我们需要保证mysql的服务是启动着的，使用mysql专门准备的另一个客户端-mysqldump，它是专门进行备份的，将我们想要备份的数据库或者只是一部分数据以sql文件的形式保存下来。

mysqldump -P端口号 -u用户名 -p密码 -B 备份数据库名  >> sql文件路径

        >> 相当于追加插入，如果不追加重定向的话会直接输出到屏幕上的。

        -B表示是要备份创建数据库的。如果只想备份表的话，可以不加B，后面带上数据库名和表名即可。

        我们还是使用test_1举例，我们知道在上面我们修改了此数据库的校验规则，里面存在表test，存在两个数据A和a，现在我们将它备份下来，保存到test.sql文件中去。

        可以发现，建库建表操作都有，均使用sql语句帮我们保存了下来。

        另外，只想保存表的数据的话可以这么做：

         所以数据库备份：1、对数据做备份；2、对操作语句做备份。操作语言备份，可以查看上下文，并且更加灵活，效率也不低的。

删除数据库：

        现在我们想把数据库test_1删掉，很简单，利用关键字drop即可。

drop database 数据库名

        注意，删库的话里面所有的数据均会被删除，所以删库的时候一定要三思，三思，三思。

         比如，现在我们将test_1数据库删除了。虽然删除了，但是我们之前备份了一份test_1数据库，那么我们如何去恢复数据库呢？

恢复数据库：

source sql文件路径;

        只需要执行上面这句即可（如果在之前备份数据库的时候没有加-B选项的话需要自己先建立数据库，然后在use进行使用哦）

         可以发现执行了很多sql语句后数据恢复回来了。