数据库高级一———数据库完整性

数据库的完整性

数据库的完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：

• 实体完整性(行完整性):
• 域完整性(列完整性):
• 引用完整性(关联表完整性):
  主键约束：primary key
  唯一约束：unique [key]
  非空约束：not null
  默认约束：default
  自动增长：auto_increment
  外键约束: foreign key
  建议这些约束应该在创建表的时候设置
  多个约束条件之间使用空格间隔

举例：

create table student(
 		studentno int primary key auto_increment,
 	    loginPwd varchar(20) not null default '123456', 
 		studentname varchar(50) not null, 
 		sex char(2) not null, gradeid int not null, 
 	    phone varchar(255) not null, 
 		address varchar(255) default '学生宿舍', 
 		borndate datetime, email varchar(50)
 );

实体完整性

实体：即表中的一行（一条记录）代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型：

主键约束（primary key）

一、

CREATE TABLE student( id int primary key, name varchar(50) );

二、

CREATE TABLE student( id int, name varchar(50), primary key(id) );

唯一约束（unique）

特点：数据不能重复

CREATE TABLE student( Id int primary key, Name varchar(50) unique );

自动增长列（auto_increment）

sqlserver数据库 (identity-标识列)
oracle数据库(sequence-序列)
给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) ); INSERT INTO student(name) values(‘tom’);

域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（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	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 744073 709 551 615)	极大整数值
float	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
double	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值

日期类型:
表示时间值的日期和时间类型为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字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	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。这些对应4种BLOB类型，有相同的
最大长度和存储需求。

非空约束

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(
   id int primary key, 
   name varchar(50) not null, 
   sex varchar(10) default '男' 
   ); 
   
create table score( 
id int primary key, 
score int,
sid int ,
 constraint fk_score_sid foreign key(sid) references student(id) );

constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)

外键列的数据类型一定要与主键的类型一致

第二种添加方式：

ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);