函数

作者: 风中小酌 | 来源:发表于2020-03-03 22:53 被阅读0次

Excel(三)
if、else if、for、while、repeat函数
strsplit、mapply、paste、match函数
Oracle中常用函数（SQL）
MySQL函数
BI-SQL丨AND & OR & IN
Python之函数
函数基本知识
积分表——不定期更新
MySQL基本使用

单行函数.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

Excel(三)
AND函数 OR函数 NOT函数 IF函数频率分析函数FREQUENCY
if、else if、for、while、repeat函数
①if函数 ②else if函数 ③for函数 ④while函数 ⑤repeat函数
strsplit、mapply、paste、match函数
strsplit函数 mapply函数 strsplit函数 mapply函数 paste函数 match函数第...
Oracle中常用函数（SQL）
Oracle函授有以下几个分类：数字函数、字符函数、日期函数、转换函数、集合函数、分析函数数字函数：字符函数：...
MySQL函数
字符函数数字运算函数比较运算符和函数日期时间函数信息函数聚合函数加密函数流程函数
BI-SQL丨AND & OR & IN
AND函数 & OR函数 & IN函数 AND函数、OR函数和IN函数都可以理解是WHERE函数的补充，当然也可以...
Python之函数
课程大纲函数定义函数的参数函数的返回值高阶函数函数作用域递归函数匿名函数内置函数函数式编程将函...
函数基本知识
函数函数的定义： def 函数名（）函数的调用：函数名（） #不能将函数调用放在函数定义上方函数的文档注...
积分表——不定期更新
基本初等函数包括：常函数：幂函数指数函数对数函数三角函数反三角函数Ｉ、反函数Ⅱ、复合函数：初等函数（...
MySQL基本使用
函数常用函数数学函数字符串函数日期函数

函数

单行函数

字符函数

大小写处理函数

字符处理函数

数字函数

日期函数

转换函数

通用函数

组函数

相关文章

Excel(三)

if、else if、for、while、repeat函数

strsplit、mapply、paste、match函数

Oracle中常用函数（SQL）

MySQL函数

BI-SQL丨AND & OR & IN

Python之函数

函数基本知识

积分表——不定期更新

MySQL基本使用

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库