MySQL基础总结（二）- 多表查询、子查询

前言

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。

根据表的连接方式来划分，包括：
内连接：等值连接、非等值连接、自连接
外连接：左外连接（左连接）、右外连接（右连接）
全连接

具体可看此文章Mysql—— 内连接、左连接、右连接以及全连接查询

（链接：本文数据下载 ， 提取码：1jvg ）

一、多表查询

1、笛卡尔积现象

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

表一（14行）：

表二（4行）：

select ename,dname from emp,dept;

结果：

图没截全，一共56行。那么对于第一张表而言14种选择，而对于第二张表来说有4种选择。所以结果就是14*4=56种选择了，也就是笛卡尔积。

怎么避免笛卡尔积现象？ 加条件进行过滤。
思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
不会，次数还是56次。系统还会匹配56次，但只显示了有效数据。

2、内连接

内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。

语法：
		select 
			xxx,xxx
		from
			A
		join
			B
		on
			连接条件
		where
			...

本文案例都将以以上经典的三表作为案例。其中：
dept(部门)：包含部门编号(deptno),职位(dname),所处位置(loc)；
emp(雇员)：包括入职时间、工资等；
salgrade(工资登记表):包括等级区间等。

	案例：找出每一个员工的部门名称，要求显示员工名和部门名。

select 
	e.ename,d.dname 
from 
	emp e
join 
	dept d
on 
	e.deptno = d.deptno;

结果图片太长，截取部分显示。

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select 
	e.ename,e.sal,s.grade
from 
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

这是自连接：最大的特点是：一张表看做两张表。自己连接自己。

select 
	e1.ename as'员工名',e2.ename as '领导名'
from 
	emp e1
join 
	emp e2
on
	e1.mgr = e2.empno;

3、外连接

外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

案例：找出每个员工的上级领导？（所有员工必须全部查询出来。）

select 
	e1.ename '员工', e2.ename '领导'
from
	emp e1
left join
	emp e2
on
	e1.mgr = e2.empno;

外连接最重要的特点是：主表的数据无条件的全部查询出来。

案例：找出哪个部门没有员工？

select 
	d.*
from 	
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where 
	e.empno is null;

案例：找出每一个员工的部门名称以及工资等级。（三张表）

select 
	e.ename,d.dname,s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on	
	e.sal between s.losal and s.hisal;

案例：找出每一个员工的部门名称、工资等级、以及上级领导。

select 
	e1.ename as '员工',d.dname,s.grade,e2.ename as '领导'
from
	emp e1
join
	dept d
on
	e1.deptno = d.deptno
join
	salgrade s
on
	e1.sal between s.losal and s.hisal
left join
	emp e2
on
	e1.mgr = e2.empno;

4、全连接 union

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

案例：找出工作岗位是SALESMAN和MANAGER的员工？

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

二、子查询

子查询：子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询。select语句当中嵌套select语句，被嵌套的select语句是子查询。

select
		..(select).
	from
		..(select).
	where
		..(select).

案例：找出高于平均薪资的员工信息。

select 
	e.ename,e.sal
from
	emp e
where
	e.sal > (select avg(sal) from emp);

案例：找出每个部门平均薪水的等级。

select 
	d.dname,t.*,s.grade
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal
join
	dept d
on
	t.deptno = d.deptno;

案例：找出每个部门平均的薪水等级。

方法一：

select
	deptno,avg(grade)
from
	(select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
group by
	deptno;

方法二：

select 
	e.deptno,avg(s.grade)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	e.deptno;

案例：找出每个员工所在的部门名称，要求显示员工名和部门名。

select 
	e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;