读了oracle查询与优化做一些笔记，方便以后查看

本文链接：https://blog.csdn.net/u011410254/article/details/50901026
<pre name="code" class="sql">1、coalesce 用法 ，该方法支持多个参数，能很方便的返回第一个不为空的值
  例如：
      Select coalesce(objectid , xm , age,classname,grade) As a From test;

      如果用nvl来实现：
      select nvl((nvl((nvl((nvl(objectid,xm),age),classname),grade) as A from test;
      明显可以看出 coalesce比nvl简单方便

2、sql 语句可以拼接一些其他的语句 ，来达到在页面上直接显示不需要处理
   例如：
     Select '我是' || xm ||',来自java'|| classname ||'班' As a From test;

     其实我们可以做更有意义的事情，例如生成SQl语句：
      select  'select *From '  || owner || '.' || table_name || ';' as 查询语句 from all_tables Where owner='TEST'
      
      结果是：select *From TEST.TEST;
     
      这样的场景在我们业务需求中确实存在的！

3、case when  的用法
   
    Select xm 姓名 ,grade As 成绩 ,
      Case  
         When grade <60 Then '不及格'  
         When grade >=80 Then '优秀'
         Else '中等'
      End As status
       From student 

       结果：
                1	zsy	70	中等
		2	jh	97	优秀
		3	xx	67	中等
		4	mm	88	优秀
		5	ll	45	不及格
     适合用于范围区间和报表之类的

4、
 translate的用法：
   语法格式：
         Translate(expr,from_string , to_string)
	 示例：
           select translate('赵守云  你好   hello ','赵守云hello' ,'江小红world') as new_str from dual
	   查询结果是：江小红  你好   worrd 
	   可以看出from_string 与 to_string 以字符为单位，对应字符一一替换

	   （1）如果to_string 为空字符串情况：
	         select translate('赵守云  你好   hello ','赵守云hello' ,'') as new_str from dual；
		 结果：是无字符串   当to_string为空则把全部替换为空，
             （2）如果to_string 的字符与expr不对应情况：
	         select translate('赵守云  你好   hello ','赵守云hello' ,'z') as new_str from dual；
		 结果：z  你好        

5、如果需求是以一个字段来排序，但是该字段存在null，并且要把空的排在前面：
     nulls first 和 nulls last 就比较好用了
   示例：
       select * from student order by 4  nulls first,3 nulls first 
       结果：
                1	kk			
		2	hh	23	女	
		3	ll	21	男	45
		4	xx	23	男	67
		5	zsy	22	男	70
		6	mm	25	女	88
		7	jh	21	女	97


6、explain plan 可以很好的查看 表的执行效率：
  示例：
        explain plan for 
            select * from LR l left join LR r on l.v = r.v;
         select * from table(dbms_xplan.display());
   结果：
        1	Plan hash value: 966592201
	2	 
	3	---------------------------------------------------------------------------
	4	| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
	5	---------------------------------------------------------------------------
	6	|   0 | SELECT STATEMENT   |      |     4 |   208 |     7  (15)| 00:00:01 |
	7	|*  1 |  HASH JOIN OUTER   |      |     4 |   208 |     7  (15)| 00:00:01 |
	8	|   2 |   TABLE ACCESS FULL| LR   |     4 |   104 |     3   (0)| 00:00:01 |
	9	|   3 |   TABLE ACCESS FULL| LR   |     4 |   104 |     3   (0)| 00:00:01 |
	10	---------------------------------------------------------------------------
	11	 
	12	Predicate Information (identified by operation id):
	13	---------------------------------------------------
	14	 
	15	   1 - access("L"."V"="R"."V"(+))
	16	 
	17	Note
	18	-----
	19	   - dynamic sampling used for this statement

	当然，通过plsql桌面查询也是可以的

7、 

 复制表sql:
     格式:
         create tableName1  as select * from tableName;
     示例：
          create emp1 as select * from emp;复制整个表，包括数据

	  create emp2 as select * from emp where 1=2 ; 复制表不包括数据

	  create emp 3 as select e.eno ,e.ename from emp e where e.eno = '2' 指定列来和条件复制


8、
  （1）、加减日、月、年 
       sql:
          select  e.hiredate as 聘用日期 , e.hiredate - 10/24/60/60 as 十秒 ,e.hiredate - 5 as 减五天,e.hiredate +5 as 加五天, add_months(e.hiredate ,+5) as 加五个月  from emp e;

	  结果：        聘用日期         减10s                   减五天         加五天            加五个月      加五年
	        1	1980/12/17	1980/12/16 23:59:50	1980/12/12	1980/12/22	1981/5/17	1985/12/17
		2	1981/2/20	1981/2/19 23:59:50	1981/2/15	1981/2/25	1981/7/20	1986/2/20
		3	1981/2/22	1981/2/21 23:59:50	1981/2/17	1981/2/27	1981/7/22	1986/2/22
		4	1981/4/2	1981/4/1 23:59:50	1981/3/28	1981/4/7	1981/9/2	1986/4/2
		5	1981/9/28	1981/9/27 23:59:50	1981/9/23	1981/10/3	1982/2/28	1986/9/28
		6	1981/5/1	1981/4/30 23:59:50	1981/4/26	1981/5/6	1981/10/1	1986/5/1
		7	1981/6/9	1981/6/8 23:59:50	1981/6/4	1981/6/14	1981/11/9	1986/6/9
		8	1987/4/19	1987/4/18 23:59:50	1987/4/14	1987/4/24	1987/9/19	1992/4/19
		9	1981/11/17	1981/11/16 23:59:50	1981/11/12	1981/11/22	1982/4/17	1986/11/17
		10	1981/9/8	1981/9/7 23:59:50	1981/9/3	1981/9/13	1982/2/8	1986/9/8
		11	1987/5/23	1987/5/22 23:59:50	1987/5/18	1987/5/28	1987/10/23	1992/5/23
		12	1981/12/3	1981/12/2 23:59:50	1981/11/28	1981/12/8	1982/5/3	1986/12/3
		13	1981/12/3	1981/12/2 23:59:50	1981/11/28	1981/12/8	1982/5/3	1986/12/3
		14	1982/1/23	1982/1/22 23:59:50	1982/1/18	1982/1/28	1982/6/23	1987/1/23

       从sql 语句可以看到，单独的加减是以为单位的，因此想操作到时分秒，就要进行处理就行了，，1/24是一小时  ，1/24/60一分钟 ，1/24/60/6O是一秒。
       如果是操作是月份，则需要用oracle的add_months(时间字段,±月份)就可以了！

   （2）、 日期间隔之时分秒
         sql:
	     with v as (select max(e.hiredate) - min(e.hiredate) as jgts from emp e where e.ename in ('WARD','ALLEN'))
             select jgts as 间隔天数 ,jgts*24 as 间隔小时 ,jgts *24*60 as 间隔分 ,jgts * 24*60*60 as 间隔秒 from v ;

         结果：
	       间隔天数   间隔小时  间隔分钟    间隔秒

	  1	  2	    48	      2880   	172800

	  通过结果可以看到，两个date相减得到的是天数，乘以24就是小时，以此类推 

   （3）、日期间隔之日月年

       sql ：
           with v as (select min(e.hiredate) as min_hd, max(e.hiredate) as max_hd from emp e)
           select (v.max_hd - v.min_hd) as 间隔天, months_between(v.max_hd,v.min_hd) as 间隔月, months_between(v.max_hd,v.min_hd)/12 as 间隔年  from v;
       结果：
                 间隔天    间隔月              间隔年
          1	  2348	  77.1935483870968	6.43279569892473

	  通过结论可以看到：计算月份的时候是用months_between(date1,date2) 就是间隔月

9、
   SYSDATE 得到的信息

     sql:
       with v as (select (e.hiredate+10/24/60/60 +20/24/60 +8/24 ) as hiredate from emp e)
       select  e.hiredate as 日期 ,
       to_char(e.hiredate ,'yyyy') 年,
       to_char(e.hiredate ,'mm') 月,
       to_char(e.hiredate ,'dd') 日,
       to_char(e.hiredate ,'hh24') 时,
       to_char(e.hiredate ,'mi') 分,
       to_char(e.hiredate ,'ss') 秒,
       to_char(e.hiredate ,'ddd') 年内第几天,    
       to_char(e.hiredate ,'day') 周几,  
       to_char(e.hiredate ,'yyyyMMddhh24miss') as 日期格式化,
       trunc(e.hiredate ,'dd') as 当天,
       trunc(e.hiredate,'day') as 周初,
       trunc(e.hiredate,'mm') as 月初,
       last_day(e.hiredate) as 月末,
       add_months(trunc(e.hiredate,'mm'),1) as 下月初,
       trunc(e.hiredate,'yy') as 年初,
       to_char(e.hiredate,'month') 月份 
       from v e

       结果： 
                 日期                   年      月      日     时       分      秒  年内第几天  周几     日期格式化     当天            周初            月初             月末                   下月初           年初            月份
        1	1980/12/17 8:20:10	1980	12	17	08	20	10	352	星期三	19801217082010	1980/12/17	1980/12/14	1980/12/1	1980/12/31 8:20:10	1981/1/1	1980/1/1	12月
	2	1981/2/20 8:20:10	1981	02	20	08	20	10	051	星期五	19810220082010	1981/2/20	1981/2/15	1981/2/1	1981/2/28 8:20:10	1981/3/1	1981/1/1	2月 
	3	1981/2/22 8:20:10	1981	02	22	08	20	10	053	星期日	19810222082010	1981/2/22	1981/2/22	1981/2/1	1981/2/28 8:20:10	1981/3/1	1981/1/1	2月 
	4	1981/4/2 8:20:10	1981	04	02	08	20	10	092	星期四	19810402082010	1981/4/2	1981/3/29	1981/4/1	1981/4/30 8:20:10	1981/5/1	1981/1/1	4月 
	5	1981/9/28 8:20:10	1981	09	28	08	20	10	271	星期一	19810928082010	1981/9/28	1981/9/27	1981/9/1	1981/9/30 8:20:10	1981/10/1	1981/1/1	9月 
	6	1981/5/1 8:20:10	1981	05	01	08	20	10	121	星期五	19810501082010	1981/5/1	1981/4/26	1981/5/1	1981/5/31 8:20:10	1981/6/1	1981/1/1	5月 
	7	1981/6/9 8:20:10	1981	06	09	08	20	10	160	星期二	19810609082010	1981/6/9	1981/6/7	1981/6/1	1981/6/30 8:20:10	1981/7/1	1981/1/1	6月 
	8	1987/4/19 8:20:10	1987	04	19	08	20	10	109	星期日	19870419082010	1987/4/19	1987/4/19	1987/4/1	1987/4/30 8:20:10	1987/5/1	1987/1/1	4月 
	9	1981/11/17 8:20:10	1981	11	17	08	20	10	321	星期二	19811117082010	1981/11/17	1981/11/15	1981/11/1	1981/11/30 8:20:10	1981/12/1	1981/1/1	11月
	10	1981/9/8 8:20:10	1981	09	08	08	20	10	251	星期二	19810908082010	1981/9/8	1981/9/6	1981/9/1	1981/9/30 8:20:10	1981/10/1	1981/1/1	9月 
	11	1987/5/23 8:20:10	1987	05	23	08	20	10	143	星期六	19870523082010	1987/5/23	1987/5/17	1987/5/1	1987/5/31 8:20:10	1987/6/1	1987/1/1	5月 
	12	1981/12/3 8:20:10	1981	12	03	08	20	10	337	星期四	19811203082010	1981/12/3	1981/11/29	1981/12/1	1981/12/31 8:20:10	1982/1/1	1981/1/1	12月
	13	1981/12/3 8:20:10	1981	12	03	08	20	10	337	星期四	19811203082010	1981/12/3	1981/11/29	1981/12/1	1981/12/31 8:20:10	1982/1/1	1981/1/1	12月
	14	1982/1/23 8:20:10	1982	01	23	08	20	10	023	星期六	19820123082010	1982/1/23	1982/1/17	1982/1/1	1982/1/31 8:20:10	1982/2/1	1982/1/1	1月 
	      

10、分页模板

   sql:

         select *
      from (select rownum as rn, n.empno, n.ename, n.sal, n.job
              from (select e.empno, e.ename, e.sal, e.job
                      from emp e
                     order by e.sal desc) n) m
     where m.rn > 3
       and m.rn < 9;


11.计算简单的小计
   一般我们做报表时都会有一个总合计 用ROLLUP就可以达到这个目的
       sql :
             select  e.ename , sum(e.sal)as s_sal  from emp e group by  Rollup(e.ename);
       结果：
            
		1	ADAMS	1100
		2	ALLEN	1600
		3	BLAKE	2850
		4	CLARK	2450
		5	FORD	3000
		6	JAMES	950
		7	JONES	2975
		8	KING	5000
		9	MARTIN	1250
		10	MILLER	1300
		11	SCOTT	3000
		12	SMITH	800
		13	TURNER	1500
		14	WARD	1250
		15		29025

		看到第15行 了吧 ，，就是对上面的合计


12，通过分析函数计算

 分析函数实在查询结果的基础上进一步分析的，，例如：
    sql:
        select e.*,max(e.sal) over (partition by e.job)  as v from emp e

	这句话的意思是在select * from emp 的结果基础进行job列进行找出最大值，看结果：
	                                job                                                     maxSal
	        1	7788	SCOTT	ANALYST	7566	1987/4/19	3000.00		20	3000
		2	7902	FORD	ANALYST	7566	1981/12/3	3000.00		20	3000
		3	7934	MILLER	CLERK	7782	1982/1/23	1300.00		10	1300
		4	7900	JAMES	CLERK	7698	1981/12/3	950.00		30	1300
		5	7369	SMITH	CLERK	7902	1980/12/17	800.00		20	1300
		6	7876	ADAMS	CLERK	7788	1987/5/23	1100.00		20	1300
		7	7698	BLAKE	MANAGER	7839	1981/5/1	2850.00		30	2975
		8	7566	JONES	MANAGER	7839	1981/4/2	2975.00		20	2975
		9	7782	CLARK	MANAGER	7839	1981/6/9	2450.00		10	2975




13、case when 的用法
   select e.empno , 
  max(case when e.deptno='10' then e.ename end ) as a,
  max(case when e.deptno='20' then e.ename end ) as b,
  max(case when e.deptno='30' then e.ename end ) as c
 from emp e group by e.empno;