MySQL Function summary

Objective

MySQL provides a number of powerful, easy-to-use functions, using these functions, can greatly improve the user's management efficiency of the database, thus more flexible to meet the needs of different users. This article classifies and summarizes MySQL's functions so that they can be viewed at any time when they are used later.

Mathematical functions

(1) ABS (x)

Returns the absolute value of X

(2) PI ()

Returns PI, 6 decimal places by default

(3) SQRT (x)

Returns the two root of a non-negative x

(4) MOD (x, y)

Returns the remainder after x is removed by y

(5) Ceil (x), CEILING (x)

Returns the smallest integer not less than X

(6) floor (x)

Returns the largest integer less than x

(7) ROUND, ROUND (x, y)

The former returns the integer closest to X, that is, rounding X, which returns the number closest to X, whose value remains at the Y-bit after the decimal point, and if Y is negative, the X to the left of the decimal point

(8) sign (x)

Returns the symbol for parameter x, 1 for negative numbers, 0 for 0,1 for positive numbers

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

Returns the value of the Y-exponent of X

(Ten) EXP (x)

Returns the value after the X-exponentiation of E

(one) LOG (x)

Returns the natural logarithm of x, the logarithm of x relative to the cardinality E

(LOG10) (x)

Returns the logarithm of the cardinality of X 10

(+) RADIANS (x)

Returns the value of x converted from angle to radians

(+) DEGREES (x)

Returns the value of x converted from radians to angles

() SIN (x), ASIN (x)

The former returns the sine of x, where x is the given radian value, the latter returns the inverse sine of x, and X is sinusoidal

(+) 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, and X is the cosine

(+) TAN (x), ATAN (x)

The former returns the tangent of x, where x is the given radian value, the latter returns the inverse tangent of x, and X is the tangent

COT (x)

Returns the cotangent of the given Radian value x

String functions

(1) char_length (str)

Count the number of string characters

(2) CONCAT (S1,S2, ...)

Returns the string that is generated by the connection parameter, one or more content to be stitched, or null for any one of the returned values

(3) Concat_ws (X,s1,s2,...)

Returns the string after concatenation of multiple strings, with an x between each string

(4) INSERT (S1,X,LEN,S2)

Returns a string S1 whose substring starts at position x and is replaced by the 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 two translate all the letters in the string to uppercase.

(6) Left (s,n), right (S,n)

The former returns the n characters from the leftmost string s, which returns the n characters from the rightmost start of the string s

(7) Lpad (S1,LEN,S2), Rpad (S1,LEN,S2)

The former returns S1, whose left side is filled by the string S2 to Len character length, and if the length of the S1 is greater than Len, the return value is shortened to the Len character, the former returns S1, the right side is filled by the string S2 to the Len character length, and if the S1 is longer than Len, the return value is shortened to the Len character

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

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

(9) TRIM (s)

Returns string S string that has been removed from both spaces

TRIM (S1 from S)

Delete string s both ends of all substrings S1, without specifying S1, the default is to remove spaces

(one) REPEAT (s,n)

Returns a string consisting of a repeating string s, and the number of S is equal to n

SPACE (N)

Returns a string consisting of n spaces

(+) REPLACE (S,S1,S2)

Returns a string that replaces all strings in the string s with the string S2 s1

(+) STRCMP (S1,S2)

Returns 0 if all strings in the S1 and S2 are the same, returns 1 if the first argument is less than the second, according to the current sorting order, and returns 1 in other cases

(SUBSTRING) (S,n,len), MID (S,n,len)

Two functions are the same, returning from the string s an nth character starting with a string of Len length

(+) LOCATE (STR1,STR), POSITION (str1 in str), INSTR (STR,STR1)

Three functions are the same, return substring str1 at the beginning of the string str (starting from the first character)

(+) REVERSE (s)

Reverses the string s

ELT (N,STR1,STR2,STR3,STR4,...)

Returns the nth string

Date and Time functions

(1) curdate (), Current_date ()

Returns the current date as a value in the format "YYYY-MM-DD" or "YYYYMMDD", depending on whether the function is used in a string or in a digital context

(2) Current_timestamp (), localtime (), now (), Sysdate ()

The four functions are the same, returning the current date and time values in the format "Yyyy_mm-dd HH:MM:SS" or "YYYYMMDDHHMMSS", depending on the function used in the string or digital context

(3) Unix_timestamp (), Unix_timestamp (date)

The former returns a Greenwich mean time of 1970-01-01 00:00:00 to the present number of seconds, which returns a Greenwich mean time of 1970-01-01 00:00:00 to a specified time in seconds

(4) from_unixtime (date)

and Unix_timestamp each other as inverse functions, converting UNIX timestamps to normal format time

(5) Utc_date () and Utc_time ()

The former returns the current UTC (World standard Time) date value in the format "YYYY-MM-DD" or "YYYYMMDD", which returns the current UTC time value in the format "YYYY-MM-DD" or "YYYYMMDD". Depending on whether the function is used in a string or in a digital context

(6) MONTH (date) and MonthName (date)

The former returns the month in the specified date, which 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 corresponding week index, 1 for Sunday, 2 for Monday, and WEEKDAY (d) for the working day index for D, 0 for Monday, and 1 for Tuesday

(8) WEEK (d), Weekofyead (d)

The former calculates that the date D is the week ordinal of a year, and the latter calculates the day of the week in the year

(9) DayOfYear (d), DayOfMonth (d)

The former returns D is the day of the year, the latter returning D is the day of the January

(date), QUARTER (date), MINUTE (Time), SECOND (time)

Year (date) returns the years of the specified date, the range is 1970~2069;quarter (date) returns the quarter of the year that corresponds to date, and the range is the number of minutes that 1~4;minute (time) returns time, and the range is 0~59;second ( Time) returns the seconds value of the setting

(one) Extrace (type from date)

Extract part from date, type can be year, Year_month, Day_hour, Day_microsecond, Day_minute, Day_second

(TIME_TO_SEC) (time)

Returns the time parameter converted to seconds, with the conversion formula "3600* hours + 60* minutes + seconds"

(+) Sec_to_time ()

and Time_to_sec (time) are reciprocal functions, converting the second value to the temporal format

Date_add (Date,interval expr type), add_date (Date,interval expr type)

Returns the time after the start time is added to the expr type, such as Date_add (' 2010-12-31 23:59:59 ', INTERVAL 1 SECOND) that represents the first time plus 1 seconds

Date_sub (Date,interval expr type), subdate (Date,interval expr type)

Returns the time after the start time minus the expr type

(+) Addtime (date,expr), Subtime (date,expr)

The former takes the time of date plus operation, the latter for date reduction operation

Conditional Judgment function

(1) IF (EXPR,V1,V2)

Returns v1 If expr is true, otherwise returns v2

(2) ifnull (V1,V2)

Returns v1 If V1 is not NULL, otherwise returns v2

(3) Case expr-V1 then R1 [when V2 then v2] [ELSE RN] END

If expr is equal to a VN, returns the result after the corresponding position and then returns the RN following the else if it does not want to wait with all values

System Information functions

(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 current user name and host combination verified by the MySQL server, typically the return values of these functions are the same

(4) CHARSET (str)

To view the character set used by the string str

(5) COLLATION ()

See how strings are arranged

Cryptographic functions

(1) PASSWORD (str)

The encrypted string password is computed and returned from the original plaintext password STR, noting that the function's encryption is unidirectional (irreversible), so it should not be applied in a personal application but should only be used in the MySQL server's authentication system

(2) MD5 (str)

A MD5 128-bit checksum is calculated for the string, and the value is returned as a binary string of 32-bit hexadecimal digits

(3) ENCODE (str, PSWD_STR)

Encrypt str using PSWD_STR as the password

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

Formats the number x and retains the n digits after the decimal point in a rounded form, and returns the result as a string

(2) CONV (n,from_base,to_base)

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

(3) Inet_aton (expr)

Gives a point address of a network address as a string, returns an integer representing the value of the address, which can make 4 or 8 bits

(4) Inet_ntoa (expr)

Given a digital network address (4 or 8 bits), returns the point address of the address as a string representing

(5) BENCHMARK (count,expr)

Repeats the count of the expression expr, which can be used to calculate the speed of the MySQL processing expression, the resulting value is usually 0 (0 is a quick, not no speed). Another function is to use it to report the time of the statement execution within the MySQL client

(6) CONVERT (str USING charset)

Use character set CharSet to represent string str

Summary of MySQL functions