数据完整性的概念
 
 
 
数据完整性是为了保证插入到数据库中的数据是正确的,防止用户可能的错误输入。
 
 
数据完整性分为实体完整性、域完整性、参照完整性。
 
实体(行)完整性
 
实体完整性中的实体指的是表中的行,因为一行记录对应一个实体。
 
实体完整性规定表的一行在表中是唯一的实体,不能出现重复。
 
实体完整性通过表的主键来实现。
 
主键关键字: primary key
 
主键特点: 不能为null，并且唯一。
 
主键分类:
 
​ 逻辑主键:例如ID,不代表实际的业务意义,只是用来唯一标识一条记录(推荐)
 
​ 业务主键:例如username，参与实际的业务逻辑。
 
主键使用方式:
 
​ 方式一:
 
Create table t1(

​	  id int primary key,

​	  name varchar(100)

);
 

 插入数据：
 
insert into t1 values(1,’zs’);

insert into t1 values(2,’ls’);
 
 
主键自动增长:
 
关键字: auto_increment
 
下面是主键自动增长的例子
 
​		create table t4(

​			id int primary key auto_increment,

​			name varchar(100)

);
 
 
插入数据：
 
insert into t4(name) values(‘zs’);

insert into t4 values(null,’ls’);
 
 
域(列)完整性
 
 
 
域(列)完整性指数据库表的列（即字段）必须符合某种特定的数据类型或约束
 
 
​ 非空约束：not null
 
​ 唯一约束： unique
 
create table t5(

​			username varchar(100) not   null  unique,

​			gender varchar(100) not   null,

​			phonenum varchar(100)  unique

​	);
 
 
username 唯一 ，如果有重复就会出现错误：
 

 [Err] 1062 - Duplicate entry 'ls' for key 'username' 
 
 
如果第二次插入代码insert into student3(username,gender,phone) VALUES('ls','男','112');
 
就会出现错误：[Err] 1062 -关键字“用户名”的重复条目“ls”
 
 
gender 不能为 NULL，如果不输入就会出现错误：
 
 
Field 'gender' doesn't have a default value  
 
执行下面这条代码insert into student3(username,gender,phone) values('ll','男','113');
 就会出现错误Duplicate entry 'll' for key 'username'
 
关键字“用户名”的重复条目“ll”
 

 
 
参照完整性**
 
参照完整性指的就是多表之间的设计,主要使用外键约束。
 
多表设计: 一对多、多对多、一对一设计
 
一对多
 
下面举例说明：
 1.客户和订单的关系就是一对多，一个客户可以有多张订单,一张订单属于一个客户;
 
 ​ 创建客户表:
 
create table  customers(

​	id int,

​	name varchar(100),

​	address varchar(255),

​	primary  key(id)
);

 
创建订单表:
 
create table orders(

​	order_num int primary key,

​	price float(8,2),

​	status int,

​	customer_id int,

constainct  customer_id_fk foreign  key(customer_id) references customers(id)

);

 
外键约束: constraint customer_id_fk foreign key(customer_id) references customers(id);
 
 
 
注: constraint: 约束的意思。foreign key: 外键。references: 参照
 
 
创建一个名叫customer_id_fk的外键约束,其中外键指的是customer_id,并且参照的是 customers表中的id列。
 
图形界面操作的过程是：
 
 
 
 
 
 
子表的删除、更新策略
 
子表的删除更新策略一共有四种：
 
1. CASCADE 级联策略。
 
使用此种策略时主表的记录被删除或者主键字段被修改时会同步删除或修改子表
 

 
 
 
2. NO ACTION 无动作策略。
 
使用此种策略时要删除主表必须先删除子表，要删除主表的记录必须先删除子表关联的记录，不能更新主表主键字段的值。
 
 
3. RSTRICT 主表约束策略。
 
此种策略对主表的约束跟 NO ACTION 一样
 
4. SET NO 置空策略。
 
使用此种策略时，如果主表被删除或者主键被更改，则将子表中的外键设置为NULL。需要注意的是，如果子表的外键是主键或者是设置为NOT NULL的，则主表的删除和主键的更改跟 NO ACTION 一样。
 
 
 
 
多对多
 
老师和学生是多对多关系, 一个老师对应多个学生，一个学生被多个老师教
 

 注意：
 
需要创建第三张表，并且公共的字段，可以放入到第三张表格中。
设置外键对应其他表格中的主键
 
创建老师表:
 
​	Create table teachers(

​		id int,

​		name varchar(100)

​		salary float(8,2),

​		primary key(id)

);
 
创建学生表:
 
Create table students(

​	id int,

​	name varchar(100),

​	grade varchar(100),

​	primary key(id)

);	
 
第三张表格：
 
Create table teacher_student(

​	t_id int,

​	s_id int,

​	primary key(t_id,s_id)

CONSTRAINT teacher_id_fk FOREIGN KEY(t_id) REFERENCES teachers(id),

CONSTRAINT student_id_fk FOREIGN KEY(s_id) REFERENCES students(id)

);

 

 
 
 
一对一
 
按照外键关联
 
​
 
1.在IdCard表中的外键添加唯一约束
 
 
按照主键关联，对主键添加外键约束
 
 
实际开发中直接设计一张表格就可以了。
 
注意：
 
一般情况，设计一张表格即可；
设计成两张表格：
 第二张表格中创建一个字段为外键（设置成唯一 unique）对应 第一张表格中的主键；
 
直接使用第二张表格中的主键为外键，对应第一张表格中的主键。

作用：保证用户输入的数据保存到数据库中是正确的。
 
确保数据的完整性 = 在创建表时给表中添加约束
 
完整性的分类：
 
 
实体完整性:
 
 
域完整性:
 
 
引用完整性:
 
4.1 实体完整性
 
实体：即表中的一行(一条记录)代表一个实体（entity）
 
实体完整性的作用：标识每一行数据不重复。
 
约束类型：
 
主键约束（primary key）
 
唯一约束(unique)
 
自动增长列(auto_increment)
 
4.1.1 主键约束（primary key）
 
​ 注：每个表中要有一个主键。
 
​ 特点：数据唯一，且不能为null
 
示例：
 
第一种添加方式：
 
CREATE TABLE student(
​
id int primary key,
​
name varchar(50)
​
);
 
第二种添加方式：此种方式优势在于，可以创建联合主键
 
CREATE TABLE student(
​
id int,
​
name varchar(50),
​
primary key(id)
​
);
 
CREATE TABLE student(
​
classid int,
​
stuid int,
​
name varchar(50),
​
primary key(classid，stuid)
​
);
 
第三种添加方式：
 
CREATE TABLE student(
​
id int,
​
name varchar(50)
​
);
​
ALTER TABLE student  ADD  PRIMARY KEY (id);
 
4.1.2 唯一约束(unique)
 
特点：数据不能重复。
 
CREATE TABLE student(
​
Id int primary key,
​
Name varchar(50) unique
​
);
 
4.1.3 自动增长列(auto_increment)
 
sqlserver数据库 (identity) oracle数据库( sequence)
 
给主键添加自动增长的数值，列只能是整数类型
 
CREATE TABLE student(
​
Id int primary key auto_increment,
​
Name varchar(50)
​
);
INSERT INTO student(name) values(‘tom’);
 
4.2 域完整性
 
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
 
域代表当前单元格
 
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
 
check约束（mysql不支持）check(sex='男' or sex='女')
 
4.2.1 数据类型:（数值类型、日期类型、字符串类型）
 
4.2.2 非空约束：not null
 
CREATE TABLE student(
​
Id int pirmary key,
​
Name varchar(50) not null,
​
Sex varchar(10)
​
);
​
INSERT INTO student values(1,’tom’,null);
 
4.2.3 默认值约束 default
 
CREATE TABLE student(
​
Id int pirmary key,
​
Name varchar(50) not null,
​
Sex varchar(10) default ‘男’
​
);
insert intostudent1 values(1,'tom','女');
​
insert intostudent1 values(2,'jerry',default);
 
4.3 引用完整性（参照完整性）
 
外键约束：FOREIGN KEY 
 
示例：
 
CREATE TABLE student(
​
sid int pirmary key,
​
name varchar(50) not null,
​
sex varchar(10) default ‘男’
​
);
 
create table score(
​
        id int,
​
        score int,
​
        sid int , -- 外键列的数据类型一定要与主键的类型一致
​
        CONSTRAINT fk_score_sid foreign key(sid) references student(id)
​
);
 
第二种添加外键方式。
 
ALTER TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);
 
 
 
第二种解释，数据完整性分为以下3类。
 

（1）域完整性：是指一个列的输入有效性，是否允许为空值。强制域完整性的方法有：限制类型（通过设定列的数据类型）、格式（通过CHECK约束和规则）或可能值的范围（通过FOREIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL定义和规则）。如：学生的考试成绩必须在0～100之间，性别只能是“男”或“女”。
 

（2）实体完整性：是指保证表中所有的行唯一。实体完整性要求表中的所有行都有一个唯一标识符。这个唯一标识符可能是一列，也可能是几列的组合，称为主键。也就是说，表中的主键在所有行上必须取唯一值。强制实体完整性的方法有：索引、UNIQUE约束、PRIMARY KEY约束或IDENTITY属性。如：student表中sno（学号）的取值必须唯一，它唯一标识了相应记录所代表的学生，学号重复是非法的。学生的姓名不能作为主键，因为完全可能存在两个学生同名同姓的情况。
 

（3）参照完整性：是指保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。它涉及两个或两个以上表数据的一致性维护。外键值将引用表中包含此外键的记录和被引用表中主键与外键相匹配的记录关联起来。在输入、更改或删除记录时，参照完整性保持表之间已定义的关系，确保键值在所有表中一致。这样的一致性要求确保不会引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。参照完整性是基于外键与主键之间的关系。例如学生学习课程的课程号必须是有效的课程号，score表（成绩表）的外键cno（课程号）将参考course表（课程表）中主键cno（课程号）以实现数据完整性。
 域完整性、实体完整性及参照完整性分别在列、行、表上实施。数据完整性任何时候都可以实施，但对已有数据的表实施数据完整性时，系统要先检查表中的数据是否满足所实施的完整性，只有表中的数据满足了所实施的完整性，数据完整性才能实施成功。
 
 

数据完整性： 为保证插入到数据库中的数据是正确的，防止用户输入错误的数据 分为实体完整性、 域完整性 、参照完整性 （下节再说）
 （1）实体完整性：
 实体指的是表中的一行，一行记录对应一个实体 ，通过主键实现
 主键：关键字（primary key）；特点：不能为null，并且唯一。
 逻辑主键（推荐）：例如ID,不代表实际的业务意义,只是用来唯一标识一条记录(推荐)
 
	eg: create table person(
	id int primary key auto_increment,
	name	varchar(20) not null,
	age int);
 
业务主键：例如username，参与实际的业务逻辑。
 
eg: create table goods(
goods_id 	varchar(20) primary key,
goods_name 	varchar(20) not null);
 
（2）域（列）完整性：
 必须符合某种数据类型或约束。
 1）数据类型包括（长度等）；
 2）约束包括（ 非空约束 not null，唯一约束 unique）。
 
eg:	 create table student(
id int primary key auto_increment,
name varchar(20) not null,
idcard varchar(20) unique);

域完整性约束的作用：限制此单元格的数据正确，不对其它单元格起作用，域代表当前单元格
 
域完整性约束：数据类型、非空约束（not null）、默认值约束(default)
 
check约束（mysql不支持）check(sex='男’or sex=‘女’)
 
数据类型
 
数值类型
 
类型
大小
范围（有符号）
范围（无符号）
用途
TINYINT
1 字节
(-128，127)
(0，255)
小整数值
SMALLINT
2 字节
(-32 768，32 767)
(0，65 535)
大整数值
MEDIUMINT
3 字节
(-8 388 608，8 388 607)
(0，16 777 215)
大整数值
INT或INTEGER
4 字节
(-2 147 483 648，2 147 483 647)
(0，4 294 967 295)
大整数值
BIGINT
8 字节
(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)
(0，18 446 744 073 709 551 615)
极大整数值
FLOAT
4 字节
查看帮助文档
查看帮助文档
单精度浮点数值
DOUBLE
8 字节
查看帮助文档
查看帮助文档
双精度浮点数值
DOUBLE(M,D)
8个字节，M表示长度，D表示小数位数
同上，受M和D的约束 DUBLE(5,2) -999.99-999.99
同上，受M和D的约束
双精度浮点数值
DECIMAL(M,D)
对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
依赖于M和D的值，M最大值为65
依赖于M和D的值，M最大值为65
小数值
日期类型:
 
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
 
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
 
TIMESTAMP类型有专有的自动更新特性
 
类型
大小(字节)
范围
格式
用途
DATE
3
1000-01-01/9999-12-31
YYYY-MM-DD
日期值
TIME
3
‘-838:59:59’/‘838:59:59’
HH:MM:SS
时间值或持续时间
YEAR
1
1901/2155
YYYY
年份值
DATETIME
8
1000-01-01 00:00:00/9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
混合日期和时间值
TIMESTAMP
4
1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS
混合日期和时间值，时间戳
字符串类型:
 
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
 
类型
大小
用途
CHAR
0-255字符
定长字符串 char(10) 10个字符
VARCHAR
0-65535 字节
变长字符串 varchar(10) 10个字符
TINYBLOB
0-255字节
不超过 255 个字符的二进制字符串
TINYTEXT
0-255字节
短文本字符串
BLOB（binary large object）
0-65 535字节
二进制形式的长文本数据
TEXT
0-65 535字节
长文本数据
MEDIUMBLOB
0-16 777 215字节
二进制形式的中等长度文本数据
MEDIUMTEXT
0-16 777 215字节
中等长度文本数据
LONGBLOB
0-4 294 967 295字节
二进制形式的极大文本数据
LONGTEXT
0-4 294 967 295字节
极大文本数据
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
 
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
 
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
 
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
 
非空约束
 
not null
 
CREATE TABLE student(
	Id int primary key,
	Name varchar(50) not null,
	Sex varchar(10)
);
INSERT INTO student values(1,’tom’,null);
 
默认值约束
 
default
 
CREATE TABLE student(
	Id int primary key,
	Name varchar(50) not null,
	Sex varchar(10) default '男'
);
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default);
 
引用完整性约束
 
（参照完整性约束）
 
外键约束：FOREIGN KEY 
 
示例：
 
第一种添加外键方式。推荐
 
#学生表(主表)
CREATE TABLE student(
	sid int primary key,
	name varchar(50) not null,
	sex varchar(10) default '男'
);
 
#成绩表（从表）
create table score(
        id int,
        score int,
        sid int , 
        CONSTRAINT fk_score_sid foreign key(sid) references student(sid)
);
-- 外键列的数据类型一定要与主键的类型一致
 
第二种添加外键方式。
 
ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES student(sid);