1.CEIL()
用法：向上取整
举例：
mysql> select CEIL(3.5); +-----------+ | CEIL(3.5) | +-----------+ | 4 | +-----------+ 1 row in set (0.05 sec) mysql> select CEIL(3.1); +-----------+ | CEIL(3.1) | +-----------+ | 4 | +-----------+ 1 row in set (0.00 sec) mysql> select CEIL(3.0); +-----------+ | CEIL(3.0) | +-----------+ | 3 | +-----------+ 1 row in set (0.00 sec)
2.DIV（DIV(X,Y)）
用法：数字除法，除数不能为0
举例：
mysql> select 3 DIV 4; +---------+ | 3 DIV 4 | +---------+ | 0 | +---------+ 1 row in set (0.03 sec) mysql> select 3 DIV 4.0; +-----------+ | 3 DIV 4.0 | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql> select 3.3 DIV 4; +-----------+ | 3.3 DIV 4 | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql> select 4.3 DIV 4; +-----------+ | 4.3 DIV 4 | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
3.FLOOR()
用法：向下取整（可以使正数可以使负数）
举例：
mysql> select FLOOR(2.3); +------------+ | FLOOR(2.3) | +------------+ | 2 | +------------+ 1 row in set (0.00 sec) mysql> select FLOOR(2.0); +------------+ | FLOOR(2.0) | +------------+ | 2 | +------------+ 1 row in set (0.00 sec) mysql> select FLOOR(-2.3); +-------------+ | FLOOR(-2.3) | +-------------+ | -3 | +-------------+ 1 row in set (0.00 sec)
4.MOD(MOD(X,Y))
用法：取余数（可以是正数可以是余数）
举例：
mysql> select 3 MOD 4; +---------+ | 3 MOD 4 | +---------+ | 3 | +---------+ 1 row in set (0.00 sec) mysql> select -3 MOD 4; +----------+ | -3 MOD 4 | +----------+ | -3 | +----------+ 1 row in set (0.00 sec) mysql> select -5 MOD 4; +----------+ | -5 MOD 4 | +----------+ | -1 | +----------+ 1 row in set (0.00 sec) mysql> select 5 MOD 4; +---------+ | 5 MOD 4 | +---------+ | 1 | +---------+ 1 row in set (0.00 sec)
5.POEWE()
用法：幂运算
举例：
mysql> select POWER(2,3); +------------+ | POWER(2,3) | +------------+ | 8 | +------------+ 1 row in set (0.03 sec) mysql> select POWER(2,-3); +-------------+ | POWER(2,-3) | +-------------+ | 0.125 | +-------------+ 1 row in set (0.00 sec) mysql> select POWER(2,2.2); +------------------+ | POWER(2,2.2) | +------------------+ | 4.59479341998814 | +------------------+ 1 row in set (0.03 sec)
6.ROUND()
用法：四舍五入
举例：
mysql> select ROUND(3.4); +------------+ | ROUND(3.4) | +------------+ | 3 | +------------+ 1 row in set (0.01 sec) mysql> select ROUND(3.5); +------------+ | ROUND(3.5) | +------------+ | 4 | +------------+ 1 row in set (0.00 sec) mysql> select ROUND(-3.5); +-------------+ | ROUND(-3.5) | +-------------+ | -4 | +-------------+ 1 row in set (0.00 sec)
7.TRUNCATE()
用法：数字截取（和正负数没有关系，只是截取位数）
举例：
mysql> select TRUNCATE(2.343434,3); +----------------------+ | TRUNCATE(2.343434,3) | +----------------------+ | 2.343 | +----------------------+ 1 row in set (0.00 sec) mysql> select TRUNCATE(2.343934,3); +----------------------+ | TRUNCATE(2.343934,3) | +----------------------+ | 2.343 | +----------------------+ 1 row in set (0.00 sec) mysql> select TRUNCATE(-2.343934,3); +-----------------------+ | TRUNCATE(-2.343934,3) | +-----------------------+ | -2.343 | +-----------------------+ 1 row in set (0.00 sec)
8.ABS()
用法：返回绝对值
举例：
mysql> select ABS(2); +--------+ | ABS(2) | +--------+ | 2 | +--------+ 1 row in set (0.03 sec) mysql> select ABS(-2); +---------+ | ABS(-2) | +---------+ | 2 | +---------+ 1 row in set (0.00 sec)
9.SIGN()
用法：返回符号（-1,0，1）
举例：
mysql> select SIGN(2) -> ; +---------+ | SIGN(2) | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> select SIGN(-2) -> ; +----------+ | SIGN(-2) | +----------+ | -1 | +----------+ 1 row in set (0.00 sec) mysql> select SIGN(0); +---------+ | SIGN(0) | +---------+ | 0 | +---------+ 1 row in set (0.00 sec)
10.SQRT(N)
用法：求N的平方根
举例：
mysql> select SQRT(4); +---------+ | SQRT(4) | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) mysql> select SQRT(3); +--------------------+ | SQRT(3) | +--------------------+ | 1.7320508075688772 | +--------------------+ 1 row in set (0.00 sec)
11.SIN(),TAN(),COS(),ACOS(),ASIN(),ATAN(),COT(),ASCOT()
用法：数学的三角函数
举例：
mysql> select SIN(30); +---------------------+ | SIN(30) | +---------------------+ | -0.9880316240928618 | +---------------------+ 1 row in set (0.00 sec) mysql> select SIN(PI()); +------------------------+ | SIN(PI()) | +------------------------+ | 1.2246467991473532e-16 | +------------------------+ 1 row in set (0.00 sec)
12.DEGREES(N),RADIANS(N)
用法：把N从弧度变换为角度并返回（把N从角度变换为弧度并返回）
举例：
mysql> select DEGREES(PI()); +---------------+ | DEGREES(PI()) | +---------------+ | 180 | +---------------+ 1 row in set (0.00 sec) mysql> select RADIANS(180); +-------------------+ | RADIANS(180) | +-------------------+ | 3.141592653589793 | +-------------------+ 1 row in set (0.00 sec)
13.LEAST(),GREATEST()
用法：返回最大值最小值
举例：
mysql> select LEAST(3,1,4,6,-1); +-------------------+ | LEAST(3,1,4,6,-1) | +-------------------+ | -1 | +-------------------+ 1 row in set (0.00 sec) mysql> select LEAST("B","A","C"); +--------------------+ | LEAST("B","A","C") | +--------------------+ | A | +--------------------+ 1 row in set (0.00 sec) mysql> select GREATEST("B","A","C"); +-----------------------+ | GREATEST("B","A","C") | +-----------------------+ | C | +-----------------------+ 1 row in set (0.00 sec) mysql> select GREATEST(2,4,5); +-----------------+ | GREATEST(2,4,5) | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec)
原文：http://www.cnblogs.com/zhangminghui/p/4077341.html