MySQL common function math functions, cryptographic functions, etc. (Go-favorites)

There are a number of   mysql functions, here are just a few of the commonly used functions.   Math function abs (x)                             &NBSP ;          //returns the absolute value of x Bin (x)  //returns the binary of X (Oct returns octal, HEX returns hex) CEILING (x)    　　　　　　　　　　                            //returns the smallest integer value greater than x exp (x) return value E (base of natural logarithm) x-square floor (x)  //returns the maximum integer value less than x greatest (x1,x2,..., xn)    //returns the largest value in the collection least (X1,X2, ..., xn)                  //returns the smallest value in the collection ln (x)         &NBSP ;      //returns x's natural logarithm log (x, y)  //returns x's Y-base logarithmic mod (x, y)        //returns the modulo (remainder) PI ()// Returns the value of Pi (PI) rand ()//returns a random value from 0 to 1, which allows the RAND () random number generator to generate a specified value by providing a parameter (seed). ROUND (x, y)//return parameter x rounded with a Y decimal value sign (×)              //Returns the value of the symbol representing the number x sqrt (x)   &nbs P        //returnReturn the square root of a number truncate (x, y)    //Returns the result of the number x truncated to Y decimal places   Two, the aggregate function (in a select query commonly used in GROUPBY clauses) AVG (COL) returns the mean count of the specified column ( 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 all the values of the specified column and Group_concat (CO L)           Return the result of a combination of column values belonging to a set   three, the String function ASCII (char)   Returns the ASCII value of the character bit_length (str ) returns the bit length of the string concat (S1,S2...,SN)   joins the S1,S2...,SN to a string Concat_ws (SEP,S1,S2...,SN) to concatenate s1,s2...,sn into a string, using the sep character interval i Nsert (STR,X,Y,INSTR)               The string str starts at position x, the substring of y characters is replaced with the string instr, and the result is returned Find_in_ SET (str,list) parses a comma-delimited list of lists, if STR is found, returns the position of STR in the 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 in the string str length (s) returns the number of characters in Str ltrim (str)               & nbsp                 Remove the opening space from the string str position (SUBSTR,STR)         &NB Sp   Return substring substr in string str firstOccurrences of quote (STR)                             with Backslash escape s Single quotation mark in TR repeat (STR,SRCHSTR,RPLCSTR)     return string str repeats x times results Reverse (str)             & nbsp         return reversed string str results right (STR,X)                       Returns the rightmost X character in the string str rtrim (str)                       returns the string str tail The Space strcmp (S1,S2) compares strings S1 and S2trim (str) to remove all whitespace from string headers and tails UCase (str) or upper (str)       Returns all characters in the string str Results in uppercase  lower (str)                               return string str all characters converted to lowercase result   four, date and time function curdate () or current_date ()     return current date curtime () or Current_time ()       Returns the current time date_add (date,interval int keyword)   Returns the date plus the result of the interval int (int must be formatted according to the keyword), such as: Selectdate_add (current_date,interval 6 MONTH);D Ate_format (date,fmt)       Format Date value date_sub (date,interval int keyword) According to the specified FMT format Returns the result of date plus interval int (int must be formatted by keyword), such as: Selectdate_sub (current_date,interval 6 MONTH);D Ayofweek (DATE)     Returns the day ordinal of the week represented by date (1~7) dayofmonth (date)   Return date is the day of the one month (1~31) dayofyear (date)     Return date is the day of the Year (1~366) dayname (date)     Returns the week name of date, such as: Selectdayname (current_date); From_unixtime (TS,FMT)   Formats the UNIX timestamp tshour (time)     timeout value (0~23) MINUTE (timing), based on the specified FMT format (timestamp)     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. such as: Selectmonthname (current_date); Now ()     Returns the current date and time quarter (date)     Returns a date in the quarter of the year (1~4), such as Selectquarter (current_date); WEEK (date)     Return date is the first week of the year (0~53) years (date)     Returns the year of date (1000~9999)   Some examples: Get current system time: Selectfrom_unixtime (Unix_timestamp ()); SELECT EXTRACT (Year_month from current_date); SELECT EXTRACT (Day_second from current_date); SELECTEXTRACT (Hour_minute from current_date); Returns the difference between two date values (number of months): Selectperiod_diff (200302,199802); age in MySQL: SELECT date_ FORMAT (From_days (To_days (now ())-to_days (birthday)), '%Y ') +0 as Agefrom employee; Thus, if Brithday is a future month or year, the 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 Agefrom employee , cryptographic functions Aes_encrypt (Str,key)   return with key key to string STR using the Advanced Encryption Standard algorithm to encrypt the result, The result of calling Aes_encrypt is a binary string, stored in BLOB type Aes_decrypt (Str,key)   returned with key key to string STR using the Advanced Encryption Standard algorithm to decrypt the result decode (Str,key)     Use key as the key to decrypt the cryptographic string Strencrypt (Str,salt)   Use the Unixcrypt () function, with the keyword salt (a string that uniquely determines the password, like a key) Encrypted string Strencode (Str,key)     uses key as the key to encrypt the string str, the result of calling encode () is a binary string, which is stored as BLOB type MD5 ()     Computes the string str MD5 checksum password (str)     Returns the encrypted version of the string str, this encryption process is irreversible, and the UNIX password encryption process uses a different algorithm. SHA ()     computed string STR's secure hashing Algorithm (SHA) checksum   Example: 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 ');  vi, control flow function MySQL has 4 functions for conditional operation, which can implement the conditional logic of SQL and allow the developer to transform some application business logic into the database background.  mysql Control Flow function: Case When[test1] then [RESULT1] ... else [default] END if TESTN is true, return RESULTN, otherwise return defaultcase [test] when[val1] Then [result] ... else [default] END   returns RESULTN if test and valn are equal, otherwise returns DEFAULTIF (test,t,f)   if test is true, returns T; Fifnull (ARG1,ARG2 If Arg1 is not empty, return arg1, otherwise return Arg2nullif (ARG1,ARG2) If ARG1=ARG2 returns NULL, otherwise return arg1  the first of these functions is ifnull (), which has two parameters, And the first parameter is judged. If the first argument is not NULL, the function returns the first argument to the caller, and if it is null, the second argument is returned. such as: Selectifnull, Ifnull (null,10), ifnull (4*null, ' false '); the  nullif () function will verify that the supplied two arguments are equal, and if they are equal, return NULL if not equal, The first argument is returned. such as: Selectnullif, Nullif (' A ', ' B '), Nullif (2+3,4+1);  and many scripting languages provide the if () function, MySQL's if () function can also establish a simple condition 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 argument. such as: Selectif (1<10,2,3), if (56>100, ' true ', ' false '), the  if () function is only appropriate when there are only two possible outcomes. In the real world, however, we may find that multiple branches are needed in a conditional test. In this case, MySQL provides the cases function, which is the same as the PHP and Perl language switch-case conditional routines. The format of the case function is somewhat complex, usually as follows: case [expression to being 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, the next is a series of when-then blocks, the first parameter of each block specifies the value to compare, if true, The result is returned. All When-then blocks will end with the else block, and when end ends all external case blocks, if each of the preceding blocks does not match, the default result specified by the else block is returned. If you do not specify an else block and all when-then comparisons are not true, MySQL will return null. The case function has another syntax, which is sometimes very convenient to use, as follows: casewhen [conditional test 1] Then [result 1]when [conditional test 2] Then [result 2]else [D Efault Result]end This condition, the result of the return depends on whether the appropriate condition test is true. Example: Mysql>select case ' green '        when ' red ' then ' Stop '        when ' green ' TH EN ' Go ' end; selectcase 9 when 1 then ' a ' while 2 Then ' B ' ELSE ' n/a ' END; SELECT case When (=4) "OK" when (+/-) <>4 then ' not OK ' END asstatus; SELECT name,if (IsActive =1), ' activated ', ' inactive ') as RESULT from Userlogininfo; SELECT Fname,lname, (math+sci+lit) as total,case when (Math+sci+lit) < "D" when (math+sci+lit) between and 150 Then ' C ' when (math+sci+lit) between 151 and "B ' ELSE ' A ' End as grade from Marks; selectif (' Sue ' ts ') =upass, ' Allow ', ' deny ') as Loginresult from users whereuname = ' sue '; #一个登陆验证   vii. Format function date_format (DATE,FMT) & nbsp Formatted date value in format (x, y)     format x as a comma-separated sequence of numbers, Y is the number of decimal digits of the result Inet_aton (IP)     Returns the numeric representation of the IP address inet_ NTOA (num)     Returns the IP address represented by the number Time_format (TIME,FMT)   format time value by string FMT The simplest is the format () function, It can be formatted with a large numeric value as an easy-to-read sequence with a comma interval. 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);  VIII, type conversion function for data type conversions, MySQL provides the cast () function, which converts a value to the specified data type. Types are: 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 ');  IX, System Information function database ()     Returns the current database name Benchmark (count,expr)   repeatedly runs the expression expr Count connection_id ()     Returns the current customer's connection idfound_rows ()     Returns the total number of rows retrieved by the last SELECT query user () or system_user ()   Returns the current login user name version ()     Returns an example of a MySQL server: SELECT DATABASE () , VERSION (), USER (); SELECT BENCHMARK (9999999,log (rand () *pi ())), #该例中, MySQL calculates the LOG (rand () *pi ()) expression 9,999,999 times.

MySQL common function math functions, cryptographic functions, etc. (Go-favorites)