写在前面

本篇正式进入数据库相关操作的学习，会使用数据定义语言（Data Define Language）来对数据库、数据表进行一些管理操作。

本篇主要学习的内容如下所示：（1）创建库；（2）删除库；（3）列出库的信息；（4）建库的通用命令；（5）创建表；（6）查看数据表结构：（7）修改表；（8）表设置备注；（9）删除表；（10）复制表；（11）修改表的存储引擎；（12）增加列；（13）修改列；（14）删除列；（15）几个问题等。

同时需要说明的是，本文所有命令中包含的[]内的信息属于可选项，开发者可按照实际情况进行选用。

库管理

创建库

创建库的语法格式如下：

1	create database [if not exists] 库名;

删除库

删除库的语法格式如下：

1	drop database [if exists] 库名;

列出库的信息

开发者可以使用如下命令来列出库的信息：

1	show databases like '数据库名';

建库的通用命令

一般都会先判断是否存在某个数据库，如果存在则先删除该库，然后才重新创建；如果不存在则直接创建，此时建库的通用命令如下：

1 2	drop database if exists 旧数据库名; create database 新数据库名;

例子1：下例完整展示了如何先查询数据库是否存在，如果存在就删除旧库，然后再创建新库的流程：

mysql> drop database if exists envythink;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> create database envythink;
Query OK, 1 row affected (0.01 sec)

mysql> show databases like 'envythink';
+----------------------+
| Database (envythink) |
+----------------------+
| envythink            |
+----------------------+
1 row in set (0.00 sec)

表管理

创建表

请注意在创建表之前，必须先使用use 数据库名;来选择需要使用的数据库。

创建表的语法格式如下所示：

create table 表名(
    字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

上述命令中存在几个注意点：
（1）在同一张数据表中，字段名不允许重复；
（2）字段名和类型是必须项，但是宽度和约束条件却是可选项；
（3）类型用于限制字段必须以何种数据类型来存储记录；
（4）类型也是对字段的约束，约束字段下的记录必须是指定类型；
（5）类型之后的约束条件，是类型之外的额外添加的约束；
（6）常用的约束条件有：not null、default value、primary key(pk)、foreign key(fk)、unique key(uq)、auto_increment等；
（7）注意最后一个字段后面不能添加逗号。

下面对上述提到的一些约束条件进行详细介绍，掌握它们对提升自身技能有非常大的帮助。

非空约束

非空约束(not null constraint)，指字段的值不能为空，非空约束的语法规则如下：

1	字段名数据类型 not null

例子2：非空约束示例：

mysql> use envybook;
Database changed

mysql> drop table if exists test1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test1(
    -> a int not null comment '字段a'
    -> );
Query OK, 0 rows affected (0.64 sec)

mysql> insert into test1 values(null);
ERROR 1048 (23000): Column 'a' cannot be null
mysql> insert into test1 values(6);
Query OK, 1 row affected (0.04 sec)

mysql> select * from test1;
+---+
| a |
+---+
| 6 |
+---+
1 row in set (0.02 sec)

默认约束

默认约束(default value constraint)，为该字段设置默认值，默认约束的语法规则如下：

1	字段名数据类型 default 默认值

例子3：默认约束示例：

mysql> drop table if exists test2;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> create table test2(
    -> a int not null comment '字段a',
    -> b int not null default 8 comment '字段b'
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> insert into test2(a) values(6);
Query OK, 1 row affected (0.14 sec)

mysql> select * from test2;
+---+---+
| a | b |
+---+---+
| 6 | 8 |
+---+---+
1 row in set (0.00 sec)

可以看到在上面插入的时候并为设置b的值，那么它就会自动去默认值8。给字段设置默认值在某些场合非常有用。

主键约束

主键约束(primary key constraint)，要求主键列的数据唯一（不可重复），且不允许为空。主键能够唯一标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，可加快数据库查询的速度。

主键分两种类型：单字段主键和多字段联合主键。

（a) 单字段主键
单字段主键由一个字段组成，SQL语句格式分为以下两种情况。

（1）在定义列的同时指定主键，语法规则为：

1	字段名数据类型 PRIMARY KEY [默认值]

例子4：单字段主键，在定义列的同时指定主键的示例：

mysql> drop table if exists test3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test3(
    -> a int not null comment '字段a' primary key
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> insert into test3(a)values(1);
Query OK, 1 row affected (0.13 sec)

mysql> insert into test3(a)values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

（2）在定义完所有列之后指定主键，语法规则为：

1	[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

例子5：单字段主键，在定义完所有列之后指定主键的示例：

mysql> drop table if exists test4;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test4(
    -> a int not null comment '字段a',
    -> b int not null default 1 comment '字段b',
    -> primary key(a)
    -> );
Query OK, 0 rows affected (0.38 sec)

mysql> insert into test4(a,b)values(1,1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into test4(a,b)values(1,6);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

可以看到，如果某个字段被设置为主键，那么插入重复的值就会报出违反主键约束的提示语。

（b) 多字段联合主键
此时的主键是由多个字段联合组成，多个字段之间使用逗号进行分隔，语法规则为：

1	PRIMARY KEY [字段1，字段2，...，字段n]

例子6：多字段联合主键的示例：

mysql> drop table if exists test5;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table test5(
    -> a int not null comment '字段a',
    -> b int not null comment '字段b',
    -> primary key(a,b)
    -> );
Query OK, 0 rows affected (0.38 sec)

mysql> insert into test5(a,b) values(1,1);
Query OK, 1 row affected (0.13 sec)

mysql> insert into test5(a,b) values(1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

外键约束

外键约束(foreign key constraint)，用于在两个表的数据间建立链接，可以是一列或者多列。一个表可有一个或多个外键。外键对应的是参照完整性约束，一个表的外键可以为空值，若不为空值，则每个外键值必须等于另一个表中主键的某个值。

外键首先是表中的一个字段，可以不是本表的主键，但对应另外一个表的主键。外键主要作用是保持数据引用的完整性，定义外键后不允许删除在另一个表中具有关联关系的行。

主表，相关联字段中主键所在的那个表是主表；相关联字段中外键所在的那个表是从表。

创建外键的语法规则如下：

1	[constraint <外键名>] foreign key 字段名1 [,字段名2,...] references <主表名> 主键列1 [,主键列2,...]

“外键名”为定义的外键约束的名称，一个表中不能有相同名称的外键；“字段名”表示子表需要添加外键约束的字段列；“主表名”即被子表外键所依赖的表的名称；“主键列”表示主表中定义的主键列，或者列组合。

还有几点需要注意：（1）两张表中需要建立外键关系的字段类型需要完全一致；（2）要设置外键的字段不能为主键；（3）被引用的字段需要为主键；（4）从表中插入的值在主表中必须存在。

例子7：外键约束的示例：

mysql> drop table if exists test6;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists test7;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test6(
    -> a int not null comment '字段a' primary key
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> create table test7(
    -> b int not null comment '字段b',
    -> t6_a int not null,
    -> foreign key(t6_a) references test6(a)
    -> );
Query OK, 0 rows affected (0.50 sec)

mysql> insert into test6(a) values(1);
Query OK, 1 row affected (0.12 sec)

mysql> insert into test7(b,t6_a)values(1,1);
Query OK, 1 row affected (0.15 sec)

mysql> insert into test7(b,t6_a)values(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`envybook`.`test7`, CONSTRAINT `test7_ibfk_1` FOREIGN KEY (`t6_a`) REFERENCES `test6` (`a`))

从运行结果报错可以知道，test7表中的t6_a字段的值来源于test6表中a字段的值，但是此处从表test7中的2在主表test6中不存在，因此就报错了。

唯一性约束

唯一性约束(unique key constraint)，用于要求该列唯一，允许为空，可以包含NULL值，并且每个NULL值都是唯一的（即NULL！=NULL）。唯一约束可以确保一列或者几列不出现重复值。

唯一性约束的语法规则如下：

（1）在定义完列之后直接指定唯一约束，语法规则如下：

1	字段名数据类型 unique

例子8：唯一性约束，在定义完列之后直接指定唯一约束，的示例：

mysql> drop table if exists test8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test8(
    -> a int not null comment '字段a' unique key
    -> );
Query OK, 0 rows affected (0.49 sec)

mysql> insert into test8(a) values(1);
Query OK, 1 row affected (0.13 sec)

mysql> insert into test8(a) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'

（2）在定义完所有列之后才指定唯一约束，语法规则如下：

1	[constraint <约束名>] unique(<字段名>)

例子9：唯一性约束，在定义完所有列之后才指定唯一约束，的示例：

mysql> drop table if exists test9;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table test9(
    -> a int not null comment '字段a',
    -> unique key(a)
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> insert into test9(a) values(1);
Query OK, 1 row affected (0.12 sec)

mysql> insert into test9(a) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'

（3）多字段设置唯一键，此时的唯一键有多个字段，多个字段之间使用逗号进行分隔，语法规则为：

1	UNIQUE KEY [字段1，字段2，...，字段n]

例子10：多字段设置唯一键的示例：

mysql> drop table if exists test10;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> create table test10(
    -> a int not null comment '字段a',
    -> b int not null comment '字段b',
    -> unique key(a,b)
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> insert into test10(a,b) values(1,1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into test10(a,b) values(1,1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'a'

表的属性值自动增加

表的属性值自动增加(auto_increment)，用于标识该字段的值自动增长，注意该字段必须是主键的一部分（一个表只能有一个字段使用auto_increment约束），可以使用任何整数类型(tinyint、smallint、int、bigint等)。

例子11：表的属性值自动增加的示例：

mysql> drop table if exists test11;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test11(
    -> a int not null auto_increment primary key comment '字段a',
    -> b int not null comment '字段b'
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> insert into test11(b) values (100);
Query OK, 1 row affected (0.06 sec)

mysql> insert into test11(b) values (200);
Query OK, 1 row affected (0.08 sec)

mysql> select * from test11;
+---+-----+
| a | b   |
+---+-----+
| 1 | 100 |
| 2 | 200 |
+---+-----+
2 rows in set (0.00 sec)

从执行结果中可以看到，我们并没有给字段a赋值，但是它却有值，因为它设置了auto_incremet字段，默认值是1，每次执行都会加1。如果开发者想自定义自动增长字段的初始值、步长可以在mysql中进行设置，如设置初始值为100，每次增长10等。

请注意，自增长列的当前值是保存在内存中的，每次重启数据库后，系统会查询当前表中自增列的最大值作为当前值；如果表数据被清除且数据库重启了，那么自增长列的值将从初始值开始。

例子12：仅清除数据库中的数据，不重启数据库，可以发现系统会查询当前表中自增列的最大值作为当前值：

mysql> delete from test11;
Query OK, 2 rows affected (0.11 sec)

mysql> insert into test11(b) values(10);
Query OK, 1 row affected (0.19 sec)

mysql> select * from test11;
+---+----+
| a | b  |
+---+----+
| 3 | 10 |
+---+----+
1 row in set (0.00 sec)

可以看到上面清除了test11数据库中的数据，然后插入了一条b字段值为10的记录，可以发现此时a字段的值变成了3。

接下来尝试清除test11数据库中的数据，同时重启数据库，然后再次插入一条b字段值为100的记录，查看此时a字段的值：

mysql> delete from test11;
Query OK, 1 row affected (0.20 sec)

mysql> select * from test11;
Empty set (0.00 sec)

mysql> exit
Bye

C:\WINDOWS\system32>net stop mysql
MySQL 服务正在停止..
MySQL 服务已成功停止。

C:\WINDOWS\system32>net start mysql
MySQL 服务正在启动 ...
MySQL 服务已经启动成功。

C:\WINDOWS\system32>mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use envybook;
Database changed

mysql> select * from test11;
Empty set (0.05 sec)

mysql> insert into test11(b) values(100);
Query OK, 1 row affected (0.06 sec)

mysql> select * from test11;
+---+-----+
| a | b   |
+---+-----+
| 1 | 100 |
+---+-----+
1 row in set (0.00 sec)

可以看到上面清除了test11数据库中的数据，又重新启动了数据库，然后插入了一条b字段值为100的记录，可以发现此时a字段的值变成了1，也就是此时的自增长列的值将从初始值开始。

查看数据表结构

在MySQL中，开发者可以使用describe和show create table语句来查看表的结构。

查看表基本结构语句describe/desc

describe/desc语句可以查看表的字段信息，其中包含：字段名、字段数据类型、是否为主键、是否有默认值等。对应的语法规则为：

1	describe 表名称;

或者简写为：

desc 表名称;

例子13：查看表基本结构语句describe/desc示例：

mysql> describe test10;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | PRI | NULL    |       |
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.13 sec)

mysql> desc test11;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

查看表详细结构语句show create table

show create table语句可以用来显示创建表时的create table语句，相对应的语法格式为：

1	show create table <表名\G>;

请注意使用show create table语句，不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。添加\G参数的目的就是为了使显示结果变得更加直观，便于查看。

例子14：查看表详细结构语句show create table示例：

mysql> show create table test11;
+--------+---------------------
| Table  | Create Table+--------+-----------------------
| test11 | CREATE TABLE `test11` (
  `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  `b` int(11) NOT NULL COMMENT '字段b',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------
1 row in set (0.10 sec)

修改表

开发者可以使用alter table语句来修改表的名称，语法规则如下：

1	alter table 旧表名 rename [to] 新表名;

其中的to为可选参数，使用与否均不影响结果。

例子15：修改test11数据表名称为test111的示例：

1 2	mysql> alter table test11 rename to test111; Query OK, 0 rows affected (0.26 sec)

请注意，修改表名并不修改表的结构，因此修改后的表和之前表的结构是完全相同的。

表设置备注

开发者可以使用如下命令来为表设置备注：

1	alter table 表名 comment '备注信息';

注意，给表设置的备注，只能用show create table 表名;这一命令进行查看：

mysql> desc test11;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table test11;
+--------+----------------------------------
| Table  | Create Table
+--------+-------------------------------
| test11 | CREATE TABLE `test11` (
  `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  `b` int(11) NOT NULL COMMENT '字段b',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='测试第11号表' |
+--------+-------------------------------
1 row in set (0.00 sec)

删除表

删除表就是将数据库中已经存在的表从数据库中删除，请注意在删除表的同时，表的定义和表中所有的数据均会被删除，因此在进行表的删除操作前，最好对表中的数据先进行备份。

删除没有被关联的表

开发者可以使用drop table命令来一次删除一个或多个没有被其他表关联的数据表。相应的语法格式为：

1	drop table [if exists] 表1，表2，表3,...表n;

删除被其他表关联的主表

数据表之间存在外键关联的情况下，如果直接删除主表，结果会显示失败，因为直接删除将破坏表的参照完整性。如果必须要删除，可以先删除与它关联的从表，再删除主表，只有这样才能同时删除两个表中的数据。

但是有的情况下可能还要保留从表，这个时候如果想单独删除主表，只需要先将关联的外键约束取消，然后就可以删除主表了。

删除表的外键约束

对于数据库中定义的外键，如果不再需要可以将其进行删除。外键一旦删除，就会解除主键和从表之间的关联关系。MySQL中删除外键的语法格式为：

1	alter table 表名 drop foreign key 外键约束名;

“外键约束名”是指在定义表时constraint关键词后面的参数，并不是外键的名称，这一点需要注意。

复制表

只复制表结构

开发者可以使用如下命令只复制表结构：

1	create table 表名 like 被复制的表名;

由于这里只复制表的结构，因此不包含任何数据。

例子16：只复制表的结构，不复制数据示例演示：

mysql> create table test12 like test11;
Query OK, 0 rows affected (0.37 sec)

mysql> select * from test12;
Empty set (0.00 sec)

mysql> show create table test12;
+--------+------------------------------------------
| Table  | Create Table
+--------+----------------------------------------------
| test12 | CREATE TABLE `test12` (
  `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  `b` int(11) NOT NULL COMMENT '字段b',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='测试第11号表' |
+--------+------------------------------------------
1 row in set (0.00 sec)

mysql> show create table test11;
+--------+-------------------------------------------
| Table  | Create Table
+--------+--------------------------------------------------
| test11 | CREATE TABLE `test11` (
  `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  `b` int(11) NOT NULL COMMENT '字段b',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='测试第11号表' |
+--------+----------------------------------------------
1 row in set (0.00 sec)

可以看到test12和test11的表结构完全一致，不同的是test12自增列的初始值是1。

复制表结构和数据

开发者可以使用如下命令来同时复制表结构和数据：

1	create table 表名 [as] select 字段,... from 被复制的表 [where 条件];

例子17：同时复制表结构和数据这一过程，示例演示：

mysql> create table test13 as select * from test11;
Query OK, 1 row affected (0.43 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test13;
+---+-----+
| a | b   |
+---+-----+
| 1 | 100 |
+---+-----+
1 row in set (0.00 sec)

mysql> select * from test11;
+---+-----+
| a | b   |
+---+-----+
| 1 | 100 |
+---+-----+
1 row in set (0.00 sec)

从结果中可以看到test13表和test11表中的数据一致，但是查看两个表的结构信息，发现test13表缺失主键，且auto_increment自增约束失效：

mysql> desc test11;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc test13;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   |     | 0       |       |
| b     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改表的存储引擎

存储引擎是MySQL中的数据存储在文件或者内存中时采用的不同技术实现。开发者可以根据自己的需要来选择不同的引擎，也可以为不同的数据表选择不同的存储引擎。

MySQL5.7支持的存储引擎有：InnoDB，MyISAM，Memory，Merge，Archive，Federated，CSV，BLACKHOLE，PERFORMANCE_SCHEMA等。可以使用show engines语句来查看系统支持的存储引擎，下图列出了5.7.27版本的MySQL所支持的存储引擎：

修改表的存储引擎的语法为：

1	alter table 表名 engine=新的存储引擎名称;

例子：，下面的例子将演示，如何修改test11数据库的存储引擎。首先查看一下test14数据表的数据结构：

mysql> show create table test11;
+--------+------------------------------
| Table  | Create Table 
+--------+-------------------
| test11 | CREATE TABLE `test11` (
  `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  `b` int(11) NOT NULL COMMENT '字段b',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='测试第11号表' |
+--------+----------------------------
1 row in set (0.00 sec)

接着将test11数据库的存储引擎由InnoDB修改为MyISAM，如下所示：

mysql> alter table test11 engine=MyISAM;
Query OK, 1 row affected (0.52 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table test11;
+--------+-------------------------------
| Table  | Create Table   
+--------+-------------------------
| test11 | CREATE TABLE `test11` (
  `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  `b` int(11) NOT NULL COMMENT '字段b',
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='测试第11号表' |
+--------+-------------------------------
1 row in set (0.00 sec)

表中列的管理

增加列

增加列的语法格式如下：

1	alter table 表名 add column 列名类型 [列约束] [first\|after 已存在的列名];

first和after用于指定新增列在表中的位置，如果SQL语句中不存在这两个参数，则默认将“新添加的列”添加在已存在数据列的后面。

(a) 在已存在数据列的后面添加“新列”。

例子18：下面的例子将演示，如何在已存在数据列的后面添加“新列”：

mysql> drop table if exists test14;
Query OK, 0 rows affected (0.25 sec)

mysql> create table test14(
    -> a int not null auto_increment primary key comment '字段a',
    -> b int not null comment '字段b'
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql> alter table test14 add column c int not null default 0 comment '字段c';
Query OK, 0 rows affected (0.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test14;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | NO   |     | NULL    |                |
| c     | int(11) | NO   |     | 0       |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

(b) 在第一列的前面插入“新列”。

例子18：下面的例子将演示，如何在第一列的前面插入“新列”：

mysql> alter table test14 add column d int not null default 100 comment '字段d' first;
Query OK, 0 rows affected (0.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test14;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| d     | int(11) | NO   |     | 100     |                |
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | NO   |     | NULL    |                |
| c     | int(11) | NO   |     | 0       |                |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

(c) 在表的指定列之后插入“新列”。

例子19：下面的例子将演示，如何在a列后面插入“新列”：

mysql> alter table test14 add column e int not null comment '字段e' after a;
Query OK, 0 rows affected (0.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test14;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| d     | int(11) | NO   |     | 100     |                |
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| e     | int(11) | NO   |     | NULL    |                |
| b     | int(11) | NO   |     | NULL    |                |
| c     | int(11) | NO   |     | 0       |                |
+-------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

修改列

修改列名

修改列名的语法格式如下：

1	alter table 表名 change column 旧列名新列名新类型 [约束];

注意，如果不需要修改列的数据类型，只需将新的数据类型设置成与原来一样的即可，注意数据类型不能为空。

例子20：下面的例子将演示，如何将字段e的名称和类型进行修改。首先查看一下test14数据表的数据结构：

mysql> show create table test14;
+--------+------------------------------
| Table  | Create Table
+--------+------------------------------
| test14 | CREATE TABLE `test14` (
  `d` int(11) NOT NULL DEFAULT '100' COMMENT '字段d',
  `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  `e` int(11) NOT NULL COMMENT '字段e',
  `b` int(11) NOT NULL COMMENT '字段b',
  `c` int(11) NOT NULL DEFAULT '0' COMMENT '字段c',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

接着将e字段修改为e14字段，类型由int修改为varchar(10)，如下所示：

mysql> alter table test14 change column e e14 varchar(10) not null default '' comment '字段e14';
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test14;
+--------+--------------------------------
| Table  | Create Table
+--------+--------------------------------------
| test14 | CREATE TABLE `test14` (
  `d` int(11) NOT NULL DEFAULT '100' COMMENT '字段d',
  `a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
  `e14` varchar(10) NOT NULL DEFAULT '' COMMENT '字段e14',
  `b` int(11) NOT NULL COMMENT '字段b',
  `c` int(11) NOT NULL DEFAULT '0' COMMENT '字段c',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------
1 row in set (0.00 sec)

修改列的数据类型

修改列的数据类型的语法格式如下：

1	alter table 表名 modify 列名数据类型;

例子21：下面的例子将演示，如何将字段e14的类型由varchar(10)类型修改为int类型。首先查看一下test14数据表的数据结构：

mysql> desc test14;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| d     | int(11)     | NO   |     | 100     |                |
| a     | int(11)     | NO   | PRI | NULL    | auto_increment |
| e14   | varchar(10) | NO   |     |         |                |
| b     | int(11)     | NO   |     | NULL    |                |
| c     | int(11)     | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

接着将e14字段的类型由varchar(10)修改为int类型，如下所示：

mysql> alter table test14 modify e14 int;
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test14;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| d     | int(11) | NO   |     | 100     |                |
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| e14   | int(11) | YES  |     | NULL    |                |
| b     | int(11) | NO   |     | NULL    |                |
| c     | int(11) | NO   |     | 0       |                |
+-------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

以上两种方式的区别在于modify不能修改列名，change命令可以修改列名。

修改列的排列位置

修改列的排列位置的语法格式如下：

1	alter table 表名 modify 列1 数据类型 first\|after 列2;

“first”为可选参数，指将“列1”修改为表的第一列；”after 列2“表示将”列1“插入到”列2”后面。由于此命令在实际工作中用的不是很多，因此就不举例说明了，可以参考之前增加列的例子进行理解。

删除列

删除列的语法格式如下：

1	alter table 表名 drop column 列名;

例子22：下面的例子将演示，如何将字段e14从数据表test14中删除。首先查看一下test14数据表的数据结构：

mysql> desc test14;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| d     | int(11) | NO   |     | 100     |                |
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| e14   | int(11) | YES  |     | NULL    |                |
| b     | int(11) | NO   |     | NULL    |                |
| c     | int(11) | NO   |     | 0       |                |
+-------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

接着将字段e14从数据表test14中删除，如下所示：

mysql> alter table test14 drop column e14;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test14;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| d     | int(11) | NO   |     | 100     |                |
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | NO   |     | NULL    |                |
| c     | int(11) | NO   |     | 0       |                |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

几个问题

（1）是否可以随意执行删除表操作？
无论是删除表还是修改表，都需要慎重考虑。因为MySQL在执行删除或者修改操作时不会有任何的提示信息，且数据库的改变是无法撤销的，因此在删除或者修改表之前，笔者建议最好是先对表中的数据进行备份，这样当出现操作失误时，可以对数据进行恢复，以避免造成无法挽回的后果。

（2）每一个表中都需要一个主键？
并不是每一个表中都需要主键，如果多个表之间进行连接操作时，需要用到主键。因此并不需要为每一个表建立主键，且有些时候最好不使用主键。

（3）并不是每个表都可以任意选择存储引擎。
首先明确外键约束(foreign key)是不能跨引擎使用的。MySQL支持多种存储引擎，且每个数据表都可以使用不同的存储引擎，但是需要注意外键约束是用来保证数据的参照完整性，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。因此存储引擎的选择也不是完全随意，也是具有一定条件的。

（4）带auto_increment约束的字段值不一定是从1开始的。
在MySQL中，auto_increment的初始值是1，每新增一条记录，字段的值就自动加1。设置自增属性(auto_increment)的时候，可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增。添加唯一性的主键约束时，往往需要设置字段自动增加的属性。

可以发现表别名只在查询的时候使用，并不在返回结果中显示，而列别名定义之后，将返回给客户端显示，显示的结果字段为字段列的别名。