MySQL function two

Iv. conditional Inference Function 1, if (EXPR,V1,V2) function: To establish the return result V1, otherwise the result V2 example: Select Id,if (grade>=60, ' Pass ', ' fail ') from T;2, Ifnull (V1,V2) Function: assumes empty display v2. Otherwise display V1 example:mysql> Select A,ifnull (A, ' no ') from pet76;+------+----------------+| A |  Ifnull (A, ' no ') |+------+----------------+| 100 | 100 | | NULL |   No | | 12 | |+------+----------------+3 rows in Set (0.01 sec) 3, Case function format: case is EXPR1 then V1 [when Expr2 then V2. .] [Else VN] End format: Case expr when E1 then V1 [when E2 then V2 ...] [Else VN] End example 1:select grade,case when grade>60 then ' good ' when grade=60 then ' pass ' else ' fail ' end level from t;//le Vel is an example of a field alias 2:select grade,case grade when the "good" when the "pass" and the "no Grade" end level from T;//level are field aliases five, System Information function version () returns the database versions connection_id () returns the server connection number database (), schema () returns the current database name user (), system_user (), Session_user () returns the current user Current_User (), CURRENT_USER returns the current user CharSet (str) return string STR's character set collation (str) return string STR's character arrangement last_insert_id () Returns the recently generated Auto_increment value. If an INSERT statement inserts multiple records, check theOut is the value of the first value when it is inserted. Six, the cryptographic function 1, password (str) can encrypt the string str, the encryption is irreversible, mainly to the user's password encryption. 2, MD5 (SRTR) is able to encrypt the string str. The main data encryption 3, encode (STR,PSWD_STR) can be used to string PSWD_STR encryption string str. The encryption result is a binary number, which must be saved with a field of BLOB Type 4, decode (CRYPT_STR,PSWD_STR) can be decrypted with the string pswd_str for Crypt_str.
CRYPT_STR is a binary data that is encrypted by encode (STR,PSWD_STR). Seven, other functions 1, format (x,n) to format the number x, the X is reserved to the N-bit after the decimal point.
Example:mysql> Select Format (2.3456,3); +------------------+| Format (2.3456,3) |+------------------+| 2.346 |+------------------+1 row in Set (0.04 sec) 2, ASCII (s) returns the ASCII code of the first character of the string s bin (x) returns x of binary coded hex () returns x hexadecimal encoding OC T (x) returns the octal encoding of x conv (X,F1,F2) to convert X from F1 to F2 number:mysql> select conv (16,10,2); +---------------+| Conv (16,10,2) |+---------------+| 10000 |+---------------+1 row in Set (0.00 sec) 3, Inet_aton (IP) function: Ability to convert IP address to digital representation, IP need to add an argument to the Inet_ntoa (n) function: Capable of converting the number n to the IP form Example:mysql> Select Inet_aton (' 192.168.1.125 '); +----------------------------+|                 Inet_aton (' 192.168.1.125 ') |+----------------------------+| 3232235901 |+----------------------------+1 row in Set (0.64 sec) 4, Get_lock (name,time) defines a lock called name duration of time seconds. Assuming the lock successfully returns 1, assuming that the attempt timed out, returns 0, and assumes that an error was encountered to return NULL. Release_lock (name) unlocks the lock named name. Assume that the unlock successfully returns 1. Suppose the attempt time-out returns 0, assuming that the unlock failure returns NULL. Is_free_lock (name) infers whether to use a lock named name. Assume that return 0 is used. Otherwise return 1. Example:mysql> Select Get_lock (' my ', ten); +-------------------+|                Get_lock (' my ', ten) |+-------------------+| 1 |+-------------------+1 row in Set (0.08 sec) 5, Benchmark (count,expr) function repeatedly runs the expression expr count times, and then returns the run time. Mainly used to infer the speed of MySQL processing expressions. 6. CONVERT (s using CS): Convert the character set of the string s into CS:mysql> select CharSet (' abc '), CharSet (CONVERT (' abc ' using UTF8)); +----------- -----+------------------------------------+| CharSet (' abc ') | CharSet (CONVERT (' abc ' using UTF8)) |+----------------+------------------------------------+| GBK |  UTF8 |+----------------+------------------------------------+1 row in Set (0.00 sec) 6, cast (x The As type and CONVERT (X,type) functions only work on binary, char, date, DateTime, Time, signed integer, unsigned integer types. Note: This value alters the data type of the output value and does not alter the type of the field in the table. Example:mysql> Select cast (' 2009-10-28 19:47:59 ' as date), convert (' 2009-10-28 19:47:59 ', time); +------------------------ -------------+-------------------------------------+| Cast (' 2009-10-28 19:47:59 ' as date) | Convert (' 2009-10-28 19:47:59 ', time) |+-------------------------------------+------------------------------------ -+|    2009-10-28                      |  19:47:59 |+-------------------------------------+-------------------------------------+1 row in Set (0.10 sec)

MySQL function two