My MySQL Learning notes (iv)--functions

Numeric Functions
1, the remainder function mod (x, y)

MoD (x, y) returns the remainder after X is removed by Y, and the MoD () also works for numbers with fractional parts, returning the exact remainder of the division operation.

2. Function truncate (x, y)

TRUNCATE (x, y) returns the number X that is removed to the Y-digit after the decimal point. If Y has a value of 0, the result does not have a decimal point or a fractional part.

If Y is set to a negative number, then the value of all lows after the beginning of the first Y-position of the decimal point is truncated (zeroed).

The Tips:round (x, y) function is rounded when the value is intercepted, and truncate (x, y) intercepts the value directly, not rounding
3, HEX (N)
Convert N to hex and return with string (n is bigint number, equivalent to Conv
(n,10,16))

Unhex is the inverse function of hex

character Functions
1, the Char_length (str) return value is the number of characters that the string STR contains. A multibyte character counts as a single character

2. Combine string function Concat_ws (x,s1,s2,...... ）

Concat_ws (X,s1,s2,...... ), Concat_ws represents CONCAT with Separator, which is a special form of the CONCAT () function.

The first parameter, X, is the delimiter for the other parameters, where the delimiter position is placed between the two strings to be concatenated. The delimiter can be a string, or it can be a

Other parameters. If the delimiter is null, the result is null. The function ignores null values after any delimiter parameters.

3. INSERT (STR,POS,LEN,NEWSTR)
Returns a string str, a substring starting at position pos, and Len characters long substrings are replaced by string newstr

4. Function Lpad (S1,LEN,S2) and Rpad (S1,LEN,S2) filled with strings

Lpad (S1,LEN,S2) returns the string S1, whose left side is filled by the string S2 to the Len string length. If the length of the S1 is greater than Len, the return value is shortened to the Len character.


Notice the difference between the two pictures above
5. Function Trim (S) for removing spaces
TRIM (s) remove spaces on both sides of the string s

6. TRIM (S1 from S) deletes all substrings in the string S S1. S1 is optional and, if unspecified, removes the space. This function removes the corresponding matching characters at both ends of the string, and the response characters appearing in the middle of the string are not removed.

7. Function repeat (s,n) for repeating string generation

8. Function strcmp to compare string size (S1,S2)
STRCMP (S1,S2) returns 0 if all strings are the same, returns 1 if the first argument is less than the second based on the current sorting order, and returns 1 in other cases

9. function matching substring start position

LOCATE (STR1,STR), POSITION (STR1 in STR), INSTR (STR,STR1) 3 functions are the same, return substring str1 start position in string STR

10. Function that returns the string at the specified position

ELT (N, String 1, String 2, String 3,...,), if n=1, the return value is String 1, if n=2, the return value is String 2, and so on. Specifies that the return string position exceeds the number of arguments and returns null

11. Function that returns the position of the specified string field (S,s1,s2,...)

FIELD (S,s1,s2,...) Return string s in List S1,s2, ... The first occurrence of the position, in the case where S is not found, the return value is 0.

If S is null, the return value is 0, because null cannot be compared equally with any value.

12. function Find_in_set returning substring position (S1,S2)
Find_in_set (S1,S2) returns the position of the string S1 in the string list s2, a list of strings consisting of multiple comma ', ' separated strings. If S1 is not S2 or S2 is an empty string, the return value is 0. If either parameter is NULL, the return value is null. This function will not work correctly when the first parameter contains a comma ', '.

13. Function Make_set (x,s1,s2,...) with string selection

Make_set (X,s1,s2,...) Returns a string consisting of a string of the corresponding bit specified by the binary number of x, s1 corresponding bit 1,s2 corresponds to bit 01 and so on.

A binary value of 1 with binary values of 0001,4 is 0100,1 with a binary value of 0101 after XOR with 4, and a right-to-left first and third bit is 1.

Make_set (1, ' A ', ' B ', ' C ') returns the first string

Make_set (1|4, ' hello ', ' nice ', ' world ') returns a string consisting of the first and third strings starting from the left end

Date and Time functions
1, get the current date of the function and get the current time of the function
Curdate (), Current_date (), Current_timestamp (), localtime (), now (), Sysdate (), Utc_time (), Utc_timestamp ()

2. Get the Month function MonthName (DATE)

The MONTHNAME (date) function returns the full English name of the month for the date

3. Get quarterly, minute, and second functions

QUARTER (date) returns the quarter value of the year corresponding to date, ranging from 1~4

Use the quarter (date) function to return the quarter of a specified date

MINUTE (time) returns the number of minutes corresponding to the time, ranging from 0~59

SECOND (time) returns the number of seconds corresponding to time, ranging from 0~59

4. Function extract (type from date) for the specified value of the date

EXTRACT (type from date) This function is the same as the DATEPART () function in SQL Server

5. Functions for time and seconds conversion
Time_to_sec (time) returns the time parameter that has been converted to seconds, with the conversion formula: hours *3600+ minutes *60+ seconds
The Sec_to_time (second) function can convert seconds to second parameter values for hours, minutes, and seconds

6, calculate the date and time function

MySQL has more functions for calculating date and time

Added on: Date_add (Date,interval expr type), adddate (Date,interval expr type) Note interval keyword

Minus Date: date_sub (Date,interval expr type), subdate (Date,interval expr type)

Add Time: Add_time (date,expr)

Minus time: Subtime (date,expr)

Date difference: DATEDIFF ()
The function returns the number of days between two dates.

Time difference: Timediff (time1,time2): Two date subtraction time1-time2, return time difference value

Date format: Date_format (Date,format)
Date format output parameters

Conditional Judgment function

The conditional judgment function is also called the Process Control function, and the corresponding process is executed according to the satisfying conditions.

The function of conditional judgment in MySQL has the IF, ifnull, case
1. IF (EXPR,V1,V2) function

if (expr,v1,v2) if the expression expr is true (expr<>0 and Expr<>null), then the return value of if () is V1;

Otherwise the return value is v2. The return value of IF () is a numeric value or a string value, depending on the context in which it is located

TIPS: If only one of the V1 or v2 is explicitly null, the result type of the if () function is the result type of a non-null expression.

2, ifnull (V1,V2) function
Ifnull (V1,V2) If v1 is not NULL, the return value of Ifnull (V1,V2) is V1, otherwise its return value is v2.
The return value of Ifnull () is either a number or a string, depending on the context

3. Case function

Note: The default return value type for a case expression is a compatible collection type for any return value, but depends on the context in which it is located.

If the string context is used, the result is returned as a string. If used in a digital context, returns the result as a decimal, real value, or integer value

System Information functions
1. Get MySQL version number, connection number and database name function
Version () returns a String indicating the version of the MySQL server. This string uses the UTF8 character set

2, connection_id () returns the number of times the MySQL server is currently connected, each with its own unique ID
View the number of connections for the current user

3, SHOW processlist;

SHOW full processlist;
The output of the Processlist command shows which threads are running, not only the current number of connections, but also the current connection status.
Help identify problematic query statements, and so on.
4. The database () and schema () functions return the default (current) name of the databases using the UTF8 character set
5, get the user name of the function
USER (), Current_User (), Current_User, System_user (), Session_user ()
These functions return the user name and host name combination that are currently validated by the MySQL server. This value matches the MySQL account that determines the access rights of the currently logged-on user.
6, CHARSET (str) returns the character set of the string STR argument

7. A function to get the last auto-generated ID value
LAST_INSERT_ID () automatically returns the value of the first occurrence of the last INSERT or update set for the Auto_increment column
Note: When inserting multiple values at once, the value returned by the function has an error

Cryptographic functions
1. Cryptographic function password (STR)

PASSWORD (str) computes and returns the encrypted password string from the source code STR, returning NULL when the argument is null

MySQL saves the password function encrypted password to the User Rights table
The Tips:passwor () function is used in the authentication system of the MySQL server; it should not be used in personal applications, PASSWORD () function encryption is unidirectional (irreversible)
Password password encryption differs from the password encryption method in Unix
2. Cryptographic function MD5 (str)
MD5 (str) calculates a MD5 128-bit checksum for a string. This value is returned as a binary string of 32-bit hexadecimal digits, and null if the argument is null
3. Cryptographic function encode (STR,PSWD_STR)
ENCODE (STR,PSWD_STR) uses PSWD_STR as the password to encrypt str. Using decode () to decrypt the result, the result is a binary string with the same length as the Str
4, decryption function decode (CRYPT_STR,PSWD_STR)
DECODE (CRYPT_STR,PSWD_STR) uses PSWD_STR as the password to decrypt the encrypted string CRYPT_STR,CRYPT_STR is the string returned by the encode ()

Other functions
1. Formatted function format (x,n)
Format (X,N) formats the number x and retains the N-bit after the decimal point in rounding, and the result is returned as a string.
If n is 0, the returned result function does not contain a decimal part

2. The function of the number conversion of the different binary
The CONV (n,from_base,to_base) function converts between different binary numbers.

CONV (' A ', 16,2) converts the hexadecimal A to a numeric value in binary notation.
3, the function of the IP address and the number mutual conversion
Inet_aton (expr) gives a point-address representation of a network address as a string, returning an integer representing the value of that address.
The address can be a 4 or 8-bit address

Inet_ntoa (expr), given a digital network address (4 or 8 bits), returns the point address of the address as a string.

4. Lock function and unlock function

Get_lock (str,timeout) tries to use the string str given a name to get a lock that times out to timeout seconds.

Release_lock (str) unlocks the lock obtained by Get_lock (), named after the string str.

Is_free_lock (str) Check whether a lock named Str can be used

Is_used_lock (str) Check if a lock named Str is in use
5. Functions that perform the specified operation repeatedly
The BENCHMARK (count,expr) function repeats the count executions of the expression expr. He can be used to calculate the speed of the MySQL processing expression.
The resulting value is typically 0 (0 only means that the process is fast, not time-consuming)
Another effect is that he can report the time of the statement execution within the MySQL client.

6, changing the character set function
CONVERT (...) The CONVERT () function with using is used to convert data between different character sets.

My MySQL Learning notes (iv)--functions