SQL 10 函数 1 数学函数

1.1 求绝对值

SELECT FWeight -50, ABS(FWeight - 50), ABS(-5.38) FROM T_Person

 

1.2 求指数

SELECT FWeight, POWER(FWeight, -0.5), POWER(FWeight, 2), POWER(FWeight, 3), POWER(FWeight, 4) FROM T_Person

 

1.3 求平方根

SELECT Fweight, SQRT(FWeight) FROM T_Person

 

1.4 求随机数

MySQL:

SELECT RAND()

Oracle:

SELECT dbms_random.value FROM dual

除了上面这种使用方式，dbms_random包中还提供了其他几种方法来完成其他的随机处理。

1)dbms_random.value(low, high)用来反水一个大于或等于low、小于或等于high的随机数

SELECT dbms_random.value(60, 100) FROM dual

2)dbms_random.normal用来返回服从正态分布的一组数。次正态分布标准偏差值为1， 期望值为0.这个函数返回的数值中有68%在-1~+1之间，95%在-2～+2之间，99%在-3～+3之间。执行下面的SQL语句：

SELECT dbms_random.normal FROM DUAL

3)dbms_random.string(opt, len)用来返回一个随机字符串，opt为选项参数，len表示反水的字符串长度，最大值为60.参数opt可选值如下：

'U' - 返回全是大写的字符串。

'L' - 返回全是小写的字符串。

'A' - 返回大小写结合的字符串。

'X' - 返回全市大写和数字的字符串。

'P' - 返回键盘上出现字符的随机组合。

SELECT dbms_random.string('U', 8) as UP,
dbms_random.string('L', 5) as LP,
dbms_random.string('A', 6) as AP,
dbms_random.string('X', 6) as XP,
dbms_random.string('P', 8) as PP
FROM DUAL;

 

1.5 舍入到最大整数

浮点数，取整数值加1

MySQL:

SELECT FName, FWeight, CEILING(FWeight), CEILING(FWeight*-1) FROM T_Person

 

Oracle:

SELECT FName, FWeight, CEIL(FWeight), CEIL(Feight*-1) FROM T_Person

 

1.6 舍人到最小整数

浮点数，只取整数值

SELECT FName, FWeight, FLOOR(FWeight), FLOOR(FWeight*-1) FROM T_Person

 

1.7 四舍五入

ROUND()函数也是用来进行数值四舍五入的。ROUND函数有两个参数和单一参数两种用法，下面分别进行进行介绍。

1) 两个参数

两个参数的ROUND()函数用法为ROUND(m,d)，其中m为带进行四舍五入的数值，而d则为计算精度，也就是进行四舍五入

SELECT FName, FWeight, ROUND(FWeight, 1), ROUND(FWeight*-1, 0), ROUND(FWeight, -1) FROM T_Person

 

2) 单个参数

SELECT FName, FWeight, ROUND(FWeight), ROUND(FWeight*-1) FROM T_Person

 

1.8 求正弦值

SELECT FName, FWeight, SIN(FWeight) FROM T_Person

 

1.9 求余弦值

SELECT FName, FWeight, COS(FWeight) FROM T_Person

 

1.10 求反正弦值

SELECT FName, FWeight, ASIN(FWeight) FROM T_Person

 

1.11 求反余弦值

SELECT FName, FWeight, ACOS(1/FWeight) FROM T_Person

 

1.12 求正切值

SELECT FName, FWeight, TAN(1/FWeight) FROM T_Person

 

1.13 求反正切值

SELECT FName, FWeight, ATAN(1/FWeight) FROM T_Person

 

1.14 求两个变量的反正切

MySQL:

SELECT FName, FWeight, ATAN2(FWeight, 2) FROM T_Person

Oracle:

SELECT FName, FWeight, ATN2(FWeight, 2) FROM T_Person

 

1.15 求余切

MySQL:

SELECT FName, FWeight, COT(FWeight) FROM T_Person

Oracle :

SELECT FName, FWeight, 1/tan(FWeight) FROM T_Person

1.16 求圆周率

MySQL:

SELECT FName, FWeight, FWeight * PI() FROM T_Person

Oracle :

SELECT FName, FWeight, FWeight * acos(-1) FROM T_Person

 

1.17 弧度制转换为角度制

MySQL:

SELECT FName, FWeight, DEGREES(FWeight) FROM T_Person

Oracle:

SELECT FName, FWeight, (FWeight*180)/acos(-1) FROM T_Person

 

1.18 角度制转换为弧度制

MySQL:

SELECT FName, FWeight, RADIANS(FWeight) FROM T_Person

Oracle :

SELECT FName, FWeight, (FWeight*acos(-1)/180) FROM T_Person

 

1.19 求符号

SELECT FName, FWeight, SIGN(FWeight-48.68) FROM T_Person

 

1.20 求整除余数

MySQL, Oracle:

SELECT FName, FWeight, MOD(FWeight, 5) FROM T_Person

 

1.21 求自然对数

MySQL:

SELECT FName, FWeight, LOG(FWeight) FROM T_Person

Oracle:

SELECT FName, FWeight, LN(FWeight) FROM T_Person

1.22 求以10为底的对数

MySQL:

SELECT FName, FWeight, LOG10(FWeight) FROM T_Person

Oracle:

SELECT FName, FWeight, LOG(10, FWeight) FROM T_Person

 

1.23 求幂

SELECT FName, FWeight, POWER(FWeight) FROM T_Person