绝对值函数abs(x)和圆周率函数pi()
mysql> select abs(0.5), abs(-0.5), pi();
+----------+-----------+----------+
| abs(0.5) | abs(-0.5) | pi() |
+----------+-----------+----------+
| 0.5 | 0.5 | 3.141593 |
+----------+-----------+----------+
row in set (0.00 sec)
平方根函数sqrt(x)和求余函数mod(x,y)
mysql> select sqrt(16), sqrt(3), mod(13,4);
+----------+--------------------+-----------+
| sqrt(16) | sqrt(3) | mod(13,4) |
+----------+--------------------+-----------+
| 4 | 1.7320508075688772 | 1 |
+----------+--------------------+-----------+
row in set (0.00 sec)
取整函数ceil(x)、ceiling(x)和floor(x)
mysql> select ceil(2.3), ceil(-2.3), ceiling(2.3), ceiling(-2.3);
+-----------+------------+--------------+---------------+
| ceil(2.3) | ceil(-2.3) | ceiling(2.3) | ceiling(-2.3) |
+-----------+------------+--------------+---------------+
| 3 | -2 | 3 | -2 |
+-----------+------------+--------------+---------------+
row in set (0.00 sec)
mysql> select floor(2.3), floor(-2.3);
+------------+-------------+
| floor(2.3) | floor(-2.3) |
+------------+-------------+
| 2 | -3 |
+------------+-------------+
row in set (0.00 sec)
ceil(x)和ceiling(x)返回大于或等于x的最小整数
floor(x)返回小于或等于x的最大整数
随机数函数rand()和rand(x)
mysql> select rand(), rand(2), rand(2);
+--------------------+--------------------+--------------------+
| rand() | rand(2) | rand(2) |
+--------------------+--------------------+--------------------+
| 0.8269294489425881 | 0.6555866465490187 | 0.6555866465490187 |
+--------------------+--------------------+--------------------+
row in set (0.00 sec)
rand()和rand(x)这两个函数丢失返回0～1的随机数
区别在于，rand()返回的数是完全随机的，而rand(x)在x相同时返回的值相同
四舍五入函数round(x)、round(x,y)和truncate(x,y)
mysql> select round(2.3), round(2.5), round(2.53,1), round(2.55,1);
+------------+------------+---------------+---------------+
| round(2.3) | round(2.5) | round(2.53,1) | round(2.55,1) |
+------------+------------+---------------+---------------+
| 2 | 3 | 2.5 | 2.6 |
+------------+------------+---------------+---------------+
row in set (0.00 sec)
round(x)返回离x最近的整数，也就是对x进行四舍五入处理
round(x,y)返回x保留到小数点后y位的值，在截取时进行四舍五入处理
mysql> select truncate(2.53,1), truncate(2.55,1);
+------------------+------------------+
| truncate(2.53,1) | truncate(2.55,1) |
+------------------+------------------+
| 2.5 | 2.5 |
+------------------+------------------+
row in set (0.00 sec)
truncate(x,y)返回x保留到小数点后y位的值，不进行四舍五入操作
符号函数sign(x)
mysql> select sign(-2), sign(0), sign(2);
+----------+---------+---------+
| sign(-2) | sign(0) | sign(2) |
+----------+---------+---------+
| -1 | 0 | 1 |
+----------+---------+---------+
row in set (0.00 sec)
sign(x)返回x的符号，-1为负数，0不变，1为整数
幂运算函数pow(x,y)、power(x,y)
mysql> select pow(3,2), power(3,2);
+----------+------------+
| pow(3,2) | power(3,2) |
+----------+------------+
| 9 | 9 |
+----------+------------+
row in set (0.00 sec)
bitscn.com