多表关系设计及多表查询

一、多表关系设计

1.介绍

表与表之间的3种关系	举例
一对多关系(最常见)	员工表和部门表 学生表和班级表
多对多关系	学生表和课程表 用户表和角色表
一对一关系(使用较少)	一对一的关系可以设计成一张表

2.一对多关系(常见）

• 一对多关系(1 : n) 例如: 班级和学生, 部门和员工, 客户和订单, 类别和商品

• 一对多建表原则 在从表(多方)创建一个字段, 该字段作为外键指向主表的主键

• 

3.多对多关系

 

• 多对多关系(m : n) 例如: 老师和学生, 学生和课程, 用户和角色。

• 多对多关系建表原则 多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多。

• 

4.一对一关系

在实际的开发过程中应用不多, 因为一对一的关系可以设计为一张表。

 

 二、多表查询

1.什么是多表查询

• DQL: 查询多张表, 获取到需要的数据

• 比如: 我们要查询家电分类下都有哪些商品, 那么我们就需要查询分类与商品两张表

2.笛卡尔积

交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。

笛卡尔积不管是否匹配，都连接。没有实际意义，有理论意义 。

笛卡尔积便于理解连接查询的原理。

语法格式：

select 字段名 from 表1 cross join 表2;

3.多表查询的分类

1.内连接查询（等值连接）

• 通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上就不显示

• 如: 按照从表的外键 = 主表的主键进行匹配 | inner join on, inner可以省略

1.SQL92隐式内连接

from子句后面直接写多个表名 使用where指定连接条件的 这种连接方式是隐式内连接. 使用where条件过滤无用的数据。

语法格式：

select 字段名 from 表1,表2 where 连接条件;

2.SQL99显示内连接

 使用 inner join ...on 这种方式, 就是显式内连接。

-- 语法格式：
select 字段名 from 表1 [inner] join 右表 on 条件;

注意事项:

• 内连接只会显示匹配的数据

• 内连接不能实现不匹配数据的显示

2.自连查询

自连接就是某张表自己和自己关联。

比如员工表，它可以认为是员工表，也可以认为是领导表。

所以我们可以通过关联，查询员工的信息及他的领导的信息。

3.非等值连接

非等值连接就是两张表进行关联的时候不是使用关联的id相等进行连接的。

-- 创建工资等级表
create table salgrade(
	grade char(1) primary key comment '等级',
	losal double comment '此等级的最低工资',
	hisal double comment '此等级的最高工资'
);

-- 插入数据
INSERT INTO `salgrade` VALUES ('A', 0, 3000);
INSERT INTO `salgrade` VALUES ('B', 3001, 8000);
INSERT INTO `salgrade` VALUES ('C', 8001, 10000);
INSERT INTO `salgrade` VALUES ('D', 10001, 50000);

-- 查询员工的工资和工资级别
select sal, grade from emp e, salgrade s where sal between s.losal and s.hisal;

4.外连接查询

通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上也可以显示。

1.左外连接

 

使用 left outer join , outer 可以省略。

语法格式：

select 字段名 from 表1 left join 表2 on 条件;

左外连接的特点:

1. 以左表为主, 左表中的数据全部显示

2. 右表匹配到数据就显示匹配到的数据

3. 右表没有匹配的数据显示为null

2.右外连接

 

使用 right outer join, outer可以省略。

语法格式：

select 字段名 from 表1 right join 表2 on 条件;

右外连接的特点:

1. 以右表为主, 右表中的数据全部显示

2. 左表匹配到数据就显示匹配到的数据

3. 左表没有匹配的数据显示为null

3.全外连接

 

注意： MySQL 中不支持 FULL OUTER JOIN 连接

可以使用 union 实现全完连接。

• UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。

• UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。

-- 语法结构
(SELECT  列名 FROM 表名 LEFT OUTER JOIN  表名 ON  连接条件) 
UNION 
(SELECT  列名 FROM 表名 RIGHT OUTER JOIN  表名 ON  连接条件)

 5.内连接和外连接总结

 

• 内连接: inner join: 只获取两张表中 交集部分的数据。

• 左外连接: left join: 以左表为基准, 查询左表的所有数据, 以及与右表有交集的部分 。

• 右外连接: right join: 以右表为基准, 查询右表的所有的数据, 以及与左表有交集的部分。