Shulou(Shulou.com)05/31 Report--

What are the commonly used functions in MySQL? in view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

1. Mathematical function

ABS (x)-returns the absolute value of x BIN (x)-returns the binary of x (OCT returns octal HEX returns hexadecimal) CEILING (x)-- returns the minimum integer value EXP (x) greater than x-- returns the x-th power FLOOR (x) of the value e (the base of the natural logarithm)-- returns the maximum integer value less than x GREATEST (x1mx2)-- returns the largest value LEAST in the set (x1mx2). Xn)-- returns the smallest value in the set LN (x)-- returns the natural logarithm of x LOG (x)-- returns the logarithm MOD of x at the base of y (x)-- returns the module (remainder) PI () of x Universe y-- returns the value of pi (pi) RAND ()-- returns a random value from 0 to 1. You can cause the RAND () random number generator to generate a specified value by providing a parameter (seed). ROUND (xQuery y)-returns the rounded value SIGN (x) of the parameter x with y decimal places-returns the value SQRT (x) representing the symbol of the number x-returns the square root of a number TRUNCATE (xQuery y)-returns the result of the number x truncated to the y decimal place

2. Aggregate function

AVG (X)-returns the average value of the specified column COUNT (X)-returns the number of non-null values in the specified column MIN (X)-returns the minimum value of the specified column MAX (X)-returns the maximum value of the specified column SUM (X)-returns the sum of all values of the specified column GROUP_CONCAT (X)-returns The result of a combination of column values that belong to a group Very useful

3. String functions (20)

ASCII (char)-- return the ASCII code value of the character BIT_LENGTH (str)-- return the bit length of the string CONCAT (s1Mague s2.. rep sn)-concatenate s1Magne sn into the string CONCAT_WS (sep,s1,s2...,sn)-concatenate s1Mague s2... And use the sep character interval INSERT (str,x,y,instr)-- replace the string str from the x position, replace the y-character-long substring with the string instr, and return the result FIND_IN_SET (str,list)-- analyze the comma-separated list list, if str is found Returns the position of str in list LCASE (str) or LOWER (str)-returns the result of changing all characters in the string str to lowercase LEFT (str,x)-returns the leftmost x character LENGTH (s) in the string str-returns the number of characters in the string str LTRIM (str)-- cuts off the opening space POSITION (substr) from the string str Str)-- returns the position where the substring substr first appears in the string str QUOTE (str)-- escapes the single quotation mark REPEAT (str,srchstr,rplcstr) in str with a backslash-- returns the result REVERSE (str) of the string str repeated x times-- returns the result RIGHT that reverses the string str (str) X)-returns the rightmost x character RTRIM (str) in the string str-returns the space STRCMP (S1) at the end of the string str S2)-- compare strings S1 and S2 TRIM (str)-- remove all spaces at the beginning and end of the string UCASE (str) or UPPER (str)-- return the result of converting all characters in the string str to uppercase

4. Date and time functions

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 the date date plus interval int (int must be formatted according to keywords) 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-- returns the result of the date date plus the interval time int (int must be formatted according to keywords) such as SELECT DATE_SUB (CURRENT_DATE,INTERVAL 6 MONTH) DAYOFWEEK (date)-returns the day of the week represented by date (1x 7) DAYOFMONTH (date)-returns the day of the month (1x 31) DAYOFYEAR (date)-returns the day of the year (1x 366) DAYNAME (date)-returns the name of the week of date, such as SELECT DAYNAME (CURRENT_DATE) FROM_UNIXTIME (ts,fmt)-formats the UNIX timestamp ts HOUR (time) according to the specified fmt format-returns the hourly value of time (0,23) MINUTE (time)-returns the minute value of time (0,59) MONTH (date)-returns the month value of date (1x12) MONTHNAME (date)-returns the month name of date, such as SELECT MONTHNAME (CURRENT_DATE) NOW ()-- returns the current date and time QUARTER (date)-- returns the quarter of the year (1-4) such as SELECT QUARTER (CURRENT_DATE); WEEK (date)-- returns the week (0,53) YEAR (date) of the year date-- returns the year of the date date (1000-9999), for example, gets 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); return the difference between the two dates (months) SELECT PERIOD_DIFF (200302 NOW) 199802); calculate the age in Mysql: SELECT DATE_FORMAT (TO_DAYS (NOW ())-TO_DAYS (birthday),'% Y') + 0 AS age FROM employee;, if Brithday is the future date, the result is 0. The following SQL statement calculates the absolute age of the employee, that is, when Birthday is a future date, it will get a negative value. SELECT DATE_FORMAT (NOW (),'% Y')-DATE_FORMAT (birthday,'% Y')-(DATE_FORMAT (NOW (), '00muri% mmurf% d') < DATE_FORMAT (birthday,' 00Mel% mmure% d')) AS age from employee

5. Encryption function

AES_ENCRYPT (str,key)-returns the result of encrypting the string str with the key key using the Advanced encryption Standard algorithm. The result of calling AES_ENCRYPT is a binary string. Store AES_DECRYPT (str,key) as BLOB type-- return the result of decrypting the string str with the key key using the advanced encryption standard algorithm DECODE (str,key)-- using key as the key to decrypt the encrypted string str ENCRYPT (str,salt)-- using the UNIXcrypt () function, using the keyword salt (a string that uniquely determines the password (just like the key) encrypts the string str ENCODE (str,key)-- encrypts the string str using key as the key, and the result of calling ENCODE () is a binary string that stores MD5 () as BLOB-- calculates the MD5 checksum PASSWORD (str) of the string str-- and returns the encrypted version of the string str. The encryption process is irreversible. The encryption process of the UNIX password uses a different algorithm. SHA ()-- A secure hash algorithm (SHA) checksum for calculating string str such as SELECT ENCRYPT ('root','salt'); SELECT ENCODE (' xufeng','key'); SELECT DECODE (ENCODE ('xufeng','key'),' key');-- SELECT AES_ENCRYPT ('root','key'); SELECT AES_DECRYPT (AES_ENCRYPT (' root','key'), 'key') SELECT MD5 ('123456'); SELECT SHA ('123456')

6. Control flow function

CASE WHEN [test1] THEN [result1]... ELSE [default] END-returns result1 if test1 is true, default CASE [test] WHEN [val1] THEN [result]... ELSE [default] END-returns result if test and valN are equal, otherwise default IF (test,t,f)-returns t if test is true Otherwise return f IFNULL (arg1,arg2)-if arg1 is not empty, return arg1, otherwise return arg2 NULLIF (arg1,arg2)-if arg1=arg2 returns NULL; otherwise return arg1 the first of these functions is IFNULL (), which takes two parameters and determines the first parameter. If the first argument is not NULL, the function returns the first argument to the caller; if it is NULL, the second argument is returned. For example, SELECT IFNULL (1 NULL,10 2), IFNULL (NULL,10), IFNULL (4 invalid null); the NULLIF () function will verify that the two parameters provided are equal, return NULL if they are equal, and return the first parameter if they are not equal. For example, SELECT NULLIF (1pr 1), NULLIF ('Achievement dint B'), NULLIF (2cm 3pr 4i 1); MySQL's IF () function can also establish a simple conditional test, this function has three parameters, the first is the expression to be judged, if the expression is true, IF () will return the second argument, if false, IF () will return the third parameter. For example, SELECT IF (1100); the IF () function is suitable for use when there are only two possible outcomes. In the real world, however, we may find that multiple branches are needed in conditional testing. In this case, it is the same as the switch-case conditional routine in the PHP and Perl languages. The format of the CASE function is somewhat 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 block specifies the value to be compared, and if true, the result is returned. All WHEN-THEN blocks will end with ELSE blocks, and when END ends all external CASE blocks, if none of the previous blocks match, the default result specified by the ELSE block will be returned. If no ELSE block is specified and all WHEN-THEN comparisons are not true, MySQL will return NULL. There is another syntax for the CASE function, which is sometimes very convenient to use as follows: under the condition of 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 corresponding conditional test is true. For example: 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 END; SELECT CASE WHEN (2x2) = 4 THEN 'OK' WHEN (2p2) 4 THEN' not OK' END AS STATUS; SELECT Name,IF ((IsActive = 1), 'activated', 'inactivated') AS RESULT FROM UserLoginInfo SELECT fname,lname, (math+sci+lit) AS total, CASE WHEN (math+sci+lit) < 50 THEN 'D'WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'WHEN (math+sci+lit) BETWEEN 151AND 250 THEN 'B'ELSE 'A'END AS grade FROM marks; SELECT IF (ENCRYPT (' sue','ts') = upass,'allow','deny') AS LoginResult FROM users WHERE uname = 'sue'

7. Formatting function

DATE_FORMAT (date,fmt)-- format the date value FORMAT (x _ date,fmt) according to the string fmt-- format x into a comma-separated sequence of numbers Y is the resulting decimal place INET_ATON (ip)-the number that returns the IP address represents INET_NTOA (num)-returns the IP address TIME_FORMAT (time,fmt) represented by the number-the simplest of which is the FORMAT () function, which formats large values into legible sequences separated by commas. For example, SELECT FORMAT (34234.34323432); SELECT DATE_FORMAT (NOW (),'% WCorre% D% M% Y% r'); SELECT DATE_FORMAT (NOW (),'% Ymuri% mlym% d'); SELECT DATE_FORMAT (19990330); SELECT DATE_FORMAT (NOW (),'% hpurr% I% p'); SELECT INET_ATON ('10.122.89.47'); SELECT INET_NTOA (175790383)

8. Type conversion function

For data type conversion, MySQL provides the CAST () function, which converts a value to a specified data type. The types are: BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED such as SELECT CAST (NOW () AS SIGNED INTEGER), CURDATE () + 0; SELECT 'f'=BINARY' Fidelity grammatical fancy cast ('F' AS BINARY)

9. System information function

DATABASE ()-returns the current database name BENCHMARK (count) Expr)-- repeat the expression expr to run count CONNECTION_ID ()-- return the connection ID FOUND_ROWS () of the current customer-- return the total number of rows retrieved by the last SELECT query, USER () or SYSTEM_USER ()-- return the current login user name VERSION ()-- return the version of the MySQL server, such as SELECT DATABASE () VERSION (), USER () SELECTBENCHMARK (99999999 RAND () * PI ());-in this case, MySQL evaluates the LOG (RAND () * PI ()) expression 9999999 times.

10. Windowing function

MySQL8.0 version begins to support windowing function

ROW_NUMBER () OVER ()-- sort: 1 RANK () OVER ()-- sort: 1 DENSE_RANK () OVER ()-- sort: 1 NTILE () OVER () Record the series LAG (expr,n) OVER ()-- return the value of the expr of the first N rows of the current line LEAD (expr,n) OVER ()-- return the value of the expr of the N rows after the current line. This is the answer to the question about which functions are commonly used in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts unsolved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.