07【连接查询】

---

07【连接查询】

7.1 表的关系简介

现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！

7.1.1 一对多

一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品

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

7.1.2 多对多

多对多（m:n） 例如：老师和学生，学生和课程，用户和角色

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

• SQL实现代码：

-- 学生表
create table s1(
	id int primary key,
    name varchar(30)
);

insert into s1 values(1,'小明');
insert into s1 values(2,'小龙');
insert into s1 values(3,'小红');

-- 课程表
create table course(
	id int primary key,
    name varchar(30)
);

insert into course values(1,'Java');
insert into course values(2,'C++');
insert into course values(3,'MySQL');

-- 学生选课表(中间表)
create table stu_course(
	s_id int,
    c_id int,
    foreign key(s_id)  references s1(id),
    foreign key(c_id)  references course(id)
);

-- 代表小明选修了Java课程
insert into stu_course values(1,1);

-- 代表小明选修了MySQL课程
insert into stu_course values(1,3);

-- 代表小龙选修了Java课程
insert into stu_course values(2,1);

-- 代表小龙选修了C++课程
insert into stu_course values(2,2);

-- 代表小红选修了C++课程
insert into stu_course values(3,2);

-- 代表小红选修了Java课程
insert into stu_course values(3,1);

7.1.3 一对一

一对一（1:1） 在实际的开发中应用不多，因为一对一可以创建成一张表。

• SQL实现代码：

-- 先创建主表

-- 学生表
create table s2 (
	id int primary key,
	name varchar(20)
);

-- 个人信息表
create table info(
	id int primary key, -- 主键
	age int,
	addr varchar(20),
	foreign key (id) references s2(id)	-- 创建外键
);

insert into s2 values(1,'小明');
insert into s2 values(2,'小龙');
insert into s2 values(3,'小红');

-- 小明的年龄为23岁,籍贯为江西
insert into info values(1,23,'江西');

-- 小龙的年龄为25岁,籍贯为广西
insert into info values(2,25,'广西');

-- 小红的年龄为34岁,籍贯为山西
insert into info values(3,34,'山西');

7.1.4 表关系小结

表与表的关系	关系的维护
一对多	通过从表中外键来维护
多对多	通过中间表，将两个一对多加到一起变成了一个多对多
一对一	1. 外键添加约束 2. 从表的主键又是外键 可以简化成一张表

7.2 笛卡尔积

• 数据准备

create database db03;

use db03;

-- 创建部门表
create table dept(
	id int primary key auto\_increment,
	name varchar(20)
);

insert into dept(name) values ('研发部'),('销售部'),('财务部');

-- 创建员工表
create table emp (
	id int primary key auto\_increment,
	name varchar(10),
	addr varchar(30), 
	age int, 
	sex char(1), 
	dept_id int,
	foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);

INSERT INTO `emp` VALUES (1, '小明', '南昌', 24, '男', 1);
INSERT INTO `emp` VALUES (2, '小红', '九江', 20, '女', 1);
INSERT INTO `emp` VALUES (3, '小兰', '抚州', 19, '女', 2);
INSERT INTO `emp` VALUES (4, '小龙', '宜春', 18, '男', 2);
INSERT INTO `emp` VALUES (5, '小军', '赣州', 23, '男', 3);

7.2.1 什么是笛卡尔积现象

• 什么是笛卡尔积：

-- 需求：查询所有的员工和所有的部门
select \* from dept,emp;

结果如下：

如果左表是部门表，右表是员工表，左表中每一行记录与右表中的每一行记录全都匹配一次。

结果的行数=左表中行数x右表中行数

• 结果分析：

7.2.2 如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id

的数据才是有用的。所以需要通过条件过滤掉没用的数据。

-- 设置过滤条件
-- 指定过滤条件 主表.主键=从表.外键
select \* from dept,emp where dept.id = emp.dept_id;

-- 可以给表起别名
select \* from dept d,emp e where d.id = e.dept_id;

Tips：以上过滤以后的结果称为隐式内连接

7.3 内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示

7.3.1 隐式内连接

• 隐式内连接：看不到JOIN关键字，条件使用WHERE指定

select 列名 from 左表, 右表 where 主表.主键=从表.外键

select \* from dept,emp where dept.id=emp.dept_id;

一个人可以走的很快，但一群人才能走的更远。欢迎戳这里联系作者（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

• 上面的案例是就隐式内连接

7.3.2 显式内连接

无论是显示内连接还是隐式内连接查询结果是一样的，只是写法不同。

• 显式内连接：使用INNER JOIN ... ON语句, 可以省略INNER

select 列名 from 左表 inner join 右表 on 主表.主键=从表.外键

Tips：隐式内连接只能使用where来进行表的关联；显示内连接可以使用on和where来关联，推荐使用on

案例：查询小龙的信息，显示员工id，姓名，地址，年龄和所在的部门名称，我们发现需要联合2张表同时才能查询出需要的数据，使用内连接

• 1）确定需要查询的表（dept、emp表）

2）确定连接条件（过滤掉不需要的数据，消除笛卡尔积）

3）确定查询条件，我们查询的是小龙的信息，员工表.name=‘小龙’

4）确定查询的列名（员工id，姓名，地址，年龄，部门名称）

-- 查询小龙的信息,显示员工id,姓名,地址,年龄和所在的部门名称

-- 我们发现需要联合2张表同时才能查询出需要的数据，使用内连接
-- 1. 确定查询哪些表，得到笛卡尔积
select \* from dept inner join emp;

-- 2. 设置表连接的条件
select \* from dept d inner join emp e on d.id = e.dept_id;

-- 3. 确定查询条件，我们查询的是小龙的信息，员工表.name='小龙'
select \* from dept d inner join emp e on d.id = e.dept_id where e.name='小龙';

-- 4. 员工id，姓名，地址，年龄，部门名称
select 
	e.id 员工id,
	e.name 姓名,
	e.addr 地址,
	e.age 年龄,
	d.name 部门名称		-- 部门名称是dept表的
from dept d 
inner join emp e 
on d.id = e.dept_id 
where e.name='小龙';

7.3.3 内连接查询步骤：

1. 确定查询哪些表
2. 确定表连接的条件，通常是 主表.主键=从表.外键（消除笛卡尔积）
3. 确定查询条件
4. 确定查询的列

7.4 左外连接

7.4.1 左连接的语法：

• 左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略

select 列名 from 左表 left join 右表 on 表连接条件

7.4.2 左连接的案例：

需求：在部门表中增加一个行政部，需要查询所有的部门和员工，将部门表设置成左表，员工表设置成右表

select \* from dept;

insert into dept (name) values ('行政部');

-- 使用内连接查询

-- 需要查询所有的部门和员工，无论这个部门下有没有员工
select \* from dept inner join emp on dept.id = emp.dept_id;

结果如下：

使用左外连接查询：

select \* from dept left join emp on dept.id = emp.dept_id;

左连接的概念：查询的数据以左表为准，即使在其他表中没有匹配的记录也会显示出来；

7.5 右外连接

7.5.1 右连接的语法：

• 右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略

语法：

select 列名 from 左表 right join 右表 on 表连接条件

7.5.2 右连接的案例：

需求：在员工表中增加一个员工，但该员工还未分配部门

select \* from emp;

-- 不在任何一个部门
INSERT INTO `emp` VALUES (6, '小赵', '吉安', 26, '男', null);

-- 希望员工的信息全部显示出来

-- 使用内连接查询
select \* from dept inner join emp on dept.id = emp.dept_id;

内连接查询结果：

使用右外连接查询：

-- 使用右外连接查询
select \* from dept right join emp on dept.id = emp.dept_id;