MySQL - Numeric Functions and Operators


MySQL - Arithmetic Operators

Arithmetic operators are used in mathematical expressions in the same way that they are used in algebra. The following table lists the MySQL arithmetic operators.

Sr.No. Name & Description
1 %, MOD (Modulus)

This operator divides left-hand operand by right-hand operand and, returns the remainder.

2 * (Multiplication)

This operator multiplies values on either side of the operator.

3 + (Addition)

This operator adds values on either side of the operator.

4 - (Subtraction)

This operator subtracts right-hand operand from left-hand operand.

5 - (Unary minus)

This operator is used to change the sign of the operand.

6 / (Division)

This operator divides left-hand operand by right-hand operand.

7 DIV (Integer division)

This operator performs the division operation and discards all the digits after the decimal.

MySQL - Numeric Functions

MySQL numeric functions are used primarily for numeric manipulation and/or mathematical calculations. The following table details the numeric functions that are available in the MySQL.

Sr.No. Name & Description
1 ABS()

Returns the absolute value of numeric expression.

2 ACOS()

Returns the arccosine of numeric expression. Returns NULL if the value is not in the range -1 to 1.

3 ASIN()

Returns the arcsine of numeric expression. Returns NULL if value is not in the range -1 to 1

4 ATAN()

Returns the arctangent of numeric expression.

5 ATAN2()

Returns the arctangent of the two variables passed to it.

6 CEIL()

Returns the smallest integer value that is not less than passed numeric expression

7 CEILING()

Returns the smallest integer value that is not less than passed numeric expression

8 CONV()

Converts numeric expression from one base to another.

9 COS()

Returns the cosine of passed numeric expression. The numeric expression should be expressed in radians.

10 COT()

Returns the cotangent of passed numeric expression.

11 CRC32()

Returns the Cyclic Redundancy Check (CRC) for the given value.

12 DEGREES()

Returns numeric expression converted from radians to degrees.

13 EXP()

Returns the base of the natural logarithm (e) raised to the power of passed numeric expression.

14 FLOOR()

Returns the largest integer value that is not greater than passed numeric expression.

15 FORMAT()

Returns a numeric expression rounded to a number of decimal places.

16 HEX()

returns the hexadecimal representation of the given value.

17 LN()

Returns the natural logarithm of the passed numeric expression.

18 LOG()

Returns the natural logarithm of the passed numeric expression.

19 LOG10()

Returns the base-10 logarithm of the passed numeric expression.

20 LOG2()

Returns the base-2 logarithm of the passed numeric expression.

21 MOD()

Returns the remainder of one expression by diving by another expression.

22 PI()

Returns the value of pi

23 POW()

Returns the value of one expression raised to the power of another expression

24 POWER()

Returns the value of one expression raised to the power of another expression

25 RADIANS()

Returns the value of passed expression converted from degrees to radians.

26 RAND()

Returns a random floating-point value with in the range 0 to 1.0.

27 ROUND()

Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points

28 SIGN()

Returns the sign of the given number.

29 SIN()

Returns the sine of numeric expression given in radians.

30 SQRT()

Returns the non-negative square root of numeric expression.

31 TAN()

Returns the tangent of numeric expression expressed in radians.

32 TRUNCATE()

Returns numeric exp1 truncated to exp2 decimal places. If exp2 is 0, then the result will have no decimal point.

mysql-useful-functions.htm