MySQL第二讲·表的创建与修改

2023-12-13 03:48:32

在这里插入图片描述

你好,我是安然无虞。

表:怎么创建和修改数据表?

创建和修改数据表,是数据存储过程中的重要一环。我们不仅需要把表创建出来,还需要正确的设置限定条件,这样才能确保数据的一致性和完整性。

同时,表中的数据会随着业务需求的变化而变化,添加和修改相应的字段也是常见的操作。

在一个简单的超市项目中,客户经常需要进货,这就需要在MySQL数据库里面创建一个表,来管理进货的相关数据。假设这个表叫做进货单头表(importhead),如下图所示:

img

这里的1、2、3表示门店的三种进货方式,分别是配送中心配送、门店采买和供货商直供。

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

所以,现在客户需要一个类似的表来存储进货数据,而且进货方式还有3种可能的取值范围,需要设置默认值,接着往下看:

1. 如何创建数据表?

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

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

在MySQL创建表的语法结构里,有一个叫做约束。约束限定了表中数据应该满足的条件。

MySQL会根据这些限定条件,对表的数据进行监控,防止破坏约束条件的操作执行,并提示错误,从而确保表中数据的唯一性、合法性和完整性。

接下来我们创建上面提到的进货单表:

创建代码如下:

create table demo.importhead
(
	listnumber int,
  	supplierid int, 
  	stocknumber int, 
  	
  	-- 我们在字段importtype定义为int类型的后面,按照MySQL创建表的语法,加了默认值1
  
  	importtype int, default 1,
  	quantity decimal(10,3),
  	importvalue decimal(10,2),
  	recoder int,
  	recodingdate datetime
);

注意,在创建表时,字段名称要避开MySQL的系统关键字,原因是MySQL系统保留的关键字都有特定的意义。

OK,接下来我们尝试往刚刚的表里面插入一条记录,来验证一下对字段importtype定义的默认值约束是否起了作用。

insert into demo.importhead
(
listnumber,
supplierid,
stocknumber,
  
-- 这里我们没有插入字段importtype的值
  
quantity,
importvalue,
recoder,
recodingdate
)
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 | recorder | recordingdate       |
+------------+------------+-------------+------------+----------+-------------+----------+---------------------+
|       1234 |          1 |           1 |          1 |   10.000 |      100.00 |        1 | 2020-12-10 00:00:00 |
|       2345 |          1 |           1 |          2 |   20.000 |     2000.00 |        1 | 2020-12-10 00:00:00 |
|       3456 |          1 |           1 |          1 |   20.000 |     2000.00 |        1 | 2020-12-10 00:00:00 |
+------------+------------+-------------+------------+----------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

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

2. 都有哪些约束?

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

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

这里,我们暂时重点介绍非空约束、唯一性约束和自增约束。

1、非空约束

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

2、唯一性约束

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

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

为了方便理解,我们以商品信息表goodsmaster为例子:

img

barcode代表条码,goodsname代表名称。为了防止条码重复,我们可以定义字段barcode满足唯一性约束。这样的话,条码就不能重复,但是可以为空。

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

3、自增约束

自增约束可以让MySQL自动给字段赋值,且保证不会重复,非常有用,但是不容易用好。

我们还是来借助商品信息表来讲解:

img

从这个表中,我们可以看到,这三个字段都不能满足唯一性,所以没有任何一个字段可以做主键,因此我们需要自己添加一个字段itemnumber,并且每次添加一条数据的时候,要给值增加1。

如何实现呢?我们可以通过定义自增约束的方式,让系统帮我们赋值,从而满足唯一性,这样就可以做主键了。

img

有两点需要注意:

  • 在数据表中,只有整数类型的字段才可以有自增约束。自增约束的字段,每增加一条数据,只会自动增加1;
  • 我们可以给自增约束的字段赋值,这个时候,MySQL会重置自增约束字段的自增基数,下次添加的时候,自动以自增约束字段的最大值加1为新的字段值。

OK,接下来我们测试一下:

insert into demo.goodsmaster
(
itemnumber,
barcode,
goodsname,
specification,
unit,
price
)
VALUES
(
-- 指定商品编号为100:
100,
'0003',
'测试1',
'',
'个',
10
);

执行该SQL看到的结果是:

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)

我们发现这个时候item number的值不连续,最大值是我们刚刚插入的100.

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

insert into demo.goodsmaster
(
-- 不指定自增字段itemnumber的值
barcode,
goodsname,
specification,
unit,
price
)
VALUES
(
'0004',
'测试2',
'',
'个',
20
);

执行这个SQL语句,结果是:

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.

3. 如何修改表?

创建完表后,我们经常需要修改表,下面详细说说:

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

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

create table demo.importheadhist
like demo.importhead;

执行这条SQL后,一个跟demo.importhead有相同结构的空表demo.importheadhist就被创建出来了。

我们还需要对这张新创建的表进行修改,来获取我们需要的“进货单历史表”。

添加字段

现在需要给这个新的表增加2个字段:confirmer和confirmdate,请看下面的代码:

mysql> alter table demo.importheadhist
		-> add confirmer int; -- 添加一个字段confirmer,类型是int 
		
mysql> alter table demo.importheadhist
		-> add confirmdate datetime; -- 添加一个字段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)

修改字段

除了添加字段外,我们可能还需要修改字段,比如,我们要把字段quantity改成importquantity,并且把字段类型改成double,具体操作如下:

alter table demo.importheadhist
change quantity importquantity double;

执行这条SQL,查看表结构:

mysql> desc 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> desc 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:

alter table demo.importheadhist
add suppliername text after supplierid;

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

mysql> desc 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)

文章来源:https://blog.csdn.net/weixin_57544072/article/details/134204926
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。