Mysql Function Overview _mysql function full Summary _mysql

Objective

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

Mathematical functions

(1) ABS (x)

Returns the absolute value of X

(2) PI ()

Returns PI Pi, showing 6 decimal places by default

(3) SQRT (x)

Returns the two square 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 not greater than X

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

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

(8) SIGN (x)

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

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

Returns the value of the Y-exponentiation of X

(a) EXP (x)

Returns the value after X of E

(one) LOG (x)

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

(a) LOG10 (x)

Returns the logarithm of x with a base of 10

(a) RADIANS (x)

Returns the value that x converts from an angle to radians

(a) DEGREES (x)

Returns the value of x converted from radians to angles

(a) SIN (x), ASIN (x)

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

() COS (x), ACOS (x)

The former returns the cosine of x, where x is the given Radian value, and the latter returns the inverse cosine of x, which is the cosine of x.

(a) TAN (x), Atan (x)

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

(km) 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 produced by the connection parameter, one or more content to be spliced, or null for any one of the values returned

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

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

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

Returns the string S1 whose substring starts at position x and is replaced by the string S2 len character

(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 converts all the letters in the string to uppercase

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

The former returns the n characters from the leftmost start of the 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 to S1, the left side is filled by the string S2 to Len character length, if the length of the S1 is greater than Len, the return value is shortened to the Len character; the former returns to S1, the right-hand side of which is filled by the string S2 to Len character length, if the length of the S1 is greater than Len, the return

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

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

(9) TRIM (s)

Return string s The string after the two spaces have been deleted

(a) TRIM (S1 from S)

Delete all substring S1 at both ends of string s, delete whitespace by default if S1 is not specified

(one) REPEAT (s,n)

Returns a string consisting of a repeating string s, with the number of strings s equal to n

() space (n)

Returns a string consisting of n spaces

(a) REPLACE (S,S1,S2)

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

(STRCMP) (S1,S2)

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

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

Two functions the same function, from the string s to return an nth character start, Len length of the string

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

Three functions are the same, returns the substring str1 the start position in string str (starting with the first few characters)

(a) REVERSE (s)

Reverses the string s

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

Returns the nth string

Date and Time functions

(1) curdate (), Current_date ()

Returns the current date in "Yyyy-mm-dd" or "YYYYMMDD" format, depending on the function used in a string or in a numeric context

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

These four functions function the same, returning the current date and time value, in the form "Yyyy_mm-dd HH:MM:SS" or "YYYYMMDDHHMMSS", depending on the function used in a string or a numeric context

(3) Unix_timestamp (), Unix_timestamp (date)

The former returns a Greenwich Mean Time 1970-01-01 00:00:00 to the current number of seconds, the latter returning a Greenwich Mean Time 1970-01-01 00:00:00 to a specified time of seconds

(4) from_unixtime (date)

and unix_timestamp reciprocal functions, converting the UNIX timestamp to the normal format of 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 form "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 names of the working days corresponding to D, such as Sunday, Monday, etc. dayofweek (d) the index of the corresponding week returned, 1 for Sunday, 2 for Monday; weekday (d) represents the weekday index for D, 0 for Monday, and 1 for Tuesday

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

The former calculates the date D is the first week of the year, and the latter calculates the week ordinal of the year

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

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

(a) year (date), quarter (date), MINUTE (Time), SECOND (time)

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

(a) Extrace (type from date)

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

() Time_to_sec (time)

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

(a) Sec_to_time ()

and Time_to_sec (time) are inverse functions, converting the second value to the date format

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

Returns the time after the start time plus the expr type, such as Date_add (' 2010-12-31 23:59:59 ', INTERVAL 1 SECOND), which means adding 1 seconds to the first time.

(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 carries on date time plus operations, which carry out date time subtraction operations

Conditional Judgment function

(1) IF (EXPR,V1,V2)

Returns v1 If expr is true, otherwise returns v2

(2) ifnull (V1,V2)

Returns v1 if the V1 is not NULL, or returns v2

(3) Case expr when V1 THEN R1 [when v2 THEN v2] [ELSE RN] End

If expr equals a vn, return the result of the corresponding position then, and if you do not want to wait for all values, return the RN after else

System Information functions

(1) VERSION ()

View the 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 combination of user names and hosts that are validated by the MySQL server, and the return values of these functions are the same

(4) CHARSET (str)

View character set used by string str

(5) Collation ()

View string Alignment

Cryptographic functions

(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 applied to a personal application and should only be used in the MySQL server's authentication system

(2) MD5 (str)

Calculates a MD5 128-bit checksum for a string, which is returned as a binary string of 32-bit hexadecimal digits

(3) ENCODE (str, PSWD_STR)

Use PSWD_STR as a password to encrypt str

(4) DECODE (CRYPT_STR,PSWD_STR)

Use PSWD_STR as a 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 preserves the decimal point by rounding, returning the result as a string

(2) CONV (n,from_base,to_base)

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

(3) Inet_aton (expr)

Gives a point address representation of a network address as a string that returns an integer representing the numeric value of the address that can be used to make 4 or 8 bits

(4) Inet_ntoa (expr)

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

(5) BENCHMARK (count,expr)

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

(6) CONVERT (str USING charset)

Using character set CharSet to represent string str

The above MySQL function list _mysql function All summary is small series share to everybody's content, hoped can give everybody a reference, also hoped that everybody supports the cloud habitat community.