oracle数据库基本查询操作

一，oracle数据库基本查询

以oracle自带EMP表示为例

--降序
SELECT * FROM emp e ORDER BY  sal desc
--分页查询
select temp.* from （SELECT ROWNUM rn , e.* FROM emp e ORDER BY  sal desc）temp where ROWNUM between 0 and 3

--分页查询,查第二页
select * from emp order by sal desc;
select * from (select rownum rn ,temp.* from (select * from emp order by sal desc) temp where rownum<=6) where rn >=4

--排序
select rownu,e.*, rank() over (order by sal desc) as rank from emp e where;
select empno,deptno,ename,sal,DENSE_RANK() over(partition by deptno order by sal desc) as rank from emp;

--2,查询每个部门薪水第二高的员工基本信息（包含并列第二）
SELECT * FROM (
       SELECT E.*,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS DRANK FROM EMP E
) E
WHERE DRANK = 2;


--显示职员的就职年度
SELECT E.*,TO_CHAR(HIREDATE,'YYYY') 年度 FROM EMP

--使用round()函数将入职日期四舍五入到年份
select ename,round(to_char(hiredate,'yyyy'))年度 from emp e;

select deptno from emp group by deptno;
---列出至少有一个雇员的所有部门
select * from dept where deptno in (select deptno from emp group by deptno having count(*) >=1);


--列出薪资比Smith多的所有雇员
select * from emp where sal > (select sal from emp where ename = 'SMITH');

--列出所有“CLERK”（办事员）的姓名及其部门名称

select e.ename,d.dname,e.job from emp e,dept d where e.deptno = d.deptno and e.job ='CLERK';

-- 列出各种工作类别的最低薪金，显示最低薪金大于1500的记录
select job,min(sal) from emp group by  job having min(sal) > 1500;

--找出各月最后一天受雇的所有雇员
SELECT * FROM EMP WHERE HIREDATE = LAST_DAY(HIREDATE);