第5章
数据表的基本操作
◎本章教学微视频：20个 41分钟
学习指引
数据实际上存储在数据表中，可见数据表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。本章将详细介绍数据表的基本操作，主要包括创建数据表、查看数据表结构、修改数据表和删除数据表。
重点导读
• 掌握创建数据表的方法。
• 掌握查看数据表结构的方法。
• 掌握修改数据表的方法。
• 掌握数据表其他操作的方法。
• 掌握删除数据表的方法。
5.1 创建数据表
在创建数据库之后，接下来就要在数据库中创建数据表。所谓创建数据表，指的是在已经创建的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。本节将介绍创建数据表的语法形式，以及如何添加主键约束、外键约束、非空约束等。
5.1.1 创建数据表的语法形式
数据表属于数据库，在创建数据表之前应该使用语句“USE<数据库名>”指定操作在哪个数据库中进行。如果没有选择数据库，直接创建数据表，则系统会显示“No database selected”的错误。
创建数据表的语句为CREATE TABLE，语法格式如下：
在使用CREATE TABLE创建表时必须指定以下信息：
（1）要创建表的名称，不区分大小写，不能使用SQL语言中的关键字，例如DROP、ALTER、INSERT等。
（2）数据表中每一列（字段）的名称和数据类型，如果创建多列，要用逗号隔开。
5.1.2 创建数据表实例
下面以创建数据表db_1为例进行讲解。
【例5-1】创建员工表db_1，结构如表5-1所示。
表5-1 员工表db_1
首先创建数据库，然后选择数据库，SQL语句如下：
开始创建数据表db_1，SQL语句如下：
语句执行结果如图5-1所示。
图5-1 创建数据表
这里已经创建了一个名称为db_1的数据表，使用“SHOW TABLES;”语句查看数据表是否创建成功，执行结果如图5-2所示。
图5-2 查看数据表创建结果
可以看到，数据表db_1创建成功，数据库mytest中已经有了数据表db_1。
5.1.3 主键约束
主键又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证号码和人之间的关系，它们之间是一一对应的。主键分为两种类型，即单字段主键和多字段主键。
1．单字段主键
主键由一个字段组成，设置主键的SQL语句格式分为两种情况。
（1）在定义列的同时指定主键，语法格式如下：
【例5-2】定义数据表db_2，其主键为id。
SQL语句如下：
（2）在定义完所有列之后指定主键，语法格式如下：
【例5-3】定义数据表db_3，其主键为id。
SQL语句如下：
上面两个例子执行后的结果是一样的，都会在id字段上设置主键约束。
2．多字段主键
主键由多个字段联合组成，语法格式如下：
【例5-4】定义数据表db_4，假设表中没有主键id，为了唯一确定一个员工，可以把name、sex联合起来作为主键。
SQL语句如下：
语句执行后便创建了一个名称为db_4的数据表，name字段和sex字段组合在一起成为该数据表的多字段主键。
5.1.4 外键约束
外键用来在两个表的数据之间建立连接，它可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
外键首先是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，在定义外键后不允许删除在另一个表中具有关联关系的行。外键还保证数据的一致性、完整性。例如部门表tb_dept的主键id，在员工表db_5中有一个键deptId与这个id关联。
• 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
• 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。
创建外键的语法格式如下：
“外键名”为定义的外键约束的名称，在一个表中不能有相同名称的外键；“字段名”表示子表需要添加外键约束的字段列。
【例5-5】定义数据表db_5，并且在该表中创建外键约束。
创建一个部门表tb_dept1，表结构如表5-2所示，SQL语句如下：
表5-2 tb_dept1表结构
定义数据表db_5，让它的deptId字段作为外键关联到tb_dept1表的主键id，SQL语句如下：
以上语句成功执行后在db_5表上添加了名称为fk_emp_dept1的外键约束，外键名称为deptId，其依赖于tb_dept1表的主键id。
5.1.5 非空约束
非空约束（NOT NULL Constraint）指字段的值不能为空，对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。
非空约束的语法格式如下：
【例5-6】定义数据表db_6，指定员工的性别不能为空。
SQL语句如下：
执行后，在db_6表中创建了一个sex字段，其插入值不能为空（NOT NULL）。
5.1.6 唯一性约束
唯一性约束（Unique Constraint）要求某列唯一，允许为空，但只能出现一个空值。唯一性约束可以确保一列或者几列都不出现重复值。
在定义完列之后指定唯一性约束，语法格式如下：
【例5-7】定义数据表db_7，指定name字段唯一。
SQL语句如下：
5.1.7 默认约束
默认约束（Default Constraint）指定某列的默认值。例如，用户表中的北京人比较多，就可以设置city字段的默认值为“北京”。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值“北京”。
默认约束的语法格式如下：
【例5-8】定义数据表db_7，指定员工的城市默认值为“北京”。
SQL语句如下：
以上语句成功执行之后，db_7表中的city字段拥有了一个默认值'北京'，新插入的记录如果没有指定city的值，则默认设置为'北京'。
5.1.8 自增属性
在MySQL数据库设计中会遇到需要系统自动生成字段的主键值的情况。例如用户表中需要id字段自增，需要使用AUTO_INCREMENT关键字来实现。
属性值自动增加的语法格式如下：
【例5-9】定义数据表db_8，指定员工的编号自动增加。
SQL语句如下：
以上语句执行后会创建名称为db_8的数据表，表中的id字段值在添加记录的时候会自动增加，id字段值默认从1开始，每次添加一条新记录，该值自动加1。
5.2 查看数据表结构
在数据表创建完成之后即可查看表结构的定义，以确认表的定义是否正确。本节将详细介绍查看数据表的方法。
5.2.1 查看数据表基本结构
使用DESCRIBE/DESC语句可以查看表字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，其语法格式如下：
或者简写为：
【例5-10】分别使用DESCRIBE和DESC查看db_2表的结构。
查看db_2表的结构，SQL语句如下：
查看结果如图5-3所示。
图5-3 查看db_2表的结构
图5-3中各个字段的含义分别如下。
• Null：表示该列是否可以存储Null值。
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在该列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有，值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。
5.2.2 查看数据表详细结构
SHOW CREATE TABLE语句可以用来查看表的详细信息，语法格式如下：
【例5-11】使用SHOW CREATE TABLE查看db_1表的详细信息。
SQL语句如下：
执行结果如图5-4所示。
图5-4 查看db_1表的详细信息
5.3 修改数据表
在数据库创建完成之后，还可以根据实际工作的需要重新修改数据表的结构，常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对修改表的操作进行讲解。
5.3.1 修改表名
MySQL是通过ALTER TABLE语句来实现表名的修改的，具体语法格式如下：
其中，TO为可选参数，使用与否不影响结果。
【例5-12】将数据表db_1改名为tb_new。
在执行修改表名操作之前使用SHOW TABLES查看数据库中的所有表。
查询结果如图5-5所示。
图5-5 查看数据库中的所有表
使用ALTER TABLE将db_1表改名为tb_new，SQL语句如下：
在语句执行之后检查db_1表是否改名成功。使用SHOW TABLES查看数据库中的表，结果如图5-6所示。
图5-6 修改数据表的名称
经过比较可以看到数据表列表中已经显示表名为tb_new。
5.3.2 修改字段数据类型
修改字段数据类型就是把字段的数据类型转换成另一种数据类型。在MySQL中修改字段数据类型的语法格式如下：
其中，表名指要修改数据类型的字段所在表的名称，字段名指需要修改的字段，数据类型指修改后字段的新数据类型。
【例5-13】将数据表tb_new中name字段的数据类型由VARCHAR(25)修改成VARCHAR(28)。
在执行修改表名的操作之前使用DESC查看tb_new表的结构，结果如图5-7所示。
图5-7 查看tb_new表的结构
可以看到现在name字段的数据类型为VARCHAR(25)，修改其数据类型，输入以下SQL语句并执行：
再次使用DESC查看表，结果如图5-8所示。
图5-8 修改数据类型为VARCHAR(28)
以上语句执行之后，检查发现tb_new表中name字段的数据类型已经修改成VARCHAR(28)，修改成功。
5.3.3 修改字段名
在MySQL中修改表字段名的语法格式如下：
其中，“旧字段名”指修改前的字段名；“新字段名”指修改后的字段名；“新数据类型”指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来的一样，但数据类型不能为空。
【例5-14】将数据表tb_new中name字段的名称改为newname。
SQL语句如下：
使用DESC查看tb_new表，发现字段名称已经修改成功，结果如图5-9所示。
图5-9 修改数据表中字段的名称
从结果可以看出，name字段的名称已经修改为newname。
提示：由于不同类型的数据在计算机中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录，因此当数据库中已经有数据时不要轻易地修改数据类型。
5.3.4 添加字段
添加字段的语法格式如下：
其中，新字段名为需要添加的字段名称；FIRST为可选参数，其作用是将新添加的字段设置为表的第一个字段；AFTER为可选参数，其作用是将新添加的字段添加到已存在字段名指定的字段后面。
【例5-15】在数据表tb_new中添加一个字段city。
SQL语句如下：
使用DESC查看tb_new表，发现在表的最后添加了一个名为city的字段，结果如图5-10所示。
图5-10 添加字段
从结果可以看出添加了一个字段city，在默认情况下，该字段放在最后一列。
读者可以在数据表的第一列添加一个字段。
【例5-16】在数据表tb_new中添加一个INT类型的字段newid。
SQL语句如下：
使用DESC查看tb_new表，发现在表的第一列添加了一个名为newid的INT(11)类型的字段，结果如图5-11所示。
图5-11 添加INT类型的字段newid
另外，读者还可以在表的指定列之后添加一个字段。
【例5-17】在数据表tb_new中的sex列后添加一个INT类型的字段ss。
SQL语句如下：
使用DESC查看tb_new表，结果如图5-12所示。
图5-12 添加指定列字段
从结果可以看出，tb_new表中增加了一个名称为ss的字段，其位置在指定的sex字段的后面，添加字段成功。
5.3.5 删除字段
删除字段是将数据表中的某个字段从表中移除，其语法格式如下：
其中，“字段名”指需要从表中删除的字段的名称。
【例5-18】删除数据表tb_new中的ss字段。
SQL语句如下：
使用DESC查看tb_new表，结果如图5-13所示。
图5-13 删除表字段
从结果可以看出，tb_new表中已经不存在名称为ss的字段，删除字段成功。
5.4 数据表的其他操作
除了上述修改数据表的操作以外，还有一些重要的操作，包括修改字段排序、更改表的存储引擎和删除表的外键约束。
5.4.1 修改字段排序
对于已经创建的数据表，读者也可以根据实际工作的需要修改字段的排列顺序，通常使用ALTER TABLE改变表中字段的排列顺序，其语法格式如下：
其中，“字段1”指要修改位置的字段；“数据类型”指“字段1”的数据类型；“FIRST”为可选参数，指将“字段1”修改为表的第一个字段；“AFTER字段2”指将“字段1”插入到“字段2”的后面。
【例5-19】将数据表tb_new中的id字段修改为表的第一个字段。
SQL语句如下：
使用DESC查看tb_new表，发现id字段已经被移至表的第一列，结果如图5-14所示。
图5-14 修改字段的排列
读者可以修改字段到列表的指定列之后。
【例5-20】将数据表tb_new中的newname字段移动到salary字段的后面。
SQL语句如下：
使用DESC查看tb_new表，结果如图5-15所示。
图5-15 修改字段到指定位置
可以看到，tb_new表中的newname字段已经被移至salary字段之后。
5.4.2 更改表的存储引擎
更改表的存储引擎的语法格式如下：
【例5-21】将数据表db_2的存储引擎修改为MyISAM。
在修改存储引擎之前首先使用SHOW CREATE TABLE查看db_2表当前的存储引擎，结果如图5-16所示。
图5-16 查看表的存储引擎
从结果可以看出，db_2表当前的存储引擎为ENGINE=InnoDB，接下来修改存储引擎类型，SQL语句如下：
使用SHOW CREATE TABLE再次查看db_2表的存储引擎，发现db_2表的存储引擎已变为MyISAM，结果如图5-17所示。
图5-17 完成存储引擎的修改
5.4.3 删除表的外键约束
对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系。MySQL中删除外键的语法格式如下：
其中，“外键约束名”指在定义表时CONSTRAINT关键字后面的参数。
【例5-22】删除数据表db_5中的外键约束fk_emp_dept1。
首先查看数据表db_5的结构，SQL语句如下：
执行结果如图5-18所示。
下面开始删除数据表db_5的外键fk_emp_dept1，SQL语句如下：
执行完毕之后将删除db_5表的外键约束，使用SHOW CREATE TABLE再次查看db_5表的结构，结果如图5-19所示。
图5-18 查看数据表结构
图5-19 删除表的外键约束
可以看到，db_5表中已经不存在FOREIGN KEY，原有的名称为fk_emp_dept1的外键约束删除成功。
5.5 删除数据表
对于不再需要的数据表，可以将其从数据库中删除。本节将详细讲解数据库中数据表的删除方法。
5.5.1 删除没有被关联的表
在MySQL中使用DROP TABLE可以一次删除一个或多个没有被其他表关联的数据表，语法格式如下：
其中，“表n”指要删除的表的名称。用户可以同时删除多个表，只需将要删除的表名依次写在后面，相互之间用逗号隔开即可。
【例5-23】删除数据表db_2。
SQL语句如下：
在语句执行完毕之后，使用SHOW TABLES查看当前数据库中所有的数据表，查看结果如图5-20所示。
图5-20 删除数据表
从结果可以看出，数据列表中已经不存在名称为db_2的数据表，删除操作成功。
5.5.2 删除被其他表关联的主表
在数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是直接删除将破坏表的参照完整性。如果必须删除，可以先删除与它关联的子表，再删除父表，只是这样同时删除了两个表中的数据。在有些情况下可能要保留子表，这时如果要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表，下面讲解这种方法。
在数据库中创建两个关联表，首先创建tb_1表，SQL语句如下：
接下来创建tb_2表，SQL语句如下：
使用SHOW CREATE TABLE命令查看tb_2表的外键约束，SQL语句如下：
执行结果如图5-21所示。
图5-21 删除关联的主表
从结果可以看到，在数据表tb_2上创建了一个名称为fk_tb_dt的外键约束。
【例5-24】删除父表tb_1。
首先直接删除父表tb_1，输入删除语句如下：
执行结果如图5-22所示。
图5-22 删除父表
可以看到，如前所述，当存在外键约束时主表不能被直接删除。
接下来解除关联子表tb_2的外键约束，SQL语句如下：
该语句成功执行后将取消tb_1和tb_2表之间的关联关系，此时可以输入删除语句，将原来的父表tb_1删除，SQL语句如下：
最后通过SHOW TABLES查看数据表列表，结果如图5-23所示。
图5-23 解除关联子表
可以看到，数据表列表中已经不存在名称为tb_1的表。
5.6 就业面试技巧与解析
面试官：带AUTO_INCREMENT约束的字段值都是从1开始的吗？
应聘者：在默认情况下，MySQL中AUTO_INCREMENT的初始值是1，每新增一条记录，字段值就自动加1。在设置自增属性（AUTO_INCREMENT）的时候还可以指定第一条插入记录的自增字段的值，这样新插入记录的自增字段值从初始开始递增。