MySql数据库的操作

        本篇将详细的介绍关于 MySql 中数据库的操作，其中主要包括数据库的创建、字符集和数据流的校验规则以及如何操纵数据库，以及查看数据库，当前位于哪个数据库，数据库的修改，数据库的备份和恢复，以及查看当前数据库有哪些用户在链接。

        本篇的操作只是涉及数据库的操作，没有涉及数据库中表的操作。

        目录如下：

目录

数据库的增删

数据库的编码集、校验集

指定字符编码集、校验集创建数据库

查看数据库、查看位于哪个数据库

数据库的修改

数据库的备份和恢复

查看连接情况

数据库的增删

        创建数据库和删除数据的语法：

mysql> create database [if not exists] naem_db;
// 创建数据库，其中方括号内的可有可无

mysql> drop database naem_db;
// 删除数据库

        如下所示：

        如上所示，当使用命令创建出对应的数据库之后就可以使用 show databases 查到对应的数据库，删除之后就查不到了。同时创建数据库会在 /var/lib/mysql 中创建对应的文件，也就意味着我们可以在 /var/lib/mysql 中创建目录文件，这样就相当于创建了数据库，但是这种方式严重不推荐。

数据库的编码集、校验集

        对于数据库的编码必须是统一的，也就是存数据和取数据时的编码必需一致，这样才能正确的保存数据。

        而我们的数据库编码集，就是数据库存储数据时的编码格式；

        数据库的校验集：支持数据库进行字段比较时的编码，本质是一种读取数据库中数据采用的编码格式。        

        对于以上的两种集，对同一份数据必须一致；

        查看系统默认字符集以及校验集的命令：

mysql> show variables like 'collation_database';
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_0900_ai_ci |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.00 sec)

        如上所示，在使用 mysql 8.0 版本下的默认字符集的编码规则为 utf8md4，对应的校验集编码为 utf8md4_0900_ai_ci，对于每个版本的 mysql 默认编码与校验集都会有所不同。

        我们可以使用如下命令查看系统中所有的编码集和校验集：

mysql> show collation;
// 查看校验集

mysql> show charset;
// 查看编码集

指定字符编码集、校验集创建数据库

        我们创建的数据库既可以直接使用命令创建数据库，也可以使用在创建数据库的命令后面加入对应的指令，指定数据库的编码格式，如下：

mysql> create database d1 charset=utf8mb4;
Query OK, 1 row affected (0.00 sec)
// 使用指令指定对应的编码集

mysql> create database d2 character set utf8mb4;
Query OK, 1 row affected (0.01 sec)
// 使用另一种方式指定对应的编码集

mysql> create database d3 charset=utf8mb4 collate utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.00 sec)
// 指定对应的编码集和校验集

mysql> create database d4 charset=gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
// 指定对应的编码集和校验集


mysql> show create database d2\G;
*************************** 1. row ***************************
       Database: d2
Create Database: CREATE DATABASE `d2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)
// 查看d2 的编码集和校验集

        如上所示，创建数据库时可以指定对应的数据库格式，若不指定则为默认的编码校验格式。

        当我们使用的编码集相同，而校验集不同对于数据处理又会有什么不同呢？如下的 test1 和 test2 的编码集都使用 utf8mb4，而校验集则采用 utf8mb4_bin 和 utf8mb4_0900_ai_ci，我们将在这两个数据库中插入相同的表，相同的元素，来比较其校验结果，如下：

test1：

mysql> create database test1 collate utf8mb4_bin;
Query OK, 1 row affected (0.00 sec)

mysql> use test1;
Database changed
mysql> create table if not exists person (name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into person (name) values ('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into person (name) values ('b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name) values ('A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name) values ('B');
Query OK, 1 row affected (0.00 sec)

mysql> select * from person where name='a';
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select * from person order by name;
+------+
| name |
+------+
| A    |
| B    |
| a    |
| b    |
+------+
4 rows in set (0.00 sec)

test2:

mysql> create database test2 collate utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use test2;
Database changed
mysql> create table if not exists person (name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into person (name) values ('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name) values ('b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person (name) values ('A');
Query OK, 1 row affected (0.01 sec)

mysql> insert into person (name) values ('B');
Query OK, 1 row affected (0.01 sec)

mysql> select * from person where name='a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

mysql> select * from person order by name;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
+------+
4 rows in set (0.00 sec)

        如上所示，对于 utf8mb4_bin 会区分大小写，不管是在查询还是在排序中，而 utf8mb4_0900_ai_ci 则不会区分大小写，在查询还是在排序中，相同字母都是排在一起的。

查看数据库、查看位于哪个数据库

        我们常用的查看数据库指令就是 show databases ，这个指令是用来查看所有的数据库的.

        当我们想要查看自己位于哪个数据库下的时候，可以使用 select database() 命令。

        当我们想要查看创建数据库时的信息，可以使用命令 show create database name_db 如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| helloworld         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
7 rows in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| helloworld |
+------------+
1 row in set (0.00 sec)

mysql> show create database helloworld \G;
*************************** 1. row ***************************
       Database: helloworld
Create Database: CREATE DATABASE `helloworld` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

数据库的修改

        我们可以使用 alter 命令对我们的数据库进行修改，如下：

        如上所示，使用 alter 命令修改了数据库的编码集。

数据库的备份和恢复

        在数据库中当我们想要对某些数据库进行大的修改的时候，一般建议先将之前的一份数据库给备份起来，保证之后想要使用的时候能使用得到（虽然备份可以直接在对应的目录下进行拷贝备份，但是这样并不建议，如果之后更换版本之后，很可能导致原来备份的数据使用不了）。备份的操作如下：

语法：
 mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

如下：
root@hcss-ecs-1078:/home/JZhong/MySql# mysqldump -P3306 -u root -B -p helloworld > helloworld.sql
Enter password: 

root@hcss-ecs-1078:/home/JZhong/MySql# ll
total 12
drwxr-xr-x 2 root   root   4096 Aug 26 15:20 ./
drwxr-x--- 7 JZhong JZhong 4096 Aug 24 14:59 ../
-rw-r--r-- 1 root   root   2189 Aug 26 15:20 helloworld.sql


// 备份的不是整个数据库，而是其中的几张表
mysqldump -u root -p 数据库名 表名1 表名2 > 数据库备份存储的文件路径

// 同时备份多个数据库
mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据
库，再使用source来还原。

        当我们想要恢复备份的数据时，我们可以使用如下指令：

mysql> source 文件目录

// 先删除
mysql> drop database helloworld;
Query OK, 1 row affected (0.01 sec)

// 在恢复
mysql> source /home/JZhong/MySql/helloworld.sql;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| helloworld         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
7 rows in set (0.00 sec)

        以上的备份原理其实是将原来在数据库中的操作全都备份一遍，恢复背负的时候，直接将文件中的备份的命令全都取出来执行一次即可。

查看连接情况

        使用 show processlist 命令可以查看出当前有哪些用户连接到我们的MySQL，如果查出某个用户不是正常登陆的，很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 190005
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 26
   User: root
   Host: localhost
     db: helloworld
Command: Query
   Time: 0
  State: init
   Info: show processlist
2 rows in set, 1 warning (0.00 sec)