第5章-数据表的基本操作

学习指引

数据实际上存储在数据表中，可见数据表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。本章将详细介绍数据表的基本操作，主要包括创建数据表、查看数据表结构、修改数据表和删除数据表。

重点内容

• 掌握创建数据表的方法
• 掌握查看数据表结构的方法
• 掌握修改数据表的方法
• 掌握数据表与其他操作的方法
• 掌握删除数据表的方法

5.1创建数据表

在创建数据库之后，接下来就要在数据库中创建数据表。所谓创建数据表，指的是在已经创建的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。本节将介绍创建数据表的语法形式，以及如何添加主键约束、外键约束、非空约束等。

5.1.1创建数据表的语法形式

数据表属于数据库，在创建数据表之前应该使用语句“USE<数据库名>”指定操作在哪个数据库中进行。如果没有选择数据库，直接创建数据表，则系统会显示“No databaseselected”的错误。

创建数据表的语句为CREATETABLE，语法格式如下：

 在使用CREATE TABLE创建表时必须指定以下信息：

（1）要创建表的名称，不区分大小写，不能使用SQL语言中的关键字，例如DROP、ALTER、INSERT等。

（2）数据表中每一列（字段）的名称和数据类型，如果创建多列，要用逗号隔开。

5.1.2创建数据表实例

下面以创建数据表db_1为例进行讲解。

【例5-1】创建员工表db_1，结构如  表5-1所示。

首先创建数据库，然后选择数据库，SQL语句如下：

#创建数据库
CREATE DATABASE mytest;

#选择数据库
USE mytest;

 开始创建数据表db_1，SQL语句如下：

#创建数据表
CREATE TABLE db_1(
    id INT(11),
    name VARCHAR(25),
    sex BOOLEAN,
    salary FLOAT
);

这里已经创建了一个名称为db_1的数据表，使用“SHOW TABLES;”语句查看数据表是否创建成功。

5.1.3主键约束

主键又称主码，是表中一列或多列的组合。主键约束（Primary KeyConstraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且 可以加快数据库查询的速度。主键和记录之间的关系如同身份证号码和人之间的关系，它们之间是一一对应的。主键分为两种类型，即单字段主键和多字段主键。

1.单字段主键

主键由一个字段组成，设置主键的SQL语句格式分为两种情况。

【例5-2】定义数据表db_2，其主键为id。

#创建数据表
CREATE TABLE db_2(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    sex BOOLEAN,
    salary FLOAT
);

【例5-3】定义数据表db_3，其主键为id。

#创建数据表
CREATE TABLE db_3(
    id INT(11),
    name VARCHAR(25),
    sex BOOLEAN,
    salary FLOAT,
    PRIMARY KEY(id)
);

2.多字段主键

【例5-4】定义数据表db_4，假设表中没有主键id，为了唯一确定一个员工，可以把name、sex联合起来作为主键。

#创建数据表
CREATE TABLE db_4(
    name VARCHAR(25),
    sex BOOLEAN,
    salary FLOAT,
    PRIMARY KEY(name,sex)
);

语句执行后便创建了一个名称为db_4的数据表，name字段和sex字段组合在一起成为该数据表的多字段主键。

5.1.4外键约束

外键用来在两个表的数据之间建立连接，它可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

外键首先是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，在定义外键后不允许删除在另一个表中具有关联关系的行。外键还保证数据的一致性、完整性。例如部门表tb_dept的主键id，在员工表db_5中有一个键deptId与这个id关联。

• 主表（父表）​：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
• 从表（子表）​：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

“外键名”为定义的外键约束的名称，在一个表中不能有相同名称的外键；​“字段名”表示子表需要添加外键约束的字段列。

【例5-5】定义数据表db_5，并且在该表中创建外键约束。

创建一个部门表tb_dept1，表结构如下：

#创建数据表
CREATE TABLE tb_dept1
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) NOT NULL,
    location VARCHAR(50)
);

 定义数据表db_5，让它的deptId字段作为外键关联到tb_dept1表的主键id，SQL语句如下：

CREATE TABLE db_5
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptid INT(11),
    salary FLOAT,
    CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptid) REFERENCES tb_dept1(id)
);

5.1.5非空约束

非空约束（NOT NULL Constraint）指字段的值不能为空，对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

【例5-6】定义数据表db_6，指定员工的性别不能为空。

#创建数据表
CREATE TABLE db_6
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    sex BOOLEAN NOT NULL
);

执行后，在db_6表中创建了一个sex字段，其插入值不能为空（NOTNULL）​。

5.1.6唯一性约束

唯一性约束（Unique Constraint）要求某列唯一，允许为空，但只能出现一个空值。唯一性约束可以确保一列或者几列都不出现重复值。

【例5-7】定义数据表db_7，指定name字段唯一。

#创建数据表
CREATE TABLE db_7
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) UNIQUE,
    sex BOOLEAN NOT NULL,
    age INT(4)
);

5.1.7默认约束

默认约束（Default Constraint）指定某列的默认值。例如，用户表中的北京人比较多，就可以设置city字段的默认值为“北京”​。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值“北京”​。

【例5-8】定义数据表db_7，指定员工的城市默认值为“北京”​。

#创建数据表
CREATE TABLE db_7
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25) NOT NULL,
    city VARCHAR(20) DEFAULT '北京'
);

以上语句成功执行之后，db_7表中的city字段拥有了一个默认值'北京'，新插入的记录如果没有指定city的值，则默认设置为'北京'。

5.1.8自增属性

在MySQL数据库设计中会遇到需要系统自动生成字段的主键值的情况。例如用户表中需要id字段自增，需要使用AUTO_INCREMENT关键字来实现。

【例5-9】定义数据表db_8，指定员工的编号自动增加。

#创建数据表
CREATE TABLE db_8
(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(25) NOT NULL,
    city VARCHAR(20)
);

以上语句执行后会创建名称为db_8的数据表，表中的id字段值在添加记录的时候会自动增加，id字段值默认从1开始，每次添加一条新记录，该值自动加1。

5.2查看数据表结构

在数据表创建完成之后即可查看表结构的定义，以确认表的定义是否正确。本节将详细介绍查看数据表的方法。

5.2.1查看数据表基本结构

使用DESCRIBE/DESC语句可以查看表字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等。

【例5-10】分别使用DESCRIBE和DESC查看db_2表的结构。

DESCRIBE db_2;

•  Null：表示该列是否可以存储Null值。
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在该列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有，值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

5.2.2查看数据表详细结构

SHOW CREATE TABLE语句可以用来查看表的详细信息。

【例5-11】使用SHOW CREATETABLE查看db_1表的详细信息。

SHOW CREATE TABLE db_1\G

 5.3修改数据表

在数据库创建完成之后，还可以根据实际工作的需要重新修改数据表的结构，常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

5.3.1修改表名

MySQL是通过ALTER TABLE语句来实现表名的修改的。

【例5-12】将数据表db_1改名为tb_new。

#将db_1表名修改为tb_new
ALTER TABLE db_1 RENAME tb_new;

在语句执行之后检查db_1表是否改名成功。使用SHOW TABLES查看数据库中的表。

5.3.2修改字段数据类型

修改字段数据类型就是把字段的数据类型转换成另一种数据类型。其中，表名指要修改数据类型的字段所在表的名称，字段名指需要修改的字段，数据类型指修改后字段的新数据类型。

【例5-13】将数据表tb_new中name字段的数据类型由VARCHAR(25)修改成VARCHAR(28)。

#修改tb_new表中的name字段类型
ALTER TABLE tb_new MODIFY name VARCHAR(28);

以上语句执行之后，检查发现tb_new表中name字段的数据类型已经修改成VARCHAR(28)，修改成功。

5.3.3修改字段名

【例5-14】将数据表tb_new中name字段的名称改为newname。

#把tb_new表中的name字段名修改为newname
ALTER TABLE tb_new CHANGE name newname VARCHAR(28);

5.3.4添加字段

【例5-15】在数据表tb_new中添加一个字段city。

#向tb_new表中新增字段city
ALTER TABLE rb_new ADD city VARCHAR(20);

【例5-16】在数据表tb_new中添加一个INT类型的字段newid，并添加在第一列。

#向tb_new表中新增一个newid字段，数据类型为INT类型，使用FIRST将字段添加在第一列
ALTER TABLE tb_new ADD newid INT(11) FIRST;

还可以在表的指定列之后添加一个字段。

【例5-17】在数据表tb_new中的sex列后添加一个INT类型的字段ss。

#向tb_new表中新增一个名为ss的字段，数据类型为INT，使用AFTER将新增的字段添加到sex字段后面
ALTER TABLE tb_new ADD ss INT(11) AFTER sex;

5.3.5删除字段

【例5-18】删除数据表tb_new中的ss字段。

#将tb_new表中的ss字段删除
ALTER TABLE tb_new DROP ss;

5.4数据表的其他操作

除了上述修改数据表的操作以外，还有一些重要的操作，包括修改字段排序、更改表的存储引擎和删除表的外键约束。

5.4.1修改字段排序

【例5-19】将数据表tb_new中的id字段修改为表的第一个字段。

#将tb_new表中的id字段修改到该表的第一列
ALTER TABLE tb_new MODIFY id INT(11) FIRST;

【例5-20】将数据表tb_new中的newname字段移动到salary字段的后面。

#将数据表tb_new中的newname字段移动到salary字段的后面
ALTER TABLE tb_new MODIFY newname VARCHAR(28) AFTER salary;

5.4.2更改表的存储引擎

【例5-21】将数据表db_2的存储引擎修改为MyISAM。

#将数据表db_2的存储引擎修改为MyISAM
ALTER TABLE db_2 ENGINE=MyISAM;

5.4.3删除表的外键约束

【例5-22】删除数据表db_5中的外键约束fk_emp_dept1。

#删除数据表db_5中的外键约束fk_emp_dept1
ALTER TABLE db_5 DROP FOREIGN KEY fk_emp_dept1;

5.5删除数据表

对于不再需要的数据表，可以将其从数据库中删除。

5.5.1删除没有被关联的表

【例5-23】删除数据表db_2。

#删除数据表db_2
DROP TABLE db_2;

5.5.2删除被其他关联的主表

在数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是直接删除将破坏表的参照完整性。如果必须删除，可以先删除与它关联的子表，再删除父表，只是这样同时删除了两个表中的数据。在有些情况下可能要保留子表，这时如果要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表，下面讲解这种方法。

在数据库中创建两个关联表，首先创建tb_1表，SQL语句如下：

CREATE TABLE tb_1
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22)
);

接下来创建tb_2表，SQL语句如下：

CREATE TABLE tb_2
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    wwid INT(11),
    CONSTRAINT fk_tb_dt FOREIGN KEY(wwid) REFERENCES tb_1(id)
);

【例5-24】删除父表tb_1。

首先直接删除父表tb_1，输入删除语句如下：

DROP TABLE tb_1;

 可以看到，如前所述，当存在外键约束时主表不能被直接删除。

接下来解除关联子表tb_2的外键约束，SQL语句如下：

ALTER TABLE tb_2 DROP FOREIGN KEY fk_tb_dt;

该语句成功执行后将取消tb_1和tb_2表之间的关联关系，此时可以输入删除语句，将原来的父表tb_1删除，SQL语句如下：

DROP TABLE tb_1;