1.内连接(inner join...on)
关键字:[inner] join ... on 关联条件
定义:按照关联条件将两张表关联,只显示匹配成功(满足关联条件)的数据,匹配不成功的不显示
语法:
select distinct 查询展示的内容
from 表名1 inner join 表名2 on 关联条件
inner join 表名3 on 关联条件 ...
where 分组前的过滤条件
group by 分组的内容 having 分组后的过滤条件
order by 排序的内容 asc|desc;
执行顺序:from - on -join -where - group by -having -select -distinct -order by
2.外连接
左外连接(left join...on)
关键字:left [outer] join ... on 关联条件
定义:将两张表按照关联条件进行关联,左表作为主表,左表的数据要全部展示,匹配不成功的数据,右表(匹配 表)以空值填充。
语法:表名1 left join 表名2 on 关联条件 ---left join 前边的表为主表,左表
条件放在 on 和 where 后边的区别
1.on后边是关联条件,where后边是过滤条件,对已经关联好的表进行过滤。
2.条件放在on后边,如果是左(右)连接,左(右)表数据全部展示,右(左)表匹配不成功的时候以空值填充。
如果是内连接,放在on和 where 后边 没有区别。
右外连接(right join...on)
关键字:right [outer] join ... on 关联条件
全外连接(full join...on)
关键字:full join ... on 关联条件
定义:将两张表按照关联条件进行关联,两张表的数据都会全部展示,匹配不成功的数据,对应的表以空值填充
3.交叉连接(相乘)
定义:两张表的数据交叉相乘,不需要关联条件,等同于 笛卡尔积
例子:
select * from emp,dept where emp.deptno=dept.deptno; ---等价于 内连接
select * from emp,dept where emp.deptno=dept.deptno(+);---等价于 左连接
select * from emp,dept where emp.deptno(+)=dept.deptno;---等价于 右连接
select * from emp,dept;----交叉连接 、笛卡尔积的简写
4.特殊连接
4.1自然连接
定义:两张表自动寻找关联字段,将两张表进行关联,只显示匹配成功的数据,去掉重复字段
关键字:natural join
4.2 自连接
没有关键字
定义:是表连接的一种特殊形式,一张表自己和自己关联,没有关键字
4.3 不等值连接
定义:也是表连接的一种特殊形式,关联条件里边不用等号
5.各种连接的特点
内连接:按照关联条件关联,将两表中都符合关联条件(匹配成功的)的数据展示出来;
左外连接:按照关联条件关联,左表作为主表,将左表所有数据展示出来,右表(匹配表)中不满足关联条件的以空值填充;
右外连接:按照关联条件关联,右表作为主表,将右表所有数据展示出来,左表(匹配表)中不满足关联条件的以空值填充;
全外连接:将两张表按照关联条件关联,两张表的数据全部展示,匹配不成功的数据,对应的表以空值填充;
笛卡尔连接:没有关联条件,左表中的每一条数据都与右表中的每一条数据做了一次关联
例题:输出员工编号为 7654 的员工的 名字和工资等级
select t1.ename, t3.grade
from emp t1
left join salgrade t2
on t1.sal between t2.losal and t2.hisal
where t1.empno=7654;
练习
--1、列出与"SCOTT" 从事相同工作的所有雇员
select t2.ename
from emp t1 ---scott所在的表
inner join emp t2 ---和scott岗位相同人所在的表
on t1.job = t2.job
and t1.ename = 'SCOTT' and t1.ename<>t2.ename;
select *
from emp t1
inner join (select job from emp where ename = 'SCOTT') t2
on t1.job = t2.job and t1.ename<>'SCOTT';
--2、列出所有员工的姓名及其直接上级的姓名
select t1.ename, t2.ename
from emp t1
left join emp t2
on t1.mgr = t2.empno;
--3、列出工资高于公司平均水平的所有员工
select t1.ename
from emp t1
inner join (select avg(sal) pjgz from emp) t2
on sal > pjgz;
--4、列出入职日期早于其直接上级的所有雇员
select t1.ename
from emp t1
inner join emp t2
on t1.mgr = t2.empno
and t1.hiredate < t2.hiredate;
--5、查询工资大于10号部门平均工资的员工信息
select *
from emp t1
inner join (select avg(sal) pjgz from emp where deptno = 10)
on t1.sal > pjgz;
--6.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
select t2.ename, t2.sal, t3.dname
from emp t1 --SMITH 所在的表
inner join emp t2 --工资比SMITH高的人
on t2.sal > t1.sal and t1.ename = 'SMITH'
inner join dept t3 --工资比SMITH高的人的部门信息
on t3.deptno = t2.deptno and loc = 'CHICAGO';
select t1.ename, t1.sal, t2.dname
from emp t1
inner join dept t2
on t1.deptno = t2.deptno
and t1.sal > (select sal from emp where ename = 'SMITH')
and t2.loc = 'CHICAGO';
select t1.ename,t1.sal,t2.dname
from emp t1
inner join (select * from dept where loc = 'CHICAGO') t2
on t1.deptno=t2.deptno
inner join (select sal from emp where ename='SMITH') t3
on t1.sal>t3.sal;
--7.查询部门人数大于所有部门平均人数的 部门编号,部门名称,部门人数
--方法一: 先关联再分组
select t1.deptno, t2.dname, count(1)
from emp t1
join dept t2
on t1.deptno = t2.deptno
group by t1.deptno,t2.dname
having count(1) > (select count(1) from emp)/ (select count(1) from dept);
--方法二:先分组判断人数 再关联部门表
select t1.deptno, t1.dname, t2.rs
from dept t1
inner join (select deptno, count(1) as rs
from emp
group by deptno
having count(1) > (select count(1) from emp) / (select count(1)
from dept)) t2
on t1.deptno = t2.deptno; --3.5
--方法三:
select *
from (select deptno, count(1) bmrs from emp group by deptno) t1
inner join dept t2
on t1.deptno = t2.deptno
join (select avg(count(1)) rs from emp group by deptno) t3
on t1.bmrs > t3.rs;
--方法四:
select distinct t1.deptno, dname,(select count(1) from emp where deptno= t1.deptno)
from emp t1
join dept t2
on t1.deptno = t2.deptno
where (select count(1) from emp where deptno= t1.deptno) >
(select avg(count(1)) from emp group by deptno);
select distinct t1.deptno,
dname,
(select count(1) from emp where deptno = t1.deptno)
from emp t1
inner join dept t2
on t1.deptno = t2.deptno
where (select count(1) from emp where deptno = t1.deptno) >
((select count(1) from emp) /
(select count(1) from (select distinct deptno from emp)));
--所有部门平均人数:
select (select count(1) from emp)/(select count(1) from dept) from dual;
select avg(count(1)) from emp group by deptno --->4.6
select avg(c) from (select count(1) c from emp group by deptno);
--8.列出至少有三个员工的所有部门和部门信息。
select t2.*
from emp t1
left join dept t2
on t1.deptno = t2.deptno
group by t2.deptno,t2.dname,t2.loc
having count(1) >= 3;
select t2.deptno,t2.dname,t2.loc,count(1)
from emp t1
inner join dept t2
on t1.deptno=t2.deptno
group by t2.deptno,t2.dname,t2.loc
having count(1)>=3;
select t1.*
from dept t1
join (select deptno from emp group by deptno having count(1) >= 3) t2
on t1.deptno = t2.deptno
select distinct t2.*, (select count(1) from emp where deptno = t1.deptno)
from emp t1
inner join dept t2
on t1.deptno = t2.deptno
where (select count(1) from emp where deptno = t1.deptno) >= 3;
--9.列出职位为“CLERK”的员工姓名和其所在部门名称,部门人数。
select ename, dname, (select count(1) from emp where deptno = t1.deptno)
from emp t1
inner join dept t2
on t1.deptno = t2.deptno
where job = 'CLERK';
select t1.ename,t3.dname,t2.rs
from emp t1
inner join (select deptno,count(1) rs from emp group by deptno) t2
on t1.deptno=t2.deptno
inner join dept t3
on t1.deptno = t3.deptno
where t1.job = 'CLERK';
多表连接综合练习:
--1、列出与"SCOTT" 从事相同工作的所有雇员
select t1.ename from emp t1inner join (select job from emp where ename='SCOTT') t2on t1.job = t2.job
--2、列出所有员工的姓名及其直接上级的姓名
select t1.ename,t2.dname
from emp t1 inner join emp t2
on t1.mgr = t2.empno
--3、列出工资高于公司平均水平的所有员工
select * from emp t1
inner join (select avg(sal) pjgz from emp ) t2
on t1.sal>pjgz
--4、列出入职日期早于其直接上级的所有雇员
select *
from emp t1
inner join (select hiredate,deptno from emp t2 group by deptno having empno = mgr)
on t1.deptno=t2.deptno
where t1.hiredate<t2.hiredate
--5、查询工资大于10号部门平均工资的员工信息
select * from emp t1
inner join (select avg(sal) pjgz from emp where deptno=10) t2
on t1.sal>pjg
--6.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
select t1.ename,t1.sal,t1.deptno
from emp t1
inner join emp t2
on t2.sal>t1.sal and t1.ename='SMTTH'
inner join dept t3
on t3.deptno =t2.deptno and loc='CHTCAGO'
--7.查询部门人数大于所有部门平均人数的 部门编号,部门名称,部门人数
select t1.deptno,t1.empno,t2.s
from emp t1
inner join (select count(1) s from emp group by deptno) t2
on t2.s>(select avg(count(1)) from emp group by deptno)
--8.列出至少有三个员工的所有部门和部门信息。
select t1.*,t2.s
from dept t1
inner join (select deptno,count(1) s from emp group by deptno) t2
on t2.s>=3 and t1.deptno=t2.deptno
--9.列出职位为“CLERK”的员工姓名和其所在部门名称,部门人数。
select t1.ename,t2.s,t3.dname
from emp t1
inner join (select deptno,count(1) s from emp group by deptno) t2
on job='CLERK' and t1.deptno=t2.deptno
inner join dept t3
on t3.deptno =t2.deptno;