Master MySQL System Functions: Complete Reference with Examples

Overview

MySQL offers a rich set of built‑in functions that simplify data manipulation, calculation, and formatting, greatly improving database management efficiency.

Numeric Functions

Common numeric functions include:

ABS(x) – returns the absolute value of x .

SQRT(x) – returns the square root of x (NULL for negative numbers).

MOD(a,b) – returns the remainder of a divided by b .

CEIL(x) / CEILING(x) – rounds x up to the nearest integer.

FLOOR(x) – rounds x down to the nearest integer (returns BIGINT).

RAND([seed]) – generates a random number between 0 and 1; providing a seed repeats the sequence.

ROUND(x[,d]) – rounds x to d decimal places (negative d rounds to tens, hundreds, etc.).

SIGN(x) – returns -1, 0, or 1 depending on the sign of x .

POW(x,y) / POWER(x,y) – raises x to the power y (negative exponent returns the reciprocal).

TRIGONOMETRIC FUNCTIONS – SIN(), COS(), TAN() and their inverse functions ( ASIN(), ACOS(), ATAN()).

SELECT ABS(-7), ABS(8), ABS(0);

SELECT SQRT(-7), SQRT(9), SQRT(39);

SELECT MOD(100,7), MOD(100,10), MOD(9,4.5), MOD(18.3,9);

SELECT CEILING(-7.9), CEIL(7.5);

SELECT FLOOR(-7.9), FLOOR(7.5);

SELECT RAND(), RAND(), RAND(2), RAND();

SELECT ROUND(78.2), ROUND(78.5), ROUND(-78.78,1), ROUND(78.7819,3), ROUND(78.78,-1), ROUND(78.78,-2);

SELECT SIGN(-78), SIGN(0), SIGN(78);

SELECT POW(10,0), POW(10,1), POW(10,2), POW(10,3), POWER(10,-2);

SELECT SIN(1), COS(1), SIN(2), SIN(1)*COS(1)+COS(1)*SIN(1), PI();