YAOTU INSIGHTS

MySQL：库与表的操作

发布时间：2026/11/6 1:51:51 来源：尧图网站开发
MySQL：库与表的操作
2. 库的操作在 MySQL 中数据是按库database→ 表table→ 行row的层级组织的。库是最高一级的逻辑容器一个 MySQL 服务端可以有多个库一个库可以有多个表。所以学习 MySQL 的第一步就是把库这一层的操作搞熟。2.1 创建数据库语法CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...];create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_namedb_name数据库名在 MySQL 中不区分大小写。IF NOT EXISTS可选如果库不存在才创建避免重复创建报错CHARACTER SET指定数据库字符集控制数据怎么存储编码COLLATE指定校验规则排序规则控制字符串比较、排序、大小写是否敏感创建数据库案例创建一个完全不指定字符集的库CREATEDATABASEIFNOTEXISTSdb1;这样创建出来的库字符集和校验规则都会使用 MySQL 的默认配置。创建一个使用 utf8 字符集、默认校验规则的库CREATEDATABASEIFNOTEXISTSdb2CHARACTERSETutf8;创建一个使用 utf8 字符集、utf8_general_ci 校验规则的库CREATEDATABASEIFNOTEXISTStest1CHARACTERSETutf8COLLATEutf8_general_ci;补充在 MySQL 的 SQL 语法中CHARSET是CHARACTER SET的合法同义词二者可以互换。例如下面三条语句完全等价CREATEDATABASEdb1CHARACTERSETutf8mb4;CREATEDATABASEdb1CHARSETutf8mb4;CREATEDATABASEdb1DEFAULTCHARSETutf8mb4;建表、改表、列定义、SHOW CHARSET等场景同理。但要注意系统变量名、命令行参数、配置文件项不能简写。2.2 字符集和校验规则概念作用举例字符集决定能存哪些字符、用什么编码utf8mb4、gbk、latin1校验规则决定字符之间怎么比较、怎么排序utf8mb4_0900_ai_ci、utf8mb4_bin2.2.1 查看系统默认字符集与校验规则-- 查看当前数据库默认字符集showvariableslikecharacter_set_database;-- 查看当前数据库默认校验规则showvariableslikecollation_database;2.2.2 查看数据库支持的字符集showcharset;-- 或showcharacterset;2.2.3 查看数据库支持的字符集校验规则showcollation;校验规则命名规律以utf8mb4_unicode_ci举例utf8mb4所属字符集unicode基于 unicode 标准排序ci case insensitive大小写不敏感A 和 a 认为相等cs case sensitive大小写敏感A≠abin二进制比较严格按字节对比区分大小写2.2.4 校验规则对数据库的影响第一步建两个库字符集相同、校验规则不同CREATEDATABASEIFNOTEXISTStest1CHARACTERSETutf8COLLATEutf8_general_ci;CREATEDATABASEIFNOTEXISTStest2CHARACTERSETutf8COLLATEutf8_bin;第二步分别在两个库中建同样的表插入相同数据-- 在 test1 中USEtest1;CREATETABLEperson(nameVARCHAR(20));INSERTINTOpersonVALUES(a),(A),(b),(B);-- 在 test2 中USEtest2;CREATETABLEperson(nameVARCHAR(20));INSERTINTOpersonVALUES(a),(A),(b),(B);第三步执行相同的查询WHERE name a-- test1utf8_general_ci不区分大小写USEtest1;SELECT*FROMpersonWHEREnamea;结果-- test2utf8_bin区分大小写USEtest2;SELECT*FROMpersonWHEREnamea;结果第四步执行排序ORDER BY-- test1USEtest1;SELECT*FROMpersonORDERBYname;结果-- test2USEtest2;SELECT*FROMpersonORDERBYname;结果2.3 操纵数据库2.3.1 查看数据库SHOWDATABASES;查看当前所在的库selectdatabase();切换到某个库usedb_name;2.3.2 显示数据库的创建语句查看当初建库完整定义看字符集和 collationshowcreatedatabasemydb;输出示例/*!40100 default.... */这个不是注释表示当前mysql版本大于4.01版本就执行这句话2.3.3 修改数据库语法ALTERDATABASEdb_name[alter_specification[,alter_specification]...];alter_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name重要说明ALTER DATABASE只能修改字符集和校验规则不能修改库名重命名库不支持只能备份后删库重建或新建库导数据修改字符集后已有的表不会自动变化只影响之后新建的表表的字符集在建表时确定生产环境改字符集需要谨慎务必先备份。示例把test2的字符集改为gbkalterdatabasetest2charactersetgbk;2.3.4 删除数据库DROPDATABASE[IFEXISTS]db_name;高危操作警告执行DROP DATABASE后库及库内所有表、所有数据全部删除且无法回滚生产环境删库前务必1.先备份mysqldump2.确认当前不在目标库中SELECT DATABASE();3.二次确认库名尤其别把test1写成test2.3.5 备份和恢复备份和恢复用的是 MySQL 客户端工具mysqldump和mysql不是在 mysql 交互界面里执行需要在操作系统的终端 / 命令行中运行。2.3.5.1 备份语法mysqldump-P 端口-u 用户名-p密码-B 数据库名备份文件路径-B表示备份整个库且在备份文件中会包含CREATE DATABASE语句不带-B时只备份库中的表不包含建库语句。示例 1备份单个库mysqldump-uroot-p-B数据库名备份文件路径示例 2一次备份多个库mysqldump-uroot-p-B数据库名1 数据库名2...数据库存放路径示例 3只备份某张表mysqldump-uroot-p数据库名 表名1 表名2备份文件路径2.3.5.2 还原在 mysql 交互界面中 sourcemysqlsource 备份文件路径;如果备份一个数据库时没有带上-B参数在恢复数据库时需要先创建空数据库然后使用数据库再使用source来还原。2.3.6 查看连接情况用来查看当前有哪些客户端连上 MySQL、正在执行什么 SQL、线程状态排查卡死、慢查询。语法showprocesslist;示例字段解释Id连接线程 IDUser连接用户Host客户端主机db当前所在库Command当前命令状态Sleep表示空闲、Query表示正在执行查询Time该状态持续秒数State内部状态Info正在执行的 SQL。3. 表的操作3.1 创建表CREATETABLE[IFNOTEXISTS]表名(字段名1数据类型[COMMENT字段注释],字段名2数据类型[COMMENT字段注释],...)[CHARACTERSET字符集][COLLATE校验规则][ENGINE存储引擎];创建表案例createtableifnotexistsusers(idintcomment用户ID,namevarchar(20)comment用户名,passwordchar(32)comment密码是32位的md5值,birthdaydatecomment生日)charactersetutf8engineMyISAM;不同的存储引擎创建表的文件不一样。users表存储引擎是MyISAM在数据目中有三个不同的文件分别是users.frm表结构users.MYD表数据users.MYI表索引示例3.2 查看表结构desc表名;示例查看完整建表语句推荐showcreatetable表名;示例这是查看表结构最完整的方式能看到字段注释、字符集、存储引擎、索引等全部信息。3.3 修改表表创建后经常需要调整比如加字段、改类型、加约束。ALTER TABLE是修改表结构的核心命令。1. 修改表名altertable表名renameto新表名;示例2. 添加列语法altertable表名add列名 数据类型[comment字段注释][first|after列名];示例默认追加到末尾用after指定位置更灵活。但表结构的列顺序在业务上并不重要不必过度纠结。3. 修改列MODIFY / CHANGEMODIFY只改类型/约束不改列名-- 把 name 从 VARCHAR(20) 改为 VARCHAR(60)并保留 NOT NULL 约束altertableusermodifynamevarchar(60)NOTNULLcomment姓名;重点MODIFY必须写出完整的列定义否则未写出的约束会被丢弃。比如原列是name VARCHAR(20) NOT NULL DEFAULT 如果你只写MODIFY name VARCHAR(50)那么NOT NULL和默认值就没了示例CHANGE改名 改类型-- 把 name 改名为 xingming同时改为 VARCHAR(60)altertableuserchange name xingmingvarchar(60)NOTNULLcomment学生姓名;示例4. 删除列altertable表名drop列名;示例3.4 删除表删除表结构和全部数据表彻底消失。DROPTABLE[IFEXISTS]表名;-- 一次删多张表DROPTABLEIFEXISTSt1,t2,t3;