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