【MySQL自学记录】多表查询

一、SQL99语法：

筛选条件和连接条件是分开的：

1、筛选条件：where、having

2、连接条件：交叉、自然连接、on、using子句

• 两表连接查询的类型：（不断优化的过程）

1、交叉连接 cross join

2、自然连接 natural join

3、内连接--using子句

4、内连接--on子句

上面四个综合来看内连接--on子句用的最多。

5、外连接

• 三表连接查询
• 自连接查询 

两表查询举例代码： 

-- 多表查询（emp表和dept表）
-- 99语法：
-- 交叉连接 cross join
select * 
from emp
cross join dept;-- 组合记录条数=14（emp记录条数）*4（dept记录条数）=56条结果，笛卡尔乘积：没有实际意义，只有理论意义
-- cross在mysql中可以省略不写，在oracle中不可以
select * 
from emp
join dept;

-- 自然连接：natural join
-- 自然连接优点：自动匹配所有的同名列，同名列只展示一次，简单
-- 自然连接缺点：自动匹配所有的同名列，但是有时候我们希望只匹配部分同名列
select * 
from emp
natural join dept;

-- 查询员工的编号、姓名、部门编号、部门名称
-- 员工的编号、姓名、部门编号在emp表中，部门名称在dept表中
select empno,ename,deptno,dname 
from emp
natural join dept;
-- 缺点：查询字段的时候没有指定字段所属的数据库表，效率低
-- 解决：指定表名
select emp.empno,emp.ename,emp.deptno,dept.dname
from emp
natural join dept;
-- 缺点：表名太长
-- 解决：给表起别名
select e.empno,e.ename,e.deptno,d.dname
from emp e
natural join dept d;

-- 解决自然连接natural join的缺点：基于内连接的using子句
select * 
from emp e
inner join dept d-- inner可以省略不写
using(deptno);-- 这里不能写natural join了，这里是内连接
-- 内连接using子句缺点：关联的字段必须是同名的
-- 解决：内连接中的on子句---on(表1.列名=表2.列名)
select * 
from emp e
inner join dept d
on(e.deptno=d.deptno);
-- 出现问题：
-- 1、40号部门没有员工，所以没有显示在查询结果中
-- 2、如果员工没有部门，也没有显示在查询结果中
-- 因此内连接缺点：有时候显示结果不全
-- 解决：外连接：除了显示匹配的数据外还可以显示一些不匹配的数据（outer可以省略）
-- 左外连接：left outer join
select * 
from emp e
left outer join dept d-- 左面表（此处是emp表）的信息即使不匹配也可以显示出
on e.deptno=d.deptno;

-- 右外连接：right outer join
select * 
from emp e
right outer join dept d-- 右面表（此处是dept表）的信息即使不匹配也可以显示出
on e.deptno=d.deptno;
 -- 全外连接：full outer join-- 这个语法在MySQL中不支持，在Oracle中支持
select * 
from emp e
full outer join dept d-- 左右表的信息即使不匹配也可以显示出
on e.deptno=d.deptno;
-- 解决MySQL中不支持全外连接的方法：”取并集“union、union all
select * 
from emp e
left outer join dept d-- 左面表（此处是emp表）的信息即使不匹配也可以显示出
on e.deptno=d.deptno 
union-- 并集 去重 效率低
select * 
from emp e
right outer join dept d-- 右面表（此处是dept表）的信息即使不匹配也可以显示出
on e.deptno=d.deptno ;

select * 
from emp e
left outer join dept d-- 左面表（此处是emp表）的信息即使不匹配也可以显示出
on e.deptno=d.deptno 
union all-- 并集 不去重 效率高
select * 
from emp e
right outer join dept d-- 右面表（此处是dept表）的信息即使不匹配也可以显示出
on e.deptno=d.deptno ;
-- mysal中对集合操作支持比较弱，只支持并集操作，交集、差集不支持，Oracle中支持

三表查询举例代码： 

-- 三表查询（emp表、dept表和salgrade表）
-- 查询员工的编号、姓名、薪水、部门编号、部门名称（dept表中）、薪水等级（salgrade表中）
select e.empno,e.ename,e.sal,e.deptno,d.dname,s.* 
from emp e
right outer join dept d
on e.deptno=d.deptno
inner join salgrade s
on e.sal BETWEEN s.losal and s.hisal;-- 把前面的关联结果当作一张表，再关联下一张表，以此类推....

自关联查询举例代码： 

-- 自连接查询：自关联，自己跟自己关联
-- 查询员工的编号、姓名、上级编号、上级姓名
select e1.empno 员工编号,e1.ename 员工姓名,e1.MGR 姓名编号,e2.ename 领导姓名 
from emp e1
inner join emp e2
on e1.mgr=e2.empno;
-- 最大领导即没有上级领导的员工没有显示出来
-- 解决:左外连接查询
select e1.empno 员工编号,e1.ename 员工姓名,e1.MGR 姓名编号,e2.ename 领导姓名 
from emp e1
left outer join emp e2
on e1.mgr=e2.empno;

二、SQL92语法：

92语法和99语法的差别：

1、92语法麻烦

2、92语法中表的连接条件和筛选条件是放在一起的没有分开

3、99语法中提供了更多查询连接类型：cross,natural,inner,outer.

-- 查询员工的编号、员工姓名、薪水、员工部门编号、部门名称
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d;
-- 相当于99语法中的cross join，出现笛卡尔积，没有意义

select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno;
-- 相当于99语法中的natural join

-- 查询工资大于2000的员工的编号、员工姓名、薪水、员工部门编号、部门名称：
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.sal>2000;
-- 92语法中表的连接条件和筛选条件是放在一起的没有分开

-- 查询员工的名字、岗位、上级编号、上级名称（自连接）
select e1.ename,e1.job,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno;

-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
select e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal>=s.losal and e.sal<=s.hisal;

三、例题：

1、求部门平均薪水的等级：

-- 部门平均薪水的等级√
select 部门名称,平均工资,s.grade '平均工资等级'
from (select d.dname '部门名称',avg(e.sal) '平均工资'
from emp e
inner join dept d
on(e.deptno=d.deptno)
group by e.deptno) av_sal
inner join salgrade s
on 平均工资 BETWEEN s.losal and s.hisal;

结果：

2、求部门平均的薪水等级：

-- 部门平均的薪水等级√
select dname 部门名称,avg(grade) 部门平均的薪水等级
from(select e.deptno,d.dname,s.grade 
from emp e
right outer join dept d
on e.deptno=d.deptno
inner join salgrade s
on e.sal BETWEEN s.losal and s.hisal) av_sal_grade
group by deptno;

结果：

 3、哪些人是领导：

-- 哪些人是领导？×
select ename 领导名字
from emp
where empno in
(select DISTINCT MGR from emp where MGR is not null);

结果：

 4、不用组函数求最高薪水*：

select sal 最高工资 from emp where sal not in 
(select distinct e1.sal from emp e1
 inner join emp e2
on (e1.sal<e2.sal));

结果：

5、平均薪水最高的部门编号：

select deptno
from (select deptno,avg(sal) as avg_sal from emp group by deptno) av_sal
where avg_sal =(select max(avg_sal)from (select deptno,avg(sal) as avg_sal from emp group by deptno) av_sal1);

结果：

 

6、平均薪水最高的部门编号和名称：

select deptno,dname from dept
where deptno=
(select deptno
from (select deptno,avg(sal) as avg_sal from emp group by deptno) av_sal
where avg_sal =(select max(avg_sal)from (select deptno,avg(sal) as avg_sal from emp group by deptno) av_sal1));

 

7、平均薪水的等级最低的部门的部门名称*：

太复杂了。。。。。放弃 

select 部门名称
from(select 部门名称,s.grade '平均工资等级'
from (select d.dname '部门名称',avg(e.sal) '平均工资'
from emp e
inner join dept d
on(e.deptno=d.deptno)
group by e.deptno) av_sal
inner join salgrade s
on 平均工资 BETWEEN s.losal and s.hisal) avg_grade
where 平均工资等级=(select min(平均工资等级)from
(select 部门名称,s.grade '平均工资等级'
from (select d.dname '部门名称',avg(e.sal) '平均工资'
from emp e
inner join dept d
on(e.deptno=d.deptno)
group by e.deptno) av_sal
inner join salgrade s
on 平均工资 BETWEEN s.losal and s.hisal) avg_grade1);

结果：