MySQL common functions full 1, mathematical functions ABS (x) return the absolute value BIN (x) of x returns the binary value of x (OCT returns octal, HEX returns hexadecimal) CEILING (x )??????????????? Returns the x power FLOOR (x) of the least integer value EXP (x) returned value e (base of the natural logarithm) greater than x, and returns the maximum integer GREATEST (x) less than x.
MySQL common functions 1. mathematical functions ABS (x) return the absolute value BIN (x) of x returns the binary value of x (OCT returns octal, HEX returns hexadecimal) CEILING (x )? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Returns the x power FLOOR (x) of the least integer value EXP (x) returned value e (base of the natural logarithm) greater than x, and returns the maximum integer GREATEST (x) less than x.
MySQL common functions
1. mathematical functions ABS (x) return the absolute value BIN (x) of x and return 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 of LN (x )???????????????????? Returns the natural logarithm LOG (x, y) of x, and returns the base y logarithm MOD (x, y )????????????????? 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 )???????????? Returns the result 2 of number x truncated to y decimal places. Aggregate Function (commonly used in SELECT query of group by clause) AVG (col) returns the average COUNT (col) of the specified column) 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 in the specified column GROUP_CONCAT (col). returns the result 3. 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, FIND_IN_SET (str, List) analyzes the list separated by commas (,). If str is found, return the LCASE (str) or LOWER (str) position 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 the string 'str'. LTRIM (str) removes the leading space POSITION (substr, str) from the string 'str'. returns the position quote (str) of the substring 'substr' that appears for the first time in the string 'str) use a backslash to escape the single quotation mark REPEAT (str, srchstr, rplcstr) in str to return the result REVERSE (str) of string str REPEAT x times and return the result of REVERSE string str 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 the s1 and s2TRIM (str) strings to remove Returns the result 4 of converting all characters in string 'str' into uppercase. the date and time functions are CURDATE () or CURRENT_DATE () returns the current date CURTIME () or CURRENT_TIME (). returns 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 as: SELECT ?? DATE_SUB (CURRENT_DATE, INTERVAL 6 MONTH); DAYOFWEEK (date )??? Returns the Day (1 ~ 7) DAYOFMONTH (date )?? Returns the Day (1 ~ 31) DAYOFYEAR (date )??? Returns the Day (1 ~ 366) DAYNAME (date )??? Returns the week name of date, for example, select dayname (CURRENT_DATE); FROM_UNIXTIME (ts, fmt )?? Format the UNIX timestamp tsHOUR (time )??? Returns 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), such as select quarter (CURRENT_DATE); WEEK (date )??? Returns the week number Of The Year (0 ~ 53) YEAR (date )??? Returns the year of the date (1000 ~ 9999) Examples: Obtain the current system time: SELECT FROM_UNIXTIME (UNIX_TIMESTAMP (); select extract (YEAR_MONTH FROM CURRENT_DATE); select extract (DAY_SECOND FROM CURRENT_DATE ); select extract (HOUR_MINUTE FROM CURRENT_DATE); returns the difference between two date values (number of months): SELECT PERIOD_DIFF (200302,199802); calculates the age in Mysql: SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (NOW ()-TO_DAYS (birthday), '% y') + 0 AS age FROM employee; in this way, if Brithday is the year, month, and day of the future, the calculation result is 0. The following SQL statement calculates the absolute age of an employee, that is, when Birthday is a future date, a negative value is obtained. SELECT DATE_FORMAT (NOW (), '% y')-DATE_FORMAT (birthday,' % y')-(DATE_FORMAT (NOW (), '00-% m-% D ') <DATE_FORMAT? (Birthday, '00-% m-% D') AS age from employee v. encryption function AES_ENCRYPT (str, key )?? Returns the result of the string str encrypted using the Advanced Encryption Standard algorithm using the key. What is the result of calling AES_ENCRYPT? String, which stores the AES_DECRYPT (str, key )?? Returns the DECODE (str, key )??? Use key as the key to decrypt the encrypted string strENCRYPT (str, salt )?? Use the UNIX crypt () function and the keyword salt (a string that can uniquely identify the password, just like a key) to encrypt the strENCODE (str, key )??? The key is used as the key encryption string str. The result of calling ENCODE () is a binary string, which stores MD5 ()???? Calculate the MD5 checksum and PASSWORD (str )??? Returns the encrypted version of the string 'str'. This encryption process is irreversible and uses different algorithms than the UNIX password encryption process. SHA ()???? Calculate the SHA checksum of the str string: select encrypt ('root', 'salt'); select encode ('xufeng', 'key '); select decode (ENCODE ('xufeng', 'key'), 'key'); # SELECT AES_ENCRYPT ('root', 'key') together for encryption and decryption '); SELECT AES_DECRYPT (AES_ENCRYPT ('root', 'key'), 'key'); SELECT MD5 ('20140901'); select sha ('20140901 '); 6. Control Flow functions MySQL has four functions used for conditional operations. These functions can implement the SQL conditional logic and allow developers to convert some application business logic to the database background ?. MySQL control flow function: case when [test1] THEN [result1]... ELSE [default] END if testN is true, THEN resultN is returned; otherwise, defaultCASE [test] WHEN [val1] THEN [result] is returned... ELSE [default] END ?? If test and valN are equal, resultN is returned; otherwise, defaultIF (test, t, f)? is returned )?? If test is true, t is returned; otherwise, arg1 (arg1, arg2) is returned if arg1 is not empty; otherwise, arg2NULLIF (arg1, arg2) is returned if arg1 = arg2, NULL is returned; otherwise, arg1 is returned. The first of these functions is IFNULL (). It has two parameters and judges the first parameter. If the first parameter is not NULL, the function returns the nth value to the caller? One Parameter; if it is NULL, the second parameter is returned. For example, select ifnull (), IFNULL (NULL, 10), IFNULL (4 * NULL, 'false'); The NULLIF () function checks whether the two parameters are equal, if they are equal, NULL is returned. If they are not equal, the first parameter is returned. For example: select nullif (), NULLIF ('A', 'B'), NULLIF (2 + 3, 4 + 1); like the IF () function provided by many scripting languages, mySQL's IF () function can also establish a simple conditional test. This function has three parameters. The first one is to be judged? IF the expression is true, IF () returns the second parameter. IF it is false, IF () returns the third parameter. For example: select if (1 <100, 3), IF (56>, 'true', 'false'); IF () functions are suitable for use only when there are two possible results. However, in the real world, we may find that multiple branches are required in conditional testing. In this case ,? MySQL provides the CASE function, which is the same as the switch-case condition routine in PHP and Perl. The format of the CASE function is a bit complex, usually as follows: CASE [expression to be evaluated] WHEN [val 1] THEN [result 1] WHEN [val 2] THEN [result 2] WHEN [val 3] THEN [result 3]...... WHEN [val n] THEN [result n] ELSE [default result] END here, the first parameter is the value or expression to be judged, followed by a series of WHEN-THEN blocks, the first parameter of each piece specifies the value to be compared. If it is true ,? Returns the result. All WHEN-THEN blocks END with the ELSE block. WHEN the END ends with all external CASE blocks, if none of the preceding blocks match, the ELSE is returned? The default result specified by the block. If no ELSE block is specified and all WHEN-THEN comparisons are not true, MySQL returns NULL. There is another syntax for the CASE function, which is sometimes very convenient to use as follows: case WHEN [conditional test 1] THEN [result 1] WHEN [conditional test 2] THEN [result 2] ELSE [default result] END, the returned result depends on whether the test is true. Example: mysql> select case 'green '?????? WHEN 'red' THEN 'stop '?????? WHEN 'green' THEN 'Go' END; select case 9 WHEN 1 THEN 'a 'when 2 THEN 'B' else' N/A' END; select case when (2 + 2) = 4 THEN 'OK' WHEN (2 + 2) <> 4 THEN 'not OK 'END AS STATUS; SELECT Name, IF (IsActive = 1), 'activated', 'inactivity') as result from UserLoginInfo; SELECT fname, lname, (math + sci + metrics) AS total, case when (math + sci + benchmark) <50 THEN 'D' WHEN (math + sci + benchmark) BETWEEN 50 AND 150 THEN 'C' WHEN (math + sci + benchmark) BETWEEN 151 AND 250 THEN 'B' else' a' ENDAS grade FROM marks; SELECT IF (ENCRYPT ('sue ', 'ts') = upass, 'allow', 'deny ') AS LoginResult FROM users WHERE uname = 'sue '; # One login verification 7. Formatting Function DATE_FORMAT (date, fmt )?? FORMAT the date value FORMAT (x, y )??? Format x as a numerical sequence separated by commas. y indicates the number of decimal places in the result. INET_ATON (ip )??? The number of the returned IP address is INET_NTOA (num )??? Returns the IP address TIME_FORMAT (time, fmt )?? FORMAT () is the simplest of the time values formatted according to the string fmt. It can FORMAT large numbers into readable sequences separated by commas. Example: select format (34234.34323432, 3); SELECT DATE_FORMAT (NOW (), '% W, % D % M % Y % R'); SELECT DATE_FORMAT (NOW (), '% Y-% m-% D'); SELECT DATE_FORMAT (19990330,' % Y-% m-% D'); SELECT DATE_FORMAT (NOW (), '% h: % I % p'); SELECT INET_ATON ('10. 122.89.47 '); SELECT INET_NTOA (175790383); 8. to convert data types, MySQL provides the CAST () function, it can convert a value to a specified data type. Types :? BINARY, CHAR, DATE, TIME, DATETIME, SIGNED, UNSIGNED example: select cast (NOW () as signed integer), CURDATE () + 0; SELECT 'F' = BINARY 'F', 'F' = CAST ('F' as binary); 9. system information function DATABASE ()??? Returns the current database name BENCHMARK (count, expr )?? Run count CONNECTION_ID ()??? Returns the connection IDFOUND_ROWS ()??? Returns the total number of USER () or SYSTEM_USER ()?? Returns the current login username VERSION ()??? Returns the MySQL server VERSION example: select database (), VERSION (), USER (); select benchmark (9999999, LOG (RAND () * PI ())); # In this example, MySQL calculates the LOG (RAND () * PI () Expression
