数值及其他函数
数值函数
**主要针对于数值做处理的函数**
1.取绝对值
ABS(NUM):获取数值NUM对应的绝对值。
示例:
1).取-2的绝对值
SELECT ABS(-2) FROM DUAL;
2).MONTHS_BETWEEN(),不知道谁大,嵌套一个绝对值函数
SELECT ABS(MONTHS_BETWEEN(SYSDATE,SYSDATE+31)) FROM DUAL;
3).函数只有一个参数,不能为含有其他字符
SELECT ABS(10) FROM DUAL;
SELECT ABS('10') FROM DUAL;
SELECT ABS('10A') FROM DUAL;--报错 无效数字
SELECT ABS(10,2) FROM DUAL;--报错 参数个数无效
2.向上取整与向下取整
CEIL(NUM) :向上取整,获取离NUM相近的两个整数中较大的整数
FLOOR(NUM):向下取整,获取离NUM相近的两个整数中较小的整数
示例:
SELECT FLOOR(1.3265) FROM DUAL;--1
SELECT CEIL(1.3265) FROM DUAL;--2
SELECT FLOOR(-1.3265) FROM DUAL;-- -2
SELECT CEIL(-1.3265) FROM DUAL;-- -1
3.ROUND()/TRUNC() 对数值进行四舍五入或截取
ROUND(NUM,V):将数值NUM按照四舍五入的原则保留V位小数
TRUNC(NUM,V):将数值NUM截取到第V位小数
个 十 百 千 万
十分位 百分位 千分位
–当精度为负数时,按照小数点前N为进行四舍五入、截取
示例:
1).对数据123.456做处理
SELECT ROUND(123.556) FROM DUAL;--124
SELECT ROUND(123.556,0) FROM DUAL;--124\
SELECT ROUND(123.556,2) FROM DUAL;--123.56
SELECT ROUND(123.556,-2) FROM DUAL;--100
SELECT ROUND(153.556,-2) FROM DUAL;--200
SELECT ROUND(123.556,-1) FROM DUAL;--120
SELECT TRUNC(123.556) FROM DUAL;--123
SELECT TRUNC(123.556,0) FROM DUAL;--123
SELECT TRUNC(123.556,2) FROM DUAL;--123.55
SELECT TRUNC(123.556,-2) FROM DUAL;--100
SELECT TRUNC(153.556,-2) FROM DUAL;--100
SELECT TRUNC(123.556,-1) FROM DUAL;--120
–既然两个函数可以对数值操作,可不可以对日期做操作?? 如何进行的??
4.ASCII码值
ASCII(N): 将字符N转化为ASCII码值
CHR(N): 将ASCII码值N转化为字符
示例:
SELECT ASCII('李') FROM DUAL;
SELECT CHR(15113614) FROM DUAL;
SELECT ASCII('李名') FROM DUAL;
5.取余函数
MOD(VALUE1,VALUE2) 取value1/value2 的余数
示例:
假设有四间教室,30名学生,怎样合理的将学生分配到教室中?
SELECT MOD(8,5) FROM DUAL;--3
6.SQRT()一个参数,返回该参数的平方根.
示例:
SELECT SQRT(5) FROM DUAL;
SELECT SQRT(4) FROM DUAL;
SELECT SQRT(-4) FROM DUAL;--报错
7.POWER(X,Y):幂指函数 X的Y次方
SELECT POWER(6,'L') FROM DUAL;--报错 无效数字
SELECT POWER(6,1) FROM DUAL;
SELECT POWER(6,2) FROM DUAL;
/其他函数/
1.ROWID/ROWNUM
ROWID:自动生成一串18位字符串,已知rowID,查询时效率比较高
ROWNUM:自动生成一组递增的数值
ROWID:18字符串,唯一的
SELECT EMP.*,ROWID FROM EMP;
SELECT * FROM EMP WHERE ROWID=‘AAAThdAAEAAAAHUAAA’;
数据去重
SELECT *
FROM EMP
WHERE ROWID IN
(SELECT MIN(ROWID) FROM EMP GROUP BY EMPNO, ENAME, SAL, JOB);
–多组重复的数据中,rowID不重复,利用分组取出一个最大或最小的ROWID,然后过让外表中的rowID=最大或最小的ROWID
–将重复的数据去掉
ROWNUM:一组递增的数组,不唯一的,只在当前语句起效
SELECT EMP.*,ROWNUM FROM EMP;
SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM<=5;
SELECT EMP.*,ROWNUM FROM EMP WHERE ROWNUM>5;--无结果
–用于排序,查找数据
2.DISTINCT 去重函数
SELECT DISTINCT JOB FROM EMP;
SELECT DISTINCT JOB,SAL FROM EMP;
—去重,当且仅当全部重复时,才会去重,将多条重复数据保留为一条
3.CASE WHEN THEN END 判断函数
DECODE(COLUMN,VALUE1,VALUES1,VALUE2,VALUES2,VALUE3,VALUES3,VALUES) --往往用于等值比较
示例:
1.将10号部门的员工标记为10号部门,20/30均如此
SELECT EMP.*,CASE WHEN DEPTNO=10 THEN '10号部门'
WHEN DEPTNO=20 THEN '20号部门'
WHEN DEPTNO=30 THEN '30号部门'
ELSE
'1111'
END FROM EMP;
SELECT EMP.*,CASE DEPTNO WHEN 10 THEN '10号部门'
WHEN 20 THEN '20号部门'
WHEN 30 THEN '30号部门'
ELSE
'1111'
END FROM EMP;
SELECT EMP.*,DECODE(DEPTNO,10,'10号部门',20,'20号部门',30,'30号部门','211') FROM EMP;--只能用作等着比较
4.NVL()/NVL2()
NVL(COLUMN,VALUE):若当COLUMN列为非空时,值为其本身,为空时赋值VALUE;
NVL2(COLUMN1,VALUE1,VALUE2) 当COLUMN1列为非空时,赋值VALUE1,为空时赋值VALUE2;
示例:
SELECT COMM, NVL(COMM,0) FROM EMP;
SELECT COMM, NVL2(COMM,'非空','空值') FROM EMP;
5.返回集合中第一个不为空的内容
COALESCE(c1,c2,c3,c4,…cn):返回括号中第一个非空表达式,如果都为空,则返回空
示例:
SELECT COALESCE(NULL,NULL,'A','B') FROM DUAL;