数据库的完整性
什么是数据的完整性
保证用户输入的数据保存到数据库中是正确的。
如何添加数据完整性
在创建表时给表中添加约束
完整性分类
实体完整性
域完整性
参照完整性
实体完整性
什么是实体完整性
表中的一行(一条记录)代表一个实体(entity)
实体完整性的作用
标识每一行数据不重复。行级约束
约束类型
主键约束(primary key)
唯一约束(unique)
自动增长列(auto_increment)
主键约束
特点:
每个表中要有一个主键
数据唯一,且不能为null
添加主键约束的方式
CREATE TABLE 表名(字段名1 数据类型 primary key,字段2 数据类型);
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(要设置主键的字段));
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(主键1,主键2));
联合主键: 两个字段数据同时相同时,才违反联合主键约束。
1.先创建表
2.再去修改表,添加主键
ALTER TABLE student ADD CONSTRAINT PRIMARY KEY (id);
唯一约束:
特点:
指定列的数据不能重复
可以为空值
CREATE TABLE 表名(字段名1 数据类型 字段2 数据类型 UNIQUE);
自动增长列
特点:
指定列的数据自动增长
即使数据删除,还是从删除的序号继续往下
CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ,字段2 数据类型 UNIQUE);
域完整性
限制此单元格的数据正确,不对照此列的其它单元格比较
域代表当前单元格
域完整性约束:
数据类型 :
数值类型、日期类型、字符串类型
非空约束(not null)
CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ,字段2 数据类型 UNIQUE not null);
默认值约束(default)
CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ,字段2 数据类型 UNIQUE not null default '男');
插入的时候,values当中的值直接给default
参照完整性
什么是参照完整性
是指表与表之间的一种对应关系
通常情况下可以通过设置两表之间的主键、外键关系,或者编写两表的触发器来实现。
有对应参照完整性的两张表格,在对他们进行数据插入、更新、删除的过程中,系统都会将被修改表格与另一张对应表格进行对照,从而阻止一些不正确的数据的操作。
数据库的主键和外键类型一定要一致;
两个表必须得要是InnoDB类型
设置参照完整性后 ,外键当中的内值,必须得是主键当中的内容
一个表设置当中的字段设置为主键,设置主键的为主表
CREATE TABLE student(sid int PRIMARY key,name varchar(50) not null,sex varchar(10) default '男');
创建表时,设置外键,设置外键的为子表
CREATE TABLE score(
sid INT,
score DOUBLE,
CONSTRAINT fk_stu_score_sid FOREIGN KEY(sid) REFERENCES student(id));
表之间关系
一对一
一夫一妻
一对多关系
一个人可以拥有多辆汽车,要求查询某个人拥有的所有车辆。
创建Person表
创建Car表
多对多关系
学生选课,一个学生可以选修多门课程,每门课程可供多个学生选择。
一个学生可以有多个老师,而一个老师也可以有多个学生
创建老师表
创建学生表
创建学生与老师关系表
关系图
添加外键
多表操作
合并结果集
什么是合并结果集
合并结果集就是把两个select语句的查询结果合并到一起
合并结果集的两种方式
UNION
合并时去除重复记录
UNION ALL
合并时不去除重复记录
格式:
UNION:
SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
创建表:
UNION:
UNION ALL:
注意事项:被合并的两个结果:列数、列类型必须相同。
多表联查:
什么是连接查询
也可以叫跨表查询,需要关联多个表进行查询
什么是笛卡尔集
假设集合A={a,b},集合B={0,1,2},
则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
可以扩展到多个集合的情况
同时查询两个表,出现的就是笛卡尔集结果
查询时给表起别名
多表联查,如何保证数据正确
逐行判断,相等的留下,不相等的全不要
连接查询
1. 内连接
内连接
图示:
作用:查询两张表的共有部分
语句:
Select from tableA A Inner join tableB B on A.Key = B.Key
示例:
SELECT * from employee e INNER JOIN department d on e.depart_id = d.id;
多表连接:
建表:
使用99连接法:
使用内联查询
2. 左连接
图示
作用:把左边表的内容全部查出,右边表只查出满足条件的记录
语句:
Select from tableA A Left Join tableB B on A.Key = B.Key
示例
SELECT * from employee e LEFT JOIN department d on e.depart_id = d.id;
3. 右连接
图示:
作用
把右边表的内容全部查出,左边表只查出满足条件的记录
语句
Select from tableA A Right Join tableB B on A.Key = B.Key
示例
SELECT * from employee e RIGHT JOIN department d on e.depart_id = d.id;
子查询
什么是子查询
一个select语句中包含另一个完整的select语句。 或两个以上SELECT,那么就是子查询语句了。
子查询出现的位置
where后,把select查询出的结果当作另一个select的条件值
from后,把查询出的结果当作一个新表;
示例表
使用
查询与项羽同一个部门人员工
先查出项羽所在的部门编号
再根据编号查同一部门的员工
把第1条查出来的结果当第2天语句的条件
查询工资高于程咬金的员工
查出程咬金的工资
再去根据查出的结果查询出大于该值的记录员工名称
工资高于30号部门所有人的员工信息
先查出30号部门工资最高的那个人
再到整个表中查询大于30号部门工资最高的那个人
查询工作和工资与妲己完全相同的员工信息
先查出妲已的工作和工资
根据查询结果当作条件再去查询工作和工资相同的员工
由于是两个条件,使用 IN进行判断
有2个以上直接下属的员工信息
对所有的上级编号进行分组
找出大于2个的,大于2个说明有两个下属
把上条的结果当作员工编号时行查询
查询员工编号为7788的员工名称、员工工资、部门名称、部门地址