数据完整性
如何添加数据完整性
- 在创建表的时候给表添加约束
完整性分类
- 实体完整性
- 域完整性
- 引用完整性
实体完整性
- 什么是实体完整性
- 表中的一行(一条记录)代表一个实体(entity)
- 实体完整性的作用
- 标识(标记)每一行数据不重复.行级约束
3. 约束类型 - 主键约束(primary key)
- 唯一约束(unique)
- 自动增长(auto_increment)
3.1 主键约束
特点:
- (创建)每个表中要有一个主键
- 数据唯一,且不能为null
添加方式:
- 方式1:
CREATE TABLE 表名(字段名1 数据类型 primary key,字段名2 数据类型);
举例:
创建person表
CREATE TABLE person(ID BIGINT primary key,NAME VARCHAR(50))
- 方式2:
CREATE TABLE 表名(字段1 数据类型,字段名2 数据类型,primary key(要设置主键的字段));
举例:
CREATE TABLE person(
ID BIGINT,
name VARCHAR(50),
age INT,
primary key(ID)
);
联合主键：两个字段数据同时相同时,才违反联合主键约束
CREATE TABLE 表名(字段1 数据类型,字段2 数据类型,primary key(主键1,主键2));
CREATE TABLE student(
ID BIGINT,
snum BIGINT,
name VARCHAR(50),
primary key(ID,snum))
先创建表再添加主键
CREATE TABLE student(sid INT,name VARCHAR(50));
**如果表中你之前已经添加了数据,数据不符合主键规则就会报错
ALTER TABLE student ADD CONSTRAINT primary key(sid);
唯一约束
特点
- 指定列的数据不能重复
- 可以为空值
格式:
CREATE TABLE students(
id INT primary key,
name VARCHAR(50) UNIQUE
)
自动增长列
特点
- 指定列的数据自动增长
- 即使数据删除,还是从删除的序号继续往下
- 一般我们会给主键设置为自动增长列
格式:
CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE);
举例:
CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE)
);
域完整性
- 域完整性
数据类型
- 数值类型
- 日期类型
- 字符串类型
非空约束(NOT NULL)
CREATE TABLE stu(
id int primary key auto_increment,
name varchar(20) unique not null,
gender char(1) default '男'
);
默认值约束
(DEFAULT)CREATE TABLE 表名(字段名1 数据类型 primary key AUTO_INCREMENT,字段2 数据类型 UNIQUE NOT NULL,字段3 数据类型 DEFAULT '默认值');
举例:
CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE NOT NULL,
gender CHAR(1) DEFAULT '男'
);
参照完整性介绍
- 什么是参照完整性
- 是指表与表之间的一种对应关系
- 通常情况下可以通过设置两表之间的主键、外键关系,或者编写两表的触发器来实现
- 有对应参照完整性的两张表格,再对他们进行数据插入,更新,删除的过程中,系统都会将被修改表格与另外一张对应表格进行对照,从而阻止一些不正确的数据的操作
- 数据库的主键和外键类型一定要一致
- 两个表必须要是InnoDB引擎
- 设置参照完整性后,外键当中的内值,必须得是主键当中的内容
- 一个表设置当中的字段设置为主键,设置主键的为主表
- 创建表时,设置外键,设置外键的为子表
参照完整性添加
1. 通过一张表里面的某个字段,关联另外一张表
1. 对已有表添加外键关系
ALTER TABLE score ADD CONSTRAINT sc_st_fk FOREIGN KEY(sid) REFERENCES student(id);
表之间关系[一对一,一对多,多对多]
1. 一对一
- 一夫一妻(开发中使用比较少)
2. 一对多关系
- 一个人可以拥有多辆汽车,要求查询某个人拥有的所有车辆
- 创建Person表
CREATE TABLE person(
p_id INT PRIMARY KEY AUTO_INCREMENT,
p_name VARCHAR(50),
p_age INT,
p_sex CHAR(1)
);
- 创建Car表
CREATE TABLE car(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(50),
c_color VARCHAR(25),
p_id INT,
CONSTRAINT fk_Person FOREIGE KEY(c_id) REFERENCES person(p_id)
);
3. 多对多关系
- 例如:学生选课,一个学生可以选修多门课程,每门课程可以供多个学生选择
- 再例如: 老师和学生的多对多关系(一个老师有多个学生,一个学生有多个老师)
- 创建老师表
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(50),
t_age INT,
t_genger CHAR(1) DEFAULT '男'
);
- 创建学生表
CREATE TABLE students(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(50) NOT NULL,
s_age INT,
s_gender CHAR(1) DEFAULT '男'
);
- 中间表(创建学生与老师 关系表)
CREATE TABLE tea_stu_rel(
t_id INT,
s_id INT
);
- 添加外键
ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_tid
FOREIGN KEY(t_id)
REFERENCES teacher(t_id);
ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_sid
FOREIGN KEY(sid)
REFERENCES students(sid);
合并结果集UNION与UNION_ALL
1. 什么是合并结果集
- 合并结果集就是把两个SELECT语句的查询结果合并到一起展示
2. 合并结果集的两种方式
1.UNION
- 合并时去除重复记录
2.UNION ALL
- 合并时不去除重复记录
3. 格式
SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
4. 示例
- 创建表
CREATE TABLE A(name VARCHAR(10),score INT);
CREATE TABLE B(name VARCHAR(10),score INT);
--
INSERT INTO A VALUES('a',10),('b',20),('c',30);
INSERT INTO A VALUES('a',10),('b',20),('c',40);
UNION
SELECT * FROM AUNIONSELECT * FROM B;
UNION ALL
SELECT * FROM AUNION ALLSELECT * FROM B;
注意事项
- 被合并的两个结果集:列数,列类型必须相同
