--多表查询
--多表查询的基本语法
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;
--多表查询的基本语法
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;