MySQL约束、多表查询和事物的四个特性

MySQL约束、多表查询和事物的四个特性

第一节 数据完整性

数据完整性是指数据的精确性(Accuracy) 和可靠性(Reliability)。

确保数据的完整性 = 在创建表时给表中添加约束，保证用户输入的数据保存到数据库中是准确可靠的。

完整性的分类：

实体完整性：主键约束，唯一主键

实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人。

域完整性：字段属性约束，保证有效

域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.

引用完整性 （参照完整性）：外键约束

参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考完整性

1.1 实体完整性

实体：即表中的一行(一条记录)代表一个实体（Entity），实体在Java中用对象表示。

实体完整性的作用：标识每一行数据不重复。

约束类型：

主键约束（primary key）

唯一约束(unique)

自动增长列(auto_increment)

1.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);

1.1.2 唯一约束(unique)

特点：数据不能重复。可以为null

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

1.1.3 自动增长列(auto_increment)

sql server数据库 (identity) oracle数据库 ( sequence)

自动增长不能单独使用，一般需要和主键配合。

给主键添加自动增长的列只能是数值类型。

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

1.2 域完整性

域完整性约束的作用：限制此单元格的数据正确，不对其它单元格起作用，域代表当前单元格

域完整性约束：数据类型、非空约束（not null）、默认值约束(default)

check约束（mysql 5.5不支持）check(sex='男’or sex=‘女’)

1.2.1 数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
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。

1.2.2 非空约束

NOT NULL

CREATE TABLE student(
	Id int primary key,
	Name varchar(50) not null,
	Sex varchar(10)
);
INSERT INTO student values(1,’tom’,null);

1.2.3 默认值约束

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);

1.3 引用完整性

引用完整性使用外键约束来实现

外键约束：FOREIGN KEY 

示例：

第一种添加外键方式。推荐

#学生表(主表)
CREATE TABLE student(
	sid int primary