[玩转SQL]:03 单行函数_pyhton函数唐老鸭-优快云博客

本文链接：https://blog.youkuaiyun.com/softomg/article/details/37562765

单行函数

SQL函数分为单行函数和多行函数.

单行函数输入只有一行数据,输出一行数据.

多行函数输入有多行数据,只输出一行数据.

语法:

function_name [(arg1,arg2,…)]

分类

字符串函数

数字函数

日期函数

转换函数

通用函数

字符串函数

大小写变换函数

1) LOWER 把所有字母变成小写

SELECT LOWER('SQL') 
FROM   dual;

2) UPPER 把所有字母变大写

SELECT UPPER('sql') 
FROM   dual;

3) INITCAP 把单词首字母变成大写其他字母变成小写

SELECT INITCAP('sql')
FROM   dual;

字符串操作函数

1) CONCAT 链接操作符

SELECT CONCAT('Hello','World') 
FROM   dual;

2) SUBSTR 取子字符串

SELECT SUBSTR('HelloWorld',1,5) 
FROM   dual;

1是起始位置,5是字符串长度.若不给字符串长度默认到字符串结尾.

3) LENGTH 取字符串长度

SELECT LENGTH('HelloWorld') 
FROM   dual;

4) INSTR 查找位置

SELECT INSTR('HelloWorld','W') 
FROM   dual;

5) LPAD 左填充

SELECT LPAD(salary, 10, '*') 
FROM   employees;

用10指定字符串长度,*指定填充字符,默认是空白.

6) RPAD 右填充

SELECT RPAD(salary, '10', '*') 
FROM   employees;

7) TRIM 移除字符串

SELECT TRIM('H' FROM 'HelloWorld') 
FROM   dual;

不加关键字'H',默认删除空格

8) REPLACE 替换字符串

SELECT REPLACE('JACK and JUE','J','BL') 
FROM   dual;

SQL中角标从1开始.

数字函数

1) ROUND 四舍五入

SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) 
FROM   dual;

2) TRUNC 截取数字

SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1) 
FROM   dual;

ROUND与TRUNC第二个参数正数就是小数点往前数多少位.

负数就是小数点往后数多少位.

3) MOD 取余数

SELECT mod(1600,300) 
FROM   dual;

日期函数

日期内部存储方式是数字,日期默认格式是DD-MM-RR 日-月-年(世纪,非YY)

YY与RR

当前(年)	输入	RR	YY
1995	95	1995	1995
1995	17	2017	1917
2001	17	2017	2017
2001	95	1995	2095

YY算法

取当前年份的前两位与输入的数字拼接成年份,可见并不准确.

RR算法

	输入年份0-49	输入年份50-99
当前年份0-49(后两位)	使用当前年份的前两位与输入拼接成年份.	使用上个世纪的前两位与输入拼接成年份.
当前年份50-99(后两位)	使用下个世纪的前两位与输入拼接成年份.	使用当前年份的前两位与输入拼接成年份.

sysdate 返回当前函数

SELECT sysdate 
FROM   dual;

日期运算

--查询id为90的员工入职多少周 
SELECT last_name, (SYSDATE - hire_date)/7 AS WEEKS 
FROM   employees 
WHERE  department_id = 90;

1) NONTHS_BETWEEN 查看给定两个时间的相差月数

SELECT MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 
FROM   dual;

2) ADD_MONTHS 给定时间增加月数的时间

SELECT ADD_MONTHS('31-JAN-96',1) 
FROM   dual;

3) NEXT_DAY 给定时间点的下一时间点

SELECT NEXT_DAY('01-SEP-95','FRIDAY') 
FROM   dual;

4) LAST_DAY 当月的最后一天

SELECT LAST_DAY('01-FEB-95') 
FROM   dual;

5) 日期四舍五入

SELECT ROUND(SYSDATE,'MONTH') 
FROM   dual;

SELECT ROUND(SYSDATE,'YEAR') 
FROM   dual;

6) 日期截取

SELECT TRUNC(SYSDATE,'MONTH') 
FROM   dual;

SELECT TRUNC(SYSDATE,'YEAR') 
FROM   dual;

转换函数

转换函数分为隐式转换和显示转换,隐式转换例如日期转换.

尽量使用显示转换.

隐式转换

--字符串隐式转换为日期
SELECT employee_id,first_name
FROM   employees
WHERE  hire_date > '01-JAN-90';

--字符串隐式转换为数字
SELECT '923.14' + 25.05
FROM   dual;

ORACLE三种类型：NUMBER、CHARACTER、DATE.

转换方式	转换函数
NUMBER -> CHARACTER	TO_CHAR
CHARACTER-> NUMBER	TO_NUMBER
DATE -> CHARACTER	TO_CHAR
CHARACTER-> DATE	TO_DATE

DATE->CHARACTER

语法:

TO_CHAR(date, 'format_model')

SELECT employee_id,TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM   employees
WHERE  last_name = 'Higgins';

--调整时间显示格式
SELECT employee_id,TO_CHAR(hire_date, 'YYYY-MM-DD') Month_Hired
FROM   employees
WHERE  last_name = 'Higgins';

--增加fm可以去除前边的0
SELECT employee_id,TO_CHAR(hire_date, 'fmYYYY-MM-DD') Month_Hired
FROM   employees
WHERE  last_name = 'Higgins';

format_model格式

YYYY	四位年份
YEAR	英语年份
MM	月份
MONTH	英语月份
MON	英语简称月份
DY	简写星期
DAY	英语星期
DD	日期
HH24	24小时时间制
HH12	12小时时间制
MI/SS	分/秒钟

SELECT employee_id,TO_CHAR(hire_date, 'YYYY-MM-DD HH24:MI:SS') Month_Hired
FROM   employees
WHERE  last_name = 'Higgins';

NUMBER-> CHARACTER

语法:

TO_CHAR(number,'format_model')

format_model格式

9	0-9
0	显示0
$	显示$
.	显示.
,	显示,

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

如果format_model小于实际值,数据库显示为多个#.

CHARACTER->NUMBER

语法:

TO_NUMBER(char[,'format_model'])

SELECT TO_NUMBER('970.13')
FROM   dual;

SELECT TO_NUMBER('-$12,345.67', '$99,999.99')
FROM   dual;

CHARACTER->DATE

语法:

TO_DATE(char[,'format_model'])

SELECT TO_DATE('July 4, 2007', 'Month DD, YYYY')
FROM   dual;

通用函数

1) NVL(expr1,expr2)

等同于

if(expr1 != null)
{
    return expr1
}
else
{
    return expr2
}

--计算年薪
SELECT last_name,salary, NVL(commission_pct,0),(salary*12) +(salary*12*NVL(commission_pct,0)) AN_SAL
FROM employees;

2) NVL2(expr1,expr2, expr3)

等同于

if(expr1 != null)
{
    return expr2
}
elsif(expr1== null)
{
    return expr3
}

3) NULLIF(expr1,expr2)
等同于

if(expr1 == expr2)
{
    return null;
}
elsif(expr1!= expr2)
{
    return expr1
}

4) COALESCE(expr1,expr2…exprN)

从左往右检测,返回第一个不是NULL的值.

CASE

语法:

CASE expr WHEN compatison_expr1 THEN return_expr1
         [WHEN compatison_expr2 THEN return_expr2
          WHEN compatison_exprN THEN return_exprN
          ELSE else_expr]
END

--根据不同职位来涨薪
SELECT last_name,job_id, salary,
       CASE job_id WHEN 'IT_PROG'   THEN 1.10*salary
                   WHEN 'ST_CLERK'  THEN 1.15*salary
                   WHEN 'SA_REP'    THEN 1.20*salary
                   ELSE  salary     END   "REVISED_SALARY"
FROM   employees;

DECODE(Oracle独有)

语法:

DECODE(col|expression,search1,result1
                    [,search2,result2…]
[,default])

--根据不同职位来涨薪
SELECT last_name,job_id, salary,
       DECODE(job_id, 'IT_RPOG', 1.10*salary,
                      'ST_CLERK',1.15*salary,
                      'SA_REP' , 1.20*salary,
              salary)
       REVISED_SALARY
FROM   employees;

QQ    :   413844436
Email :   softomg@163.com
Blog :   http://blog.youkuaiyun.com/softomg