Oracle 查询

-- 集合操作
--union  并集 苏偶有内容都查询，重复的显示一次
select * from emp union select * from emp20;
--union all 并集 所有内容都显示 包括重复
select * from emp union all select * from emp20;
--intersect 交集
select * from emp intersect select * from emp20;
--minus 差集  只显示对方没有的 与顺序有关
select * from emp minus select * from emp20;


--子查询
--单行子查询 
select *from emp where sal>(select sal from emp where empno=7566);
--多行子查询
select * from emp where sal>any(select avg(sal) from emp group by deptno);
select * from emp where sal>all(select all(sal) from emp group by deptno);
select * from emp where job in (select job from emp where ename ='Martin' or ename ='Smith');
--top N 查询
select *from emp where rownum=1 or rownum=2;
select * from emp where rownum<=5;

--分页查询
select *
  from (select rownum no, e.*
          from (select * from emp order by sal desc) e
         where rownum <= 5)
 where no >= 3
 
 
 select *
   from (select rownum no, e.* from (select * from emp order by sal desc) e)
  where no >= 3
    and no <= 5
    
 --随即返回5条记录
select * from(select * from MEMBERINFO order by dbms_random.value())where rownum<=5;

--处理空值排序
select *from emp order by comm desc nulls last;
select *from emp order by comm desc nulls first;

--查询跳过表中的偶数行
select memberid from  (select row_number() over (order by memberid) rn ,memberid from MEMBERINFO ) x where mod(rn,2)=1

--查询所有员工信息与其中工资最高和最低工资
select ename ,sal max(sal) over(),min(sal) over() from emp;

--连续求和
select ename,sal ,sum(sal) over(),sum(sal) over (order by ename) from emp;

--分部门连续求和
select deptno,sal sum(sal) over(partition by deptno order by ename) as s from emp;
--得到当前行，上一行 和下一行的数据
select memberid,memberstate, lead(memberstate) over (order by memberid) aaa,lag(memberstate) over(order by memberid) bbb from MEMBERINFO;
--根据子串分组
select to_char(t.createdate,'yyyy'),avg(memberstate) from MEMBERINFO t group by to_char(t.createdate,'yyyy');
--确定一年内的天数
select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')from dual;

--查询某用户下所有表
select table_name from all_tables where owner='SCOTT'
--查询HEALTHMANAGEMENT 表中的所有字段（列）
select * from all_tab_columns where table_name='MEMBERINFO'

--列车表的索引值
select * from all_ind_columns where table_name ='MEMBERINFO'

--列出表的约束
select *from all_constraints where table_name='MEMBERINFO'
--在oracle 中描述数据字典的视图
select table_name ,comments from dictionary where table_name like '%TABLE%'