MySQL (basic) (system functions, encryption functions, and condition judgment functions)

1. MySQL system function VERSION () function returns the database version number; CONNECTION_ID () function returns the number of connections to the server, that is, the number of connections to the MySQL service so far; DATABASE () and SCHEMA () return the current database name. USER (), SYSTEM_USER (), SESSION_USER (), CURRENT_USER (), and CURRENT_USER

1. MySQL system function VERSION () function returns the database version number; CONNECTION_ID () function returns the number of connections to the server, that is, the number of connections to the MySQL service so far; DATABASE () and SCHEMA () return the current database name. USER (), SYSTEM_USER (), SESSION_USER (), CURRENT_USER (), and CURRENT_USER

I. MySQL system 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.

Ii. 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 as the crypt_str

Iii. Conditional judgment Functions

If statement

If (expr, v1, v2) function: if the expression expr is true, return the result v1; otherwise, return the result v2.

Select id, grdade, if (grade> = 60, 'pass', 'fail ') from t;

The id and score are queried from Table t. When the score is greater than or equal to 60, "pass" is displayed; otherwise, "fail" is displayed ".

Ifnull (v1, v2) function: If v1 is not empty, v1 is displayed; otherwise, v2 is displayed.

Select I, iffull (grade, 'no grad') from t;

The student ID is queried from the t table. If the score is not empty, the score is displayed. Otherwise, no grade is displayed ".

Case statement

Example:

Select id, grade,

Case when grade> 60 then 'good' when grade = 60 then' pass 'else' fail 'end level

From t;

Example

Select id, grade,

Case grade when 90 then 'good' when 60 then 'pass'

When 50 then 'fail 'else' no grade 'end level

From t;

Zookeeper