MySQL必知必会 03丨表：怎么创建和修改表？

创建和修改数据表，是数据存储过程中的重要一环。我们不仅需要把表创建出来，还需要正确地设置限定条件，这样才能确保数据的一致性和完整性。同时，表中的数据会随着业务需求的变化而变化，添加和修改相应的字段也是常见的操作。

这节课，我们就来学习下具体的方法。

在超市项目里，客户经常需要进货，这就需要在 MySQL 数据库里面创建一个表，来管理进货相关的数据。我们先看看这个表里有什么内容。

假设这个表叫做进货单头表（importhead），如下图所示：

这里的 1、2、3 表示门店的 3 种进货方式，分别是

配送中心配送
门店采买
供货商直供。

其中，“1（配送中心配送）”是标准进货方式。因为超市是连锁经营，为了确保商品质量和品类一致，超过 9 成的门店进货，是通过配送中心进行配送的。因此，我们希望这个字段的值能够默认是 1，这样一来，除非有特别的指定，否则，门店进货单的进货方式，就自动设置成“1”了。

现在，客户需要一个类似的表来存储进货数据，而且进货方式还有 3 个可能的取值范围，需要设置默认值，那么，应该怎么创建这个表呢？另外，创建好表以后，又该怎么进行修改呢？

如何创建数据表？

首先，我们要知道 MySQL 创建表的语法结构：

CREATE TABLE <表名>
{
字段名1 数据类型 [字段级别约束] [默认值]，
字段名2 数据类型 [字段级别约束] [默认值]，
......
[表级别约束]
};

在这里，我们通过定义表名、表中的字段、表的属性等，把一张表创建出来。

你可能注意到了，在 MySQL 创建表的语法结构里面，有一个词叫做“约束”。“约束”限定了表中数据应该满足的条件。MySQL 会根据这些限定条件，对表的操作进行监

我们先来看基本的数据表创建流程，创建代码如下：

CREATE TABLE demo.importhead
(
listnumber INT,
supplierid INT,
stocknumber INT,
importtype INT DEFAULT 1,
quantity DECIMAL(10,3),
importvalue DECIMAL(10,2),
recorder INT,
recordingdata DATETIME
);

在创建表的时候，字段名称要避开 MySQL 的系统关键字，原因是 MySQL 系统保留的关键字都有特定的含义，如果作为字段名称出现在 SQL 语句中，MySQL 会把这个字段名称理解为系统关键字，从而导致 SQL 语句无法正常运行。比如，刚刚我们把进货金额设置为“importvalue”，而不是“value”，就是因为，“value”是 MySQL 的系统关键字。

现在我们尝试往刚刚创建的表里插入一条记录，来验证一下对字段“importtype”定义的默认值约束是否起了作用。

INSERT INTO demo.importhead
(
  listnumber,
  supplierid,
  stocknumber,
  quantity,
  importvalue,
  recorder,
  recordingdate
)
VALUE
(
 3456,
 1,
 1,
 10,
 100,
 1,
 '2023-7-14'
);

插入完成后，我们来查询一下表的内容：

1	SELECT * FROM demo.importhead

你会发现，字段 importtype 的值已经是 1 了。这样，通过在创建表的时候设置默认值，我们就实现了将字段的默认值定义为 1 的目的。

到这里，表就被创建出来了。

都有哪些约束？

刚刚这种给字段设置默认值的做法，就是默认约束。设置了默认约束，插入数据的时候，如果不明确给字段赋值，那么系统会把设置的默认值自动赋值给字段。除了默认约束，还有主键约束、外键约束、非空约束、唯一性约束和自增约束。

1. 非空约束

非空约束表示字段值不能为空，如果创建表的时候，指明某个字段非空，那么添加数据的时候，这个字段必须有值，否则系统就会提示错误。

2. 唯一性约束

唯一性约束表示这个字段的值不能重复，否则系统会提示错误。跟主键约束相比，唯一性约束要更加弱一些。在一个表中，我们可以指定多个字段满足唯一性约束，而主键约束则只能有一个，这也是MySQL 系统决定的。另外，满足主键约束的字段，自动满足非空约束，但是满足唯一性约束的字段，则可以是空值。

比如有个商品信息表 goodsmaster，如下表所示：

barcode	goodsname	price
0001	book	89
0002	pen	12

barcode 代表条码，goodsname 代表名称。为了防止条码重复，我们可以定义字段“barcode”满足唯一性约束。这样一来，条码就不能重复，但是可以为空，而且只能有一条记录条码为空。

同样道理，为了防止名称重复，我们也可以定义字段“goodsname”满足唯一性约束。但是，由于无论名称和条码都可能重用，或者可能为空，都不适合做主键。因此，对这个表来说，可以添加一个满足唯一性要求的新字段来做主键。

3. 自增约束

自增约束可以让 MySQL 自动给字段赋值，且保证不会重复，非常有用，只是不容易用好。所以，我借助一个例子来给你具体讲一讲。假如我们有这样一个商品信息表：

barcode	goodsname	price
0001	书	89
0002	馒头	1.5
0002	花卷	1.8

从这个表中，我们可以看到，barcode、goodsname 和 price 都不能确保唯一性，所以没有任何一个字段可以做主键，因此，我们可以自己添加一个字段 itemnumber，并且每次添加一条数据的时候，要给值增加 1。怎么实现呢？我们就可以通过定义自增约束的方式，让系统自动帮我们赋值，从而满足唯一性，这样就可以做主键了。

itemnumber	barcode	goodsname	price
1	0001	书	89
2	0002	馒头	1.5
3	0002	花卷	1.8

这里，有 2 个问题需要注意。

第一，在数据表中，只有整数类型的字段（包括 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT），才可以定义自增约束。自增约束的字段，每增加一条数据，值自动增加1。

第二，你可以给自增约束的字段赋值（100），这个时候，MySQL 会重置自增约束字段的自增基数，下次添加数据的时候，自动以自增约束字段的最大值加 1 为新的字段值（101）。

如何修改表？

在超市项目中，当我们创建新表的时候，会出现这样的情况：我们前面创建的进货单表，是用来存储进货数据的。但是，还要创建一个进货单历史表（importheadhist），用来存储验收过的进货数据。这个表的结构跟进货单表类似，只是多了 2 个字段，分别是验收人（confirmer）和验收时间（confirmdate）。针对这种情况，我们很容易想到可以通过复制表结构，然后在这个基础上通过修改表结构，来创建新的表。具体怎么实现呢？

首先，我们要把原来的表结构复制一下，代码如下：

1	CREATE TABLE demo.importheadhist LIKE demo.importhead

运行这个语句之后，一个跟 demo.importhead 有相同表结构的空表demo.importheadhist，就被创建出来了。这个新创建出来的表，还不是我们需要的表，我们需要对这个表进行修改，通过增加 2 个字段：confirmer 和 confirmdate，来获得我们需要的“进货单历史表”。运行一下语句：

1
2

ALTER TABLE demo.importheadhist ADD confirmer INT; -- 添加一个字段confirer，类型INT
ALTER TABLE demo.importheadhist ADD confirmdate DATETIME; -- 添加一个字段confirmdate，类型DATETIME

这样，通过简单增加 2 个字段，我们就获得了进货单历史表。

修改字段

除了添加字段，我们可能还要修改字段，比如，我们要把字段名称“quantity”改成“importquantity”，并且把字段类型改为 DOUBLE，该怎么操作呢？我们可以通过修改表结构语句 ALTER TABLE，来修改字段：

1	ALTER TABLE demo.imortheadhist CHANGE quantity importquantity DOUBLE;

如果你不想改变字段名称，只想改变字段类型，例如，把字段“importquantity”类型改成 DECIMAL(10,3)，你可以这样写：

1	ALTER TABLE demo.imortheadhist MODIFY quantity DECIMAL(10,3);

我们还可以通过 SQL 语句向表中添加一个字段，我们甚至可以指定添加字段在表中的位置。

比如说，在字段 supplierid 之后，添加一个字段 suppliername，数据类型是 TEXT。我们可以这样写 SQL 语句：

1	ALTER TABLE demo.importheadhist ADD suppliername TEXT AFTER supplierid;

总结

这节课，学习了创建和修改数据表的具体方法。在讲创建表时，我讲到了一个重要的概念，就是约束，包括默认约束、非空约束、唯一性约束和自增约束等。

默认值约束：就是给字段设置一个默认值。
非空约束：就是声明字段不能为空值。
唯一性约束：就是声明字段不能重复。
自增约束：就是声明字段值能够自动加 1，且不会重复。

在修改表时，我们可以通过修改已经存在的表创建新表，也可以通过添加字段、修改字段的方式来修改数据表。

最后，我给你汇总了一些常用的创建表的 SQL 语句，你一定要牢记。

CREATE TABLE(字段名 字段类型 PRIMARY KEY);
CREATE TABLE(字段名 字段类型 NOT NULL);
CREATE TABLE(字段名 字段类型 UNIQUE);
CREATE TABLE(字段名 字段类型 DEFAULT 值);

-- 这里要注意自增类型的条件，字段类型必须是整数类型。
CREATE TABLE(字段名 字段类型 AUTO_INCREMENT);

-- 在一个已经存在的表基础上，创建一个新表
CREATE demo.importheadhist LIKE demo.importhead;

-- 修改表的相关语句
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 FIRST|AFTER 字段名;
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST|AFTER 字段名;

对于初学者来说，掌握了今天的内容，就足够对数据表进行操作了。不过，MySQL 支持的数据表操作不只这些，我来举几个简单的小例子，你可以了解一下，有个印象。

比如，你可以在表一级指定表的存储引擎：

1	ALTER TABLE 表名 ENGING=INNODB

还可以通过指定关键字 AUTO_EXTENDSIZE，来指定存储文件自增空间的大小，从而提高存储空间的利用率。

在 MySQL 8.0.23 之后的版本中，你甚至还可以通过 INVISIBLE 关键字，使字段不可见，但却可以使用。

Channing's Blog