MySQL 必知必会(5)表:怎么创建和修改表?

2022年11月6日 40点热度

怎么创建和修改表

今天,我们来聊一聊怎么创建和修改数据表。

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

在我们的超市项目里,客户经常需要进货,这就需要在 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,
--我们在字段importype定义为INT类型的后面,按照MySQL创建表的语法,加了默认值1。
importtype INT DEFAULT 1,
quantity DECIMAL(10,3),
importvalue DECIMAL(10,2),
recorder INT,
recordingdate DATETIME
);

运行这个 SQL 语句,表 demo.importhead 就按照我们的要求被创建出来了。

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

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

INSERT INTO demo.importhead(listnumber, supplierid, stocknumber, quantity, importvalue,recorder,recordingdate) VALUES(3456, 1, 1, 10, 100, 1, '2020-12-10');

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

SELECT * FROM demo.importhead;

运行结果如下:

mysql> select * from demo.importhead;
+------------+------------+-------------+------------+----------+-------------
| listnumber | supplierid | stocknumber | importtype | quantity | importvalue
+------------+------------+-------------+------------+----------+-------------
| 1234 | 1 | 1 | 1 | 10.000 | 100.00
| 2345 | 1 | 1 | 2 | 20.000 | 2000.00
| 3456 | 1 | 1 | 1 | 20.000 | 2000.00
+------------+------------+-------------+------------+----------+-------------
3 rows in set (0.00 sec)

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

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

都有哪些约束?

刚刚这种给字段设置默认值的做法,就是默认约束。设置了默认约束,插入数据的时候,如果不明确给字段赋值,那么系统会把设置的默认值自动赋值给字段。

除了默认约束,还有主键约束、外键约束、非空约束、唯一性约束自增约束

我们在上节课里学的主键,其实就是主键约束,我就不多说了。外键约束涉及表与表之间的关联,以及确保表的数据一致性的问题,内容比较多,后面我在讲“关联表”的时候,再给你具体解释。

现在,我来重点给你介绍一下非空约束、唯一性约束和自增约束。

1. 非空约束

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

2. 唯一性约束

唯一性约束表示这个字段的值不能重复,否则系统会提示错误。跟主键约束相比,唯一性约束要更加弱一些。

在一个表中,我们可以指定多个字段满足唯一性约束,而主键约束则只能有一个,这也是 MySQL 系统决定的。另外,满足主键约束的字段,自动满足非空约束,但是满足唯一性约束的字段,则可以是空值

为了方便你理解,我来举个例子。比如,我们有个商品信息表 goodsmaster,如下所示:

商品信息表

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

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

3. 自增约束

自增约束可以让 MySQL 自动给字段赋值,且保证不会重复,非常有用,只是不容易用好。所以,我借助一个例子来给你具体讲一讲。

假如我们有这样一个商品信息表:

商品信息表-1

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

商品信息表-2

这里,有 2 个问题需要你注意一下。

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

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

举个例子,我们通过 Workbench 把数据表 demo.goodsmaster 中的字段 itemnumber,定义为满足自增约束,如下图所示:

Workbench 数据表 demo.goodsmaster

然后,我们插入一条测试记录:

INSERT INTO demo.goodsmaster(itemnumber,barcode,goodsname,specification,unit,price) VALUES(100,'0003','测试1','','个',10);

运行这条语句之后,查看表的内容,我们得到:

mysql> select * from demo.goodsmaster;
+------------+---------+-----------+---------------+------+----+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
|          1 | 	  0001 |        书 |          16开 |   本 | 89.00 |
|          2 |    0002 |      地图 |          NULL |   张 | 9.90  |
| 		   3 |    0003 |        笔 |          10支 |   包 | 3.00  |
| 		 100 |    0003 |     测试1 |               |   个 | 10.00 |
+------------+---------+-----------+---------------+------+-------+
4 rows in set (0.02 sec)

这个时候,字段“itemnumber”的值不连续,最大值是我们刚刚插入的 100。

接着,我们再插入一条数据:

INSERT INTO demo.goodsmaster(barcode,goodsname,specification,unit,supplierid,price) VALUES('0004','测试2','','个',1,20);

运行这条语句之后,我们再查看表的内容:

mysql> select * from demo.goodsmaster;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 		   1 | 	  0001 | 		书 | 		  16开 |   本 | 89.00 |
| 		   2 | 	  0002 | 	  地图 | 		  NULL |   张 |  9.90 |
| 		   3 | 	  0003 | 		笔 | 		  10支 |   包 |  3.00 |
| 		 100 | 	  0003 | 	 测试1 | 			   |   个 | 10.00 |
| 		 101 | 	  0004 | 	 测试2 | 			   |   个 | 20.00 |
+------------+---------+-----------+---------------+------+-------+
5 rows in set (0.00 sec)

可以看到,系统自动给自增字段“itemnumber”,在最大值的基础之上加了 1,赋值为 101。

好了,到这里,我们就学会了创建表和定义约束的方法。约束要根据业务需要定义在相应的字段上,这样才能保证数据是准确的,你一定要注意它的使用方法。

如何修改表?

创建完表以后,我们经常还需要修改表,下面我们就来学习下修改表的方法。

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

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

CREATE TABLE demo.importheadhist LIKE demo.importhead;

运行这个语句之后,一个跟 demo.importhead 有相同表结构的空表 demo.importheadhist,就被创建出来了。

这个新创建出来的表,还不是我们需要的表,我们需要对这个表进行修改,通过添加字段和修改字段,来获得我们需要的“进货单历史表”。

添加字段

你可能会想到,我们可以通过 Workbench,用可视化操作来修改表的结构,如下图所示:

可视化操作修改表

这样当然没问题,但是我想给你讲一个更方便灵活的方式:用 SQL 语句来修改表的结构

现在我要给这个新的表增加 2 个字段:confirmer 和 confirmdate,就可以用下面的代码:

mysql> ALTER TABLE demo.importheadhist
    -> ADD confirmer INT; -- 添加一个字段confirmer,类型INT
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE demo.importheadhist
    -> ADD confirmdate DATETIME; -- 添加一个字段confirmdate,类型是DATETIME
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

运行这个 SQL 语句,查看表的结构,你会发现,在字段的最后,多了两个字段:

  • “confirmer”,数据类型是 INT;
  • “confirmdate",类型是 DATETIME。

我们来查看一下表结构:

mysql> DESCRIBE demo.importheadhist;
+----------------+---------------+------+-----+---------+-------+
| Field 	  	| Type 			| Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| listnumber  	| int 			| NO   | PRI | NULL    | 	   |
| supplierid  	| int 			| NO   | 	 | NULL    | 	   |
| stocknumber 	| int 			| NO   | 	 | NULL    | 	   |
| importtype  	| int 			| YES  | 	 | 1       | 	   |
| quantity    	| decimal(10,3) | YES  | 	 | NULL    | 	   |
| importvalue 	| decimal(10,2) | YES  | 	 | NULL    | 	   |
| recorder    	| int 			| YES  | 	 | NULL    | 	   |
| recordingdate | datetime 		| YES  | 	 | NULL    | 	   |
| confirmer     | int 			| YES  | 	 | NULL    | 	   |
| confirmdate   | datetime 		| YES  | 	 | NULL    | 	   |
+----------------+---------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

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

修改字段

除了添加字段,我们可能还要修改字段,比如,我们要把字段名称“quantity”改成“importquantity”,并且把字段类型改为 DOUBLE,该怎么操作呢?

我们可以通过修改表结构语句 ALTER TABLE,来修改字段

mysql> ALTER TABLE demo.importheadhist
    -> CHANGE quantity importquantity DOUBLE;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

运行这个 SQL 语句,查看表的结构,我们会得到下面的结果:

mysql> DESCRIBE demo.importheadhist;
+----------------+---------------+------+-----+---------+-------+
| Field 		 | Type 		 | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| listnumber 	 | int 			 | NO 	| PRI | NULL 	| 		|
| supplierid 	 | int 			 | NO 	| 	  | NULL 	| 		|
| stocknumber 	 | int 			 | NO 	| 	  | NULL 	|  		|
| importtype 	 | int 			 | YES  |     | 1 		|  		|
| importquantity | double 		 | YES  |     | NULL 	|  		|
| importvalue 	 | decimal(10,2) | YES  |     | NULL 	|  		|
| recorder 		 | int 			 | YES  |     | NULL 	|  		|
| recordingdate  | datetime 	 | YES  | 	  | NULL 	|  		|
| confirmer 	 | int 			 | YES  | 	  | NULL 	|  		|
| confirmdate 	 | datetime 	 | YES  | 	  | NULL 	|  		|
+----------------+---------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

可以看到,字段名称和字段类型全部都改过来了。

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

ALTER TABLE demo.importheadhist MODIFY importquantity DECIMAL(10,3);

运行 SQL 语句,查看表结构,你会发现,已经改过来了。

mysql> DESCRIBE demo.importheadhist;
+----------------+---------------+------+-----+---------+-------+
| Field 		 | Type 		 | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| listnumber 	 | int 			 | NO 	| PRI | NULL 	| 		|
| supplierid 	 | int 			 | NO 	| 	  | NULL 	| 		|
| stocknumber 	 | int 			 | NO 	| 	  | NULL 	|  		|
| importtype 	 | int 			 | YES  |     | 1 		|  		|
| importquantity | decimal(10,3) | YES  |     | NULL 	|  		|
| importvalue 	 | decimal(10,2) | YES  |     | NULL 	|  		|
| recorder 		 | int 			 | YES  |     | NULL 	|  		|
| recordingdate  | datetime 	 | YES  | 	  | NULL 	|  		|
| confirmer 	 | int 			 | YES  | 	  | NULL 	|  		|
| confirmdate 	 | datetime 	 | YES  | 	  | NULL 	|  		|
+----------------+---------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

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

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

ALTER TABLE demo.importheadhist ADD suppliername TEXT AFTER supplierid;

运行 SQL 语句,查看表结构:

mysql> DESCRIBE demo.importheadhist;
+----------------+---------------+------+-----+---------+-------+
| Field 		 | Type 		 | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| listnumber 	 | int 			 | NO 	| PRI | NULL 	| 		|
| supplierid 	 | int 			 | NO 	| 	  | NULL 	| 		|
| suppliername   | text          | YES  |     | NULL    | 		|
| stocknumber 	 | int 			 | NO 	| 	  | NULL 	|  		|
| importtype 	 | int 			 | YES  |     | 1 		|  		|
| importquantity | decimal(10,3) | YES  |     | NULL 	|  		|
| importvalue 	 | decimal(10,2) | YES  |     | NULL 	|  		|
| recorder 		 | int 			 | YES  |     | NULL 	|  		|
| recordingdate  | datetime 	 | YES  | 	  | NULL 	|  		|
| confirmer 	 | int 			 | YES  | 	  | NULL 	|  		|
| confirmdate 	 | datetime 	 | YES  | 	  | NULL 	|  		|
+----------------+---------------+------+-----+---------+-------+
11 rows in set (0.02 sec)

到这里,我们就完成了修改字段在表中位置的操作。

总结

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

  • 默认值约束:就是给字段设置一个默认值。
  • 非空约束:就是声明字段不能为空值。
  • 唯一性约束:就是声明字段不能重复。
  • 自增约束:就是声明字段值能够自动加 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 支持的数据表操作不只这些,我来举几个简单的小例子,你可以了解一下,有个印象。

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

ALTER TABLE 表名 ENGINE=INNODB;

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

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

思考题

请你写一个 SQL 语句,将表 demo.goodsmaster 中的字段“salesprice”改成不能重复,并且不能为空。

Baimi

世上只有两种编程语言:一种是总是被人骂的,一种是从来没人用的。