MySQL Database Function

MySQL Simple Function

  • In MySQL you can create own function to return specific data. Function are normally used for computation.
  • The retutn clause is mandatory for a function.
  • It indicates the return type of the function, and the function body must contain a RETURN value statement.
  • There are various queries on funtion:
  • Create Function
  • Syntax-
    CREATE FUNCTION name (param_name param_type)
    RETURNS return_type
    BEGIN
    RETURN return_value;
    END

  • Select Function
  • Syntax-
    SELECT function name

  • Drop Function
  • Syntax-
    DROP FUNCTION function name

Example-
Simple mysql functions

MySQL Aggregate Function

  • In SQL functions are subprograms defined inside the database
  • They are basically functions that can be called from inside SQL statements, and are useful for the same reasons that calling functions in other languages are useful.
  • Most SQL languages come with some simple important functions and user defined functions can be added. There are different aggregate functions:
  • String Funtion
    Function Description
    LEN() Returns the length of a text field
    UCASE() Converts a field to upper case
    LCASE() Converts a field to lower case
    FORMAT() Formats how a field is to be displayed
    MID() Extract characters from a text field

    Numerical functions
    Function Description
    MIN() Returns the smallest value
    MAX() Returns the largest value
    AVG() Returns the average value
    SUM() Returns the sum
    COUNT() Returns the number of rows
    ROUND() Rounds a numeric field to the number of decimals specified

    Date functions
    Function Description
    FIRST() Returns the first value
    LAST() Returns the last value
    NOW() Returns the current system date and time