MySQL Built in Data Manipulation Functions

MySQL Functions are in-built utilities given by MySQL to aid develpers. They are divided into five main categories based on their function.

Aggregate Functions: All the functions related to Grouping of values.
Comparison Functions : They help compare every value in the resultset to a specified condition or value.
Control flow functions: All the functions related to if-then-else queries and logic in MySQL.
Date Functions : All the functions related to the Date Data type manipulation in MySQL.
String Functions: All the functions for String manipulation.
Window Functions: These are advanced functions which help better MySQL query optimizations.
Math Functions: These are all the functions required for mathematical calculations like trigonometry etc.

Given below is the list of all the functions, functionality-wise.

Aggregate Functions in MySQL

Function	Description
`AVG()`	Average value of non-NULL values.
`BIT_AND()`	Bitwise AND.
`BIT_OR()`	Bitwise OR.
`BIT_XOR()`	Bitwise XOR.
`COUNT()`	The number of rows in a group, including NULL values.
`GROUP_CONCAT()`	A concatenated string.
`JSON_ARRAYAGG()`	Result set as a single JSON array.
`JSON_OBJECTAGG()`	Result set as a single JSON object.
`MAX()`	The highest value or maximum in a set of non-NULL values.
`MIN()`	The lowest value or minimum in a set of non-NULL values.
`STDEV()`	The standard deviation.
`STDDEV_POP()`	The population standard deviation.
`STDDEV_SAMP()`	The sample standard deviation.
`SUM()`	Return the sum total of all non-NULL values a set.
`VAR_POP()`	Return the standard variance.
`VARP_SAM()`	Return the sample variance.
`VARIANCE()`	Return the population standard variance.

Comparison function in MySQL

Function	Description
`COALESCE`	It returns the first not Null arguments. Mainly used for Null substitution.
`Greatest , Least`	It takes n arguments and return the greatest and least values respectively.
`ISNULL`	Return 1 if the argument is `NULL`, otherwise, return zero

Control Flow Functions in MySQL

Function	Description
`CASE <value> WHEN <value1> THEN <result -value>`	Return the corresponding result in `THEN` branch if the condition in the `WHEN` branch is satisfied, otherwise, return the result in the `ELSE` branch.
`IF(<condition>,<true-condition>,<false-condition>)`	If checks the condition given. If tru then true-expression is executed else the false condition is executed.
`IFNULL`	It is similar to If but it checks the IF NULL condition specifically.
`NULLIF`	It checks the equality between two arguments and returns NULL if true and returns first argument if False.

String Functions in MySQL

Function	Description
`CONCAT`	Concatenate two or more strings into a single string
`INSTR`	Return the position of the first occurrence of a substring in a string
`LENGTH`	Get the length of a string in bytes and in characters
`LEFT`	Get a specified number of leftmost characters from a string
`LOWER`	Convert a string to lowercase
`LTRIM`	Remove all leading spaces from a string
`REPLACE`	Search and replace a substring in a string
`RIGHT`	Get a specified number of rightmost characters from a string
`RTRIM`	Remove all trailing spaces from a string
`SUBSTRING`	Extract a substring starting from a position with a specific length.
`SUBSTRING_INDEX`	Return a substring from a string before a specified number of occurrences of a delimiter
`TRIM`	Remove unwanted characters from a string.
`FIND_IN_SET`	Find a string within a comma-separated list of strings
`FORMAT`	Format a number with a specific locale, rounded to the number of decimals
`UPPER`	Convert a string to uppercase

Math Functions in MySQL

Function	Description
`ABS()`	Returns the absolute value of a number
`CEIL()`	Returns the smallest integer value greater than or equal to the input number (n).
`FLOOR()`	Returns the largest integer value not greater than the argument
`MOD()`	Returns the remainder of a number divided by another
`ROUND()`	Rounds a number to a specified number of decimal places.
`TRUNCATE()`	Truncates a number to a specified number of decimal places
`ACOS(n)`	Returns the arc cosine of n or null if n is not in the range -1 and 1.
`ASIN(n)`	Returns the arcsine of n which is the value whose sine is n. It returns null if n is not in the range -1 to 1.
`ATAN()`	Returns the arctangent of n.
`ATAN2(n,m), ATAN(m,n)`	Returns the arctangent of the two variables n and m
`CONV(n,from_base,to_base)`	Converts a number between different number bases
`COS(n)`	Returns the cosine of n, where n is in radians
`COT(n)`	Returns the cotangent of n.
`CRC32()`	Computes a cyclic redundancy check value and returns a 32-bit unsigned value
`DEGREES(n)`	Converts radians to degrees of the argument n
`EXP(n)`	Raises to the power of e raised to the power of n
`LN(n)`	Returns the natural logarithm of n
`LOG(n)`	Returns the natural logarithm of the first argument
`LOG10()`	Returns the base-10 logarithm of the argument
`LOG2()`	Returns the base-2 logarithm of the argument
`PI()`	Returns the value of PI
`POW()`	Returns the argument raised to the specified power
`POWER()`	Returns the argument raised to the specified power
`RADIANS()`	Returns argument converted to radians
`RAND()`	Returns a random floating-point value
`SIGN(n)`	Returns the sign of n that can be -1, 0, or 1 depending on whether n is negative, zero, or positive.
`SIN(n)`	Returns the sine of n
`SQRT(n)`	Returns the square root of n
`TAN(n)`	Returns the tangent of n

Window Functions in MySQL

Function	Description
`CUME_DIST`	Calculates the cumulative distribution of a value in a set of values.
`DENSE_RANK`	Assigns a rank to every row within its partition based on the `ORDER BY` clause. It assigns the same rank to the rows with equal values. If two or more rows have the same rank, then there will be no gaps in the sequence of ranked values.
`FIRST_VALUE`	Returns the value of the specified expression with respect to the first row in the window frame.
`LAG`	Returns the value of the Nth row before the current row in a partition. It returns NULL if no preceding row exists.
`LAST_VALUE`	Returns the value of the specified expression with respect to the last row in the window frame.
`LEAD`	Returns the value of the Nth row after the current row in a partition. It returns NULL if no subsequent row exists.
`NTH_VALUE`	Returns value of argument from Nth row of the window frame
`NTILE`	Distributes the rows for each window partition into a specified number of ranked groups.
`PERCENT_RANK`	Calculates the percentile rank of a row in a partition or result set
`RANK`	Similar to the `DENSE_RANK()` function except that there are gaps in the sequence of ranked values when two or more rows have the same rank.
`ROW_NUMBER`	Assigns a sequential integer to every row within its partition