【MySQL】3. MySQL库的操作

注意：

1. MySQL 的 SQL 关键字（如 SELECT、CREATE）不区分大小写，大小写写法均可执行；
2. 数据库 / 表名的大小写规则受操作系统影响（Linux 区分、Windows 不区分）；
3. 字符串值完全区分大小写，列名建议保持写法一致；

1. 创建数据库

1.1 创建数据库语法指令

语法：

CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...]create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name

createdatabase[ifnotexists]db_name[create_specification[,create_specification]...]create_specification:[default]charactersetcharset_name[default]collatecollation_name

说明：

• 大写的表示关键字
• []是可选项
• CHARACTER SET：指定数据库采用的字符集
• COLLATE：指定数据库字符集的校验规则

创建数据库的时候，有两个编码集：

1. 数据库字符集：数据库为来存储数据的格式
2. 数据库校验集：支持数据库，进行字段比较使用的编码，本质也是一种

数据库无论对数据做任何操作，都必须保证操作和编码必须是编码一致的

1.2 创建数据库案例

• 创建名为db1的数据库

  createdatabasedb1;

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

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

  createdatabasedb2charset=utf8;

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

  createdatabasedb3charset=utf8collateutf8_general_ci;

2. 删除数据库

dropdatabasedb_name

• 删除名为db1的数据库

dropdatabasedb1;

3. 字符集和校验规则

3.1 查看系统默认字符集以及校验规则

showvariableslike'character_set_database';showvariableslike'collation_database';

3.2 查看数据库支持的字符集

showcharset;

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

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

showcollation;

3.4 查看某一个数据库的字符集/校验规则

使用SHOW CREATE DATABASE语句，这是官方推荐的方式，能直接看到创建数据库时的完整配置（包括你指定的字符集、校验规则）。

语法：

SHOWCREATEDATABASE数据库名;

示例：

假设你创建了这样的数据库：

-- 创建时指定字符集utf8mb4、校验规则utf8mb4_unicode_ciCREATEDATABASEtest_dbDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;

执行查看语句：

SHOWCREATEDATABASEtest_db;

输出结果（示例）：

解读：

• DEFAULT CHARACTER SET utf8mb4：你指定的字符集；
• COLLATE utf8mb4_unicode_ci：你指定的校验规则；
• 即使是 MySQL 8.0+（无 db.opt 文件），这个语句也能正常返回结果。

3.5 校验规则对数据库的影响

1.不区分大小写的校验规则

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

# 创建数据库createdatabasetest1collateutf8_general_ci;# 使用数据库usetest1;# 建表createtableperson(namevarchar(20));# 插入数据insertintopersonvalues('a');insertintopersonvalues('A');insertintopersonvalues('b');insertintopersonvalues('B');# 查看数据库中的表（要在某一个数据库里面使用）showtables;# 查看某一个表结构desctable_name;

2.区分大小写的校验规则

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

createdatabasetest2collateutf8_bin;usetest2;createtableperson(namevarchar(20));insertintopersonvalues('a');insertintopersonvalues('A');insertintopersonvalues('b');insertintopersonvalues('B');

3.进行查询

不区分大小写的查询以及结果

mysql>usetest1;mysql>select*frompersonwherename='a';+------+|name|+------+|a||A|+------+2rowsinset(0.01sec)

区分大小写的查询以及结果

mysql>usetest2;mysql>select*frompersonwherename='a';+------+|name|+------+|a|+------+2rowsinset(0.01sec)

4.结果排序

不区分大小写排序以及结果：

mysql>usetest1;mysql>select*frompersonorderbyname;+------+|name|+------+|a||A||b||B|+------+

区分大小写排序以及结果：

mysql>usetest2;mysql>select*frompersonorderbyname;+------+|name|+------+|A||B||a||b|+------+

4. 操纵数据库

4.1 查看数据库

1. 查看所有数据库

showdatabases;

2. 查看自己所在的当前数据库

showdatabase();

4.2 显示创建语句（同查看数据库字符集/校验规则）

showcreatedatabase数据库名;

示例：

mysql>showcreatedatabasemytest;+----------+--------------------------------------------------------------+|Database|CreateDatabase|+----------+--------------------------------------------------------------+|mysql|CREATEDATABASE`mytest`/*!40100 DEFAULT CHARACTER SET utf8 */|+----------+--------------------------------------------------------------+

说明：

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

4.3 修改数据库

语法：

ALTERDATABASEdb_name[alter_specification[,alter_specification]...]alter_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name

说明：

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

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

mysql>alterdatabasemytestcharset=gbk;Query OK,1rowaffected(0.00sec)mysql>showcreatedatabasemytest;+----------+--------------------------------------------------------------+|Database|CreateDatabase|+----------+--------------------------------------------------------------+|mytest|CREATEDATABASE`mytest`/*!40100 DEFAULT CHARACTER SET gbk */|+----------+--------------------------------------------------------------+

4.4 数据库删除

DROPDATABASE[IFEXISTS]db_name;

执行删除之后的结果：

• 数据库内部看不到对应的数据库。
• 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。

注意：不要随意删除数据库。

4.5 备份和恢复（放在最后）

4.5.1 备份

核心说明：mysqldump是 MySQL 官方提供的备份工具，需在操作系统的命令行终端（而非 MySQL 客户端）执行，适用于所有 MySQL 版本。

基础语法：

# 完整格式（推荐）mysqldump -P 端口号 -u 用户名 -p 数据库名>备份文件存储路径# 带-B参数（备份时包含CREATE DATABASE语句，恢复更便捷）mysqldump -P 端口号 -u 用户名 -p -B 数据库名>备份文件存储路径

参数说明：

• -P：指定 MySQL 端口号（注意是大写 P，小写 p 是密码参数）；
• -u：指定连接的 MySQL 用户名；
• -p：表示需要输入密码（建议不直接写密码，回车后交互式输入更安全）；
• -B：备份时包含CREATE DATABASE和USE语句，恢复时无需手动创建 / 切换数据库。

示例：将mytest库备份到 D 盘根目录（安全写法）

# 执行后会提示输入密码，回车后输入123456即可mysqldump -P3306 -u root -p -B mytest>D:/mytest.sql

备份文件内容：mytest.sql会包含创建数据库、建表、插入数据的完整 SQL 语句，本质是纯文本的 SQL 脚本。

4.5.2 还原

方式 1：MySQL 客户端内执行（推荐）

需先登录 MySQL 客户端，再通过source命令执行备份脚本：

-- 登录MySQLmysql-u root-p-- 执行还原（路径需写备份文件的绝对路径）mysql>source D:/mytest.sql;

方式 2：命令行终端执行

无需登录 MySQL 客户端，直接在终端执行：

mysql -u root -p<D:/mytest.sql# 输入密码后自动完成还原

4.5.3 进阶备份场景

1. 备份单个 / 多张表（无需-B参数）：

   # 备份mytest库中的user表和order表mysqldump -P3306 -u root -p mytest user order>D:/mytest_tables.sql

2. 同时备份多个数据库：

   # 备份mytest、test1、test2三个数据库mysqldump -P3306 -u root -p -B mytest test1 test2>D:/multi_db.sql

4.5.4 关键注意事项

1. -B 参数的核心作用：

   • 带-B：备份文件包含CREATE DATABASE mytest和USE mytest，恢复时直接执行source即可；

   • 不带-B：备份文件仅包含表结构和数据，恢复前需手动创建空数据库并切换：

     -- 无-B参数时的还原步骤mysql>CREATEDATABASEIFNOTEXISTSmytest;mysql>USEmytest;mysql>source D:/mytest.sql;

2. 安全规范：

   • 避免在命令行直接写密码（如-p123456），防止密码泄露；
   • 备份文件建议定期归档，存储路径避免中文 / 空格（防止解析失败）。

3. 跨版本注意：

   • 低版本备份的脚本可在高版本还原，高版本备份的脚本需注意字符集（如 8.0 默认 utf8mb4）兼容问题。

4.6 查看连接情况

语法：

showprocesslist;

示例：

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

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

…过云雨-CSDN博客