Oracle基础(九)简单函数

SQL 函数包含单行函数和多行函数
单行函数就是输入一行输出页数一行.
多行函数也称为分组函数,将多行数据返回一个值.

1. 字符函数
   1. ascii(x) 用于返回字母的ascii码
   SQL> select ascii(dname) from dept;

    	ASCII(DNAME)
    	------------
    	          98
    	          65
    	          82
    	          83
    	          79
   

   2. length(x) 获取字符个数

       SQL> select length(dname) from dept;
       LENGTH(DNAME)
       -------------
                   1
                  10
                   8
                   5
                  10
       
       SQL> select dname from dept where length(dname)>5;
       
       DNAME
       --------------
       ACCOUNTING
       RESEARCH
       OPERATIONS
      

      3. concat(x,y) 拼接字符串
         SQL> select deptno,dname||loc from dept;

         DEPTNO DNAME||LOC

      ---

           50 ba
           10 ACCOUNTINGNEW YORK
           20 RESEARCHDALLAS
           30 SALESCHICAGO
           40 OPERATIONSBOSTON
      

      SQL> select deptno,concat(dname,loc) from dept;

       DEPTNO CONCAT(DNAME,LOC)
      

      ---

           50 ba
           10 ACCOUNTINGNEW YORK
           20 RESEARCHDALLAS
           30 SALESCHICAGO
           40 OPERATIONSBOSTON
      

      4. lower(x) 转换小写
         SQL> select * from dept where lower(dname) = lower(‘SalEs’);

         DEPTNO DNAME LOC

      ---

           30 SALES          CHICAGO
      

      5. upper(x) 转换大写
         SQL> select upper(dname) from dept;
         UPPER(DNAME)

         B
         ACCOUNTING
         RESEARCH
         SALES
         OPERATIONS

      6. substr(x,start[,length])
         x : 需要截取的字符串
         start : 开始位置
         length : 攫取的长度

      7. instr(x,find_string[,start][,occurrence]) 查询find_string,然后返回所在的位置.occurrence第几次出现的位置

         SQL> select instr(‘welcome’,‘e’),instr(‘welcome’,‘e’,3,2) from dual;

         INSTR(‘WELCOME’,‘E’) INSTR(‘WELCOME’,‘E’,3,2)

         ---

                         2                        0
         

         SQL> select instr(‘welcome’,‘e’),instr(‘welcome’,‘e’,1,2) from dual;

         INSTR(‘WELCOME’,‘E’) INSTR(‘WELCOME’,‘E’,1,2)

         ---

                         2                        7
         

      8. replace(x,search_string,replace_string) 替换
         x : 用于查找的字符串
         search_string : 要查询的字符串
         replace_string : 替换的字符串

         SQL> select replace(dname,‘S’,’*’) from dept;

         REPLACE(DNAME,

         b
         ACCOUNTING
         REEARCH
         ALE
         OPERATION

      9. initcap(x) : 用于将单词首字母转换成大写.
         SQL> select initcap(‘welcome to you!’) from dual;
         SQL> select initcap(dname),initcap(loc) from dept;

         INITCAP(DNAME) INITCAP(LOC)

         ---

         B A
         Accounting New York
         Research Dallas
         Sales Chicago
         Operations Boston

          SQL> select initcap(substr(dname,2,3)) from dept;	
          INITCA
          
          
          Cco
          Ese
          Ale
          Per
         

      10. rpad(x,widht[,pad_string]) : 在x的右边补齐多少个空格
          SQL> select rpad(dname,20,’*’) from dept;

          RPAD(DNAME,20,’*’)

          b*******************
          ACCOUNTING**********
          RESEARCH************
          SALES***************
          OPERATIONS**********

      11. lpad()
          SQL> select lpad(dname,20,’*’) from dept;

          LPAD(DNAME,20,’*’)

          *******************b
          **********ACCOUNTING
          ************RESEARCH
          ***************SALES
          **********OPERATIONS

   3. 数字函数

      1. ABS(X) 绝对值
         SQL> select abs(10),abs(-10) from dual;

         ABS(10) ABS(-10)

      ---

           10         10
      

      2. MOD(X) 余数
         SQL> select mod(8,3),mod(8,4) from dual;

      MOD(8,3) MOD(8,4)

      ---

            2          0
      

      3. CEIL(X) 向上取整
         SQL> select ceil(13.1),ceil(-5.2) from dual;

      CEIL(13.1) CEIL(-5.2)

      ---

           14         -5
      

      4. FLOOR(x) 向下取整
         SQL> select floor(13.8) from dual;

      FLOOR(13.8)

            13
      

      5. ROUND(X[,y]) 四舍五入
         SQL> select round(123.456,2),round(5.75,-1) from dual;

         ROUND(123.456,2) ROUND(5.75,-1)

         ---

                123.46             10
         

         SQL> select round(123.456,2),round(3.75,-1) from dual;

         ROUND(123.456,2) ROUND(3.75,-1)

         ---

                123.46              0
         

      6. POWER(x,y) x的y次幂
         SQL> select power(2,3),power(-3,2) from dual;

         POWER(2,3) POWER(-3,2)

         ---

               8           9
         

      7. SQRT(x) 平方根
         SQL> select sqrt(4) from dual;

         SQRT(4)

              2
         

      8. SIGN(x) 检测正负值
         SQL> select sign(10),sign(0),sign(-1) from dual;

         SIGN(10) SIGN(0) SIGN(-1)

         ---

              1          0         -1
         

      9. COS(x)
         SQL> select cos(0.5) from dual;

         COS(0.5)

         .877582562

      10. ACOS(x) 反余弦
          SQL> select acos(0.3),acos(-0.3) from dual;

          ACOS(0.3) ACOS(-0.3)

       ---------- ----------
       1.26610367 1.87548898
      

   4. 转换函数

      1. to_char(x) : 将x转换成一个字符串,格式化字符串
         SQL> select to_char(123459.67,’$999,999.99’) from dual;

         TO_CHAR(1234

         $123,459.67

      2. to_number(x,[,format]) : 将x转换成一个数字.
         SQL> select 123+to_number(‘25’) from dual;

         123+TO_NUMBER(‘25’)
         148

      SQL> select to_number(’-$12,345.67’,’$99,999.99’)+10 from dual;

       TO_NUMBER('-$12,345.67','$99,999.99')+10
       ----------------------------------------
                                      -12335.67
      

      3. cast(x as type) : 将x转换成指定type的兼容类型
         SQL> select
         2 cast(123 as varchar2(10)),
         3 cast(‘456’ as number(10))
         4 from dual;

      4. asciistr() : 将任意字符串转换成数据库字符集的ascii字符串
         SQL> select asciistr(‘中国’) from dual;

         ASCIISTR(’

         \4E2D\56FD

      5. bin_to_num(x) : 将二进制数字转换成十进制

      6. to_date() : 将字符串转换成时间类型
         SQL> select to_date(‘2019-08-09’,‘yyyy-MM-dd’) from dual;

         TO_DATE('2019-

         09-8月 -19

   5. 正则表达式(忽略)

      1. regexp(x,pattern[,match_option]) 用于在x中查找pattern参数中定义的正则表达式
         SQL> select dname from dept where regexp_like(dname,’^r’,‘i’);

         DNAME

         RESEARCH

      SQL> select dname from dept where regexp_like(dname,’^\w{4,5}$’,‘i’);

       DNAME
       --------------
       SALES
      

   6. 聚合函数

      1. avg() 平均数,非null的平均值

      2. count() 获取非空值得数量

      3. max()和min() 函数

      4. sum() 所有值得和
         SQL> select deptno,sum(sal) from emp group by deptno;

         DEPTNO SUM(SAL)

      ---

                     800
           30       9400
           20      10075
           10       8750
      

   7. 日期函数
      to_char()和to_date()转换时间
      SQL> select to_char(sysdate,‘yyyy-MM-dd HH24:MI:SS’) from dual;

      TO_CHAR(SYSDATE,'YY

      2019-08-31 10:50:07

      SQL> insert into mydate values(to_date(‘2019-09-09’,‘yyyy-MM-dd’))

      2. sysdate 获取系统当前时间
         SQL> select sysdate from dual
         2 ;

      SYSDATE

      31-8月 -19

      3. months_between(x,y) 函数判断2个月份差
         SQL> select months_between(sysdate,to_date(‘2020/01/01’,‘yyyy/MM/dd’)) from dual;

         MONTHS_BETWEEN(SYSDATE,TO_DATE(‘2020/01/01’,‘YYYY/MM/DD’))

                                                      -4.0175806
         

      4. add_months(x,y) 计算x+y个月后的结果
         SQL> select add_months(sysdate,4) from dual;

          ADD_MONTHS(SYS
          --------------
          31-12月-19
         

         SQL> select add_months(sysdate,-5) from dual;

          ADD_MONTHS(SYS
          --------------
          31-3月 -19
         

      5. next_day(x,day)函数 返回由第二个参数day指定的星期几,第一次出现的日期.
         SQL> select next_day(to_date(‘2019-08-27’,‘yyyy-MM-dd’),‘星期三’)from dual;

         NEXT_DAY(TO_DA

         28-8月 -19

         SQL> select next_day(to_date(‘2019-08-27’,‘yyyy-MM-dd’),‘星期一’)from dual;

         NEXT_DAY(TO_DA

         02-9月 -19

      6. round(x) 四舍五入
         SQL> select round(to_date(‘2019-06-26’,‘yyyy-MM-dd’),‘yyyy’) from dual;

         ROUND(TO_DATE(

         01-1月 -19
         SQL> select round(to_date(‘2019-07-16’,‘yyyy-MM-dd’),‘MM’) from dual;

         ROUND(TO_DATE(

         01-8月 -19

         SQL> select round(to_date(‘2019-07-19 13:25:5’,‘yyyy-MM-dd HH24:MI:SS’),‘dd’) from dual;

         ROUND(TO_DATE(

         20-7月 -19

      7. TRUNC() 截断
         SQL> select trunc(to_date(‘2019-08-04’,‘yyyy-MM-dd’),‘YYYY’) from dual;

         TRUNC(TO_DATE(

         01-1月 -19

         SQL> select trunc(to_date(‘2019-08-04’,‘yyyy-MM-dd’),‘MM’) from dual;

         TRUNC(TO_DATE(

         01-8月 -19

      8. extract() 提取时间

      SQL> select extract(year from sysdate) from dual;

       EXTRACT(YEARFROMSYSDATE)
       ------------------------
                           2019
      

   8. 其他函数
      【语法】NVL (expr1, expr2)
      【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
      注意两者的类型要一致
      【语法】NVL2 (expr1, expr2, expr3)
      【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。
      expr2和expr3类型不同的话，expr3会转换为expr2的类型

      SQL> select ename,decode(deptno,10,‘A’,20,‘B’,30,‘C’,‘D’) from emp;

       ENAME      D
       ---------- -
       SMITH      D
       ALLEN      C
       WARD       C
       JONES      B
       MARTIN     C
       BLAKE      C
       CLARK      A
       SCOTT      B
       KING       A
       TURNER     C
       ADAMS      B
       
       ENAME      D
       ---------- -
       JAMES      C
       FORD       B
       MILLER     A
       
       已选择14行。