Before going to Learn the MySQL Built-in Functions, let us have a classification :
Direct Switch to Contents
- Built-in functions
- Aggregate Functions
- Math Functions
- String Function
- Date time functions
- Some Examples
Let’s have a brief categorizationvia Snap:
MySQL has many built-in functions.
This blog gives you the most commonly used MySQL functions including basic SQL Functions, aggregate functions, string functions, date-time functions, control flow functions, etc.
One of the basic function is SELECT DATABASE();
This function shows the current database name that is in use.
Some of the aggregate functions are SUM, AVG, MAX, MIN, and COUNT functions.
Some of the String Function is CONCAT, LENGTH, LEFT, LOWER, REPLACE, RIGHT, SUBSTRING, UPPER, TRIM Functions.
Let us take one by one by catteries.
AVG() :Return the average of non-NULL values.
COUNT(): Return the number of rows in a group, including rows with NULL values.
MAX() :Return the highest value (maximum) in a set of non-NULL values.
MIN() :Return the lowest value (minimum) in a set of non-NULL values.
SUM(): Return the summation of all non-NULL values a set.
ABS() :Returns the absolute value of a number
CEIL(): Returns the smallest integer value greater than or equal to the input number (n).
FLOOR(): Returns the largest integer value not greater than the argument
MOD(): Returns the remainder of a number divided by another
ROUND(): Rounds a number to a specified number of decimal places.
TRUNCATE(): Truncates a number to a specified number of decimal places.
CONCAT : Concatenate two or more strings into a single string
INSTR : Return the position of the first occurrence of a substring in a string
LENGTH : Get the length of a string in bytes and in characters
LEFT : Get a specified number of leftmost characters from a string
LOWER : Convert a string to lowercase
LTRIM : Remove all leading spaces from a string
REPLACE : Search and replace a substring in a string
RIGHT : Get a specified number of rightmost characters from a string.
TRIM : Remove unwanted characters from a string.
FORMAT : Format a number with a specific locale, rounded to the number of decimals
UPPER : Convert a string to uppercase
Date time functions
CURDATE : Returns the current date.
DATEDIFF : Calculates the number of days between two DATE values.
DAY : Gets the day of the month of a specified date.
DATE_ADD : Adds a time value to date value.
DATE_SUB : Subtracts a time value from a date value.
DATE_FORMAT : Formats a date value based on a specified date format.
DAYNAME : Gets the name of a weekday for a specified date.
DAYOFWEEK : Returns the weekday index for a date.
EXTRACT : Extracts a part of a date.
LAST_DAY : Returns the last day of the month of a specified date
NOW : Returns the current date and time at which the statement executed.
MONTH : Returns an integer that represents a month of a specified date.
WEEK : Returns a week number of a date.
WEEKDAY : Returns a weekday index for a date.
YEAR : Return the year for a specified date.
Let us take some of the examples one by one.
The INSERT() function inserts a string within a string at the specified position and for a certain number of characters.
Syntax: INSERT(string, position, number, string2);
string : Required. The string that will be modified
position : Required. The position where to insert string2
number : Required. The number of characters to replace
string2 : Required. The string to insert into the string
This function is used to add two or more strings.
There may be one or more arguments.
Returns the string that results from concatenating the arguments. It also returns a nonbinary string, if all arguments are nonbinary strings.
Returns a binary string, if the arguments include any binary strings. If the argument is numeric, it is converted to its equivalent nonbinary string form. Returns NULL if any argument is NULL.
This function retrieves the average value of a given expression. If the function does not find a matching row, it returns NULL. Where expr is given expression.
The SQRT function returns the square root of a number.
The syntax for the SQRT function in MySQL is:
SQRT( number )
A positive number used to calculate the square root.
Note: The SQRT function will return NULL, if the number is a negative value.
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows. The above syntax is the general SQL 2003 ANSI standard syntax
SQL UPPER function
The SQL UPPER function converts all the letters in a string into uppercase. If you want to convert a string to lowercase, you use the LOWER function instead.
The syntax of the UPPER function is as simple as below.
If the input string is NULL, the UPPER function returns NULL, otherwise, it returns a new string with all letters converted to uppercase.
Besides the UPPER function, some database systems provide you with an additional function named UCASE which is the same as the UPPER function. It is “there is more than one way to do it”.
Want To Learn More MySQL Functions Click Here.
Here we discussed the basic knowledge functions in my SQL. There is some built-in function and I have explained a systematic way of them with examples.
COUNT() function is an Aggregate Function that returns the number of rows in a result set returned by a SELECT statement. The general syntax is as follow:
The COUNT(expression) returns the number of rows that do not contain NULL values as the result of the expression.
An Aggregate Function summarizes the results of an expresion over a number of rows, returning a single value. The general syntax for the most Aggregate Function is as follow:
Min returns the smallest value of a column and Max return the largest value of a column.
Previous Blog | What are Keys in MySQL? Overview And Understanding