1. ROUND(n,i): 四舍五入函数
说明:
(1) n是任意数值,是必需的。i是整数,是可选的,忽略则默认为0
(2) 若i为0,则取整数位四舍五入;若 i > 0, 则对n四舍五入取整到小数点左侧第i位;若 i < 0, 则对n四舍五入取整到小数点右侧第i位。
(3) Oracle和MySQL中四舍五入函数相同
例:
SQL> SELECT ROUND(3.141592653) FROM dual;
ROUND(3.141592653)
------------------
3
SQL> SELECT ROUND(3.141592653 , 0) FROM dual;
ROUND(3.141592653,0)
--------------------
3
SQL> SELECT ROUND(3.141592653 , -1) FROM dual;
ROUND(3.141592653,-1)
---------------------
0
SQL> SELECT ROUND(35.41592653 , -1) FROM dual;
ROUND(35.41592653,-1)
---------------------
40
SQL> SELECT ROUND(3.141592653 , 4) FROM dual;
ROUND(3.141592653,4)
--------------------
3.1416
SQL> SELECT ROUND(-5.6) FROM dual;
ROUND(-5.5)
-----------
-6
2. TRUNC(n,i): 截断函数
说明:
(1) n是任意数值,是必需的。i是整数,是可选的,忽略则默认为0
(2) 在MySQL中,与TRUNC对应的函数为TRUNCATE(n,i),但是在MySQL中,两个参数都是必须的,参数i不可省略
例:
SQL> SELECT TRUNC(3.141592653) FROM dual;
TRUNC(3.141592653)
------------------
3
SQL> SELECT TRUNC(3.141592653 , 0) FROM dual;
TRUNC(3.141592653,0)
--------------------
3
SQL> SELECT TRUNC(3.141592653 , -1) FROM dual;
TRUNC(3.141592653,-1)
---------------------
0
SQL> SELECT TRUNC(35.41592653 , -1) FROM dual;
TRUNC(35.41592653,-1)
---------------------
30
SQL> SELECT TRUNC(3.141592653 , 4) FROM dual;
TRUNC(3.141592653,4)
--------------------
3.1415
SQL> SELECT TRUNC(-5.6) FROM dual;
TRUNC(-5.6)
-----------
-5
3. CEIL(n):向上取整函数
说明:n是任意数值,是必需的。
例:
SQL> SELECT CEIL(3.1415926) FROM dual;
CEIL(3.1415926)
---------------
4
SQL> SELECT CEIL(-3.1415926)FROM dual;
CEIL(-3.1415926)
----------------
-3
4. FLOOR(n): 向下取整函数
说明:n是任意数值,是必需的。
例:
SQL> SELECT FLOOR(3.1415926) FROM dual;
FLOOR(3.1415926)
----------------
3
SQL> SELECT FLOOR(-3.1415926) FROM dual;
FLOOR(-3.1415926)
-----------------
-4
5. MOD(n1 , n2): 求余函数
例:
SQL> SELECT MOD(9 , 3) FROM dual;
MOD(9,3)
----------
0
SQL> SELECT MOD(11 , 3)FROM dual;
MOD(11,3)
----------
2
SQL> SELECT MOD(9.3,3.2) FROM dual;
MOD(9.3,3.2)
------------
2.9
6. REMAINDER(n1,n2) : 确定距离n1最近的n2的倍数,并且返回 n1 与 距离n1最近的n2的倍数之间的差值
说明:n1和n2均为数值且都是必需的
例:
SQL> SELECT REMAINDER(9,3) FROM dual;
REMAINDER(9,3)
--------------
0
SQL> SELECT REMAINDER(11,3) FROM dual;
REMAINDER(11,3)
---------------
-1 //距离11最近的3的倍数是12, 11 - 12 = -1
7. ABS(n): 绝对值函数
例:
SQL> SELECT ABS(3.1415926) FROM dual;
ABS(3.1415926)
--------------
3.1415926
SQL> SELECT ABS(-3.1415926) FROM dual;
ABS(-3.1415926)
---------------
3.1415926
8. SQRT(n): 平方根函数
说明:n >= 0
例:
SQL> SELECT SQRT(121) FROM dual;
SQRT(121)
----------
11
9. POWER(x,y): 幂函数,求 x 的 y 次幂
例:
SQL> SELECT POWER(11,2) FROM dual;
POWER(11,2)
-----------
121
SQL> SELECT POWER(8 , 1/3) FROM dual;
POWER(8,1/3)
------------
2