07【连接查询】

---

上一篇：06【数据库的约束】

下一篇：08【子查询】

目录：【MySQL零基础系列教程】

---

---

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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

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,'山西');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

7.1.4 表关系小结

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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

7.2.1 什么是笛卡尔积现象

• 什么是笛卡尔积：

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

结果如下：

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

结果的行数=左表中行数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;
1
2
3
4
5
6

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

7.3 内连接

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

7.3.1 隐式内连接

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

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

select * from dept,emp where dept.id=emp.dept_id;
1
2
3

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

7.3.2 显式内连接

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

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

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

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='小龙';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

7.3.3 内连接查询步骤：

1. 确定查询哪些表

2. 确定表连接的条件，通常是 主表.主键=从表.外键（消除笛卡尔积）

3. 确定查询条件

4. 确定查询的列

7.4 左外连接

7.4.1 左连接的语法：

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

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

7.4.2 左连接的案例：

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

select * from dept;

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

-- 使用内连接查询

-- 需要查询所有的部门和员工，无论这个部门下有没有员工
select * from dept inner join emp on dept.id = emp.dept_id;
1
2
3
4
5
6
7
8

结果如下：

使用左外连接查询：

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

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

7.5 右外连接

7.5.1 右连接的语法：

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

语法：

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

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;
1
2
3
4
5
6
7
8
9

内连接查询结果：

使用右外连接查询：

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

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

7.6 全连接

到目前为止我们学过了内连接、外连接；

• 1）内连接：分为显示内连接和隐式内连接，查询的数据必须左右两表都存在才会显示
• 2）左连接：又叫左外连接，查询的数据以左表为准，即使左表中的数据在其他表中没有匹配也会查询出来，大不了以null补齐
• 3）右连接：又叫右外连接，和左外连接相反，查询的数据以右表为准，即使右表中的数据在其他表中没有匹配也会查询出来，大不了以null补齐

我们知道左连接是无论如何左表的数据都能够显示全，右连接是右表的数据无论如何都能够显示全面，那么如果我们希望左表和右表的数据都能够显示全面呢（在对方表中没有匹配的数据就以null补齐）？这种连接查询我们称之为全连接（full join），但是很遗憾，MySQL并没有提供全连接，但Oracle支持；

虽然MySQL不支持全连接，但是我们可以利用MySQL提供的其它功能来完成全连接的功能：

left join + right join

select * from dept d left join emp e on d.id=e.dept_id
union 
select * from dept d right join emp e on d.id=e.dept_id;
1
2
3

全连接查询的结果如下：

Tips：union关键字可以将两个或多个SQL语句的结果集拼接成一个结果集，前提是这些SQL语句的结果集列数必须相同；

union关键字自带去重功能，即去除重复的数据：

如果需要保留重复的数据则可以使用union all关键字来连接多个SQL语句的结果集：