MySQL库与表的操作 MySQL 库与表的操作实战指南本文涵盖数据库和数据表的增删改查、字符集配置、备份恢复等核心操作所有命令均可直接复制执行。目录第一部分数据库库的操作1. 创建数据库2. 字符集与校验规则2.1 什么是字符集2.2 什么是校验规则2.3 查看系统默认配置2.4 查看 MySQL 支持的所有字符集2.5 查看 MySQL 支持的所有校验规则2.6 校验规则的实际影响重点理解3. 查看数据库4. 修改数据库5. 删除数据库6. 备份与恢复6.1 备份整个数据库6.2 备份指定的表6.3 同时备份多个数据库6.4 恢复数据库7. 查看连接情况第二部分数据表表的操作1. 创建数据表2. 查看表结构3. 修改表结构3.1 添加新字段3.2 修改字段类型或长度3.3 修改字段名称3.4 删除字段3.5 修改表名3.6 修改字段操作对比4. 删除数据表附录常用命令速查表数据库操作数据表操作字符集与校验规则第一部分数据库库的操作1. 创建数据库基本语法CREATEDATABASE[IFNOTEXISTS]数据库名[DEFAULT]CHARACTERSET字符集名[DEFAULT]COLLATE校验规则名;语法说明CREATE DATABASE创建数据库的关键字必须项[IF NOT EXISTS]可选项加上后如果数据库已存在不会报错而是给出警告CHARACTER SET指定数据库使用的字符集不指定则使用系统默认值COLLATE指定字符集的校验规则不指定则使用字符集对应的默认规则创建示例示例 1最简单的创建方式CREATEDATABASEmydb1;这条命令创建了一个名为mydb1的数据库字符集和校验规则都采用系统默认值通常是utf8和utf8_general_ci。示例 2显式指定字符集CREATEDATABASEmydb2CHARACTERSETutf8;示例 3同时指定字符集和校验规则CREATEDATABASEmydb3CHARACTERSETutf8COLLATEutf8_general_ci;示例 4避免重复创建时报错CREATEDATABASEIFNOTEXISTSmydb1;如果mydb1已经存在不会抛出错误而是返回一条警告信息。2. 字符集与校验规则2.1 什么是字符集字符集决定了数据库能存储哪些语言的文字。常见的字符集有字符集说明utf8支持中文、英文等多语言最常用gbk支持中文兼容老系统latin1只支持西欧字符不支持中文utf8mb4utf8 的超集支持 emoji 表情等 4 字节字符2.2 什么是校验规则校验规则Collation决定了字符串比较和排序时的行为最核心的差异是是否区分大小写。校验规则大小写敏感说明utf8_general_ci不区分默认规则查询时不区分大小写utf8_bin区分按二进制比较严格区分大小写ci是 Case Insensitive 的缩写bin表示按二进制方式比较。2.3 查看系统默认配置-- 查看当前默认字符集SHOWVARIABLESLIKEcharacter_set_database;-- 查看当前默认校验规则SHOWVARIABLESLIKEcollation_database;2.4 查看 MySQL 支持的所有字符集SHOWCHARSET;2.5 查看 MySQL 支持的所有校验规则SHOWCOLLATION;2.6 校验规则的实际影响重点理解校验规则会直接影响查询结果和排序行为下面通过一个完整的实验来演示。第一步分别创建两个数据库-- 创建使用不区分大小写规则的数据库CREATEDATABASEtest1COLLATEutf8_general_ci;-- 创建使用区分大小写规则的数据库CREATEDATABASEtest2COLLATEutf8_bin;第二步在两个库中分别建表并插入相同数据-- 在 test1 中操作USEtest1;CREATETABLEperson(nameVARCHAR(20));INSERTINTOpersonVALUES(a);INSERTINTOpersonVALUES(A);INSERTINTOpersonVALUES(b);INSERTINTOpersonVALUES(B);-- 在 test2 中操作USEtest2;CREATETABLEperson(nameVARCHAR(20));INSERTINTOpersonVALUES(a);INSERTINTOpersonVALUES(A);INSERTINTOpersonVALUES(b);INSERTINTOpersonVALUES(B);第三步对比查询结果不区分大小写查询test1USEtest1;SELECT*FROMpersonWHEREnamea;结果返回a和A两条记录因为utf8_general_ci认为a和A是相同的。区分大小写查询test2USEtest2;SELECT*FROMpersonWHEREnamea;结果只返回a一条记录因为utf8_bin认为a和A是不同的。第四步对比排序结果-- 不区分大小写的排序USEtest1;SELECT*FROMpersonORDERBYname;-- 结果a, A, b, B小写和大写混合排列-- 区分大小写的排序USEtest2;SELECT*FROMpersonORDERBYname;-- 结果A, B, a, b大写字母排在前面因为大写字母的 ASCII 值更小实际开发建议大多数业务场景使用utf8_general_ci就够了。只有在需要严格区分大小写的场景如密码比对、验证码校验才使用utf8_bin。3. 查看数据库3.1 列出所有数据库SHOWDATABASES;3.2 查看指定数据库的创建语句SHOWCREATEDATABASE数据库名;这条命令会返回创建该数据库时的完整 SQL 语句包括字符集和校验规则信息。示例SHOWCREATEDATABASEmydb1;返回结果类似-------------------------------------------------------------------------- | Database | Create Database | -------------------------------------------------------------------------- | mydb1 | CREATE DATABASE mydb1 /*!40100 DEFAULT CHARACTER SET utf8 */ | --------------------------------------------------------------------------返回结果中的特殊符号说明反引号用于包裹数据库名防止名称与 SQL 关键字冲突/*!40100 ... */这不是注释而是 MySQL 的版本兼容语法表示如果 MySQL 版本 4.01就执行这段语句4. 修改数据库数据库创建后可以修改的内容主要是字符集和校验规则。语法ALTERDATABASE数据库名[DEFAULT]CHARACTERSET新字符集名[DEFAULT]COLLATE新校验规则名;示例将字符集从 utf8 改为 gbkALTERDATABASEmydb1CHARACTERSETgbk;验证修改是否生效SHOWCREATEDATABASEmydb1;返回结果中字符集已经变成gbk。5. 删除数据库语法DROPDATABASE[IFEXISTS]数据库名;示例DROPDATABASEIFEXISTSmydb1;删除操作的影响执行删除后会发生以下事情该数据库在SHOW DATABASES结果中消失该数据库对应的文件夹从磁盘上被删除库中的所有表和数据全部被删除且无法恢复重要提醒删除数据库是不可逆操作生产环境务必谨慎。建议删除前先做好备份。6. 备份与恢复6.1 备份整个数据库在终端不是 MySQL 客户端执行mysqldump-P3306-uroot-p123456-B数据库名/path/to/backup.sql参数说明参数含义-P3306指定 MySQL 端口号-u root指定登录用户名-p123456指定密码注意-p和密码之间没有空格-B关键参数表示备份整个数据库包含建库语句 /path/to/backup.sql指定备份文件的保存路径实际示例mysqldump-P3306-uroot-p123456-Bmydb1/home/backup/mydb1.sql备份生成的.sql文件本质上是一系列 SQL 语句的集合包含了建库、建表、插入数据的完整流程。6.2 备份指定的表mysqldump-uroot-p123456数据库名 表名1 表名2/path/to/backup.sql6.3 同时备份多个数据库mysqldump-uroot-p123456-B数据库名1 数据库名2/path/to/backup.sql6.4 恢复数据库在 MySQL 客户端中执行SOURCE/path/to/backup.sql;注意事项如果备份时没有使用-B参数恢复时需要先手动创建空数据库然后切换到该库再执行SOURCE命令。CREATEDATABASEmydb1;USEmydb1;SOURCE/path/to/backup.sql;7. 查看连接情况语法SHOWPROCESSLIST;示例输出------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ------------------------------------------------------------------- | 2 | root | localhost | test | Sleep | 1386 | | NULL | | 3 | root | localhost | NULL | Query | 0 | NULL | show processlist | -------------------------------------------------------------------结果说明Id连接的唯一标识User连接使用的用户名Host连接来源的地址db当前使用的数据库Command当前正在执行的命令类型Sleep表示空闲Query表示正在执行查询Time状态持续的时间秒Info正在执行的 SQL 语句实际用途排查数据库连接慢的问题查看是否有大量空闲连接检查是否有异常连接如果发现不认识的用户或来源地址可能存在安全风险第二部分数据表表的操作1. 创建数据表基本语法CREATETABLE表名(字段名1数据类型[COMMENT字段说明],字段名2数据类型[COMMENT字段说明],字段名3数据类型[COMMENT字段说明])CHARACTERSET字符集ENGINE存储引擎;语法说明字段名列的名称数据类型列存储数据的类型如INT、VARCHAR、DATE等COMMENT字段的备注说明方便团队协作理解CHARACTER SET指定表的字符集不指定则继承数据库的字符集ENGINE指定存储引擎常见的有InnoDB默认和MyISAM创建示例CREATETABLEusers(idINTCOMMENT用户ID,nameVARCHAR(20)COMMENT用户名,passwordCHAR(32)COMMENT密码32位MD5值,birthdayDATECOMMENT生日)CHARACTERSETutf8ENGINEMyISAM;存储引擎与文件的关系不同的存储引擎在磁盘上生成的文件不同MyISAM 引擎每个表生成 3 个文件文件作用表名.frm存储表的结构定义表名.MYD存储表中的数据表名.MYI存储表的索引信息InnoDB 引擎默认引擎表结构和数据存储在系统表空间中不像 MyISAM 那样每个表独立文件支持事务、行级锁、外键等高级特性实际开发建议除非有特殊需求使用默认的 InnoDB 引擎即可。2. 查看表结构查看表的字段信息DESC表名;或者使用完整写法DESCRIBE表名;输出示例-------------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------------- | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | password | char(32) | YES | | NULL | | | birthday | date | YES | | NULL | | --------------------------------------------------各列含义列名含义Field字段名称Type字段的数据类型和长度Null是否允许为空YES 允许NO 不允许Key索引类型PRI 主键UNI 唯一索引MUL 普通索引Default字段的默认值Extra额外信息如AUTO_INCREMENT自增查看建表语句SHOWCREATETABLE表名;3. 修改表结构在实际项目中表结构经常需要调整比如增删字段、修改字段类型、更改表名等。3.1 添加新字段ALTERTABLE表名ADD字段名 数据类型[COMMENT说明][AFTER已有字段名];示例在users表的birthday字段后面添加一个assets字段ALTERTABLEusersADDassetsVARCHAR(100)COMMENT图片路径AFTERbirthday;验证结果DESCusers;--------------------------------------------------- | Field | Type | Null | Key | Default | Extra | --------------------------------------------------- | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | password | char(32) | YES | | NULL | | | birthday | date | YES | | NULL | | | assets | varchar(100) | YES | | NULL | | ---------------------------------------------------新增字段对表中已有的数据没有影响原有记录在新字段上的值为NULL。3.2 修改字段类型或长度ALTERTABLE表名MODIFY字段名 新数据类型;示例将name字段的长度从 20 改为 60ALTERTABLEusersMODIFYnameVARCHAR(60);3.3 修改字段名称ALTERTABLE表名 CHANGE 旧字段名 新字段名 新数据类型;示例将name字段改名为xingmingALTERTABLEusers CHANGE name xingmingVARCHAR(60);注意CHANGE操作需要同时指定新字段名和完整的数据类型不能省略类型。3.4 删除字段ALTERTABLE表名DROP字段名;示例删除password字段ALTERTABLEusersDROPpassword;重要提醒删除字段会同时删除该字段的所有数据操作前请确认是否需要备份。3.5 修改表名ALTERTABLE旧表名RENAMETO新表名;或者省略TOALTERTABLE旧表名RENAME新表名;示例将users表改名为employeeALTERTABLEusersRENAMETOemployee;3.6 修改字段操作对比操作命令能改名能改类型MODIFYALTER TABLE 表名 MODIFY 字段 新类型;不能能CHANGEALTER TABLE 表名 CHANGE 旧名 新名 新类型;能能DROPALTER TABLE 表名 DROP 字段名;删除字段删除字段4. 删除数据表语法DROP[TEMPORARY]TABLE[IFEXISTS]表名1[,表名2...];示例-- 删除单个表DROPTABLEIFEXISTSusers;-- 同时删除多个表DROPTABLEIFEXISTSt1,t2,t3;附录常用命令速查表数据库操作操作命令创建数据库CREATE DATABASE 数据库名;创建数据库避免重复报错CREATE DATABASE IF NOT EXISTS 数据库名;查看所有数据库SHOW DATABASES;查看建库语句SHOW CREATE DATABASE 数据库名;修改字符集ALTER DATABASE 数据库名 CHARACTER SET 新字符集;删除数据库DROP DATABASE IF EXISTS 数据库名;备份数据库mysqldump -u root -p -B 数据库名 backup.sql恢复数据库SOURCE /path/to/backup.sql;查看连接SHOW PROCESSLIST;数据表操作操作命令创建表CREATE TABLE 表名 (字段 类型, ...);查看表结构DESC 表名;查看建表语句SHOW CREATE TABLE 表名;添加字段ALTER TABLE 表名 ADD 字段 类型 AFTER 已有字段;修改字段类型ALTER TABLE 表名 MODIFY 字段 新类型;修改字段名ALTER TABLE 表名 CHANGE 旧名 新名 新类型;删除字段ALTER TABLE 表名 DROP 字段名;修改表名ALTER TABLE 旧表名 RENAME TO 新表名;删除表DROP TABLE IF EXISTS 表名;字符集与校验规则操作命令查看默认字符集SHOW VARIABLES LIKE character_set_database;查看默认校验规则SHOW VARIABLES LIKE collation_database;查看支持的字符集SHOW CHARSET;查看支持的校验规则SHOW COLLATION;