MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL Advance function
2022-04-08 08:08:02

MySQL Advance function

The advance function operates numerical values, string values, and data types.  The advance function converts, displays, and compares given values as per requirement. Here, you find out database, table, and MySQL interface information. You can also find table-related data like null data. The advance function mostly includes BINARY(), CONVERT(), DATABASE(), and ISNULL() functions.

MySQL advance function syntax

Mysql advance function syntax gives below.

SELECT advance _function();

MySQL advance function with position syntax shows below.

SELECT advance _function(value);

MySQL advance function with several values syntax shows below.

SELECT advance _function(value1, value2);

MySQL Advance Functions

The following table shows advanced functions and its description.

FunctionDescription
BINThis function displays a binary representation of numerical data.
BINARYThis function converts a given value into binary data.
CASTThis function converts a given data type value into a required datatype value.
COALESCEThis function displays the first non-null value in table data.
CONNECTION_IDThis function displays the unique and current connection ID.
CONVThis function converts a one-number base system to another numerical system.
CONVERTThis function converts a value into the required data type or character set of the table.
CURRENT_USERThis function displays the user name or hostname of the MySQL system.
DATABASEThis function displays the name of the current database.
IFThis function declares true and false conditions. If a condition is TRUE, then it displays the output.
IFNULLThis function declares a specific value that is null. If the query is not null, then it displays the given value.
ISNULLThis function is Boolean and displays 1 and 0 values based on null data.
LAST_INSERT_ID This function displays the last row of the AUTO_INCREMENT id. It helps to insert and update the table data.
NULLIFThis function compares two queries or values. If two values are equal, then it shows the NULL output. If two values are not equal, then shows first value is output.
SESSION_USERIt shows the exits user name or hostname of the MySQL system.
SYSTEM_USERIt returns the exits user name or hostname of the MySQL system.
USERThe user() function shows the exits user name or hostname of the MySQL system.
VERSIONThe version() function displays the current version of the MySQL system.

Examples of the MySQL advance function

The advance function works on the numerical value, string data, and MySQL database system interface. You can use a function to get version, system, database, connection id and different users. Here, you can convert from one type of data to another format of data. This function finds the null value and not the null value of the given data.

1. Example of the advance function without value

MySQL function returns database, version, and system information of the MySQL data management system interface. This example uses four advanced functions without value. Here, you do not need any value with function.

mysql > SELECT USER(), VERSION(), CONNECTION_ID(), DATABASE();

Output

MySQL Advance function

This output image returns the user name, version of the MySQL system, connection id, and current database. Each MySQL system provides different output information with functions.

2. Example of the advance function with different user

Execute the below MySQL query to get different users using the advance function. This advanced function provides current user, session user and system user. You can get the last insert id of the database system. This function returns either a stable value or a different value as output. This advanced user function does not require a specific value.

mysql > SELECT LAST_INSERT_ID(), 
               CURRENT_USER(), 
               SESSION_USER(), 
               SYSTEM_USER();

Output

MySQL Advance function

The above image displays different system's users of the mysql system. The output shows the current, session, and system user of the interface. The MySQL user provides the same or different information as per requirement. The first function column return id of the last insert data.

3. Example of the advance function with a value

You can use binary (bin), conversion (conv), and cast functions in this function. This function returns a value with a specific format. This advanced function requires a specific value. The binary function shows a value of the numerical and string data. The conv function converts from binary to numerical and vice versa. The cast function returns particular data and other data.  

mysql > SELECT BIN(21) AS binary_number,
	CONV (21, 10, 2) AS decimal_binary,
	CONV (10010, 2, 10) AS binary_decimal,
             CAST("2021-08-21" AS DATE) AS date_value;

Output

MySQL Advance function

The image shows the output of the bin, conv, and cast function. The 21 numerical value converts into a binary value. The conv function converts from 21 values into 10101 binary values. The 10010 binary number converts into 18 decimal values. The last function shows data using date cast.

4. Example of the advance function with a null value

The advance function provides isnull, ifnull, and nullif functions. This function determines either the value is null or not null.

mysql > SELECT ISNULL (NULL),
	NULLIF ("NULL", 59),
	IFNULL ("NULL", "MySQL");

OUTPUT

MySQL Advance function

If a function contains a null value, then the advance function returns a null value. If MySQL query contains a value, then the advance function returns available data.