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 什么是字符集

字符集决定了数据库能存储哪些语言的文字。常见的字符集有：

2.2 什么是校验规则

校验规则（Collation）决定了字符串比较和排序时的行为，最核心的差异是是否区分大小写。

ci是 Case Insensitive 的缩写，bin表示按二进制方式比较。

2.3 查看系统默认配置

-- 查看当前默认字符集SHOWVARIABLESLIKE'character_set_database';-- 查看当前默认校验规则SHOWVARIABLESLIKE'collation_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');

第三步：对比查询结果

不区分大小写查询（test1）：

USEtest1;SELECT*FROMpersonWHEREname='a';

结果：返回a和A两条记录，因为utf8_general_ci认为a和A是相同的。

区分大小写查询（test2）：

USEtest2;SELECT*FROMpersonWHEREname='a';

结果：只返回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 改为 gbk

ALTERDATABASEmydb1CHARACTERSETgbk;

验证修改是否生效：

SHOWCREATEDATABASEmydb1;

返回结果中字符集已经变成gbk。

5. 删除数据库

语法

DROPDATABASE[IFEXISTS]数据库名;

示例

DROPDATABASEIFEXISTSmydb1;

删除操作的影响

执行删除后会发生以下事情：

1. 该数据库在SHOW DATABASES结果中消失
2. 该数据库对应的文件夹从磁盘上被删除
3. 库中的所有表和数据全部被删除，且无法恢复

重要提醒：删除数据库是不可逆操作，生产环境务必谨慎。建议删除前先做好备份。

6. 备份与恢复

6.1 备份整个数据库

在终端（不是 MySQL 客户端）执行：

mysqldump-P3306-uroot-p123456-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.sql

6.3 同时备份多个数据库

mysqldump-uroot-p123456-B数据库名1 数据库名2>/path/to/backup.sql

6.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 个文件）：

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 | | +----------+-------------+------+-----+---------+-------+

各列含义：

查看建表语句

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 改为 60

ALTERTABLEusersMODIFYnameVARCHAR(60);

3.3 修改字段名称

ALTERTABLE表名 CHANGE 旧字段名 新字段名 新数据类型;

示例：将name字段改名为xingming

ALTERTABLEusers CHANGE name xingmingVARCHAR(60);

注意：CHANGE操作需要同时指定新字段名和完整的数据类型，不能省略类型。

3.4 删除字段

ALTERTABLE表名DROP字段名;

示例：删除password字段

ALTERTABLEusersDROPpassword;

重要提醒：删除字段会同时删除该字段的所有数据，操作前请确认是否需要备份。

3.5 修改表名

ALTERTABLE旧表名RENAMETO新表名;

或者省略TO：

ALTERTABLE旧表名RENAME新表名;

示例：将users表改名为employee

ALTERTABLEusersRENAMETOemployee;

3.6 修改字段操作对比

4. 删除数据表

语法

DROP[TEMPORARY]TABLE[IFEXISTS]表名1[,表名2...];

示例

-- 删除单个表DROPTABLEIFEXISTSusers;-- 同时删除多个表DROPTABLEIFEXISTSt1,t2,t3;

附录：常用命令速查表

数据库操作

数据表操作

字符集与校验规则