欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL笔记之数学函数详解

程序员文章站 2024-02-22 16:45:28
绝对值函数abs(x)和圆周率函数pi()复制代码 代码如下:mysql> select abs(0.5), abs(-0.5), pi();+----------+...

绝对值函数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)