【MySQL篇】数据库操作：从创建到管理

文章目录数据库操作从创建到管理一、前言二、创建数据库2.1 基本语法2.2 创建数据库的三种方式方式一最简单的创建方式二指定字符集方式三同时指定字符集和校验规则2.3 IF NOT EXISTS 的用处三、字符集与校验规则3.1 什么是字符集3.2 什么是校验规则3.3 查看系统默认字符集3.4 查看系统默认校验规则3.5 查看系统支持的所有字符集3.6 查看系统支持的所有校验规则四、校验规则的实际影响4.1 案例区分大小写 vs 不区分大小写实验一不区分大小写查询实验二区分大小写查询4.2 案例排序规则的影响不区分大小写的排序区分大小写的排序4.3 校验规则选择建议五、操纵数据库5.1 查看所有数据库5.2 查看数据库的创建语句5.3 修改数据库字符集5.4 删除数据库六、数据库备份与恢复6.1 完整备份一个数据库语法示例备份 db2 数据库查看备份文件内容6.2 恢复数据库从备份文件恢复恢复后验证6.3 备份单个表6.4 同时备份多个数据库6.5 备份最佳实践七、查看数据库连接7.1 查看当前连接情况7.2 使用场景7.3 杀死连接八、总结与实践数据库操作从创建到管理一、前言这一篇讲什么学会操纵数据库本身核心内容如何创建数据库字符集和校验规则有什么区别如何修改、删除数据库如何备份和恢复数据在前一篇中我们成功安装了 MySQL并通过简单的例子了解了数据库的基本概念。现在需要深入学习如何实际操作数据库。这一篇的所有操作都是在 MySQL 命令行中执行的 SQL 语句。二、创建数据库2.1 基本语法创建数据库的标准语法如下CREATEDATABASE[IFNOTEXISTS]db_name[DEFAULTCHARACTERSETcharset_name][DEFAULTCOLLATEcollation_name];各参数说明参数说明IF NOT EXISTS可选。如果数据库已存在则不报错db_name要创建的数据库名称CHARACTER SET可选。指定数据库使用的字符集COLLATE可选。指定字符集的校验规则排序和比较规则2.2 创建数据库的三种方式方式一最简单的创建CREATEDATABASEdb1;这种方式不指定字符集和校验规则。MySQL 会使用系统默认值。在 Ubuntu 22.04 MySQL 8.0 中默认字符集是utf8mb4校验规则是utf8mb4_0900_ai_ci。方式二指定字符集CREATEDATABASEdb2DEFAULTCHARACTERSETutf8mb4;或者简写为CREATEDATABASEdb2CHARSETutf8mb4;这样创建的数据库将使用utf8mb4字符集校验规则仍然使用默认值。方式三同时指定字符集和校验规则CREATEDATABASEdb3DEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_unicode_ci;这是最完整的创建方式明确指定字符集和排序规则。2.3 IF NOT EXISTS 的用处如果数据库已经存在直接执行CREATE DATABASE会报错。使用IF NOT EXISTS可以避免这个错误CREATEDATABASEIFNOTEXISTSdb1;如果db1已存在这条语句不会报错也不会做任何操作。如果不存在就创建它。这在脚本中特别有用因为脚本可能执行多次。三、字符集与校验规则3.1 什么是字符集字符集定义了数据库中允许使用哪些字符以及这些字符如何编码。不同的字符集支持不同的语言。字符集支持语言说明utf8mb4所有语言推荐4 字节 UTF-8支持 emoji 表情是现代应用的标准utf8所有语言3 字节 UTF-8历史遗留新应用不推荐用latin1英文单字节占用空间少但只支持西欧字符gbk中文2 字节仅中文应用不兼容其他语言在互联网应用中强烈推荐使用utf8mb4。它支持所有语言和表情符号是国际标准。3.2 什么是校验规则校验规则Collation定义了字符的排序和比较方式。同一个字符集可能有多个校验规则。以utf8mb4为例常见的校验规则有校验规则说明utf8mb4_unicode_ciUnicode 标准排序ci表示 case insensitive不区分大小写utf8mb4_bin二进制比较区分大小写utf8mb4_general_ci通用排序不区分大小写较旧utf8mb4_0900_ai_ciMySQL 8.0 新增ai表示 accent insensitive不区分重音civsbin的区别cicase insensitiveA和a被视为相同。binbinaryA和a被视为不同严格按二进制值比较。3.3 查看系统默认字符集SHOWVARIABLESLIKEcharacter_set%;输出示例-----------------------------------------------------|Variable_name|Value|-----------------------------------------------------|character_set_client|utf8mb4||character_set_connection|utf8mb4||character_set_database|utf8mb4||character_set_filesystem|binary||character_set_results|utf8mb4||character_set_server|utf8mb4||character_set_system|utf8|-----------------------------------------------------这些变量控制 MySQL 在不同阶段使用的字符集。其中character_set_server是默认的字符集。3.4 查看系统默认校验规则SHOWVARIABLESLIKEcollation%;输出示例----------------------------------------|Variable_name|Value|----------------------------------------|collation_connection|utf8mb4_0900_ai_ci||collation_database|utf8mb4_0900_ai_ci||collation_server|utf8mb4_0900_ai_ci|----------------------------------------3.5 查看系统支持的所有字符集SHOWCHARSET;这会列出 MySQL 支持的所有字符集以及它们的默认校验规则。3.6 查看系统支持的所有校验规则SHOWCOLLATION;这会列出所有可用的校验规则。如果输出太多可以用WHERE过滤SHOWCOLLATIONWHERECharsetutf8mb4;四、校验规则的实际影响4.1 案例区分大小写 vs 不区分大小写现在通过两个实验直观感受校验规则的影响。实验一不区分大小写查询创建数据库和表CREATEDATABASEtest_ciCOLLATEutf8mb4_unicode_ci;USEtest_ci;CREATETABLEperson(nameVARCHAR(20));INSERTINTOpersonVALUES(a);INSERTINTOpersonVALUES(A);INSERTINTOpersonVALUES(b);INSERTINTOpersonVALUES(B);查询大写字母 ‘A’SELECT*FROMpersonWHEREnameA;输出结果------|name|------|a||A|------2rowsinset(0.00sec)在utf8mb4_unicode_ci校验规则下A和a被视为相同所以两条都返回了。实验二区分大小写查询创建数据库和表CREATEDATABASEtest_binCOLLATEutf8mb4_bin;USEtest_bin;CREATETABLEperson(nameVARCHAR(20));INSERTINTOpersonVALUES(a);INSERTINTOpersonVALUES(A);INSERTINTOpersonVALUES(b);INSERTINTOpersonVALUES(B);查询大写字母 ‘A’SELECT*FROMpersonWHEREnameA;输出结果------|name|------|A|------1rowinset(0.00sec)在utf8mb4_bin校验规则下只返回了大写字母 ‘A’。4.2 案例排序规则的影响校验规则不仅影响查询条件还影响排序结果。不区分大小写的排序在test_ci数据库中执行SELECT*FROMpersonORDERBYname;输出结果------|name|------|a||A||b||B|------因为不区分大小写小写字母和对应的大写字母按插入顺序排列。区分大小写的排序在test_bin数据库中执行SELECT*FROMpersonORDERBYname;输出结果------|name|------|A||B||a||b|------因为区分大小写大写字母的 ASCII 值小于小写字母所以所有大写字母排在前面。4.3 校验规则选择建议互联网应用推荐使用utf8mb4_unicode_ci。大多数应用中用户输入通常不区分大小写这样的设计更符合用户习惯。需要精确匹配使用utf8mb4_bin。比如存储密码哈希值必须精确匹配。系统字段考虑utf8mb4_bin。比如用户名、邮箱虽然显示上不区分但实际存储要精确避免冲突。五、操纵数据库5.1 查看所有数据库SHOWDATABASES;输出示例--------------------|Database|--------------------|information_schema||mysql||performance_schema||sys||db1||db2||db3|--------------------前四个是 MySQL 系统库不要删除。我们自己创建的数据库如db1、db2、db3显示在下面。5.2 查看数据库的创建语句SHOWCREATEDATABASEdb_name;这个命令会显示创建该数据库时使用的完整 SQL 语句包括字符集和校验规则。示例SHOWCREATEDATABASEdb2;输出------------------------------------------------------|Database|Create Database|------------------------------------------------------|db2|CREATE DATABASEdb2/*!40100DEFAULT|||CHARACTER SET utf8mb4 */|------------------------------------------------------输出说明反引号用来围绕数据库名防止名称恰好是 SQL 关键字时出错。/*!40100 ... */不是注释。40100表示 MySQL 版本号语义是如果 MySQL 版本 4.01就执行这句话。这是 MySQL 特有的版本控制语法。5.3 修改数据库字符集虽然不建议修改已使用的数据库字符集但语法是这样的ALTERDATABASEdb_nameDEFAULTCHARACTERSETcharset_name[DEFAULTCOLLATEcollation_name];示例将db2的字符集改为gbkALTERDATABASEdb2DEFAULTCHARACTERSETgbk;验证修改SHOWCREATEDATABASEdb2;注意修改数据库的字符集只影响之后创建的新表不会改变已有表中数据的字符集。如果需要完全转换通常的做法是导出数据删除旧库用新字符集重建。5.4 删除数据库DROPDATABASE[IFEXISTS]db_name;示例DROPDATABASEdb1;删除后的影响数据库从 MySQL 系统中消失再也看不到。数据库对应的磁盘文件夹被删除其中包含的所有表和数据全部丢失。使用IF EXISTS避免错误DROPDATABASEIFEXISTSdb1;如果db1存在就删除不存在也不报错。这在脚本中很有用。警告删除数据库是不可逆的操作。确保数据已备份后再执行删除。六、数据库备份与恢复在实际工作中定期备份数据库是必须的。数据库崩溃、硬件故障或误操作都可能导致数据丢失。备份是最后的保险。6.1 完整备份一个数据库语法mysqldump-hhost-Pport-uuser-ppassword-Bdatabase_namebackup_file.sql参数说明参数说明-hMySQL 服务器地址默认localhost-PMySQL 端口默认3306-u数据库用户名-p密码紧接在-p后面中间无空格-B备份整个数据库包含CREATE DATABASE语句示例备份 db2 数据库在 Ubuntu 的 bash 终端中执行不在 MySQL 命令行中mysqldump-uroot-p-Bdb2~/db2_backup.sql系统会提示输入密码。输入后备份文件db2_backup.sql会生成在用户主目录。查看备份文件内容cat~/db2_backup.sql备份文件包含了创建数据库和表的所有 SQL 语句以及数据的INSERT语句。6.2 恢复数据库从备份文件恢复在 MySQL 命令行中执行SOURCE~/db2_backup.sql;或者使用符号从 bash 中直接恢复退出 MySQL 后mysql-uroot-p~/db2_backup.sql系统会提示输入密码然后恢复数据库。恢复后验证SHOWDATABASES;USEdb2;SHOWTABLES;SELECT*FROMperson;确认数据是否恢复正确。6.3 备份单个表如果只想备份数据库中的某些表不备份整个库mysqldump-uroot-pdatabase_name table1 table2backup_tables.sql这样只备份database_name中的table1和table2。6.4 同时备份多个数据库mysqldump-uroot-p-Bdb1 db2 db3backup_multiple.sql-B参数后面可以跟多个数据库名用空格分隔。6.5 备份最佳实践定期备份根据数据重要性每天、每周或每月备份一次。备份到不同位置不要把备份文件和源数据库放在同一台机器上防止硬件故障导致同时丢失。测试恢复定期测试备份文件能否成功恢复确保备份可用。记录备份时间给备份文件名添加时间戳便于管理。mysqldump-uroot-p-Bdb2~/db2_backup_$(date%Y%m%d_%H%M%S).sql这样会生成类似db2_backup_20240407_143022.sql的文件名。七、查看数据库连接7.1 查看当前连接情况SHOWPROCESSLIST;这个命令显示当前所有连接到 MySQL 的客户端。输出示例--------------------------------------------------------------------|Id|User|Host|db|Command|Time|State|Info|--------------------------------------------------------------------|1|root|localhost|db1|Sleep|120||NULL||2|root|localhost|db2|Query|0|NULL|SHOW PROCESSLIST|--------------------------------------------------------------------字段说明字段说明Id连接的唯一标识可用于KILL命令User连接的用户名Host连接来自的主机 IP 和端口db当前连接使用的数据库Command连接执行的操作通常是Query、Sleep或ConnectTime连接持续的时间秒State连接的状态Info正在执行的 SQL 语句7.2 使用场景诊断数据库性能问题如果发现数据库查询变慢执行这个命令可以看到当前有哪些用户连接。他们正在执行什么操作。当前State或者Command持续多久了Time字段。是否有死连接长时间Sleep的连接。安全检查执行这个命令后如果看到陌生的用户或异常的连接来源可能表示数据库被入侵。7.3 杀死连接如果需要断开某个连接使用KILLconnection_id;示例断开 Id 为 1 的连接KILL1;八、总结与实践现在你已经掌握了✅创建数据库支持指定字符集和校验规则✅字符集与校验规则理解排序和比较规则的区别✅修改与删除ALTER DATABASE和DROP DATABASE的用法✅备份与恢复使用mysqldump备份用SOURCE或管道恢复✅连接管理通过SHOW PROCESSLIST监控数据库连接建议练习创建三个数据库分别使用不同的字符集和校验规则。在utf8mb4_unicode_ci和utf8mb4_bin的数据库中分别插入相同数据对比查询结果。备份其中一个数据库到文件删除原数据库然后恢复确保流程熟悉。下一篇我们将深入学习表的操作如何创建表、定义列、设置约束等。表是数据库中最重要的结构直接影响数据的组织和性能。