In MySQL Advanced Function Part-3 we will discuss about the CURRENT_USER, DATABASE, DEFAULT, DENSE_RANK, FIRST_VALUE, FOUND_ROWS, GET_LOCK, GROUPING, GTID_SUBSET, GTID_SUBTRACT, HEX, ICU_VERSION, IF
CURRENT_USER Function:- CURRENT_USER Function is used for returning MySQL username and hostname. Which MySQL use to authenticate the current clients.
Syntax:- CURRENT_USER()
Example:-
SELECT CURRENT_USER();
Output:- guest@%
DATABASE Function:- The DATABASE Function is used for returning the current database name.
Syntax:- DATABASE()
Example:-
SELECT DATABASE();
Output:- employee
DEFAULT Function:- DEFAULT Function used for returning the table column default value which assigns, if no value is specified by the user then the table column will take the default value.
This function return error if the table column will not specify a default value.
Syntax:- DEFAULT(column name)
Example:- SELECT DEFAULT(salary) FROM employee.
DENSE_RANK Function:- DENSE_RANK Function is used for a partition of each row. This function is the MySQL window function.
This function always uses the OVER() clause and assigns rank on the basis of the ORDER BY clause.
Syntax:- DENSE_RANK()
FIRST_VALUE Function:- FIRST_VALUE Function is used for selecting the first row of a window frame, partition, or result set. This function is a window function.
Syntax:- FIRST_VALUE (expression) OVER ( [partition_clause] [order_clause] [frame_clause])
FOUND_ROWS Function:- FOUND_ROWS Function is used for returning a number of rows. Its uses with LIMIT clause.Syntax:- FOUND_ROWS()
Example:-
SELECT * FROM employee LIMIT 10;
SELECT FOUND_ROWS();
GET_LOCK Function:- GET_LOCK Function is used for locking or to simulate record lock. It accepts two parameters. name(string) and time. If time is negative that means an infinite lock. It returns 1 if successful, and 0 if fails. and NULL if an error produces. It locks only the current session.
Syntax:- GET_LOCK(string, time out)
GROUPING Function:- GROUPING Function is used to distinguish between a NULL representing the set of all values in a super-aggregate row (produced by a ROLLUP operation) from a NULL in a regular row.
Syntax:- GROUPING(column name)
GTID_SUBSET Function:- GTID_SUBSET Function is a stored function. Given two sets of global transaction identifiers set1 and set2 returns true if all GTIDs in set1 are also in set2. Returns false otherwise.
Syntax:- GTID_SUBSET(set1, set2);
GTID_SUBTRACT Function:- GTID_SUBTRACT Function is a stored function. It returns all GTIDs in a set that is not in the subset.
Syntax:- GTID_SUBTRACT(set, subset)
HEX Function:- HEX Function is used for returning a Hexadecimal value from a given string or decimal value.
Syntax:- HEX(string) or HEX(decimal number)
Example:-
SELECT HEX(0);
Output:- 0
ICU_VERSION Function:- ICU_VERSION Function is MySQL’s built-in function. It is used for the return version of the ICU(International Components for Unicode) library.
Syntax:- ICU_VERSION();
IF Function:- IF Function is used for returning the value of the IF condition is true or IF condition is false.
Syntax:- IF(condition, if value is true, if value is false)
Example:-
SELECT IF(200<100, “YES”, “NO”);
Output:- NO
Read Also:-
MySQL Advanced Function Part-1
MySQL Advanced Function Part-2
MySQL Advanced Function Part-4
MySQL Advanced Function Part-5
Read more detail about functions in the MySQL manual.
