MySQL Query Functions

There are so many MySQL functions that commonly used to minimize the statements like string operation functions, date time functions, aggregate functions etc.

MySQL LAST_INSERT_ID

To get the last generated sequence number of the last inserted row.

SELECT LAST_INSERT_ID();

MySQL CAST()

To convert a value of any type into a value with a your own choice type.

SELECT CAST(`id` AS CHAR);

MySQL NOW()

Returns the current date and time.

SELECT NOW();

MySQL STR_TO_DATE()

Converts a string into a date and time.

SELECT STR_TO_DATE('15.6.2015','%d.%m.%Y');

MySQL DATEDIFF()

Calculates the number of days between two date.

SELECT DATEDIFF('2015-07-05','2015-07-12');

MySQL DATE_FORMAT()

Format to a specified date format.

SELECT DATE_FORMAT(post_date,'%Y-%m-%d') p_date FROM tbl_name;

MySQL IF()

Get the result based on conditions.

SELECT IF(surname IS NULL,'N/A',surname) surname FROM tbl_name;

MySQL IFNULL()

If NULL then return the first argument, otherwise second.

SELECT IFNULL(last_name,first_name) name FROM users;

MySQL NULLIF()

It returns NULL if first argument = second argument, otherwise return first argument.

SELECT NULLIF(1,2); returns 1

MySQL CASE()

It is a flow control that allows you to build conditions inside a query.

SELECT id, name, age FROM users
ORDER BY (
CASE
    WHEN last_name IS NULL THEN first_name
    ELSE first_name
END
);

MySQL CONCAT()

Concatenate two or more strings into one string.

SELECT CONCAT(first_name,', ',last_name) name FROM users;

MySQL REPLACE()

To replace a string in a column of a table by a another new string.

UPDATE users SET country = REPLACE(country,'IND','INDIA');

MySQL SUBSTR()

Extracts a substring starting from a particular position with a target length.

SELECT SUBSTRING(name,2,4) FROM users;

MySQL LENGTH()

To get the length of strings in characters and in bytes.

SELECT LENGTH('my string');

MySQL GROUP_CONCAT()

This concatenates strings from a group into a string.

SELECT GROUP_CONCAT(country SEPARATOR ';') FROM users;

MySQL MAX()

Determine the maximum value from a set of values.

SELECT MAX(salary) FROM employee;

MySQL MIN()

Determine the minimum value from a set of values.

SELECT MIN(salary) FROM employee;

MySQL COUNT()

Calculate the total number of rows in a table.

SELECT COUNT(*) FROM users;

MySQL SUM()

Calculates the sum of a group of values.

SELECT SUM(salary) FROM employee;

MySQL AVG()

Calculates the average value of a group of values.

SELECT AVG(salary) FROM employee;