MySQL之连接查询详细介绍

一、含义

又称为多表查询，当查询的字段来自于多张表时，就会用到连接查询

二、引入

比如现在有两张表一张为beauty表，一张为boys表，如下

（图片均来自尚硅谷）
假如我们要查询每个女孩对应的男朋友，则按照之前的逻辑来讲，我们应该写如下语句：

SELECT
	name,boyName
FROM
	boys,beauty;

但是这样查的会产生48种结果，相当于用女孩表中的每一行去匹配男孩表的每一行，这显然不是我们想要的结果，这种现象称为笛卡尔乘积现象，就是表一有m行，表二有n行，产生结果为n*m行，那么如何避免这种现象呢，这就用到了连接查询，可以向以上语句添加限制条件：

SELECT
	name,boyName
FROM
	boys,beauty
WHERE
	beauty.boyfriend_id = boys.id;

三、分类

A：按年代分类
Sql92标准：仅仅支持内连接
Sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

B：按功能分类
a.内连接：
1.等值连接
2.非等值连接
3.自连接

b.外连接：
1.左外连接
2.右外连接
3.全外连接

c.交叉连接

四、具体讲解

1.sql92标准

一、等值连接
方法介绍：
a.多表等值连接的结果为多表的交集部分
b.n表连接，至少需要n-1个连接条件
c.多表的顺序没有要求
d.一般需要为表起别名
e.可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

1.简单使用

#案例1：引入内容就是一个等值连接，查询每个女孩对应的男朋友
SELECT
	name,boyName
FROM
	boys,beauty
WHERE
	beauty.boyfriend_id = boys.id;
	
#案例2：查询员工名对应和对应的部门名
SELECT
	name,department_name
FROM
	Employees,departments
WHERE
	employees.department_id = departments._id;

2.为表起别名
作用：
1.提高语句的简洁度
2.区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名来限定

#案例：查询员工名(name)，工种号(job_id)，工种名(job_title)
#工种号在两张表中名字相同，所以添加表名限制，可以用别名的方式
SELECT
	name,e.job_id,job_title
FROM
	employees  e,jobs  j
WHERE
	e.job_id=j.job_id;

3.等值连接加入筛选条件

#案例1：查询有奖金(commission_pct)的员工名(name)和部门名(department_name)

SELECT
	name,department_name,commission_pct
FROM
	employees e,departments d
WHERE
	e. department_id = d. department_id
AND
	e. commission_pct IS NOT NULL;
#多个限制条件时用AND限制

#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT
	department_name,city
FROM
	departments d,locations_id l
WHERE
	d.location_id = l.location_id
AND city LIKE ‘_o%’;

4.等值连接加入分组

#案例1：查询每个城市的部门个数
SELECT
	COUNT(*) 个数,city
FROM
	departments d,
	locations l
WHERE
	d.location_id = l.location_id
GROUP BY
	city;

#案例2：查询有奖金的每个部门的部门名和该部门的最低工资
SELECT
	department_name,
	MIN(salary)
FROM
	departments d,
	employees e
WHERE
	d.deparment_id = e.department_id
AND
	commission_pct IS NOT NULL
GROUP BY
	department_name;

5.等值连接加排序

#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT
	job_title,
	COUNT(*)
FROM
	employees e,
	jobs j
WHERE
	e.job_id = j.job_id
GROUP BY
	job_title
ORDER BY
	COUNT(*) DESC;

6.等值连接三表连接

#案例：查询员工名、部门名和所在的城市
SELECT
	name,department_name,city
FROM
	employees e,departments d,locations l
WHERE
	e.department_id = d.department_id
AND
	d.location_id = l.location_id;

二、非等值连接
方法介绍：和等值连接一样，可以添加排序等功能，唯一不同就是筛选条件的构造

#案例：查询员工的工资和工资级别
SELECT
	salary,grade_level
FROM
	employees e,job_grades g
WHERE
	salary BETWEEN g.lowest_sal AND g.highest_sal;

三、自连接
方法介绍：其实就是等值连接，只不过是只查一张表，例如：一张人员表里面有员工和领导，先根据员工查到领导的编号，再根据领导的编号查员工，使用范围就是表比较特殊的时候(加别名区分)。

SELECT
	e.name,m.name
FROM
	employees e,employees m
WHERE
	e.manager_id = m.manager_id;

2.sql99标准

语法：

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where筛选条件】
【group by分组】
【having 筛选条件】
【order by排序】

连接类型分类：
内连接：inner 
外连接：1.左外连接：left 【outer】
		2.右外连接：right【outer】
		3.全外连接：full【outer】
交叉连接：cross

一、内连接
特点：
a.添加排序、分组、筛选
b.inner可以省略
c.筛选条件可以放在where后面，连接条件放在on后面，提高分离性，便于阅读
d.inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
1.等值连接

#案例1：查询员工名、部门名
SELECT
	name,department_name
FROM
	departments d
INNER JOIN
	employees e
ON
	e.department_id = d.department_id;

#案例2：查询名字中含有e的员工名和工种名（添加筛选）
SELECT
	name,job_title
FROM
	employees e
INNER JOIN
	jobs j
ON
	e.job_id = j.job_id
WHERE
	e.name LIKE ‘%e%’;

#案例3：查询部门个数大于3的城市名和部门个数（添加分组+筛选）
#第一步：查询每个城市的部门个数
#第二步：用第一步的结果筛选满足条件的
SELECT
	city,COUNT(*) 部门个数
FROM
	departments d
INNER JOIN
	locations l
ON
	d.location_id = l.location_id
GROUP BY
	city
HAVING
	COUNT(*)>3;

#案例4：查询哪个部门的员工的个数大于3的部门名和员工个数，并按个数降序（添加排序）
#第一步：查询每个员工的员工个数
SELECT
	COUNT(*),department_name
FROM
	employees e
INNER JOIN
	departments d
ON
	e.department_id = d.department_id
GROUP BY
	department_name;
#第二步：在第一步的结果上筛选员工个数大于3的记录，并排序
SELECT
	COUNT(*),department_name
FROM
	employees e
INNER JOIN
	departments d
ON
	e.department_id = d.department_id
GROUP BY
	department_name
HAVING
	COUNT(*)>3
ORDER BY
	COUNT(*) DESC;

#案例5：查询员工名、部门名、工种名，并按部门名降序（添加多表）
SELECT
	name,department_name,job_title
FROM
	employees e
INNER JOIN
	department d
ON
	e.departmrnt_id = d.department_id
INNER JOIN
	jobs j
ON
	d.job_id = j.job_id
ORDER BY
	department_name DESC;

2.非等值连接

#案例：查询员工的工资级别
SELECT
	salary,grade_level
FROM
	employees e
JOIN					#省略INNER
	job_grades g
ON
	e.salary BETWEEN g.lower_sal AND g.highest_sal;

3.自连接

#案例：查询名字中包含k的员工的名字、上级的名字
SELECT
	e.name,m.name
FROM
	employees e
JOIN
	employees m
ON
	e.manager_id = m.employee_id
WHERE
	e.name LIKE ‘%k%’;

二、外连接
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
a.外连接查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null，外连接查询结果等于内连接结果加主表有而从表没有的记录。
b.左外连接，left join左边的是主表，右外连接，right join右边的是主表
c.左外和右外交换俩个表的顺序，可以实现同样的效果
d.由于Mysql数据库不支持全外连接，就不做案例了，全外连接就等于内连接的结果加表1有而表2没有的再加表2有而表1没有的数据
1.左外和右外

#案例1：查询男朋友不在男神表的女神名
SELECT
	b.name
FROM
	beauty b
LEFT OUTER JOIN
	boys bo
ON
	b.boyfriend_id = bo.id       #到这步已经查询到了一张新表里面含有主从表所有记录
WHERE
	bo.id IS NULL;              #筛选
#这是左外连接   右外连接只需要换成right outer 然后交换两张表的位置就好

三、交叉连接
交叉连接相当于之前讲过的笛卡尔积，就是表一有m行，表二有n行，产生结果为n*m行，就不做多的案例了

连接查询就介绍到这里，如果发现问题或者产生疑问请直接提出