oracle -----多表查询

[code]
如果要使用多表查询的时候，最好指定别名
inner join 是默认的连接方式，可缩写为join
left outter join可用left join代替。在有些数据库中，如HSqlDb, 只能使用left join而不能使用left outter join。所以用缩写更好

emp{empno,ename,job,mgr(雇员的领导的编号)，hiredate,sal,comm,deptno}
dept{deptno,dname,loc}
salgrade{grade,losal,hisal}
bonus{ename,job,sal,comm}

查询出每个雇员的姓名，工资，部门名称，工资等级（salgrade）,及领导的姓名以及领导工资所在公司等级


思路：分解，查出每个雇员的姓名，工资，部门名称，工资在公司的等级
select e.ename,e.sal,d.ename,s.grade
from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal between losal and hisal
分解：领导的姓名以及领导工资所在公司等级
select e.ename,e.sal,d.ename,s.grade,m.ename,m.sal,ms.grade
from emp e,dept d,salgrade s，emp m,salgrade ms
where e.deptno = d.deptno and e.sal between losal and hisal
and e.mgr = m.empno and m.sal between ms.losal and ms.hisal;


查询雇员名称以及每一个雇员的领导（注意，最高级的雇员肯定没有领导，那么最高级的雇员的领导需要用空来填充）
select e.ename,m.ename
from emp e,emp m
where e.mgr = m.empno(+); //这是左连接，也就是左边连过来的，如果碰到了右边没有，那么他只好连接一个空

//下面的只要了解
select * from emp,dept; //这个笛卡尔积的另一种写法是： select * from emp CROSS JOIN dept;就是把","换成了CROSS JOIN

自然连接：
select a.ad_ph_telePhone,e.email_account,m.menber_name from addressandphone as a, email as e, menber as m where a.menber_Id=e.menber_Id and a.menber_Id=m.menber_Id;

也可以通过natural join 联合查询：

select a.ad_ph_telePhone,e.email_account,m.menber_name from addressandphone as a natural join email as e natural join menber as m ;

如果用natural join 你写的东西会少很多，不过使用natural join 有两点是要注意：

1. natural join 的原理是根据两个表之间相同字段名的列表把两个表联合起来，同时去掉这个字段中不同值的部分，如果两个表都有一个password列，而这两个password记录的内容是不一样的话，那么联合起来就不符合你的意思，所以表中的字段名最好在前面加上表名，这样就不会搞乱。

2.通过natural join出来的字段必定是不空的，比如a.ad_ph_telePhone,e.email_account,m.menber_name 中有一个列没有值的话就会得不到结果，所以如果你想查找出来的结果允许有空值的话，就不适合用natrual join 来实现




USING子句
select * from emp e JOIN dept d USRING(deptno)
where deptno = 30;
ON子句： 表示自己编写条件
select * from emp e JOIN dept d ON(e.deptno = d.deptno)
where e.deptno = 30;

左连接又叫左外连接(所谓外连接，就是可以允许值是不匹配的，也就是可以匹配空值)
select *
from emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno)


-------------------------------------------------------------------------------------
emp{empno,ename,job,mgr(雇员的领导的编号)，hiredate,sal,comm,deptno}
dept{deptno,dname,loc}
salgrade{grade,losal,hisal}
bonus{ename,job,sal,comm}
列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

select j.job , count(e.empno)
from emp e,
(select job from bonus
group by job having min(sal) > 1500) j
where e.job = j.job
group by j.job

方法二：
select e.job count(e.empno)
from emp e
where job in
(select job from bonus
group by job having min(sal) > 1500)
group by e.job

学习： 字表可以作为连接表，也可以作为过滤条件，因为在emp表中有题目中的两个字段，所以用第二种方法更加优秀。。。

列出薪金高于公司平均薪金的所有员工，所在部门，上级领导(的名称)，公司的工资等级

select e.ename,d.dname , m.ename,s.grade
from emp e,dept d ,salgrade s，emp m
where e.deptnp = d.deptno
and sal >( select AVG(sal) from emp)
and e.mgr = m.empno(+)
and e.sal between losal and hisal

我们在连接表的时候一定要问问，可以为空吗。。


列出与'SCOTT'从事相同工作的所有员工及部门名称 ,注意：这句话的意思是scott的同事，所以不包括scott
select e.ename ,e.empno ,d.dname
from emp e,dept d
where emp.deptno = d.deptno and
job = (select job from emp where ename = 'SCOTT' )
and ename <>'SCOTT'


[/code]