07-oracle多表查询
--笛卡尔积,多表查询时,n张表中的行数相乘(本例中14*4=56)
--多表查询时笛卡尔积无法消除,即使使用了限定条件(where)也只是不显示而已,实际上笛卡尔积仍存在
--只能使用合理的做法来处理多表查询。
--多表查询时,每当增加一张关联表时都需要设置一个消除笛卡儿积的条件。
select count(*) from emp;
14
select count(*) from dept;
4
select count(*) from emp,dept;
--查询员工姓名,工作,工资等级编号,工资等级范围
select e.ename,e.job,e.sal,s.grade,s.losal,s.hisal
from emp e,salgrade s
where e.sal between s.losal and s.hisal
--查询员工姓名,工作,工资等级编号,工资等级范围
--将等级用中文显示(如:<1200的为E等工资)
select e.ename,e.job,e.sal,s.grade,s.losal,s.hisal,
decode(s.grade,
1,'E等',
2,'D等',
3,'C等',
4,'B等',
5,'A等'
) 工资等级
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
--内连接:等值连接,不显示不匹配的行。
--外连接:左外连接,右外连接,全外连接,通过(+)符号进行控制
--emp插入部门为null的‘中华’员工信息
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values('8888','中华','manager','9888',to_date('2019/1/1','yyyy-mm-dd'),'5000','1000',null);
commit;
--通过deptno关联查询时由于用户‘中华’的deptno为null,所以结果中未显示此用户的信息
select *
from emp e,dept d
where e.deptno=d.deptno;
--通过左连接,可以将没有关联的‘中华’用户的信息也显示出来
select *
from emp e,dept d
where e.deptno=d.deptno(+);
--右外连接,无用户属于dept表中deptno为40的部门,通过e.deptno=d.deptno时由于无匹配关联,所以deptno为40的行不会显示,通过右外连接显示此部门信息
-
--自连接,查询emp员工对应领导的编号和领导姓名,因为king无领导若不采用外连接(+),king的信息会不显示
select * from emp;
select e.ename,e.job,e.empno,e.mgr,m.empno,m.ename
from emp e,emp m
where e.mgr=m.empno(+);
--分析要求,逐步查出
select e.empno,e.ename,to_char(e.hiredate,'yyyy-mm-dd') hiredate,e.job,
m.ename,e.sal,nvl2(e.comm,e.sal+e.comm,e.sal)*12 income,
decode (s.grade,'1','A',2,'B',3,'C',4,'D') Slevel,d.deptno,d.dname,d.loc
from emp e,emp m,salgrade s,dept d
where /*e.hiredate like '%81%'*/ to_char(e.hiredate,'yyyy')='1981'
and e.mgr=m.empno
and e.sal between s.losal and s.hisal
and e.deptno=d.deptno
and e.sal between 1500 and 3500
order by income desc, e.job;
--SQL1999语法:
--emp 表有14行数据,dept表有4行数据
--交叉连接 cross join功能:产生笛卡儿积
select * from emp cross join dept;
--自然连接:natural join功能:消除笛卡儿积,等值字段放在查询结果的第一列
select * from emp natural join dept;
--using:没有关联的多表查询时,using将表关联并消除笛卡儿积
select * from emp join dept using(deptno)
--on:
select * from emp e join salgrade s on (e.sal between s.losal and s.hisal);
--全外连接:
--左外连接2种写法
select *
from emp e,dept d
where e.deptno=d.deptno(+);
--sql1999语法左外连接:
select * from emp e left outer join dept d on (e.deptno=d.deptno);
--sql1999语法右外连接:
select * from emp e right outer join dept d on (e.deptno=d.deptno);
--sql1999语法全外连接:
select * from emp e full outer join dept d on (e.deptno=d.deptno);
--建议使用union或union all 替代or
--集合操作时查询返回的数据结构要求一致
--数据的集合运算
--并集操作union(不显示重内容):返回若干个查询结果的全部内容,重复部分不显示
select * from dept
union
select * from dept d where d.deptno='10'
--并集操作union all(显示重复内容):返回若干个查询结果的全部内容,显示重复行
select * from dept
union all
select * from dept t where t.deptno='10';
--minus 差集:显示2个查询语句结果不同的内容
select * from dept
minus
select * from dept t where t.deptno='10';
--intersect 交集:只显示2个查询语句结果相同的内容
select * from dept
intersect
select * from dept t where t.deptno='10'