MySQL高级查询(表连接查询与子查询)

表连接的分类：

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

　相同的数据表也可以做表连接。表连接查询速度比子查询高很多

内连接：

　内连接的数据表不一定有同名字段，只要字段之间符合逻辑关系就行。

#内连接的多种语法形式
#查询每名员工的部门信息
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,e.job,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;
#查询与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) a from t_emp) t
on e.sal>t.a;
#查询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.dname="RESEARCH";
#查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级?
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 a from t_emp group by deptno) t
on e.deptno=t.deptno and e.sal>t.a;

外连接：

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

　使用外连接时要特别注意where与on子句的区别，where会筛选掉符合某条件的数据，而on不会。

#外连接
select e.empno,e.ename,d.dname
from t_emp e left join t_dept d
on e.deptno=d.deptno;
#查询每个部门的名称和部门人数
select d.dname,count(e.deptno)
from t_emp e right join t_dept d
on e.deptno=d.deptno
group by d.deptno;
#查询每个部门的名称和部门人数，如果没有部门的员工，部门名称用null代替
(select d.dname,count(e.deptno)
from t_emp e right join t_dept d
on e.deptno=d.deptno
group by d.deptno) 
union
(select d.dname,count(*)
from t_emp e left join t_dept d
on e.deptno=d.deptno
group by d.deptno);
#查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、. 上司部门
select 
  e.empno,e.ename,d.dname,
  (e.sal+ifnull(e.comm,0)),s.grade,
  floor(datediff(now(),e.hiredate) /365),
  t.mgrno,t.mename,t.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
(select 
  e1.empno as mgrno,e1.ename as mename,d1.dname as mdname
from t_emp e1 join t_dept d1 
on e1.deptno=d1.deptno) t
on e.mgr=t.mgrno;

 

#筛选条件写在where和on的区别
select e.empno,e.ename,d.dname
from t_emp e left join t_dept d
on e.deptno=d.deptno
where e.deptno=10;

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

图一：

图二：

 

子查询：

 　子查询是一种查询中嵌套查询的语句。where子句在筛选数据时会执行很多次，select也是类似。from子句在筛选数据事只会执行一次，只推荐使用from子句的子查询

单行子查询和多行子查询:

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

　where子句中，可以使用in、all、any、exist关键字来处理多行表达式结果集的条件判断

#查询FORD和MARTIN两个人的同事
select ename
from t_emp
where deptno in
(select deptno from t_emp where ename in("FORD","MARTIN") )
and ename not in("FORD","MARTIN");

#查询比FORD和MARTIN底薪都高的员工信息
select ename from t_emp
where sal >= all
(select sal from t_emp where ename in("FORD","MARTIN"))
and ename not in("FORD","MARTIN");

#查询公子等级是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)
);