数据库高级数据库完整性多表查询

---

数据库高级

---

一、 数据完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类：

• 实体完整性(行完整性):
• 域完整性(列完整性):
• 引用完整性(关联表完整性):

主键约束：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) 
);

1.实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）

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

约束类型：

主键约束（primary key）

唯一约束 (unique)

自动增长列 (auto_increment)

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.2 唯一约束(unique)

特点：数据不能重复。

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

1.3 自动增长列(auto_increment)

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

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

2.域完整性

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

2.1非空约束

not null

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

2.2默认值约束

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

3.引用完整性

（参照完整性）
外键约束：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 TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

二、多表查询

多个表之间是有关系的，那么关系靠谁来维护?
多表约束：外键列

1.多表的关系

1.1 一对多/多对一关系

客户和订单，分类和商品，部门和员工.
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

1.2 多对多关系

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

1.3 一对一关系

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

两种建表原则：

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

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

2.多表查询

多表查询有如下几种：

1. 合并结果集:UNION 、 UNION ALL

2. 连接查询

   2.1内连接 [INNER] JOIN ON

   2.2外连接 OUTER JOIN ON

   左外连接 LEFT [OUTER] JOIN
   
   右外连接 RIGHT [OUTER] JOIN
   
   全外连接（MySQL不支持）FULL JOIN
   

   2.3自然连接 NATURAL JOIN

3. 子查询

2.1合并结果集UNION 、 UNION ALL

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

合并结果集有两种方式：

l UNION：去除重复记录
例如：

SELECT * FROM t1 UNION SELECT  *  FROM t2；

l UNION ALL：不去除重复记录
例如：

SELECT * FROM t1 UNION ALL SELECT * FROM t2；

注意：被合并的两个结果：列数、列类型必须相同。

2.2连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录，当然是通过

条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

使用主外键关系做为条件来去除无用信息

2.3内连接

语法：

select 
	列名 
from 
	表1 
inner join 表2 on 表1.列名=表2.列名 
where ...
//外键列的关系 where.....

等价于：

select 
	列名 
from 
	表1,表2 
where 
	表1.列名=表2.列名 and ...(其他条件)

注:

1. 表1和表2的顺序可以互换
2. 找两张表的等值关系时,找表示相同含义的列作为等值关系。
3. "."点操作符表示“的”,格式: 表名.列名
4. 可以使用as,给表名起别名,注意定义别名之后,统一使用别名

示例：

//查询学生表中的学生姓名和分数表中的分数 

select 
	name,score 
from 
	student as s 
inner join scores as c on s.studentid=c.stuid 

等价于： 

select 
	name,score 
from 
	student as s,scores as c 
where 
	s.studentid=c.stuid

三表联查：

语法：
select 列名 from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1或表2.列名=表3.列名
where

等价于:
select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

SQL标准的内连接为：

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。

2.4 外连接

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

外联查询

左外联:

select 
	列名 
from 
	主表 
left join 次表 on 主表.列名=次表.列名

右外联:

select 
	列名 
from 
	次表 
right join 主表 on 主表.列名=次表.列名

左外连接:

SELECT 
	列名 
FROM 
	主表 
LEFT OUTER JOIN 次表 ON 主表.列名=次表.列名

右外连接：

SELECT 
	列名 
FROM 
	次表 
RIGHT OUTER JOIN 主表 ON 主表.列名=次表.列名

• 左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
• 右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null

2.主表和次表不能随意调换位置

使用场景:一般会作为子查询的语句使用

连接查询心得：

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

2.5 自然连接

自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。.

2.6子查询

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

子查询出现的位置：

• where后，作为条为被查询的一条件的一部分；
• from后，作表；

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

• any
• all

子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

示例：

SELECT 
	* 
FROM 
	emp 
WHERE 
	sal > (SELECT sal FROM emp WHERE ename='JONES');

三、扩展

1.多行新增

insert into 表名(列名) values (列值),(列值),(列值)；

2.多表更新

(1)

update 
	表1,表2 
set 
	列名=列值 
where 
	表1.列名=表2.列名 and 其他限定条件

(2)

update 
	表1
inner join 表2 on 表1.列名=表2.列名
set 
	列名=列值
where 
	限定条件

3.多表删除

语法：

delete 
	被删除数据的表 
from 
	删除操作中使用的表
where 
 	限定条件

注:多张表之间使用逗号间隔

示例：

delete 
	d,e,s 
from 
	department d,employee e,salary s
where 
 	d.depid=e.depid and s.empid=e.empid and depname='人事部

4.日期运算函数

now() 获得当前系统时间

year(日期值) 获得日期值中的年份

date_add(日期,interval 计算值 计算的字段);

注：计算值大于0表示往后推日期，小于0表示往前推日期

示例：

 date_add(now(),interval -40 year);//40年前的日期

四、数据简单优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10 union all select id from t where Name = 'admin'

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and **3**

很多时候用 exists 代替 in 是一个好的选择