oracle经典SQL查询语句题稿.docVIP

Oracle查询语句 select * from scott.emp ; 1.--dense_rank()分析函数（查找每个部门工资最高前三名员工信息） select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) a from scott.emp) where a=3 order by deptno asc,sal desc ; 结果： --rank()分析函数(运行结果与上语句相同) select * from (select deptno,ename,sal,rank() over(partition by deptno order by sal desc) a from scott.emp ) where a=3 order by deptno asc,sal desc ; 结果： --row_number()分析函数（运行结果与上相同） select * from(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) a from scott.emp) where a=3 order by deptno asc,sal desc ; --rows unbounded preceding 分析函数（显示各部门的积累工资总和） select deptno,sal,sum(sal) over(order by deptno asc rows unbounded preceding) 积累工资总和 from scott.emp ; 结果： --rows 整数值 preceding(显示每最后4条记录的汇总值) select deptno,sal,sum(sal) over(order by deptno rows 3 preceding) 每4汇总值 from scott.emp ; 结果： --rows between 1 preceding and 1 following（统计3条记录的汇总值【当前记录居中】） select deptno,ename,sal,sum(sal) over(order by deptno rows between 1 preceding and 1 following) 汇总值 from scott.emp ; 结果： --ratio_to_report(显示员工工资及占该部门总工资的比例) select deptno,sal,ratio_to_report(sal) over(partition by deptno) 比例 from scott.emp ; 结果： --查看所有用户 select * from dba_users ; select count(*) from dba_users ; select * from all_users ; select * from user_users ; select * from dba_roles ; --查看用户系统权限 select * from dba_sys_privs ; select * from user_users ; --查看用户对象或角色权限 select * from dba_tab_privs ; select * from all_tab_privs ; select * from user_tab_privs ; --查看用户或角色所拥有的角色 select * from dba_role_privs ; select * from user_role_privs ; -- rownum:查询10至12信息 select * from scott.emp a where rownum=3 and a.empno not in(select b.empno from scott.emp b where rownum=9); 结果： --not exists；查询emp表在dept表中没有的数据 select * from scott.emp a where not exists(select * from scott.dept b where a.empno=b.deptno) ; 结果： --rowid;查询重复数据信息 select * from scott.emp a where a.rowid(select min(x.rowid) from scott.emp x wher