oracle学习笔记(2)

连接
自然连接：自动连接多个表中相同列名，当有多个相同列名时，返回为全部相同列的连接比较
select ename, deptno, dname from emp natural join dept;
产生笛卡尔积
select col1, col2 from table1 cross join table2;
外连接
a和b进行连接,如果要完全显示a，就在b条件上加一个(+),不带+的表完全显示
select * from emp e,dept d where e.deptno(+)=d.deptno;
左外连接：select e.name, e.depno, d.dname from dept d, emp e where d.depno (+) = e.depno;
from emp e left outer join dept d on d.deptno = e.deptno
右外连接：select e.name, e.depno, d.dname from dept d, emp e where d.depno = e.depno (+);
from emp e right outer join dept d on d.deptno = e.deptno
全外连接：select e.name, e.depno, d.dname from emp e full outer join dept d on d.depno = e.depno;
自连接：select e.empno, e.ename from emp e, emp e2 where e.empno <> e2.empno and e.ename = e2.ename;

组函数
一般会忽略NULL值，可用NVL处理
count(rowid) --更快，不使用*
select custid, count(custid) from group_subscriber group by custid; --查找不同的custid，和其每个custid的数目
select deptno, job, avg(sal) from emp group by deptno. job order by 3 desc;
select deptno, job, avg(sal) from emp group by deptno. job order by rollup(deptno, job); --对查找出的数据做小计
select deptno, job, avg(sal) from emp group by deptno. job order by cube(deptno, job); --分别做小计,按排列的顺序

子查询
子查询只向父查询返回非NULL值
select deptno, job, avg(sal) from emp group by deptno,job having avg(sal) > (select sal from emp where ename='MAR');
--减少重复查询
with summary as
(select dname, sum(sal) as dept_total from emp, dept where emp.deptno = dept.deptno group by dname)
select dname, dept_total from summary where dept_total > (select sum(dept_total)*1/3 from summary) order by dept_total desc;
--from子句中的子查询
select e.ename, subq.loc from emp e, (select deptno, loc from dept where loc in('New York', 'DALL')) subq
where e.deptno = subq.deptno
select ename, job, sal, rownum from (select ename, job, sal from emp order by sal) where rownum <= 3;
关联子查询:在子查询中需要利用"主查询表别名.列名"来引用主查询当前行的值
--查询大于平均工资的人
select * from emp where sal>(select avg(sal) from emp);
--查找所有工资高于自己本部门平均工资的人
select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
--查找每个部门工资最高的人的详细资料
select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno);
--显示每个部门的信息和人员数量
select d.*,(select count(*) from emp where deptno=d.depno) tot from dept d;
--更新no_of_per为该员工工资在本部门的排名
update myemp m set no_of_sal=(select count(*)+1 from myemp where sal>m.sal and deptno=m.deptno);
--查找入职日期早于其直接上级的员工：
select * from emp e where hiredate<(select hiredate from emp where empno=e.mgr);