8.连接查询

#DQL语言
##七、连接查询

在讲之前，我们先导入一个girls.sql，里面包含beauty和boys两张表，导入方法已经在之前讲过了，这里就不再赘述，效果如图↓

在深入学习前，我们先来认识一下这beauty和boys这两张表
①beauty表：

②boys表：

显而易见，在beauty表里每个女神后面都有一个boyfriend_id，这个号码对应着boys表中相应男神前面的id号，那假如我们用下面这个语句去查询会有什么效果呢？

SELECT
	NAME,
	boyName
FROM
	boys,
	beauty;

截图如下↓

可见，这样一来每个女神对分别对应了boys表中所有的男神，最终产生了4*12=48行的结果，我们称这种现象为笛卡尔现象，其产生的原因就在于没有有效的连接条件，那正确的查询写法是什么呢？代码如下：

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

PS:由于两张表里面都有“id”这一字段，所以我们用“表名.字段”去区分！
结果如下：

###一、连接查询的分类

按照功能进行分类
①内连接：
a.等值连接
b.非等值连接
c.自连接

②外连接：
a.左外连接
b.右外连接
c.全外连接

③交叉连接

按照年代进行划分
①sql92标准：仅仅支持内连接(其实也支持一点点外连接，但占比实在太小，故我们一般只认为sql92标准只支持内连接)

②sql99标准：支持 内连接+外连接(支持左外和右外)+交叉连接 →推荐使用

####1.sql92标准
#####①等值连接
######a.简单的等值查询

案例一：查询员工名和对应的部门名

SELECT
	last_name,
	department_name
FROM
	employees,
	departments
WHERE
	employees.`department_id`=departments.`department_id`;

案例二：查询员工名、工种号、工种名

首先我们看这样写对吗？↓

SELECT
	last_name,
	job_id,
	job_title
FROM 
	employees,
	jobs
WHERE
	employees.`job_id`=jobs.`job_id`;

结果报错…

为什么呢？我们可以注意到：

在employees和jobs两张表中都有job_id这一字段，那我们select后面所查询的job_id是不是没有指明是哪张表中的job_id呀？于是我们可以这样改：

SELECT
	last_name,
	employees.job_id,              //添加表名前缀~
	job_title
FROM 
	employees,
	jobs
WHERE
	employees.`job_id`=jobs.`job_id`;

扩展：我们也可以为表名起别名，来提高撸码效率~ 如下

SELECT
	last_name,
	employees.job_id,              
	job_title
FROM 
	employees e,
	jobs j
WHERE
	e.`job_id`=j.`job_id`;

结果竟然再次报错！

思考:其实是因为我们为表起别名之后，系统自动生成了一个虚拟“表图”，用别名覆盖了之前的表名，同时我们也知道select语句的执行顺序其实是在from语句之后的，所以在from语句中为表起了别名之后，原本的表名employees就被e给覆盖，那我们再去select语句中执行查询employees.job_id时就找不到原本的employees这一表名，那么显然正确的改法就是↓

SELECT
	last_name,
	e.job_id,              
	job_title
FROM 
	employees e,
	jobs j
WHERE
	e.`job_id`=j.`job_id`;

select后面除了job_id以外，其他的两个字段last_name和job_title由于没有"歧义"，所以加不加别名都可以哒~同时在from语句中两个表的位置也可以互换。

######b.添加筛选的等值查询

案例：查询城市名中第二个字符为o的部门名和城市名

SELECT
	department_name,
	city
FROM
	departments d,
	locations l
WHERE
	d.`location_id`=l.`location_id`
	AND
	l.`city` LIKE '_o%';

######c.添加分组的等值查询

案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT
	department_name,
	d.manager_id,            //注意两个表都有manager_id,需要加表名指定一下
	MIN(salary)
FROM
	employees e,
	departments d
WHERE 
	e.`department_id`=d.`department_id`
	AND
	commission_pct IS NOT NULL
GROUP BY
	department_name,manager_id;

######d.添加排序的等值查询

案例：查询每个工种的工种名和员工的个数，并且按员工个数进行降序排序

SELECT
	job_title,
	COUNT(*)
FROM
	employees e,
	jobs j
WHERE 
	e.`job_id`=j.`job_id`
GROUP BY
	job_title
ORDER BY
	COUNT(*) DESC;

######e.三表连接的等值查询

案例：查询员工名、部门名和所在的城市

SELECT
	last_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
	e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY
	e.`salary` DESC;

#####③自连接

所谓自连接，即指：自己表与自己表通过某些条件进行相互连接，并不是每一个表都可以满足自连接的条件，下面举一个例子来具体说明一下这个道理

案例：查询员工名和上级的名称

分析:我们先看employees这张表

我们知道要找到一个员工的上级编号，可以从该员工的manager_id上进行查找，因为上级也是员工，那么他也有employee_id,故第一次查表先看员工的manager_id,第二次查表就根据该manager_id去查找与之相同的employee_id就找到啦！查询语句如下

SELECT
	e.employee_id,e.last_name,m.employee_id,m.last_name
FROM 
	employees e,employees m               //因为要查询两次，故引用两次employees表，且必须用别名来区分开
WHERE
	e.`manager_id`=m.`employee_id`;       //查询的条件

结果如下：↓

再来一道案例

案例一：查询city在Toronto工作的员工的last_name,job_id,department_id,department_name

select
	last_name,job_id,d.department_id,department_name    //一定要注意标注“歧义”字段！！！
from
	employees e,
	departments d,
	locations l
where
	e.`department_id`=d.`department_id`
	and
	d.`location_id`=l.`location_id`
	and
	l.`city`='Toronto';

案例二：查询每个国家下的部门个数大于2的国家编号

SELECT
	COUNT(*),
	country_id
FROM
	locations l,
	departments d
WHERE
	l.`location_id`=d.`location_id`
GROUP BY
	country_id
HAVING
	COUNT(*)>2;

####2.sql99标准

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

分类
内连接(重要):inner

外连接
左外(重要):left【outer】
右外(重要):right【outer】
全外：full【outer】

交叉连接

#####①内连接

内连接基本语法
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

######a.等值连接

案例一：查询名字中包含e的员工名和工种名

SELECT
	last_name,
	job_title
FROM
	employees e
INNER JOIN
	jobs j
ON	e.`job_id`=j.`job_id`
WHERE
	last_name LIKE '%e%';

案例二：查询员工名、部门名、工种名，并按部门名降序

SELECT
	last_name,
	department_name,
	job_title
FROM
	employees e
INNER JOIN 
	departments d ON e.`department_id`=d.`department_id`
INNER JOIN
	jobs j ON e.`job_id`=j.`job_id`	
ORDER BY
	department_name DESC;

PS几点需要注意的部分
·第一点：sql99标准的连接查询也可以添加筛选、排序、分组查询
·第二点：inner可以省略，但为了清楚地书写我们还是尽量不去略写
·第三点：筛选放在where语句后面，连接条件单独放在on后面，这样一来提高了代码分离性
·第四点：举个例子↓

假如我们先写from jobs表(只是假如而已)，然后紧接着的on后面的连接条件写为departments d ON e.department_id=d.department_id，那么这样是不行的！！！后面挨着的连接条件所涉及到的表必须
包含前面刚写的jobs表，这一点希望大家牢记~

######b.非等值连接

案例：查询工资级别的个数>20的个数，并且按工资级别降序

SELECT
	COUNT(*),
	grade_level
FROM
	employees e
INNER JOIN
	job_grades g 
ON	e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY
	grade_level
HAVING
	COUNT(*)>20 
ORDER  BY
	COUNT(*) DESC;

######c.自连接

案例：查询姓名中包含字符k的员工的名字以及上级的名字

SELECT
	e.last_name,
	m.last_name
FROM
	employees e
INNER JOIN
	employees m
ON	e.`manager_id`=m.`employee_id`
WHERE	
	e.`last_name` LIKE '%k%';

#####②外连接

什么是外连接？
→先把概念抛出来：简单来说就是查询一个表中有(称之为主表)，另一个表中没有的记录

举个例子…

beauty表(女神表)

boys表(男神表)

看这两张表，如果说，使用内连接相当于是由连接条件取两表的交集部分，那么外连接则相当于是取主表中有的部分，但从表中没有的部分。那么我们使用外连接的语法看看有什么效果呢↓

SELECT
	b.*,
	bo.*
FROM
	beauty b
LEFT OUTER JOIN             //左外连接
	boys bo
ON	b.`boyfriend_id`=bo.`id`;

结果如下

可见，没有与连接条件匹配的男神信息每一字段都是null
那么接下来看一个案例

案例一：查询男朋友不在男神表中的女神名

SELECT
	b.name
FROM
	beauty b
LEFT OUTER JOIN              //这里用了 左外连接
	boys bo
ON	b.`boyfriend_id`=bo.`id`
WHERE
	bo.`id` IS NULL;

PS:那么我们的筛选条件为什么要选择bo.id去限制呢，为什么不能是bo.name呢？我们从下图中可以看到


boys表中的id字段为非空键值，所以无论如何id都不为null，这样一来对于我们后面的“为null”筛选就提供了确定性，因为之前的id值始终不为null，那么外连接后为null的id值一定是我们匹配到的信息！！！这一点希望大家注意！！！！

上面的查询语句也可以用右外连接去实现，道理是一样的，左外连接语句(LEFT OUTER JOIN)左边的为主表，右边的为从表；右外连接语句(RIGHT OUTER JOIN)左边的为从表，右边的为主表。

SELECT
	b.name
FROM
	boys bo
right OUTER JOIN              //这里用了 右外连接
	beauty b
ON	b.`boyfriend_id`=bo.`id`
WHERE
	bo.`id` IS NULL;

案例二：查询哪些部门没有员工

SELECT
	department_name
FROM
	departments d
LEFT OUTER JOIN                        //左外连接
	employees e
ON	d.`department_id`=e.`department_id`
WHERE
	e.`employee_id` IS NULL;

案例三：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

SELECT
	b.id,b.name,bo.*
FROM
	beauty b
INNER JOIN 
	boys bo
ON	b.`boyfriend_id`=bo.`id`
WHERE
	b.`id`>3;

左/右外连接讲完了，那么什么又是全外连接呢？

通俗来讲
全外连接=内连接的结果+表1中有但是表2没有的+表2中有但表1没有的
由于全外连接在mysql里面不支持，所以例子就不举了…

#####③交叉连接

看例子↓

SELECT
	b.*,
	bo.*
FROM
	beauty b
CROSS JOIN
	boys bo;

效果如下：

总共48行，即4*12,交叉连接相当于实现了“九九乘法”

以上就是连接查询的内容，下一节将介绍子查询！