List of MySQL functions_Summary of all MySQL functions_MySQL

Foreword

MySQL provides many powerful, convenient and easy-to-use functions. Using these functions can greatly improve users' database management efficiency, thereby more flexibly meeting the needs of different users. This article classifies and summarizes MySQL functions so that you can view them at any time when used in the future.

Mathematical functions

(1)ABS(x)

Returns the absolute value of x

(2)PI()

Returns pi, pi, with 6 decimal places displayed by default

(3) SQRT(x)

Returns the square root of a non-negative number x

(4) MOD(x,y)

Returns the remainder after x is divided by y

(5) CEIL(x), CEILING(x)

Return the smallest integer not less than x

(6)FLOOR(x)

Returns the largest integer not greater than x

(7) ROUND(x), ROUND(x,y)

The former returns the integer closest to x, that is, rounding x; the latter returns the number closest to x, and its value is retained to y places after the decimal point. If y is a negative value, it will be retained to x to the y place to the left of the decimal point

(8)SIGN(x)

Returns the sign of parameter x, -1 means negative number, 0 means 0, 1 means positive number

(9) POW(x,y) and POWER(x,y)

Returns the value of x raised to the power of y

(10) EXP(x)

Returns the value of e raised to the power of x

(11)LOG(x)

Returns the natural logarithm of x, the logarithm of x relative to base e

(12)LOG10(x)

Returns the base 10 logarithm of x

(13)RADIANS(x)

Returns the value of x converted from angle to radians

(14) DEGREES(x)

Returns the value of x converted from radians to angle

(15) SIN(x), ASIN(x)

The former returns the sine of x, where x is the given radian value; the latter returns the arcsine of x, where x is the sine

(16) COS(x), ACOS(x)

The former returns the cosine of x, where x is the given radian value; the latter returns the inverse cosine of x, where x is the cosine

(17) TAN(x), ATAN(x)

The former returns the tangent of x, where x is the given radian value; the latter returns the arc tangent of x, where x is the tangent

(18) COT(x)

Returns the cotangent of the given radians value x

String functions

(1)CHAR_LENGTH(str)

Count the number of characters in a string

(2) CONCAT(s1,s2,...)

Returns the string generated by the connection parameters, one or more contents to be spliced, if any one is NULL, the return value is NULL

(3) CONCAT_WS(x,s1,s2,...)

Returns the string after concatenating multiple strings, with an x

(4) INSERT(s1,x,len,s2)

Returns string s1 whose substring starts at position x and is replaced by string s2 len characters

(5) LOWER(str) and LCASE(str), UPPER(str) and UCASE(str)

The first two convert all the letters in str to lowercase, and the latter two convert all the letters in the string to uppercase

(6) LEFT(s,n), RIGHT(s,n)

The former returns n characters starting from the leftmost side of string s, and the latter returns n characters starting from the rightmost side of string s

(7) LPAD(s1,len,s2), RPAD(s1,len,s2)

The former returns s1, the left side of which is filled with the string s2 to the length of len characters. If the length of s1 is greater than len, the return value is shortened to len characters; the former returns s1, the right side of which is filled with the string s2 to the length of len characters. len character length. If the length of s1 is greater than len, the return value is shortened to len characters

(8) LTRIM(s), RTRIM(s)

The former returns a string s with all spaces on the left deleted; the latter returns a string s with all spaces on the right deleted

(9) TRIM(s)

Returns the string s with the spaces on both sides removed

(10) TRIM(s1 FROM s)

Delete all substrings s1 at both ends of string s. If s1 is not specified, spaces will be deleted by default

(11) REPEAT(s,n)

Returns a string consisting of repeated string s, the number of string s is equal to n

（12）SPACE(n)

Returns a string consisting of n spaces

(13)REPLACE(s,s1,s2)

Returns a string, replacing all strings s1 in string s with string s2

(14) STRCMP(s1,s2)

If all the strings in s1 and s2 are the same, 0 is returned; according to the current classification order, if the first parameter is less than the second, -1 is returned, and 1 is returned in other cases

(15) SUBSTRING(s,n,len), MID(s,n,len)

The two functions have the same effect, returning a string starting from the nth character and having a length of len from the string s

(16) LOCATE(str1,str), POSITION(str1 IN str), INSTR(str,str1)

The three functions have the same effect, returning the starting position of substring str1 in the string str (from which character it starts)

（17）REVERSE(s)

Reverse the string s

(18)ELT(N,str1,str2,str3,str4,...)

Return the Nth string

Date and time functions

(1) CURDATE(), CURRENT_DATE()

Return the current date in the format of "YYYY-MM-DD" or "YYYYMMDD". The specific format depends on whether the function is used in a string or numeric context

(2) CURRENT_TIMESTAMP(), LOCALTIME(), NOW(), SYSDATE()

These four functions have the same function, returning the current date and time value in the format of "YYYY_MM-DD HH:MM:SS" or "YYYYMMDDHHMMSS". The specific format is used in a string or numeric context depending on the function. It depends

(3) UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

The former returns the number of seconds from 1970-01-01 00:00:00 GMT to the present, and the latter returns the number of seconds from 1970-01-01 00:00:00 GMT to the specified time. Number

(4) FROM_UNIXTIME(date)

and UNIX_TIMESTAMP are inverse functions of each other, converting UNIX timestamps into time in ordinary format

(5) UTC_DATE() and UTC_TIME()

The former returns the current UTC (Universal Standard Time) date value in the format of "YYYY-MM-DD" or "YYYYMMDD", and the latter returns the current UTC time value in the format of "YYYY-MM-DD" " or "YYYYMMDD". Which one to use depends on whether the function is used in a string or numeric context

(6) MONTH(date) and MONTHNAME(date)

The former returns the month in the specified date, and the latter returns the name of the month in the specified date

(7) DAYNAME(d), DAYOFWEEK(d), WEEKDAY(d)

DAYNAME(d) returns the English name of the working day corresponding to d, such as Sunday, Monday, etc.; DAYOFWEEK(d) returns the index corresponding to the week, 1 means Sunday, 2 means Monday; WEEKDAY(d) ) represents the working day index corresponding to d, 0 represents Monday, 1 represents Tuesday

(8) WEEK(d), WEEKOFYEAD(d)

The former calculates the week number of the year when date d is, and the latter calculates the week number of a day in the year

(9) DAYOFYEAR(d), DAYOFMONTH(d)

The former returns the day of the year when d is, and the latter returns the day of the month when d is

(10) YEAR(date), QUARTER(date), MINUTE(time), SECOND(time)

YEAR(date) returns the year corresponding to the specified date, the range is 1970~2069; QUARTER(date) returns the date corresponding to the quarter of the year, the range is 1~4; MINUTE(time) returns the minutes corresponding to the time, The range is 0~59; SECOND(time) returns the seconds value of the specified time

（11）EXTRACE(type FROM date)

Extract part of the date, type can be YEAR, YEAR_MONTH, DAY_HOUR, DAY_MICROSECOND, DAY_MINUTE, DAY_SECOND

(12)TIME_TO_SEC(time)

Returns the time parameter converted to seconds. The conversion formula is "3600*hour 60*minute second"

(13) SEC_TO_TIME()

and TIME_TO_SEC(time) are inverse functions of each other, converting seconds value into time format

(14) DATE_ADD(date,INTERVAL expr type), ADD_DATE(date,INTERVAL expr type)

Returns the time after adding the start time to expr type. For example, DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) means adding 1 second to the first time

(15) DATE_SUB(date,INTERVAL expr type),SUBDATE(date,INTERVAL expr type)

Returns the time minus expr type from the starting time

(16)ADDTIME(date,expr),SUBTIME(date,expr)

The former performs the time addition operation on date, and the latter performs the time subtraction operation on date

Conditional judgment function

(1)IF(expr,v1,v2)

If expr is TRUE, return v1, otherwise return v2

(2)IFNULL(v1,v2)

If v1 is not NULL, return v1, otherwise return v2

(3)CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

If expr is equal to a certain vn, return the result after the corresponding position THEN. If you don’t want to wait for all values, return rn after ELSE

System information function

(1)VERSION()

View MySQL version number

(2) CONNECTION_ID()

View the number of connections for the current user

(3) USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER()

View the combination of username and host currently verified by the MySQL server. Generally, the return values ​​​​of these functions are the same

(4)CHARSET(str)

View the character set used by the string str

(5) COLLATION()

View string arrangement

Encryption function

(1)PASSWORD(str)

Calculate and return the encrypted string password from the original plaintext password str. Note that the encryption of this function is one-way (irreversible), so it should not be used in personal applications. Should only be used within the MySQL server's authentication system

(2) MD5(str)

Calculate an MD5 128-bit checksum for the string, and return the value as a binary string of 32 hexadecimal digits

(3)ENCODE(str, pswd_str)

Use pswd_str as password, encrypt str

(4)DECODE(crypt_str,pswd_str)

Use pswd_str as the password to decrypt the encrypted string crypt_str. crypt_str is the string returned by the ENCODE function

Other functions

(1)FORMAT(x,n)

Format the number x and round it to n decimal places, and return the result as a string

(2) CONV(N,from_base,to_base)

Conversion between different base numbers, the return value is a string representation of the value N, converted from from_base base to to_base base

(3)INET_ATON(expr)

gives a dot address representation of a network address as a string, returns an integer representing the value of the address, the address can be 4 or 8 bits

(4)INET_NTOA(expr)

Given a numeric network address (4 or 8 bits), return the dot address representation of that address as a string

(5) BENCHMARK(count,expr)

Repeat the expression expr count times, which can be used to calculate the speed of MySQL processing expressions. The result value is usually 0 (0 just means fast, not no speed). Another function is to use it to report statement execution time inside the MySQL client

(6) CONVERT(str USING charset)

Use the character set charset to represent the string str

The above list of MySQL functions_A summary of all MySQL functions is all the content shared by the editor. I hope it can give you a reference, and I hope you will support me a lot.