草莓熊Lotso个人主页❄️个人专栏:《C知识分享》 《Linux 入门到实践零基础也能懂》✨生活是默默的坚持毅力是永久的享受 博主简介文章目录前言一. 数据库库的核心操作1.1 创建数据库指定字符集与校验规则1.1.1 语法格式1.1.2 实战案例1.2 字符集与校验规则影响查询和排序1.2.1 查看系统默认配置1.2.2 查看支持的字符集和校验规则1.2.3 校验规则的实际影响1.3 操纵数据库查询、修改、删除1.3.1 查看所有数据库1.3.2 查看数据库创建语句1.3.3 修改数据库仅字符集和校验规则1.3.4 删除数据库谨慎操作1.4 数据库备份与恢复避免数据丢失1.4.1 备份退出 MySQL 客户端执行1.4.2 恢复在 MySQL 客户端执行1.5 查看数据库连接排查并发问题二. 数据表表的核心操作2.1 创建表指定字段、类型、存储引擎2.1.1 语法格式2.1.2 实战案例2.1.3 不同存储引擎的文件差异2.2 查看表结构验证表设计2.3 修改表适配业务需求变更2.3.1 常用修改操作语法2.3.2 实战案例2.3.3 注意事项2.4 删除表谨慎操作三. 总结与避坑指南结尾前言在上一篇 MySQL 基础入门中我们了解了数据库的基本概念和简单操作。而在实际开发中数据库和表的创建、修改、备份、删除等操作是日常高频需求掌握这些精准操作能避免数据丢失、提升开发效率。本文将基于 MySQL 实战场景详细拆解库与表的完整操作流程包括字符集选择、表结构设计、备份恢复等核心知识点带你从 “会用” 进阶到 “活用” MySQL。一. 数据库库的核心操作数据库是表的容器合理的库操作是数据管理的基础。下面涵盖库的创建、查询、修改、删除、备份恢复等关键操作同时详解字符集和校验规则的影响。1.1 创建数据库指定字符集与校验规则创建数据库时不仅要定义库名还需根据业务场景指定字符集如支持中文的utf8和校验规则如是否区分大小写避免后续出现乱码或查询异常。1.1.1 语法格式CREATEDATABASE[IFNOTEXISTS]db_name[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name;IF NOT EXISTS避免重复创建数据库报错可以不加但是这里推荐加CHARACTER SET指定数据库字符集默认utf8COLLATE指定字符集的校验规则默认utf8_general_ci。1.1.2 实战案例-- 1. 创建默认字符集的数据库db1CREATEDATABASEIFNOTEXISTSdb1;-- 2. 创建指定utf8字符集的数据库db2CREATEDATABASEIFNOTEXISTSdb2CHARACTERSETutf8;-- 3. 创建指定字符集和校验规则的数据库db3CREATEDATABASEIFNOTEXISTSdb3CHARACTERSETutf8COLLATEutf8_general_ci;1.2 字符集与校验规则影响查询和排序字符集决定了数据的存储编码如是否支持中文校验规则则影响字符串的比较和排序如是否区分大小写这是容易被忽略但关键的细节。1.2.1 查看系统默认配置-- 查看默认字符集showvariableslikecharacter_set_database;-- 查看默认校验规则showvariableslikecollation_database;1.2.2 查看支持的字符集和校验规则-- 查看所有支持的字符集showcharset;-- 查看所有支持的校验规则showcollation;1.2.3 校验规则的实际影响以 “是否区分大小写” 为例对比两种常用校验规则utf8_general_ci不区分大小写cicase insensitiveutf8_bin区分大小写binbinary按二进制比较。案例演示-- 1. 创建不区分大小写的数据库test1CREATEDATABASEtest1COLLATEutf8_general_ci;USEtest1;CREATETABLEperson(namevarchar(20));INSERTINTOpersonVALUES(a),(A),(b),(B);-- 查询namea返回a和A不区分大小写SELECT*FROMpersonWHEREnamea;-- 排序按字母顺序排序不区分大小写SELECT*FROMpersonORDERBYname;-- 2. 创建区分大小写的数据库test2CREATEDATABASEtest2COLLATEutf8_bin;USEtest2;CREATETABLEperson(namevarchar(20));INSERTINTOpersonVALUES(a),(A),(b),(B);-- 查询namea仅返回a区分大小写SELECT*FROMpersonWHEREnamea;-- 排序按二进制ASCII码排序大写在前小写在后SELECT*FROMpersonORDERBYname;1.3 操纵数据库查询、修改、删除1.3.1 查看所有数据库showdatabases;1.3.2 查看数据库创建语句验证数据库的字符集、校验规则等配置showcreatedatabasedb3;输出样例--------------------------------------------------------------------------|Database|Create Database|--------------------------------------------------------------------------|db3|CREATE DATABASEdb3/*!40100DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci */|--------------------------------------------------------------------------反引号 防止库名与关键字冲突/*!40100 ... */条件执行MySQL 版本≥4.0.10 时生效。1.3.3 修改数据库仅字符集和校验规则数据库创建后仅支持修改字符集和校验规则不支持修改库名需通过备份恢复间接修改-- 将db3的字符集改为gbkALTERDATABASEdb3CHARACTERSETgbk;1.3.4 删除数据库谨慎操作删除数据库会级联删除所有表和数据且无法恢复DROPDATABASEIFEXISTSdb3;1.4 数据库备份与恢复避免数据丢失备份恢复是数据库运维的核心技能支持全库备份、单表备份、多库备份。1.4.1 备份退出 MySQL 客户端执行语法mysqldump-P端口-u用户名-p密码-B 数据库名备份文件路径补充说明备份单表mysqldump -uroot -p 数据库名 表名1 表名2 备份文件路径备份多库mysqldump -uroot -p -B 数据库名1 数据库名2 ... 备份文件路径。1.4.2 恢复在 MySQL 客户端执行-- 恢复整个数据库source 备份文件路径;注意若备份时未加-B参数恢复前需先创建空数据库并切换CREATEDATABASEIFNOTEXISTSmytest;USEmytest;source 备份文件路径;1.5 查看数据库连接排查并发问题当数据库响应缓慢时可查看当前连接情况排查异常连接如被入侵showprocesslist;输出样例-------------------------------------------------------------------|Id|User|Host|db|Command|Time|State|Info|-------------------------------------------------------------------|2|root|localhost|test1|Sleep|120||NULL||3|root|localhost|NULL|Query|0|NULL|show processlist|-------------------------------------------------------------------Command连接状态Sleep为空闲Query为执行中Time连接持续时间秒Info执行的 SQL 语句。二. 数据表表的核心操作表是存储数据的核心载体表结构的设计和修改直接影响业务开发下面涵盖表的创建、查看、修改、删除全流程。2.1 创建表指定字段、类型、存储引擎创建表时需明确字段名、数据类型、字符集、存储引擎等同时可通过comment添加字段说明。2.1.1 语法格式CREATETABLEtable_name(field1 datatype[comment字段说明],field2 datatype[comment字段说明],...)CHARACTERSET字符集COLLATE校验规则ENGINE存储引擎;2.1.2 实战案例USEmytest;CREATETABLEusers(idintcomment用户ID,namevarchar(20)comment用户名,passwordchar(32)comment密码是32位的MD5加密值,birthdaydatecomment生日)CHARACTERSETutf8ENGINEMyISAM;2.1.3 不同存储引擎的文件差异MySQL 支持插件式存储引擎不同引擎的表文件存储格式不同MyISAM示例中使用users.frm表结构文件users.MYD表数据文件users.MYI表索引文件InnoDB默认引擎users.frm表结构文件users.ibd表数据 索引文件聚簇索引结构。2.2 查看表结构验证表设计-- 简洁查看表结构descusers;-- 详细查看表结构含注释showcreatetableusers;2.3 修改表适配业务需求变更项目开发中表结构需频繁适配业务变更如添加字段、修改字段类型等ALTER TABLE是核心指令。2.3.1 常用修改操作语法操作类型语法示例添加字段ALTER TABLE表名ADD字段名类型[comment‘说明’] [AFTER已有字段名]修改字段类型ALTER TABLE表名MODIFY字段名新类型修改字段名 类型ALTER TABLE表名CHANGE旧字段名新字段名新类型删除字段ALTER TABLE表名DROP字段名修改表名ALTER TABLE旧表名RENAME TO新表名TO可省略2.3.2 实战案例USEmytest;-- 1. 给users表添加字段assets图片路径放在birthday之后ALTERTABLEusersADDassetsvarchar(100)comment图片路径AFTERbirthday;-- 2. 修改name字段长度为60适配更长的用户名ALTERTABLEusersMODIFYnamevarchar(60);-- 3. 删除password字段假设密码存储方式变更ALTERTABLEusersDROPpassword;-- 4. 修改表名为employeeALTERTABLEusersRENAMEemployee;-- 5. 将name字段改为xingming适配中文命名习惯ALTERTABLEemployee CHANGE name xingmingvarchar(60);2.3.3 注意事项添加字段新字段默认允许为NULL不会影响原有数据修改字段类型若字段已有数据需确保新类型兼容旧数据如varchar转int可能失败删除字段字段及对应数据会永久删除需提前备份。2.4 删除表谨慎操作删除表会删除表结构和所有数据无法恢复DROPTABLEIFEXISTSemployee;TEMPORARY仅删除临时表CREATE TEMPORARY TABLE创建的表DROPTEMPORARYTABLEIFEXISTStemp_table;三. 总结与避坑指南本文覆盖了 MySQL 库与表的全流程操作核心要点总结如下创建数据库时建议明确指定CHARACTER SET utf8和校验规则避免乱码(也可以提前去自己配置好)校验规则决定字符串比较逻辑需根据业务场景选择如用户名是否区分大小写备份恢复是数据安全的保障重要数据库需定期备份备份时建议添加-B参数修改表结构时删除字段和修改字段类型需格外谨慎避免数据丢失存储引擎选择InnoDB 支持事务和行级锁默认推荐MyISAM 查询速度快适合只读场景。常见避坑点库名、表名、字段名避免使用 MySQL 关键字如order、user若必须使用需加反引号 备份时未加-B参数恢复前需手动创建数据库并切换数据库不支持直接修改库名需通过 “备份→删除旧库→恢复为新库名” 实现字段类型选择需合理如密码用char(32)存储 MD5 值生日用date类型避免浪费空间或存储异常关于类型问题我们后面还会进行更加详细的学习。结尾 我是草莓熊 Lotso若这篇技术干货帮你打通了学习中的卡点 【关注】跟我一起深耕技术领域从基础到进阶见证每一次成长 ❤️ 【点赞】让优质内容被更多人看见让知识传递更有力量 ⭐ 【收藏】把核心知识点、实战技巧存好需要时直接查、随时用 【评论】分享你的经验或疑问比如曾踩过的技术坑一起交流避坑 ️ 【投票】用你的选择助力社区内容方向告诉大家哪个技术点最该重点拆解 技术之路难免有困惑但同行的人会让前进更有方向愿我们都能在自己专注的领域里一步步靠近心中的技术目标结语掌握库与表的操作是 MySQL 开发的基础下一篇将深入讲解 MySQL 数据类型、约束主键、外键、唯一索引等进阶知识点。创作不易觉得有帮助的话欢迎点赞、收藏、关注三连 若有操作疑问或场景需求欢迎在评论区留言交流✨把这些内容吃透超牛的放松下吧✨ʕ˘ᴥ˘ʔづきらど