📑 目录

1. 📚 前言

在 MySQL 的学习与面试中，对数据库本身的操作是基石中的基石。无论是创建项目、设计表结构，还是进行数据迁移与恢复，都离不开对数据库的增删改查。本文将基于 MySQL 官方文档与实践经验，系统性地梳理数据库的创建、字符集选择、查看、修改、删除、备份与恢复等核心操作，并附上经典面试题，助你轻松应对面试挑战。

2. 🛠️ 库的操作

2.1 🆕 创建数据库

语法
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
    [DEFAULT] COLLATE collation_name
说明
  • 大写的表示关键字：CREATE、DATABASE、IF NOT EXISTS 等是 SQL 关键字，建议大写以增强可读性。
  • [] 是可选项：IF NOT EXISTS 表示如果数据库不存在才创建，避免重复创建报错。
  • CHARACTER SET：指定数据库采用的字符集，例如 utf8、gbk。
  • COLLATE：指定数据库字符集的校验规则，例如 utf8_general_ci、utf8_bin。
创建数据库案例

1. 创建名为 db1 的数据库

create database db1;

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

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

create database db2 charset=utf8;

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

create database db3 charset=utf8 collate utf8_general_ci;

2.3 🔤 字符集和校验规则

2.3.1 查看系统默认字符集以及校验规则
show variables like 'character_set_database';
show variables like 'collation_database';
2.3.2 查看数据库支持的字符集
show charset;

字符集主要是控制用什么语言。比如 utf8 就可以使用中文。

2.3.3 查看数据库支持的字符集校验规则
show collation;
2.3.4 校验规则对数据库的影响

校验规则决定了数据库在比较字符时是否区分大小写、排序规则等。我们通过两个例子来直观感受。

1. 不区分大小写

创建一个数据库，校验规则使用 utf8_general_ci（不区分大小写）。

create database test1 collate utf8_general_ci;
use test1;
create table person(name varchar(20));
insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');

2. 区分大小写

创建一个数据库，校验规则使用 utf8_bin（区分大小写）。

create database test2 collate utf8_bin;
use test2;
create table person(name varchar(20));
insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');

进行查询

  • 不区分大小写的查询以及结果
mysql> use test1;
mysql> select * from person where name='a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.01 sec)
  • 区分大小写的查询以及结果
mysql> use test2;
mysql> select * from person where name='a';
+------+
| name |
+------+
| a    |
+------+
2 rows in set (0.01 sec)

结果排序

  • 不区分大小写排序以及结果
mysql> use test1;
mysql> select * from person order by name;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
+------+
  • 区分大小写排序以及结果
mysql> use test2;
mysql> select * from person order by name;
+------+
| name |
+------+
| A    |
| B    |
| a    |
| b    |
+------+

总结：utf8_general_ci 在比较时会将 a 和 A 视为相同，排序时也不区分大小写；而 utf8_bin 则会严格区分，排序时大写字母会排在小写字母之前（基于 ASCII 码）。

2.4 🎮 操纵数据库

2.4.1 查看数据库
show databases;
2.4.2 显示创建语句
show create database 数据库名；

示例：

mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mysql    | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+

说明：

  • MySQL 建议我们关键字使用大写，但是不是必须的。
  • 数据库名字的反引号 `，是为了防止使用的数据库名刚好是关键字。
  • /*!40100 default.... */ 这个不是注释，表示当前 mysql 版本大于 4.01 版本，就执行这句话。

查看当前处于哪一个数据库中

select database();
2.4.3 修改数据库

语法

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]

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

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

实例：将 mytest 数据库字符集改成 gbk

mysql> alter database mytest charset=gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mytest   | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+----------------------------------------------------------------+

同时修改字符集和校验规则

alter database test2 charset=gbk collate gbk_chinese_ci;
2.4.4 数据库删除

语法

DROP DATABASE [IF EXISTS] db_name;

注意：不建议直接对数据库进行删除。

执行删除之后的结果：

  • 数据库内部看不到对应的数据库。
  • 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。
2.4.5 备份和恢复
2.4.5.1 备份

语法

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

示例：将 mytest 库备份到文件（退出连接，在命令行窗口执行该命令）

# mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql

这时，可以打开看看 mytest.sql 文件里的内容，其实把我们整个创建数据库，建表，导入数据的语句都装载这个文件中。

2.4.5.2 还原
mysql> source D:/mysql-5.7.22/mytest.sql;
2.4.5.3 注意事项
  • 如果备份的不是整个数据库，而是其中的一张表，怎么做？
# mysqldump -u root -p 数据库名 表名1 表名2  > D:/mytest.sql
  • 同时备份多个数据库
# mysqldump -u root -p -B 数据库名1  数据库名2 ... > 数据库存放路径
  • 如果备份一个数据库时，没有带上 -B 参数，在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用 source 来还原。
2.4.6 查看连接情况
show processlist

示例：

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  2 | root | localhost | test | Sleep   | 1386 |       | NULL             |
|  3 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

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

3. 💎 总结

本文详细介绍了 MySQL 数据库操作的核心知识点，从创建、字符集选择、查看、修改、删除到备份与恢复，每一步都配有清晰的语法和实例。掌握这些内容，不仅能让你在日常开发中游刃有余，更能让你在面试中自信地回答相关问题。记住，数据库操作是基础，务必亲手敲一遍代码，加深理解。

4. 🎯 经典面试题

1. 什么是数据库的字符集和校验规则？它们有什么区别？

解答：字符集（Character Set）定义了数据库可以存储哪些字符，例如 utf8 可以存储中文、英文等。校验规则（Collation）定义了字符之间的比较规则，例如是否区分大小写、排序顺序等。简单来说，字符集决定“能存什么”，校验规则决定“怎么比较和排序”。

2. 创建一个数据库时，如果不指定字符集，默认会使用什么？

解答：在 MySQL 中，如果不指定字符集，默认会使用 utf8 字符集，对应的默认校验规则是 utf8_general_ci。

3. 请解释 utf8_general_ci 和 utf8_bin 两种校验规则的区别，并举例说明。

解答：utf8_general_ci 是不区分大小写的校验规则，在查询和排序时会将 a 和 A 视为相同。而 utf8_bin 是二进制比较，严格区分大小写。例如，在 utf8_general_ci 规则下，SELECT * FROM table WHERE name='a' 会同时匹配到 a 和 A；而在 utf8_bin 规则下，只会匹配到 a。

4. 如何备份和恢复一个 MySQL 数据库？请写出关键命令。

解答：

  • 备份：使用 mysqldump 命令，例如 mysqldump -u root -p -B 数据库名 > 备份文件路径.sql。
  • 恢复：登录 MySQL 后，使用 source 命令，例如 source /path/to/备份文件.sql。如果备份时没有使用 -B 参数，需要先手动创建数据库并 use 它，再执行 source。

5. 如何查看当前有哪些用户连接到了 MySQL 数据库？这个命令有什么实际用途？

解答：使用 show processlist; 命令可以查看当前所有连接到 MySQL 的连接信息，包括用户、主机、正在执行的命令等。这个命令的实际用途是排查数据库连接问题，例如当数据库响应变慢时，可以查看是否有大量异常连接或长时间未关闭的连接，甚至可以用来发现潜在的安全入侵。

# 数据库 # mysql # oracle
Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

四天蒸发近2000亿，宇树科技股价近乎“腰斩”；苹果举办库克告别派对：9月1日正式卸任CEO；Anthropic两款新Claude模型曝光 | 极客头条

avatar AI编程社区
cover

Claude Code反超GitHub Copilot登顶第一、90%程序员已用上Agent，最新AI编码调查报告来了

avatar AI编程社区
cover

如何用好 Claude Code 写后端代码

avatar AI编程社区