In addition to the four basic arithmetic operations addition (+), Subtraction (-), Multiplication (*) and Division (/), MySQL also has the Modulo (%) operator. This calculates the remainder left after division.
select 87 % 9;
+——–+
| 87 % 9 |
+——–+
| 6 |
+——–+
1 row in set (0.00 sec)
MySQL – MOD(x, y)
Displays the remainder of x divided by y, SImilar to the Modulus operator.
select MOD(37, 13);
+————-+
| MOD(37, 13) |
+————-+
| 11 |
+————-+
1 row in set (0.00 sec)
MySQL ABS(x)
Calculates the Absolute value of number x. Thus, if x is negative its positive value is returned.
select ABS(-4.05022);
+—————+
| ABS(-4.05022) |
+—————+
| 4.05022 |
+—————+
1 row in set (0.00 sec)
select ABS(4.05022);
+————–+
| ABS(4.05022) |
+————–+
| 4.05022 |
+————–+
1 row in set (0.00 sec)
SQL SIGN(x)
Returns 1, 0 or -1 when x is positive, zero or negative, respectively.
select SIGN(-34.22);
+————–+
| SIGN(-34.22) |
+————–+
| -1 |
+————–+
1 row in set (0.00 sec)
select SIGN(54.6);
+————+
| SIGN(54.6) |
+————+
| 1 |
+————+
1 row in set (0.00 sec)
select SIGN(0);
+———+
| SIGN(0) |
+———+
| 0 |
+———+
1 row in set (0.00 sec)
POWER(x,y)
Calculates the value of x raised to the power of y.
select POWER(4,3);
+————+
| POWER(4,3) |
+————+
| 64.000000 |
+————+
1 row in set (0.00 sec)
SQRT(x)
Calculates the square root of x.
select SQRT(3);
+———-+
| SQRT(3) |
+———-+
| 1.732051 |
+———-+
1 row in set (0.00 sec)
ROUND(x) and ROUND(x,y)
Returns the value of x rounded to the nearest integer. ROUND can also accept an additional argument y that will round x to y decimal places.
select ROUND(14.492);
+—————+
| ROUND(14.492) |
+—————+
| 14 |
+—————+
1 row in set (0.00 sec)
select ROUND(4.5002);
+—————+
| ROUND(4.5002) |
+—————+
| 5 |
+—————+
1 row in set (0.00 sec)
select ROUND(-12.773);
+—————-+
| ROUND(-12.773) |
+—————-+
| -13 |
+—————-+
1 row in set (0.00 sec)
select ROUND(7.235651, 3);
+——————–+
| ROUND(7.235651, 3) |
+——————–+
| 7.236 |
+——————–+
1 row in set (0.00 sec)
FLOOR(x)
Returns the largest integer that is less than or equal to x.
select FLOOR(23.544);
+—————+
| FLOOR(23.544) |
+—————+
| 23 |
+—————+
1 row in set (0.00 sec)
select FLOOR(-18.4);
+————–+
| FLOOR(-18.4) |
+————–+
| -19 |
+————–+
1 row in set (0.00 sec)
CEILING(x)
Returns the smallest integer that is greater than or equal to x.
select CEILING(54.22);
+—————-+
| CEILING(54.22) |
+—————-+
| 55 |
+—————-+
1 row in set (0.00 sec)
select CEILING(-62.23);
+—————–+
| CEILING(-62.23) |
+—————–+
| -62 |
+—————–+
1 row in set (0.00 sec)
TAN(x), SIN(x) and COS(x)
Calculate the trignometic ratios for angle x (measured in radians).
select SIN(0);
+———-+
| SIN(0) |
+———-+
| 0.000000 |
+———-+
1 row in set (0.00 sec)