【MySQL】第二章：库的操作

一、库的增删查改

1、创建数据库

建库 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]这个内容会与后续的【字符集与校验集】一起说明）：

创建数据库的案例

（1）创建一个名字为 db1 的数据库。

SQL语句执行结果表示SQL语句执行成功，有1行被影响。

mysql> create database if not exists db1;
Query OK, 1 row affected (0.00 sec)

说明：当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则
是：utf8_ general_ ci（我们这里暂时不显示指定，后面在【字符集与校验集】会说明）

（2）继案例1之后再次尝试创建一个名字为db1的数据库。

SQL语句执行结果显示，语句执行出错，名字为db1的数据库已存在，不允许重复创建。

mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists

（3）增加[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)

2、删除数据库

语法介绍

删库的 SQL 语法：

DROP DATABASE [IF EXISTS] db_name;

该SQL语句执行之后：

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

样例展示

（1）删除名为 db1 的数据库。

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

（2）继案例1之后再次删除名字为db1的数据库

SQL语句执行报错，显示数据库不存在，删除操作失败。

mysql> drop database db1;
ERROR 1008 (HY000): Can't drop database 'db1'; database doesn't exist

（3）使用[if exists]选项，再次尝试删除名字为helloworld的数据库。

SQL语句执行成功，0行被影响，1个警告。SQL语句本来是有问题的，但[if exists]表示不让该语句真正执行，将错误降为了警告

mysql> drop database if exists db1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

3、查看数据库

查看数据库

• 查看数据库：列举当前 MySQL 中的所有存在的数据库

  show databases;
  

  结果演示：

  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | StudentInfo        |
  | mysql              |
  | newDir_test        |
  | performance_schema |
  | sys                |
  +--------------------+
  6 rows in set (0.01 sec)
  

• 查看数据库创建：展示库的创建时使用的语句

  show create database db_name;
  

  结果演示：之前我创建了一个数据库 StudentInfo，他就将我创建该库时使用的语句展示出来（这里从侧面说明了我们在 MySQL 中的多数行为其实都会被记录下来）

  

  说明

  • 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字。

  • /*!40100 DEFAULT CHARACTER SET utf8 */不是注释，这个是指如果当MySQL的版本大于4.01就执行这句话，这句话的意思是设置test1数据库的默认字符集为utf8。

选择数据库

use db_name;

上一章讲过，数据库就类似文件系统中的目录，我们查看某路径下的某文件，必须先进入该文件的上级目录，因此我们查看数据库的数据时，必先进入该数据库

查看当前所选数据库

select database();

本命令的含义如标题，展示当前进入了哪个数据库

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

show processlist;

本图展示了当前有多少个用户正在连接使用 MySQL，并展示其他信息

4、修改数据库

语法介绍

ALTER DATABASE 数据库名 [[alter_spacification] [alter_spacification]...];

alter_spacification:
    [DEFAULT] CHARACTER SET charset_name
    [DEFAULT] COLLATE collation_name

样例展示

我这里直接修改某数据库所使用的 字符集（关于字符集代表什么，下一节讲解，这里主要暂时我们通过一些可选选项修改了对应的数据库信息）

二、字符集与校验集

1、引入

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

• default-character-set=utf8 就是该数据库设置的 “字符集”
• default-collation 就是该数据库设置的 “校验集”

2、概念

在数据库中，字符集（Character Set）和校验集（Collation）是两个非常重要的概念，它们直接影响到数据的存储、检索和比较方式。以下是对这两个概念的详细讲解：

---

（1）字符集（Character Set）

字符集定义了数据库中可以存储哪些字符以及这些字符是如何编码的。换句话说，字符集是一组符号及其对应的二进制编码规则。

主要作用：

• 确定数据库支持的语言字符范围。
• 决定字符如何存储为二进制数据。
  
  

常见字符集：

• ASCII: 包含128个字符（英文字符为主），每个字符占用1字节。
• UTF-8: 支持几乎所有的语言字符，是一种可变长度的编码方式（1~4字节）。
• GBK: 支持中文字符集，主要用于简体中文环境。

---

（2）校验集（Collation）

校验集是在字符集的基础上进一步定义了字符的比较规则。它决定了字符之间的排序顺序和相等性判断。

主要作用：

• 规定字符的比较规则（大小写敏感、重音敏感等）。
• 确定字符串的排序顺序。

校验集的属性：

• 区分大小写（Case-Sensitive, CS）: ‘A’ 和 ‘a’ 被认为是不同的字符。
• 不区分大小写（Case-Insensitive, CI）: ‘A’ 和 ‘a’ 被认为是相同的字符。

常见校验集示例：

• utf8_general_ci: 基于 UTF-8 字符集，不区分大小写，适用于通用场景。
• utf8_bin: 基于 UTF-8 字符集，按照二进制值进行比较（区分大小写和重音）。

我一句话讲明白校验符的作用：

校验集（Collation）的作用是定义字符的比较规则，例如设置为 utf8_general_ci 时，数据库在查找数据时会不区分大小写，所以查找 ‘a’ 时，会匹配到 ‘a’ 或 ‘A’。

---

（3）字符集与校验集的关系

• 校验集依赖于字符集，必须先指定字符集才能选择相应的校验集。
• 一个字符集可以有多个校验集，例如 UTF-8 字符集有 utf8_general_ci、utf8_unicode_ci 和 utf8_bin 等多种校验集。

---

3、查看MySQL默认字符集

show variables like 'character_set_database';

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

4、查看MySQL默认校验集

show variables like 'collation_database';
/*这条命令的大概理解是：展示一下名为collation_database的变量的值*/

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

5、查看MySQL可支持的字符集

show charset;

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

6、创建指定编码的数据库（操作）

（1）创建 db1 ：查看默认字符集和校验集

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

create database if not exists db1;

我们可以在 dp1的配置文件，就是创建数据库时MySQL服务自动生成的那个叫做db.opt的文件中查看 它的默认字符集和校验集

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

（2）创建 db2 ：指定字符集和校验集

创建数据库时字符集的设定是通过创建选项来指定的：我们下面指定字符集为 gbk，指定校验集 gbk_chinese_ci

• 设置字符集的创建选项的正式写法是character set，可以简写成charset。
• 标准写法是空格字符集名称，也可以写成=字符集名称，这个也适用于其他创建选项。

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

可以发现，在配置文件 db.opt 里面，字符集和校验集发生了修改

7、验证不同校验集的影响

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

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

（1）准备工作

创建 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');

（2）查询名字为 ‘a’ 的：name='a'

不区分大小写的查询结果

因为数据库 test1 设置的校验集为 utf8_general_ci，不区分大小写，因此查询一个小写 a，就会得到 大小写的 a

-- 选择test1数据库
use test1;
-- 查询名字为 `‘a’` 的
select name from person where name='a';

区分大小写的查询结果

因为数据库 test2 设置的校验集为 utf8_bin，区分大小写，因此查询一个小写 a，只会得到 小写的 a

-- 选择test2数据库
use test2;
-- 查询名字为 `a` 的
select name from person where name='a';

（3）查询排序结果（升序）

不区分大小写的查询结果：

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

-- 选择test1数据库
use test1;
-- 按照名字排序
select * from person order by name;
/*如何理解一个查询语句加上排序：其实就是查询数据，同时对查询的数据进行排序后展示*/ 

区分大小写的查询结果：

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

-- 选择test2数据库
use test2;
-- 按照名字排序
select * from person order by name;

（4）总结

---

本实验通过实例展示了数据库校验规则对数据查询和排序的重要影响。当使用不区分大小写的校验集（如 utf8_general_ci 或 utf8mb4_general_ci）时，相同字符的不同大小写会被视为等价，例如 ‘A’ 和 ‘a’ 被认为是相同的；而在区分大小写的校验集（如 utf8_bin 或 utf8mb4_bin）中，这些字符则会被视为不同的值。因此，在实际应用中，选择合适的校验规则对于确保数据处理的准确性和符合预期的结果至关重要。

尽管字符集和校验集在数据库设计中扮演着关键角色，但在创建数据库时，并不一定需要特别设置字符集和校验集。MySQL 默认使用的字符集通常是 utf8mb4，默认校验集为 utf8mb4_general_ci。这一组合适用于大多数语言和字符需求，能够无缝处理多语言数据，并且对大小写不敏感。这种配置非常适合一般的应用场景，例如存储文本、用户名、标签等内容。

综上所述，除非应用有特殊需求（如严格区分大小写或支持特定语言特性），对于大多数应用场景而言，使用 MySQL 的默认字符集和校验集是一个更简便且高效的选择。

---

三、库的备份与恢复

库的备份

前言：
在计算机科学中，“dump” 通常指的是将数据从一个地方（如内存或数据库）提取出来并存储到另一个地方（如文件）。这种操作常用于调试程序，通过查看程序运行时的内存状态来找出问题所在。例如，核心转储（core dump）就是一种记录了程序崩溃时内存状态的文件。
在我们数据库这里，就是将数据提取出来存储到另外的一个地方，也就是备份了

mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名 > 数据库备份存储的文件路径
/*从这里可以看出，备份一个数据首先就是要登录Mysql账户，找到指定的数据进行备份*/ 

示例：将数据库 db1 备份到文件 db1.sql。（注意：-p123456 这里要写入你的MySQL密码，我的密码是 123456）

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

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

正确的做法是：

1、退出 MySQL 命令行客户端：

mysql> quit;
Bye

2、在终端中运行 mysqldump 命令：

图中的一些报错可以先不用理会，我们这里是正常备份成功了

这时，可以打开看看备份文件里的内容，它其实不是把我们要备份的数据库复制一份，而是把我们整个创建数据库，建表，导入数据的语句都装载这个文件中，如果我们将来要恢复该数据库，就把这个备份文件中的 SQL 语句全部执行一次（也就是说，数据库的建立过程中所使用的所有历史命令，它都逐一记录下来了）

这里很精髓呀，这样的方式相当于将一个数据库内容和构造结构都备份下来了，在逻辑上可以理解为一个数据库本身都被复制下来了

库的恢复

方法一：在 MySQL命令行中

source ./db1.sql;

方法二：在终端Bash上

mysql -u root -p < ./db1.sql

这个方法相当于将备份文件重定向输入到我们的 MySQL中

注意事项

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

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

• 同时备份多个数据库。

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

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