-- 集合操作--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%'