函数

2020-03-03 本文已影响0人风中小酌

单行函数.png

单行函数

处理单行数据

字符函数

接受字符数据作为输入

大小写处理函数

转换所字母为小写 LOWER

SQL> select LOWER(job_id) from employees;
LOWER(JOB_ID)
-------------
ac_account
ac_mgr
ad_asst
ad_pres
ad_vp

转换所有字母为大写 UPPER

SQL> select UPPER(last_name) from employees;
UPPER(LAST_NAME)
-------------------------
ABEL
ANDE
ATKINSON
AUSTIN

转换单词首字母大写 INITCAP

SQL> select last_name from employees where last_name = initcap('taylor');
LAST_NAME
-------------------------
Taylor
Taylor

字符处理函数

拼接字符串 CONCAT

SQL> select concat(last_name, first_name) from employees;
CONCAT(LAST_NAME,FIRST_NAME)
---------------------------------------------
AbelEllen
AndeSundar
AtkinsonMozhe

截取子字符串 SUBSTR

SQL> select substr('hello', 1, 4) from dual;
SUBSTR('HELLO',1,4)
-------------------
hell

SQL> select substr('hello', -1, 4) from dual;
SUBSTR('HELLO',-1,4)
--------------------
o

SQL> select substr('hello', -3) from dual;
SUBSTR('HELLO',-3)
------------------
llo

SQL> select substr('hello', 1) from dual;
SUBSTR('HELLO',1)
-----------------
hello

SQL> select substr('hello', 0) from dual;
SUBSTR('HELLO',0)
-----------------
hello

计算字符串长度 LENGTH

SQL> select length('hello') from dual;
LENGTH('HELLO')
---------------
              5

获取指定字符串在原字符串中的起始位置 INSTR

INSTR(原字符串，要查找的字符串)
SQL> select instr('hello','e') from dual;
INSTR('HELLO','E')
------------------
                 2
INSTR(原字符串，要查找的字符串, 起始位置)
SQL> select instr('hello','l', 4) from dual;
INSTR('HELLO','L',4)
--------------------
                   4
INSTR(原字符串，要查找的字符串, 起始位置，第几次出现)
SQL> select instr('hello','l', 3, 2) from dual;
INSTR('HELLO','L',3,2)
----------------------
                     4

左填充 LPAD

LPAD(原字符串, 填充后字符串长度, 填充字符)
SQL> select lpad('hello',3, '*') from dual;
LPAD('HELLO',3,'*')
-------------------
hel

SQL> select lpad('hello',10, '*') from dual;
LPAD('HELLO',10,'*')
--------------------
*****hello

右填充 RPAD

RPAD(原字符串, 填充后字符串长度, 填充字符)
SQL> select rpad('hello',3, '*') from dual;
RPAD('HELLO',3,'*')
-------------------
hel

SQL> select rpad('hello',10, '*') from dual;
RPAD('HELLO',10,'*')
--------------------
hello*****

去掉字符串首尾字符 TRIM

同时去掉首尾字符
SQL> select TRIM('h' from 'helloeh') from dual;
TRIM('H'FROM'HELLOEH')
----------------------
elloe
同时去掉首尾字符
SQL> select TRIM(both 'h' from 'helloeh') from dual;
TRIM(BOTH'H'FROM'HELLOEH')
--------------------------
elloe
去头不去尾
SQL> select TRIM(leading 'h' from 'helloeh') from dual;
TRIM(LEADING'H'FROM'HELLOEH')
-----------------------------
elloeh
去尾不去头
SQL> select TRIM(trailing 'h' from 'helloeh') from dual;
TRIM(TRAILING'H'FROM'HELLOEH')
------------------------------
helloe

替换字符串中内容 REPLACE

SQL> select replace('helloeh', 'll', 'rr') from dual;
REPLACE('HELLOEH','LL','RR')
----------------------------
herroeh

数字函数

四舍五入 ROUND

SQL> select round(3214.5555, 2) from dual;
ROUND(3214.5555,2)
------------------
           3214.56

SQL> select round(3214.5555, -1) from dual;
ROUND(3214.5555,-1)
-------------------
               3210

SQL> select round(3214.5555) from dual;
ROUND(3214.5555)
----------------
            3215

截取数字，不进行四舍五入 TRUNC

SQL> select trunc(3214.5555, 2) from dual;
TRUNC(3214.5555,2)
------------------
           3214.55

SQL> select trunc(3214.5555, -1) from dual;
TRUNC(3214.5555,-1)
-------------------
               3210

SQL> select trunc(3214.5555) from dual;
TRUNC(3214.5555)
----------------
            3214

获取余数 MOD

SQL> select mod(3214, 5) from dual;
MOD(3214,5)
-----------
          4

日期函数

两个日期之间的月数 MONTHS_BETWEEN

SQL> select MONTHS_BETWEEN(sysdate, hire_date) from employees where rownum<3;
MONTHS_BETWEEN(SYSDATE,HIRE_DA
------------------------------
              200.610258736559
              173.481226478495

增加月数 ADD_MONTHS

SQL> select hire_date, ADD_MONTHS(hire_date, 3) newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/9/17 星
2005/9/21 星 2005/12/21

SQL> select hire_date, ADD_MONTHS(hire_date, -4) newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/2/17 星
2005/9/21 星 2005/5/21 星

下个星期几 NEXT_DAY

SQL> select hire_date, next_day(hire_date, '星期一') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/6/23 星
2005/9/21 星 2005/9/26 星

星期几用数字表示时1~7，表示星期日~星期六
SQL> select hire_date, next_day(hire_date, 2) newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/6/23 星
2005/9/21 星 2005/9/26 星

当月的最后一天 LAST_DAY

SQL> select hire_date, last_day(hire_date) newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/6/30 星
2005/9/21 星 2005/9/30 星

对日期四舍五入 ROUND

SQL> select hire_date, round(hire_date) newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/6/17 星
2005/9/21 星 2005/9/21 星

SQL> select hire_date, round(hire_date,'dd') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/6/17 星
2005/9/21 星 2005/9/21 星

SQL> select hire_date, round(hire_date,'mm') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/7/1 星期
2005/9/21 星 2005/10/1 星

SQL> select hire_date, round(hire_date,'yy') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/1/1 星期
2005/9/21 星 2006/1/1 星期

对日期截取，不进行四舍五入 TRUNC

SQL> select hire_date, trunc(hire_date) newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/6/17 星
2005/9/21 星 2005/9/21 星

SQL> select hire_date, trunc(hire_date, 'dd') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/6/17 星
2005/9/21 星 2005/9/21 星

SQL> select hire_date, trunc(hire_date, 'mm') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/6/1 星期
2005/9/21 星 2005/9/1 星期

SQL> select hire_date, trunc(hire_date, 'yy') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -----------
2003/6/17 星 2003/1/1 星期
2005/9/21 星 2005/1/1 星期

转换函数

转换日期/数字到字符串 TO_CHAR

SQL> select hire_date, to_char(hire_date, 'yyyy-mm-dd hh:mi:ss') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -------------------
2003/6/17 星 2003-06-17 12:00:00
2005/9/21 星 2005-09-21 12:00:00

SQL> select hire_date, to_char(hire_date, 'yyyy-mm-dd hh24:mi:ss') newdate from employees where rownum<3;
HIRE_DATE   NEWDATE
----------- -------------------
2003/6/17 星 2003-06-17 00:00:00
2005/9/21 星 2005-09-21 00:00:00

FM可去掉字符串前后空格
SQL> select salary, to_char(salary, 'fm$999,999,999.00') newdate from employees where rownum<3;
    SALARY NEWDATE
---------- ----------------
  24000.00 $24,000.00
  17000.00 $17,000.00

转移字符串为日期 TO_DATE

SQL> SELECT TO_DATE('2020-1-3 12:35:44','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_DATE('2020-1-312:35:44','YY
------------------------------
2020/1/3 星期五 12:35:44

通用函数

转换空值为指定字符或数字 NVL

如果第一个参数为空，返回第二个参数的值
SQL> select NVL('', 2) from dual;
NVL('',2)
---------
2

SQL> select NVL(null, 2) from dual;
NVL(NULL,2)
-----------
          2

根据条件转换空值 NVL2
相当于java中三目运算

如果第一个参数为空，返回第三个参数的值
SQL> select NVL2(null, 2, 4) from dual;
NVL2(NULL,2,4)
--------------
             4
如果第一个参数不为空，返回第二个参数的值
SQL> select NVL2(0, 2, 4) from dual;
NVL2(0,2,4)
--------------
             2

比较两个参数是否相等 NULLIF

参数相同返回空
SQL> SELECT NULLIF('EXP', 'EXP') FROM DUAL;
NULLIF('EXP','EXP')
-------------------
参数不相同返回第一个参数值
SQL> SELECT NULLIF('EXP', 'EX') FROM DUAL;
NULLIF('EXP','EX')
------------------
EXP

SQL> SELECT NULLIF('EX', 'EXP') FROM DUAL;
NULLIF('EX','EXP')
------------------
EX

返回第一个不为空的值 COALESCE

参数可以为任意多个
SQL> SELECT COALESCE('','','') FROM DUAL;
COALESCE('','','')
------------------

SQL> SELECT COALESCE('','','a') FROM DUAL;
COALESCE('','','A')
-------------------
a

多条件选择 CASE表达式

SQL> SELECT LAST_NAME, JOB_ID, SALARY, CASE JOB_ID WHEN 'AD_PRES' THEN SALARY * 1.1 WHEN 'AD_VP' THEN SALARY * 1.3 ELSE SALARY END FROM EMPLOYEES WHERE ROWNUM<3;
LAST_NAME                 JOB_ID         SALARY CASEJOB_IDWHEN'AD_PRES'THENSAL
------------------------- ---------- ---------- ------------------------------
King                      AD_PRES      24000.00                          26400
Kochhar                   AD_VP        17000.00                          22100

多条件选择 DECODE

SQL> SELECT LAST_NAME, JOB_ID, SALARY, DECODE(JOB_ID,'AD_PRES', SALARY * 1.1, 'AD_VP', SALARY * 1.3) FROM EMPLOYEES WHERE ROWNUM<3;
LAST_NAME                 JOB_ID         SALARY DECODE(JOB_ID,'AD_PRES',SALARY
------------------------- ---------- ---------- ------------------------------
King                      AD_PRES      24000.00                          26400
Kochhar                   AD_VP        17000.00                          22100

组函数

组函数操作行集，给出每组结果，对行的集合进行操作。

AVG 求平均值
SUM 求和
MAX 最大值
MIN 最小值
COUNT 求不为空的行数
count(*) 表中记录数
count(列名) 表中该列不为空的记录数
count(distinct 列名) 表中该列不为空且不重复的记录数

SQL> select COUNT(*) from employees;
  COUNT(*)
----------
       107

SQL> select COUNT(department_id) from employees;
COUNT(DEPARTMENT_ID)
--------------------
                 106

SQL> select COUNT(DISTINCT department_id) from employees;
COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
                          11

GROUP BY 将表中数据划分成较小的分组
分组原则：
如果在select 中包含了组函数，就不能出现单独的列名，除非该列出现在 group by 子句中；
使用 where 可以在划分成组以前对行进行过滤；
在 group by 中必须包含列且不能是列别名；
结果中可以使用 order by定义排序方式。
group by后的列，不必出现在select子句中

对一列进行分组
SQL> select department_id, avg(salary) avg, count(*) from employees group by department_id order by avg;
DEPARTMENT_ID        AVG   COUNT(*)
------------- ---------- ----------
           50 3475.55555         45
           30       4150          6
           10       4400          1
           60       5760          5
           40       6500          1
                    7000          1
          100 8601.33333          6
           80 8955.88235         34

对两列进行分组
SQL> select department_id, avg(salary) avg, count(*) from employees group by department_id,job_id order by avg;
DEPARTMENT_ID        AVG   COUNT(*)
------------- ---------- ----------
           30       2780          5
           50       2785         20
           50       3215         20
           10       4400          1
           60       5760          5
           20       6000          1
           40       6500          1
                    7000          1
           50       7280          5

HAVING 约束分组结果，对分组后的结果进行过滤
having 通常与 group by 联合使用，用来过滤由 group by 返回的记录集；
弥补了 where 关键字不能与聚合函数联合使用的不足。

SQL> select department_id, max(salary) max from employees group by department_id having max(salary) > 10000 order by max;
DEPARTMENT_ID        MAX
------------- ----------
           30      11000
          100      12008
          110      12008
           20      13000
           80      14000
           90      24000
6 rows selected

SQL> select department_id, avg(salary) avg from employees group by department_id having max(salary) > 10000;
DEPARTMENT_ID        AVG
------------- ----------
          100 8601.33333
           30       4150
           90 19333.3333
           20       9500
          110      10154
           80 8955.88235
6 rows selected

函数

单行函数

字符函数

大小写处理函数

字符处理函数

数字函数

日期函数

转换函数

通用函数

组函数

猜你喜欢

热点阅读