蜀中猿从学oracle，02，时间2015-5-15

oracle基本函数

字符函数

1、ASCII(<A>)，返回第一个字母的ASCII码值，逆函数是CHR()
2、CHR(<A>)，A为数字，返回十进字字符。例：
select ascii('A') from dual;返回65。
select chr('65') from dual;返回A。
3、CONCAT(A,B)，A、B字符串相连接。
例：select concat('A','B') from dual;返回AB。
4、INITCAP(A)，A为字符串，以第一个字母大写返回，其它字母以小写返回。例：
select initcap('abCdEFg') from dual;返回Abcdefg。
5、INSTR(A,B,C,D)， AB为字符串，CD为整数，返回B在A中的第D次出现的位置，从A的C个字符开始，从左到右进行，CD默认为1。
6、INSTRB，与INSTR类型类似，区别，instr是文本方式搜索，instrb是二进制方式搜索，
例：instr是文本方式搜索，instrb是二进制方式搜索
   select INSTRB('你好你好你好','好',1,1) from dual; 执行结果：4（字符集不一样，会导致执行结果不同，此结果是在字符集SIMPLIFIED CHINESE_CHINA.AL32UTF8下执行）
   select INSTR('你好你好你好','好',1,1) from dual;  执行结果：2
7、LENGTH与LENGTHB与上面类似。
8、lower(A)返回字符串的小写。相反函数为upper。
9、LPAD(A,B,C)，A、C为字符串，B补足多少位（数字），使A字符串的长度为B，向A的左边补C，将A补足B位。相反的函数为RPAD。
例：select  lpad('ABCD',6,'R') from dual;返回RRABCD
    select  rpad('ABCD',6,'R') from dual;返回ABCDRR
10、LTRIM(A,B)，若A的第一个字符在B里面，则去掉A的第一个字符，以此类推，至到A的第一个字符不在B里面为止。相反的函数为RTRIM。
例：select ltrim('ABCD','AB') from dual;返回CD。
11、REPLACE(A,B,C)，ABC均为字符串，用C代替出现在A中的B，例：
    select replace('ABC','B','D') from dual;返回ADC。
12、SUBSTR(A,B,C)，A为字符串，BC为整数，截取从A的第B位开始，截取C位，例：
    select substr('ABCDEFG',3,4) from dual;返回CDEF
13、SUBSTRB(A,B,C)与SUBSTR类似，前者BC以字节计算，例：
    select substr('各位朋友大家好',3,4) from dual;返回，朋友大家
    select substrb('各位朋友大家好',3,4) from dual;返回，位朋
14、SOUNDEX(A)，返回A发音相似的词（不常用没弄懂）。
15、TRANSLATE(A,B,C)，ABC均为字符串，将A中与B相同的，用C代替，例：
    select TRANSLATE('ABC','B','D') from dual;返回ADC
    与REPLACE的区别，例：
    如：select replace('acccccd','cd','ef') from dual; --> accccef    ------字符串级别的代替
    如：select translate('acccccd','cd','ef') from dual; -->aeeeeef   ------字符级别的代替
16、TRIM(A)，将A的字第个空格和最后一个空格去掉，例：
    select trim(' A B C ') from dual;返回'A B C'。

数值函数

1、ABS(A)，A为数值型，返回A的绝对值。
2、ACOS(A),反余弦函数，A的值为-1~1之间的数据。值为弧度
   如：select acos(-1) from dual;返回弧度值3.1415926...即TT的值，不会返回180度。
3、ASIN(A)反正弦，ACOS(A)反余弦，SIN(A)正弦，COS(A)余弦，TAN(A)正切。例：
   select sin(asin(1)) from dual;返回1，asin(1)即(TT/2)，sin(TT/2)=1;其它同理。
4、CEIL(A)返回大于或等于A的最小整数，FLOOR(A)返回小于等于A的最大整数，ROUND(A,B)返回A的小数点右边的B位的值（四舍五入）。例：
select CEIL(1.5),FLOOR(1.5),round(1.5)  from dual;
 CEIL(1.5) FLOOR(1.5) ROUND(1.5)
---------- ---------- ----------
         2          1          2
5、EXP(A)返回e的A次幂。
6、LN(A)返回A的自然对数，A必须大于0。
7、LOG(A,B)返回A为底B的对数。例：
SQL> select ln(2),log(exp(1),2) from dual;
 
     LN(2) LOG(EXP(1),2)
---------- -------------
0.69314718 0.69314718055
exp(1)即e的值，ln即以e为底的对数，因此上面两个值相等
求数值列的乘积，exp(sum(ln(列名))) from dept;ln即以e为底的对数，sum(ln(列名))即(ln列1+ln列2...ln列n)=(ln(列1*列2*列3))，再求其e的log e为底的对数，即是求列1*列2*列3。
8、MOD(A,B)返回A除以B的余数。
9、POWER(A,B)返回A的B次方。
10、SING(A)，如果A为正数返回1，负数返回-1，0返回0.
11、SINH(A)，返回A的双曲正弦值，A是弧度。COSH(A)，返回A的双曲余弦值，A是弧度。TANH(A)返回A的双正切值，A是弧度。
12、SQRT(A)，返回A的平方根，A是弧度。
13、TRUNC(A,B)，返回A的小数点右边的B位的值（不四舍五入）。例：
SQL> select TRUNC(1234.567,1),TRUNC(1234.567,-1) from dual;
TRUNC(1234.567,1) TRUNC(1234.567,-1)
----------------- ------------------
           1234.5               1230

日期函数

1、ADD_MONTHS(A,B)，返回A日期加B个月，若B为小数，将截取小数部分，只保留整数部分。例：
SQL> select ADD_MONTHS(sysdate,1),ADD_MONTHS(sysdate,1.56) from dual;
 ADD_MONTHS(SYSDATE,1) ADD_MONTHS(SYSDATE,1.56)
--------------------- ------------------------
2015-6-16 20:06:42    2015-6-16 20:06:42
2、LAST_DAY(A)，返回A月份的最后一天，A为日期类型。
3、MONTHS_BETWEEN(A,B)返回A、B月份之间的月份差，A月份-B月份，例：
SQL> select MONTHS_BETWEEN(date'2015-5-1',date'2015-6-1') from dual;
 MONTHS_BETWEEN(DATE'2015-5-1',
------------------------------
                            -1
4、NEW_TIME(A,B,C)A为日期类型，BC均时区码，表示时间A如果是B的时间，那么返回时区B对应时区C的时间。

5、next_day(x,y)用于计算x时间后第一个星期y的时间。x 是一个时间，y 是星期一至星期日中的一个。也可能用数字1 - 7代替，

但是用数字的时候1是代表的周日2才是代表的周一。

6、trunc(A,B)A为日期，B为fmt值。
trunc(sysdate,'yyyy') --返回当年第一天.
trunc(sysdate,'mm') --返回当月第一天.
trunc(sysdate,'d') --返回当前星期的第一天.
trunc(sysdate,'dd')--返回当前年月日
trunc(sysdate, 'hh')--返回当前小时
trunc(sysdate, 'mi')--返回当前分钟

转换函数

1、CHARTOROWID(A)，将A转换为rowid类型()。把包含外部格式的ROWID的CHAR或VARCHAR2数值转换为内部的二进制格式.参数A必须是包含外部格式的ROWID的18字符的字符串.oracle7和 oracle8中的外部格式是不同的.CHARTOROWID是ROWIDTOCHAR的反函数。
2、convert(A,B,C)，用于转换字符集，如convert('您好','AL32UTF8','AL16UTF16')。
3、hextoraw(A)，将十六进制的A转换为二进制，逆函数为rawtohex。
4、to_char(A,B)，A为日期或数字，B为转换的格式
   日期常用用法：
   a、select to_char(sysdate, 'yyyy-mm-dd') from dual;返回2015-05-19。
   b、select to_char(sysdate, 'yyyy-mm-dd HH24:MI:SS') from dual;返回2015-05-19 15:46:40。
   c、select to_char(sysdate, 'yyyy-mm-dd HH12:MI:SS') from dual;返回2015-05-19 03:46:40。
   d、select to_char(sysdate, 'Day, yyyy-mm-dd HH12:MI:SS') from dual;返回星期二, 2015-5-19 3:52:15。
   e、select to_char(sysdate, 'HH'),to_char(sysdate, 'HH12'),to_char(sysdate, 'HH24') from dual;返回03,03,15。
   f、select to_char(sysdate, 'AM'),to_char(sysdate, 'PM'),to_char(sysdate, 'A.M.'),to_char(sysdate, 'P.M.') from dual;返回下午，下午，下午，下午。
   g、select to_char(sysdate, 'Y,YYY'),to_char(sysdate, 'YYYY'),to_char(sysdate, 'YYY'),to_char(sysdate, 'YY'),to_char(sysdate, 'Y') from dual;返回2,015,2015,          
015,15,5。
   h、select to_char(sysdate, 'month'),to_char(sysdate, 'mm'),to_char(sysdate,'day') from dual;返回5月,05,星期三。
   i、select to_char(sysdate, 'DDD') from dual;返回一年里的第几天。
   j、select to_char(sysdate, 'DD') from dual;返回一月里的第几天。
   k、select to_char(sysdate, 'D') from dual;返回一周里的第几天。
   l、select to_char(sysdate, 'WW') from dual;返回一年里的第几周。
   m、select to_char(sysdate, 'W') from dual;返回一月里的第几周。
   n、select to_char(sysdate, 'Q') from dual;返回2（季度）。
   o、select to_char(sysdate, 'RM') from dual;返回罗马数字的月份。
   数字常用用法：
   例：select to_char(0.896,'FM999,999,999,990.00') from dual返回0.90
   其中0，9，FM解答：
   1：其0代表：如果存在数字则显示数字，不存在则显示0，即占位符。
   2：其9代表：如果存在数字则显示数字，不存在则显示空格。
   3：其FM代表：删除如果是因9带来的空格，则删除。
5、to_date(A,B)，A为字符串，B为即将要转换的格式。
   1、date的精度没有timestamp的精度大，date的精度精确到秒，而timestamp的精度可精确到纳秒。
   2、sysdate--返回当前系统日期和时间，精确到秒
      systimestamp--返回当前系统日期和时间，精确到毫秒
   3、select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;可以这么写，设置语言。
6、to_multi_byte(A)，将单字节字符转换为多字节字符。逆函数为to_single_byte(A)。
7、to_number(A,B)，也可一个参数，将字符串转换为数字，例：
   a、select to_number('99999999') from dual;
   b、select to_number('RMB234234.4350','L999990.99999') from dual;（L在）
   c、select to_number('$234234.4350','$999990.99999') from dual;

组函数

1、AVG(列A)平均值， 列A值必须全为数字，允许存在空行，如果存在空行，空行不会被记录在内，如列A共有三条记录，分别为2,null,5，此时候的值为(2+5)/2。
2、SUM(列A)求总和，列A值必须全为数字，允许存在空行。
3、MAX(列A)求最大值，没要求列值必须为数字，可以为数字也可以为字符串、日期。如果值为字符串，则按照a~b，A~Z的顺序，越往后就越大，如果字符串第一个字符相同，则比较第二个字符，以此类推，如果为汉字不知道咋比。日期则按照日期越晚越大的原则进行比较。
4、MIN(列A)与上面类似。
5、stddev(列A)，Standard Deviation，求标准差，所谓标准差，求一组数的离散程度，举个例子：两组数A{10，10，50，30}，B{5,90,2,3}。两组数的平均值相同，但前者标准差较小，因为离平均值波动较小，如果用A、B表示两个同学这学期语文的分数，标准差越小的越稳定，即A同学学习成绩比较稳定。可用于做生意计算风险。
  标准差的具体算法：所有数减去其平均值的平方和，所得结果除以该组数之个数（或个数减一，即变异数），再把所得值开根号，所得之数就是这组数据的标准差。
6、variance（列A）求统计方差（没怎么弄明白）。

分析函数

1、rollup
2、cube
3、grouping
4、rank() over开窗函数
5、dense_rank() over开窗函数
6、row_number() over开窗函数
7、lag和lead函数
这篇文章写得比较好：http://love-flying-snow.iteye.com/blog/573083

having与where的区别
where条件后不能跟组函数，而having后面一般跟组函数。

unin、union all、intersect、minus的区别
1、union合并两表的数据，要求列数相同，各列数据类型对应相同，要过滤重复行数据。
2、union all合并两表数据，不过滤重复行数据。
3、intersect，取两表共有行数据，即取两表相同行数据返回。
4、minus与intersect相反。
  
case语句与decode语句的区别：
语法：a、case 列A值 when '1' then '是A' else '是B' ，case when 列A='1' then ‘是A’ else是B。
      b、decode(列A值,B,C,D,E,F,G,H)，如果列A值等于B，则返回C，如果等于D则返回E，如果等于F，则返回G，如果等于其它，则返回H。 
首先两者都可以实现简单的分支语句逻辑，decode相对较简洁，不用写plsql语句。