MySql Functions

MySQL aggregate functions

an aggregate function performs a calculation on a set of values and returns a single value.
MySQL provides many aggregate functions including AVG, COUNT, SUM, MIN , MAX, etc. An aggregate function ignores NULL values when it performs calculation except for the COUNT function.

AVG function

The AVG function calculates the average value of a set of values. It ignores NULL values in the calculation.
Syntax

AVG(expression)

Query:-Get managerwise average salary from employee table order by salary ascending

SELECT manager, avg( SALARY ) AvgSalary
FROM emp
GROUP BY manager
ORDER BY AvgSalary ASC

COUNT function

The COUNT function returns the number of the rows in a table.
Syntax

count(expression)

Query:-Get Total Number Of Employee from emp table where salary>1000 as Group By manager

SELECT manager, COUNT( * ) AS "Number of Employee"
FROM emp
WHERE salary >=1000
GROUP BY manager

SUM function

The SUM function returns the sum of a set of values. The SUM function ignores NULL values. If no matching row found, the SUM function return NULL.
Syntax

sum(expression)

Query:-Get Total Salary of all employee from emp table as Group By Salary.

SELECT salary, SUM( salary ) AS "Total Salary"
FROM emp
GROUP BY salary;

MAX function

The MAX function returns the maximum value in a set of values.
Syntax

MAX(expression)

Query:-Get Highest Salary from emp table.

SELECT empid, MAX( salary ) AS "Highest salary"
FROM emp

Min function

The MIN function returns the minimum value in a set of values.
Syntax

Min(expression)

Query:-Get Lowest Salary from emp table as Group By manager.

SELECT empid, MIN( salary ) AS "Lowest salary"
FROM emp
GROUP BY manager;

MySQL GROUP_CONCAT function

The GROUP_CONCAT function concatenates strings from a group into one string with various options.
Syntax

GROUP_CONCAT(DISTINCT expression
ORDER BY {column_name | usinged_integer | expression}
SEPARATOR sep);

Query:-Get all Last Name where employee locate as a comma-separated string.

SELECT GROUP_CONCAT( lastName )
FROM emp;
Query:-Get Distinct Values in Last Name where employee locate as a comma-separated string.

SELECT GROUP_CONCAT( DISTINCT lastName )
FROM emp;

MySQL string functions

MySQL String Based function which is used on string operation.
Syntax

Function Name(Expression)

Concat Function

The concat function is used to concatenate(add string) two or more string and returns concatenating string.
Syntax

CONCAT(str1,str2,…)

Query:-Get fullname of employee from emp table using cancat function.

 SELECT CONCAT( firstName, lastName ) fullname
FROM emp
LIMIT 5 

Concat with Separator Function

Syntax:-CONCAT_WS(seperator,str1,str2,…)

 SELECT CONCAT_WS( ', ', firstName, lastName ) fullname
FROM emp
LIMIT 5 

MySQL string length funtion

you to get the length of strings measured in bytes and in characters.
MySQL supports various character sets such as latin1, utf8, etc. You use the SHOW CHARACTER SET statement to get all character sets supported by MySQL database server.

SHOW CHARACTER SET;

Syntax for string

LENGTH(str);

Syntax for Character

CHAR_LENGTH(str);

Query:-Get the length of string and character of emp firstname.

 SELECT length( firstName )
FROM emp
SELECT CHAR_LENGTH( firstName )
FROM emp

MySQL Replace Function

REPLACE to allow you to replace a string in a column of a table by a new string.
Syntax

REPLACE(field_name,string_to_find,string_to_replace);

Query:-Get First_Name from employee table after replacing ‘o’ with ‘$’.

SELECT REPLACE( firstName, 'o', '$' )
FROM emp

MySQL Substring

The SUBSTRING function returns a substring from a string starting at a specific position with a given length.
Syntax

SUBSTR(string,position);

Query:-Get substring on position 6 from given string .

 SELECT SUBSTRING( 'parvez alam', 6 ) 

Date Function
MySQL DATEDIFF Function

MySQL DATEDIFF function to calculate the number of days between two date values.
Syntax

DATEDIFF(date_expression_1,date_expression_2);

Example.

SELECT DATEDIFF( '2011-08-17', '2011-08-17' ) ;# Rows: 1-- 0 day
SELECT DATEDIFF( '2011-08-17', '2011-08-08' ) ;# Rows: 1-- 9 days
SELECT DATEDIFF( '2011-08-08', '2011-08-17' ) ;-- -9 days

To calculate the number of days between required date and shipped date for orders in the orders table.

SELECT orderNumber,
       DATEDIFF(requiredDate,shippedDate) daysLeft
FROM orders
ORDER BY daysLeft DESC;

Gets all orders whose statuses are in process and calculates the number of days between the ordered date and required date:

SELECT orderNumber, 
       DATEDIFF(requiredDate,orderDate) remaining_days
FROM orders
WHERE status = 'In Process'
ORDER BY remaining_days;

For calculating an interval in week or month, you can divide the returned value of the DATEDIFF function by 7 or 30 as the following query:

SELECT orderNumber, 
       DATEDIFF(requiredDate,orderDate) /7, 
       DATEDIFF(requiredDate,orderDate) /30 
FROM orders
WHERE status = 'In Process'

MySQL DATE_FORMAT Function

MySQL DATE_FORMAT function to format a date value based on a specific format.
MySQL DATE_FORMAT Function
Syntax

DATE_FORMAT(date,format)

SELECT orderNumber,
       DATE_FORMAT(orderdate,'%Y-%m-%d')  orderDate,
       DATE_FORMAT(requireddate,'%a %D %b %Y')  requireddate,
       DATE_FORMAT(shippedDate,'%W %D %M %Y')  shippedDate
FROM orders;
SELECT orderNumber, 
       DATE_FORMAT(shippeddate,'%W %D %M %Y') 'Shipped date'
FROM orders
WHERE shippeddate IS NOT NULL
ORDER BY shippeddate;

MySQL STR_TO_DATE() Function

MySQL STR_TO_DATE() function to convert a string into a date time value.
Syntax

STR_TO_DATE(str,fmt);

using STR_TO_DATE() function to convert strings into a date and/or time values

SELECT STR_TO_DATE( '21,5,2013', '%d,%m,%Y' ) ;

The STR_TO_DATE() function ignores extra characters at the end of the input string when it parses the input string based on the format string. See the following example:

SELECT STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y');

The STR_TO_DATE() sets all incomplete date values, which are not provided by the input string, to zero. See the following example:

SELECT STR_TO_DATE('2013','%Y');

converts a time string into a TIME value:

SELECT STR_TO_DATE('113005','%h%i%s');

the STR_TO_DATE() function sets unspecified time part to zero, see the following example:

SELECT STR_TO_DATE('11','%h');

converts of the string into a DATETIME value because the input string provides both data and time parts.

SELECT STR_TO_DATE('20130101 1130','%Y%m%d %h%i') ;

MySQL NOW() function

The MySQL NOW() function returns the current date and time in the configured time zone as a string or a number in the ‘YYYY-MM-DD HH:MM:DD’ or ‘YYYYMMDDHHMMSS.uuuuuu’ format.
Syntax

SELECT NOW();

Query:-Get The Current Date & Time of the system.

SELECT NOW();

Query:-The NOW() function returns a constant date and time at which the statement started executing.

SELECT NOW(), SLEEP(5), NOW();

Query:-If you want to get exact time at which the statement executes, you need to use SYSDATE() instead; see the following example:

SELECT SYSDATE(), SLEEP(5), SYSDATE();

Query:-The following statement returns the current date and time, now minus 1 hour and now plus 1 hour:

SELECT (NOW() - INTERVAL 1 HOUR) 'NOW - 1 hour',
        NOW(),
       -- mysql now plus 1 hour
       NOW() + INTERVAL 1 HOUR 'NOW + 1 hour';

Query:-The following statement returns the current date and time, now minus 1 day and now plus 1 day:

        -- mysql now minus 1 day
SELECT (NOW() - INTERVAL 1 DAY) 'NOW - 1 day',
        NOW(),
        -- mysql now plus 1 day
        (NOW() + INTERVAL 1 DAY) 'NOW + 1 day';

Admin has written 171 articles