03-Oracle学习_常用函数

常用SQL函数
单行函数: 有多少条记录就有多少个输出
多行函数: 也叫组函数, 一组记录 只产生一个输出

1, 单行函数

① 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

2, 组函数

① 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