Home / mySQL / MYSQL Functions

MYSQL Functions

Hi all,
Welcome to MYSQL functions tutorial. MYSQL functions are useful while performing mathematical calculations, string concatenations, sub-strings etc. SQL provides many built in functions to performe these functionality or performing calculations on data.




The most commonly used MySQL functions including:

  • Aggregate functions
  • String functions
  • Date time functions
  • control flow functions



  • Aggregate Functions

    The aggregate functions returns the single value after calculating from a group of values or from values in a column.

    1) AVG()

    The MySQL AVG aggregate function returns the average values. Its general syntax:

    SELECT AVG(column_name) from table_name

    Example using AVG(). Consider following Employee table







    Employee_id Employee_name salary
    1 Appu 9000
    2 Ammu 8000
    3 Achu 6000
    4 Arathy 10000
    5 Ashwathi 8000

    SQL query to find average of salary will be,

    SELECT avg(salary) from Employee;

    Result of the above query will be,

    avg(salary)
    8200

    2) COUNT()

    It is used to count the number of rows in a database table.Its general Syntax is,

    SELECT COUNT(column_name) from table-name

    Example using COUNT(). Consider following Employee table




    Employee_id Employee_name salary
    1 Appu 9000
    2 Ammu 8000
    3 Achu 6000
    4 Arathy 10000
    5 Ashwathi 8000

    SQL query to count employees, satisfying specified condition is,

    SELECT COUNT(Employee_namee) from Employee where salary = 8000;

    Result of the above query will be,

    count(name)
    2





    3) FIRST()

    It is used for returns the first values from selected coloumn.Syntax for FIRST function is,

    SELECT FIRST(column_name) from table-name

    Example using FIRST(). Consider following Employee table




    Employee_id Employee_name salary
    1 Appu 9000
    2 Ammu 8000
    3 Achu 6000
    4 Arathy 10000
    5 Ashwathi 8000

    SQL query

    SELECT FIRST(Employee_name) from Employee;

    Result will be,

    first(Employee_name)
    Appu




    4) LAST()

    It is used for returns the last values from selected coloumn.Syntax for LAST function is,

    SELECT LAST(column_name) from table-name

    Example using LAST(). Consider following Employee table







    Employee_id Employee_name salary
    1 Appu 9000
    2 Ammu 8000
    3 Achu 6000
    4 Arathy 10000
    5 Ashwathi 8000

    SQL query

    SELECT LAST(Employee_name) from Employee;

    Result will be,

    last(Employee_name)
    Ashwathi




    5) MAX()

    It is used for returns the maximum values from selected coloumn.Syntax for LAST function is,

    SELECT MAX(column_name) from table-name

    Example using MAX(). Consider following Employee table




    Employee_id Employee_name salary
    1 Appu 9000
    2 Ammu 8000
    3 Achu 6000
    4 Arathy 10000
    5 Ashwathi 8000

    SQL query

    SELECT MAX(salary) from Employee;

    Result will be,

    MAX(salary)
    10000

    6) MIN()

    It is used for returns the minimum values from selected coloumn. Returns the smallest values. Syntax for LAST function is,

    SELECT MIN(column_name) from table-name

    Example using MIN(). Consider following Employee table




    Employee_id Employee_name salary
    1 Appu 9000
    2 Ammu 8000
    3 Achu 6000
    4 Arathy 10000
    5 Ashwathi 8000

    SQL query

    SELECT MIN(salary) from Employee;

    Result will be,

    MIN(salary)
    6000




    7) SUM()

    It is used for returns thetotal sum values from selected coloumn. Syntax for LAST function is,

    SELECT SUM(column_name) from table-name

    Example using MIN(). Consider following Employee table




    Employee_id Employee_name salary
    1 Appu 9000
    2 Ammu 8000
    3 Achu 6000
    4 Arathy 10000
    5 Ashwathi 8000

    SQL query

    SELECT SUM()salary) from Employee;

    Result will be,

    SUM(salary)
    41000




    String Functions

    Complete list of MySQL functions required to manipulate strings in MySQL.

    1) CONCAT()

    This is used to concatenate any string inside any MySQL command. It is used to combine two or more string into one string.The syntax is:

    CONCAT(str1,str2,…)

    2) LENGTH and CHAR_LENGTH

    MySQL string length functions that allow you to get the length of strings measured in bytes and in characters.





    3) REPLACE

    REPLACE() function allows to replace the old string to new string. The general syntax is

    REPLACE(str,old_string,new_string);

    4) SUBSTRING

    The SUBSTRING function returns a substring from a string starting at a specific position with a given length. The general syntax is:

    SUBSTR(string,position);

    Date and Time Functions

    1) DATEDIFF

    The DATEDIFF() function used for calculate the number of days between two date.The DATEDIFF function accepts two arguments which are two valid DATE. The general syntax is:

    DATEDIFF(date_expression_1,date_expression_2)

    Example

    SELECT DATEDIFF(‘2011-08-17′,’2011-08-17’); — 0 day
    SELECT DATEDIFF(‘2011-08-17′,’2011-08-08’); — 9 days
    SELECT DATEDIFF(‘2011-08-08′,’2011-08-17’); — -9 days

    2) DATE_FORMAT

    To format a data value to a specific format.the syntax is:

    DATE_FORMAT(date,format);

    The following table illustrates the specifiers and their meanings that you can use to construct date format string:










    Specifier Meaning
    %a Three-characters abbreviated weekday name e.g., Mon, Tue, Wed, etc.
    %b Three-characters abbreviated month name e.g., Jan, Feb, Mar, etc.
    %c Month in numeric e.g., 1, 2, 3…12
    %D Day of the month with English suffix e.g., 0th, 1st, 2nd, etc.
    %d Day of the month with leading zero if it is 1 number e.g., 00, 01,02, …31
    %e Day of the month without leading zero e.g., 1,2,…31
    %f Microseconds in the range of 000000..999999
    %H Hour with 24-hour format with leading zero e.g., 00..23
    %h Hour with 12-hour format with leading zero e.g., 01, 02…12
    %I Same as %h
    %i Minutes with leading zero e.g., 00, 01,…59
    %j Day of year with leading zero e.g., 001,002,…366
    %k Hour in 24-hour format without leading zero e.g., 0,1,2…23
    %l Hour in 12-hour format without leading zero e.g., 1,2…12
    %M Full month name e.g., January, February,…December
    %m Month name with leading zero e.g., 00,01,02,…12
    %p AM or PM, depending on other time specifiers
    %r Time in 12-hour format hh:mm:ss AM or PM
    %S Seconds with leading zero 00,01,…59
    %s Same as %S
    %T Time in 24-hour format hh:mm:ss
    %U Week number with leading zero when the first day of week is Sunday e.g., 00,01,02…53
    %u Week number with leading zero when the first day of week is Monday e.g., 00,01,02…53
    %V Same as %U; it is used with %X
    %v Same as %u; it is used with %x
    %W Full name of weekday e.g., Sunday, Monday,…, Saturday
    %w Weekday in number (0=Sunday, 1= Monday,etc.)
    %X Year for the week in four digits where the first day of the week is Sunday; often used with %V
    %x Year for the week, where the first day of the week is Monday, four digits; used with %v
    %Y Four digits year e.g., 2000, 2001,…etc.
    %y Two digits year e.g., 10,11,12, etc.
    %% Add percentage (%) character to the output

    The following are some commonly used date format strings:




    DATE_FORMAT string Formatted date
    %Y-%m-%d 7/4/2013
    %e/%c/%Y 4/7/2013
    %c/%e/%Y 7/4/2013
    %d/%m/%Y 4/7/2013
    %m/%d/%Y 7/4/2013
    %e/%c/%Y %H:%i 4/7/2013 11:20
    %c/%e/%Y %H:%i 7/4/2013 11:20
    %d/%m/%Y %H:%i 4/7/2013 11:20
    %m/%d/%Y %H:%i 7/4/2013 11:20
    %e/%c/%Y %T 4/7/2013 11:20
    %c/%e/%Y %T 7/4/2013 11:20
    %d/%m/%Y %T 4/7/2013 11:20
    %m/%d/%Y %T 7/4/2013 11:20
    %a %D %b %Y Thu 4th Jul 2013
    %a %D %b %Y %H:%i Thu 4th Jul 2013 11:20
    %a %D %b %Y %T Thu 4th Jul 2013 11:20:05
    %a %b %e %Y Thu Jul 4 2013
    %a %b %e %Y %H:%i Thu Jul 4 2013 11:20
    %a %b %e %Y %T Thu Jul 4 2013 11:20:05
    %W %D %M %Y Thursday 4th July 2013
    %W %D %M %Y %H:%i Thursday 4th July 2013 11:20
    %W %D %M %Y %T Thursday 4th July 2013 11:20:05
    %l:%i %p %b %e, %Y 7/4/2013 11:20
    %M %e, %Y 4-Jul-13
    %a, %d %b %Y %T Thu, 04 Jul 2013 11:20:05

    3) NOW

    Returns current date and time. Syntax is:

    SELECT NOW();




    Control flow functions

    1) IF

    Returns the value based on conditions.Syntax is:

    IF(expr,if_true_expr,if_false_expr)

    Example

    SELECT IF(1 = 2,’true’,’false’); — false

    SELECT IF(1 = 1,’ true’,’false’); — true




    If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

Loading Facebook Comments ...
Social Media Auto Publish Powered By : XYZScripts.com