第 5 章 chapter 5... 数据的完整性 本章学习目标 熟练掌握实体完整性 熟练掌握索引 熟练掌握域完整性 熟练掌握引用完整性前面章节学习了数据库与数据表的基本操作, 在实际开发中, 数据表中的数据是非常多的, 保证数据的准确是至关重要的 MySQL 提供了数据的完整性约束, 主要包括实体完整性 域完整性和引用完整性, 本章将重点讲解数据的完整性 5.1 实体完整性 实体完整性是对关系中的记录进行约束, 即对行的约束 此处主要讲解主键约束 唯一约束和自动增长列 5.1.1 主键约束 主键 (primary key) 用于唯一地标识表中的某一条记录 在两个表的关系中, 主键用来在一个表中引用来自于另一个表中的特定记录 一个表的主键可以由多个关键字共同组成, 并且主键的列不能包含空值 主键的值能唯一标识表中的每一行, 这就好比所有人都有身份证, 每个人的身份证号是不同的, 能唯一标识每一个人 接下来通过一个案例演示未设置主键会出现的问题 首先创建一张订单表 orders, 表结构如表 5.1 所示 表 5.1 orders 表 字 段 字段类型 说 明 oid INT 订单号 total DOUBLE 订单金额总计 name VARCHAR(20) 收货人 phone VARCHAR(20) 收货人电话 addr VARCHAR(50) 收货人地址
在表 5.1 中列出了订单表的字段 字段类型和说明 创建订单表的 SQL 语句及执行结果如下 mysql> CREATE TABLE orders( -> oid INT, -> total DOUBLE, -> name VARCHAR(20), -> phone VARCHAR(20), -> addr VARCHAR(50) Query OK, 0 rows affected (0.18 sec) 然后向订单表中插入一条数据 mysql> INSERT INTO orders( -> oid,total,name,phone,addr -> ) VALUES( -> 1,100,'zs',1366,'xxx' Query OK, 1 row affected (0.07 sec) 以上执行结果证明插入数据完成 为了进一步验证, 使用 SELECT 语句查看 orders 表中的数据 mysql> SELECT * FROM orders; +------+-------+------+-------+------+ oid total name phone addr +------+-------+------+-------+------+ 1 100 zs 1366 xxx +------+-------+------+-------+------+ 1 row in set (0.02 sec) 从以上执行结果可以看出,orders 表中的数据成功插入 此时再次向表中插入数据, 新插入数据的 oid 仍然为 1 mysql> INSERT INTO orders( -> oid,total,name,phone,addr -> ) VALUES( -> 1,200,'ls',1369,'yyy' Query OK, 1 row affected (0.04 sec) 以上执行结果证明插入数据完成 为了进一步验证, 使用 SELECT 语句查看 orders 表中的数据
第 章数据的完整性 mysql> SELECT * FROM orders; +------+-------+------+-------+------+ oid total name phone addr +------+-------+------+-------+------+ 1 100 zs 1366 xxx 1 200 ls 1369 yyy +------+-------+------+-------+------+ 2 rows in set (0.00 sec) 从以上执行结果可以看出,orders 表中的数据成功插入, 该表中此时有两条订单数据, 且两个订单的 oid 都为 1 但订单号相同, 商品的付款 送货等流程可能会出现问题 为了避免这种问题, 在此处可以为表添加主键约束, 为已经存在的表设置主键的语法格式如下 ALTER TABLE 表名 ADD PRIMARY KEY( 列名 ); 在以上语法格式中, 表名表示需要修改的已存在的表,PRIMARY KEY 代表主键, 列名表示需要设置为主键的列 接下来为 orders 表添加主键约束, 设置 oid 列为主键 mysql> ALTER TABLE orders ADD PRIMARY KEY(oid); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 从以上执行结果可以看出, 添加主键失败 此处报告一个错误, 因为表中已经存在两条 oid 相同的数据, 所以不可以添加主键, 此时可以使用 DELETE 语句删除其中一条数据 mysql> DELETE FROM orders WHERE name='ls'; Query OK, 1 row affected (0.09 sec) 以上执行结果证明删除数据完成 为了进一步验证, 使用 SELECT 语句查看 orders 表中的数据 mysql> SELECT * FROM orders; +------+-------+------+-------+------+ oid total name phone addr +------+-------+------+-------+------+ 1 100 zs 1366 xxx +------+-------+------+-------+------+ 1 rows in set (0.00 sec) 从以上执行结果可以看出,orders 表中只有一条数据, 不存在 oid 重复的数据 然后继续设置主键 mysql> ALTER TABLE orders ADD PRIMARY KEY(oid); Query OK, 1 row affected (0.26 sec) Records: 1 Duplicates: 0 Warnings: 0
以上执行结果证明主键添加完成 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC orders; Field Type Null Key Default Extra oid int(11) NO PRI 0 total double YES NULL name varchar(20) YES NULL phone varchar(20) YES NULL addr varchar(50) YES NULL 5 rows in set (0.01 sec) 从以上执行结果可以看出,oid 字段的 Key 值为 PRI, 代表主键 然后将前面删除的第二条数据再次添加进去 mysql> INSERT INTO orders( -> oid,total,name,phone,addr -> ) VALUES(1,200,'ls',1369,'yyy' ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 从以上执行结果可以看出, 插入数据失败 这是因为主键对其进行了约束, 新插入的数据主键不能重复 此处将 oid 的值改为 2, 再次插入 mysql> INSERT INTO orders( -> oid,total,name,phone,addr -> ) VALUES( -> 2,200,'ls', 1369,'yyy' Query OK, 1 row affected (0.03 sec) 以上执行结果证明插入数据完成 为了进一步验证, 使用 SELECT 语句查看 orders 表中的数据 mysql> SELECT * FROM orders; +-----+-------+------+-------+------+ oid total name phone addr +-----+-------+------+-------+------+ 1 100 zs 1366 xxx 2 200 ls 1369 yyy +-----+-------+------+-------+------+ 2 rows in set (0.00 sec) 从以上执行结果可以看出, 当 oid 的值不重复时插入成功 另外, 主键的值不能为
第 章数据的完整性 NULL, 此处继续验证, 向表中插入一条数据, 将 oid 设置为 NULL mysql> INSERT INTO orders( -> oid,total,name,phone,addr -> ) VALUES( -> NULL,300,'w5',1591,'zzz' ERROR 1048 (23000): Column 'oid' cannot be null 以上执行结果证明定义为主键的字段值不能为 NULL, 否则会报错 以上是为已经存在的表添加主键约束, 实际上, 在创建表时同样可以添加主键约束, 具体语法格式如下 CREATE TABLE 表名 ( 字段名数据类型 PRIMARY KEY ); 在以上语法格式中, 字段名表示需要设置为主键的列名, 数据类型为该列的数据类型,PRIMARY KEY 代表主键 接下来通过具体案例演示在创建表时添加主键约束, 见例 5-1 例 5-1 创建订单表 orders2, 表结构与前面的 orders 表相同, 在创建表的同时为 oid 列添加主键约束 mysql> CREATE TABLE orders2( -> oid INT PRIMARY KEY, -> total DOUBLE, -> name VARCHAR(20), -> phone VARCHAR(20), -> addr VARCHAR(50) Query OK, 0 rows affected (0.08 sec) 以上执行结果证明 orders2 表创建完成并添加了主键 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC orders2; Field Type Null Key Default Extra oid int(11) NO PRI NULL total double YES NULL name varchar(20) YES NULL phone varchar(20) YES NULL addr varchar(50) YES NULL 5 rows in set (0.02 sec)
从以上执行结果可以看出,oid 字段的 Key 值为 PRI, 说明主键约束添加成功 在前面的案例中讲解了添加单字段的主键约束, 但随着业务的复杂, 可能会需要多 字段的主键约束, 例如手机接收信息, 这时通过手机号就不能够唯一确定一条记录, 可 能一个手机号在一天中接收了很多的信息 解决这个问题可以添加主键约束为手机号和 时间戳两个列, 根据两个列的数据能够唯一确定一条记录 添加多字段的主键约束的语 法格式如下 CREATE TABLE 表名 ( 字段名 1 数据类型, 字段名 2 数据类型, PRIMARY KEY( 字段名 1, 字段名 2, 字段名 n) ); 在以上语法格式中,PRIMARY KEY 中的参数表示构成主键的多个字段的名称 接下来通过具体案例演示添加多字段的主键约束, 见例 5-2 例 5-2 创建订单表 orders3, 表结构与前面的 orders 表相比多了一个 INT 类型的 pid 字段, 该字段代表商品 id, 在创建表的同时为 oid 和 pid 两列添加主键约束 mysql> CREATE TABLE orders3( -> oid INT, -> pid INT, -> total DOUBLE, -> name VARCHAR(20), -> phone VARCHAR(20), -> addr VARCHAR(50), -> PRIMARY KEY(oid,pid) Query OK, 0 rows affected (0.07 sec) 以上执行结果证明 orders3 表创建完成并添加了主键 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC orders3; Field Type Null Key Default Extra oid int(11) NO PRI 0 pid int(11) NO PRI 0 total double YES NULL name varchar(20) YES NULL phone varchar(20) YES NULL addr varchar(50) YES NULL
第 章数据的完整性 6 rows in set (0.01 sec) 从以上执行结果可以看出,oid 字段和 pid 字段的 Key 值都为 PRI, 说明多个字段的主键约束添加成功 5.1.2 唯一约束 唯一约束用于保证数据表中字段值的唯一性, 在 MySQL 中使用 UNIQUE 关键字添加唯一约束 在创建表时为某个字段添加唯一约束的具体语法格式如下 CREATE TABLE 表名 ( 字段名数据类型 UNIQUE, ); 在以上语法格式中, 字段名表示需要添加唯一约束的列名, 列名后跟着数据类型和 UNIQUE 关键字, 两者之间用空格隔开 接下来通过具体案例演示唯一约束的用法, 见例 5-3 例 5-3 创建员工表 emp, 并按如表 5.2 所示的表结构添加约束 表 5.2 emp 表字段字段类型约束说明 id INT PRIMARY KEY 员工编号 name VARCHAR(20) 员工姓名 phone VARCHAR(20) UNIQUE 员工电话 addr VARCHAR(50) 员工住址 在表 5.2 中列出了 emp 表的结构, 共包含 4 个字段, 其中 id 字段需要添加主键约束, phone 字段需要添加唯一约束 创建 emp 表的 SQL 语句及执行结果如下 mysql> CREATE TABLE emp( -> id INT PRIMARY KEY, -> name VARCHAR(20), -> phone VARCHAR(20) UNIQUE, -> addr VARCHAR(50) Query OK, 0 rows affected (0.08 sec) 以上执行结果证明 emp 表创建完成并添加了约束 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC emp; Field Type Null Key Default Extra id int(11) NO PRI NULL name varchar(20) YES NULL
phone varchar(20) YES UNI NULL addr varchar(50) YES NULL 4 rows in set (0.01 sec) 从以上执行结果可以看出,id 字段的 Key 值为 PRI, 说明主键约束添加成功,phone 字段的 Key 值为 UNI, 说明唯一约束添加成功 此时向 emp 表中添加数据进行验证, 这里直接向表中插入两条数据, 且 phone 字段的值相同 mysql> INSERT INTO emp(id,name,phone,addr) -> VALUES(1,'zs',1366,'xxx'),(2,'ls',1366,'yyy'); ERROR 1062 (23000): Duplicate entry '1366' for key 'phone' 从以上执行结果可以看出, 因为添加的两条数据的 phone 字段值相同, 所以添加失败 此处只需要让两条数据的 phone 字段值不同即可 mysql> INSERT INTO emp(id,name,phone,addr) -> VALUES(1,'zs',1366,'xxx'),(2,'ls',1591,'yyy'); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 以上执行结果证明插入数据完成 为了进一步验证, 使用 SELECT 语句查看 emp 表中的数据 mysql> SELECT * FROM emp; +----+------+-------+------+ id name phone addr +----+------+-------+------+ 1 zs 1366 xxx 2 ls 1591 yyy +----+------+-------+------+ 2 rows in set (0.02 sec) 从以上执行结果可以看出,emp 表中的数据成功插入,phone 字段的值不相同, 这就是唯一约束的作用 同样, 唯一约束也可以添加到已经创建完成的表中, 语法格式如下 ALTER TABLE 表名 ADD UNIQUE( 列名 ); 接下来通过具体案例演示为已经创建完成的表添加唯一约束, 如例 5-4 例 5-4 在创建学生表 stu 后为 cid 字段添加唯一约束, 表结构如表 5.3 所示 表 5.3 stu 表字段字段类型说明 id INT 学生编号 cid INT 课程编号 name VARCHAR(20) 员工姓名 首先创建 stu 表
第 章数据的完整性 mysql> CREATE TABLE stu( -> id INT, -> cid INT, -> name VARCHAR(20) Query OK, 0 rows affected (0.09 sec) 以上执行结果证明 stu 表创建完成 接着为表中的 cid 列添加唯一约束 mysql> ALTER TABLE stu ADD UNIQUE(cid); Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 以上执行结果证明 stu 表中的 cid 字段添加唯一约束成功 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC stu; Field Type Null Key Default Extra id int(11) YES NULL cid int(11) YES UNI NULL name varchar(20) YES NULL 3 rows in set (0.01 sec) 从以上执行结果可以看出,stu 表中的 cid 字段成功添加了唯一约束 5.1.3 自动增长列 在前面的学习中, 数据表中的 id 字段一般从 1 开始插入, 不断增加, 这种做法存在一些问题, 即在添加数据时比较烦琐, 每次都要添加一个 id 字段的值, 而且容易出错 为了解决这个问题, 可以将 id 字段的值设置为自动增加 在 MySQL 中使用 AUTO_ INCREMENT 关键字设置表字段值自动增加 在创建表时将某个字段的值设置为自动增长, 语法格式如下 CREATE TABLE 表名 ( 字段名数据类型 AUTO_INCREMENT, ); 在以上语法格式中, 字段名表示需要设置字段值自动增加的列名, 列名后跟着数据类型和 AUTO_INCREMENT 关键字, 两者之间用空格隔开 接下来通过具体案例演示自动增长列的用法, 见例 5-5
例 5-5 创建员工表 emp2, 并按如表 5.4 所示的表结构添加约束 表 5.4 emp2 表字段字段类型约束说明 id INT PRIMARY KEY AUTO_INCREMENT 员工编号 name VARCHAR(20) 员工姓名 phone VARCHAR(20) 员工电话 在表 5.4 中列出了 emp2 表的结构, 共包含 3 个字段, 其中 id 字段需要添加主键约束并设置为自动增长的列 首先创建 emp2 表 mysql> CREATE TABLE emp2( -> id INT PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(20), -> phone VARCHAR(20) Query OK, 0 rows affected (0.07 sec) 以上执行结果证明 emp2 表创建完成并添加了约束和设置了自动增长列 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC emp2; +-------+-------------+------+-----+---------+----------------+ Field Type Null Key Default Extra +-------+-------------+------+-----+---------+----------------+ id int(11) NO PRI NULL auto_increment name varchar(20) YES NULL phone varchar(20) YES NULL +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) 从以上执行结果可以看出,id 字段的 Key 值为 PRI, 说明主键添加成功 ;Extra 值为 auto_increment, 说明自动增长列设置成功 此时向 emp2 表中添加数据进行验证 mysql> INSERT INTO emp2(name,phone) VALUES('aa',1355); Query OK, 1 row affected (0.04 sec) 以上执行结果证明数据添加成功 为继续验证, 使用 SELECT 语句查看 emp2 表中的数据 mysql> SELECT * FROM emp2; +----+------+-------+ id name phone +----+------+-------+
第 章数据的完整性 1 aa 1355 +----+------+-------+ 1 row in set (0.00 sec) 从以上执行结果可以看出, 数据在插入的同时 id 字段自动生成了数值 1 然后向 emp2 表中添加数据进行验证 mysql> INSERT INTO emp2(name,phone) VALUES('bb',1366); Query OK, 1 row affected (0.04 sec) 以上执行结果证明了数据添加成功 然后使用 SELECT 语句查看 emp2 表中的数据 mysql> SELECT * FROM emp2; +----+------+-------+ id name phone +----+------+-------+ 1 aa 1355 2 bb 1366 +----+------+-------+ 2 rows in set (0.00 sec) 从以上执行结果可以看出, 第二条数据在插入的同时 id 字段自动生成了数值 2, 说 明 id 字段成功设置了自动增长列 此外, 也可以为已经创建完成的表字段设置自动增长列, 语法格式如下 ALTER TABLE 表名 MODIFY 字段名数据类型 PRIMARY KEY AUTO_INCREMENT; 接下来通过具体案例演示为已经创建完成的表字段设置自动增长列, 见例 5-6 例 5-6 在创建教师表 teacher 后为 id 字段添加主键约束, 并设置为自动增长列, 表结构如表 5.5 所示 表 5.5 teacher 表 字 段 字段类型 说 明 id INT 教师编号 name VARCHAR(20) 教师姓名 phone VARCHAR(20) 教师电话 首先创建 teacher 表 mysql> USE qianfeng3; Database changed mysql> CREATE TABLE teacher( -> id INT, -> name VARCHAR(20), -> phone VARCHAR(20) Query OK, 0 rows affected (0.09 sec)
以上执行结果证明 teacher 表创建完成 接着为 id 字段添加主键约束, 并设置为自动增长列 mysql> ALTER TABLE teacher MODIFY id INT PRIMARY KEY AUTO_INCREMENT; Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 以上执行结果证明 teacher 表中 id 字段添加主键约束成功, 并设置为自动增长列 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC teacher; +-------+-------------+------+-----+---------+----------------+ Field Type Null Key Default Extra +-------+-------------+------+-----+---------+----------------+ id int(11) NO PRI NULL auto_increment name varchar(20) YES NULL phone varchar(20) YES NULL +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) 从以上执行结果可以看出,id 字段的 Key 值为 PRI,Extra 值为 auto_increment, 说明 teacher 表中的 id 字段添加主键约束成功, 并设置为自动增长列 5.2 索引 在现实生活中, 人们去图书馆查找一本感兴趣的书时, 如果从第一本开始依次查找, 则效率明显是低下的, 如果按照图书的分类进行查找, 则效率会有所提升 在 MySQL 中查找数据也有类似的问题, 如果使用 SELECT * FROM 表名 WHERE id=1000, 则数据库从第一条记录开始遍历, 直到找到 id 等于 1000 的数据, 这种做法的效率明显非常低,MySQL 提供了索引来解决这个问题 在关系数据库中, 索引是一种单独的 物理的对数据库表中一列或多列的值进行排序的存储结构, 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单 索引的作用相当于图书的目录, 用户可以根据目录中的页码快速找到所需的内容 MySQL 中的索引分为很多种, 包括普通索引 唯一索引 全文索引 单列索引 多列索引 空间索引 组合索引等, 接下来详细讲解其中常用的普通索引和唯一索引 5.2.1 普通索引 普通索引是最基本的索引类型, 它的唯一任务是加快对数据的访问速度, 因此应该只为那些最经常出现在查询条件或排序条件中的数据列创建索引, 尽可能选择一个数据
第 章数据的完整性 最整齐 最紧凑的数据列来创建索引, 例如一个整数数据类型的列 在创建表时可以创建普通索引, 语法格式如下 CREATE TABLE 表名 ( 字段名数据类型, INDEX [ 索引名 ]( 字段名 [( 长度 )]) ); 在以上语法格式中,INDEX 表示字段的索引, 索引名是可选值, 括号中的字段名是创建索引的字段, 参数长度是可选的, 用于表示索引的长度 接下来通过具体案例演示普通索引的创建方法, 见例 5-7 例 5-7 创建测试表 test1, 并为 id 字段添加主键约束, 为 name 字段创建普通索引, 表结构如表 5.6 所示 表 5.6 test1 表字段字段类型说明 id INT 主键 name VARCHAR(20) 普通索引 remark VARCHAR(50) 创建 test1 表并添加约束和索引 mysql> CREATE TABLE test1( -> id INT PRIMARY KEY, -> name VARCHAR(20), -> remark VARCHAR(50), -> INDEX(name) Query OK, 0 rows affected (0.11 sec) 以上执行结果证明 test1 表创建完成, 并添加了主键约束和普通索引 为了进一步验证, 使用 SHOW CREATE TABLE 语句查看表的具体信息 mysql> SHOW CREATE TABLE test1\g; *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE 'test1' ( 'id' int(11) NOT NULL, 'name' varchar(20) DEFAULT NULL, 'remark' varchar(50) DEFAULT NULL, PRIMARY KEY ('id'), KEY 'name' ('name') ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
从以上执行结果可以看出,id 字段为主键,name 字段创建了索引, 说明 test1 表中的 id 字段添加主键约束成功,name 字段创建普通索引成功, 这是在创建表的同时创建普通索引 另外, 对于已经创建完成的表, 也可以为其某个字段创建普通索引, 语法格式如下 CREATE INDEX 索引名 ON 表名 ( 字段名 [( 长度 )]); 接下来通过具体案例演示为已经创建完成的表的字段创建普通索引, 见例 5-8 例 5-8 在创建测试表 test2 后为 id 字段创建普通索引, 表结构如表 5.7 所示 表 5.7 test2 表字段字段类型说明 id INT 普通索引 name VARCHAR(20) remark VARCHAR(50) 首先创建 test2 表 mysql> CREATE TABLE test2( -> id INT, -> name VARCHAR(20), -> remark VARCHAR(50) Query OK, 0 rows affected (0.11 sec) 然后为 test2 表的 id 字段创建普通索引 mysql> CREATE INDEX test2_id ON test2(id); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 以上执行结果证明为 test2 表的 id 字段创建普通索引完成 为了进一步验证, 使用 SHOW CREATE TABLE 语句查看表的具体信息 mysql> SHOW CREATE TABLE test2\g; *************************** 1. row *************************** Table: test2 Create Table: CREATE TABLE 'test2' ( 'id' int(11) DEFAULT NULL, 'name' varchar(20) DEFAULT NULL, 'remark' varchar(50) DEFAULT NULL, KEY 'test2_id' ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 从以上执行结果可以看出, 为 id 字段成功创建了索引, 索引名称为 test2_id
第 章数据的完整性 5.2.2 唯一索引 前面讲解了普通索引, 它允许被索引的数据列包含重复的值, 例如姓名可能出现重复的情况, 但有些值是不能重复的, 在为这个数据列创建索引的时候就应该用 UNIQUE 关键字把它定义为一个唯一索引 唯一索引可以保证数据记录的唯一性, 这种做法的好处 : 一是简化了 MySQL 对这个索引的管理工作, 这个索引也因此变得更有效率 ; 二是 MySQL 会在有新记录插入数据表时自动检查新记录的这个字段的值是否已经在某个记录的这个字段中出现, 如果已经出现,MySQL 将拒绝插入那条新记录 在创建表时可以创建唯一索引, 语法格式如下 CREATE TABLE 表名 ( 字段名数据类型, UNIQUE INDEX [ 索引名 ]( 字段名 [( 长度 )]) ); 在以上语法格式中,UNIQUE INDEX 关键字表示唯一索引, 索引名是可选值, 括号中的字段名是创建索引的字段, 参数长度是可选的, 用于表示索引的长度 接下来通过具体案例演示唯一索引的创建方法, 见例 5-9 例 5-9 创建测试表 test3, 并为 id 字段添加主键约束, 为 name 字段创建唯一索引, 表结构如表 5.8 所示 表 5.8 test3 表字段字段类型说明 id INT 主键 name VARCHAR(20) 唯一索引 remark VARCHAR(50) 创建 test3 表并添加约束和索引 mysql> CREATE TABLE test3( -> id INT PRIMARY KEY, -> name VARCHAR(20), -> remark VARCHAR(50), -> UNIQUE INDEX(name) Query OK, 0 rows affected (0.07 sec) 以上执行结果证明 test3 表创建完成, 并添加了主键和唯一索引 为了进一步验证, 使用 SHOW CREATE TABLE 语句查看表的具体信息 mysql> SHOW CREATE TABLE test3\g; *************************** 1. row ***************************
Table: test3 Create Table: CREATE TABLE 'test3' ( 'id' int(11) NOT NULL, 'name' varchar(20) DEFAULT NULL, 'remark' varchar(50) DEFAULT NULL, PRIMARY KEY ('id'), UNIQUE KEY 'name' ('name') ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 从以上执行结果可以看出,id 字段为主键,name 字段创建了唯一索引, 说明 test3 表中的 id 字段添加主键约束成功,name 字段创建唯一索引成功, 这是在创建表的同时创建唯一索引 另外, 对于已经创建完成的表, 也可以为其某个字段创建唯一索引, 语法格式如下 CREATE UNIQUE INDEX 索引名 ON 表名 ( 字段名 [( 长度 )]); 接下来通过具体案例演示为已经创建完成的表的字段创建唯一索引, 见例 5-10 例 5-10 在创建测试表 test4 后为 id 字段创建唯一索引, 表结构如表 5.9 所示 表 5.9 test4 表字段字段类型说明 id INT 唯一索引 name VARCHAR(20) remark VARCHAR(50) 首先创建 test4 表 mysql> CREATE TABLE test4( -> id INT, -> name VARCHAR(20), -> remark VARCHAR(50) Query OK, 0 rows affected (0.08 sec) 然后为 test4 表的 id 字段创建唯一索引 mysql> CREATE UNIQUE INDEX test4_id ON test4(id); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 以上执行结果证明 test4 表的 id 字段的唯一索引创建完成 为了进一步验证, 使用 SHOW CREATE TABLE 语句查看表的具体信息 mysql> SHOW CREATE TABLE test4\g; *************************** 1. row *************************** Table: test4
第 章数据的完整性 Create Table: CREATE TABLE 'test4' ( 'id' int(11) DEFAULT NULL, 'name' varchar(20) DEFAULT NULL, 'remark' varchar(50) DEFAULT NULL, UNIQUE KEY 'test4_id' ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 从以上执行结果可以看出,id 字段成功创建了唯一索引, 索引名称为 test4_id 5.3 域完整性 域完整性是对关系中的单元格进行约束, 域代表单元格, 也就是对列的约束 域完整性约束包括数据类型 非空约束 默认值约束和 CHECK 约束, 数据类型在前面章节中已经讲解过,MySQL 会忽略 CHECK 约束, 因此本节只讲解非空约束和默认值约束 5.3.1 非空约束 非空约束用于保证数据表中某个字段的值不为 NULL, 在 MySQL 中使用 NOT NULL 关键字添加非空约束 在创建表时, 为某个字段添加非空约束的具体语法格式如下 CREATE TABLE 表名 ( 字段名数据类型 NOT NULL, ); 在以上语法格式中, 字段名是需要添加非空约束的列名, 列名后跟着数据类型和 NOT NULL 关键字, 两者之间用空格隔开 接下来通过具体案例演示非空约束的用法, 见例 5-11 例 5-11 创建测试表 test5, 并按如表 5.10 所示的表结构添加约束 表 5.10 test5 表字段字段类型约束 id INT PRIMARY KEY name VARCHAR(20) NOT NULL addr VARCHAR(50) 在表 5.10 中列出了 test5 表的结构, 共包含 3 个字段, 其中 id 字段需要添加主键约束,name 字段需要添加非空约束 首先创建 tese5 表 mysql> CREATE TABLE test5(
-> id INT PRIMARY KEY, -> name VARCHAR(20) NOT NULL, -> addr VARCHAR(50) Query OK, 0 rows affected (0.07 sec) 以上执行结果证明 test5 表创建完成并添加了约束 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC test5; Field Type Null Key Default Extra id int(11) NO PRI NULL name varchar(20) NO NULL addr varchar(50) YES NULL 3 rows in set (0.01 sec) 从以上执行结果可以看出,id 字段的 Key 值为 PRI, 说明主键约束添加成功 ;name 字段的 Null 列显示为 NO, 即不可以为 NULL 值, 说明非空约束添加成功 此时向 test5 表中添加数据进行验证 mysql> INSERT INTO test5(id,name,addr) VALUES(1,NULL,'xxx'); ERROR 1048 (23000): Column 'name' cannot be null 从以上执行结果可以看出, 因为添加 name 字段的值为 NULL, 所以添加失败 此外, 非空约束也可以添加到已经创建完成的表中, 语法格式如下 ALTER TABLE 表名 MODIFY 字段名数据类型 NOT NULL; 接下来通过具体案例演示为已经创建完成的表添加非空约束, 见例 5-12 例 5-12 创建测试表 test6, 在创建完成后为 id 字段添加非空约束, 表结构如表 5.11 所示 首先创建 test6 表 表 5.11 test6 表 字 段 字段类型 id INT name VARCHAR(20) addr VARCHAR(50) mysql> CREATE TABLE test6( -> id INT, -> name VARCHAR(20), -> addr VARCHAR(50)
第 章数据的完整性 Query OK, 0 rows affected (0.08 sec) 以上执行结果证明 test6 表创建完成 然后为 test6 表中的 id 列添加非空约束 mysql> ALTER TABLE test6 MODIFY id INT NOT NULL; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 以上执行结果证明 test6 表中的 id 字段添加非空约束成功 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC test6; Field Type Null Key Default Extra id int(11) NO NULL name varchar(20) YES NULL addr varchar(50) YES NULL 3 rows in set (0.01 sec) 从以上执行结果可以看出,test6 表中 id 字段的 Null 列的值为 NO, 说明成功添加了非空约束 5.3.2 默认值约束 默认值约束用于为数据表中某个字段的值添加默认值, 例如订单的创建时间, 如果不进行手动填写, 可以设置创建时间字段的默认值为当前时间 在 MySQL 中使用 DEFAULT 关键字添加默认值约束, 为某个字段添加默认值约束的具体语法格式如下 CREATE TABLE 表名 ( 字段名数据类型 DEFAULT 默认值, ); 在以上语法格式中, 字段名表示需要添加默认值约束的列名, 列名后跟着数据类型和 DEFAULT 关键字,DEFAULT 是添加的默认值 接下来通过具体案例演示默认值约束的用法, 见例 5-13 例 5-13 创建测试表 test7, 并按如表 5.12 所示的表结构添加约束 表 5.12 test7 表字段字段类型约束 id INT PRIMARY KEY name VARCHAR(20) addr VARCHAR(50) DEFAULT 'ABC'
在表 5.12 中列出了 test7 表的结构, 共包含 3 个字段, 其中 id 字段需要添加主键约束,addr 字段需要添加默认值约束 首先创建 test7 表 mysql> CREATE TABLE test7( -> id INT PRIMARY KEY, -> name VARCHAR(20), -> addr VARCHAR(50) DEFAULT 'ABC' Query OK, 0 rows affected (0.08 sec) 以上执行结果证明 test7 表创建完成并添加了约束 为了进一步验证, 使用 DESC 语句查看表结构 mysql> DESC test7; Field Type Null Key Default Extra id int(11) NO PRI NULL name varchar(20) YES NULL addr varchar(50) YES ABC +------- +-------------+------+-----+---------+-------+ 3 rows in set (0.02 sec) 从以上执行结果可以看出,id 字段的 Key 值为 PRI, 说明主键约束添加成功 ;addr 字段的 Default 值为 ABC, 说明默认值约束添加成功 此时向 test7 表中添加数据进行验证 mysql> INSERT INTO test7(id,name) VALUES(1,'zs'); Query OK, 1 row affected (0.05 sec) 以上执行结果证明 test7 表添加了一条数据, 且只添加了 id 和 name 两个字段的值, 这时可以查看表中的数据 mysql> SELECT * FROM test7; +----+------+------+ id name addr +----+------+------+ 1 zs ABC +----+------+------+ 1 row in set (0.00 sec) 从以上执行结果可以看出,test7 表中的 addr 字段使用了默认值 ABC, 说明默认值约束添加成功 此外, 默认值约束也可以添加到已经创建完成的表中, 语法格式如下 ALTER TABLE 表名 MODIFY 字段名数据类型 DEFAULT 默认值 ;
第 章数据的完整性 接下来通过具体案例演示为已经创建完成的表添加默认值约束, 见例 5-14 例 5-14 创建测试表 test8, 在创建完成后为 name 字段添加默认值约束, 默认值 为 lilei, 表结构如表 5.13 所示 表 5.13 test8 表 字 段 字段类型 id INT name VARCHAR(20) addr VARCHAR(50) 首先创建 test8 表 mysql> CREATE TABLE test8( -> id INT, -> name VARCHAR(20), -> addr VARCHAR(50) Query OK, 0 rows affected (0.08 sec) 以上执行结果证明 test8 表创建完成 然后为 test8 表中的 name 字段添加默认值约束 mysql> ALTER TABLE test8 MODIFY name VARCHAR(20) DEFAULT 'lilei'; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 以上执行结果证明 test8 表中的 name 字段加默认值约束成功 为了进一步验证, 使 用 DESC 语句查看表结构 mysql> DESC test8; Field Type Null Key Default Extra id int(11) YES NULL name varchar(20) YES lilei addr varchar(50) YES NULL 3 rows in set (0.00 sec) 从以上执行结果可以看出,test8 表中 name 字段的 Default 列的值为 lilei, 说明成功 添加了默认值约束 5.4 引用完整性 引用完整性是对实体之间关系的描述, 是定义外关键字与主关键字之间的引用规
则, 也就是外键约束 如果要删除被引用的对象, 那么也要删除引用它的所有对象, 或者把引用值设置为空 接下来详细讲解与外键约束有关的内容 5.4.1 外键的概念 外键是指引用另一个表中的一列或多列, 被引用的列应该具有主键约束或唯一约束 外键用于建立和加强两个表数据之间的连接, 接下来通过两张表讲解什么是外键约束 首先创建学科表 subject, 它包含两个字段 ( 专业编号 sub_id 和专业名称 sub_name) mysql> CREATE TABLE subject( -> sub_id INT PRIMARY KEY, -> sub_name VARCHAR(20) Query OK, 0 rows affected (0.35 sec) 然后创建学生表 student, 它包含 3 个字段 ( 学生编号 stu_id 学生姓名 stu_name 和专业编号 sub_id) mysql> CREATE TABLE student( -> stu_id INT PRIMARY KEY, -> stu_name VARCHAR(20), -> sub_id INT NOT NULL Query OK, 0 rows affected (0.16 sec) 在创建的 subject 表中 sub_id 为主键, 在 student 表中也有 sub_id 字段, 此处是引入了 subject 表的主键, 那么 student 表中的 sub_id 字段就是外键 被引用的 subject 表是主表, 引用外键的 student 表是从表, 两个表是主从关系,student 表可以通过 sub_id 连接 subject 表中的信息, 从而建立两个表的数据之间的连接 因为 student 表中的 sub_id 字段是外键, 所以当外键字段引用了主表 subject 的数据时,subject 表不允许单方面删除表或表中的数据, 需要先删除引用它的所有对象, 或者把引用值设置为空 接下来验证这种情况, 首先为主表 subject 添加数据 mysql> INSERT INTO subject(sub_id,sub_name) VALUES(1,'math'); Query OK, 1 row affected (0.08 sec) 以上执行结果证明了插入数据完成 为了进一步验证, 使用 SELECT 语句查看 subject 表中的数据 mysql> SELECT * FROM subject; +--------+----------+ sub_id sub_name +--------+----------+
第 章数据的完整性 1 math +--------+----------+ 1 row in set (0.03 sec) 从以上执行结果可以看出,subject 表中的数据成功插入 然后向 student 表中插入数据 mysql> INSERT INTO student(stu_id,stu_name,sub_id) VALUES(1,'zs',1); Query OK, 1 row affected (0.05 sec) 以上执行结果证明插入数据完成 为了进一步验证, 使用 SELECT 语句查看 student 表中的数据 mysql> SELECT * FROM student; +--------+----------+--------+ stu_id stu_name sub_id +--------+----------+--------+ 1 zs 1 +--------+----------+--------+ 1 row in set (0.00 sec) 从以上执行结果可以看出,student 表中的数据成功插入, 表中的 sub_id 为 1, 是引用了 subject 表中 sub_id 字段的值 然后删除主表 subject 中的数据 mysql> DELETE FROM subject; Query OK, 1 row affected (0.07 sec) 从以上执行结果可以看出,subject 表中的数据删除成功, 这明显不符合外键约束的作用, 当数据被从表引用时, 主表中的数据不应该被删除, 这是因为此时还没有为 sub_id 字段添加外键约束, 接下来详细讲解如何添加外键约束 5.4.2 添加外键约束 前面讲解了外键约束是什么以及为什么需要外键约束, 若想真正连接两个表的数据, 就需要为表添加外键约束, 语法格式如下 ALTER TABLE 表名 ADD FOREIGN KEY( 外键字段名 ) REFERENCES 主表表名 ( 主键字段名 ); 接下来仍然使用前面创建的 student 表和 subject 表, 并清空两张表的数据, 此处就不再演示 为 student 表中的 sub_id 字段添加外键约束 mysql> ALTER TABLE student -> ADD FOREIGN KEY(sub_id) REFERENCES subject(sub_id); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0
以上执行结果证明了添加外键约束成功 此时, 如果先为 student 表添加数据, 则是无法添加的, 因为 subject 表中还没有可以引用的数据 mysql> INSERT INTO student(stu_id,stu_name,sub_id) VALUES(1,'zs',1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('qianfeng3'.'student', CONSTRAINT 'student_ibfk_1' FOREIGN KEY ( 'sub_id') REFERENCES 'subject' ('sub_id')) 从以上执行结果可以看出, 主表中没有数据, 从表中无法插入数据 此时先为 subject 表插入数据 mysql> INSERT INTO subject(sub_id,sub_name) VALUES(1,'math'); Query OK, 1 row affected (0.08 sec) 以上执行结果证明了插入数据完成 然后为 student 表插入数据 mysql> INSERT INTO student(stu_id,stu_name,sub_id) VALUES(1,'zs',1); Query OK, 1 row affected (0.05 sec) 以上执行结果证明插入数据完成 接下来仍然进行前面的实验, 直接删除 subject 表中的数据 mysql> DELETE FROM subject; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ('qianfeng3'.'student', CONSTRAINT 'student_ibfk_1' FOREIGN KEY ('sub_id') REFERENCES 'subject' ('sub_id')) 从以上执行结果可以看出, 因为 subject 表中的数据被 student 表引用, 所以无法删除 subject 表中的数据 此时可以先删除从表中的数据, 再删除主表中的数据 首先删除 student 表中的数据 mysql> DELETE FROM student; Query OK, 1 row affected (0.03 sec) 以上执行结果证明 student 表中的数据删除成功 然后删除主表 subject 中的数据 mysql> DELETE FROM subject; Query OK, 1 row affected (0.03 sec) 以上执行结果证明 subject 表中的数据删除成功, 这就是外键约束的基本使用 用户除了可以在创建表后添加外键约束以外, 还可以在创建表的同时添加外键约束, 语法格式如下 CREATE TABLE 表名 ( 字段名数据类型,, FOREIGN KEY( 外键字段名 ) REFERENCES 主表表名 ( 主键字段名 ) ); 接下来通过一个案例演示在创建表的同时添加外键约束, 创建学生表 student2 和分
第 章数据的完整性 数表 score, 其中学生表包含两个字段 ( 学生编号 stu_id 和学生姓名 stu_name), 分数表包含 3 个字段 ( 分数编号 sco_id 分数 score 和学生编号 stu_id) 首先创建学生表 mysql> CREATE TABLE student2( -> stu_id INT PRIMARY KEY, -> stu_name VARCHAR(20) Query OK, 0 rows affected (0.08 sec) 以上执行结果证明 student2 表创建成功 然后创建分数表, 在创建表的同时添加外键约束 mysql> CREATE TABLE score( -> sco_id INT PRIMARY KEY, -> score INT, -> stu_id INT, -> FOREIGN KEY(stu_id) REFERENCES student2(stu_id) Query OK, 0 rows affected (0.09 sec) 以上执行结果证明 score 表创建成功, 在创建的同时添加了外键约束 为了进一步验证, 使用 SHOW CREATE TABLE 语句查看 score 表 mysql> SHOW CREATE TABLE score\g; *************************** 1. row *************************** Table: score Create Table: CREATE TABLE 'score' ( 'sco_id' int(11) NOT NULL, 'score' int(11) DEFAULT NULL, 'stu_id' int(11) DEFAULT NULL, PRIMARY KEY ('sco_id'), KEY 'stu_id' ('stu_id'), CONSTRAINT 'score_ibfk_1' FOREIGN KEY ('stu_id') REFERENCES 'student2' ('stu_id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 从以上执行结果可以看出,score 表中的 stu_id 字段有外键约束, 关联的主表为 student2 5.4.3 删除外键约束 前面讲解了添加外键约束的两种方式, 在实际开发中可能会出现需要解除两个表之间的关联关系的情况, 这就需要删除外键约束, 语法格式如下 ALTER TABLE 表名 DROP FOREIGN KEY 外键名 ;
接下来演示将 student 表中的外键约束删除, 首先查看表的外键名 mysql> SHOW CREATE TABLE student\g; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE 'student' ( 'stu_id' int(11) NOT NULL, 'stu_name' varchar(20) DEFAULT NULL, 'sub_id' int(11) NOT NULL, PRIMARY KEY ('stu_id'), KEY 'sub_id' ('sub_id'), CONSTRAINT 'student_ibfk_1' FOREIGN KEY ('sub_id') REFERENCES 'subject' ('sub_id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 从以上执行结果可以看出,sub_id 字段为外键, 关联的主表是 subject, 外键名为 student_ibfk_1 接下来删除这个外键约束 mysql> ALTER TABLE student DROP FOREIGN KEY student_ibfk_1; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 以上执行结果证明 student 表的外键约束删除成功 为了进一步验证, 使用 SHOW CREATE TABLE 语句查看 student 表 mysql> SHOW CREATE TABLE student\g; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE 'student' ( 'stu_id' int(11) NOT NULL, 'stu_name' varchar(20) DEFAULT NULL, 'sub_id' int(11) NOT NULL, PRIMARY KEY ('stu_id'), KEY 'sub_id' ('sub_id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 从以上执行结果可以看出,student 表的外键约束删除成功 5.5 本章小结 本章首先介绍了实体完整性, 其中重点讲解了主键约束 唯一约束和自动增长列 ; 其次介绍了索引, 其中举例讲解了普通索引和唯一索引 ; 再次讲解了域完整性, 其中重点讲解了非空约束和默认值约束 ; 最后讲解了引用完整性 对于本章, 大家需要多理解引用完整性, 以便于后面多表查询的学习
第 章数据的完整性 5.6 习题 1. 填空题 (1) 用于唯一地标识表中的某一条记录 (2) 在 MySQL 中使用 关键字添加唯一约束 (3) 在 MySQL 中使用 关键字设置表字段值自动增加 (4) 普通索引是最基本的索引类型, 它的唯一任务是加快对数据的 (5) 是指引用另一个表中的一列或多列, 被引用的列应该具有主键约束或 唯一约束 2. 选择题 (1) 在 MySQL 中使用 ( ) 关键字添加非空约束 A.NOT NULL B.CREATE C.PRIMARY KEY D.ALTER (2) 在 MySQL 中使用 ( ) 关键字添加默认值约束 A.DROP B.ALTER C.UNIQUE D.DEFAULT (3) 引用完整性是对实体之间关系的描述, 是定义 ( ) 与主关键字之间的引用 规则 A. 唯一约束 B. 外键关键字 C. 默认值约束 D. 普通索引 (4) 若需要真正连接两个表的数据, 可以为表添加 ( ) A. 唯一约束 B. 主键约束 C. 唯一索引 D. 外键约束 (5) 解除两个表之间的关联关系需要删除 ( ) A. 外键约束 B. 唯一约束 C. 普通索引 D. 主键约束 3. 思考题 (1) 简述实体完整性有哪些 (2) 简述索引分为哪几类 (3) 简述索引的作用 (4) 简述域完整性有哪些 (5) 简述引用完整性的作用