MySQL 内置数学函数

学习学习mysql内置数学函数，先进入mysql：

C:\Users\Administrator>cd /d D:\MySQL Server 5.5\bin

D:\MySQL Server 5.5\bin>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

如直接输入以下命令，却出现：

说明没有设置系统的环境变量。

我们需要把Mysql bin文件夹的地址添加到windows的“环境变量”/“系统变量”/path 中

如出现 ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)的错误，应该是mysql服务没有开启。以管理员身份运行cmd，开启即可。还不懂就看： http://blog.csdn.net/thearcticocean/article/details/49983331

数学函数：

abs(x) 返回数字的绝对值

floor(x) 返回不大于x的最大整数

rand() 返回0至1的随机数

rand(x) 也返回随机数，不过x作为种子

PI() 返回  值

mysql> select abs(-2),floor(1.5),rand(),rand(),PI();
+---------+------------+--------------------+--------------------+----------+
| abs(-2) | floor(1.5) | rand()             | rand()             | PI()     |
+---------+------------+--------------------+--------------------+----------+
|       2 |          1 | 0.8543179415672253 | 0.6597909654116174 | 3.141593 |
+---------+------------+--------------------+--------------------+----------+

TRUNCAT用于单纯的截取小数。

mysql> select TRUNCATE(5.242354,4);
+----------------------+
| TRUNCATE(5.242354,4) |
+----------------------+
|               5.2423 |
+----------------------+

round()四舍五入取最近整数。

round(x,y)保留x小数点后y位的值

mysql> select round(5.242354,4);
+-------------------+
| round(5.242354,4) |
+-------------------+
|            5.2424 |
+-------------------+

mysql> select round(1.4),round(1.5),round(1.6);
+------------+------------+------------+
| round(1.4) | round(1.5) | round(1.6) |
+------------+------------+------------+
|          1 |          2 |          2 |
+------------+------------+------------+

sign(x) 判断x的正负性。
x>0: 1 
x<0: -1
x=0: 0

mysql> select sign(4),sign(-4),sign(0);
+---------+----------+---------+
| sign(4) | sign(-4) | sign(0) |
+---------+----------+---------+
|       1 |       -1 |       0 |
+---------+----------+---------+

pow(x,y)  

sqrt(x)  

EXP(x) 返回e的x乘方后的值

mod(x,y)  返回 x mod y的值

log(x) 返回 的值

log10(x)

mysql>  select pow(2,6),sqrt(64),exp(2),mod(65,64),log(64),log10(100);
+----------+----------+------------------+------------+--------------------+------------+
| pow(2,6) | sqrt(64) | exp(2)           | mod(65,64) | log(64)            | log10(100) |
+----------+----------+------------------+------------+--------------------+------------+
|       64 |        8 | 7.38905609893065 |          1 | 4.1588830833596715 |          2 |
+----------+----------+------------------+------------+--------------------+------------+

和角度相关的函数：

radians(x) 角度转弧度

degrees(x) 弧度转角度

mysql> select radians(180),degrees(PI());
+-------------------+---------------+
| radians(180)      | degrees(PI()) |
+-------------------+---------------+
| 3.141592653589793 |           180 |
+-------------------+---------------+

三角函数相关的函数：

mysql>  select sin(PI()/2),cos(PI()/2.0),acos(0),tan(PI()/4.0),atan(1);
+-------------+-----------------------+--------------------+--------------------+--------------------+
| sin(PI()/2) | cos(PI()/2.0)         | acos(0)            | tan(PI()/4.0)      | atan(1)            |
+-------------+-----------------------+--------------------+--------------------+--------------------+
|           1 | 6.123233995736766e-17 | 1.5707963267948966 | 0.9999999999999999 | 0.7853981633974483 |
+-------------+-----------------------+--------------------+--------------------+--------------------+

参数都是弧度。

小瑕疵，浮点误差T_T