一、MySQL创建数据表

在 MySQL 中，可以使用 CREATE TABLE 语句创建表。CREATE TABLE 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。

其语法格式为：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [<数据库名>.]<表名> 
(
  [表结构定义选项]
) [表选项] [分区选项];

CREATE TABLE 语句的主要语法及使用说明如下：

• “[]” 方括号：代表内部的语法可有可无。
• CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。
• IF NOT EXISTS ：在创建表之前会判断表是否存在，存在则无法创建，提示报错。
• <表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，’mydb’.’mytbl’ 是合法的，但 ‘mydb.mytbl’ 不合法。
• [表定义选项] ：可以定义表的多个字段，完整性约束、索引等。

实例1，这是一个简单的例子

CREATE TABLE `test` (
    `id`  int NOT NULL AUTO_INCREMENT ,
    `name`  varchar(20) DEFAULT '' ,
    PRIMARY KEY (`id`)
);

说明：该例子创建一个表名为test的表，字段id，类型为int，非空not null，自增AUTO_INCREMENT；字段name,类型为varchar，长度为20，默认值为空字符串；设置主键为id。

1、表结构定义选项

[表结构定义选项] ：可以定义表的多个字段，完整性约束、索引等。其格式如下：

[字段定义项]
[完整性约束定义]
[索引定义项]

#其中 [完整性约束定义] 可分为如下
[主键约束定义项]
[唯一约束定义项]
[外键约束定义项]
[检查约束定义项]

对于初学者来说，可以先只了解字段定义项和主键约束定义项。

[字段定义项] 包含字段名称、字段类型、字段约束定义、字符类型字段的字符集和校对规则、字段注释、可以定义多条。格式如下：

<字段1名称> <类型1>  [字段约束定义] [[DEFAULT] CHARACTER SET charset_name  [DEFAULT] COLLATE collation_name] [comment '字段1的注释']
[,...]
[,<字段n名称> <类型n> 字段约束定义] [[DEFAULT] CHARACTER SET charset_name  [DEFAULT] COLLATE collation_name] [comment '字段n的注释']

其中，[DEFAULT] CHARACTER SET设置字符集，[DEFAULT] COLLATE设置字段的校对规则，comment为字段的注释。（mysql字符集和校对规则详情请查看我的其他相关文章）

字段约束定义：包含主键约束（primary key）、主键自增（auto_increment）、非空约束（not null）、唯一约束（unique）、默认值（default）。完整的字段定义项语法如下：

<字段1> <类型1>  [primary key] [auto_increment] [not null] [unique] [default 默认值] [comment '字段1的注释']
[,<字段n> <类型n> [primary key] [auto_increment] [not null] [unique] [default 默认值]] [comment '字段n的注释']

[表定义选项]的其他部分语法格式：

#主键约束定义项，单独一行。或者在字段约束定义里定义主键约束（上面primary key已说明）
[CONSTRAINT <约束名>] primary key(`某个字段` [,`其他字段1` [,`其他字段n`]])

#唯一约束 唯一约束也是索引的一种
UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…

#外键约束定义项
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

#检查约束定义项
CHECK <表达式>

#索引定义项
[unique][key|index][<索引名>] [<索引类型>] (<字段名>,…)

后面的文章会对这几个部分做更详细的介绍。

2、表选项

表选项就是，创建一个表的时候，对该表的整体设定，主要有如下几个：

• charset 要使用的字符编码，
• COLLATE 字符的校对规则
• engine 要使用的存储引擎（也叫表类型）。mysql常用的引擎有InnoDB, MyISAM, BDB, archive, Memory。通过show engines查看mysql支持的数据库引擎
• auto_increment 设定当前表的自增长字段的初始值，默认是1
• comment 表注释
• 还有其他表选项，限于篇幅，其他表选项将在其他文章说明。

实例：

CREATE TABLE `test` (
    `id`  int NOT NULL AUTO_INCREMENT ,
    `name`  varchar(20) DEFAULT '' ,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
COMMENT='test'
AUTO_INCREMENT=100

二、MySQL修改数据表 ALTER TABLE

在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。语法格式如下：

ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

1、mysql表添加新字段

mysql表添加新字段的语法格式如下：

ALTER TABLE <表名> 
ADD COLUMN <新字段名><数据类型>[约束条件] [FIRST|AFTER <已有的字段>]
[,ADD COLUMN <新字段名><数据类型>[约束条件] [FIRST|AFTER <已有的字段>] ];

说明：

• [约束条件] 是可选的，用来对添加的字段进行约束。
• first 在开头位置（第一列的前面）添加新字段，MySQL 默认在表的最后位置添加新字段。
• after 新字段添加到某个已有的字段后面。.
• 可以有多个ADD COLUMN，添加多个新字段，用英文逗号隔开。

ALTER TABLE `student`
ADD COLUMN `s_num`  int NOT NULL DEFAULT 0 COMMENT '编号' AFTER `s_sex`,
ADD COLUMN `s_addree`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '家庭住址' AFTER `s_num`;

2、mysql表修改字段

mysql表修改字段的语法有两个。分别是change和modify，如果不修改字段名称，使用modify。

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型和约束>;

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

说明

• 旧字段名：指修改前的字段名；
• 新字段名：指修改后的字段名；
• 新数据类型和约束：指修改后的数据类型和约束，如果不需要修改这部分，需要和之前的完全一样，否则就会被修改。
• 可以有多个CHANGE COLUMN，修改多个字段，用英文逗号隔开。

ALTER TABLE `student`
CHANGE COLUMN `s_birth` `s_birth_day`  varchar(20) NOT NULL DEFAULT '' AFTER `s_name`,
CHANGE COLUMN `s_sex` `s_sex2`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' AFTER `s_birth_day`;

#用modify
ALTER TABLE `student`
MODIFY COLUMN `s_birth`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `s_name`;

3、mysql表删除字段

mysql表删除字段的格式：

ALTER TABLE <表名> DROP <字段名>；

实例：

ALTER TABLE `student`
DROP COLUMN `s_name`,
DROP COLUMN `s_birth`;

4、mysql修改表名

mysql修改表名的格式如下：

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

#实例
ALTER TABLE student RENAME TO tb_students_info;

5、mysql修改表字符集、校对规则、引擎、自增长值等表选项。

ALTER TABLE `student`
ENGINE=MyISAM,
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci,
AUTO_INCREMENT=500;

三、MySQL删除数据表

在删除表的同时，表的结构和表中所有的数据都会被删除，使用 DROP TABLE 语句可以删除一个或多个数据表，语法格式如下：

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

对语法格式的说明如下：

• 表名1, 表名2, 表名3 …表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
• IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。
• 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。
• 表被删除时，用户在该表上的权限不会自动删除。