Mysql function 2

Mysql function 2

Iv. Conditional judgment functions 1. if (expr, v1, v2): returns the result v1. Otherwise, select id, if (grade> = 60, 'pass', 'fail ') from t; 2. IFNULL (V1, V2) function: If v2 is empty, otherwise v1 is displayed, for example: mysql> select a, ifnull (, 'No') from pet76; + ------ + ---------------- + | a | ifnull (a, 'no ') | + ------ + ---------------- + | 100 | 100 | NULL | no | 12 | 12 | + ------ + ---------------- + 3 rows in set (0.01 sec) 3. CASE Function Format: case when expr1 then v1 [when expr2 then v2...] [else vn] end format: cas E 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; // level is the field alias. Example 2: select grade, case grade when 90 then 'Good 'when 60 then' pass 'else' no grade 'end level from t; // level is the field alias 5. The system information function version () returns the database version number connection_id () returns the number of connections to the server database (), schema () returns the current database name user (), system_user (), session_user () returns the current User current_user (), current_user returns the character set collation (str) of the string str returned by the current user charset (str) return the character arrangement of the string str last_insert_id () return the most recently generated auto_increment value, if an insert statement inserts multiple records, the value is the value when the first value is inserted. 6. encryption functions 1. password (str) can encrypt the string 'str'. This encryption is irreversible and is mainly used to encrypt the user's password. 2. MD5 (srtr) can encrypt the string 'str'. It mainly encrypts common data. 3. encode (str, pswd_str) can use the string 'pswd_str 'to encrypt the string 'str. The encryption result is a binary number and must be saved with blob fields. 4. decode (crypt_str, pswd_str) can be decrypted with the string pswd_str for crypt_str. Crypt_str is the binary data encrypted by encode (str, pswd_str. 7. Other functions 1. format (x, n) can format the number x and retain x to n digits 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 bin (x) of the first character of string s. returns the binary code hex () of x. returns the hexadecimal code oct (x) of x) returns the octal code conv (x, f1, f2) of x to convert x from the f1 hexadecimal number to the f2 hexadecimal number. Example: mysql> select conv (, 2 ); + --------------- + | conv (10000, 10, 2) | + ------------------- + | 0.00 | + --------------- + 1 row in set (sec) 3. inet_aton (ip) function: ip address location can be If the address is converted to a number, the ip address needs to be enclosed in inet_ntoa (n). function: You can convert the number n to an ip address. For example, mysql> select inet_aton ('123. 168.1.125 '); + -------------------------- + | inet_aton ('2017. 168.1.125 ') | + ---------------------------- + | 3232235901 | + -------------------------- + 1 row in set (0.64 sec) 4. get_lock (name, time) defines a lock with the name and duration as time seconds. If the lock succeeds, 1 is returned. If the attempt times out, 0 is returned. If an error occurs, null is returned. Release_lock (name) 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. Is_free_lock (name) determines whether to use the lock named name. If 0 is returned, otherwise 1 is returned. for example, mysql> select get_lock ('my', 10); + ----------------- + | get_lock ('my', 10) | + ------------------- + | 1 | + ----------------- + 1 row in set (0.08 sec) 5. the benchmark (count, expr) function repeats the expr expression for count times, and then returns the execution time. It is mainly used to judge the speed of mysql processing expressions. 6. convert (s using cs): converts the character set of string s to cs, for example, mysql> select charset ('abc') and charset (convert ('abc' using utf8 )); + ---------------- + -------------------------------- + | charset ('abc') | charset (convert ('abc' using utf8 )) | + -------------- + bytes + | gbk | utf8 | + -------------------- + -------------------------------- + 1 row in set (0.00 sec) 6, cast (x as type) and convert (x, type) these two functions are only for bin Ary, char, date, datetime, time, signed integer, and unsigned integer types take effect. Note: This value changes the Data Type of the output value and does not change the field type in the table. Example: mysql> select cast ('2017-10-28 19:47:59 'as date), convert ('2017-10-28 19:47:59', time ); + ----------------------------------- + ------------------------------------- + | cast ('2017-10-28 19:47:59 'as date) | convert ('2017-10-28 19:47:59', time) | + rows + | 2009-10-28 | 19:47:59 | + ------------------------------------- + 1 row in set (0.10 sec)