MYSQL entry 7: MYSQL common functions

MYSQL entry 7: MYSQL common functions links: MYSQL Entry 1: basic operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.html 1. mathematical function www.2cto.com ABS (x) returns the absolute value BIN (x) of x and returns the binary value of x (OCT returns octal, HEX returns hexadecimal) CEILING (x) returns the x power FLOOR (x) of the smallest integer value EXP (x) returned value e (base of the natural logarithm) greater than x, and returns the maximum integer GREATEST (x1, x2 ,..., returns the Maximum LEAST (x1, x2 ,..., xn) returns the smallest value in the Set LN (x). returns the natural logarithm LOG (x, y) of x. returns the base y logarithm MOD (x, y) of x) returns the modulus (remainder) PI () of x/y, returns the value (circumference rate) of pi, RAND () returns the random value from 0 to 1, you can provide a parameter (SEED) generate a specified value for the RAND () random number generator. ROUND (x, y) returns the rounding value of parameter x with y decimal places SIGN (x) returns the value of the symbol representing the number x SQRT (x) returns the square root of a number. TRUNCATE (x, y) returns the result example with the number x truncated to y decimal places: mysql> select abs (-1 ); + --------- + | abs (-1) | + --------- + | 1 | + --------- + mysql> select ceiling (5.2); + -------------- + | ceiling (5.2) | + -------------- + | 6 | + -------------- + mysql> select mod (10, 4); + ----------- + | mod (10, 4) | + ----------- + | 2 | + ----------- + mysql> select rand (); + -------- ---------- + | Rand () | + ------------------ + | 0.98648901096218 | + ------------------ + mysql> select truncate (5.1235, 2); + ------------------ + | truncate (5.1235, 2) | + -------------------- + | 5.12 | + -------------------- + 2. Aggregate functions (commonly used in SELECT queries of group by clauses) www.2cto.com AVG (col) return the average value of the specified column COUNT (col) returns the number of non-NULL values in the specified column. MIN (col) returns the minimum value of the specified column. MAX (col) returns the maximum value of the specified column. SUM (col) returns the sum of all values of the specified column GROUP_CONCAT (col). returns the join of all values of the specified column. Result 3: The string function ASCII (char) returns the ASCII value of the character BIT_LENGTH (str) returns the string's bit length CONCAT (s1, s2 ..., sn) to s1, s2 ..., the sn is connected to the CONCAT_WS (sep, s1, s2 ..., sn) to s1, s2 ..., the sn is connected to a string and inserted (str, x, y, instr) with the sep character interval to replace str from position x with the string instr, returns the FIND_IN_SET (str, list) result to analyze the list separated by commas (,). If str is found, returns the position LCASE (str) or LOWER (str) of str in the list) returns the result LEFT (str, x) After all characters in the str string are changed to lowercase. returns the leftmost x character LENGTH (s) in the str string) returns the number of characters in string 'str '. Return the POSITION (substr, str) of the substring substr that appears for the first time in the str string. Use a backslash to escape the single quotation mark REPLACE (str, from_str, to_str) returns the str string. All the occurrences of the from_str string are replaced by the string to_str REPEAT (str, x) and returns the REVERSE (str) of the string str repeated x times) returns the reverse str result RIGHT (str, x). returns the rightmost x character RTRIM (str) in the str string. returns the space STRCMP (s1, s2) at the end of the str string) compares string s1 and s2 TRIM (str) to remove all spaces at the string header and tail. UCASE (str) or UPPER (str) returns the result example of converting all characters in the str string to uppercase: mysql> select bit_length ('abcd'); + -------------------- + | Bit_length ('abcd') | + ---------------------- + | 32 | + -------------------- + mysql> select insert ('abcdef', 2, 3, 'ghilj '); + values + | insert ('abcdef', 2, 3, 'ghilj') | + -------------------------- + | aghiljef | + ---------------------------- + mysql> select right ('abcdef', 3 ); + ----------------- + | right ('abcdef', 3) | + ----------------- + | def | + ----- -------------- + 4. the date and time functions CURDATE () or CURRENT_DATE () return the current date CURTIME () or CURRENT_TIME () to return the current time DATE_ADD (date, INTERVAL int keyword) returns the result of date plus the INTERVAL int (int must be formatted according to the keyword), such as: SELECT DATE_ADD (CURRENT_DATE, INTERVAL 6 MONTH); DATE_FORMAT (date, fmt) format the date value DATE_SUB (date, INTERVAL int keyword) according to the specified fmt Format to return the result of date plus the INTERVAL int (int must be formatted according to the keyword), such: SELECT DATE_SUB (CURRENT_DATE, INTERVAL 6 MONTH); DAYOFWEEK (date) returns The day of the week represented by date (1 ~ 7) DAYOFMONTH (date) returns the Day (1 ~ 31) DAYOFYEAR (date) returns the Day (1 ~ 366) DAYNAME (date) returns the name of the week of date, for example, select dayname (CURRENT_DATE); FROM_DAYS (N) returns a number of days N, and returns a DATE value. (Starting from) FROM_UNIXTIME (ts, fmt) formatted the UNIX timestamp ts HOUR (time) based on the specified fmt Format and returned the HOUR value of time (0 ~ 23) MINUTE (time) returns the MINUTE value of time (0 ~ 59) MONTH (date) returns the MONTH value of date (1 ~ 12) MONTHNAME (date) returns the month name of date, for example, select monthname (CURRENT_DATE); NOW () returns the current date and time QUARTER (date) returns the quarter (1 ~ 4), for example, select quarter (CURRENT_DATE); WEEK (date) returns the WEEK number Of The Year for date (0 ~ 53) YEAR (date) returns the YEAR of the date (1000 ~ 9999) Example: mysql> select from_unixtime (unix_timestamp (); + ----------------------------------- + | from_unixtime (unix_timestamp ()) | + hour + | 23:11:24 | + ------------------------------------- + mysql> select extract (year_month from current_date); + hour + | extract (year_month from current_date) | + hour -------------------------------------- -+ | 201212 | + ------------------------------------- + mysql> select week (current_date (); + -------------------- + | week (current_date ()) | + ---------------------- + | 50 | + ---------------------- + 5. the encryption function AES_ENCRYPT (str, key) returns the result encrypted by using the key pair string str using the Advanced Encryption Standard algorithm, the result of calling AES_ENCRYPT is a binary string that stores AES_DECRYPT (str, key) in BLOB type and returns the result DECODE (str, key) use the key as the key to decrypt the encrypted string str ENCRYPT (str, salt) Use the UNIX crypt () function and the keyword salt (a string that uniquely identifies a password, just like a key) to encrypt the str ENCODE (str, key) string) the key is used as the key to encrypt the str string. The result of calling ENCODE () is a binary string. It stores MD5 () as BLOB and calculates the MD5 checksum and PASSWORD (str) of the str string) returns the encrypted version of the string 'str'. This encryption process is irreversible and uses different algorithms than the UNIX password encryption process. SHA () Security Hash Algorithm for string str calculation (SHA) checksum example: mysql> select md5 ('000000'); + ---------------------------------- + | md5 ('000000 ') | + keys + | keys | + -------------------------------- + mysql> select password ('000000'); + keys + | password ('000000') | + keys + | * 6BB4837EB74329105EE 4568DDA7DC67ED2CA2AD9 | + latency + mysql> select sha ('000000'); + -------------------------------------- + | sha ('20140901 ') | + conditions + | conditions | + ------------------------------------------ + 6. Control Flow functions MySQL has four functions for conditional operations. These functions can implement the SQL conditional logic, allows developers to switch some application business logic to the database background. MySQL control flow function: case when [test1] THEN [result1]... ELSE [default] END if testN is true, resultN is returned; otherwise, default CASE [test] WHEN [val1] THEN [result] is returned... ELSE [default] end if test and valN are equal, resultN is returned; otherwise, default IF (test, t, f) is returned. IF test is true, t is returned; otherwise, f IFNULL (arg1, arg2) is returned. If arg1 is not empty, arg1 is returned. Otherwise, arg2 NULLIF (arg1, arg2) is returned. If arg1 = arg2, NULL is returned. Otherwise, arg1 is returned: mysql> select case 'sz 'when' sz 'then' shenzhen '-> when 'gz 'then' guangzhou '-> When 'xm 'then' xiamen'-> else 'n'/A' end; + else | case 'sz 'when' sz 'then' shenzhen 'when' gz 'then' guangzhou 'when' xm 'then' xiamen 'else' N/A' end | + required | shenzhen + ----------------------------------------------------------- mysql> select IF (sex = 1 ), 'male', 'female'); ERROR 1054 (42S22): Unknown column 'sex' in 'field list' mysql> select IF (0 = 1), 'male', 'female '); + expiration + | IF (0 = 1), 'male', 'female ') | + --------------------------- + | female | + ------------------------- + 7. Formatting Function DATE_FORMAT (date, fmt) FORMAT x to a comma-separated numeric sequence based on the string fmt FORMAT date value FORMAT (x, y). y is the number of decimal places in the result. INET_ATON (ip) the number of the returned IP Address indicates INET_NTOA (num). The returned IP address TIME_FORMAT (time, fmt) is displayed according to the time value of the string fmt Format time. Example: mysql> select format (1324.343633, 2); + --------------------- + | format (1324.343633, 2) | + ----------------------- + | 1,324.34 | + ----------------------- + mysql> select date_format (now (), '% Y-% m-% D '); + ----------------------------- + | date_format (now (), '% Y-% m-% D ') | + ----------------------------- + | 2012-12-12 | + --------------------------------- + 8. to convert data types, MySQL provides the CAST () function, which converts a value The specified data type. Types include BINARY, CHAR, DATE, TIME, DATETIME, SIGNED, and UNSIGNED. Example: mysql> select cast (now () as signed integer); + --------------------------- + | cast (now () as signed integer) | + ----------------------------- + | 20121212232553 | + ------------------------------- + 9. The system information function DATABASE () returns the current DATABASE name SCHEMA () and DATABASE () BENCHMARK (count, expr) repeats the count CONNECTION_ID () operation in the expression expr and returns the current customer's connection ID FOUND_ROWS () to the last one. The total number of rows retrieved by the SELECT query ROW_COUNT () returns the number of rows upgraded, inserted, or deleted by the preceding statement. The number of rows is the same as the number of rows displayed on the mysql client and the value returned by the mysql_affected_rows () c api function. LAST_INSERT_ID () LAST_INSERT_ID (expr) automatically returns the first value of USER () or SYSTEM_USER () set for the last INSERT or UPDATE query as the AUTO_INCREMENT column. returns the current login username SESSION_USER () and USER () SYSTEM_USER () and USER () have the same meaning: CURRENT_USER, CURRENT_USER () returns the username and host name combination verified by the current session VERSION () returns the MySQL server CHARSET (str) version, returns the COERCIBILITY (str) Character Set of the string independent variable, and returns the full-order computation value of the string independent variable COLLATION (str). The sorting method of the string parameters is as follows: mysql> select database (); + ------------ + | database () | + ------------ + | test | + ------------ + mysql> select user (); + ---------------- + | user () | + ---------------- + | root @ localhost | + ---------------- + mysql> select version (); + --------------------- + | version () | + --------------------- + | 5.1.28-rc-community | + --------------------- + 10. Other functions www.2cto.com DEFAULT (col_name) return the default uuid () of a table column () returns an example of a string that is unique in time and space: mysql> select uuid (); + ------------------------------------ + | uuid () | + ------------------------------------ + | ddf93e7b-4460-11e2-aaaa-fc6e2fa4a75a |