2016_SQL学习（1）

select deptno, job, sum(sal) from emp group by rollup(deptno, job);

break on deptno skip 2;

set pagesize 30;

title col 15 '' col 35 sql.pno

col deptno heading 部门号

col job heading 职位

col sum(sal) heading 工资总额

break on deptno skip 1

把以上内容保存在e:/oracle/salary.sql

get e:/oracle/salary.sql

@e:/oracle/salary.sql

select deptno, job, sum(sal) from emp group by rollup(deptno, job);

 

笛卡尔集

等值连接

不等值连接

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

外连接

自连接

 

层次查询

select level, e.empno, e.ename, e.sal, e.mgr from emp e connect by prior empno = mgr start with e.empno = 7839 order by 1;

 

分页查询

初步select所有待分页内容并order by

接着加rownum rn,且rownum<

最后rn>

(select rownum, empno, ename, sal from emp order by sal desc)