03 库的操作

库的操作

1. 创建数据库

1. 语法格式

CREATE DATABASE 用于在 MySQL 中创建新的数据库，可以同时指定字符集（编码格式）和校验规则。

1
2
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARACTER SET 字符集名] [DEFAULT COLLATE 校验规则];
CREATE DATABASE [IF NOT EXISTS] 数据库名 [[DEFAULT] CHARSET=字符集名] [[DEFAULT] COLLATE=校验规则];

其中带 [] 表示可选项，不写就会自动选择默认的配置。sql 语句也可以使用小写，会自动识别！在之后的讲解中，我会尽量使用多种写法来展示同一条 sql 语句的效果。

参数详解：

参数说明
IF NOT EXISTS如果没有就创建，如果已有同名的则报错：提示无法创建，因为已存在
DEFAULT CHARACTER SET指定数据库的默认字符集（如 utf8、utf8mb4 等）
DEFAULT COLLATE指定校验规则，即字符串比较、排序方式
数据库名自定义的新数据库名称

注：字符集决定存储文字的编码方式；校验规则决定字符串的比较方式。例如 utf8_general_ci 表示 utf8 字符集下，比较时不区分大小写（ci = case insensitive）。

2. 采用默认编码格式和校验规则创建数据库

1
CREATE DATABASE test;		# 创建一个名为 test 的库，对应 /var/lib/mysql/ 下的 test 文件夹

使用 MySQL 配置文件中默认的字符集与校验规则，根据我们的配置会自动选择 utf8mb4 和 utf8mb4_general_ci。

3. 指定 UTF-8 编码格式创建数据库

1
2
3
CREATE DATABASE mydb_utf8 DEFAULT CHARACTER SET utf8;
create database mydb_utf8 charset=utf8;
# 创建一个名为 mydb_utf8 的库，并设置默认字符集为 utf8，对应 /var/lib/mysql/ 下的 mydb_utf8 文件夹

仅指定字符集为 utf8，校验规则会自动采用该字符集的默认规则（即 utf8_general_ci）。

4. 指定 UTF-8 编码格式与校验规则创建数据库

1
2
3
CREATE DATABASE test1 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
create database test1 charset=utf8 collate=utf8_general_ci;
# 创建一个名为 test1 的库，设置默认字符集为 utf8，默认排序规则为 utf8_general_ci，对应 /var/lib/mysql/ 下的 test1 文件夹

同时指定字符集与校验规则，使字符串比较、排序时不区分大小写。

数据库命名规范建议：数据库名只能包含字母、数字、下划线、不能以数字开头、建议使用小写字母、避免使用 MySQL 关键字（如 database、table 等）、如果必须使用关键字，需要用反引号 ` 包裹：

1
>SHOW CREATE DATABASE `database`;

2. 字符集和校验规则

1. 查看系统默认的字符集与校验规则

我们可以使用以下语句查看当前 MySQL 服务的 系统默认字符集 和 默认校验规则：

1
2
3
4
SHOW VARIABLES LIKE 'character_set_database';	# 查看当前 USE 的数据库的字符集
SHOW VARIABLES LIKE 'collation_database';		# 查看当前 USE 的数据库的校验规则
SHOW VARIABLES LIKE 'character_set_server';		# 查看服务器默认字符集
SHOW VARIABLES LIKE 'collation_server';			# 查看服务器默认校验规则

也可以一次性查看所有字符集相关配置：

1
2
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation%';

说明：

  • character_set_server 表示 MySQL 服务的全局默认字符集。
  • collation_server 表示 MySQL 服务的全局默认校验规则。
  • character_set_database 和 collation_database 表示当前已选中数据库的默认字符集与校验规则。
  • 如果未执行 USE，它们会回退到 character_set_server 和 collation_server 的值，但语义上仍属于“当前数据库上下文”。

2. 查看 MySQL 支持的字符集

MySQL 支持多种字符集，可用以下命令查看：

1
2
SHOW CHARACTER SET;
show charset;

输出结果示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

说明：

  • Charset：字符集名称。
  • Description：描述。
  • Default collation：默认校验规则。
  • Maxlen：每个字符所需最大字节数。

3. 查看某个字符集支持的校验规则

使用以下语句查看指定字符集的所有可用校验规则：

1
SHOW COLLATION WHERE Charset='utf8';

输出示例：

1
2
3
4
5
6
7
+--------------------+---------+----+---------+----------+---------+
| Collation          | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+----+---------+----------+---------+
| utf8_general_ci    | utf8    | 33 | Yes     | Yes      | 1       |
| utf8_bin           | utf8    | 83 |         | Yes      | 1       |
| utf8_unicode_ci    | utf8    | 192|         | Yes      | 8       |
+--------------------+---------+----+---------+----------+---------+

常见校验规则说明：

  • utf8_general_ci：不区分大小写的通用排序规则（ci = case insensitive）。
  • utf8_bin：区分大小写的二进制比较方式（bin = binary）。
  • utf8_unicode_ci：基于 Unicode 标准的更准确排序方式（不区分大小写，但性能略低）。

4. 校验规则对数据库的影响

校验规则主要影响字符串的 比较 与 排序。不同的规则可能导致查询结果差异。

示例：

1
2
3
4
5
6
7
CREATE DATABASE test_ci DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE DATABASE test_bin DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

USE test_ci;
CREATE TABLE t (name VARCHAR(20));
INSERT INTO t VALUES ('abc'), ('ABC');
SELECT * FROM t WHERE name='abc';

输出：

1
2
3
4
5
6
+------+
| name |
+------+
| abc  |
| ABC  |   ← 不区分大小写
+------+

而如果使用 utf8_bin：

1
2
3
4
USE test_bin;
CREATE TABLE t (name VARCHAR(20));
INSERT INTO t VALUES ('abc'), ('ABC');
SELECT * FROM t WHERE name='abc';

输出：

1
2
3
4
5
+------+
| name |
+------+
| abc  |   ← 区分大小写
+------+

总结：

  • utf8_general_ci：比较和排序不区分大小写。
  • utf8_bin：比较和排序区分大小写。
  • 校验规则不会改变数据存储内容，只影响字符串比较结果与排序方式。

3. 操作数据库

1. 查看数据库

1. 查看当前服务器中所有数据库

1
2
SHOW DATABASES;		# 注意不要丢了最后的 S ！
show databases;

说明：这会显示 MySQL 服务端当前存在的所有数据库。注意：information_schema、mysql、performance_schema、sys 是系统库，不要随意修改或删除。

2. 查看当前正在使用的数据库

1
2
SELECT DATABASE();
select database();

2. 显示数据库创建语句

要查看数据库的创建详情（字符集、校验规则等），可用：

1
2
3
SHOW CREATE DATABASE 数据库名;
show create database 数据库名;
SHOW CREATE DATABASE test;		# 示例

输出示例：

1
2
3
4
5
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+

看着有点乱对吧，可以使用 show create database test\G，此时后面的 ; 可有可无，G 选项的作用是将结果按行垂直排列，每个字段单独占一行，特别适合处理字段值较长的输出场景：

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

说明： 语句会完整显示数据库的创建 SQL，包括字符集与校验规则，可用于迁移或备份时复制创建语句。/*!40100 DEFAULT CHARACTER SET utf8 */ 不是注释，它表示当前 MySQL 版本如果大于 4.10，则执行后面的 SQL 语句，相当于 if 语句。

3. 修改数据库

可通过 ALTER DATABASE 修改数据库的默认字符集与校验规则，语法格式：

1
2
3
ALTER DATABASE 数据库名 [DEFAULT CHARACTER SET 字符集名] [DEFAULT COLLATE 校验规则];
ALTER DATABASE 数据库名 [DEFAULT] CHARSET=字符集名 [DEFAULT] COLLATE=校验规则;
alter database 数据库名 charset=字符集名 collate=校验规则;

1. 示例 1：修改字符集

1
2
ALTER DATABASE test DEFAULT CHARACTER SET utf8mb4;
alter database test default charset=utfmb4;

2. 示例 2：修改字符集和校验规则

1
2
3
4
5
6
ALTER DATABASE test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 分 2 次修改：
alter database test character set utf8mb4;
alter database test collate utf8mb4_general_ci;

说明： 只会改变“数据库默认字符集”，不会自动修改已存在表的字符集。如果要真正改变表内数据编码，还需针对表执行 ALTER TABLE。

4. 删除数据库

语法格式：

1
2
DROP DATABASE [IF EXISTS] 数据库名;
drop database 数据库名;

示例：

1
drop database test;

说明：

  • IF EXISTS 表示若数据库不存在则不报错。
  • MySQL 不支持同时删除多个库的语法，一次只能删除一个数据库。如果要一次性删除多个库，需要写多条语句，或用脚本批量执行。
  • 删除数据库会 直接删除对应目录及全部数据文件（库中的表也会一并删除），例如 /var/lib/mysql/test/。
  • 删除操作不可恢复，务必谨慎执行。

前面提到，一个数据库的创建其实是在 /var/lib/mysql/ 会创建对应的文件夹，那么删除一个库是不是可以直接使用 rm 进行删除呢？

操作方式行为风险
DROP DATABASEMySQL 内部安全删除：清理表空间、日志、数据字典记录、权限信息推荐、安全
手动删除文件夹只删掉物理文件，MySQL 内部元数据（如 information_schema、权限缓存）不会更新不推荐、高风险

从 MySQL 8.0 开始，所有数据库元数据都存储在 InnoDB 的数据字典中，不再依赖文件系统目录是否存在。这意味着：删了 /var/lib/mysql/test 目录，表面上好像效果一样，但 SHOW DATABASES; 可能仍然显示 test，尝试 USE mydb; 会报错：Unknown database 'test'，也 无法再创建同名数据库，因为元数据还存在！这会导致“幽灵数据库”问题：看不见、删不掉、建不了！ 所以：推荐直接使用 MySQL 的命令，最好不要直接删除对应的文件夹！反过来也最好不要使用 mkdir 进行创建。

4. 备份和恢复

1. 备份

1. 基础备份

mysqldump 是一个命令行工具（不是 SQL 语句），不能在 MySQL 客户端（即 mysql> 交互界面）中直接执行，需要先 exit 退出。

语法格式：

1
2
3
4
mysqldump -P端口号 -u 用户名 -p 密码 -B 数据库名 > 数据库备份存储路径

mysqldump -P3306 -u root -p123456 -B test > D:/test.sql		# 示例：将 mytest 库备份到 D: 盘
mysqldump -P3306 -u root -p -B test > ~/temp/t.sql		# 或者执行完再输密码，注意：文件/目录不存在不会自动创建！

说明： 备份文件 test.sql 中包含整个数据库的创建语句、表结构、表数据，可以打开文件查看 SQL 内容。比如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
root@hcss-ecs-be68:~/temp# cat t.sql 
-- MySQL dump 10.13  Distrib 8.0.43, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	8.0.43-0ubuntu0.22.04.2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `test`;

--
-- Table structure for table `students`
--

DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `age` int DEFAULT NULL,
  `grade` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `students`
--

LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (1,'张三',18,'高三'),(2,'李四',17,'高二'),(3,'王五',19,'大学一年级');
/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2025-9-11 15:56:29

2. 备份单张表

1
mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql	# 仅备份指定表，不会备份整个数据库。

3. 同时备份多个数据库

1
mysqldump -u root -p -B 数据库名1 数据库名2 ... > D:/backup.sql

注意： 如果没有 -B 参数，备份单库时，需要先手动创建空数据库，然后再用 source 导入，mysqldump 会生成标准 SQL 文件，可跨服务器恢复。

2. 恢复

语法：

1
2
3
mysql> source 数据库备份文件路径;

mysql> source D:/new/test.sql;	# 示例

说明： 恢复会执行 SQL 文件里的所有语句：创建数据库、建表、导入数据，如果备份文件没有 CREATE DATABASE，需要先手动创建空数据库再导入。注意事项：

  • 备份不是整个数据库，而是单表时，需指定表名。
  • 多库备份可通过 -B 数据库名1 数据库名2 实现。
  • 恢复单库时，如果备份文件没有 -B 参数创建数据库，需要先创建空库。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
mysql> source ~/temp/t.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

5. 查看连接情况

1. 语法格式

1
2
SHOW PROCESSLIST;
show processlist;

示例：

1
2
3
4
5
6
7
8
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 8710 | Waiting on empty queue | NULL             |
| 15 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set, 1 warning (0.00 sec)

说明：

  • Id：连接编号。
  • User：用户名。
  • Host：客户端主机。
  • db：当前使用的数据库。
  • Command：操作类型。
  • Time：操作持续时间。
  • State：操作状态。
  • Info：执行的 SQL 语句。

用途：

  • 查看当前有哪些用户连接到 MySQL。
  • 可判断是否有异常或未授权的连接。
  • 当发现数据库操作缓慢或异常时，可用此命令排查连接。

2. 终止异常连接

1
KILL 123;  # 终止ID为123的连接