What are the commonly used MySQL functions?

Commonly used MySQL functions include mathematical functions, string functions, date and time functions, conditional judgment functions, system information functions, encryption functions, formatting functions and other functions. These functions can simplify user operations. .

Commonly used MySQL functions are:

1. Mathematical functions

(1)ABS(x)

Return the absolute value of x

(2)PI()

Returns pi, 6 decimal places are displayed by default

(3)SQRT(x)

Returns the square root of a non-negative number x

(4)MOD(x,y)

Return the remainder after x is divided by y

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

Returns the smallest integer that is not less than x

(6)FLOOR(x)

Returns the largest integer that is 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, its value will be retained to y digits after the decimal point. If y is a negative value, it will be retained from x to y digits to the left of the decimal point

(8)SIGN(x)

Returns the sign of parameter x, -1 represents a negative number, 0 represents 0, and 1 represents a positive number

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

Return the value of x raised to the power of y

(10)EXP(x)

Return The value of e raised to the power of

#（12）

LOG10(x)

Returns the base 10 logarithm of x

（13）

RADIANS(x)

Returns the value of x converted from angle to radian

(14)

DEGREES(x)

Returns the value of x converted from radian 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 value of x, 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 a given radian value x

2. String function

(1)

CHAR_LENGTH(str)

Calculate 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 for len characters

(5)

LOWER(str) and LCASE(str)

,

UPPER(str)

and

UCASE(str)the first two The first converts all the letters in str to lowercase, and the latter two converts 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, with the left side filled with string s2 to len characters Length, if the length of s1 is greater than len, the return value is shortened to len characters; the former returns s1, and the right side is padded to the length of len characters by the string s2. 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 the string s, with all spaces to the left deleted; the latter Returns the string s with all the spaces on the right side deleted

(9)TRIM(s)

Returns the string s with the spaces on both sides deleted

（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)

Return a string, replace 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 will be returned; according to the current classification order, if the first parameter is less than the second, -1 will be returned, otherwise 1 will be returned

(15)

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

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

(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

3. 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 effect and return the current date and time value in the format of "YYYY_MM -DD ​​HH:MM:SS" or "YYYYMMDDHHMMSS", the specific format depends on whether the function is used in a string or numeric context

(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. Seconds

(4)FROM_UNIXTIME(date)

and UNIX_TIMESTAMP are inverse functions of each other, converting UNIX timestamps into time in common 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", which 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), MONTHNAME(date)

The former returns the specified date The month in the specified date, 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 represents Sunday, 2 represents Monday; WEEKDAY(d) represents the working day corresponding to d Index, 0 means Monday, 1 means Tuesday

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

The former calculates the day d is in the year Week, the latter calculates the week number of a certain day in the year

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

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

(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 number of minutes corresponding to time, ranging from 0 to 59; SECOND(time) returns the seconds value of the specified time

(11)EXTRACE(type FROM date)

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

(12)TIME_TO_SEC(time)

Return to seconds time parameter, the conversion formula is "3600*hour 60*minute second"

(13)SEC_TO_TIME()

and TIME_TO_SEC(time) are inverse functions of each other. Convert seconds value to time format

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

Return to add the starting time The time after expr type, such as 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 after subtracting 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

4. 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 it matches all values If you don’t want to wait, return rn

after ELSE

5. System information function

(1) VERSION()

View the MySQL version number

(2)CONNECTION_ID()

View the number of connections of the current user

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

Check the combination of user name 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 string str

(5)COLLATION()

View the string arrangement

6. 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 in the authentication system of the MySQL server (2) MD5(str)

to calculate an MD5 128-bit checksum for the string. Change the value to Returns

as a binary string of 32 hexadecimal digits

(3)ENCODE(str, pswd_str)

Use pswd_str as password and 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

7. 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, represented by from_base Base conversion to to_base base

(3)INET_ATON(expr)

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

(4)INET_NTOA(expr)

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

(5)BENCHMARK(count,expr)

Repeats the expression expr count times, which can be used 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 the statement execution time inside the MySQL client

(6)CONVERT(str USING charset)

Use the character set charset to represent the string str

Related learning recommendations: mysql video tutorial

The above is the detailed content of What are the commonly used MySQL functions?. For more information, please follow other related articles on the PHP Chinese website!