【MySQL】第三章库的操作

系列文章目录

《【MySQL】第一章 MySQL 5.7的安装与卸载》
《【MySQL】第二章初识数据库》
《【MySQL】第三章库的操作》
《【MySQL】第四章表的操作》

文章目录

系列文章目录
库的增删查改
- 创建数据库
- 删除数据库
- 查看数据库
- - 查看数据库
  - 查看数据库创建
  - 选择数据库
  - 查看当前所选数据库
  - 查看当前MySQL服务的连接状态
  - 修改数据库
- 字符集与校验集
- - 概念
  - 查看MySQL默认字符集
  - 查看MySQL默认校验集
  - 查看MySQL可支持的字符集
  - 查看MySQL 可支持的校验集
  - 创建指定编码的数据库
  - 验证不同校验集的影响
库的备份与恢复
- 备份
- 恢复
- 注意事项

目标：

了解MySQL的库级别的增删查改操作。
了解创建数据库时编码集和校验集的相关话题。
了解MySQL的库的备份操作与恢复操作。

库的增删查改

创建数据库

建库 SQL 的语法：

CREATE DATABASE [IF NOT EXISTS] db_name [[create_specification] [create_specification] ...];create_specification:[DEFAULT] CHARACTER SET charset_name[DEFAULT] COLLATE collation_name

CREATE、DATABASE这些是MySQL内定的关键字，不区分大小写。
CREATE DATABASE是固定用法，注意与show databases区分。
db_name新创建的数据库的名字。
[]表示是可选项。
[IF NOT EXISTS]可选项表示如果数据库不存在就创建，反之不执行任何操作。
[create_specification]是创建数据库时的一些创建选项。
[[create_specification] [create_specification] ...]表示创建选项各自独立，以空格进行分割，并且可以按需组合。
语法种列举了两种创建选项，分别是
- [DEFAULT] CHARACTER SET charset_name表示指定默认字符集。
- [DEFAULT] COLLATE collation_name表示指定默认校验集。

以下是一些创建数据库的案例，案例中暂时不涉及到[create_specification]这个内容会与后续的【字符集与校验集】一起说明：

创建一个名字为db1的数据库。
SQL语句执行结果表示SQL语句执行成功，有1行被影响。
```
mysql> create database if not exists db1;
Query OK, 1 row affected (0.00 sec)
```
继案例1之后再次尝试创建一个名字为db1的数据库。
SQL语句执行结果显示，语句执行出错，名字为db1的数据库已存在，不允许重复创建。
```
mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
```
增加[if not exists]选项再次创建名字为db1的数据库。
SQL语句执行结果显示，语句执行成功，有1行被影响，有1个警告。
[if not exists]让案例2的报错变成了一个警告，说明操作有问题但不影响后续执行。
```
mysql> create database if not exists db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
```

删除数据库

删库的 SQL 语法：

DROP DATABASE [IF EXISTS] db_name;

该SQL语句执行之后：

MySQL内再也找不到该数据库。
对应的数据库目录被删除，目录里的数据表也会被一并删除。
在没有备份的情况下，该删除操作是不可逆的，所以千万不要随意删除数据库！

以下是一些删除数据库的案例：

删除名为helloworld的数据库。

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

继案例1之后再次删除名字为helloworld的数据库
SQL语句执行报错，显示数据库不存在，删除操作失败。
```
mysql> drop database helloworld;
ERROR 1008 (HY000): Can't drop database 'helloworld'; database doesn't exist
```
使用[if exists]选项，再次尝试删除名字为helloworld的数据库。
SQL语句执行成功，0行被影响，1个警告。SQL语句本来是有问题的，但[if exists]表示不让该语句真正执行，将错误降为了警告。
```
mysql> drop database if exists helloworld;
Query OK, 0 rows affected, 1 warning (0.00 sec)
```

查看数据库

show databases;

查看数据库创建

show create database db_name;

示例：

mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

说明：

数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字。
/*!40100 DEFAULT CHARACTER SET utf8 */不是注释，这个是指如果当MySQL的版本大于4.01就执行这句话，这句话的意思是设置test1数据库的默认字符集为utf8。

选择数据库

use db_name;

查看当前所选数据库

select database();

查看当前MySQL服务的连接状态

show processlist;

示例：

mysql> show processlist;
+----+------+-----------+--------+---------+------+----------+------------------+
| Id | User | Host      | db     | Command | Time | State    | Info             |
+----+------+-----------+--------+---------+------+----------+------------------+
| 11 | root | localhost | gomoku | Query   |    0 | starting | show processlist |
+----+------+-----------+--------+---------+------+----------+------------------+
1 row in set (0.00 sec)

修改数据库

ALTER DATABASE 数据库名 [[alter_spacification] [alter_spacification]...];alter_spacification:[DEFAULT] CHARACTER SET charset_name[DEFAULT] COLLATE collation_name

示例：

-- 修改前
mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)-- 将test1数据库字符集改成gbk。
mysql> alter database test1 charset=gbk;
Query OK, 1 row affected (0.00 sec)-- 修改后
mysql> show create database test1;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

字符集与校验集

在关于数据库创建的语法介绍中，提到了“字符集”和“校验集”这样的字眼，另外我们还发现，当一个数据库被新建，哪怕我们什么也没做，MySQL服务也会默认生成一个名称为db.opt的配置文件

在这里插入图片描述

配置文件中，default-character-set=utf8和“字符集”有关；default-collation和“校验集”有关。

下面就来详细说明一下什么字符集、什么是校验集，这两个集合对于数据库有什么影响。

概念

字符集（Character Set）是用于表示字符的编码标准。它定义了一组符号和对应的数值编码，用于存储文本数据。不同的字符集支持不同的字符集合，例如拉丁字母、汉字、阿拉伯字母等。

常见的字符集：

utf8mb4：UTF-8 编码的变种，最多使用 4 个字节来表示一个字符。它可以表示几乎所有语言的字符，是目前推荐的字符集，也是mysql默认使用的字符集。
latin1：西欧字符集，使用单字节编码（ISO-8859-1），仅支持基本的拉丁字母。
ascii：美国信息交换标准代码，使用单字节编码，只包含 128 个字符，适用于英文文本。

校验集（Collation）定义了字符集中的字符如何比较和排序。它包括字符的排序顺序和比较方式，决定了数据库在执行排序和比较操作时的行为。

常见的校验集：

utf8mb4_general_ci：UTF-8 字符集的通用不区分大小写的排序规则，比较性能较好，但处理某些特殊字符可能不够准确。
utf8mb4_unicode_ci：UTF-8 字符集的 Unicode 不区分大小写的排序规则，支持更精确的字符比较，特别是对多语言支持更好。

举一个通俗的例子来理解字符集与校验集：

现在有一本书，我们可以把这本书看作一个数据库（目录），书中的内容就是这个数据库的数据（目录里的文件），假设中文就是这本书的字符集，我们（数据库管理系统）可以把这个中文字符集理解成一本字典，我们看到书里的某个字或者符号就要到字典里查，只有在字典里定义好的字或者符号，才是我们能够理解的，如果这时候出现了一个字典里不存在的火星文字，我们就无法识别它的意思，这就叫做 “字符不在字符集的范围内，数据库管理系统无法正确识别和处理这些字符”。

至于校验集，我们可以用比较“中文”和“中國”的例子来说明。在读书的过程中，我们会将“中文”和“中國”理解成是等价的，这个就是校验集中的比较。校验集决定了在比较和排序时，哪些字符或字符串被认为是等价的，哪些字符或字符串是不同的。

而字符集和校验集必须要对应，毕竟火星文的校验集对于中文字符集没什么意义。

查看MySQL默认字符集

show variables like 'character_set_database';

SQL语句执行结果

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.01 sec)

当前创建的数据库如果没有手动指定字符集，MySQL 默认使用 utf8，不过， MySQL 的 utf8 编码也不是真正的 utf8 ，没有包含某些复杂的中文字符。MySQL真正的 utf8 是使用 utf8mb4。

查看MySQL默认校验集

show variables like 'collation_database';

SQL语句执行结果

+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

当前创建的数据库如果没有手动指定字符集，MySQL 默认使用 utf8_general_ci。

查看MySQL可支持的字符集

show charset;

SQL语句输出结果过长，这里仅截取一部分

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
-- (省略)

查看MySQL 可支持的校验集

show collation;

SQL语句输出结果过长，这里仅截取一部分

+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
-- (省略)

创建指定编码的数据库

创建一个名字为dp1的数据库，查看它的默认字符集和校验集。

create database if not exists db1;

查看dp1的字符集和校验集有两种方式：

select语句，这个尚未了解；
dp1的配置文件，就是创建数据库时MySQL服务自动生成的那个叫做db.opt的文件。

关于db.opt的一点说明：在用户没有主动设定的时候，db.opt的内容是拷贝自/etc/mysql里的配置文件，而一旦我们主动设定了，就以用户设定的内容为准，相当于就近原则。

新启动终端2（参考之前【见一见数据库】的操作，该终端在后续案例直接使用），使用root用户登录，进入/var/lib/mysql目录下的db1目录下，读取db.opt文件的内容，能够看到db1的默认使用的字符集是utf8，默认使用的校验集是utf8_general_ci。

root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db1# ll
total 12
drwxr-x--- 2 mysql mysql 4096 Oct 30 10:55 ./
drwxr-x--- 8 mysql mysql 4096 Oct 30 10:55 ../
-rw-r----- 1 mysql mysql   61 Oct 30 10:55 db.opt
root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db1# cat db.opt 
default-character-set=utf8
default-collation=utf8_general_ci

创建一个名字为db2，使用gbk字符集的数据库，再次查看dp2的配置文件。
创建数据库时字符集的设定是通过创建选项来指定的。
设置字符集的创建选项的正式写法是character set，可以简写成charset。
标准写法是空格字符集名称，也可以写成=字符集名称，这个也适用于其他创建选项。

-- 写法1
create database if not exists db2 charset gbk;
-- 写法2
create database if not exists db2 charset=gbk;
-- 写法3
create database if not exists db2 character set gbk;
-- 写法4
create database if not exists db2 character set=gbk;

切换回终端2，进入db2数据库对应的目录，然后读取db.opt的内容，显然能够看到配置文件中的字符集相较于默认的配置发生了变化。

root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db2# ll
total 12
drwxr-x--- 2 mysql mysql 4096 Oct 30 11:19 ./
drwxr-x--- 9 mysql mysql 4096 Oct 30 11:19 ../
-rw-r----- 1 mysql mysql   59 Oct 30 11:19 db.opt
root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db2# cat db.opt 
default-character-set=gbk
default-collation=gbk_chinese_ci

创建一个名字为db3，使用gbk字符集，使用gbk_chinese_ci校验集的数据库。
设置校验集的创建选项是collate，该选项没有简写。

-- 写法1
create database if not exists db3 charset=gbk collate=gbk_chinese_ci;
-- 写法2
create database if not exists db3 charset=gbk collate gbk_chinese_ci;

切换回终端2，进入db3数据库对应的目录，然后读取db.opt的内容，显然能够看到配置文件中的字符集和校验集相较于默认的配置都发生了变化。

root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db3# ll
total 12
drwxr-x---  2 mysql mysql 4096 Oct 30 11:23 ./
drwxr-x--- 10 mysql mysql 4096 Oct 30 11:23 ../
-rw-r-----  1 mysql mysql   59 Oct 30 11:23 db.opt
root@iZ7xvhuhaslv4h5dm1d1svZ:/var/lib/mysql/db3# cat db.opt 
default-character-set=gbk
default-collation=gbk_chinese_ci

验证不同校验集的影响

现在来做一个实验，创建两个字符集相同，但是校验规则不同的数据库，分别建立一个相同的表，插入一些相同的数据，然后进行查询，观察不同校验编码对于查询结果的影响。

注：这部分内容会出现一些表的插入、查询相关的SQL语句，后续会有详细讲解。

【第一阶段：数据的准备】

创建test1的数据库，字符集使用utf8，校验集使用utf8_general_ci（该校验集不区分字母大小写），创建person表，依次插入a、A、b、B。

-- 创建test1数据库
create database if not exists test1 charset=utf8 collate=utf8_general_ci;
-- 选择test1数据库
use test1;
-- 创建Person表
create table if not exists person (name varchar(10));
-- 依次插入a、A、b、B
insert into person (name) values ('a');
insert into person (name) values ('A');
insert into person (name) values ('b');
insert into person (name) values ('B');

创建test2数据库，字符集使用utf8，校验集使用utf8_bin（该校验集区分字母大小写），创建person表，依次插入a、A、b、B。

-- 创建test2数据库
create database if not exists test2 charset=utf8 collate=utf8_bin;
-- 选择test2数据库
use test2;
-- 创建Person表
create table if not exists person (name varchar(10));
-- 依次插入a、A、b、B
insert into person (name) values ('a');
insert into person (name) values ('A');
insert into person (name) values ('b');
insert into person (name) values ('B');

【第二阶段：以**name='a'**为条件进行查询】

不区分大小写的查询结果

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

区分大小写的查询结果

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

【第三阶段：查询升序排序结果】

不区分大小写的查询结果。
在不区分大小写的情况下，a、A按照插入顺序进行排列。

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

区分大小写的查询结果。

在区分大小写的情况下，a、A严格按照ASCII码进行排列。

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

【第四阶段：总结】

本实验展示了数据库校验规则对数据查询和排序的显著影响。使用不区分大小写的校验集时，相同字符的不同大小写会被视为相同，而在区分大小写的校验集中，这些字符则会被视为不同的值。选择合适的校验规则对于确保数据处理的准确性和预期结果至关重要。

字符集和校验集虽然重要，但是创建数据库的时候没有特地设置字符集和编码集的必要，MySQ 的默认字符集通常是utf8（utf8mb4），默认校验集为utf8mb4_general_ci，适用于大多数语言和字符，可以无缝处理多语言数据，并且对大小写不敏感。这适合大多数应用需求，如存储文本、用户名和标签等。

综上，除非应用需求特别要求，对于一般应用，使用默认字符集和校验集是更简便的选择。

库的备份与恢复

备份

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

示例：将 mytest 库备份到文件 mytest.sql。

mysqldump -P 3306 -u root -p123456 -B mytest > ./mytest.sql

需要注意的一点，mysqldump 是一个在命令行（Shell）中运行的工具，而不是一个在 MySQL 命令行客户端（即 mysql> 提示符下）中运行的 SQL 命令，像下面这样的使用方法是有问题的：

mysql> mysqldump -P 3306 -u root -p -B test1 > ~/temp/test1.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -P 3306 -u root -p -B test1 > ~/temp/test1.sql' at line 1

正确的做法是：

退出 MySQL 命令行客户端：
```
mysql> quit;
Bye
```

在终端中运行 mysqldump 命令：

# temp 不存在要先创建
# -bash: /root/temp/test1.sql: No such file or directory
# 创建命令：mkfir temp
mysqldump -P 3306 -u root -p -B test1 > ~/temp/test1.sql

这时，可以打开看看备份文件里的内容，它其实不是把我们要备份的数据库复制一份，而是把我们整个创建数据库，建表，导入数据的语句都装载这个文件中，如果我们将来要恢复该数据库，就把这个备份文件中的 SQL 语句全部执行一次。

恢复

方法一：

mysql> source ~/temp/test1.sql;

方法二：

root@iZ7xvhuhaslv4h5dm1d1svZ:~# mysql -u root -p < ~/temp/test1.sql

注意事项

如果备份的不是整个数据库，而是其中的一张表，怎么做？

mysqldump -u root -p 数据库名 表名1 表名2 > 数据库备份存储的文件路径

同时备份多个数据库。

mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库备份存储的文件路径

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

【MySQL】第三章库的操作

系列文章目录

文章目录

库的增删查改

创建数据库

删除数据库

查看数据库

查看数据库

查看数据库创建

选择数据库

查看当前所选数据库

查看当前MySQL服务的连接状态

修改数据库

字符集与校验集

概念

查看MySQL默认字符集

查看MySQL默认校验集

查看MySQL可支持的字符集

查看MySQL 可支持的校验集

创建指定编码的数据库

验证不同校验集的影响

库的备份与恢复

备份

恢复

注意事项

相关资讯

热文排行

最新新闻

推荐新闻

热搜词

【MySQL】第三章 库的操作

系列文章目录

文章目录

库的增删查改

创建数据库

删除数据库

查看数据库

查看数据库

查看数据库创建

选择数据库

查看当前所选数据库

查看当前MySQL服务的连接状态

修改数据库

字符集与校验集

概念

查看MySQL默认字符集

查看MySQL默认校验集

查看MySQL可支持的字符集

查看MySQL 可支持的校验集

创建指定编码的数据库

验证不同校验集的影响

库的备份与恢复

备份

恢复

注意事项

相关资讯

热文排行

最新新闻

推荐新闻

热搜词

【MySQL】第三章库的操作