Getting started with MySQL is simple-learning notes-Chapter 13th MySQL Functions

13.1 mathematical functions

Random numbers may be used, and others are ignored.

13.2. String Functions

Key Concat (S1, S2 ....)

13.3. Date and Time Functions

Key Points

13.4 conditional judgment Functions

If (expr, V1, V2)

Ifnull (V1, V2)

Case

1. Case whenexpr1 then V1 [When expr2 then V2…] [Else VN] End

2. Case expr whene1 then V1 [When E2 then V2…] [Else VN] End

13.5 system information functions

The version () function returns the database version number;

The connection_id () function returns the number of connections to the server, that is, the number of connections to the MySQL service until now;

Database () and Schema () return the current database name.

User (), system_user (), session_user (), CURRENT_USER (), and CURRENT_USER functions can return the name of the current user.

The charset (STR) function returns the STR character set. Generally, this character set is the default Character Set of the system;

The collation (STR) function returns the character arrangement of the STR string.

The last_insert_id () function returns the final auto_increment value.

13.6 encryption functions

The password (STR) function can encrypt the string 'str. Generally, the password (STR) function is used to encrypt the user's password.

The MD5 (STR) function can encrypt the string 'str. The MD5 (STR) function encrypts common data.
The encode (STR, pswd_str) function can use the string pswd_str to encrypt the string Str. The encrypted result is a binary number, which must be saved using BLOB fields.

The decode (crypt_str, pswd_str) function can use the string pswd_str to decrypt crypt_str.

13.7 formatting Functions

The format (x, n) function can format the number X and retain X to N digits after the decimal point. This process requires rounding.

 

ASCII (s) returns the ASCII code of the first character of string S;

Bin (x) returns the binary code of X;

Hex (x) returns the hexadecimal code of X;

Oct (x) returns the octal code of X;

Conv (x, F1, F2) converts X from the F1 hexadecimal number to the F2 hexadecimal number.

 

The inet_aton (IP) function can convert an IP address to a digital representation. The inet_ntoa (n) function can convert the number n to an IP address. The IP value in the inet_aton (IP) function must be enclosed by quotation marks. These two functions are inverse functions.

 

The get_loct (name, time) function defines a lock named Nam with a duration of time seconds. If the lock succeeds, 1 is returned. If the attempt times out, 0 is returned. If an error occurs, null is returned.

The release_lock (name) function removes the lock named name. If the unlock succeeds, 1 is returned. If the attempt times out, 0 is returned. If the unlock fails, null is returned;

The is_free_lock (name) function checks whether a lock named name is used. If yes, 0 is returned. Otherwise, 1 is returned.

 

The benchmark (count, expr) function repeats the expression expr for count times and returns the execution time. This function can be used to determine the speed at which MySQL processes expressions.

 

The convert (s using CS) function converts the character set of string s to CS.

Cast (X as type) and convert (x, type) convert X to type. These two functions only apply to binary, Char, date, datetime, time, signed integer, and unsignedinteger types. However, the two methods only change the data type of the output value and do not change the field type in the table.