MySQL复习总结（二）

连接查询

连接查询就是将多张表联合起来进行查询。

连接方式：

• 内连接
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接（左连接）
  • 右外连接（右连接）
• 全连接

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

解决：并且加上过滤条件。使用一个案例来说明

--- 找出每一个员工的部门名称，要求显示员工名和部门名。
select ename,dname from emp,dept -- 如果emp表中有5条记录，dept中有4条记录，查出来的结果就是20条记录

-- 解决
select e.ename,d.dname from emp e,dept d where d.deptno = e.deptno -- sql92语法
-- 虽然查出来的结果没有出现笛卡尔积现象，但是底层进行比对的次数还是不变，只是在显示结果的时候把不符合条件的数据给过滤掉了。

内连接

内连接的等值连接：连接条件是等值关系

-- 例子：找出每一个员工的部门名称，要求显示员工名和部门名。
select e.ename,d.dname from emp e,dept d where d.deptno = e.deptno -- sql92语法

select e.ename,d.dname from emp e (inner) join dept d on e.ename = d.dname -- sql99语法(inner可以省略)

-- 语法
		...
			A
		join
			B
		on
			连接条件
		where
			...
			
-- sql99语法的好处：表的连接条件和后来的where条件分离了

内连接的非等值连接：连接条件中的关系是非等量关系

-- 案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。 涉及两张表 emp 和 salgrade
select 
	e.ename,e.sal,s.grade
from 
	emp e
join 
	salgrade s
on 
	e.sal between s.losal and s.hisal

自连接：特点是一张表看成两张表，自己连接自己

--案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。
select 
	a.ename as '员工',b.ename as '领导'
from 
	emp a
join 
	emp b
on 
	a.mgr = b.empno

外连接

外连接和内连接的区别：

• 内连接：A表和B表上能够匹配的记录都会查出来。两张表没有主副之分，是平等的。
• 外连接：A，B两张表有一个表是主表，另一个表是副表，主要查询主表中的数据，顺带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表会自动模拟出null与之匹配。

外连接的分类：

左外连接（左连接）：左边的表是主表

右外连接（右连接）：右边的表是主表

-- 案例：找出每个员工的上级领导，所有员工都要查出来  

-- 所有员工都要查出来说明员工表是主表

-- 左连接
select 
	a.ename as '员工',b.ename as '领导'
from 
	emp a
left join 
	emp b
on 
	a.mgr = b.deptno

-- 左外连接
select 
	a.ename as '员工',b.ename as '领导'
from 
	emp a
left outer join 
	emp b
on 
	a.mgr = b.empno

-- 右连接  --outer可以省略
select 
	b.ename as '员工',a.ename as '领导'
from 
	emp a
right join 	-- 右面的b表是主表,也就是员工表
	emp b
on 
	b.mgr = a.empno

--右外连接  --outer可以省略
select 
	b.ename as '员工',a.ename as '领导'
from 
	emp a
right outer join 	-- 右面的b表是主表,也就是员工表
	emp b
on 
	b.mgr = a.empno
	

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

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

--分析：因为要查出查出所有的部门才能找出来哪个部门没有员工，所以如果使用内连接的话可能会有数据的丢失
-- 因此要使用外连接，并且dept表要作为主表

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

首先根据部门号查出所有的匹配的信息：

select e.*,d.* from dept d LEFT join emp e on e.deptno = d.deptno

此处注意，emp表中是14条记录，此时查出来的是15条记录，因为有一个部门是没有员工的，况且dept表作为主表查询，所以会副表会模拟出null值与没有员工的部门进行对应。

因此，要把最后一条记录筛选出来，只要加个条件过滤就可以了。条件可以是where e.empno is null（不唯一，也可以是其他条件，比如，e.ename is null。。。）

三张表的连接查询

-- 语法
....
		A
	join
		B
	join
		C
	on
		...
	
	表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。
-- -- 案例：找出每一个员工的部门名称以及工资等级。
	
-- 员工在emp表中，部门名称dname在dept表中，工资等级grade在salgrade表中
select 
	e.ename,d.dname,e.sal,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 
	e.ename,d.dname,e.sal,s.GRADE,m.ename
from 
	emp e 
join 
	dept d 
on 
	e.deptno = d.deptno 
join 
	salgrade s 
on 
	e.sal between s.LOSAL and s.HISAL
left join 						-- 这里用到了外连接，left join 代表主表是e
	emp m
on 
	e.mgr = m.empno