数据库常用的函数

数据库常用的函数主要分为单行函数和多行函数；
单行函数： 输入一个返回一个；例如字符型，日期，数字，转换，通用函数
多行函数：输入多个返回一个；例如求和函数，最大值等

1.字符型函数
1.1大小写转换函数（Oracle数据库大小写是敏感的）
函数 结果
LOWER(‘SQL Course’) sql course
UPPER(‘SQL Course’) SQL COURSE
INITCAP(‘SQL course’) Sql Course
1.2字符操作函数
substr(‘helloworld’,a,b) 参数A表示截取的起始点，参数b表示截取的长度，若b省略则默认截取到字符串的末尾；
instr(string1,string2[,start_position[,nth_appearence]])
string1：要在此字符串中查找。
string2：要在string1中查找的字符串。
start_position：从string1开始查找的位置。可选，默认为1，正数时，从左到右检索，负数时，从右到左检索。
nth_appearence：查找第几次出现string2。可选，默认为1，不能为负。
注：如果没有查找到，返回0。
例如：

select instr('helloworld','l','1',1) from dual;
select instr('helloworld','l','1',3) from dual;
select instr('helloworld','low','1',1) from dual;

该函数可以用于模糊查询以及判断包含关系：
eg: select code, name, dept, occupation from staff where instr(code, ‘001’) > 0;
　　select code, name, dept, occupation from staff where code like ‘%001%’ ;
数字操作函数
round表示四舍五入，包括两个参数，一个是要操作的数字，另一个是需要保留的位数；
trunc舍弃超出的位数
3.日期操作函数
months_between 主要用来计算两个日期之间相差的月份数；主要是第一个日期减去第二个日期；

select months_between(to_date(‘20080228’, ‘yyyymmdd’),
to_date(‘20090228’, ‘yyyymmdd’)) as months
from dual;
-12
add_months(time,months)函数可以得到某一时间之前或之后n个月的时间
1. 如 select add_months(sysdate,-6) from dual;
该查询的结果是当前时间半年前的时间
2.select add_months(sysdate,6) from dual;
该查询的结果是当前时间半年后的时间
3.next_day:第一个参数是对应的时间，第二个主要是星期或者数字1-7，其中1代表周一，按照日历排序；
SELECT NEXT_DAY(SYSDATE, ‘Sunday’) FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL;
4.last_day: 某个日期当月的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
5.对日期进行四舍五入
SELECT ROUND(SYSDATE, ‘MONTH’) 月, ROUND(SYSDATE, ‘YEAR’) FROM DUAL;
6.–对日期进行截断
SELECT TRUNC(SYSDATE, ‘MONTH’) 月, TRUNC(SYSDATE, ‘YEAR’) FROM DUAL;
转换函数

1 .TO_CHAR() 函数：日期到字符串的转换
select to_char(sysdate,’yyyy-mm-dd-dy’) from dual;
select to_char(sysdate,’yyyy-mm-dd-dy’) from dual;
select to_char(888989,’l9,999,999’) from dual;
2.TO_DATE
SELECT TO_DATE(‘20160525’,’YYYY-MM-DD’) FROM DUAL;
3.TO_NUMBER

4.Decode
DECODE(value,if1,then1,if2,then2,if3,then3,…,else)
表示如果value 等于if1时，DECODE函数的结果返回 then1,如果不等于任何一个if值，则返回else；
5.case when
CASE
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
区别：
DECODE 只能用做相等判断,但是可以配合sign函数进行大于，小于，等于的判断
CASE 　可用于=,>=,<,<=,<>,is null,is not null 等的判断;

单行函数补充
1.replace
REPLACE(‘string’,’s1’,’s2’)
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace(‘he love you’,’he’,’i’) from dual;
http://www.3lian.com/edu/2013/08-02/86216.html
分析函数总结
多行函数
分组计算函数：相对于单行函数，也可称之为多行函数，它的输入是多个行构成得一个行集（这个行集可以是 一张表的所有行，也可以是按照某个维度进行分组后的某一组行），而输出都是一个值；
备注：MIN, MAX 可用于任何数据类型，但AVG ， SUM，STDDEV, VARIANCE仅适用于数值型字段。
1count函数
count详解：
count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）.
distinct 列名,得到的结果将是除去值为null和重复数据后的结果
如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(),自动会优化指定到那一个字段。所以没必要去count(?)，用count(),sql会帮你完成优化的

2 使用GROUP BY 子句进行分组: 不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。

SELECT department_id, AVG(salary) FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000
order by department_id ;

       ----练习的SQL代码------
         ----日期操作函数----
--显示当前日期
SELECT SYSDATE FROM DUAL;
--last_day: 某个日期当月的最后一天  
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--next_day:下周日
SELECT NEXT_DAY(SYSDATE, 'Sunday') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL;
--对日期进行四舍五入  
SELECT ROUND(SYSDATE, 'MONTH') 月, ROUND(SYSDATE, 'YEAR') FROM DUAL;
--对日期进行截断  
SELECT TRUNC(SYSDATE, 'MONTH') 月, TRUNC(SYSDATE, 'YEAR') FROM DUAL;


--字符串-
SELECT LTRIM(' abc') S1, RTRIM('zhang   ') S2, TRIM('  zhang  hello ') S3，
 TRIM （'f' FROM 'fjdkfhdkhf'）
  FROM DUAL;
SELECT SUBSTR('abcdef', 1, 4)
  FROM DUAL
    -- 计算字符串长度
         SELECT LENGTH('abcdef')
           FROM DUAL

   ----initcap（首字母变大写） ,lower(变小写),upper(变大写)
         SELECT LOWER('ABC') S1, UPPER('def') S2, INITCAP('efg') S3
   ---replace
         select replace('abc','b','xy') from dual;


    --数字操作函数
 -----返回固定小数位数 (round:四舍五入，trunc:直接截断)
   select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

---to_char
SELECT TO_CHAR(SYSDATE) S1,
       TO_CHAR(SYSDATE, 'yyyy-mm-dd') S2,
       TO_CHAR(SYSDATE, 'yyyy-mm-dd hh12:mi:ss') S3,
       TO_CHAR(SYSDATE, 'dy') S3 ,
       TO_CHAR(SYSDATE, 'fmDD "of"  month YYYY') AS DATE1
  FROM DUAL;

 ---to_date
SELECT to_date('2013-09-12'，'yyyy-mm-dd') FROM dual;

---to_number
select TO_NUMBER('$4,456','$9,999' )from dual;
select TO_NUMBER('$4,456,455.000','$9,999,999,999,999.999' )from dual;

----其他操作函数
 ---decode 
     select ht.row_number,ht.row_desc,
            sum(decode(ht.measure_code, 'A',VALUE_A/10000 ,null)) as A,
            sum(decode(ht.measure_code, 'B',VALUE_A/10000 ,null)) as B,
            sum(decode(ht.measure_code, 'C',VALUE_A/10000 ,null)) as C,
            sum(decode(ht.measure_code, 'D',VALUE_A ,null)) as D,
            sum(decode(ht.measure_code, 'E',VALUE_A ,null)) as E,
            sum(decode(ht.measure_code, 'F',VALUE_A ,null)) as F,
            sum(decode(ht.measure_code, 'G',VALUE_A/10000 ,null)) as G,
            sum(decode(ht.measure_code, 'H',VALUE_A/10000,null)) as H, 
            sum(decode(ht.measure_code, 'I',VALUE_A/10000 ,null)) as I,
             sum(decode(ht.measure_code, 'J',VALUE_A/10000 ,null)) as J
             from hebinglast_ ht
               group by HT.ROW_NUMBER,HT.ROW_DESC
               order by HT.ROW_NUMBER;
     -----
 CREATE TABLE tb( 姓名 VARCHAR(10),课程 VARCHAR(10),分数 number);
 insert into tb VALUES ('张三','语文',74);
insert into tb VALUES ('张三','数学',83);
insert into tb VALUES ('张三','物理',93);
insert into tb VALUES ('李四','语文',74);
insert into tb VALUES ('李四','数学',84);
insert into tb VALUES ('李四','物理',94);
SELECT * FROM tb;
-----decode 
select tb.姓名,
     sum(decode(tb.课程, '语文', 分数,null)) as CHINESE,
     sum(decode(tb.课程, '数学', 分数,null)) as MATH,
     sum(decode(tb.课程, '物理', 分数,null)) as Physics
     from  tB
   group by tB.姓名
   -----case when 
SELECT TB.姓名,
       SUM(CASE 课程
             WHEN '语文' THEN
              分数
           END) AS CHINESE,

       SUM(CASE 课程
              WHEN '数学' THEN
               分数
            END) AS MATH， SUM(CASE 课程
         WHEN '物理' THEN
          分数
       END) AS PHYSICS

  FROM TB
 GROUP BY 姓名;

----decode
  select ht.row_number,ht.row_desc,
            sum(decode(ht.measure_code, 'A',VALUE_A/10000 ,null)) as A,
            sum(decode(ht.measure_code, 'B',VALUE_A/10000 ,null)) as B,
            sum(decode(ht.measure_code, 'C',VALUE_A/10000 ,null)) as C,
            sum(decode(ht.measure_code, 'D',VALUE_A ,null)) as D,
            sum(decode(ht.measure_code, 'E',VALUE_A ,null)) as E,
            sum(decode(ht.measure_code, 'F',VALUE_A ,null)) as F,
            sum(decode(ht.measure_code, 'G',VALUE_A/10000 ,null)) as G,
            sum(decode(ht.measure_code, 'H',VALUE_A/10000,null)) as H, 
            sum(decode(ht.measure_code, 'I',VALUE_A/10000 ,null)) as I,
             sum(decode(ht.measure_code, 'J',VALUE_A/10000 ,null)) as J
             from hebinglast_ ht
               group by HT.ROW_NUMBER,HT.ROW_DESC
               order by HT.ROW_NUMBER;
       -----case when 
      SELECT 
        CASE 
           WHEN T1.COLUMN_CODE IS NULL AND T1.TB_ID=1 
                 THEN 1
              WHEN T1.COLUMN_CODE IS NULL AND T1.TB_ID BETWEEN 2 AND 7 
                THEN 2
                WHEN T1.COLUMN_CODE IS NULL AND T1.TB_ID BETWEEN 8 AND 30
                   THEN 3
                  WHEN T1.COLUMN_CODE IS NULL AND T1.TB_ID BETWEEN 31 AND 32 
                    THEN 4
                    WHEN T1.COLUMN_CODE IS NULL AND T1.TB_ID BETWEEN 33 AND 41 
                      THEN 5
                      WHEN T1.COLUMN_CODE IS NULL AND T1.TB_ID BETWEEN 42 AND 999 
                        THEN 6
                        ELSE T1.COLUMN_CODE 
                              END COLUMN_CODE
                            FROM RPT_L_005_DTL_XIB_REG T1 ;

                                 -----by ido丶