Numerical functions are also frequently used. Remember that they can make a lot of work more efficient.

abs(x) returns the absolute value:

mysql> select abs(-2),abs(3),abs(null);
+---------+--------+-----------+
| abs(-2) | abs(3) | abs(null) |
+---------+--------+-----------+
|       2 |      3 |      NULL |
+---------+--------+-----------+
1 row in set (0.00 sec)

ceil(x) returns the minimum integer > = x:

mysql> select ceil(-0.3),ceil(2),ceil(null);
+------------+---------+------------+
| ceil(-0.3) | ceil(2) | ceil(null) |
+------------+---------+------------+
|          0 |       2 |       NULL |
+------------+---------+------------+
1 row in set (0.01 sec)

floor(x) returns the maximum integer of < = x:

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

mod(x,y) returns the module of x/y. note that the result of mod is the same as that of x%y

mysql> select mod(10,3),10%3;
+-----------+------+
| mod(10,3) | 10%3 |
+-----------+------+
|         1 |    1 |
+-----------+------+
1 row in set (0.00 sec)

rand() returns any number from 0 to 1 regardless of whether the parameter is filled or not:

mysql> select rand(),rand(100),rand(-3);
+--------------------+---------------------+---------------------+
| rand()             | rand(100)           | rand(-3)            |
+--------------------+---------------------+---------------------+
| 0.6118045995121865 | 0.17353134804734155 | 0.40467110313910165 |
+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Using the combination of several functions, you can generate a random number within a specified return, such as an integer from 0 to 100:

mysql> select ceil(100*rand());
+------------------+
| ceil(100*rand()) |
+------------------+
|               62 |
+------------------+
1 row in set (0.00 sec)

round(x,y) returns the result of keeping y digits after X decimal point:

select round(1.23456,3);
+------------------+
| round(1.23456,3) |
+------------------+
|            1.235 |
+------------------+
1 row in set (0.00 sec)

truncate(x,y) returns the result that the number x decimal point truncates the Y digit. The difference is that it is not rounded:

mysql> select truncate(1.23456,3);
+---------------------+
| truncate(1.23456,3) |
+---------------------+
|               1.234 |
+---------------------+
1 row in set (0.00 sec)