三、单行函数--SQL基础

有道云链接

类型：

number 数字函数

character 字符函数

date 日期函数

conversion 转换函数

general 通用函数

 

1.字符函数(character)

1.1 大写转换函数

upper('string')--大写STRING
inicap('string')--首字母大写String 

1.2 字符串转换函数

concat(Hello,'World') --HELLOWORLD
substr('HelloWorld',1,5) --从第1个开始截取，取5个字符 Hello
length('HelloWorld') --字符串长度 10
instr('HelloWorld','W') --取'W'的位置 6
Lpad(salary,5,'*') --在左L补齐5个*   *****24000
Rpad(salary,5,'*') --在右R补齐5个*   *****24000
trim('H' from 'HelloWorld') --trim用法1：从HelloWorld中去除H  elloWorld
trim('HelloWorld') --trim用法2：去除两边的空格
trim('Hello World') --中间的空格没办法去除 Hello World 

2.数字函数(number)

round(45.926,2) --四舍五入保留2位小数  45.93
trunc(45.926,2) --保留2为小数，步四舍五入  45.92
mod(1600,300) --求余数 100
round(45.926,-1) --负数表示个位四舍五入  50 

3.日期函数(date)

格式：

YYYY-MM-DD

YYYY-MM-DD HH24:MI:SS

YYYY-MM-DD HH:MI:SS

DD-MMM-RR

DD-MMM-YY

MM-DD-YYYY

3.1 日期常用的函数及其含义

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --后减去前的月份 19.6774193548387
ADD_MONTHS('11-JAN-95',6) --加上6个月
NEXT_DAY('01-SEP-95','FRIDAY') --1995-9-1 第一个周五 1995/9/8
NEXT_DAY('01-SEP-95',1) --下一周的第一天 即第一个周日 1995/9/3
NEXT_DAY(TO_DATE('1995-9-1'),'YYYY-MM-DD',1) --1995/9/3
LAST_DAY('01-FEB-95') --2月最后一天 1995/2/28
ROUND(TO_DATE('25-JUL-95'),'MONTH') --对月份四舍五入，15日后五入 1995/8/1
ROUND(TO_DATE('25-JUL-95'),'YEAR') --对年份 四舍五入 1996/7/1
TRUNC(TO_DATE('25-JUL-95'),'MONTH') --日归1  1995/7/1
TRUNC(TO_DATE('25-JUL-95'),'YEAR') --日月归1 1995/1/1 

3.2 日期 & 字符串 的转换(to_char & to_date)

# to_date('string','转换的日期格式')

select to_date('25-jul-91') from dual; --1991/7/25

 

# to_char

select to_char(sysdate,'YYYY/MM/DD HH:MI:SS') from dual;

 

一些格式的含义：

YYYY --4位数字 年份

YEAR --英文描述年份

select to_char(to_date('17-SEP-90'),'YEAR') from dual;--Nineteen ninety

 

MM --2位数字月份

MONTH --英文描述月份

MON --3位英文缩写月份

DD --2位数字日期

DAY --英文星期几

DY --3位英文星期几

HH24:MI:SS --时分秒格式化

DDspth --英文月中第几天

fm --格式化关键字，可选

SELECT last_name,to_char(hire_date,'fmDD "of" Month YYYY') AS HIREDATE
FROM EMPLOYEES;
SELECT TO_CHAR(SYSDATE,'FMDD') FROM DUAL; --26

 

# 日期的运算

SELECT LAST_NAME,(SYSDATE_HIRE_DATE)/7 AS WEEK,
       SYSDATE+1 AS TOMORROW,
       HIRE_DATE+ 8/24
FROM EMPLOYEES
WHERE DEPARTMENT_ID=90;

 

 

4.转换函数(conversion):隐式转换 & 自写转换函数

4.1 隐式转换

4.1.1 赋值转换

varchar2/char => number

varchar2/char => date

number => varchar2

date => varchar2

 

4.1.2 表达式比较操作只能

varchar2/char => number

varchar2/char => date date结果为英文月份缩写3位

 

4.2 显式转换

4.2.1 to_char (number,'格式')

9 --表示1个数字

0 --强制显示为0

$ --放一个美元符号

L --使用符号本地币种符号

. --显示一个小数点占位符

, --显示一个千分位占位符

--例子
select to_char(salary,'L99,999.00') salary
from employees
where last_name='Ernst';
--更改本地币种
alter session set NSL_CURRENCY='$';

 

4.2.2 to_number

--例子
select to_number('4456') from dual; --4456
select to_number('$4,456','$9,999') from dual; --去掉','、'$',4位数字  4456

 

4.2.3 to_date

select to_date('22-FEB-11') from dual;
select to_date('2044-1-22','YYYY-MM-DD') from dual; 

5.其他单行函数(general)

NVL(expr1,expr2)                                                 expr1为空，返回expr2

NVL2(expr1,expr2,expr3)                                     expr1为空，则返回expr3，否则返回expr2

NULLIF(expr1,expr2)                                           expr1和expr2相等，则返回空，不等返回1

COALESCE(expr1,expr2,...,exprn)                     全部为空，返回NULL否则从第一个开始直到找到不为空的参数返回

SELECT LAST_NAME,SALARY,COMMISSION_PCT,
       NVL2(COMMISSION_PCT,'SAL+COMM','SAL') INCOME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(50,80);
 
SELECT FIRST_NAME,LENGTH(FIRST_NAME) "EXPR1",
       LAST_NAME,LENGTH(LAST_NAME) "EXPR2",
       NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME)) RESULT
FROM EMPLOYEES; 

6.CASE 语句

SELECT LAST_NAME,JOB_ID,SALARY,
       (CASE JOB_ID
             WHEN 'IT_PROG' THEN 1.10 * SALARY
             WHEN 'SA_REP'  THEN 1.20 * SALARY
             WHEN 'ST_CLERK'THEN 1.15 * SALARY
             ELSE SALARY
         END
       )REVISED_SALARY 
FROM EMPLOYEES; 

7.DECODE 语句(与case语句作用一样)

SELECT LAST_NAME,JOB_ID,SALARY,
       (decode JOB_ID
               'IT_PROG', 1.10 * SALARY,
               'SA_REP' , 1.20 * SALARY,
               'ST_CLERK',1.15 * SALARY,
                SALARY   
       )REVISED_SALARY 
FROM EMPLOYEES;