MySQL函数大全(4)

TAN(N)

返回N的正切值
mysql> select TAN(PI()+1);
-> 1.557408

ACOS(N)

返回N反余弦(N是余弦值,在-1到1的范围,否则返回NULL)
mysql> select ACOS(1);
-> 0.000000
mysql> select ACOS(1.0001);
-> NULL
mysql> select ACOS(0);
-> 1.570796

ASIN(N)

返回N反正弦值
mysql> select ASIN(0.2);
-> 0.201358
mysql> select ASIN('foo');
-> 0.000000

ATAN(N)

返回N的反正切值
mysql> select ATAN(2);
-> 1.107149
mysql> select ATAN(-2);
-> -1.107149

ATAN2(X,Y)

返回2个变量X和Y的反正切(类似Y/X的反正切,符号决定象限)
mysql> select ATAN(-2,2);
-> -0.785398
mysql> select ATAN(PI(),0);
-> 1.570796

COT(N)

返回N的余切
mysql> select COT(12);
-> -1.57267341
mysql> select COT(0);
-> NULL

RAND()

RAND(N)

返回在范围0到1.0内的随机浮点值(可以使用数字N作为初始值)
mysql> select RAND();
-> 0.5925
mysql> select RAND(20);
-> 0.1811
mysql> select RAND(20);
-> 0.1811
mysql> select RAND();
-> 0.2079
mysql> select RAND();
-> 0.7888

DEGREES(N)

把N从弧度变换为角度并返回
mysql> select DEGREES(PI());
-> 180.000000

RADIANS(N)

把N从角度变换为弧度并返回
mysql> select RADIANS(90);
-> 1.570796

TRUNCATE(N,D)

保留数字N的D位小数并返回
mysql> select TRUNCATE(1.223,1);
-> 1.2
mysql> select TRUNCATE(1.999,1);
-> 1.9
mysql> select TRUNCATE(1.999,0);
-> 1

LEAST(X,Y,...)

返回最小值(如果返回值被用在整数(实数或大小敏感字串)上下文或所有参数都是整数(实数或大小敏感字串)则他们作为整数(实数或大小敏感字串)比较,否则按忽略大小写的字符串被比较)
mysql> select LEAST(2,0);
-> 0
mysql> select LEAST(34.0,3.0,5.0,767.0);
-> 3.0
mysql> select LEAST("B","A","C");
-> "A"

GREATEST(X,Y,...)

返回最大值(其余同LEAST())
mysql> select GREATEST(2,0);
-> 2
mysql> select GREATEST(34.0,3.0,5.0,767.0);
-> 767.0
mysql> select GREATEST("B","A","C");
-> "C"

时期时间函数

DAYOFWEEK(date)

返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)
mysql> select DAYOFWEEK('1998-02-03');
-> 3

WEEKDAY(date)

返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。
mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5
mysql> select WEEKDAY('1997-11-05');
-> 2

DAYOFMONTH(date)

返回date是一月中的第几日(在1到31范围内)
mysql> select DAYOFMONTH('1998-02-03');
-> 3

DAYOFYEAR(date)

返回date是一年中的第几日(在1到366范围内)
mysql> select DAYOFYEAR('1998-02-03');
-> 34

MONTH(date)

返回date中的月份数值
mysql> select MONTH('1998-02-03');
-> 2

DAYNAME(date)

返回date是星期几(按英文名返回)
mysql> select DAYNAME("1998-02-05");
-> 'Thursday'

MONTHNAME(date)

返回date是几月(按英文名返回)
mysql> select MONTHNAME("1998-02-05");
-> 'February'

QUARTER(date)

返回date是一年的第几个季度
mysql> select QUARTER('98-04-01');