There are so many MySQL functions that commonly used to minimize the statements like string operation functions, date time functions, aggregate functions etc.
To get the last generated sequence number of the last inserted row.
SELECT LAST_INSERT_ID();
To convert a value of any type into a value with a your own choice type.
SELECT CAST(`id` AS CHAR);
Returns the current date and time.
SELECT NOW();
Converts a string into a date and time.
SELECT STR_TO_DATE('15.6.2015','%d.%m.%Y');
Calculates the number of days between two date.
SELECT DATEDIFF('2015-07-05','2015-07-12');
Format to a specified date format.
SELECT DATE_FORMAT(post_date,'%Y-%m-%d') p_date FROM tbl_name;
Get the result based on conditions.
SELECT IF(surname IS NULL,'N/A',surname) surname FROM tbl_name;
If NULL then return the first argument, otherwise second.
SELECT IFNULL(last_name,first_name) name FROM users;
It returns NULL if first argument = second argument, otherwise return first argument.
SELECT NULLIF(1,2); returns 1
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 );
Concatenate two or more strings into one string.
SELECT CONCAT(first_name,', ',last_name) name FROM users;
To replace a string in a column of a table by a another new string.
UPDATE users SET country = REPLACE(country,'IND','INDIA');
Extracts a substring starting from a particular position with a target length.
SELECT SUBSTRING(name,2,4) FROM users;
To get the length of strings in characters and in bytes.
SELECT LENGTH('my string');
This concatenates strings from a group into a string.
SELECT GROUP_CONCAT(country SEPARATOR ';') FROM users;
Determine the maximum value from a set of values.
SELECT MAX(salary) FROM employee;
Determine the minimum value from a set of values.
SELECT MIN(salary) FROM employee;
Calculate the total number of rows in a table.
SELECT COUNT(*) FROM users;
Calculates the sum of a group of values.
SELECT SUM(salary) FROM employee;
Calculates the average value of a group of values.
SELECT AVG(salary) FROM employee;
For any types of queries, you can contact us on info[at]namasteui.com.
IplWin stands as a reliable and enthralling platform for Indian punters, offering a captivating blend…
Introduction In today's online age, consumers are constantly bombarded with information. They crave valuable content…
In today’s rapidly aging society, finding a living situation that provides older adults with both…
If you wish to purchase a Mac, then it is strongly suggested to consider purchasing…
Oil drilling is a complex process that involves several components, including the drilling bit. The…
Watches can be more than a fashion. They can be a symbol, especially in the…
View Comments
Great article. I appreciate you effort to such a wonderful information.