Oracle多表查询

--多表查询


--多表查询的基本语法


select count(*) from emp;


select count(*) from dept;


select * from emp,dept;


select * from emp,dept where emp.deptno=dept.deptno;


--多表查询实例


select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno;
 
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;


select e.empno,e.ename,e.hiredate,e.sal,s.grade 
from emp e,salgrade s 
where e.sal
between s.losal and s.hisal;


select e.empno,e.ename,e.hiredate,e.sal,
decode(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资') grade
from emp e, salgrade s where e.sal between s.losal and s.hisal;


select e.ename,e.job,e.sal from emp e;


select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;


select e.ename,e.job,e.sal,d.dname,s.grade,
decode(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资') grade
from emp e,dept d, salgrade s
where e.deptno= d.deptno and e.sal between s.losal and s.hisal;


--表的连接


insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8888,'李兴华','CLERK',7369,sysdate,800,100,null);


select * from emp;


select * from emp e,dept d where e.deptno=d.deptno;


select * from emp e,dept d where e.deptno=d.deptno(+);


select * from emp e,dept d where e.deptno(+)=d.deptno;


--自身关联


select * from emp;


select e.empno eno,e.ename ename,m.empno mno,m.ename
from emp e,emp m
where e.mgr= m.empno;


select e.empno eno,e.ename ename,m.empno mno,m.ename
from emp e,emp m
where e.mgr= m.empno(+);


select e.empno,e.ename,e.hiredate,e.sal,(e.sal+nvl(e.comm,0))*12 income
from emp e
where to_char(e.hiredate,'yyyy')='1981' and e.sal between 1500 and 3500;


select e.empno,e.ename,e.hiredate,e.sal,(e.sal+nvl(e.comm,0))*12 income,
m.ename mename
from emp e,emp m
where to_char(e.hiredate,'yyyy')='1981' 
and e.sal between 1500 and 3500
and e.mgr=m.empno(+);


select e.empno,e.ename,e.hiredate,e.sal,(e.sal+nvl(e.comm,0))*12 income,
m.ename mename,
d.deptno,d.dname,d.loc
from emp e,emp m,dept d
where to_char(e.hiredate,'yyyy')='1981' 
and e.sal between 1500 and 3500
and e.mgr=m.empno(+)
and e.deptno=d.deptno;
 
select e.empno,e.ename,e.hiredate,e.sal,(e.sal+nvl(e.comm,0))*12 income,
m.ename mename,
d.deptno,d.dname,d.loc,
s.grade,decode(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资') 工资等级
from emp e,emp m,dept d, salgrade s
where to_char(e.hiredate,'yyyy')='1981' 
and e.sal between 1500 and 3500 
and e.mgr=m.empno(+)
and e.deptno= d.deptno
and e.sal between s.losal and s.hisal;


select e.empno,e.ename,e.hiredate,e.sal,(e.sal+nvl(e.comm,0))*12 income,
m.ename mename,
d.deptno,d.dname,d.loc,
s.grade,decode(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资') 工资等级
from emp e,emp m,dept d, salgrade s
where to_char(e.hiredate,'yyyy')='1981' 
and e.sal between 1500 and 3500 
and e.mgr=m.empno(+)
and e.deptno= d.deptno
and e.sal between s.losal and s.hisal
order by income desc,e.job;


--SQL:1999语法


select * from emp cross join dept;


select * from emp natural join dept;


select * from emp join dept using(deptno);


select * from emp e join salgrade s on(e.sal between s.losal and s.hisal);


select * from emp e left outer join dept d on(e.deptno=d.deptno);


select * from emp e right outer join dept d on(e.deptno=d.deptno);


select * from emp e full outer join dept d on(e.deptno=d.deptno);


--数据的集合运算


select * from dept;


select * from dept union select * from dept where deptno=10;


select * from dept union all select * from dept where deptno=10;


select * from dept minus select * from dept where deptno=10;


select * from dept intersect select * from dept where deptno=10;