数据库原理与应用（MySQL版）最新章节_梁琦著

4.3 基本表

表是数据库中最重要的对象，它用于存储用户的数据。在学习了数据类型的知识后，我们就可以开始创建表了。关系数据库的表是简单二维表，包含行和列，创建表就是定义表所包含的每个列，包括列名、数据类型、约束等。列名是为列取的名字，一般为便于记忆，最好取有意义的名字，比如“Sno”，而不要取无意义的名字，比如a1；列的数据类型说明了列的可取值范畴；列的约束更进一步限制了列的取值范围，这些约束包括：列取值是否允许为空、主键约束、外键约束、列取值范围约束等。

本节介绍表（或称为基本表）的创建、删除以及对表结构的修改等。

4.3.1 基本表的定义

表属于数据库对象，在创建数据表之前，在MySQL中应使用语句“USE＜数据库名＞”指定操作是在哪个数据库中进行。在同一个数据库中，表名不能有重名。

1.创建基本表

定义基本表使用SQL语言数据定义功能中的CREATE TABLE语句实现，其一般格式为：

注意：

1）SQL语句在Windows操作系统中不区分大小写，在Linux操作系统中区分大小写。

2）标点符号必须使用英文半角字符。

参数说明如下：

1）＜表名＞是所要定义的基本表的名字。

2）＜列名＞是表中所包含的属性列的名字。

3）IF NOT EXISTS：如果数据库中已经存在某个表，再来创建一个同名的表，系统会报错。这种情况下就可以在创建表时加上这个选项，只有当前数据库中不存在该同名表时才执行CREATE TABLE操作。需要说明的是，标准SQL语言的建表语句没有该选项，这是MySQL自己的扩展。

4）在定义表的同时还可以定义与表有关的完整性约束。如果完整性约束只涉及表中的一个列，则这些约束可以在“列级完整性约束”处定义，也可以在“表级完整性约束”处定义；但对涉及表中多个属性列的约束，必须在“表级完整性约束”处定义。

上述语法中用到了一些特殊的符号，比如[ ]，这些符号是语法描述的常用符号，而不是SQL语句的部分。我们简单介绍一下这些符号的含义（在后边的语法介绍中也要用到这些符号），有些符号在上述语法中可能没有用到。

1）方括号（[ ]）中的内容表示是可选的（即可出现0次或1次），比如[列级完整性约束定义]代表可以有，也可以没有“列级完整性约束定义”。

2）花括号（{}）与省略号（…）一起，表示其中的内容可以出现0次或多次。

3）竖杠（｜）表示在多个选项中选择一个，比如term1|term2|term3，表示在三个选项中任选一项。竖杠也能用在方括号中，表示可以选择由竖杠分隔的子句中的一个，但整个子句又是可选的（也就是可以没有子句出现）。

在定义基本表时可以同时定义数据的完整性约束。完整性约束可以作为列定义的一部分，也可以作为表的一个独立项定义。作为列定义的一部分约束称为列级完整性约束，作为表的独立一项定义的完整性约束称为表级完整性约束。在列级完整性约束定义处可以定义以下约束：

1）PRIMARY KEY：主键约束。指定本列为主键。

2）FOREIGN KEY：外键约束。定义本列为引用其他表的外键。

3）NOT NULL：非空约束。限制列取值非空。

4）UNIQUE：唯一值约束。限制列取值不能重复。

5）DEFAULT：默认值约束。指定列的默认值。

6）AUTO_INCREMENT：设置自增属性，只有整数类型才能设置此属性。

7）CHECK：列取值范围约束。限制列的取值范围。

在上述约束中，NOT NULL和DEFAULT只能在“列级完整性约束”处定义，其他约束均可在“列级完整性约束”和“表级完整性约束”处定义。

下面我们先介绍各完整性约束的含义。

2.完整性约束

（1）主键（PRIMARY KEY）约束

定义主键约束的语法格式为：

[CONSTRAINT＜主键约束名＞] PRIMARY KEY [（＜列名＞[，… n]）]

各部分含义如下：

1）CONSTRAINT＜主键约束名＞：可选参数，表示可以为主键约束命名。如果用户省略此部分，则系统将自动为主键约束命名。

2）（＜列名＞[，… n]）：主键所包含的列名。如果在列级完整性约束处定义单列主键，则可省略“[（＜列名＞[，… n]）]”部分，表示当前列即主键列。

（2）外键（FOREIGN KEY）约束

外键大多数情况下都是单列的，也可以是多列的复合外键。外键可以定义在列级完整性约束处，也可以定义在表级完整性约束处。定义外键的语法格式为：

各部分含义如下：

1）CONSTRAINT＜外键约束名＞：可选参数，表示可以为外键约束命名。如果用户省略此部分，则系统将自动为外键约束命名。

2）FOREIGN KEY（＜列名＞）：指定外键的列名。如果是在列级完整性约束处定义外键，则可省略此部分，表示当前列即外键列。

3）REFERENCES＜外表名＞（＜外表列名＞）：指定外键参照的表名和列名。

4）CASCADE：父表（被参照的主键所在表）记录的删除（DELETE）或更改（UPDATE）操作，会自动删除或更改子表（外键所在表）中与之对应的记录。

5）SET NULL：父表记录的删除（DELETE）或更改（UPDATE）操作，会将子表中与其对应记录的外键值自动设置为NULL。

6）NO ACTION：父表记录的删除（DELETE）或更改（UPDATE）操作，如果子表存在与其对应的记录，则删除或更改操作将失败，即不能删除或更改父表中的记录。

7）RESTRICT：与“NO ACTION”选项功能相同，且为级联选项的默认值。

如果是在列级完整性约束处定义外键，则可以省略“FOREIGN KEY（＜列名＞）”部分。

（3）非空（NOT NULL）约束

非空约束用于限制一个列的取值不能有NULL。例如，学生的姓名不能为空值。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，则数据库管理系统会报错并且拒绝添加数据。

定义非空约束的语法格式为：

＜列名＞＜数据类型＞NOT NULL

（4）唯一值（UNIQUE）约束

唯一值约束用于限制一个列的取值不重复，或者是多个列的组合取值不重复。这个约束用在事实上具有唯一性的属性列上，比如每个人的身份证号、驾驶证号等均不能有重复值。

在一个已有主键的表中使用唯一值约束定义非主键列取值不重复是很有用的，比如学生的身份证号码，“身份证号”列不是主键，但它的取值也不能重复，这种情况就需要使用唯一值约束。

定义唯一值约束的语法格式为：

[CONSTRAINT＜约束名＞] UNIQUE [（＜列名＞[，… n]）]

如果在列级完整性约束处定义单列的唯一值约束，则可省略“[（＜列名＞[，… n]）]”部分。

（5）默认值（DEFAULT）约束

默认值约束用DEFAULT约束来实现，它用于提供列的默认值，即向表中插入数据时，如果没有为有DEFAULT约束的列提供值，则系统自动使用DEFAULT约束指定的默认值。

一个默认值约束只能为一个列提供默认值，且默认值约束必须是列级约束。

默认值约束的定义有两种形式，一种是在定义表时指定默认值约束，另一种是在修改表结构时添加默认值约束。在创建表时定义默认值约束的语法格式为：

＜列名＞＜数据类型＞DEFAULT＜默认值＞

在修改表结构时添加默认值约束的语法格式请参见本章4.3.3小节。

（6）自增（AUTO_INCREMENT）约束

在实际应用中，有时希望每次在表中插入新记录时，系统能自动为某字段生成有规律递增的非重复值，比如1、2、3、…。这可以通过为字段添加AUTO_INCREMENT关键字来实现。在MySQL中，默认情况下AUTO_INCREMENT的起始值为1，步长也为1。MySQL支持设置起始值和步长。设置某个表的自增约束的起始值可以使用ALTER TABLE实现。例如，假设要将T1表的自增约束的起始值设置为100，可使用以下语句实现：

ALTER TABLE T1 AUTO_INCREMENT=100；

设置步长可以使用SET语句实现。例如，假设要将步长设置为3，实现语句为：

SET AUTO_INCREMENT_INCREMENT=3；

在一个表中，只能为一个字段使用自增约束。自增约束的字段必须是整数类型的。当为字段设置了自增约束后，在每次插入数据时，数据库管理系统都会为该字段生成一个唯一值。

注意，在MySQL中，设置自增约束的字段必须是有主键约束或唯一值约束的列。定义自增约束列的语法格式如下：

＜列名＞＜数据类型＞AUTO_INCREMENT

（7）检查（CHECK）约束

检查约束用于限制列的取值在指定范围内，即约束列的取值符合应用语义，例如，人的性别只能是“男”或“女”，工资必须大于或等于2000（假设最低工资为2000）。需要注意的是，检查约束所限制的列必须在同一个表中。

定义检查约束的语法格式为：

[CONSTRAINT＜约束名＞] CHECK（逻辑表达式）

注意，如果检查约束是定义多列之间的取值约束，则只能在表级完整性约束处定义。

3.示例

下面通过几个例子说明建表语句的使用，假设这些表是建立在“db_borrows”数据库中的（db_borrows数据库需要提前建好）。

例4-1 用SQL语句创建三张表：students（学生）表、books（图书）表和borrow（借阅）表，其结构见表4-7～表4-9。

表4-7 students表的结构

表4-8 books表的结构

表4-9 borrow表的结构

这三张表的创建语句如下：

说明：“#”为单行注释符。

例4-2 定义自增约束列的例子。用SQL语句创建表4-10所示的TestTable1表。

表4-10 TestTable1表的结构

4.3.2 查看基本表结构

使用SQL语句创建好表之后，在MySQL中可以查看数据库中的全部表以及各表的结构等，以确认表的定义是否正确。

本小节所有的SQL语句均在MySQL 8.0 Workbench环境下执行。

1.显示数据库中全部表

使用SHOW TABLES语句可以显示指定数据库中的所有表的表名。语法格式为：

SHOW TABLES；

例4-3 显示db_borrows数据库中的所有表。

SHOW TABLES；

结果如图4-1所示。

图4-1 数据库中的所有表名

2.显示表的结构

使用DESCRIBE语句以表格形式显示表的定义，其语法格式为：

DESCRIBE＜表名＞；

使用SHOW语句以脚本形式显示表的定义，其语法格式为：

SHOW CREATE TABLE＜表名＞；

例4-4 用表格方式显示students表的结构。

DESCRIBE students；

结果如图4-2所示。

图4-2 以表格形式显示表的定义

例4-5 显示students表的定义语句。

SHOW CREATE TABLE students；

部分结果如图4-3所示。

图4-3 以脚本形式显示表的定义

4.3.3 基本表的维护

在定义基本表之后，如果需求有变化，可以对表的结构进行修改，这称为基本表的维护。修改表结构使用ALTER TABLE语句实现。ALTER TABLE语句可以修改表名及列名、修改列的数据类型、添加及删除列以及添加和删除约束等。

不同数据库产品的ALTER TABLE语句的格式略有不同，我们这里给出MySQL支持的ALTER TABLE语句的语法格式，对于其他的数据库管理系统，可以参见相关产品的语言参考手册。

1.修改基本表

（1）修改表名

修改表名并不会改变表的结构。修改表名的ALTER TABLE语句的语法格式为：

ALTER TABLE＜旧表名＞RENAME [TO]＜新表名＞；

例如，把TestTable1表的表名改为TestTab。

ALTER TABLE TestTable1 RENAME TestTab；

（2）修改列的数据类型和相关约束

在MySQL中修改列的数据类型和相关约束的语法为：

其中＜列名＞指需要修改的字段，＜数据类型＞指修改后字段的新数据类型。DEFAULT＜默认值＞、NOT NULL和UNIQUE是可选项，分别为该列设置默认值、非空或唯一值约束。重新设置列的数据类型时，会同时删掉该列的默认值约束和非空约束。使用ALTER TABLE语句设置唯一值约束时需要注意，要确保该列的数据没有重复值，否则设置唯一值约束将失败。删除唯一值约束需要额外的SQL语句，会在后文说明。

例4-6 只更改列的数据类型。将students表的sname的数据类型从VARCHAR（20）改为VARCHAR（50）。

ALTER TABLE students MODIFY sname VARCHAR（50）；

修改数据类型后可以查看修改的结果。执行下列语句：

DESCRIBE students；

结果如图4-4所示。

图4-4 修改sname列的数据类型后的students表结构

例4-7 添加非空约束，数据类型不变。为students表的sname列添加非空约束。

ALTER TABLE students MODIFY sname VARCHAR（50）NOT NULL；

例4-8 添加默认值约束，数据类型不变。为students表的gender列添加默认值：′男′。

ALTER TABLE students MODIFY sname VARCHAR（50）DEFAULT′男′；

例4-9 为sname列同时添加默认值约束和非空约束。

ALTER TABLE students MODIFY sname VARCHAR（50）DEFAULT′男′NOT NULL；或

ALTER TABLE students MODIFY sname VARCHAR（50）NOT NULL DEFAULT′男′；

说明：添加多个约束时，约束的前后顺序不重要。

（3）修改列名和相关约束

MySQL中修改表中列名的语句如下：

ALTER TABLE＜表名＞CHANGE＜旧列名＞＜新列名＞＜新数据类型＞

[ DEFAULT＜默认值＞|NOT NULL|UNIQUE ]；

其中，＜旧列名＞指修改前的列名；＜新列名＞指修改后的列名；＜新数据类型＞指修改后的数据类型，如果不需要修改字段的数据类型，可以将＜新数据类型＞设置成与原来一样即可，但数据类型不能为空。[DEFAULT＜默认值＞|NOT NULL|UNIQUE ]为可选项。

CHANGE也可以只修改数据类型，实现和MODIFY同样的效果，方法是将上述语句中的＜旧列名＞和＜新列名＞设置为相同的名称，只改变数据类型。由于不同类型的数据在计算机中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录。因此，当数据库中已经有数据时，不要轻易修改数据类型。

注意：

1）当使用CHANGE操作修改已有检查约束的列时，若只修改列名而不改变列的数据类型，则该修改操作将失败。

2）当使用CHANGE操作成功修改列后，该列的非空、默认值约束将会失效（若有的话）。

例4-10 将students表的college列名变为school，数据类型为VARCHAR（30）。

ALTER TABLE students CHANGE college school VARCHAR（30）；

修改完成后查看students表的结构，结果如图4-5所示。

如果创建表时没有为students表中的Email列定义唯一值约束，则在students表创建成功后，可以用如下SQL语句为Email列添加唯一值约束。

ALTER TABLE students CHANGE Email Email VARCHAR（30）UNIQUE；

图4-5 修改college列后的students表结构

（4）添加列

添加列的语句的语法如下：

其中，

1）FIRST：可选参数，其作用是将新添加的列设置为表中的第一个字段。

2）AFTER＜已经存在的列名＞：可选参数，其作用是将新添加的列添加到＜已经存在的列名＞的后面。

如果省略这两个参数，则默认将新添加的列作为表的最后列。

例4-11 在students表中增加一个电话列，列名：phone，类型：CHAR（11），非空。

ALTER TABLE students ADD phone CHAR（11）NOT NULL；

修改完成后查看students表的结构，结果如图4-6所示。

图4-6 添加phone列后的students表

例4-12 在students表中增加一个新列，列名：TestID，类型：CHAR（11）。此列将作为students表的第一个列。

ALTER TABLE students ADD TestID CHAR（11）FIRST；

修改完成后查看students表的结构，结果如图4-7所示。

图4-7 在第1列添加新列后的students表

（5）删除列

删除列的语句的语法如下：

ALTER TABLE＜表名＞DROP＜列名＞；

例4-13 删除students表中的TestID列。

ALTER TABLE students DROP TestID；

（6）改变表中列的排列顺序

表中列的排列顺序就是创建表时指定的顺序，但这个顺序是可以改变的，通过ALTER TABLE可以改变表中列的相对位置。该语句的语法如下：

ALTER TALBE＜表名＞MODIFY＜列1＞＜数据类型＞[FIRST|AFTER＜列2＞]；其中，

1）＜列1＞：要修改位置的字段名。

2）＜数据类型＞：指＜列1＞的数据类型。

3）FIRST：将＜列1＞修改为表的第一个字段。

4）AFTER＜列2＞：将＜列1＞放置到＜列2＞后面。

例4-14 将students表中的phone列放置到Email列的前面。

ALTER TABLE students MODIFY Email VARCHAR（30）AFTER phone；

修改完成后查看students表的结构，结果如图4-8所示。

图4-8 修改列的排列顺序

（7）修改表的主键约束

修改表的主键约束的语句的语法如下：

例4-15 修改borrow表的主键，删除原来的主键，并将（ISBN，SID）设置为新的主键。修改主键后，borrow表的主键从原来的（ISBN，SID，borrow_time）变为图4-9所示的（ISBN，SID）。

ALTER TABLE borrow DROP PRIMARY KEY，ADD PRIMARY KEY（ISBN，SID）；

图4-9 修改主键后的borrow表

（8）添加和删除表的外键约束

MySQL中添加外键约束的语句的语法为：

ALTER TABLE＜表名＞ADD [CONSTRAINT＜约束名＞]

FOREIGN KEY（＜列名＞）REFERENCES＜外表名＞（＜外表列名＞）

[ON DELETE{CASCADE|RESTRICT|SET NULL|NO ACTION}]

[ON UPDATE{CASCADE|RESTRICT|SET NULL|NO ACTION}]；

例4-16 为borrow表的SID列添加外键约束，约束名：fksid，参照students表的SID列。

ALTER TABLE borrow ADD CONSTRAINT fksid

FOREIGN KEY（SID）REFERENCES students（SID）；

在MySQL Workbench左边的“SCHEMAS”窗格中，展开“borrow”表中的“Foreign Keys”项，可看到外键约束fksid添加成功，如图4-10所示。

图4-10 添加外键约束

对于已定义的外键，如果不再需要，可以将其删除。外键一旦删除，主表和从表间的关联关系也就随之解除。MySQL中删除外键的语句的语法为：

ALTER TABLE＜表名＞DROP FOREIGN KEY＜外键约束名＞；

例4-17 删除borrow表的外键约束fksid。

ALTER TABLE borrow DROP FOREIGN KEY fksid；

（9）添加和删除唯一值约束

修改表结构时，为列添加唯一值约束的语法为：

ALTER TABLE＜表名＞ADD UNIQUE（＜列名＞）；

或

ALTER TABLE＜表名＞ADD UNIQUE INDEX＜约束名＞（＜列名＞）；

例4-18 为students表的Email列添加唯一值约束。

ALTER TABLE students ADD UNIQUE（Email）；

或

ALTER TABLE students ADD UNIQUE INDEX Email（Email）；

删除唯一值约束的语法为：

ALTER TABLE＜表名＞DROP INDEX＜约束名＞；

如果要删除students表中Email列上的唯一值约束，如果不知道或忘记了约束名，可以在MySQL Workbench左边的“SCHEMAS”窗格中，展开“db_borrows”数据库下的“students”表，并展开“students”表下的“Indexes”节点，可找到该约束的约束名。从图4-11左边可看到Email列的唯一值约束名为“Email”。

图4-11 Email列的唯一值约束名为Email

执行语句：

Describe students；

可显示表中Email列的Key类型，这里为“UNI”（如图4-11右边最下一行所示），也就是唯一值约束，在创建约束时默认情况下约束名就是列名，也就是图4-11左边窗格中“Indexes”中列出的“Email”。

例4-19 删除students表中Email列上的唯一值约束。

ALTER TABLE students DROP INDEX Email；

（10）添加和删除检查约束

MySQL中为列添加检查约束的语法为：

ALTER TABLE＜表名＞ADD CONSTRAINT＜约束名＞CHECK（逻辑表达式）；

删除检查约束的语法为：

ALTER TABLE＜表名＞DROP CONSTRAINT＜约束名＞；

或

ALTER TABLE＜表名＞DROP CHECK＜约束名＞；

如果不知道约束名，可以用SHOW CREATE TABLE＜表名＞语句查看检查约束名。

例4-20 为students表的gender列添加取值只能是“男”或“女”的检查约束。

例4-21 删除gender列上的chk_gender检查约束。

ALTER TABLE students DROP CONSTRAINT chk_gender；

或

ALTER TABLE students DROP CHECK chk_gender；

2.删除基本表

删除表就是将数据库中已经存在的表从数据库中删除。注意，在删除表的同时，表的定义和表中所有的数据均会被删除。因此，进行删除表的操作时一定要慎重。

可以使用DROP TABLE语句删除表，其语法格式为：

DROP TABLE [IF EXISTS]＜表名1＞{，＜表名2＞，…，＜表名n＞}；

可选参数IF EXISTS用于在删除前判断被删除的表是否存在，如果表不存在，SQL语句可以顺利执行，但是会发出警告。如果是同时删除多个表，则各表名间用逗号分隔。

例4-22 删除borrow表。

DROP TABLE borrow；

注意，删除表时必须先删除外键所在表，然后删除被参照的主键所在表。创建表时必须先建立被参照的主键所在表，然后建立外键所在表。