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

Foreword:

MySQL database provides a wealth of functions, such as our commonly used aggregate functions, date and string processing functions and so on. These functions can be used by SELECT statements and their conditional expressions, which can help users deal with the data in the table more conveniently and make the function of MySQL database more powerful. This article mainly introduces the usage of several kinds of commonly used functions.

1. Aggregate function

Aggregate function is a common type of function, which is listed as follows:

COUNT (col) Statistics the number of rows of query results MIN (col) query the minimum value of the specified column MAX (col) query the maximum value of the specified column SUM (col) summation, return the sum of the specified column AVG (col) to average, return the average of the specified column data 2. Numerical function

Numerical function is mainly to deal with numerical data to get the results we want, several commonly used are listed below, the specific use of you can try.

ABS (x) returns the absolute value of x BIN (x) returns the binary CEILING (x) of x returns the minimum integer value EXP (x) greater than x returns the x-th power of the value e (the base of the natural logarithm) FLOOR (x) returns the maximum integer value less than x GREATEST (x1 Magi x2 Magnum) returns the largest value LEAST in the set. Xn) returns the smallest value in the set LN (x) returns the natural logarithm of x (x) returns the logarithm of x based on y (x MOD) returns the module (remainder) PI () returns the value of pi (pi) RAND () returns a random value within 0 to 1, and can cause the RAND () random number generator to generate a specified value ROUND (x) by providing a parameter (seed) Y) returns the rounded value of the parameter x with the decimal place y (TRUNCATE (x)) returns the result of the number x truncated to the decimal place y

Some examples:

# ABS () function to find absolute values mysql > SELECT ABS (5), ABS (- 2.4), ABS (- 24), ABS (0) +-+ | ABS (5) | ABS (- 2.4) | ABS (- 24) | ABS (0) | +-+ | 5 | 2.4 | | 24 | 0 | +-# take the entire function CEIL (x) and CEILING (x) with the same meaning Returns the minimum integer values not less than x mysql > SELECT CEIL (- 2.5), CEILING (2.5) +-+-+ | CEIL (- 2.5) | CEILING (2.5) | +-+-+ |-2 | 3 | +-+-+ # remainder function MOD (x Y) returns the remainder of x divided by y mysql > SELECT MOD (63 SELECT MOD), MOD (120 mem10), MOD (15.5 L3) +-+ | MOD (63) | MOD (120) | MOD (15.5) | +-+ | 7 | 0 | 0.5 | +- When the-+ # RAND () function is called You can generate a random number mysql > SELECT RAND (), RAND (), RAND () between 0 and 1. +-+ | RAND () | +- +-+ | 0.24996517063115273 | 0.9559759106077029 | 0.029984071878701515 | +-+ 3. String function

String function can deal with string type data, which is of great use in program applications. Here are a few commonly used ones as follows:

LENGTH (s) calculates the string length function, returns the byte length of the string CONCAT (s1Magazine s2..) merges the string function, and returns the string generated by the connection parameter, which can be one or more INSERT (str,x,y,instr) to replace the string str from the x position, and the y-character-long substring to the string instr. Returns the result LOWER (str) converts letters in the string to lowercase UPPER (str) converts letters in the string to uppercase LEFT (str,x) returns the leftmost x characters in the string str RIGHT (str,x) returns the rightmost x characters in the string str (str) deletes the space REPLACE string replacement function on the left and right sides of the string, and returns the replaced new string SUBSTRING intercepts the string Returns the result of reversing the string str for REVERSE (str) of the specified length starting from the specified position

Some examples:

The return value of # LENGTH (str) function is the byte length of the string mysql > SELECT LENGTH ('name'), LENGTH (' database') +-+-+ | LENGTH ('name') | LENGTH (' database') | +-+-+ | 4 | 9 | + -+-# CONCAT (sl S2.) Function returns the string generated by the connection parameter. If any parameter is NULL, the return value is NULLmysql > SELECT CONCAT ('MySQL','5.7'), CONCAT (' MySQL',NULL). +-+ | CONCAT ('MySQL','5.7') | CONCAT (' MySQL') NULL) | +-- +-- + | MySQL5.7 | NULL | +-+-- + # INSERT (S1) X _ Football',2,4,'Play') returns the string S1, where the substring starts at the x position, and replaces s2mysql > SELECT INSERT ('Football',2,4,'Play') AS col1,-> INSERT (' Football',-1,4,'Play') AS col2,-> INSERT ('Football',3,20,'Play') AS col3 with a string of len characters long. +-+ | col1 | col2 | col3 | +-+ | FPlayall | Football | FoPlay | +-+ # UPPER,LOWER is the case conversion function mysql > SELECT LOWER ('BLUE') LOWER ('Blue'), UPPER (' green'), UPPER ('Green') +-+ | LOWER ('BLUE') | LOWER (' Blue') | UPPER ('green') | UPPER (' Green') | +- -+ | blue | blue | GREEN | GREEN | +- -+ # LEFT RIGHT is the function mysql > SELECT LEFT ('MySQL',2), RIGHT (' MySQL',3) to intercept the left or right string. +-+ | LEFT ('MySQL',2) | RIGHT (' MySQL') 3) | +-+ | My | SQL | +-+-+ # REPLACE (s S2) replace all strings s1mysql > SELECT REPLACE ('aaa.mysql.com','a','w') in the string s with the string S2 +-- + | REPLACE ('aaa.mysql.com','a' 'w') | +-+ | www.mysql.com | function SUBSTRING (s) Len) returns a substring of the same length as the len character from the string s, starting at the position nmysql > SELECT SUBSTRING ('computer',3) AS col1,-> SUBSTRING (' computer',3,4) AS col2,-> SUBSTRING ('computer',-3) AS col3,-> SUBSTRING (' computer',-5,3) AS col4 +-+ | col1 | col2 | col3 | col4 | +-+ | mputer | mput | ter | put | +-+ 4. Date and time functions CURDATE and CURRENT_DATE function are the same, return the date value of the current system CURTIME and CURRENT_TIME function is the same, return the time value of the current system NOW and SYSDATE function is the same, return the date and time value of the current system UNIX_TIMESTAMP to get the UNIX timestamp function, return an unsigned integer FROM_UNIXTIME based on the UNIX timestamp to convert the UNIX timestamp into a time format The inverse function with UNIX_TIMESTAMP is MONTH to get the month in the specified date, MONTHNAME to get the name of the month in the specified date, DAYNAME to get the name of the day of the week corresponding to the specified date, DAYOFWEEK to get the index location value of the week corresponding to the specified date, WEEK to get the week of the year on the specified date, whether the range of the return value is 0,52 or 1~53DAYOFYEAR to get the day of the year. The range of return value is the day of the month on which the specified date is obtained by 1~366DAYOFMONTH. The range of return value is the year of 1~31YEAR acquisition. The range of return value is that 1970~2069TIME_TO_SEC converts time parameters to seconds and SEC_TO_TIME converts seconds into time. It has the same function as DATE_ADD and ADDDATE, which are inverse functions of TIME_TO_SEC, and both add specified time intervals to the date. DATE_SUB and SUBDATE have the same function. All subtract the specified time interval ADDTIME time addition operation from the date, add the specified time SUBTIME time subtraction operation to the original time, subtract the specified time DATEDIFF from the original time to obtain the interval between two dates, return parameter 1 minus the value of parameter 2 DATE_FORMAT to format the specified date, and return the value WEEKDAY of the specified format according to the parameter to obtain the corresponding working day index of the specified date within a week.

Some examples:

The # CURDATE () and CURRENT_DATE () functions have the same effect, returning the current date in the format "YYYY-MM-DD" or "YYYYMMDD" to mysql > SELECT CURDATE (), CURRENT_DATE (), CURRENT_DATE () + 0 +-+ | CURDATE () | CURRENT_DATE () | CURRENT_DATE () + 0 | +-+ | 2019-10 -22 | 2019-10-22 | 20191022 | the +-+ # MONTH (date) function returns the month mysql > SELECT MONTH corresponding to the specified date ('2017-12-15') +-+ | MONTH ('2017-12-15') | +-+ | 12 | +-+ # DATE_ADD (date,INTERVAL expr type) and ADDDATE (date,INTERVAL expr type) have the same function Are used to perform date addition operations. Mysql > SELECT DATE_ADD ('2018-10-31 23 SECOND 59 SECOND SECOND) AS C1,-> DATE_ADD (' 2018-10-31 23 23 59 lane 51 SECOND) AS C2,-> ADDDATE ('2018-10-31 23 59 lane 59 SECOND) AS C3 +-+ | C1 | C2 | C3 | +- -+ | 2018-11-01 00:00:00 | 2018-11-01 00:01:00 | 2018-11-01 00:00:00 | +-+-+ -+ # DATEDIFF (date1 Date2) returns the number of days between the start time date1 and the end time date2 mysql > SELECT DATEDIFF ('2017-11-30) AS COL1,-> DATEDIFF (' 2017-11-30) AS col2 +-+ | COL1 | col2 | +-+-+ | 1 |-15 | +-+-+ # DATE_FORMAT (date The format) function displays the date value mysql > SELECT DATE_FORMAT ('2017-11-15 21 date 4500 AS col1% W% M% D% Y') AS col1,-> DATE_FORMAT (' 2017-11-15 21 date 4512) AS col2 according to the format specified by SELECT DATE_FORMAT. +-- +-- + | col1 | col2 | +-- -- + | Wednesday November 15th 2017 | 09 PM November 15th 2017 | +-- +-- + 5. Flow control function

Process control class functions can perform conditional operations to implement the conditional logic of SQL, allowing developers to convert some application business logic to the database backend, as listed below:

IF (test,t,f) returns t if test is true; otherwise returns fIFNULL (arg1,arg2) if arg1 is not empty, returns arg1, otherwise returns arg2NULLIF (arg1,arg2) if arg1=arg2 returns NULL Otherwise, return arg1CASE WHEN [test1] THEN [result1]... ELSE [default] END if testN is true, return resultN, otherwise return defaultCASE [test] WHEN [val1] THEN [result]... ELSE [default] END return resultN if test and valN are equal, otherwise return default

Some examples:

# IF statements allow you to execute a set of SQL statements mysql > SELECT IF (15 √','×') c2 if (STRCMP ('abc','ab'),' yes','no') c3 based on a condition or value result of an expression +-+-- +-+ | C1 | c2 | c3 | +-+ | 1 | × | yes | +-+-- + # IFNULL accepts two parameters. If the first parameter is not NULL, the first parameter is returned. Otherwise, the IFNULL function returns the second argument, mysql > SELECT IFNULL (513), IFNULL (NULL,'OK'). +-+-+ | IFNULL (5Power8) | IFNULL (NULL) 'OK') | +-+-+ | 5 | OK | +-+-+ # NULLIF function mysql > SELECT NULLIF (5-8), NULLIF (8-8) +-+-+ | NULLIF (5Power8) | NULLIF (8Power8) | +-+-+ | 5 | NULL | +-+-+ # CASE WHEN function mysql > SELECT CASE WHEN 1 > 0 THEN 'true' ELSE' false' END +-+ | CASE WHEN 1 > 0 THEN 'true' ELSE' false' END | +-+ | true | | +-+ mysql > SELECT CASE 11 WHEN 1 THEN 'one'-> WHEN 2 THEN' two' ELSE 'more' END | +-- + | CASE 11 WHEN 1 THEN 'one'WHEN 2 THEN' two' ELSE 'more' END | +-- -+ | more | +-+ 6. Encryption function

Encryption functions are mainly used to encrypt strings. Several commonly used functions are listed as follows:

ENCRYPT (str,salt) uses the UNIXcrypt () function to encrypt the string strENCODE (str,key) with the keyword salt (a string that uniquely determines the password, just like a key). The string str is encrypted using key as the key. The result of the call to ENCODE () is a binary string that stores the MD5 checksum PASSWORD (str) of the computed string str in BLOB type and returns the encrypted version of the string str This encryption process is irreversible and uses a different algorithm than the UNIX password encryption process. SHA () calculates the secure hash algorithm (SHA) checksum of string str

Some examples:

# ENCRYPT function mysql > SELECT ENCRYPT ('root','salt'); +-+ | ENCRYPT (' root','salt') | +-+ | saFKJij3eLACw | +-+ # MD5 () function mysql > SELECT MD5 ('123456') +-- + | MD5 ('123456') | +-+ | e10adc3949ba59abbe56e057f20f883e | +-- -+ # PASSWORD function mysql > select PASSWORD ('abcd') +-- + | PASSWORD ('abcd') | +-- + | * A154C52565E9E7F94BFC08A1FE702624ED8EFFDA | +- -- +

Summary:

This paper mainly introduces the function of common functions of MySQL and gives simple examples to illustrate the usage of some functions. In the actual environment, these functions may be used in nesting, and the method of use is much more complicated. I hope you will pay more attention to the role of each parameter when you use it.

Reference:

Http://c.biancheng.net/mysql/function/

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.