INBUILT FUNCTIONS IN MySQL

Answer»

STRING FUNCTIONS

Function	Description
ASCII	Returns the ASCII value of a character
CHAR_LENGTH	Returns the length of a string.
CHARACTER_LENGTH	Returns the length of a string
CONCAT	Concatenates two or more expressions.
CONCAT_WS	Concatenates with a separator.
FIELD	Returns the index of value in a list.
FIND_IN_SET	Returns the index of a string within a list.
FORMAT	Changes the format/representation.
INSERT	Inserts a string within a string at a given index.
INSTR	Returns the index of the first occurrence of a string in another one.
LCASE	Converts an entire string to lowercase.
LEFT	Extracts a length of characters from the left of a string.
LENGTH	Returns the string length in bytes.
LOCATE	Returns the location of the first occurrence of a substring in a given string
LOWER	Converts an entire string to lowercase.
LPAD	Left-pads a string with a given string.
LTRIM	Removes spaces from the left of a string.
MID	Extracts a substring from a string at a given position.
POSITION	Returns the location of the first occurrence of a substring in a given string
REPEAT	Repeats the string the number of times the user specifies.
REPLACE	Replaces occurrences of a substring in a string with another substring.
REVERSE	Reverses the string.
RIGHT	Extracts a length of characters from the right of a string.
RPAD	Right-pads a string with a given string.
RTRIM	Removes spaces from the right of a string.
STRCMP	Checks whether two strings are equal.
SUBSTR	Extracts a substring from a string at a position mentioned by the user.
SUBSTRING	Same as substr.
TRIM	Trims leading and trailing spaces from a string as specified by the user.
UCASE	Converts an entire string to uppercase.
UPPER	Converts an entire string to uppercase.

NUMERIC FUNCTIONS

Function	Description
ABS	Returns the absolute value.
ACOS	Returns the cosine inverse.
ASIN	Returns the sine inverse.
ATAN	Returns the tan inverse of one or two numbers.
ATAN2	Returns the tan inverse of two numbers.
AVG	Returns the mean value.
CEIL	Returns the smallest integer that is greater than or equal to the number
CEILING	Returns the smallest integer that is greater than or equal to the number
COS	Returns the cosine.
COT	Returns the cotangent.
COUNT	Returns the number of records returned by a query.
DEGREES	Converts angle in Radians to Degrees.
DIV	Integer division
EXP	Returns e raised to the power of value mentioned.
FLOOR	Returns the largest integer that is less than or equal to a number
GREATEST	Returns the largest value in the list.
LEAST	Returns the smallest value in the list.
LN	Calculates logarithm to the base e.
LOG	Calculates logarithm to the base e.
LOG10	Calculates logarithm to the base 10.
LOG2	Calculates logarithm to the base 2.
MAX	Returns the largest value in a set.
MIN	Returns the least value in a set.
MOD	Returns the remainder after division of two numbers.
PI	Returns value of π
POW	Used for exponents.
POWER	Used for exponents.
RADIANS	Converts angle in Degree to Radians.
RAND	Generates a random number.
ROUND	Rounds the number to the nearst decimal place.
SIGN	Returns the sign of a number
SIN	Returns the sine.
SQRT	Returns the root of a number.
SUM	Calculates the sum of a set.
TAN	Returns the tangent.

MYSQL DATE FUNCTION

Function	Description
ADDDATE	Adds a date interval and return the value.
ADDTIME	Adds a time interval and then returns the value.
CURDATE	Returns today’s date
CURRENT_DATE	Same as CURDATE
CURRENT_TIME	Returns the time at the moment
CURRENT_TIMESTAMP	Returns date and time at the moment.
CURTIME	Returns time at the moment.
DATE	Picks up the date from an expression of Date/Time.
DATEDIFF	Returns number of days between two given dates.
DATE_ADD	Similar to ADDDATE
DATE_FORMAT	Changes the format in which Date is displayed.
DATE_SUB	Subtracts a time interval and returns the value.
DAY	Returns the weekday for today.
DAYNAME	Returns the weekday name for any date.
DAYOFMONTH	Used to retrieve the index of the day of the month of any date.
DAYOFWEEK	Used to retrieve the index of the weekday of any date.
DAYOFYEAR	Used to retrieve the index of the day of a year of any date.
EXTRACT	Extracts a part of any date.
HOUR	Returns the “hours” in a given time.
LAST_DAY	Return the last day of the given month.
LOCALTIME	Returns the date and time at the moment.
LOCALTIMESTAMP	Similar to LOCALTIME.
MAKEDATE	Returns a date based on the year and the no. of days you specify.
MAKETIME	Returns a time based on the hours , minutes and seconds you specify.
MICROSECOND	Returns the microseconds in a given time.
MINUTE	Returns the minutes in a given time.
MONTH	Returns the month on a given date.
MONTHNAME	Same as MONTH but returns the name of the month.
NOW	Returns date and time at the moment.
PERIOD_ADD	Adds a specific number of months.
PERIOD_DIFF	Return the difference between two time periods.
SECOND	Return the seconds in a given time.
SEC_TO_TIME	Returns time in seconds.
STR_TO_DATE	Formats the date based on a particular string.
SUBDATE	Same as DATE_SUB.
SUBTIME	Subtracts a time interval.
SYSDATE	Returns the date/time reflected by the system.
TIME	Returns the time from a date/time value.
TIME_FORMAT	Time is displayed based on a certain format.
TIME_TO_SEC	Returns time in seconds.
TIMEDIFF	Returns the difference between two date-time values.
TO_DAYS	Returns the number of days between amy date and "0000-00-00"

ADVANCED MYSQL FUNCTION

Function	Description
BIN	Returns binary value of a given number.
BINARY	Converts a given string to a binary string.
CAST	Converts data from one data type to another.
COALESCE	Returns the first non-null value in a set or list.
CONV	Converts a number from one number-base system to another
CONVERT	Similar to CAST in working
CURRENT_USER	Returns the user name and host name for the MySQL account that is currently used.
DATABASE	Returns the name of the database currently in use.
IF	IF condition statement.
SESSION_USER	Returns the current MySQL user name and host name.
SYSTEM_USER	Similar to SESSION_USER.
USER	Similar to SESSION_USER.
VERSION	Returns the current version of the MySQL server installed.

CONCLUSION

By going through this cheat sheet, you would have got a decent understanding/revision of MySQL. More than memorizing syntax, do pay attention to practising them and solving problems.

Additional Resources

MySQL Interview Questions

MySQL Commands: Full List With Examples

Top MySQL Features

MySQL Vs MongoDB

MySQL Vs PostgreSQL

MySQL vs SQL

INBUILT FUNCTIONS IN MySQL

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment