四、SQL函数①(数学函数1)

数据准备

创建数据表

MYSQL:
CREATE TABLE T_Person (
FIdNumber VARCHAR(20),
FName VARCHAR(20),
FBirthDay DATETIME,
FRegDay DATETIME,
FWeight DECIMAL(10,2))

MSSQLServer:
CREATE TABLE T_Person (
FIdNumber VARCHAR(20),
FName VARCHAR(20),
FBirthDay DATETIME,
FRegDay DATETIME,
FWeight NUMERIC(10,2))

Oracle:
CREATE TABLE T_Person (
FIdNumber VARCHAR2(20),
FName VARCHAR2(20),
FBirthDay DATE,
FRegDay DATE,
FWeight NUMERIC(10,2))

DB2:
CREATE TABLE T_Person (
FIdNumber VARCHAR(20),
FName VARCHAR(20),
FBirthDay DATE,

准备数据值：

MYSQL、MSSQLServer、DB2：

INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789120","Tom","1981-03-22","1998-05-01",56.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789121","Jim","1987-01-18","1999-08-21",36.17);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789122","Lily","1987-11-08","2001-09-18",40.33);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789123","Kelly","1982-07-12","2000-03-01",46.23);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789124","Sam","1983-02-16","1998-05-01",48.68);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789125","Kerry","1984-08-07","1999-03-01",66.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789126","Smith","1980-01-09","2002-09-23",51.28);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES("123456789127","BillGates","1972-07-18","1995-06-19",60.32);

Oracle：

INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789120","Tom",TO_DATE("1981-03-22", "YYYY-MM-DD HH24:MI:SS"),TO_DATE("1998-05-01", "YYYY-MM-DD HH24:MI:SS"),56.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789121","Jim",TO_DATE("1987-01-18", "YYYY-MM-DD HH24:MI:SS"),TO_DATE("1999-08-21", "YYYY-MM-DD HH24:MI:SS"),36.17);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789122","Lily",TO_DATE("1987-11-08", "YYYY-MM-DD HH24:MI:SS"),TO_DATE("2001-09-18", "YYYY-MM-DD HH24:MI:SS"),40.33);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789123","Kelly",TO_DATE("1982-07-12", "YYYY-MM-DD HH24:MI:SS"),TO_DATE("2000-03-01", "YYYY-MM-DD HH24:MI:SS"),46.23);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789124","Sam",TO_DATE("1983-02-16", "YYYY-MM-DD HH24:MI:SS"),TO_DATE("1998-05-01", "YYYY-MM-DD HH24:MI:SS"),48.68);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789125","Kerry",TO_DATE("1984-08-07", "YYYY-MM-DD HH24:MI:SS"),TO_DATE("1999-03-01", "YYYY-MM-DD HH24:MI:SS"),66.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789126","Smith",TO_DATE("1980-01-09", "YYYY-MM-DD HH24:MI:SS"),TO_DATE("2002-09-23", "YYYY-MM-DD HH24:MI:SS"),51.28);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ("123456789127","BillGates",TO_DATE("1972-07-18", "YYYY-MM-DD HH24:MI:SS"),TO_DATE("1995-06-19", "YYYY-MM-DD HH24:MI:SS"),60.32);

求绝对值

ABS()函数用来返回一个数值的绝对值。该函数接受一个参数，这个参数为待求绝对值的表达式。
如：SELECT FWeight - 50,ABS(FWeight - 50) , ABS(-5.38) FROM T_Person

求指数

POWER()函数是用来计算指数的函数。该函数接受两个参数，第一个参数为待求幂的表达式，第二个参数为幂。
如：

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

求平方根

SQRT()函数是用来计算平方根的函数。该函数接受一个参数，这个参数为待计算平方根的表达式。
如：

SELECT FWeight,SQRT(FWeight) FROM T_Person

求随机数

在生成随机密码、随机问题等的时候需要使用随机算法生成随机数。不同的数据库提供的生成随机数的方法不同。

MySQL：RAND()函数用来生成随机算法。如：

SELECT RAND()

执行结果：

0.4614449609115853

该结果是随机的

MSSQLServer：RAND()函数用来生成随机算法

(使用方法与MySQL类似)，除此之外RAND()函数还支持一个参数，这个参数为随机数种子，执行下面的SQL语句：

SELECT RAND(9527)

执行结果：

0.8910896774185367

Oracle

Oracle中无内置的生成随机数的函数，但可用 dbms_random来生成随机数，使用方法如下：

SELECT dbms_random.value FROM dual

执行结果：

VALUE
0.14190212623840265315906642197785380122

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

dbms_random.value (low, high)用来返回一个大于或等于low，小于high的随机数。如

SELECT dbms_random.value(60,100) FROM dual

执行结果：

DBMS_RANDOM.VALUE(60,100)
89.1205784709389820708190169252633962636

dbms_random. normal 用来返回服从正态分布的一组数。此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有68%是介于-1 与+1 之间，95%介于-2 与+2 之间，99%介于-3与+3之间。如：

SELECT dbms_random.normal FROM dual

执行结果：

NORMAL
-0.8376085098260252406127477817042282552217

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

"U"：返回全是大写的字符串。
"L"：返回全是小写的字符串。
"A"：返回大小写结合的字符串。
"X"：返回全是大写和数字的字符串。
"P"：返回键盘上出现字符的随机组合。

可以使用dbms_random.string 来产生随机的用户密码或者验证码。执行下面的SQL 语句：

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

执行结果：

UP LP AP XP PP
RXSINVJD swokb blsYor M63XKZ 4H{lUKMs

DB2

DB2无内置的生成随机数的函数，但DB2的SYSFUN包提供了rand函数用来生成随机数
注意：在使用之前要确保已经被正确安装了

select SYSFUN.rand() from SYSIBM.SYSDUMMY1

执行结果：

1
0.1216772972808008

舍入到最大整数

MYSQL/MSSQLServer/DB2：CEILING()函数，
Oracle：CEIL()函数
该函数用来舍掉一个数的小数点后的部分，并且向上舍入到邻近的最大的整数。
如：

3.33将被舍入为4
2.89 将被舍入为3
-3.61将被舍入为-3

这个函数有一个参数，参数为待舍入的数值，如：
MYSQL、MSSQLServer、DB2：

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

Oracle：

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

舍入到最小整数：FLOOR()函数

FLOOR()函数用来舍掉一个数的小数点后的部分，并且向下舍入到邻近的最小的整数。
如

3.33将被舍入为3
2.89将被舍入为2
-3.61将被舍入为-4

这个函数有一个参数，参数为待舍入的数值，如：

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

四舍五入：ROUND()函数(将数值向最近的数值舍入)

ROUND()函数有两个参数和单一参数两种用法两个参数
两个参数的ROUND()函数用法为：ROUND(m,d)，其中m为待进行四舍五入的数值，而d则为计算精度，也就是进行四舍五入时保留的小数位数。
如

3.663 进行精度为2 的四舍五入得到3.66
-2.337 进行精度为2 的四舍五入得到-2.34
3.32122进行精度为3的四舍五入得到3.321

d为0 的时候则表示不保留小数位进行四舍五入
如

3.663进行精度为0的四舍五入得到4
-2.337进行精度为0的四舍五入得到-2
3.32122进行精度为0的四舍五入得到3

d还可以取负值，表示在整数部分进行四舍五入
如：

36.63进行精度为-1 的四舍五入得到40
233.7 进行精度为-2 的四舍五入得到200
3321.22 进行精度为-2的四舍五入得到3300。

执行下面的SQL语句：

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

单一参数
单一参数的ROUND()函数用法为：ROUND(m)，其中m为待进行四舍五入的数值，它可以看做精度为0 的四舍五入运算，也就是ROUND(m,0)。

执行下面的SQL语句：

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

注意：单一函数的用法在MSSQLServer 上以及DB2 上不被支持，必须显示的指明精度为0。