第四章 - 数据表与数据完整性
第四章 - 数据表与数据完整性
4.1 MySQL数据表的管理
4.1.1 数据表的存储
创建数据库的是为了存储、管理和查询数据，而表是存储数据的最重要的载体。表是MySQL数据库中最重要的数据库对象，也是构建高性能数据库的基础。数据表设计的优劣将影响磁盘空间使用效率、数据处理时内存的利用率以及数据的查询效率。
本章将通过建立一个教务管理数据库中的学生、课程、教师和成绩等数据表，介绍各种数据表的创建、修改、管理、存储与数据格式转换，以及实现数据完整性的方法和基本操作。
在MySQL数据库系统中，可以按照不同的标准对表进行分类。
按照表的用途分类:
① 系统表
用于维护MySQL服务器和数据库正常工作的数据表。例如，系统数据库mysql中就存在若干系统表。
② 用户表
由用户自己创建的、用于各种数据库应用系统开发的表。
③ 分区表
分区表是将数据水平划分为多个单元的表，这些单元可以分布到数据库中的多个文件组中。在维护整个集合的完整性时，使用分区可以快速而有效地访问或管理数据子集，从而使大型表或索引更易于管理。
按照表的存储时间分类
① 永久表：
包括SQL Server的系统表和用户数据库中创建的数据表，该类表除非人工删除，否则一直存储在介质中。
② 临时表：
临时表只有创建该表的用户在用来创建该表的连接中可见。临时表关联的连接被关闭时，临时表自动地被删除。如果服务器关闭，则所有临时表会被清空、关闭。
4.1.2 InnoDB存储引擎的表空间
1、查看数据库的表空间
利用如下命令可以查看数据库的表空间。
mysql> show variables like 'InnoDB_data%';
表空间有四个文件组成：ibdata1、ibdata2、ibdata3、ibdata4，每个文件的大小为10M，当每个文件都满了的时候，ibdata4会自动扩展；
不管是共享表空间和独立表空间，都会存在InnoDB_data_file文件，因为这些文件不仅仅要存放数据，而且还要存储事务回滚（undo）信息。
2、共享表空间和独立表空间的比较
共享表空间的特点：
-
表空间可以分成多个文件存放在一起方便管理。
-
多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，对于经常删除操作的这类应用最不适合用共享表空间。
-
共享表空间分配后不能回缩。
独立表空间的特点：
-
每个表都有独立的表空间，每个表的数据和索引都会存在 自已的表空间中，可以实现单表在不同的数据库中移动。
-
Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过命令“alter table TableName engine=innodb; ”回收不用的空间。
-
对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
-
单表增加过大，当单表占用空间过大时，存储空间会不足。
3、共享表空间和独立表空间之间的转换
（1）查看当前数据库的表空间管理类型。可以通过如下命令查看。
mysql> show variables like "InnoDB_file_per_table";
（2）修改数据库的表空间管理方式。修改InnoDB_file_per_table的参数值（InnoDB_file_per_table=1 为使用独占表空间，InnoDB_file_per_table=0 为使用共享表空间）即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；
（3）共享表空间转化为独立表空间的方法（参数InnoDB_file _per_table=1需要设置）。
单个表的转换操作可以用如下命令实现：
alter table table_name engine = innodb;
4.1.3 创建数据库表
1、查看数据库的表空间
查看数据库的表空间，利用如下命令可以查看数据库的表空间。
mysql> show variables like 'InnoDB_data%';
表空间有四个文件组成：ibdata1、ibdata2、ibdata3、ibdata4，每个文件的大小为10M，当每个文件都满了的时候，ibdata4会自动扩展。
如果用 autoextend 选项描述最后一个数据文件，当 InnoDB 用尽所有表自由空间后将会自动扩充最后一个数据文件，每次增量为 8 MB。
不管是共享表空间和独立表空间，都会存在InnoDB_data_file文件，因为这些文件不仅仅要存放数据，而且还要存储事务回滚（undo）信息。
2、创建表的语法结构
表决定了数据库的结构， 表是存放数据的地方，一个库需要什么表，各数据库表中有什么样的列，是要合理设计的。
创建表的语法结构如下：
create [temporary]table[if not exists]table_name
[([column_definition], ...|[index_definition])]
[table_option][select_statement];
-- CREATE TABLE命令的基本格式：
CREATE TABLE tbl_name
（列名1 数据类型 1 [NOT NULL | NULL] ,
列名2 数据类型 2 [NOT NULL | NULL] ,
…..）
column_definition：字段的定义。包括指定字段名、数据类型、是否允许空值，指定默认值、主键约束、唯一性约束、注释字段名、是否为外键，以及字段类型的属性等。
字段的定义具体格式描述如下：
col_name type [not null | null] [default default_value]
[auto_increment] [unique [key] | [primary] key]
[comment 'string'] [reference_definition]
利用SQL语句创建数据表
本书的教务管理数据库teaching将根据需求分析和简化，创建5张表：student(学生表)、course（课程表）、score（成绩表）、teacher（教师表）和teach_course（纽带表）。各表的结构如表4-1至表4-5所示。
【例4.1】按照表所示的学生信息表结构创建student表。
| 列序号 | 字段名 | 类型 | 取值说明 | 列含义 |
|---|---|---|---|---|
| 1 | studentno | char(11) | 主键 | 学生学号 |
| 2 | sname | char(8) | 否 | 学生姓名 |
| 3 | sex | enum (2) | 否 | 性别 |
| 4 | birthdate | date | 否 | 出生日期 |
| 5 | entrance | int(3) | 否 | 入学成绩 |
| 6 | phone | varchar(12) | 否 | 电话 |
| 7 | varchar(20) | 否 | 电子信箱 |
mysql> create table if not exists student (
studentno char(11) not null comment '学号',
sname char(8) not null comment '姓名',
sex enum('男', '女') default '男' comment '性别',
birthdate date not null comment '出生日期',
entrance int(3) null comment '入学成绩',
phone varchar(12) not null comment '电话',
Email varchar(20) not null comment '电子信箱',
primary key (studentno));
mysql> create table if not exists student (
studentno char(11) not null primary key,
sname char(8) not null,
sex enum('男', '女') default '男',
birthdate date not null,
entrance int(3) null,
phone varchar(12) not null,
Email varchar(20) not null,);
注意：在创建数据表之前要先创建数据库，并使用use数据库，在数据库中创建表，否则会报错。
【例4.2】利用create table命令建立课程信息表course，表结构如表4-2所示。
| 列序号 | 字段名 | 类型 | 取值说明 | 列含义 |
|---|---|---|---|---|
| 1 | courseno | char(6) | 主键 | 课程编号 |
| 2 | cname | char(20) | 否 | 课程名称 |
| 3 | type | char(8) | 否 | 类别 |
| 4 | period | int(2) | 否 | 总学时 |
| 5 | exp | int(2) | 否 | 实验学时 |
| 6 | term | int(2) | 否 | 开课学期 |
mysql> create table if not exists course(
courseno char(6) not null primary key,
cname char(6) not null,
type char(8) not null,
period int(2) not null,
exp int(2) not null,
term int(2) not null);
【例4.3】利用create table命令建立学生分数表score，表结构如表4-3所示。该表中主键由两个列构成。
| 列序号 | 字段名 | 类型 | 取值说明 | 列含义 |
|---|---|---|---|---|
| 1 | studentno | char(11) | 主键 | 学号 |
| 2 | courseno | char(6) | 主键 | 课程编号 |
| 3 | daily | float(3,1) | 否 | 平时成绩 |
| 4 | final | float(3,1) | 否 | 期末成绩 |
mysql> create table if not exists score
(studentno char(11) not null,
courseno char(6) not null,
daily float(3,1) default 0,
final float(3,1) default 0,
primary key (studentno , courseno)
);
【例4.4】利用create table命令建立教师信息表teacher，表结构如表4-4所示。
| 列序号 | 字段名 | 类型 | 取值说明 | 列含义 |
|---|---|---|---|---|
| 1 | teacherno | char(6) | 主键 | 教师编号 |
| 2 | tname | char(8) | 否 | 教师姓名 |
| 3 | major | char(10) | 否 | 专业 |
| 4 | prof | char(10) | 是 | 职称 |
| 5 | department | char(16) | 否 | 院系部门 |
mysql> create table if not exists teacher
(teacherno char(6) not null comment '教师编号',
tname char(8) not null comment'教师姓名',
major char(10) not null comment '专业',
prof char(10) not null comment '职称',
department char(16) not null comment '部门',
primary key (teacherno)
);
【例4.5】为了完善teaching数据库的表间联系，创建表结构如表4-5所示的纽带表teach_course。
| 列序号 | 字段名 | 类型 | 取值说明 | 列含义 |
|---|---|---|---|---|
| 1 | teacherno | nchar(6) | 主键 | 教师编号 |
| 2 | courseno | nchar(6) | 主键 | 课程编号 |
mysql> create table if not exists teach_course
(teacherno char(6) not null,
courseno char(6) not null,
primary key (teacherno,courseno)
);
说明：
（1）主键设置。primary key表示设置该字段为主键。
（2）添加注释。comment'学号'表示对studentno字段增加注释为“学号”
（3）字段类型的选择。sex enum('男' ，'女')表示sex字段的字段类型是enum，取值范围为'男'和 '女'。对于取值固定的字段可以设置数据类型为enum。例如，在course表的type字段表示的是课程的类型，一般是固定的几种类型。因此，也可以把该字段的定义写成：type enum ('必修课'，' 选修课') default '必修课'。
（4）默认值的设置。default'男'表示默认值为“男”。
（5）设置精度。Score表中的daily float(3,1)表示精度为4 ，小数位1位。
（6）如果没有指定是null或是not null，则列在创建时假定为null。
设置表的属性值自动增加
在MySQL数据表中，一个整数列可以拥有一个附加属性auto_increment。其主要用于为表中插入的新记录自动生成唯一的序列编码。
默认的情况下，该字段值是从1开始自增，也可自定义开始值。一个数据表只能有一个字段使用auto_increment约束，且该字段必须为主键的一部分。可以是任何整数类型（tinyint、samllint、int、bigint等）。
设置属性值字段增加的基本语法规则如下：
属性名 数据类型 auto_increment
【例4.6】 在teaching库中，创建选课表sc，选课号sc_no是自动增量，选课时间默认为当前时间，其他字段分别是学号、课程号和教师号。
mysql> create table sc (
-> sc_no int(6) not null auto_increment,
-> studentno char(11) not null,
-> courseno char(6) not null,
-> teacherno char(6) not null,
-> sc_time timestamp not null default now(),
-> primary key(sc_no));
Query OK, 0 rows affected (0.03 sec)
4.1.4 查看表
数据表创建后，就可以用 show tables 命令查询已创建的表的情况。也可以查看表结构，即是指查看数据库中已存在的表的定义。
查看表结构的语句包括 describe 语句和 show create table 语句。
通过这两个语句，可以查看表的字段名、字段的数据类型、完整性约束条件等。
（1）查看已经创建的表。命令和运行结果如下：
mysql>show tables;
（2）查看表基本结构语句describe。MySQL中，describe语句可以查看表的基本定义。其中包括，字段名、字段数据类型、是否为主键和默认值等。
describe语句的命令如下：
mysql> describe student;
也可以缩写为desc形式
mysql> desc student;
（3） 查看表详细结构语句show create table。MySQL中，show create table语句可以查看表的详细定义。
show create table语句的命令如下：
mysql> show create table course;
（4）当数据库表创建完毕后，也可以通过安装路径（如：C:\Documents and Settings\All Users\MySQL\MySQL Server 5.7\Data\db4）查看磁盘文件数据库及其包含的数据表文件，如图
从图中可以查看数据库db4中创建的各个数据表文件，如课程表course 、成绩信息表score、 选修课程表se_course等。
4.1.5 修改数据库表
修改表是指修改数据库中已存在的表的定义。修改表比重新定义表简单，不需要重新加载数据，也不会影响正在进行的服务。MySQL中通过alter table语句来修改表。
修改表包括修改表名、修改字段数据类型、修改字段名、增加字段、删除字段、修改字段的排列位置、更改默认存储引擎和删除表的外键约束等。
修改表语法格式。修改数据库表语法格式如下：
alter [ignore] table tbl_name
alter_specification [, alter_specification] ...
alter_specification:
add [column] column_definition [first | after col_name ] //添加字段
|alter [column]col_name{set default literal|drop default}//修改字段默认值
|change [column] old_col_name column_definition //重命名字段
[first|after col_name]
|modify [column]column_definition[first|aftercol_name] //修改字段数据类型
|drop [column] col_name //删除列
|rename [TO] new_tbl_name //对表重命名
|order by col_name //按字段排序
|convert TO character set charset_name[collate collation_name]
//将字符集转换为二进制
|[default] character set charset_name [collate collation_name]
//修改表的默认字符集
修改数据库表的示例
alter table用于更改原有表的结构。例如，可以增加或删减字段、重新命名字段或表，还可以修改默认字符集。
（1）增加字段。
在创建表时，表中的字段就已经定义完成。如果要增加新的字段，可以通过alter table语句进行增加。
增加表的字段，可以实现如下功能：
- 增加无完整性约束条件的字段。
- 增加有完整性约束条件的字段。
- 表的第一个位置增加字段。
- 表的指定位置之后增加字段。
【例4.7】 在student表的Email列后面增加一列address。
mysql>alter table student
->add address varchar(30) not null after Email;
（2）修改表名。
表名可以在一个数据库中唯一的确定一张表。数据库系统通过表名来区分不同的表。MySQL中，修改表名是通过SQL语句alter table实现的。
【例4.8】 将表sc重名为se_course。
mysql> alter table sc rename to se_course;
（3）修改字段的数据类型。alter table语句也可以修改字段的数据类型。
【例4.9】修改course表的type字段，因为该字段一般是取固定值。因此，也可以把该字段的定义写成：type enum (‘必修课，’ 选修课‘) default ’必修课‘。
mysql> alter table course
-> modify type enum('必修','选修') default '必修';
（4）删除字段。删除字段是指删除已经定义好的表中的某个字段。MySQL中，alter table语句也可以删除表中的字段。
【例4.10】 删除student表的字段address。
mysql> alter table student drop address;
4.1.6 删除数据库表
删除表是指删除数据库中已存在的表。删除表时，会删除表中的所有数据。因此，在删除表时要特别注意。
MySQL中通过drop table语句来删除表。删除表的语法格式如下：
drop table table_name;
【例4.11】在mysqltest数据库中创建表example，然后删除example表。
mysql> use mysqltest;
Database changed
mysql> Create table example(
-> today datetime,
-> name char(20) );
Query OK, 0 rows affected (0.11 sec)
mysql> desc example;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| today | datetime | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> drop table example ;
Query OK, 0 rows affected (0.07 sec)
4.1.7 临时表的管理
Mysql临时表适合当工作在非常大的表上时，偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。
创建临时表很容易，给正常的create table语句加上temporary关键字即可。例如，创建临时表tmp_emp1。
mysql> create temporary table tmp_emp1
-> (name varchar(10) not null,
-> value integer not null
-> );
临时表将在连接MySQL期间存在。断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除临时表并释放空间。删除方法与一般用户表相同。
drop table tmp_table
说明：
（1）创建临时表你必须有create temporary table 权限。
（2）show tables语句不会列举临时表。
（3）不能用rename来重命名一个临时表。
4.2 表的数据操作
MySQL数据表分为表结构（Structure）和数据记录（Record）2部分。前面创建表的操作，仅仅是创建了表结构，表结构即决定表拥有哪些字段以及这些字段的名称、数据类型、长度、精度、小数位数、是否允许空值（null）、设置默认值和主键等。
MySQL语言一般通过insert、update和delete等3种DML语句对表进行数据的添加、更新和删除数据操作，并以此维护和修改表的数据。
4.2.1 插入表记录
1、insert into| replace语句
为数据表输入数据的方式有多种，常见的有通过命令方式添加行数据的，也可以通过程序实现表数据的添加。可以通过insert into、replace into语句插入，也可以使用load data infile方式将保存在文本文件中的数据插入到指定的表。
使用insert into| replace语句添加数据
（1）insert into| replace语句语法格式：
insert｜replace[into]table_name[(col_name,...)]
values({expr|default},...),(...),...
|set col_name ={expr|default}, ...
【例4.12】利用insert表student中插入1行数据。
代码和运行结果如下：
mysql>insert into student
->(studentno,sname,sex,birthdate,entrance,phone,Email)
-> values ('18122210009','许东山','男','1999/11/5',789, '13623456778','qwe@163.com');
【例4.13】利用insert into命令向表student中插入多行数据。
mysql> insert into student values
('18122221324','何白露','女','2000/12/4','879','13178978999','heyy@sina.com '),
('18125111109','敬横江','男','2000/3/1','789','15678945623','jing@sina.com '),
('18125121107','梁一苇','女','1999/9/3','777','13145678921','bing@126.com '),
('18135222201','凌浩风','女','2001/10/6','867','15978945645','tang@163.com '),
('18137221508','赵临江','男','2000/2/13','789','12367823453','ping@163.com '),
('19111133071','崔依歌','女','2001/6/6','787','15556845645','cui@126.com '),
('19112100072','宿沧海','男','2002/2/4','658','12545678998','su12@163.com'),
('19112111208','韩山川','男','2001/2/14','666','15878945612','han@163.com '),
('19122203567','封月明','女','2002/9/9','898','13245674564','jiao@126.com'),
('19123567897','赵既白','女','2002/8/4','999','13175689345','pingan@163.com'),
('19126113307','梅惟江','女','2003/9/7','787','13245678543','zhu@163.com');
Query OK, 11 rows affected (0.05 sec)
【例4.14】利用replace into命令向表course中插入多行数据。
mysql> replace into course values
-> ('c05103','电子技术','必修','64','16','2'),
-> ('c05109','C语言','必修','48','16','2'),
-> ('c05127','数据结构','必修','64','16','2'),
-> ('c05138','软件工程','选修','48','8','5'),
-> ('c06108','机械制图','必修','60','8','2'),
-> ('c06127','机械设计','必修','64','8','3'),
-> ('c06172','铸造工艺','选修','42', '16','6'),
-> ('c08106','经济法','必修','48','0','7'),
-> ('c08123','金融学','必修','40','0','5'),
-> ('c08171','会计软件','选修','32','8','8');
使用insert into| replace语句添加数据，说明：
（1）使用insert语句可以向表中插入一行数据，也可以插入多行数据，最好一次插入多行数据，各行数据之间用“，”分隔。
（2）values子句：包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若表名后不给出列名，则在values子句中要给出每一列（除identity和 timestamp类型的列）的值，如果列值为空，则值必须置为null，否则会出错。
（3）如果向表中添加已经存在的学号（已经设为主键主键）的记录，因此将出现主键冲突错误。例如，插入已经存在的学号19112111208记录，结果如下。
mysql> insert into student values('19112111208','韩小雨',
-> '女','2001/2/14','666','15878945612','han@163.com ');
ERROR 1062 (23000): Duplicate entry '19112111208' for key 'primary'
（4）replace into向表中插入数据时，首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断），则先删除此行数据，然后插入新的数据，否则，直接插入新数据。
（5）还可以向表中插入其他表的数据，这也是成批插入数据的一种方式。但要求两个表要有相同的结构。具体操作将在以后介绍。其语法格式如下，：
insert into table name1 select * from table name2;
2、利用load data语句将数据装入数据库表中
【例4.15】假设teacher表的数据已放在“d:\teacher.txt”中，现将teaching.txt的数据插入到teacher表中。
mysql>load data local infile "d:\\teacher.txt" into table teacher;
Query OK, 9 rows affected, 8 warnings (0.03 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from teacher; // 输出表的记录
说明：
（1）teacher.txt各行文本之间要用制表符`<Tab>`分隔，每行最后也加<Tab>分隔符。
（2）"d:\\teacher.txt":要用“\\”,表示斜线。
（3）以此类推，可以将score表和teach_course表的数据载入。
【例4.16】利用load data语句输入score表数据。
mysql>load data local infile “d:\\score.txt” into table score;
mysql> select * from score;
【例4.17】利用load data语句输入teach_course表数据。
mysql> load data local infile "d:\\teach_course.txt"
-> into table teach_course;
mysql> select * from teach_course;
3、使用set子句插入数据
用set子句直接赋值时可以不按列顺序插入数据，对允许空值的列可以不插入。
【例4.18】利用set子句向se_course表插入数据。
mysql> insert into se_course
-> set studentno=‘19120000111’,courseno=‘c01236’,teacherno=‘t01237’;
mysql> select * from se_course;
4、图片数据的插入
MySQL还支持图片的存储，图片一般可以以路径的形式来存储，即插入图片采用直接插入图片的存储路径。当然，也可以直接插入图片本身，只要用load_file( )函数即可。
【例4.19】参照student表结构创建student01表，添加一个能够存储图片字段，然后插入一行数据。照片路径为“d: \image\ picture.jpg”。
mysql> create table student01 as select * from student;
mysql> select * from student01;
mysql> alter table student01 add fields mediumblob comment‘照片’;
mysql> insert into student01 values
-> ('18122221329','何影映','女','2001/12/9','877','13178978997', 'heyy1@sina.com ', 'd:\\image\\picture.jpg');
下面语句是直接存储图片本身。
mysql> insert into student01 values('18122221329','何影映','女','2001/12/9','877', '13178978997','heyy1@sina.com ', load_file('d:\\image\\picture.jpg');
说明：
（1）存放图片的字段要使用blob类型。blob是专门存储二进制文件的类型，有大小之分，例如mediumblob、longblob等，以存储大小不同的二进制文件，一般的图形文件使用mediumblob就足够了。
（2）插入图片文件路径的办法要比插入图片本身好。图片如果很小的话，可以存入数据库，但是如果图片大的话，保存或读取操作会很慢，倒不如将图片存入指定的文件夹，然后把文件路径和文件名存入数据库。
4.2.3 修改表记录
1、UPDATE语句
UPDATE 表名 SET 列名=值[,列名=值,……] [WHERE <条件>]
【注意】
MySQL运行在SAFE_UPDATES模式下，该模式会导致非主键条件下无法执行UPDATEA或DELETE命令。需要执行命令 SET SQL_SAFE_UPDATES=0; 修改数据库模式。
【例4.20】将学号为18137221508学生的课程号为 c08106的平时成绩daily修改为80分。
mysql> update score set daily=80
-> where studentno=‘18137221508’ && courseno=‘c08106’;
mysql> select * from score
-> where studentno=‘18137221508’ && courseno=‘c08106’;
【例4.21】 将课程student01表中低于700分的入学成绩增加8%。
mysql> update student01 set entrance=entrance*1.08
-> where entrance<700;
4.2.4 删除表记录
利用delete… from…语句可以从单个表中删除指定表数据，一般表记录删除的语法格式如下：
delete[low_priority] [quick] [ignore] from tbl_name
[where子句]
[order by子句]
[limit row_count]
说明：
（1）quick修饰符：可以加快部分种类的删除操作的速度。
（2）from子句：用于指定从何处删除数据。
（3）where子句指定的删除条件。如果省略where子句则删除该表的所有行。
（4）order by子句：各行按照子句中指定的顺序进行删除，此子句只在与limit联用时才起作用。
（5）limit子句：用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。
（6）数据删除后将不能恢复，因此，在执行删除之前一定要对数据做好备份。
【例4.22】删除student01表中入学成绩低于750分记录。
mysql> delete from student01 where entrance <750;
Query OK, 2 rows affected (0.04 sec)
【例4.23】删除student01表中入学成绩最低的2行记录。
mysql> delete from student01 order by entrance limit 2;
Query OK, 2 rows affected (0.01 sec)
4.3 表的数据完整性
4.3.1 数据完整性
在定义表结构的同时，还可以定义与该表相关的完整性约束条件，包括实体完整性、参照完整性和用户自定义完整性。这些完整性约束条件都被存入系统的数据字典中，当用户操作表中的数据时，由数据库管理系统自动检查该操作是否违背这些完整性约束条件。
关系的完整性约束条件包括三大类：
- 实体完整性
- 参照完整性
- 用户定义的完整性
1．实体完整性规则
实体完整性给出了主键的取值的最低约束条件。
规则：主键的各个属性都不能为空值。
2．参照完整性
参照完整性给出了在关系之间建立正确的联系的约束条件。
定义：设F是关系R的一个或一组属性（但F不是R的主键），K是关系S的主键。如果F与K相对应，则称F是关系R的外键，并称关系R为参照关系，关系S为被参照关系。而关系R和关系S可以是同一个关系。
规则：外键或者取空值（要求外键的每个属性均为空值），或者等于被参照关系中的主键的某个值。
3．用户定义的完整性
规则：属性的取值应当满足用户定义的约束条件。
DBMS应该提供定义和检验这类完整性的机制（如约束Check、触发器Trigger等），以便用统一的方法来处理它们，而不应该由应用程序来承担这个功能。
如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，其他情况则既可以定义在列级上也可以定义在表级上。
这些约束条件主要包括not null（非空约束）、primary key（主键约束）、unique（唯一性约束）、foreign key（外键参照完整性约束）以及check（检查约束）。
学习创建和修改约束的方法，掌握数据约束条件的实际应用，对实现数据完整性起到不可或缺的作用。
4.3.2 非空约束
在前面的数据表定义过程中，每个字段都要有一个是否为null值的选择，这就是对数据表中将来的数据提出的约束条件。
- null（允许空值）：
表示数值未确定，并不是数字“0”或字符“空格”。表中的比较两个空值或空值与其他任何类型值比较的结果为空值。
- not null（不允许空值）：
表示数据列中不允许空值出现。这样可以确保数据列中必须包含有意义的值。对于数据列中设置“不允许空值”，在向表中输入数据时，就必须输入一个值，否则该行数据将不会被收入表中。
例如：学生选课时，学号、课程号就不能为空值，因为这必须是确定值，才能描述哪位同学选的什么课。如果存在成绩字段，则成绩字段就应该允许空值，因为此时还没有结束课程，成绩是不确定的。
设置表的非空约束是指在创建表时为表的某些特殊字段加上not null约束条件。非空约束将保证所有记录中该字段都有值。如果用户新插入的记录中，该字段为空值，则数据库系统会自动报错。
4.3.3 主键（PRIMARY KEY）约束
设置主键的主要是可以帮助MySQL以最快的速度查找到表中的指定信息。primary key可指定一个字段作为表主键，也可以指定2个及以上的字段作为复合主键，其值能唯一地标识表中的每一行记录，而且primary key约束中的列不允许取空值。
由于primary key约束能确保数据唯一，所以经常用来定义标志列。
主键约束主要是针对主键，以保证主键值的完整性。主键约束要求主键值必须满足两个条件：
-
值唯一；
-
不能为空值。
主键约束分列级和表级两种定义方式。列级针对表中一列，而表级则针对同一表中个一列或多列。
可以在创建表时创建主键，也可以对表中已有主键进行修改或者增加新的主键。
设置主键通常有两种方式：表的完整性约束和列的完整性约束。
1、创建表时定义完整性约束
若在列定义的时加上关键字primary key，就可以定义列的完整性约束主键。
【例4.24】创建表course01，用列的完整性约束设置主键。
mysql>create table if not exists course01
(courseno char(6) not null primary key,
cname char(6) not null,
type char(8) not null,
period int(2) not null,
exp int(2) not null,
term int(2) not null );
说明：
（1）主键可以是单一字段时，可以是表级约束，也可以是列级约束。
（2）当表中的主键为复合主键时，只能定义为表的完整性约束。例如，前面定义score表时，就是采用这种方式。
2、修改表的主键
【例4.25】修改表student02的主键，删除原来主键sname，增加studentno 为主键。
mysql> alter table student02 add primary key (sname);
mysql> alter table student02 drop primary key;
mysql> alter table student02 add primary key (studentno);
4.3.4 外键约束
1、理解参照完整性
在关系型数据库中，有很多规则是和表之间的关系有关的，表与表之间往往存在一种“父子”关系。
例如，字段studentno是一个表score表的属性，且依赖于表student的主键studentno。那么，称表student为父表，表score为子表。通常将studentno设为表score的外键，参照表student的主键字段，通过studentno字段将父表student和子表score建立关联关系。
外键的作用是建立子表与其父表的关联关系，保证子表与父表关联的数据一致性。父表中更新或删除某条信息时，子表中与之对应的信息也必须有相应的改变。
2、设置外键的原则
设置外键时必须依赖于数据库中已存在的父表的主键；外键可以为空值。
当需要在score表中添加、删除、修改studentno字段的数据时，其结果中的studentno值必须在student表存在。即编辑成绩表score中的学号studentno时，该学号必须是student表存在的学号。这种类型的关系就是参照完整性约束（referential integrity constraint）。
外键声明和参照完整性定义的语法格式如下：
constraint foreign_key_name foreign key (col_name1 [,col_name2….])
references table_name(col_name1[,col_name2...)])
[on delete {restrict | cascade | set null | no action}]
[on update {restrict | cascade | set null | no action}]
（1）constraint foreign_key_name:定义外键约束和约束名。foreign key (col_name1 [,col_name2….]，外键引用的字段表。
（2）外键被定义为表的完整性约束，reference_definition中包含了外键所参照的表和列，还可以声明参照动作。
（3）restrict：当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。
（4）cascade：从父表删除或更新行时自动删除或更新子表中匹配的行。
（5）set null：当从父表删除或更新行时，设置子表中与之对应的外键列为null。如果外键列没有指定not null限定词，这就是合法的。
（6）no action：no action意味着不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和restrict一样。
（7）set default：作用和set null一样，只不过set default是指定子表中的外键列为默认值。
3、对已有的表添加外键
【例4.26】 用alter table语句在数据库teaching中，为表score添加外键约束。
mysql>alter table score
-> add constraint fk_st_score
-> foreign key(studentno) references student(studentno);
mysql>alter table score
->add constraint fk_cou_score
->foreign key(courseno) references course(courseno);
4、在创建表时创建外键
【例4.27】在mysqltest数据库，参照score表创建score1表，其中studentno作为外键，参照student02表中的studentno字段。
mysql> create table if not exists score1
-> (studentno char(11) not null, courseno char(6) not null,
-> daily float(3,1) default 0, final float(3,1) default 0,
-> primary key (studentno , courseno),
-> foreign key(studentno)
-> references student02(studentno)
-> on update cascade
-> on delete cascade);
4.3.5 唯一性约束
唯一性是指所有记录中该字段的值不能重复出现。设置表的唯一性约束是指在创建表时为表的某些特殊字段加上unique约束条件。唯一性约束将保证所有记录中该字段的值不能重复出现。创建表时可以设置列的唯一约束，也可以在已经创建的表的列上添加唯一。例如，在Email字段加上唯一性约束，所以记录中Email字段上不能出现相同的值。
唯一约束主要是针对于候选键，以保证候选键值的完整性。唯一约束要求候选键满足2个条件：
-
值唯一；
-
可有一个且仅有一个空值。
唯一约束既可以在列级定义，也可以在表级定义。
【例4.29】在mysqltest数据库中，对student02表的Email列添加唯一约束。
mysql> alter table student02 add unique (Email);
mysql> select studentno,sname,email from student02;
mysql> insert into student02 values ('16104444444' , '徐赛克', '男' ,'2001-01-01', '809', '13102020207' , 'z3@162.com');
ERROR 1062 (23000): Duplicate entry 'z3@162.com' for key 'Email' //插入失败，违反唯一性约束的条件
说明：
（1）一个数据表只能创建一个主键，但可以有若干个unique约束。
（2）主键列值不允许为null，而unique字段的值可取null，但是必须使用null或not null声明。
（3）一般在创建primary key约束时，系统会自动产生primary key索引。创建unique约束时，系统自动产生unique索引。
4.3.6 检查约束
利用主键和外键约束可以实现一些常见的完整性操作。在进行数据完整性管理时，还需要一些针对数据表的列进行限制数值范围的约束。例如，score表中final字段的数值要在0～100之间，表中birthdate必须大于1990年12月31日。这样的规则可以使用check完整性约束来指定。
check约束在创建表时定义，可以定义为列完整性约束，也可以定义为表完整性约束。定义check约束时的格式比较简单。
【例4.28】在mysqltest数据库中，对student02表的birthdate列添加check约束，要求出生日期必须大于1999年12月31日，性别只能是“男”和“女”。
mysql> alter table student02
-> add constraint ch_stu_birth
-> check(birthdate>'1999-12-31');
4.3.7 总结
表是数据库存储数据的基本单位。数据操作是数据库管理中最基本、最重要的操作。完整性约束是保证数据正确性的重要方法。学习本章后要掌握如下重要内容：
- 创建表的方法。
- 表的完整性约束条件。
- 查看表结构的方法。
- 修改表的方法。
- 删除表的方法。
