MySQL之连接查询（笛卡尔积现象，内连接，外连接，多表连接，子查询）

一，MySQL之连接查询

首先你得知道这章要用的三张表：emp（员工表），dept（部门表），salgrade（工资等级表）

emp表

dept表

salgrade表

1.什么是连接查询

像将emp表与dept表联合起来查询数据，这种跨表查询，多张表联合起来查询数据叫连接查询

2.连接查询的分类

根据语法的年代分类

（1）：SQL92（1992年出现）
（2）：SQL99（1999年出现）重点学

根据连接方式分类

（1）：内连接
内连接又可以继续分为：等值连接，非等值连接，自连接
（2）：外连接
外连接又可以继续分为：左外连接，右外连接

3. 笛卡尔积现象

什么是笛卡尔积现象

当两张表进行连接查询时，没有任何条件限制的时候，最终查询结果数目是这两张表条数的乘积。
举个栗子：

select 
	count(*)//count（*）计算表的行数
from
	emp,dept;//这里进行了表的连接，但是没有限制条件
			//emp表有14行，dept表有4行数据

从输出的结果可以知道没有限制条件输出的表有56行，而56=14（emp表的行数）*4（dept表的行数），这一切是因为巧合吗？？？
要搞清楚就得搞明白表连接的字段匹配机制
我们取出emp表的一个字段和dept表的一个字段来举例解释
我们假设emp中ename字段有3行数据，dept中dname字段有2行数据

emp.ename	dept. dname
ename1	dname1
ename2	dname2
ename3

首先执行 from emp，dept代码，进入emp中，找到第一行的数据，拿走到dept表中与dept表中每一行的数据进行匹配。

然后要输出的表就变成了

emp.ename	dept. dname
ename1	dname1
ename1	dname2

emp表中第一行的数据匹配完了后，再找下一行数据与dept表中数据进行匹配。

然后要输出的表就变成了

emp.ename	dept. dname
ename1	dname1
ename1	dname2
ename2	dname1
ename2	dname2

以此类推，最后emp和dept合成的表就变成了

emp.ename	dept. dname
ename1	dname1
ename1	dname2
ename2	dname1
ename2	dname2
ename3	dname1
ename3	dname2

现在我们来计算一下合成后的表的行数，一共是6行。
通过上面的解释，应该能够得出，合成后的表的行数=被合成的表的行数的乘积。
这也就解释了，下面代码执行后为什么输出的行数是56了。

select 
	count(*)//count（*）计算表的行数
from
	emp,dept;//这里进行了表的连接，但是没有限制条件
			//emp表有14行，dept表有4行数据

如何避免笛卡尔积现象

我们都说了笛卡尔积现象出现的原因是：两表连接时，未加限制条件导致的。所以我们只需要加上限制条件就行了。

select
	ename,dname
from 
	emp,dept
where
	emp.deptno=dept.deptno;

加了限制条件后，输出的结果是不是变得正常多了。但是这样子写代码，会让效率变低。
效率低的原因就是：找字段名ename和dname时，mysql会不知道去哪个表找，她可能会在emp表中找dname，在dept表中找ename。
那如何让笨笨的mysql女士能够在找对字段名所在的表呢？

直接告诉她不就得了！ 上代码！

select
	e.ename,d.dname //指明ename，dname所在的表
from
	emp e,dept d //给表起别名，这样方便写代码
where
	e.deptno=d.deptno; //这个也得加上

这样笨笨的mysql也能准确的找到字段名了呢！

笛卡尔积现象结论

我们现在已经理解了笛卡尔积现象的形成原因，那么我们可以的出一个结论：表的连接次数越多，效率会呈倍数乃至指数型的降低。

二，内连接

内连接的特点：
（1）将能够匹配上连接限制条件的数据查询出来
（2）两表之间没有主次关系（主次关系在外连接中会说明）

1.等值连接与SQL99（join…on…）

什么是等值连接

连接限制条件是通过 = 连接
such：

e.deptno=d.deptno

SQL99（join…on…）

在解释笛卡尔积现象中的代码中，以 “from 表，表” 来连接的sql语句属于SQL92语法（不常用），常用的是SQL99语法。那SQL99语法的表连接该如何写呢?
SQL99：

select
	e.ename,d.dname
from
	emp e  
inner join //inner join 表示内连接。inner可省略
	dept d
on
	e.deptno=d.deptno;

join： 表示的意思是连接，from emp join dept的意思是emp表内连接dept表。
on： 表示的意思，连接emp表和dept表的限制条件。
这样SQL92和SQL99的代码对比起来，SQL99显得更加的清晰，结构分得更加的清楚。SQL92的代码结构不够清晰，她将表的连接条件，和后期进一步筛选的条件都放在了where子句中。

2.非等值连接

什么是非等值连接

与等值连接相对的就是非等值连接，进一步解释就是连接限制条件不是等号。

such：找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级

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

上面代码可能有点难理解，但只要联系一下表连接的匹配机制是一行一行进行的就好理解了。

3.自连接

什么是自连接

自连接就是自己跟自己连接，自连接也可以当成将一张表看成两张表。
这样说可能不太清楚，我们直接

such：查询员工的上级领导，要求显示员工名和对于的领导名

select
	e1.ename as Employeer,e2.ename as Boss
from
	emp e1 //员工表
inner join
	emp e2 //上级领导表
on
	e1.mgr=e2.empno; //mgr:员工的上级领导编号

成功输出了结果但是没有KING（最大的领导）没有输出，那是因为KING的mrg是null。
那如何解决这个问题呢，让KING也能够输出，那就是要用到，我们接下来要提到的外连接了…

三，外连接

特点：将未匹配的数据也能查出来；有主次表之分。
主表：查询时以主表为主，将主表的数据取出与次表进行匹配（联想一下上面讲到的表连接的匹配机制）
其实内连接类似左外连接表，只是内连接无法将未匹配的数据查出，但内连接也是和左外连接一样，将join左边的表的数据取出，与右边的表中数据进行循环配对。

左外连接（join左边的表为主表）

依旧是上面那个例子，使用左外连接
such：查询员工的上级领导，要求显示员工名和对于的领导名

select
	e1.ename as Employeer,e2.ename as Boss
from
	emp e1
left outer join //outer可省略
	emp e2
on
	e1.mgr=e2.empno;

这个例子其实就是外连接（左外连接）与内连接（自连接）的结合使用了

右连接表（join右边的表为主表）

右外连接表与左外连接表类似，不同的地方就是：
（1）join右边的表作为主表
（2）left改成right
这个例子借用下上面的例子的数据，将left改成right，看看输出的结果

select
	e1.ename as Employeer,e2.ename as Boss
from
	emp e1
right outer join //outer可省略
	emp e2
on
	e1.mgr=e2.empno;

外连接与内连接的查询结果次数大小

外连接的查询结果次数要大于等于内连接的查询结果次数
这应该也好理解，因为外连接多出来的次数就是将内连接匹配不成功的数据查询输出了。

四，多表连接（内连接与外连接的混合）

多表连接其实就是多个join…on…的使用
语法：

select
	...
from
	a
inner join//或者  left/right outer join
	b
on
	...//a和b的连接条件
inner join//或者  left/right outer join
	c
on
	...//a和c的连接条件
....
	

多表连接需要注意的是：分清楚是哪个表和哪个表连接。只要记住被连接表都是和第一个表连接就行了。
来到例题吧！
such：找出每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资，薪资等级

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

五，子查询

1.什么是子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

2.子查询能够出现在哪

//（select）代表子查询
select
	...(select)
from
	...(select)
where
	...(select)

3.where中的子查询

such：找出比最低工资高的员工姓名和工资
易错写法，错误原因：where语句中不能分组查询

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

实现子查询一定要先分析，在进行写代码
实现思路：
step1： 查询最低工资的员工

select
	min(sal)
from
	emp e;

step2: 找出工资比800大的员工

select
	e.ename,e.sal
from
	emp e
where
	sal>800;

step3： 合并step1和step2

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

通过这样的思路将子查询的语句成功地写出来了

4.from中的子查询

from后面的子查询，可以将子查询的查询结果当作一张临时表
such：找出每个工资岗位的平均工资的薪资等级
同样的不能一上来就写代码，因为子查询代码对于初学者来说不是能一下就做出来的，所以得一步步分析。
实现思路：
step1： 找出每个工资岗位的平均工资

select
	e.job,avg(sal) as avgsal
from
	emp e
group by
	e.job;

step2： 将以上查询结果当成t表，salgrade表当成s表
t表和s表的连接条件是：t.avgsal between s.losal and s.hisal

select
	t.*,s.grade
from
	t
inner join
	salgrade s
on
	t.avgsal between s.losal and s.hisal; //s表和t表是非等值连接，属于内连接
	

step3 ： 合并

select
	t.job,t.avgsal,s.grade
from
	(select e.job,avg(sal) as avgsal from emp e group by job) t 
inner join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

from 中子查询的 avg(sal)一定要起别名，如果不起的话，在后续的t.avg(sal)中的avg(sal)不会当成一个变量，而是当成t的一个函数。
这里面的t表就相当于子查询得出的一个临时表。

5.select中的子查询(了解即可)

such：找出每个员工的部门名称，要求显示员工名，部门名
这里就直接上代码了，不过多讲述

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

需要注意的是：对于select中的子查询，这个子查询只能一次返回一条结果
重点需要理解一次返回一条结果这句话。
如果写成下面这段代码，就会报错

select
	e.ename,e.deptno,(select dname from dept)
from
	emp e;

不需要重点掌握的原因是：select中的子查询其实是表连接，用表连接的方式就可以写。

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

你看查询的结果是不是一样的。

六，union（合并查询结果集）

such：查询工作岗位为’MANAGER’和’SALESMAN’的员工
way1: OR

select 
	e.ename,e.job
from 
	emp e
where
	job= 'MANAGER' or job= 'SALESMAN' ;//不要写成job= 'MANAGER' or 'SALESMAN' 

way2：IN

select 
	e.ename,e.job
from 
	emp e
where
	job in ('MANAGER','SALESMAN') ;

way3：UNION

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

使用union的注意事项

1.对于表连接，使用union得到效率更高，因为表连接每连接一次，匹配次数就满足笛卡尔积，
2.union的匹配相当于加法，表连接的匹配相当于乘法。
3.union连接的两个结果集的列数必须相同
4.合并时，列的数据类型必须相同（MySQL中允许，但是Oracle不允许）

七，limit（MySQL提供的）

作用

将查询结果集的一部分取出来，通常用在分页查询当中。

什么是分页查询？

像百度一样，规定了一页显示多少的数据，剩下的数据在其他页。
这样做的好处就是能够提高用户的体验。

用法

完整用法

limit startIndex,length //startIndex表示起始下标（从0开始），length表示长度

缺省用法

limit length //默认起始下标是零

such：
取出薪资排名前五的员工

select
	e.ename,e.sal
from 
	emp e
order by
	e.sal desc
limit 5;//也可以写成: limit 0,5

such:取出薪资排名2~5名的员工

select
	e.ename,e.sal
from
	emp e
order by
 	e.sal desc
limit 1,3;  //1表示起始下标，3表示长度

分页的实现

such：每页显示pageSize条记录,pageNo表示页码。

String sql ="limit "+(pageNO-1)*pageSize+","+pageSize; //JDBC中会用

八，DQL语句大总结

select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...
执行顺序： from → where → group by → having → select → order by → limit
还记得之前说的order by一定是最后执行的吗，这里突然冒出了一个limit把最后的位置给抢了。
那是因为limit是MySQL提供的，不属于SQL语句。

好了！本章结束了，各位有学到吗？学到了的话，麻烦点个赞哦。