mysql表连接

表连接

1.表连接的分类

（1）表连接分为两种：内连接和外连接。
（2）内连接是结果集中只保留符合连接条件的记录。
（3）外连接是不管符不符合连接条件，记录都要保留在结果接种。

2.内连接

select … from 表1
[inner] join 表2 on 条件
[inner] join 表3 on 条件
…
内连接的多种语法形式
select … from 表1 join 表2 on 连接条件；
select … from 表1 join 表2 where 连接条件；
select … from 表1,表2 where 连接条件；

#查询每名员工的信息
select e.empno,e.ename,d.dname
from t_emp e join t_dept d on e.deptno=d.deptno;
select e.empno,e.ename,d.dname
from t_emp e join t_dept d where e.deptno=d.deptno;
select e.empno,e.ename,d.dname
from t_emp e , t_dept d where e.deptno=d.deptno;

#查询每名员工的工号、姓名、部门名称、底薪、职位、工资等级？
select e.empno,e.ename,d.dname,e.sal,s.grade
from t_emp e join t_dept d on e.deptno=d.deptno
join t_salgrade s on e.sal between s.losal and s.hisal;

tips:内连接的数据表中不一定必须有同名字段，只要字段之间符合逻辑关系就可以

#查询与scott相同部门的员工都有谁？
select e2.ename
from t_emp e1 join t_emp e2 on e1.deptno=e2.deptno
where e1.ename = "scott" and e2.ename!="scott";

#查询底薪超过公司平均底薪的员工信息？
select e.empno,e.ename,e.sal
from t_emp e join 
(select avg(sal) avg from t_emp) t
on e.sal>=t.avg;

#查询research部门的人数、最高底薪、最低底薪、平均底薪、平均工龄？
select count(*),max(e.sal),min(e.sal),avg(e.sal),
avg(datediff(now(),e.hiredate)/365)
from t_emp e join t_dept d on e.deptno=d.deptno
where d.name="research"

select floor(28.9)
select ceil(1.1)

floor() 强制舍去
ceil() 强制进位

#查询每种职业的最高工资，最低工资，平均工资，最高工资等级和最低工资等级？
select e.job,max(e.sal+ifnull(e.comm,0)),
min(e.sal+ifnull(e.comm,0)),
avg(e.sal+ifnull(e.comm,0)),
max(s.grade),min(s.grade)
from t_emp e join t_salgrade s
on (e.sal+ifnull(e.comm,0)) between s.losal and s.hisal
group by e.job;

#查询每个底薪超过部门平均底薪的员工信息
select e.empno,e.ename,e.sal
from t_emp e join
(select deptno,avg(sal) as avg from t_emp group by deptno) t 
on e.deptno=t.deptno and e.sal>t.avg;

3.外连接简介

外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录。

select e.empno,e.ename,dname
from t_emp e left join t_dept d
on e.deptno = d.deptno;

(1)左连接和右连接

左外连接就是保留左表所有的记录，与右表做连接。如右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用null与左表相连。右外连接也是如此。

select e.empno,ename,d.name
from t_dept d right join t_emp e
on e.deptno=d.deptno;

UNION关键字可以将多个查询语句的结果集进行合并
（查询语句） UNION （查询语句）UNION （查询语句）…

(
select d.name,count(e.deptno)
from t_dept d left join t_emp e
on d.deptno = e.deptno
group by d.deptno
)
UNION
(select d.dname,count(*)
from t_dept d right join t_emp e
on d.deptno = e.deptno
group by d.deptno);

#查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门？
select 
	e.empno,ename,d.dname,
	e.sal+ifnull(e.comm,0),s.grade,
	floor(datediff(now(),e.hiredate)/365),
	t.empno as mgrno,t.name as mname,t.dname as mdname
	from t_emp e left join t_dept d on e.deptno =d.deptno
	left join t_salgrade s on e.sal between s.losal and s.hisal
	left join 
	(selet e1.empno,e1.ename,d1.name
	from t_emp e1 join t_dept d1
	on e1.deptno = d1.deptno
	) t on e.mgr=t.empno;

外连接的注意事项
内连接只保留符合条件的记录，所以查询条件写在on子句和where子句中的效果是相同的。但是外连接里，条件写在where子句里，不符合条件的记录是会被过滤掉，而不是保留下来。

3.子查询简介

(1)子查询是一种查询中嵌套查询的语句

#查询底薪超过公司平均底薪的员工的信息
select empno,ename,sal
from t_emp
where sal>=(select avg(sal) from t_emp);

(2)子查询的分类

子查询可以写在三个地方：where子句、from子句、select子句，但是只有from子句子查询最可取

where子查询

1）子查询最简单，最容易理解，但是却效率很低的子查询

#查询底薪超过公司平均底薪的员工的信息
select empno,ename,sal
from t_emp
where sal>=(select avg(sal) from t_emp);

from子查询

这种子查询只会执行一次，所以查询效率很高

select e.empno,e.ename,e.sal,t.avg
from t_emp e join
   (select deptno,avg(sal) as avg
   from t_emp group by deptno) t
on e.deptno=t.deptno and e.sal>=t.avg;

select 子查询

这种子查询每输出一条记录的时候都要执行一次，查询效率很低。

select e.empno,e.ename,
(select dname from t_dept where deptno = e.deptno)
from t_emp e;

单行子查询和多行子查询

• 单行子查询的结果集之后一条记录，多行子查询结果集有多行记录。
• 多行子查询只能出现在where子句和from子句中。

#子查询查询ford和martin两个人的同事
select ename from t_emp
where deptno in
(select deptno from t_emp where ename in("ford","margin"))

where 子句中的多行子查询

• where 子句中，可以使用in,all,any,exists关键字来处理多行表达式结果集的条件判断
  all 所有
  any 任一

#查询比ford和martin底薪都搞的员工信息
select ename from t_emp
where sal > all
(select sal from t_emp
where ename in("martin","ford"));

exists关键字

• exists关键字是把原来在子查询之外的条件判断，写到了子查询里面。
  select … from 表名 where [not] exists(子查询)；

#查询工资等级是3级或4级的员工信息
select empno,ename,sal
from t_emp
where exists(
select grade from t_salgrade
where sal between losal and hisal
and grade in(3,4)
);