Return the user name and host name for the MySQL account:
SELECT CURRENT_USER();
The CURRENT_USER() function returns the user name and host name for the MySQL account that the server used to authenticate the current client.
The result is returned as a string in the UTF8 character set.
CURRENT_USER()
Convert a value to a DATE datatype:
SELECT CONVERT("2017-08-29", DATE);
The CONVERT() function converts a value into the specified datatype or character set.
CONVERT(value, type)
OR:
CONVERT(value USING charset)
Convert a number from numeric base system 10 to numeric base system 2:
SELECT CONV(15, 10, 2);
The CONV() function converts a number from one numeric base system to another, and returns the result as a string value.
Note: This function returns NULL if any of the parameters are NULL.
CONV(number, from_base, to_base)
Return the unique connection ID for the current connection:
SELECT CONNECTION_ID();
The CONNECTION_ID() function returns the unique connection ID for the current connection.
CONNECTION_ID()
Return the first non-null value in a list:
SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');
The COALESCE() function returns the first non-null value in a list.
COALESCE(val1, val2, ...., val_n)
Convert a value to a DATE datatype:
SELECT CAST("2017-08-29" AS DATE);
The CAST() function converts a value (of any type) into the specified datatype.
CAST(value AS datatype)
Go through conditions and return a value when the first condition is met:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN "The quantity is greater than 30"
WHEN Quantity = 30 THEN "The quantity is 30"
ELSE "The quantity is under 30"
END
FROM OrderDetails;
The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result.
If no conditions are true, it will return the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
Convert a value to a binary string:
SELECT BINARY "Iampsp.com";
The BINARY function converts a value to a binary string.
BINARY value
Return a binary representation of 15:
SELECT BIN(15);
The BIN() function returns a binary representation of a number, as a string value.
BIN(number)
Return the year and week number for a date:
SELECT YEARWEEK("2017-06-15");
The YEARWEEK() function returns the year and week number (a number from 0 to 53) for a given date.
YEARWEEK(date, firstdayofweek)