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-
- Select Function Syntax-
- Drop Function Syntax-
CREATE FUNCTION name (param_name param_type)
RETURNS return_type
BEGIN
RETURN return_value;
END
SELECT function name
DROP FUNCTION function name
Example-
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 |
