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)