MySQL 数据库操作入门：从建库到备份，一篇讲清楚-平芜编程栈

1. 前言

最近在整理 MySQL 的学习笔记，发现数据库操作这块内容虽然基础，但知识点挺零散的。今天干脆把「库的操作」这部分系统地梳理一遍，从创建数据库、字符集设置，到修改、删除、备份恢复，一次讲明白。文章里的命令我都实际跑过，可以直接复制跟着敲。

2. 创建数据库

2.1 基本语法

先看语法结构：

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

几个要点说明一下：

大写的部分表示关键字，比如CREATE DATABASE
[]里的内容是可选项，不写也能用
CHARACTER SET：指定数据库采用的字符集
COLLATE：指定数据库字符集的校验规则

2.2 实际创建几个库试试

先创建一个最简单的：

createdatabasedb1;

这里有个小细节：当我们创建数据库没有指定字符集和校验规则时，系统会使用默认字符集utf8，校验规则是utf8_general_ci。

再创建一个指定 utf8 字符集的：

createdatabasedb2charset=utf8;

最后创建一个既指定字符集又带校对规则的：

createdatabasedb3charset=utf8collateutf8_general_ci;

3. 字符集和校验规则

3.1 查看系统默认配置

想知道当前系统的默认字符集和校验规则，可以这样查：

showvariableslike'character_set_database';showvariableslike'collation_database';

3.2 查看支持的字符集

showcharset;

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

3.3 查看支持的校验规则

showcollation;

3.4 校验规则对查询的影响

这一块挺有意思的，校验规则直接决定了查询时是否区分大小写。我们实际演示一下。

不区分大小写的情况

创建一个使用utf8_general_ci（不区分大小写）的数据库：

createdatabasetest1collateutf8_general_ci;usetest1;createtableperson(namevarchar(20));insertintopersonvalues('a');insertintopersonvalues('A');insertintopersonvalues('b');insertintopersonvalues('B');

然后查询name='a'：

mysql>select*frompersonwherename='a';+------+|name|+------+|a||A|+------+

可以看到，a和A都被查出来了，因为校验规则不区分大小写。

区分大小写的情况

再创建一个使用utf8_bin（区分大小写）的数据库：

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

同样查询name='a'：

mysql>select*frompersonwherename='a';+------+|name|+------+|a|+------+

这次只查出了小写的a，A被过滤掉了。

3.5 校验规则对排序的影响

校验规则同样影响排序结果。

不区分大小写排序：

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

区分大小写排序：

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

可以看到，utf8_bin下大写字母排在了小写前面。

4. 操纵数据库

4.1 查看数据库

showdatabases;

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_spacification[,alter_spacification]...]alter_spacification:[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 备份和恢复

备份和恢复这块建议放到最后来学，因为涉及的命令稍微多一点。

备份

基本语法：

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

示例：把mytest库备份到文件（需要退出 MySQL 连接后执行）：

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

备份完成后可以打开mytest.sql文件看看，其实里面把我们整个创建数据库、建表、导入数据的语句都装进去了。

还原

mysql>source D:/mysql-5.7.22/mytest.sql;

注意事项

有几个实际中经常遇到的场景：

只备份一张表：

# mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql

同时备份多个数据库：

# mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

没带 -B 参数的情况：

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

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|+----+------+-----------+------+---------+------+-------+------------------+