数值函数

mysql> select abs(-1);     
+---------+
| abs(-1) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select sqrt(2);
+--------------------+
| sqrt(2)            |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set (0.18 sec)


mysql> select pow(2,3);
+----------+
| pow(2,3) |
+----------+
|        8 |
+----------+
1 row in set (0.18 sec)

mysql> select pow(2,2);
+----------+
| pow(2,2) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select 5/2;
+--------+
| 5/2    |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)

mysql> select 5%2;
+------+
| 5%2  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


mysql> select rand(1);
+---------------------+
| rand(1)             |
+---------------------+
| 0.40540353712197724 |
+---------------------+
1 row in set (0.00 sec)

mysql> select rand(2);
+--------------------+
| rand(2)            |
+--------------------+
| 0.6555866465490187 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand(4);
+---------------------+
| rand(4)             |
+---------------------+
| 0.15595286540310166 |
+---------------------+
1 row in set (0.00 sec)




mysql> select greatest(1,2,4,7);  
+-------------------+
| greatest(1,2,4,7) |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

mysql> select least(1,2,4,7);
+----------------+
| least(1,2,4,7) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)




mysql> select ceiling(123.44);
+-----------------+
| ceiling(123.44) |
+-----------------+
|             124 |
+-----------------+
1 row in set (0.00 sec)

mysql> select floor(1234.55);   
+----------------+
| floor(1234.55) |
+----------------+
|           1234 |
+----------------+
1 row in set (0.00 sec)



mysql> select round(12321.45);
+-----------------+
| round(12321.45) |
+-----------------+
|           12321 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(12321.55);
+-----------------+
| round(12321.55) |
+-----------------+
|           12322 |
+-----------------+
1 row in set (0.00 sec)





mysql> select format(13423.33,1);
+--------------------+
| format(13423.33,1) |
+--------------------+
| 13,423.3           |
+--------------------+
1 row in set (0.00 sec)

mysql> select format(13423.33,2);
+--------------------+
| format(13423.33,2) |
+--------------------+
| 13,423.33          |
+--------------------+
1 row in set (0.00 sec)

mysql> select format(13423.33,3);
+--------------------+
| format(13423.33,3) |
+--------------------+
| 13,423.330         |
+--------------------+
1 row in set (0.00 sec)

mysql> select format(13423.33,4);
+--------------------+
| format(13423.33,4) |
+--------------------+
| 13,423.3300        |
+--------------------+
1 row in set (0.00 sec)







mysql> select conv(100,10,2);
+----------------+
| conv(100,10,2) |
+----------------+
| 1100100        |
+----------------+
1 row in set (0.00 sec)

mysql> select conv(7,10,2);   
+--------------+
| conv(7,10,2) |
+--------------+
| 111          |
+--------------+
1 row in set (0.00 sec)

mysql> select conv(0,10,2); 
+--------------+
| conv(0,10,2) |
+--------------+
| 0            |
+--------------+
1 row in set (0.00 sec)

mysql> select conv(2,10,2); 
+--------------+
| conv(2,10,2) |
+--------------+
| 10           |
+--------------+
1 row in set (0.00 sec)

mysql> select conv(3,10,2); 
+--------------+
| conv(3,10,2) |
+--------------+
| 11           |
+--------------+
1 row in set (0.00 sec)

mysql> select conv(4,10,2); 
+--------------+
| conv(4,10,2) |
+--------------+
| 100          |
+--------------+
1 row in set (0.00 sec)

mysql> select conv(10,10,16); 
+----------------+
| conv(10,10,16) |
+----------------+
| A              |
+----------------+
1 row in set (0.00 sec)

mysql> select conv(11,10,16); 
+----------------+
| conv(11,10,16) |
+----------------+
| B              |
+----------------+
1 row in set (0.00 sec)

mysql> select conv(12,10,16); 
+----------------+
| conv(12,10,16) |
+----------------+
| C              |
+----------------+
1 row in set (0.00 sec)

 

floor 与 ceiling

floor:返回比小数值小的最大整数

mysql> select floor(-1234.55);   
+-----------------+
| floor(-1234.55) |
+-----------------+
|           -1235 |
+-----------------+
1 row in set (0.00 sec)


mysql> select floor(1234.55);    
+----------------+
| floor(1234.55) |
+----------------+
|           1234 |
+----------------+
1 row in set (0.00 sec)

mysql> select ceiling(1234.55);       
+------------------+
| ceiling(1234.55) |
+------------------+
|             1235 |
+------------------+
1 row in set (0.00 sec)

mysql> select ceiling(-1234.55); 
+-------------------+
| ceiling(-1234.55) |
+-------------------+
|             -1234 |
+-------------------+
1 row in set (0.00 sec)