老方块sql总结

1  函数
initcap 第一个字母大写 其他小写
LOWER转换小写 ⊙ UPPER 转换成大写
2 substr  截取要查询的字段  从第二个位置开始查（如果是-1从后往前查），查询2位(可以省略）   
select * from zc_bdsqlb  where substr(YWBH,2,2)='JJ';
SELECT SUBSTR('oracle',-3,3)FROM dual;  结果是cle


3  select trunc(sysdate,'dd') from dual   返回当前的年月日  trunc 处理数字有round差不多，不同的就是不用四舍五入


4select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;


MONTHS_BETWEEN是查两个日期间相差的月数
select months_between(to_date('2013-01-01','yyyy-MM-dd'),to_date('2012-01-01','yyyy-MM-dd')) from dual;


4 加上一个月  select add_months(to_date('2012-04-30','yyyy-mm-dd'),1) from dual;  （很完美的加上了一个月）
结果是：2012/5/31
numtoyminterval得到的日期并不是下一个月的最后一天，而只是下一个月的同一天。  如果例如5月31号 下一个月没有31号就会报错的
增加年
select sysdate,sysdate+numtoyminterval(1,'year')   from dual;
5 select to_char(SYSDATE,'yyyy-mm-dd') from dual; 2016-08-01
select to_char(SYSDATE,'yyyy-fmmm-dd') from dual; 2016-8-1
select to_char(SYSDATE,'yyyy-fmmm-fmdd') from dual; 2016-8-01  负负得正
select to_char(SYSDATE,'yyyy-mm-fmdd') from dual; 2016-08-1  
6 select to_char(10000000,'L99,999,999.000') from dual;
L 可以显示当前的货币单位
         ￥10,000,000.000


7 查询今年的天数 
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual;


8查询今天是一年的第几天


select TO_CHAR(SYSDATE,'DDD'),sysdate from dual;


9 类型转换
Select cast(sysdate as varchar2(20)) from dual;


10 NVL(C,100)
如果字段C是空,那么设置成100,否则仍然是C
NVL2 (expr1, expr2, expr3)
如果expr1 是空，则返回expr3，不是空返回expr2
NULLIF (expr1, expr2)（异或）
1.如果两个值相同返回空
2.如果两个值不同就选取第1个表达式


11 select coalesce(null,1,2,3,4) from dual;  


返回第一个非空值表达式


12 连接相关


带+的填充NULL 
不带+的决定结果集




13 count函数 
count（*）与count(1)是一样的
⊙ COUNT(字段名)
返回由expr标识的列中非空值的数量
⊙ COUNT(distinc 字段名)
返回由expr标识的列中非空值的不重复值的数量




注：count(*) 只有在有索引并且索引是“非空”的时候才会走索引  
    count(索隐列)走索引 但还是走成本最低的索引
 注： 表中如果只有一字段 ，该字段是索引，且索引非空 会走全表扫描，因为 走索引更耗性能 
索引 要记录着一列的值 和物理地址，比表多出了个物理地址  例如计算面积的话  长相同 ，换多了个物理地址
索引是不记录NULL值的




COUNT(1)是无意义的，ORACLE内部会将COUNT(1)优化成COUNT(*)


（avg）就是分组函数不会计算空值 需要计算 另外处理


HAVING是分一个组后才有值.所以放在GROUP BY后（）     延：或者WHERE和GROUP BY之间


COUNT(非空列)是怎么样执行的?
选择成本最低的非空列索引




14  ORDER BY X NULLS FIRST   数据中含有控制可以先排列


15 统计近6个月的情况
max( case when                                                    
 months_between(sysdate,to_date(to_char(year_month),'yyyymm'))<=6 
    then x end ) max_6,  
15 横向比较
a  b  c  Row_max  
----------------  
1  2  3    3  <-- 
4  5  6    6  <-- 
select t.*, greatest( a,b,c) row_max from t; 求横向最大值  还有最小值least 函数
16select x from t order by lpad( x, 2 );


这个是右填充  不够2位的填充0




16 select x,z from t order by 3;


这是错误的，这个数字表示的位置必须以SELECT后面的位置来计算


17     如何实现这样的效果(外国人的名字分开）
1 WHITE L
2 KENNY L
3 ROBERT F
4 JOHN F
5 R M
6 U M




CREATE TABLE X (last_name varchar2(20),first_name varchar2(20),middle_name varchar2(20));


insert into x values('WHITE',       'ROBERT',            'R');
insert into x values('KENNY',       'JOHN',              'U');


select decode( type, 'L', last_name, 'F', first_name, 'M', middle_name ), 
       type
  from X, ( select 'L' type from dual UNION ALL
            select 'F' type from dual UNION ALL
            select 'M' type from dual );


17 除了&和&&来定义变量  区别 多个&需要输入多次 一个&&只需要输入一次


select * from ZC_YH  WHERE ROWNUM <=&ROWNUM


18 如何更改表的顺序
方法一：alter table t rename to t_table;
create table t as select x,y,z from t_table;
这种方法会产生大量的IO


方法二：创建视图（视图几乎是0IO）：alter table t rename to t_table;
create or replace view t as select x, y, z from t_table;


19 约束◎ NOT NULL◎ UNIQUE◎ PRIMARY KEY◎ FOREIGN KEY◎ CHECK
⊙ 约束可以强制在一个表的级别上，表中数据受限于一些约束。每种约束都有一种或者多种规则。
如数据相关性的一个主外键关联的两个父子表，约束将阻止父表记录的删除




20 视图 其实可以理解成封装好的一个表，不能存储数据，以SQL存在数据字典
 注意视图中 有A表  删除A表 后有重建了 视图不受影响    区别存储过程
21 rowNum的原理
rownum = 1                      
declare                                    
Rowno number := 1;                         
begin                                      
For x in ( select * from t )               
Loop                                       
if ( Rowno >= 5 )                          
   then                                    
      dbms_output.put_line(x.object_name); 
  Rowno := Rowno+1;                        
   end if;                                 
End loop;                                  
end;                                       
/




select * from t where rownum >=5 ; （×）
select * (select t.*,rownum rn from t) where rn>5（√）


21 分页的优化语句 （不用全表烧苗）


Select * from (select emp.*,rownum rnum from emp where rownum<=10) where rnum>=5;
22  索引与表的区别


我们的普通表叫堆表  
大家看过沙堆吧 一堆一堆的 其实表在数据文件中的存储也是这样一堆一堆的 每个堆可能是1个区，或者多个区 但是每个区与区间是不连续的
索引：它的数据构成类似目录 有KEY，有地址 KEY，就好比我们目录项，地址好比后面的页码


23 B树是2叉树 B- 不是二叉树了 子孙无联系  B+只有孙子有联系 B*子孙有联系