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 字符集的createdatabasedb2charsetutf8;最后创建一个既指定字符集又带校对规则的createdatabasedb3charsetutf8collateutf8_general_ci;3. 字符集和校验规则3.1 查看系统默认配置想知道当前系统的默认字符集和校验规则可以这样查showvariableslikecharacter_set_database;showvariableslikecollation_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);然后查询nameamysqlselect*frompersonwherenamea;------|name|------|a||A|------可以看到a和A都被查出来了因为校验规则不区分大小写。区分大小写的情况再创建一个使用utf8_bin区分大小写的数据库createdatabasetest2collateutf8_bin;usetest2;createtableperson(namevarchar(20));insertintopersonvalues(a);insertintopersonvalues(A);insertintopersonvalues(b);insertintopersonvalues(B);同样查询nameamysqlselect*frompersonwherenamea;------|name|------|a|------这次只查出了小写的aA被过滤掉了。3.5 校验规则对排序的影响校验规则同样影响排序结果。不区分大小写排序mysqlusetest1;mysqlselect*frompersonorderbyname;------|name|------|a||A||b||B|------区分大小写排序mysqlusetest2;mysqlselect*frompersonorderbyname;------|name|------|A||B||a||b|------可以看到utf8_bin下大写字母排在了小写前面。4. 操纵数据库4.1 查看数据库showdatabases;4.2 显示创建语句showcreatedatabase数据库名;实际效果mysqlshowcreatedatabasemytest;--------------------------------------------------------------------------|Database|CreateDatabase|--------------------------------------------------------------------------|mysql|CREATEDATABASEmytest/*!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数据库字符集改成gbkmysqlalterdatabasemytestcharsetgbk;Query OK,1rowaffected(0.00sec)mysqlshowcreatedatabasemytest;--------------------------------------------------------------------------|Database|CreateDatabase|--------------------------------------------------------------------------|mytest|CREATEDATABASEmytest/*!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文件看看其实里面把我们整个创建数据库、建表、导入数据的语句都装进去了。还原mysqlsource 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;实际效果mysqlshowprocesslist;-------------------------------------------------------------------|Id|User|Host|db|Command|Time|State|Info|-------------------------------------------------------------------|2|root|localhost|test|Sleep|1386||NULL||3|root|localhost|NULL|Query|0|NULL|showprocesslist|-------------------------------------------------------------------这个命令可以告诉我们当前有哪些用户连接到 MySQL。如果查出某个用户不是你正常登录的那很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时也可以用这个指令来查看数据库连接情况。5. 小结数据库操作这块核心就是建库、设字符集、改库、删库、备份恢复这几件事。字符集和校验规则的选择会影响查询和排序的行为建议在建库时就考虑清楚。备份恢复虽然平时用得不多但真出问题的时候能救命值得花点时间练熟。