Mathematical Functions in MySQL

Name

Description
ABS() Return the absolute value
ACOS() Return the arc cosine
ASIN() Return the arc sine
ATAN2(), ATAN() Return the arc tangent of the two arguments
ATAN() Return the arc tangent
CEIL() Return the smallest integer value not less than the argument
CEILING() Return the smallest integer value not less than the argument
CONV() Convert numbers between different number bases
COS() Return the cosine
COT() Return the cotangent
CRC32()(v4.1.0) Compute a cyclic redundancy check value
DEGREES() Convert radians to degrees
EXP() Raise to the power of
FLOOR() Return the largest integer value not greater than the argument
LN() Return the natural logarithm of the argument
LOG10() Return the base-10 logarithm of the argument
LOG2() Return the base-2 logarithm of the argument
LOG() Return the natural logarithm of the first argument
MOD() Return the remainder
OCT() Return an octal representation of a decimal number
PI() Return the value of pi
POW() Return the argument raised to the specified power
POWER() Return the argument raised to the specified power
RADIANS() Return argument converted to radians
RAND() Return a random floating-point value
ROUND() Round the argument
SIGN() Return the sign of the argument
SIN() Return the sine of the argument
SQRT() Return the square root of the argument
TAN() Return the tangent of the argument
TRUNCATE() Truncate to specified number of decimal places

Below are just some functions above that I’ve tested with my database.

ABS() function

SELECT abs(-2.55);

The result is 2.55

CEIL() OR CEILING() function

You can use either function, it still returns the same result.

SELECT CEIL(1.56);

The result is 2

Another example that you can add value to each record during the query. For example, your table “Cost” has 2 records in the field “money” which are 7.80 and 15.25 respectively. Now you want to add 100 to each record and get the ceiling values rather than the exact values. You can use the query below:

select ceil(money + 100) from cost;

The results are 108, 116 accordingly.

DEGREES() function

SELECT DEGREES(PI());

FORMAT(X,D) funtion

SELECT format(700000, 2);

The result is 700,000.00

PI() function

Select PI();

The result is 3.141593

POW() or POWER() function

Select Pow(2,2);

The result is 4

TRUNCATE() function

SELECT TRUNCATE(10.778, 2);

The result is 10.77