2. 库的操作

在 MySQL 中，数据是按库（database）→ 表（table）→ 行（row）的层级组织的。库是最高一级的逻辑容器，一个 MySQL 服务端可以有多个库，一个库可以有多个表。所以学习 MySQL 的第一步，就是把"库"这一层的操作搞熟。

2.1 创建数据库

语法

CREATEDATABASE[IFNOTEXISTS]db_name[create_specification[,create_specification]...];create_specification:[DEFAULT]CHARACTERSETcharset_name[DEFAULT]COLLATEcollation_name

• db_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 字符集和校验规则

2.2.1 查看系统默认字符集与校验规则

-- 查看当前数据库默认字符集showvariableslike'character_set_database';-- 查看当前数据库默认校验规则showvariableslike'collation_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≠a）
• bin：二进制比较，严格按字节对比，区分大小写

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'

-- test1（utf8_general_ci，不区分大小写）USEtest1;SELECT*FROMpersonWHEREname='a';

结果：

-- test2（utf8_bin，区分大小写）USEtest2;SELECT*FROMpersonWHEREname='a';

结果：

第四步：执行排序ORDER BY

-- test1USEtest1;SELECT*FROMpersonORDERBYname;

结果：

-- test2USEtest2;SELECT*FROMpersonORDERBYname;

结果：

2.3 操纵数据库

2.3.1 查看数据库

SHOWDATABASES;

查看当前所在的库：

selectdatabase();

切换到某个库：

usedb_name;

2.3.2 显示数据库的创建语句

查看当初建库完整定义，看字符集和 collation：

showcreatedatabasemydb;

输出示例：

• /*!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的字符集改为gbk

alterdatabasetest2charactersetgbk;

2.3.4 删除数据库

DROPDATABASE[IFEXISTS]db_name;

高危操作警告：

• 执行DROP DATABASE后，库及库内所有表、所有数据全部删除，且无法回滚；
• 生产环境删库前务必：
  1.先备份（mysqldump）；
  2.确认当前不在目标库中（SELECT DATABASE();）；
  3.二次确认库名（尤其别把test1写成test）；

2.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 交互界面中 source

mysql>source 备份文件路径;

• 如果备份一个数据库时，没有带上-B参数，在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

2.3.6 查看连接情况

用来查看当前有哪些客户端连上 MySQL、正在执行什么 SQL、线程状态，排查卡死、慢查询。

语法：

showprocesslist;

示例：

字段解释：

• Id：连接线程 ID；
• User：连接用户；
• 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 / CHANGE）

MODIFY：只改类型/约束，不改列名

-- 把 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;