MySQL库的操作

创建数据库

创建数据库的SQL语句：

CREATE DATABASE [IF NOT EXISTS] db_name [[DEFAULT] CHARSET=charset_name] [[DEFAULT] COLLATE=collation_name];

说明：

• SQL中大写的表示关键字
• [] 是可选项，里面的内容可以省略
• CHARACTER SET：指定数据库采用的字符集
• COLLATE：指定数据库字符集的校验规则

示例：

1️⃣ 创建一个名为db1 的数据库

创建数据时若不指定字符集和校验规则，则使用系统默认的：

说明：当我们创建数据库时没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_general_ci。

2️⃣ : 创建一个使用 utf8 字符集的 db2 数据库

只需要在创建数据库时通过 charset 指定编码格式即可（如：charset=utf8）：

3️⃣ ：创建一个使用 utf8 字符集，并带校对规则 utf8_general_c 的 db3 数据库

字符集和校验规则

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

show variables like 'character_set_database';
show variables like 'collation_database';

在指定的数据库下使用该 SQL，则查看的是该数据库对应的字符集。

查看数据库支持的字符集

使用 show character set; 或者 show charset; 的方式获取当前版本中支持的字符集。

show charset;

通过 show charset 查出来的是当前所有可用的字符集。字符集主要是控制用什么语言，比如 utf8 就可以使用中文。

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

show collation;

校验规则对数据库的影响

字符集编码格式和字符集校验规则之间的区别❓

字符集编码格式是指将字符编码为计算机可以识别和存储的二进制数据的方式。常见的字符集编码格式有：UTF-8、UTF-16、GBK等。不同的字符集编码格式使用不同的编码方式，因此它们所能表示的字符集范围和编码效率不同。

字符集校验规则是指在比较和排序字符时所使用的规则。它主要用于确定哪些字符被认为是相等的，以及如何按照字母顺序对字符进行排序。常见的字符集校验规则有：utf8mb4_general_ci、utf8mb4_bin等。不同的字符集校验规则对于相同的字符集编码格式和字符，可能会有不同的比较和排序结果。

例如：我们使用 UTF-8格式来存储数据，因此在进行数据比对时也必须使用 UTF-8 格式进行比对，保存 “存取数据” 方式的一致性。

注意：在选择字符集编码格式和字符集校验规则时，需要根据具体情况进行选择，以确保正确存储、比较和排序字符。

校验规则对数据库的影响

例子：假设我们有一个包含用户信息的数据库表，其中一个字段为 username 。若我们选择的校验规则是 utf8mb4_general_ci，那么在比对和排序 username 时，会忽略大小写和重音符号，如：‘‘Make’’ 和 ‘‘make’’ 会被认为是相同的，‘‘Café’’ 和 ‘‘Cafe’’ 也会被认为是相同的。但若我们选择的校验规则是 utf8mb4_bin ，那么在进行比对和排序 username 时，‘‘Make’’ 和 ‘‘make’’ 会被认为是不同的，‘‘Café’’ 和 ‘‘Cafe’’ 也会被认为是不同的。

接下来我们进行测试：

• 不区分大小写和重音符号

创建一个数据库，校验规则使用 utf8mb4_general_ci【不区分大小写】

create database t1 collate utf8mb4_general_ci;
use t1;
insert into info_table values ('Make');
insert into info_table values ('make');
insert into info_table values ('Café');
insert into info_table values ('Cafe');

插入数据后的表格如下所示：

不区分大小写和重音符号查询后的结果如下所示：

• 区分大小写和重音符号

创建一个数据库，校验规则使用 utf8mb4_bin【区分大小写和重音符号】

create database t2 collate utf8mb4_bin;
use t2;
create table info_table(name varchar(20));
insert into info_table values ('Make');
insert into info_table values ('make');
insert into info_table values ('Café');
insert into info_table values ('Cafe');

插入数据后的表格如下所示：

区分大小写和重音符号查询后的结果如下所示：

因此，在选择校验规则时，需要根据具体情况进行选择，以保证数据的准确性、正确性和存储效率。同时，在进行数据迁移时，也需要注意校验规则的一致性，以避免数据错误和不兼容的问题。

操作数据库

查看数据库

使用 show databases; 可以查看系统下属于该用户的所有数据库。

show databases;

显示创建语句

使用下列语句可以查看某一个数据库的创建语句。

show create database 数据库名称;

说明：

• MySQL 建议关键字使用大写，但是不是必须这样做。
• 如果表名或列名包含特殊字符，例如：空格、括号、划线等，需要使用反引号 (`) 将其括起来。数据库名字的反引号，是为了防止使用的数据库名刚好是关键字。
• /!40100 DEFAULT…/ 不是注释，表示当前MySQL的版本大于 4.01 版本，就执行这句话。

修改数据库

说明：对数据库的修改主要指的是修改数据库的字符集和校验规则。

修改数据库的 SQL 语句如下：

ALTER DATABASE 数据库名称 [[DEFAULT] CHARSET=charset_name] [[DEFAULT] COLLATE=collation_name];

例如：将数据库 t1 的字符集改为 gbk，将校验规则改为 gbk_bin ，如下所示：

删除数据库

要删除 MySQL 数据库，可以使用以下语句：

DROP DATABASE database_name; // database_name表示要删除数据库的名称

示例：删除数据库t1，执行删除之后，数据库内部看不到对应的数据库，对应的数据库文件夹被删除，级联删除，里面的数据表全部被删除。

注意：不要随意删除数据库。若确实要删除数据库，在执行此操作之前，请确保你已经备份了该数据库的数据，因为一旦删除，该数据库中的所有数据都将丢失。

说明：

• 只有具有 DROP 权限的 MySQL 用户才可以删除数据库。
• 确认要删除的数据库是正确的，以免意外删除其它数据库。删除数据库后，无法撤销此操作。
• 在删除数据库之前，请确保没有任何应用程序正在使用该数据库，若要删除的数据库正在被使用，可能会导致应用程序奔溃或出现其它问题。

数据库的备份和还原

数据库备份

要备份 MySQL 数据库，可以使用以下语句：

mysqldump -P 端口号 -u username -p 密码 -B 数据库名1 数据库名2 ... > backup_file.sql 
// backup_file.sql是备份文件的名称和路径

在执行此命令后，MySQL 将生成一个名为 backup_file.sql 的文件，其中包含数据库的所有内容，包括表结构和数据。

接下来，我们使用 t1 数据库进行演示，t1 数据库中有一个名为 info_table 的表，如下所示：

执行备份 SQL 语句，开始备份：

此时，打开 t2_backup_file.sql 文件查看内容，实际上是将整个创建数据库，建表，导入数据的语句都装载在这个文件中。

此外，还可以指定其它选项来自定义备份过程：

• --single-transaction：以事务方式备份，确保备份期间不会中断正在进行的事务。
• --routines：备份存储过程和函数。
• --events：备份事件。

例如，以下命令使用压缩算法并备份存储过程和事件：

mysqldump -P3306 -u username -p --compress --routines --events database_name > backup_fie.sql

数据库还原

MySQL 数据库还原语句如下：

source 数据库备份存储的文件路径    // 在登录数据库之后可使用该语句

mysql -u username -p database_name < 数据库备份存储的文件路径   // 未登录mysql直接备份

示例，我们使用 source 来对数据库进行还原，首先将原有的数据库删除：

让 MySQL 执行还原命令对数据库进行还原，数据库的还原实际上就是执行备份文件中的 SQL 语句：

查看数据库是否还原成功：

表的备份和还原

数据库备份

备份 MySQL 数据库中的表的语法如下：

mysqldump -P 端口号 -u username -p 密码 database_name table_name > 表备份存储的文件路径

示例，将数据库 t2 中的表 info_table 进行备份：

使用数据库表的备份语句备份 t2 中的 info_table：

查看备份的表的文件，可以看出存储的是关于表的 SQL 语句。

数据库还原

在还原数据库表之前，需要先选择目标数据库，并指定要将表还原到哪个数据库中。为例避免还原出来的表与目标数据库中已有的表重名，一般在还原时会选择先创建一个空的数据库，再将表恢复到该数据库中。

MySQL 中使用以下命令对数据库表进行还原：

source 表备份存储的文件路径

示例：为了便于测试，先将数据库中的 t2 数据库删除：

创建一个空的数据库，用于对表进行还原：

选择数据库，执行 MySQL 还原表的指令对表进行还原：

查看数据库表是否还原：

总结：为了成功还原表并避免命名冲突，建议先创建一个空的数据库，并将表还原到该数据库中。

查看连接情况

要查看当前 MySQL 数据库中的连接情况，可以使用以下 SQL 语句：

SHOW PROCESSLIST;

执行该语句后，将返回当前 MySQL 数据库中的所有连接信息，这些信息包括连接ID、连接状态、连接所使用的用户、连接所使用的主机、连接操作的数据库、SQL语句等。

需要注意，如果 MySQL 数据库中存在大量连接，执行 show processlist; 或 select * from information_schema.processlist; 可能会导致性能下降，因此实际中许哟啊谨慎使用这些语句，以避免对数据库性能产生不良影响。

MySQL 数据库连接可以告诉我们当前哪些用户连接到了我们的 MySQL，若查出某个用户不是你正常登录的，那么你的数据库有可能被入侵了。以后当发现自己的数据库比较慢时，可以使用该指令来查看数据库的连接情况。

总之，查看 MySQL 数据库连接可以帮助管理员或开发人员了解数据库的运行情况，及时发现问题并采取相应的措施，从而保障数据库的正常运行和数据的安全性。