Getting Started with MySQL (12)--mysql intrinsics

The rich functions simplify the operation of the user, make the operation more flexible, moreover, because the function executes very quickly, also can improve MySQL processing speed.
The SELECT statement and its conditional expressions, which are described earlier, can be used by the Insert, update, and DELETE statements and their conditional expressions.
MySQL functions include mathematical functions, string functions, date and time functions, conditional judgment functions, washes information functions, cryptographic functions, formatting functions, and so on. The use of these functions is described in more detail below.

1. Mathematical Functions

Mathematical functions are mainly used for processing numbers, including integers, floating-point types, and so on.

2. String functions

String functions are primarily used to manipulate strings in a table.

(1) Assuming that the function lpad (' jiangjing ', 15, ' + + ') is used to fill the string ' + ' to the beginning of ' jiangjing ', so that the string length reaches 15, then the result is ' +-+-+jiangjing '.
(2) Assuming that the function trim (' ab ' from ' Ababdddababdddab ') is used to remove the ' ab ' in the string ' Ababdddababdddab ', the ' dddababddd ' will be obtained.
(3) Assuming the use of make_set (one, ' a ', ' B ', ' C ', ' d ') functions to select a string, the first 11 conversion into the binary number 1011, because the order of reading binary number is from right to left, so that the right-to-left 1th, 2nd and 4th bit 1, so corresponding to select ' A ' ' B ' and ' d '. Be sure to note the order.

3. Date and Time functions

where MySQL's date interval type is as follows

The date and time format in MySQL is as follows

4. Conditional Judgment function

Conditional judgment functions are used to make conditional judgments in SQL statements.

4.1 IF (EXPR,V1,V2) function

If the expression expr is true, returns the result V1, otherwise v2 the result.

4.2 ifnull (V1,V2) function

If V1 is not empty, the value of V1 is displayed, otherwise the value of v2 is displayed.

4.3 Case function

(1) Case if Exp1 then V1 [when Expr2 then v2] [ELSE VN] END
Similar to If Else statement
(2) Case expr when E1 then V1 [when E2 then v2] [ELSE VN] END
Similar to case statements

5. System Information function

System information functions are used to query the MySQL database for system information.

6. Cryptographic functions

The function that MySQL uses to encrypt data when it encrypts functions.

6.1 Cryptographic function password (str)

Usually used to encrypt a user's password.
Note that the encryption of the password () function is irreversible.

6.2 Cryptographic function MD5 (str)

It is mainly used to encrypt common data.

6.3 Cryptographic function encode (STR,PSWD_STR)

You can use string Pswd_str to encrypt the string str, and the result of the encryption is a binary number that must be saved with a field of blob type.

6.4 Decryption function decode (CRYPT_STR,PSWD_STR)

You can use string pswd_str to decrypt Crypt_str, where crypt_str is encrypted with the 6.3 function, noting that the string pswd_str should be the same as the string pswd_str when it is encrypted.

7. Other functions 7.1 formatting function format (x,n)

You can format the number x to hold it to the N-bit after the decimal point, which is rounded.
Similar to function round (x, y).

7.2 The function of converting the number of the different binary

(1) ASCII (s) returns the ASCII code of the first character of a string s
(2) BIN (x) returns the binary encoding of X
(3) Hex (x) returns the hexadecimal encoding of X
(4) OCT (x) returns octal encoding of X
(5) CONV (X,F1,F2) converts X from F1 to F2 number of binary

7.3 Functions for converting IP addresses to numbers

(1) Inet_aton (IP) can convert IP address to digital representation
(2) Inet_ntoa (n) can turn the number n into the form of IP
Note the IP value in the Inet_aton (IP) function needs to be quoted.

7.4 Plus lock function and unlock function

(1) get_loct (name,time) defines a lock that is named name, lasts for a length of time, and returns 1 if the lock succeeds, returns 0 if the attempt times out, and returns null if an error is encountered.
(2) release_loct (name) unlocks a lock named name and returns 1 if the unlock succeeds, or 0 if an attempt times out, or null if an error is encountered.
(3) Is_free_lock (name) determines whether a lock named name is used and returns 0 if used, otherwise 1.

7.5 Functions that perform the specified action repeatedly

BENCHMARK (count,expr) repeats the expression expr count times and returns the execution time after a meal, which can be used to determine the speed of the MySQL processing expression.

7.6 Changing the character Set function

CONVERT (S USING CS) changes the character set of the string s to CS

7.7 Functions for changing field data types

CAST (x as type) and CONVERT (X,type)

Getting Started with MySQL (12)--mysql intrinsics