[MySQL]MySQL数据库的介绍和库相关操作

目录

一、数据库介绍

1.什么是数据库

2.为什么使用数据库

3.数据库的操作运行逻辑

4.MySQL架构

5.SQL语句的分类

二、数据库的操作

1.数据库的连接

2.数据库的操作

创建数据库 

查看数据库

 显示数据库的创建语句

删除数据库

修改数据库

3.字符集和校验集

查看系统默认的字符集和校验集

查看数据库支持的字符集和校验集

不同校验规则的影响

4.数据库的备份与恢复

备份

恢复

5.查看连接情况 

---

一、数据库介绍

1.什么是数据库

 

        在我们安装数据库的时候，会给我们安装两个应用程序，mysql客户端和mysqld服务器。mysql数据库本质实际上是一个基于C/S模式的一个网络服务，上图中也可以看到3306号端口的tcp网络服务。那么他的作用是什么呢？mysql一整套服务可以给我们提供数据存取的服务。也就是说把我们的数据按照特定的结构和形式存放到磁盘文件，同时提供了将这些特定结构和形式的文件进行解析读取呈现给我们的能力。

        我们一般口语上说数据库指的是，在磁盘或者内存中按照特定结构和组织形式的数据文件。而再谈数据库服务的时候就是mysqld，而并非是mysql了。

2.为什么使用数据库

        上述我们说了数据库也是将数据存放到磁盘文件当中，那么为什么不直接存放到文件呢，而是使用一层数据库变为特定格式的文件在放到磁盘道中？

        一般的文件确实为我们提供了数据存储的能力，但是文件没有提供非常好的数据管理能力，不是操作系统不好管理，操作系统管理文件只是管理文件的一些属性，不对内容进行管理。而是对于用户来说不好管理。例如一个场景：有1万条登录记录，当我们要统计在北京登录的信息，怎么办呢？难道要一条一条的去过滤吗？程序员写一个过滤程序也是可以的，但是每次都要写吗？这也不方便啊，而数据库就提供了数据的管理能力，可以很快的将我们想要的信息提取出来。 所以数据库存在的意义就是对数据内容存储的管理提供一套解决方案。

        文件的保存还有一些其他的缺陷：文件存储不安全、文件不利于数据查询和管理、文件不利于存储海量的数据、文件在程序中控制不太方便（文件的操作）。

3.数据库的操作运行逻辑

4.MySQL架构

        MySQL是一个可移植的数据库，几乎在当前所有的操作系统下都是可以运行的，虽然各种系统在底层的实现方法对于MySQL有所不同，但是MySQL基本能够保证在各个平台上的物理体系结构的一致性。

• 连接层主要负责处理客户端的连接请求，进行客户端的身份验证以及管理多个连接的操作。
• 服务层主要分为查询缓存、解析器、优化器、执行器4个部分。查询缓存是将已经执行过的查询语句和结果进行缓存，当收到请求的时候，首先在缓存中查询是否有相同的请求。解析器是对客户端发出的SQL语句继续语法和词法分析，对判断准确性。优化器就是将用户输入SQL语句进行一些优化。执行器就是按照优化器生成的执行内容，调用存储引擎的接口执行SQL语句。
• 存储引擎层主要负责数据的存储和读取操作，MySQL支持多种存储引擎，不同的引擎有自己的特点和优势。MySQL的核心就是插件式存储引擎。
• 系统文件层主要涉及数据库文件的存储和管理，包括存储数据库的数据和文件，还有日志文件等内容。

查看存储引擎的指令：show engines

5.SQL语句的分类

• DDL数据定义语言，用来维护存储数据的结构。代表指令：create、drop、alter等
• DML数据操纵语言，用来对数据进行操作。代表指令：insert、delete、update等
• DQL数据查询语言，是DML的一个分支。代表指令：select。
• DCL数据控制语言，主要负责权限管理和事务管理。代表指令：grant、revoke、commit等。

二、数据库的操作

1.数据库的连接

        mysql有很多选项，在登录的时候-h表示的是登录部署mysql服务主机的IP地址，-P表示的是要访问的端口号，-u表示的是登录的用户名称，-p表示密码登录。所以也可以看出mysql也是可以跨网络进行登录的。下面是一个完整的数据库连接操作指令，但是一般可以省略-h和-P，在配置文件中有设置，如何省略的话默认是本地连接3306端口号。

mysql -h 127.0.0.1 -P 3306 -u root -p 123456

2.数据库的操作

创建数据库 

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification];

案例：create database test_database1 charset=utf8 collate utf8_general_ci;

        一般来说我们创建数据库的时候都是使用create database xxxx;即可，而IF NOT EXISTS字段表示的是，如果数据库不存在的时候在创建，存在就会报一个警告。如果不带该字段的话，数据库存在还创建会直接报错。create_specification字段表示的是，可以设置数据库的使用的字符集和校验规则。当我们没有指定的时候，就默认字符集是utf8，校验规则是utf8_general_ci。

        在linux系统下，我们创建一个数据库在文件系统的视角下来看的话，就是创建了一个名字为db_name的一个目录，存在于/var/lib/mysql目录下。而且我们如果在该目录下创建和删除数据库目录文件的时候，在show databases;查看数据库的时候也会收到影响，但是一定不要这样做，因为在创建数据库的时候并非只创建了一个目录，还有很多其他的操作的。

查看数据库

show databases;

 显示数据库的创建语句

show create database db_name;

        这里会显示数据库创建时候的创建语句，对于数据库名称带用反引号进行修饰是防止数据库名称和关键字恰好一样，同时MySQL建议关键字使用大写表示，只是建议。后面的40100开头的话，不是注释，而是表示如果数据库的版本大于4.01就执行该语句，80016也是如此。

删除数据库

DROP DATABASE [IF EXISTS] db_name;

案例：drop database test_database1;

        删除数据库只会，对应/var/lib/mysql目录下的数据库文件夹也会删除，级联和里面的数据表也会删除，所以在实际开发的时候不要轻易的删除数据库，在删除之前可以先进行数据库的一个备份操作。

修改数据库

ALTER DATABASE db_name [alter_spacification]

案例：alter database test_database1 charactor set gbk;   //charactor set 和charset是一样的

        对于数据库的修改来说，一般都是修改数据库的字符集和校验规则，当修改字符集的时候，校验规则也会修改成字符集默认的校验规则。

        也可以修改数据库的名称，但是这种修改一般会收到权限和数据库系统版本等因素的限制。而且会有一些安全风险，如数据丢失或者于存储引擎不兼容等问题。对于目前版本的MySQ是不支持该操作的。

3.字符集和校验集

        在创建数据库的时候，有两个编码集的设定，一个是字符集另一个是校验集。数据库字符集表示的是数据库未来存储数据的编码规则。数据库校验集用于数据库在进行字段比较的时候使用的编码格式，通俗来说就是查询数据库内容的时候用什么编码格式将二进制数据翻译一下。一个是用于设置存储时候的编码规则，另一个是用于设置数据库自身访问数据时候的编码规则，那么用什么方式存储就应该用什么方式来解析读取，所以说字符集和校验集之间一定存在很强的关系。

查看系统默认的字符集和校验集

默认字符集：show variables like 'character_set_database';

默认校验集：show variables like 'collation_database';

        如果我们想要查看当前数据库的字符集的话直接查看show create database db_name即可。

查看数据库支持的字符集和校验集

字符集：show charset;

校验集：show collation;

不同校验规则的影响

        使用uf8编码规则的时候，使用utf8_general_ci的校验规则和使用utf8_bin的校验规则进行操作SQL语句返回的结果是不一样的。下面的一个案例是插入一堆英文字母，并查询字母A，观察返回的情况。utf8_general_ci规则下返回的是不区分大小写的，会返回a和A两种字符，而utf8_bin是区分大小写的校验规则，只会返回a字符。

创建数据库、表以及插入数据：

查看查询数据结果：

 

4.数据库的备份与恢复

        对于数据库的删除来说，是非常危险的，因为删除了只会就无法进行还原了，而且数据库的用途一般都是存储大量且重要的数据，所以我们在删除之前应该先将数据库进行备份操作处理。mysql体系中为我们提供了一个mysqldump程序来帮助我们实现数据库的备份操作。这样我们就不用找到数据库文件进行备份了，而且这样备份的话也会有问题，一些数据库属性字段信息都没有保存上，备份好了也没法恢复。还有就是可能mysql的版本不同，备份后的数据库想要放到新版本的mysql下也不好弄，所以提供了mysqldump程序帮我们解决这些问题。

备份

mysqldump -P3306 -u root -p 123456 -B 数据库名称 > 数据库备份存储的路径

以上面的test1数据库为例进行一个备份操作案例：

        从备份文件的内容可以看出，备份文件内部记载了数据库的名称、数据库的创建语句、见表语句以及对表进行操作的各种语句，还有该数据库的版本信息等内容。

        这里的-B选项是备份的数据库内容里面带不带数据库的名称，如果不带的话，那么恢复的时候，需要我们先创建一个空的数据库，然后再将备份的内容导入进入。带的话，恢复数据库的时候，就会自动帮我们按照文件中的数据库名称创建一个数据库，然后将文件内容全部导入到新建的数据库中。

        同时也是可以同时备份多个数据库到一个文件中的，到时候恢复的时候，就会按照文件内容中的记载，一下子创建多个数据库了

mysqldump还支持单独备份一个表的内容：

mysqldump -P3306 -u root -p 123456  表名称 > 表备份存储的路径

恢复

source 备份文件路径

5.查看连接情况 

show processlist;

         通过该指令可以查看到当前有哪些用户使用了该ip地址下的mysql服务。防止了某些不正常的登录，导致数据库被入侵，或者数据库运行变得非常慢，也可能是连接使用mysql服务的人太多了。