MySQL Query Functions

MySQL Best Practices

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;

One thought on “MySQL Query Functions”

Leave a Reply

Your email address will not be published. Required fields are marked *