MySQL 数据库操作详解：从创建到备份

目录1. 前言2. ️ 库的操作2.1 创建数据库2.3 字符集和校验规则2.4 操纵数据库3. 总结4. 经典面试题1. 前言在 MySQL 的学习与面试中对数据库本身的操作是基石中的基石。无论是创建项目、设计表结构还是进行数据迁移与恢复都离不开对数据库的增删改查。本文将基于 MySQL 官方文档与实践经验系统性地梳理数据库的创建、字符集选择、查看、修改、删除、备份与恢复等核心操作并附上经典面试题助你轻松应对面试挑战。2. ️ 库的操作2.1 创建数据库语法CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...]create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name说明大写的表示关键字CREATE、DATABASE、IF NOT EXISTS等是 SQL 关键字建议大写以增强可读性。[]是可选项IF NOT EXISTS表示如果数据库不存在才创建避免重复创建报错。CHARACTER SET指定数据库采用的字符集例如utf8、gbk。COLLATE指定数据库字符集的校验规则例如utf8_general_ci、utf8_bin。创建数据库案例1. 创建名为db1的数据库createdatabasedb1;说明当我们创建数据库没有指定字符集和校验规则时系统使用默认字符集utf8校验规则是utf8_general_ci。2. 创建一个使用utf8字符集的db2数据库createdatabasedb2charsetutf8;3. 创建一个使用utf8字符集并带校对规则的db3数据库createdatabasedb3charsetutf8collateutf8_general_ci;2.3 字符集和校验规则2.3.1 查看系统默认字符集以及校验规则showvariableslikecharacter_set_database;showvariableslikecollation_database;2.3.2 查看数据库支持的字符集showcharset;字符集主要是控制用什么语言。比如utf8就可以使用中文。2.3.3 查看数据库支持的字符集校验规则showcollation;2.3.4 校验规则对数据库的影响校验规则决定了数据库在比较字符时是否区分大小写、排序规则等。我们通过两个例子来直观感受。1. 不区分大小写创建一个数据库校验规则使用utf8_general_ci不区分大小写。createdatabasetest1collateutf8_general_ci;usetest1;createtableperson(namevarchar(20));insertintopersonvalues(a);insertintopersonvalues(A);insertintopersonvalues(b);insertintopersonvalues(B);2. 区分大小写创建一个数据库校验规则使用utf8_bin区分大小写。createdatabasetest2collateutf8_bin;usetest2;createtableperson(namevarchar(20));insertintopersonvalues(a);insertintopersonvalues(A);insertintopersonvalues(b);insertintopersonvalues(B);进行查询不区分大小写的查询以及结果mysqlusetest1;mysqlselect*frompersonwherenamea;------|name|------|a||A|------2rowsinset(0.01sec)区分大小写的查询以及结果mysqlusetest2;mysqlselect*frompersonwherenamea;------|name|------|a|------2rowsinset(0.01sec)结果排序不区分大小写排序以及结果mysqlusetest1;mysqlselect*frompersonorderbyname;------|name|------|a||A||b||B|------区分大小写排序以及结果mysqlusetest2;mysqlselect*frompersonorderbyname;------|name|------|A||B||a||b|------总结utf8_general_ci在比较时会将a和A视为相同排序时也不区分大小写而utf8_bin则会严格区分排序时大写字母会排在小写字母之前基于 ASCII 码。2.4 操纵数据库2.4.1 查看数据库showdatabases;2.4.2 显示创建语句showcreatedatabase数据库名示例mysqlshowcreatedatabasemytest;--------------------------------------------------------------------------|Database|CreateDatabase|--------------------------------------------------------------------------|mysql|CREATEDATABASEmytest/*!40100 DEFAULT CHARACTER SET utf8 */|--------------------------------------------------------------------------说明MySQL 建议我们关键字使用大写但是不是必须的。数据库名字的反引号是为了防止使用的数据库名刚好是关键字。/*!40100 default.... */这个不是注释表示当前 mysql 版本大于 4.01 版本就执行这句话。查看当前处于哪一个数据库中selectdatabase();2.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 */|--------------------------------------------------------------------------同时修改字符集和校验规则alterdatabasetest2charsetgbkcollategbk_chinese_ci;2.4.4 数据库删除语法DROPDATABASE[IFEXISTS]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 还原mysqlsource 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 查看连接情况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如果查出某个用户不是你正常登陆的很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时可以用这个指令来查看数据库连接情况。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 namea会同时匹配到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 的连接信息包括用户、主机、正在执行的命令等。这个命令的实际用途是排查数据库连接问题例如当数据库响应变慢时可以查看是否有大量异常连接或长时间未关闭的连接甚至可以用来发现潜在的安全入侵。