MySQL function
MySQL function is a piece of program to perform some specific task. Here we pass a parameter and then return a single value. MySQL function mainly supports string, numeric, conditional, and date functions. MySQL main function shows below.
MySQL control flow function | This function controls the table data. |
MySQL aggregation function | This function works on the numerical table data. |
MySQL comparison function | This function compares the table data. |
MySQL date function | This function works on the date of the table data. |
MySQL string function | This function works on the string table data. |
MySQL Math function | This function works on the numerical table data. |
MySQL other function | This function works with the table data in various ways. |
MySQL control flow function
The control flow function uses values or operands for logical conditions. This function control application's data using the below statement.
MySQL IF statement | The IF statement shows the true result of the control flow function. |
MySQL IFNULL statement | The IFNULL statement shows null or not null of the data. |
MySQL NULLIF statement | The NULLIF statement works on the not null value of the data. |
MySQL CASE statement | This statement works on multiple conditions. |
MySQL Aggregate function
The aggregation function works on multiple values and returns the output in a single value. The advanced and complex operation becomes simple using the aggregation function. The summation (SUM), MAX, AVG, MIN, COUNT functions come under the aggregate function.
SUM | This function works for the addition of the table values. |
MAX | This function determines the maximum value of the data set. |
AVG | This function determines the average value of the data set. |
MIN | This function determines the minimum value of the data set. |
COUNT | This function returns the total count of the data set. |
MySQL comparison function
The comparison function compares and maintains values of the MySQL table. This function contains COALESCE, GREATEST, LEAST, and ISNULL functions.
COALESCE | This statement displays the first not null value of the table data. |
GREATEST AND LEAST | This statement displays the greatest and last values of the table. |
ISNULL function | This statement displays the null value of the table data in a Boolean form. |
MySQL Date function
The date function displays day, year, month, time, and current date. This function shows the time as per the requirement of the application. The data either store on the table or display directly on an output page. The following date function along with descriptions are given below:
ADD_DATE | This function shows a date interval as per requirement. |
ADD_TIME | This function shows a time interval as per requirement. |
CURATE | This function shows a current date interval. |
CURRENT_DATE | This function shows a current date interval. |
CURRENT_TIMESTAMP | This function shows the current date and time. |
CURTIME | This function shows a current time. |
DATE | This function extracts the date interval as per requirement. |
DATEDIFF | This function shows the days between two dates. |
DATE_ADD | This function adds a date to date and shows the date. |
DATE_SUB | This function subtracts a date to date and shows the date. |
DAY | This function shows the day of the date or month. |
DAYOFMONTH | This function shows the month of the date. |
DAY OF WEEK | This function shows the week of the date. |
DAYOFYEAR | This function shows the year of the date. |
FROM_DAYS | This function shows the numerical date value of the given date. |
HOUR | This function shows the hours of the date and time. |
LOCAL TIME | This function displays the current date and time |
LOCALTIMESTAMP | This function displays the current date and time |
MAKEDATE | This function creates and displays the date. |
MAKE TIME | This function creates and displays the time (hour, minute, and second). |
MICROSECOND | This function shows the microsecond of the given date and time. |
MINUTE | This function shows the minute of the given time or date-time. |
MONTH | This function shows the month of the given date. |
MONTH NAME | This function shows the month of the given date. |
NOW | This function shows the current date and time. |
PERIOD_ADD | This function inserts months of the date as a period. |
PERIOD_DIFF | This function shows the difference between the two periods. |
QUARTER | This function shows the quarter of the given year. |
SECOND | This function shows the second of the given datetime. |
SEC_TO_TIME | This function shows the time of the datetime into seconds. |
STR_TO_DATE | This function shows a date in a string format. |
SUBDATE | This function subtracts a date of the datetime and returns the date. |
SUBLIME | This function subtracts a time of the datetime and returns the time. |
TIME | This function shows the time of the given datetime. |
TIME_FORMAT | This function shows time with the required format. |
TIME_TO_SEC | This function converts a given time into seconds. |
TIMEDIFF | This function differences between two times or datetime. |
TIMESTAMP | This function shows a date or datetime. |
TO_DAYS | This function shows the number of days between two dates. |
WEEK | This function displays the week number of the date. |
WEEKDAY | This function shows the number of the weekday of the date. |
WEEKOFYEAR | This function shows the weeks of the date. |
YEAR | This function shows the year of the date. |
YEAR WEEK | This function shows the year and week of the given date. |
MySQL string function
The string function is used to maintain and operate string values. This function modifies and specifies required string data as per function. We can use concatenation, convert, removes, and replaces the string data using the string function.
ASCII | This function displays the ASCII value of the given character. |
CHAR_LENGTH | This function displays the length of a string in character. |
CONCAT | This function combines the two or more expressions of the data. |
CONCAT_WS | This function separates the two or more expressions of the data. |
FIELD | This function displays the index position of a value in a list. |
FIND_IN_SET | This function displays the position of a value in a list. |
INSERT | This function inserts a string at the required position with a given number of characters. |
INSTR | This function displays the position of the first occurrence of a string with the other strings. |
LCASE | This function uses to convert a string to lower-case. |
UCASE | This function uses to convert a string to upper-case. |
UPPER | This function uses to convert a string to upper-case. |
LEFT | This function extracts several characters from a left string. |
LENGTH | This function displays the length of a string in bytes format. |
LOCATE | This function displays the position of the first circumstance of a substring. |
LOWER | This function uses to convert a string to lower-case. |
LTRIM | This function removes the initial spaces of the string. |
MID | This function extracts a substring of a string at any position. |
POSITION | This function displays the position of the first occurrence of a substring in a string |
REPEAT | This function repeats a string in the list of the data. |
REPLACE | This function replaces the circumstance of the substring in a string with a new substring. |
REVERSE | This function used to reverse a string and return the required output. |
RIGHT | This function extracts several characters from a right string. |
RTRIM | This function uses to remove trailing spaces from a string. |
SPACE | This function displays a string of the space characters |
STRCMP | This function uses to compares two strings. |
SUBSTR | This function uses to remove a substring from a starting string at any position. |
TRIM | This function uses to remove leading and trail spaces from a string. |
MySQL Math function
The math function operates numerical values and displays output in numeric form. The math function finds out sin, cos, tan values. Here, you find out the absolute value, reminder, and logarithmic values using a data table. The math function mostly includes ABS(), MOD(), ROUND(), and TRUNCATE() functions.
ABS() | This function displays the absolute value of a given data number. |
CEIL() | This function displays the smallest integer value. This integer value is greater than or equal to the given value (n). |
FLOOR() | This function displays the largest integer value. This integer value is not greater than the given value. |
MOD() | This function displays the remainder of a division operation. |
ROUND() | This function shows the round number of the decimal number or given number. |
TRUNCATE() | This function truncates a decimal point of the given number. |
ACOS (n) | This function displays the "arc cosine" of the given value (n). This function displays a null value when the given value is not in a range of -1 to 1. |
ASIN (n) | This function displays the "arc sine" of the given value (n). This function displays a null value when the given value is not in a range of -1 to 1. |
ATAN () | This function displays the arctangent of the given value (n). |
ATAN2 (n,m), ATAN(m,n) | This function displays the arctangent of the two variables or values such as an "n" and "m". |
CONV(n, from_base, to_base) | This function converts from a given number base to the required number base. |
COS (n) | This function displays the cosine of the given value (n). Here, the given value (n) is the radian. |
COT (n) | This function displays the cotangent of the given value (n). |
CRC32 () | This function displays a 32-bit unsigned value. This function determines the cyclic redundancy check of the 32-bit value. |
DEGREES (n) | This function converts radian's value into degrees of the given value (n). |
EXP (n) | This function displays the specific power of e raised into the power of the given value (n). |
LN (n) | This function displays the natural logarithm of the given value (n). |
LOG (n) | This function displays the natural logarithm of the given value. |
LOG10 () | This function displays the base-10 logarithm of the given value. |
LOG2 () | This function displays the base-2 logarithm of the given value. |
PI () | This function displays the PI value. |
POW () | This function displays the specific power of the given value. |
POWER () | This function displays the specific power of the given value. |
RADIANS() | This function displays given value converts into radians value. |
RAND() | This function displays a random value that belongs to a floating-point. |
SIGN (n) | This function displays the sign of the given value (n). This value depends on the negative (-1), zero (0), or positive (1). |
SIN (n) | This function displays the sine of a given value (n). |
SQRT (n) | This function displays the square root of the given value (n). |
TAN (n) | This function displays the tangent of the given value (n). |
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. With the help of this function, you find out database, table, and MySQL interface information. The advance function mostly includes BINARY(), CONVERT(), DATABASE(), and ISNULL() functions.
BIN | This function displays a binary representation of numerical data. |
BINARY | This function converts a given value into binary data. |
CAST | This function converts a given data type value into a required datatype value. |
COALESCE | This function displays the first non-null value in table data. |
CONNECTION_ID | This function displays the unique and current connection ID. |
CONV | This function converts a one-number base system to another numerical system. |
CONVERT | This function converts a value into the required data type or character set of the table. |
CURRENT_USER | This function displays the user name or hostname of the MySQL system. |
DATABASE | This function displays the name of the current database. |
IF | This function declares true and false conditions. If a condition is TRUE, then it displays the output. |
IFNULL | This function declares a specific value that is null. If the query is not null, then it displays the given value. |
ISNULL | This 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. This function helps to insert and update the table data. |
NULLIF | This 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_USER | This function shows the exits user name or hostname of the MySQL system. |
SYSTEM_USER | This function shows the exits user name or hostname of the MySQL system. |
USER | This function shows the exits user name or hostname of the MySQL system. |
VERSION | This function shows the current version of the MySQL system. |