MySQL数据库与表结构操作指南

前言：本文系统梳理MySQL核心操作语句。内容覆盖建库建表、结构调整、数据迁移全流程（包含创建/修改/删除/备份场景）。希望它们能帮你快速解决问题。

库结构操作

一、库的创建

一个库的简单创建：

create database 库名;

注意：database是单数，不要写成databases。 

比如创建一个名为d1的库：

直接创建：

create database d1;

判断该库是否存在，不存在则创建：

create database if not exists d1;

如下： 

除此之外在shell命令行中对应mysql目录下创建文件夹，同样相当于创建库： 

使用show databases;指令同样能查到： 

这里只是说明库的本质就是文件夹，但千万不要以这种方式创建库。

以下是删除库的语句，我们对d2进行删除：

drop database d2;

最后我们来看库创建的语法：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] create_specification: [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name

说明：

• 大写的表示关键字
• [] 是可选项
• CHARACTER SET: 指定数据库采用的字符集
• COLLATE: 指定数据库字符集的校验规则

1.编码与校验

        在创建库时可以在库名后面指定字符集和校验集。如果不带则默认使用配置文件中所设的字符集和校验集。

字符集，校验集的关系：

字符集：

• 相当于"字典"：定义所有可用字符的集合
• 为每个字符分配唯一的代码点（Code Point）
• 例如：ASCII字符集包含128个字符，Unicode字符集包含14+万字符

校验集：

• 相当于"使用规则"：定义字符的比较、排序和匹配规则
• 解决如何对文本数据进行查询、排序等操作
• 例如：是否区分大小写、重音符号如何处理。

查看数据库支持的字符集：

show charset;

查看数据库支持的校验集：

show collation;

创建库后，库的字符集和校验集会在文件db.opt中存储，如下：

utf8_general_ci：utf8格式，不区分大小写。

utf8_bin：utf8格式，区分大小写。

通常在查找指定数据或排序时体现出它们的区别。 

创建库时指明字符集和校验集的两种方式：

2.库的备份和恢复

2.1.备份

        数据库通常会带有备份工具，在shell命令行创建一个目录，这里命名为MySQL。在MySQL目录下使用以下指令进行备份：

mysqldump -P3306 -uroot -B 库名 > 生成备份文件名.sql

• mysqldump：备份指令
• -P3306：指定端口号
• -uroot：指定用什么用户身份
• -B：填写-B选项后恢复数据库时不用先创建该库。
• >： 重定向

对d1库在MySQL目录下进行备份：

事实上它备份的不是数据而是操作，我们可以把d1.sql打开查看。 

2.2.恢复

恢复库的语句：

source 备份文件的路径;

二、库的查改

查看一共有哪些库：

show databases;

查看某个库的详细信息（即创建库时带的信息）

show create database 库名;

修改库结构：

alter database 库名 选项(如编码集，校验集);

与创建相比只是把create改成了alter。 

表结构操作

进行表操作首先要进入库，使用use进入库：

use 库名;

查看当前所在的库： 

select database();

三、表的创建

语法：

CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;

说明：

• field：表示列名。
• datatype：表示列的类型。
• character set：字符集，如果没有指定字符集，则以所在数据库的字符集为准。
• collate：校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。

在/var/lib/mysql/d1中新增了对应的两个文件。

表的备份:

mysqldump -P3306 -uroot 库名 表名1,表名2,... > 备份的文件;

表的恢复和库的恢复相同。 

四、表的删改查

查看表结构

查看库中有哪些表：

show tables;

查看指定表信息： 

desc 表名;

查看指定表的详细信息（即创建时的信息）：

show create table 表名;

 或：

show create table 表名 \G

修改表结构

重命名：

alter table 原表名 rename to 新表名;

这里to可以省略。

新增列：

alter table 表名 add 新增列的属性(列名，类型，...) after 列1; 

 after选项可不加，表示把新增列放在列1后面。

改列属性：

alter table 表名 modify 列名 列的其他属性(类型，...);

注意它是把原来的列信息丢弃，新增列信息。而不是做局部修改，所以需要把列信息写全。 

改列名：

alter table 表名 change 原列名 新的列属性(新列名，类型，...);

删除列：

alter table 表名 drop 列名;

删除表： 

drop table 表名;