Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the functions commonly used in Mysql are. It is very detailed and has a certain reference value. Friends who are interested must finish reading it.

Aggregate function

AVG (col_name) average: ignore the COUNT (col_name) of null to find the quantity, do not ignore the row of null when using *, ignore the maximum value of MAX () when specifying the field, ignore the minimum value of row MIN () of null, and return the sum of all the values of the specified column if the row sum (col_name) of null is ignored.

Mathematics

Floor () rounding down ceil () rounding up round (,) rounding truncate (,) does not round mod (,) to the absolute value of abs (), power of pi (), six-digit rand () random number sign () > 0 returns 1, less than 0 returns-1, otherwise it is the power of 0exp () e

String

Number of char_length () characters length () string length concat () concatenation string If Null is included, the returned value is nullconcat_wa () to concatenate the string concat_wa (null,'','') with the specified delimiter. Return nullconcat_wa ('-','a delimiter pamphlet (null,'','')) return nullconcat_wa () = ucase () turn uppercase lower () = lcase () convert lowercase reverse () convert uppercase and lowercase left (,) | right (,) the first few characters of string | the last few characters lpad (XQuery yline z) | rpad Z) x by y length before | add z characters trim () | ltrim () | rtrim () to remove spaces repeat (, times) repeat the specified number of times replace (xmemy Magazine z) x string replaces the y string with the zsubstring (xmeny Magnez) x string to intercept z length from y Y start from 1 strcmp (XBI y) string comparison

Date and time

Curdate () = current_date () current date such as: 2019-03-01curtime () = current_time () current time such as: 19:01:57now () = current_timestamp () = sysdate () current date and time such as: 2019-03-01 19:00:16month (curdate ()) current month such as: 3 monthname (curdate ()) current month name dayname (curdate ()) days of the week such as: Fridaydayofweek (curdate ()) Sunday is the week of 1week (now ()) the week of the year, year/month/day/hour/minute/second, month, day, hour, minute, second, datediff (), the difference between the two dates date_format (date, "% Y%m%d")

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. Storing AES_DECRYPT (str,key) as BLOB returns the result of decrypting the string with the key key using the advanced encryption standard algorithm DECODE (str,key) using key as the key to decrypt the encrypted string strENCRYPT (str,salt) using the UNIXcrypt () function, encrypting the string strENCODE (str,key) with the keyword salt (a string that uniquely determines the password, just like the key) encrypting the string str using key as the key The result of the call to ENCODE () is a binary string that stores the MD5 checksum of the MD5 () computed string str (str) in type BLOB and returns the encrypted version of the string str, which is irreversible and uses a different algorithm than the UNIX password encryption process. SHA () calculates the secure hash algorithm (SHA) checksum of string str

Other

Version () version connection_id () connections database () = schema () current database user () = current_user () = system_user () = session_user () current user last_insert_id () the last auto_ increment value of the current table md5 () password () encrypts format (number, 2) 1000-percentile represents and retains more than two decimal places is all the contents of the article "what are the common functions in Mysql"? thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.