oracle教程之oracle的常用函数

① lower, 字符串转小写
    SQL> select lower(ename) from emp;
    SQL> select ename from emp
      2  where ename like '_a%' or ename like '_A%';
    SQL> select ename from emp
      2  where lower(ename) like '_a%';
② upper
    SQL> select upper('abc') from dual;
③ substr(字符串, 起始位置, 截取字符数), 从1开始
    SQL> select substr('123456', 2, 3) from dual;
    SUBSTR
    ------
    234
    SQL> select substr(ename, 2, 3) from emp;
④ chr(ASCII码), 转换成对应ASCII对应的字符
    SQL> select chr(65) from dual;
    CH
    --
    A
    SQL> select chr(65+32) from dual;
    CH
    --
    a
⑤ ascii(字符), 将字符转换成对应的ASCII码
    SQL> select ascii('A') from dual;
    ASCII('A')
    ----------
            65
⑥ round(浮点数 [, 四舍五入到哪一位]), 默认为0
    SQL> select round(1234.56789) from dual;
    ROUND(1234.56789)
    -----------------
                 1235
    SQL> select round(1234.56789, 2) from dual;
    ROUND(1234.56789,2)
    -------------------
                1234.57
    SQL> select round(1234.56789, -1) from dual;
    ROUND(1234.56789,-1)
    --------------------
                    1230
⑦ A. to_char(数字, '格式') 格式化数字,
   以指定格式输出字符串; L,本地货币符
    SQL> select to_char(sal, '$99,999.9999') from emp order by sal;
    TO_CHAR(SAL,'$99,999.9999'
    --------------------------
        $950.0000
      $1,210.0000
      $1,250.0000
    SQL> select to_char(sal, 'L99,999.9999') from emp order by sal;
    TO_CHAR(SAL,'L99,999.9999')
    --------------------------------------------
               ￥950.0000
             ￥1,210.0000
    SQL> select to_char(sal, 'L00,000.0000') from emp order by sal;
    TO_CHAR(SAL,'L00,000.0000')
    --------------------------------------------
            ￥00,950.0000
            ￥01,210.0000
   B. to_char(日期, '格式')
    SQL> select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual;
    TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')
    --------------------------------------
    2013-08-10 08:18:31
    SQL> select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
    TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
    --------------------------------------
    2013-08-10 20:17:53
⑧ to_date(字符串, '格式'), 将字符串转换成指定格式的日期
    SQL> select ename, to_char(hiredate, 'YYYY-MM-DD HH24:MI:SS') from emp
      2  where hiredate > to_date('1981-12-03 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
    ENAME                TO_CHAR(HIREDATE,'YYYY-MM-DDHH24:MI:SS
    -------------------- --------------------------------------
    SCOTT                1987-04-19 00:00:00
⑨ to_number(带货币符的字符串, '格式')
    SQL> select sal from emp
      2  where sal > to_number('$1,234.567', '$999,999.999');
           SAL
    ----------
          1250
          2975   
⑩ nvl(参数, 0), 如果参数为NULL, 则返回0
    SQL> select ename, sal*12 + comm from emp;
    ENAME                SAL*12+COMM
    -------------------- -----------
    BLAKE
    CLARK
    SCOTT
    KING
    SQL> select ename, sal*12 + nvl(comm, 0) from emp;
    ENAME                SAL*12+NVL(COMM,0)
    -------------------- ------------------
    CLARK                             29400
    SCOTT                             39600
    KING                              60000

① max, min
    -- 最大薪水
    SQL> select max(sal) from emp;
      MAX(SAL)
    ----------
          5000
    -- 最小薪水
    SQL> select min(sal) from emp;
      MIN(SAL)
    ----------
           950
② avg, sum
    -- 格式化 平均薪水
    SQL> select to_char(avg(sal), 'L999,999.999') from emp;
    TO_CHAR(AVG(SAL),'L999,999.999')
    --------------------------------------------
              ￥2,252.917
    -- 四舍五入 平均薪水
    SQL> select round(to_char(avg(sal), '99999.999'), 2) avg_sal from emp;
       AVG_SAL
    ----------
       2252.92
    -- 公司每个月总共需要支付给员工多少钱
    SQL> select sum(sal) from emp;
      SUM(SAL)
    ----------
         27035
③ count
    -- 总共有多少条记录
    SQL> select count(*) from emp;
      COUNT(*)
    ----------
            13
    -- 不是NULL 则算一个
    SQL> select count(comm) from emp;
    COUNT(COMM)
    -----------
              4
    -- 去重复
    SQL> select count(distinct deptno) from emp;
    COUNT(DISTINCTDEPTNO)
    ---------------------
                        3