数据的完整性（约束）、多表查询、连接查询

一、数据的完整性

1.1、定义

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

1.2、数据完整性的分类

实体完整性

域完整性

引用完整性

1.3、实体完整性

实体：表中一行（一条记录）代表一个实体。
实体完整性的作用：标识每一行数据不重复。
保证实体完整性的一种常用的方式是添加约束

1.4约束

1.4.1 主键约束概念
注：每个表中要有一个主键。
主键约束（primary key）是唯一约束(unique)且为非空约束可以设置为自动增长列(auto_increment)
1.4.2主键添加方式

方式一

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

删除主键约束后~~~~~~~~~~
注意: 现代项目中的主键基本上使用 bigint类型的自增
1.4.3 唯一约束（unique）

特点：数据不能重复。

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

1.4.4 自动增长例（autp_increment）

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

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

1.5域完整性

域代表当前单元格
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较。
域完整性约束：数据类型非空约束（not null）默认值约束(default)
check约束（mysql不支持）check(sex='男'or sex='女')。
1.5.1非空约束：not null

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

1.5.2 默认值约束 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);

1.6 引用完整性（参照完整型）

外键约束：foreign key
1.6.2条件外键的方式
方式一

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 TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

二、多表查询

2.1 多表的关系

2.1.1 一对多关系

例如：客户和订单，分类和商品，部门和员工.

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

2.1.2 多对多关系

例如：学生和课程:

多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

2.1.3 一对一关系

在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：

唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.

主键对应：让一对一的双方的主键进行建立关系.

2.2、多表查询

合并结果集；UNION 、 UNION ALL

作用：合并结果集就是把两个select语句的查询结果合并到一起！

合并结果集有两种方式：
l UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
l UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
注意：被合并的两个结果：列数、列类型必须相同

连接查询
连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
CREATE TABLE emp(
empno     INT,
ename     VARCHAR(50),
job       VARCHAR(50),
mgr       INT,
hiredate  DATE,
sal       DECIMAL(7,2),
comm     decimal(7,2),
deptno        INT
) ;
#添加数据SQL语句省略
CREATE TABLE dept(
deptno     INT,
dname     varchar(14),
loc       varchar(13)
);
#添加数据SQL语句省略
执行如下SQL语句
select * from emp,dept;
使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

内连接 [INNER] JOIN ON
内连接的特点：查询结果必须满足条件。
SQL标准的内连接为:
SELECT * FROM emp e INNER JOIN dept d ON  >e.deptno=d.deptno;

外连接 OUTER JOIN ON
包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

左外连接 LEFT [OUTER] JOIN
左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
右外连接 RIGHT [OUTER] JOIN
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

全外连接（MySQL不支持）FULL JOIN

自然连接 NATURAL JOIN

连接查询心得：
连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

子查询
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置：
a. where后，作为条为被查询的一条件的一部分；
b. from后，作表；

当子查询出现在where后作为条件时，还可以使用如下关键字： any all

子查询结果集的形式：
a. 单行单列（用于条件）
b. 单行多列（用于条件）
c. 多行单列（用于条件）
d. 多行多列（用于表）