【MySQL】表结构的增删查改操作（DDL）

大家好，欢迎来到IT知识分享网。

一. 创建表

语法：

create table [if not exists] 表名( field1 datatype, field2 datatype, field3 datatype ) [charset 字符集 collate 校验规则 engine 存储引擎]; 

说明：

• [ ]内的可写可不写
• field 表示字段名。
• datatype 表示字段类型。
• 最后的字符集、校验规则和存储引擎可以不指定，这时就用默认的。
• 还可以在字段类型最后面加上comment '描述信息'，用来描述该字段，这样在用desc查看表结构时可读性会更好。

举例：
1、先创建一个数据库，发现在该库的数据目录中只有一个叫做db.opt的库文件：

2、在库里创建一张叫做user_1的表，存储引擎采用MyISAM，发现在库的数据目录下多了三个表相关的文件：

PS：user_1表所使用的存储引擎是MyISAM ，所在库的数路径下多了三个不同的文件，它们的意义如下：

• user_1.frm：描述表结构。
• user_1.MYD：描述表数据。
• user_1.MYI：描述表索引。

3、不同的存储引擎，创建表的文件不一样。接下来创建一个引擎是InnoDB的表user_2：

继续观察数据库的数据目录，发现以存储引擎InnoDB创建的user_2表产生了两个文件：

• user_2.frm：描述表结构。
• user_2.ibd：描述表的数据和索引。

二. 删除表

语法：

drop table 表名; 

举例：

// 1、查看当前库中所有的表 mysql> show tables; +-------------------+ | Tables_in_ForTest | +-------------------+ | user_1 | | user_2 | +-------------------+ 2 rows in set (0.00 sec) // 2、删除表user_1 mysql> drop table user_1; Query OK, 0 rows affected (0.00 sec) // 3、再次查看所有的表，发现user_1不在了 mysql> show tables; +-------------------+ | Tables_in_ForTest | +-------------------+ | user_2 | +-------------------+ 1 row in set (0.00 sec) 

三. 查看表

1、输入show tables;来查看当前库下所有的表。记得table要加s：

// 1、首先进入ForTest这个数据库 mysql> use ForTest; Database changed // 2、查看当前库中所有的表 mysql> show tables; +-------------------+ | Tables_in_ForTest | +-------------------+ | user_1 | | user_2 | +-------------------+ 2 rows in set (0.00 sec) 

2、输入desc 表名;查看具体某张表的结构：

mysql> desc user_1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.01 sec) 

表结构说明：

3、输入show create table 表名;可以查看表创建时候的信息：

PS：如果觉得这种横线的排版方式不方便看的话，还可以用 \G代替最后的分号以更简洁的排版方式输出表的创建信息：

四. 修改表

1、修改整张表的名称

语法：alter table 表的旧名称 rename [to] 新名称;

举例：先创建一张名叫employee的表，然后把表名改成users：

mysql> create table if not exists employee( -> ame varchar(32), -> age smallint -> ); Query OK, 0 rows affected (0.04 sec) mysql> show tables; +-------------------+ | Tables_in_ForTest | +-------------------+ | employee | +-------------------+ 1 row in set (0.00 sec) mysql> alter table employee rename to users; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_ForTest | +-------------------+ | users | +-------------------+ 

2、修改表中某个字段的名称

语法：alter table 表名 change 旧字段名称 新字段名称 类型...;

注意：相当于重写定义字段。

举例：

mysql> desc users; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ame | varchar(32) | YES | | NULL | | | age | smallint(6) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table users change ame name varchar(50); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc users; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | | age | smallint(6) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 

3、修改表中字段的类型

语法：alter table 表名 modify 原字段名称 修改后的类型;

说明：仅仅修改某个字段的类型。

举例：
修改age字段的类型为int：

mysql> desc users; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | | age | smallint(6) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table users modify age int; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc users; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 

4、在表中添加一个字段

语法：alter table 表名 add 字段名称 类型...;

举例：
在表中新增一个gender字段，用来标识用户的性别：

mysql> desc users; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table users add gender varchar(10); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc users; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | | age | int(11) | YES | | NULL | | | gender | varchar(10) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 

补充：
新插入的字段默认排到表的最后位置，如果想指定排到某个特定字段之后，新增时可以在最后添加：after 目标字段：

5、删除表中的一个字段

语法：alter table 表名 drop 需要删除的字段名称;

举例：
删除表中的gender字段：

mysql> desc users; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | | gender | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table users drop gender; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc users; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)