MySQL functions overview _ MySQL functions summary, mysql_mysql

MySQL functions overview _ MySQL functions summary, mysql_mysql

Preface

MySQL provides many powerful and easy-to-use functions. Using these functions can greatly improve the efficiency of database management and flexibly meet the needs of different users. This article classifies and summarizes MySQL functions so that they can be viewed at any time for future use.

Mathematical functions

(1) ABS (x)

Returns the absolute value of x.

(2) PI ()

Returns the circumference rate π. The default value is 6 decimal places.

(3) SQRT (x)

Returns the quadratic root of x with a non-negative number.

(4) MOD (x, y)

Returns the remainder of x after division by y.

(5) CEIL (x) and CEILING (x)

Returns the smallest integer not less than x.

(6) FLOOR (x)

Returns the maximum integer not greater than x.

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

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

(8) SIGN (x)

Returns the x symbol.-1 indicates a negative number, and 0 indicates 0 and 1 indicates a positive number.

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

Returns the value of x's y multiplication.

(10) EXP (x)

Returns the value of x after multiplication of e.

(11) LOG (x)

Returns the natural logarithm of x, which is 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 radian.

(14) DEGREES (x)

Returns the value of x from radians to degrees.

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

The former returns the sine of x, where x is the given radian value; the latter returns the arc sine of x, which is the sine of x.

(16) COS (x) and ACOS (x)

The former returns the cosine of x, where x is the given radian value; the latter returns the arc 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, and x is the tangent of x.

(18) COT (x)

Returns the cotangent of the given radian value x.

 

String Functions

(1) CHAR_LENGTH (str)

Returns the number of string characters.

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

Returns the string produced by the connection parameter. One or more items to be spliced are returned. If any one is NULL, the return value is NULL.

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

Returns the concatenated string of multiple strings. Each string has an x

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

Returns string s1. Its substring starts from position x and replaces len with string s2.

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

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

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

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

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

The former returns s1, and the left side is filled by string s2 to len. If s1 is longer than len, the return value is shortened to len. The former returns s1, on the right side of the string is filled by string s2 to len Length. If s1 is longer than len, the return value is shortened to len.

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

The former returns string s, and all spaces on the left are deleted. The latter returns string s, and all spaces on the right are deleted.

(9) TRIM (s)

Returns the string s. The string with spaces on both sides is deleted.

(10) TRIM (s1 FROM s)

Delete all substrings s1 at both ends of string s. If no s1 is specified, spaces are 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 that replaces all strings s1 in string s with string s2.

(14) STRCMP (s1, s2)

If all strings in s1 and s2 are the same, 0 is returned. Based on the current classification order, if the first parameter is smaller than the second,-1 is returned. Otherwise, 1 is returned.

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

The two functions share the same purpose. From string s, return a string with the nth character starting with len.

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

Returns the starting position of the substring str1 In the str string (starting from the nth character)

(17) REVERSE (s)

Reverse string s

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

Returns the nth string.

 

Date and Time Functions

(1) CURDATE (), CURRENT_DATE ()

Returns the value of the current date in the format of "YYYY-MM-DD" or "YYYYMMDD", depending on the format used by the function in a string or numeric Context

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

The four functions work the same 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 the function used in the string or number context.

(3) UNIX_TIMESTAMP (), UNIX_TIMESTAMP (date)

The former returns the number of seconds from 00:00:00 to the present, and the latter returns the number of seconds from 00:00:00 to the specified time.

(4) FROM_UNIXTIME (date)

And UNIX_TIMESTAMP are reciprocal functions, which convert UNIX timestamp to normal format time

(5) UTC_DATE () and UTC_TIME ()

The former returns the current UTC (World Standard Time) date value in the format of "YYYY-MM-DD" or "YYYYMMDD", and the latter returns the current UTC time value, the format is "YYYY-MM-DD" or "YYYYMMDD ". Which of the following depends on whether the function is used in a string or a digital 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 business day corresponding to d, such as Sunday and Monday. DAYOFWEEK (d) returns the index of the corresponding week. 1 indicates Sunday and 2 indicates Monday; WEEKDAY (d) indicates the index of the business day corresponding to d, 0 indicates Monday, 1 indicates Tuesday

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

The former calculates the week d in the year, and the latter calculates the week d in the year.

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

The former returns the day of the year, and the latter returns the day of the year.

(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 QUARTER of the year corresponding to date, range: 1 ~ 4; MINUTE (time) returns the number of minutes corresponding to time, ranging from 0 ~ 59; SECOND (time) returns the SECOND value of the specified time

(11) EXTRACE (type FROM date)

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

(12) TIME_TO_SEC (time)

Returns the time parameter converted to seconds. The conversion formula is "3600 * hours + 60 * minutes + seconds"

(13) SEC_TO_TIME ()

And TIME_TO_SEC (time) are reciprocal functions, convert the second value to the time Format

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

Returns the time after the start time is added with the expr type. For example, DATE_ADD ('2017-12-31 23:59:59 ', INTERVAL 1 SECOND) indicates that the first time is added to 1 SECOND.

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

Returns the start time minus the expr type.

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

The former performs the date time plus operation, and the latter performs the date time minus operation.

 

Conditional judgment Function

(1) IF (expr, v1, v2)

If expr is TRUE, v1 is returned; otherwise, v2 is returned.

(2) IFNULL (v1, v2)

If v1 is not NULL, v1 is returned; otherwise, v2 is returned.

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

If expr is equal to a specific vn, the result after the corresponding location THEN is returned. If it is not equal to all values, the rn following ELSE is returned.

 

System information functions

(1) VERSION ()

View MySQL version

(2) CONNECTION_ID ()

View the number of connections of the current user

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

Check the combination of the username and host 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 str string

(5) COLLATION ()

View the string Arrangement

 

Encryption Functions

(1) PASSWORD (str)

Calculate from the original plaintext password str and return the encrypted string password. Note that the encryption of this function is unidirectional (irreversible ), therefore, it should not be applied to personal applications, but should be used only in the authentication system of the MySQL server.

(2) MD5 (str)

Calculate an MD5 128-bit checksum for the string. The value is returned in the format of a 32-bit hexadecimal number.

(3) ENCODE (str, pswd_str)

Use pswd_str as the password to 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 the last n digits of the decimal point. The result is returned as a string.

(2) CONV (N, from_base, to_base)

Conversion between different hexadecimal numbers. The return value is a string representation of the value N, which is converted from from_base to to_base.

(3) INET_ATON (expr)

Returns an integer representing the network address of a string. The address can be 4 or 8 bits.

(4) INET_NTOA (expr)

Given a digital network address (4 or 8 bits), return the point address representation of the address as a string.

(5) BENCHMARK (count, expr)

Repeat the count expression expr, which can be used to calculate the speed of the MySQL processing expression. The result value is usually 0 (0 only indicates that it is fast, not no speed ). Another function is to report the statement execution time in the MySQL client.

(6) CONVERT (str USING charset)

Use character set charset to represent the str string

The above MySQL function overview _ MySQL function summary is all the content shared by the editor. I hope to give you a reference, and I hope you can provide more support to the customer's house.