This section gives you the most commonly used MySQL functions including aggregate functions, string functions, date time functions, control flow functions, etc.
MySQL aggregate functions
- MySQL aggregate function – provides a brief overview of the most commonly used MySQL aggregate functions
- AVG – calculates the average value of a set of values or an expression.
- COUNT – counts the number rows in a table.
- SUM – calculates the sum of a set of values or an expression.
- MIN – finds the minimum value in a set of values
- MAX – finds the maximum value in a set of values
- GROUP_CONCAT – concatenates strings from a group into a string with various options such as
- MySQL standard deviation functions – shows you how to compute population standard deviation and sample standard deviation by using functions:
STDDEV_SAMP. In addition, the population variance and sample variance functions such as
VAR_SAMPare also covered.
MySQL string functions
- CONCAT – combines two or more strings into one string.
- LENGTH and CHAR_LENGTH – gets the length of strings in bytes and in characters.
- REPLACE – searches and replaces a substring in a string.
- SUBSTRING – extracts a substring starting from a position with a specific length.
- LEFT – gets the left part of a string with a specified length.
- FIND_IN_SET – finds a string within a comma-separated list of strings.
- TRIM – removes unwanted characters from a string.
- INSTR – returns the position of the first occurrence of a substring in a string.
- FORMAT – formats a number with a specific locale, rounded to the number of decimals.
MySQL control flow functions
- IF – returns a value based on a given condition
- IFNULL – returns the first argument if it is not
NULL, otherwise returns the second argument.
- NULLIF – returns
NULLif the first argument is equal to the second argument, otherwise it returns the first argument.
- CASE – returns the corresponding result in
THENpart if the condition in the
WHENpart is satisfied, otherwise return the result in the
MySQL date and time functions
- DATEDIFF – calculates the number of days between two
- DATE_FORMAT – formats a date value based on a specified date format.
- STR_TO_DATE – converts a string into a date and time value based on a specified format.
- NOW – returns the current date and time at which the statement executed.
MySQL comparison functions
- COALESCE – returns the first non-NULL arguments. It is very handy for substituting NULL values.
- GREATEST and LEAST – takes N arguments and returns the greatest and least values in N argument respectively.
- ISNULL – returns 1 if the argument is NULL, otherwise return 0.