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

The main content of this article is "introduction to the usage of functions commonly used in MySQL5.6". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "introduction to the usage of functions commonly used in MySQL5.6".

Common functions are:

1. String function; mainly used to deal with strings.

two。 A numeric function; mainly used to deal with numbers.

3. Date and time functions; mainly used to deal with dates and events.

4. System information function; obtain system information.

1. Use the string function:

1.1 merge the string functions concat () and concat_ws ():

Concat (S1, s2jie... sn)

/ / this function concatenates the passed parameters and returns data of the merged string type. If one of the parameters is null, the return value is null.

Example:

Mysql > select concat ('my','s','ql')

+-- +

| | concat ('my','s','ql') | |

+-- +

| | mysql |

+-- +

1 row in set (0.00 sec)

Mysql > select concat ('my','s','ql',null)

+-+

| concat ('my','s','ql',null) |:

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

Mysql > select concat (curdate () 12.2)

+-+

| | concat (curdate (), 12.2) | |

+-+

| | 2017-03-1712.2 | |

+-+

1 row in set (0.00 sec)

The definition of concat_ws ():

Concat_ws (sep,s1,s2,...sn)

/ / compared with concat (), this function has an extra seq parameter that represents the delimiter, which not only concatenates the other parameters passed in, but also splits the strings by the delimiter.

The / / delimiter can be a string or other parameters. If the delimiter is null, the return result is null. Function ignores any parameter null after the separator.

Example:

Mysql > select concat_ws ('-', '010')

+-+

| | concat_ws ('-', '010 ") |

+-+

| | 010-87658907 | |

+-+

1 row in set (0.02 sec)

Mysql > select concat_ws (null,'010','87658907')

+-+

| | concat_ws (null,'010','87658907') |

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

Mysql > select concat_ws ('-', '010')

+-+

| | concat_ws ('-', '010 "). Nulllens" 87658907') |

+-+

| | 010-87658907 | |

+-+

1 row in set (0.00 sec)

1.2 compare string size function strcmp ():

Strcmp () is defined as:

Strcmp (str1,str2)

/ / return 1 if the parameter str1 is greater than str2,-1 if str1 is less than str2, and 0 if str1 is equal to str2

Example:

Mysql > select strcmp ('abc','abd'), strcmp (' abc','abc'), strcmp ('abc','abb')

+-+

| | strcmp ('abc','abd') | strcmp (' abc','abc') | strcmp ('abc','abb') |

+-+

| |-1 | 0 | 1 |

+-+

1 row in set (0.00 sec)

1.3 get the string length function length () and the character count function char_length ():

Length () is defined as follows:

Length (str)

Char_length (str) is defined as follows:

Char_length (str)

Example:

Mysql > select length ('mysql'), length (' China'), char_length ('mysql'), char_length (' China')

+-+

| | length ('mysql') | length (' Chinese character') | char_length ('mysql') | char_length (' China') |

+-+

| | 5 | 6 | 5 | 2 |

+-+

1 row in set (0.00 sec)

The / / string 'MySQL' has five characters, but occupies 6 bytes. This is because each string ends with\ 0. Both functions get the number of characters of the string rather than the size of the space occupied. Utf8 (three bytes in one Chinese), so two Chinese characters account for 6 bytes.

Case conversion of 1.4 letters upper () and lower ():

Uppercase conversion function: upper (s); ucase (s)

Lowercase conversion function: lower (s); lcase (s)

Example:

Mysql > select upper ('mysql'), ucase (' mYsql'), lower ('MYSQL'), lcase (' MYsql')

+-+

| | upper ('mysql') | ucase (' mYsql') | lower ('MYSQL') | lcase (' MYsql') |

+-+

| | MYSQL | MYSQL | mysql | mysql | |

+-+

1 row in set (0.00 sec)

1.5 find string:

Mysql provides a wealth of functions to find the location of the string. There are find_in_set () function, field () function, locate () function, position () function and instr () function. It also provides a function elt () to find the string at the specified location.

1.5.1 find_in_set () function that returns the position of the string:

The function is defined as:

Find_in_set (str1,str2)

/ / returns the position of the string that matches str1 in the string str2, and the parameter str2 string will contain several strings separated by commas.

Example:

Mysql > select find_in_set ('mysql','oracle,db2,mysql')

+-+

| | find_in_set ('mysql','oracle,db2,mysql') | |

+-+

| | 3 |

+-+

1 row in set (0.00 sec)

1.5.2 returns the field () function at the specified string position:

The function is defined as:

Filed (str,str1,str2...)

/ / returns the position of the first string that matches the string str.

Example:

Mysql > select field ('mysql','oracle','db2','redis','mysql')

+-- +

| | field ('mysql','oracle','db2','redis','mysql') | |

+-- +

| | 4 |

+-- +

1 row in set (0.00 sec)

1.5.3 returns the starting position where the substring matches:

There are three functions in mysql that can get the starting position of a substring match: the locate (), position (), and instr () functions.

Locate (str1,str) / / returns the starting position of the string str1 in the parameter str

Position (str1 instr) and instr (str,str1)

Example:

Mysql > select locate ('sql','mysql'), position (' sql' in 'mysql'), instr (' mysql','sql')

+-- +

| | locate ('sql','mysql') | position (' sql' in 'mysql') | instr (' mysql','sql') |

+-- +

| | 3 | 3 | 3 |

+-- +

1 row in set (0.00 sec)

1.5.4 the elt () function that returns the string at the specified location:

The function syntax is:

Elt (nGrainstr1 ~ str2...)

Example:

Mysql > select elt (2, "DB2", "" MySQL, "Oracle")

+-+

| | elt (2), "DB2", "MySQL", "" oracle ") |

+-+

| | mysql |

+-+

1 row in set (0.00 sec)

1.5.5 Select the make_set () function of the string:

The function is defined as:

Make_set (num,str1,str2...strn)

Example:

Mysql > select bin (5), make_set (5)

+-+

| | bin (5) | make_set (5) |

+-+

| | 101 | mysql,oracle |

+-+

1 row in set (0.00 sec)

/ / make_set () first converts the numeric num to a binary number, and then selects the corresponding string from the parameter str1,str2,...,strn in binary. Then read the value in binary order from right to left, and select the string if the value is 1, otherwise the string will not be selected.

1.6 truncate a substring from an existing string:

The functions that intercept substrings are: left (), right (), substring (), mid ()

1.6.1 truncate the substring from the left or right:

The function is defined as:

Left (str,num)

/ / returns the string str that contains the first num letter (counted from the left).

Right (str,num)

/ / returns a string containing the last num letters (from the right) in the string str.

Example:

Mysql > select left ('mysql',2), right (' mysql',3)

+-+ +

| | left ('mysql',2) | right (' mysql',3) | |

+-+ +

| | my | sql |

+-+ +

1 row in set (0.01 sec)

1.6.2 intercept a string of specified location and length:

You can intercept strings of specified location and length through the substring () and mid () functions.

The function syntax is:

Substring (str,num,len) / / returns a substring of length len starting at the num position in the string str.

Mid (str,num,len)

Example:

Mysql > select substring ('duansf',1,4), mid (' duansf',5,6)

+-+ +

| | substring ('duansf',1,4) | mid (' duansf',5,6) | |

+-+ +

| | duan | sf |

+-+ +

1 row in set (0.00 sec)

1.7 remove the leading and trailing spaces of the string:

The functions that remove the leading and trailing spaces of a string are: ltrim (), rtrim (), trim ()

1.7.1 remove the space at the beginning of the string:

The function is defined as follows:

Ltrim (str) / / returns the string that removes the space at the beginning

Example:

Mysql > select length (concat ('-', 'mysql','-'), length (concat ('-', ltrim ('mysql'),'-'))

+-+

| | length (concat ('-', 'mysql','-')) | length (concat ('-', ltrim ('mysql'),'-')) |

+-+

| | 9 | 8 |

+-+

1 row in set (0.00 sec)

1.7.2 remove the space at the end of the string:

Rtrim (str) / / returns a string that removes the space at the end.

Example:

Mysql > select length (concat ('-', 'mysql','-'), length (concat ('-', rtrim ('mysql'),'-'))

+-+

| | length (concat ('-', 'mysql','-')) | length (concat ('-', rtrim ('mysql'),'-')) |

+-+

| | 9 | 8 |

+-+

1 row in set (0.00 sec)

1.7.3 remove the leading and trailing spaces of the string:

Trim (str) / / returns a string with leading and trailing spaces removed

Example:

Mysql > select concat ('mysql') origi,length (concat ('mysql')) orilen, concat (trim ('mysql')) after, length (concat (trim ('mysql')) afterlen

+-+

| | origi | orilen | after | afterlen | |

+-+

| | mysql | 7 | mysql | 5 | |

+-+

1 row in set (0.00 sec)

1.8 replace string:

Implement the function of replacing strings, insert () and replace ()

1.8.1 use the insert () function:

The function is defined as:

Insert (str,pos,len,newstr)

The / / insert () function replaces the string with the length of len at the beginning of the pos position in the string str with the string newstr.

/ / if the value of the parameter pos exceeds the length of the string, the return value is the original string str.

/ / if the length of the len is greater than the length of the string left in the original str, replace it all starting from the position pos. If any parameter is null, the return value is null.

Example:

Mysql > select insert ('this is the mysql database system', 3pd5) bieming

+-+

| | bieming |

+-+

| | this is an oracle database system |

+-+

1 row in set (0.00 sec)

1.8.1 use the replace () function:

The function is defined as:

Replace (str,substr,newstr) / / replaces the substring substr in the string str with the string newstr.

Example:

Mysql > select replace ('this is the db2 database', 'db2','mysql') bieming

+-+

| | bieming |

+-+

| | this is a mysql database |

+-+

1 row in set (0.00 sec)

two。 Use numeric functions:

2.1 get a random number:

Get random numbers through the rand () and rand (x) functions. Both functions return random numbers between 0 and 1, where the number returned by the rand () function is completely random and the random value returned by the rand (x) function is exactly the same.

Example:

Mysql > select rand (), rand (), rand (2), rand (2)

+-+

| | rand () | rand () | rand (2) | rand (2) |

+-+

| | 0.6218820266629402 | 0.4098255535679176 | 0.6555866465490187 | 0.6555866465490187 |

+-+

1 row in set (0.00 sec)

2.2 functions that get integers:

In specific applications, if you want to get integers, you can use the ceil () and floor () functions to achieve.

The ceil () function is defined as:

The ceil (x) / / function returns the smallest integer greater than or equal to the numeric value x.

The floor () / / function returns the largest integer less than or equal to the numeric value x.

Example:

Mysql > select ceil, ceil (- 2.4), floor (4.3), floor (- 2.4)

+-+

| | ceil (4.3) | ceil (- 2.4) | floor (4.3) | floor (- 2.4) |

+-+

| | 5 |-2 | 4 |-3 |

+-+

1 row in set (0.00 sec)

2.3 intercept numeric functions:

You can intercept the decimal places of a value through truncate ():

The function is defined as:

Truncate (XBI y) / / returns the value x, leaving the y place after the decimal point

Example:

Mysql > select truncate (903.34244), truncate (903.342)

+-+-

| | truncate (903.342434) | truncate (903.342) |

+-+-

| | 903.34 | 900C |

+-+-

1 row in set (0.00 sec)

2.4 rounding functions:

Rounding numeric values can be achieved through the round () function:

Round (x)

/ / the function returns the value of x after rounding.

Round (XBI y)

/ / returns the value x to the value of y after the decimal point. It needs to be rounded when intercepting data.

Example:

Mysql > select round (902.53567), round (- 902.53567), round (902.53567), round (902.53567)

+-+

| | round (902.53567) | round (- 902.53567) | round (902.53567) | round (902.53567) |

+-+

| | 903 |-903 | 902.54 | 900 | |

+-+

1 row in set (0.02 sec)

3. Use the date and time functions:

3.1 function to get the current date and time:

3.1.1 get the current date and time (date + time):

In MySQL, you can get the current date and time through four functions, namely now (), current_timestamp (), localtime (), and sysdate (). These four functions not only get the current date and time, but also display them in the same format. Now () is recommended

Example:

Mysql > select now (), current_timestamp (), localtime (), sysdate ()

+-+

| | now () | current_timestamp () | localtime () | sysdate () |

+-+

| | 2017-03-17 15:52:24 | 2017-03-17 15:52:24 | 2017-03-17 15:52:24 | 2017-03-17 15:52:24 |

+-+

1 row in set (0.00 sec)

3.1.2 get the current date:

Get the curdate () and current_date () functions of the current date.

Example:

Mysql > select curdate (), current_date ()

+-+ +

| | curdate () | current_date () |

+-+ +

| | 2017-03-17 | 2017-03-17 | |

+-+ +

1 row in set (0.00 sec)

3.1.3 get the current time:

The function to get the current time, curtime () or current_time (); curtime () is recommended

Example:

Mysql > select curtime (), current_time ()

+-+ +

| | curtime () | current_time () |

+-+ +

| | 15:53:32 | 15:53:32 |

+-+ +

1 row in set (0.00 sec)

3.2 get the values of each part of the date and time:

In MySQL, the values of each part of the current date and time can be obtained through various functions, in which the year () function returns the year in the date, the quarter () function returns the quarter to which the date belongs, the month () function returns the month to which the date belongs, the week () function returns the week to which the date belongs, the dayofmonth () function returns the day of the current month, and the hour () function returns the hour of the time. The minute () function returns minutes of time, and the second () function returns seconds of time.

Example:

Mysql > select now (), year (now ()), quarter (now ()), month (now ()), week (now ()), dayofmonth (now ()), hour (now ()), minute (now ()), second (now ())

+- -+

| | now () | year (now ()) | quarter (now ()) | month (now ()) | week (now ()) | dayofmonth (now ()) | hour (now ()) | minute (now ()) | second (now ()) |

+- -+

| | 2017-03-17 15:54:07 | 2017 | 1 | 3 | 11 | 17 | 15 | 54 | 7 |

+- -+

1 row in set (0.00 sec)

3.2.1 the function of the month:

Example:

Mysql > select now (), month (now ()), monthname (now ())

+-+

| | now () | month (now ()) | monthname (now ()) |

+-+

| | 2017-03-17 15:54:43 | 3 | March |

+-+

1 row in set (0.03 sec)

3.2.2 functions about the week:

Example:

Mysql > select now (), week (now ()), weekofyear (now ()), dayname (now ()), dayofweek (now ()), weekday (now ())

+-+ +

| | now () | week (now ()) | weekofyear (now ()) | dayname (now ()) | dayofweek (now ()) | weekday (now ()) |

+-+ +

| | 2017-03-17 15:55:32 | 11 | 11 | Friday | 6 | 4 |

+-+ +

1 row in set (0.00 sec)

3.2.3 functions about days:

Example:

Mysql > select now (), dayofyear (now ()), dayofmonth (now ())

+-+

| | now () | dayofyear (now ()) | dayofmonth (now ()) |

+-+

| | 2017-03-17 15:56:12 | 76 | 17 | |

+-+

1 row in set (0.00 sec)

3.2.4 get the extract () of the specified value:

The function is defined as:

Extract (type from date)

/ / the above function gets the value of the specified type parameter type from the date and time parameter date. The values of type can be: year,month,day,hour,minute and second

Example:

Mysql > select now (), extract (year from now ()) year,extract (month from now ()) month,extract (day from now ()) day,extract (hour from now ()) hour,extract (minute from now) minute,extract (second from now ()) second

+-+

| | now () | year | month | day | hour | minute | second |

+-+

| | 2017-03-17 15:57:26 | 2017 | 3 | 17 | 15 | 57 | 26 |

+-+

1 row in set (0.00 sec)

3.3 functions for calculating date and time:

3.3.1 operate with default date and time:

Two functions, to_days () and from_days (), are used to operate with the default date and time.

To_days (date): this function calculates the number of days between the date parameter date and the default date and time (January 1, 000).

From_days (number): this function calculates the date and time after going through number days from the default date and time (January 1, 000).

Example:

Mysql > select now (), to_days (now ()), from_days (to_days (now ()

+-+

| | now () | to_days (now ()) | from_days (to_days (now () |

+-+

| | 2017-03-17 15:58:44 | 736770 | 2017-03-17 | |

+-+

1 row in set (0.00 sec)

3.3.2 operate with the specified date and time:

Adddate (date,n) function: this function calculates the date parameter date plus the date after n days.

Subdate (date,n) function: this function calculates the date parameter date minus n days.

Adddate (ddepartment interval expr type): returns the date parameter d plus the date after a period of time. The expression parameter expr determines the length of time, and the parameter type determines the object being operated on.

Subdate (dmoment interval expr type): returns the date after the date parameter d minus a period of time, and the expression expr determines the length of time. The parameter type determines the object being operated on.

Addtime (time,n): calculate the time after the time parameter time plus n seconds.

Subtime (time,n): calculates the time after the time parameter time minus n seconds.

Example 1:

Mysql > select curdate (), adddate (curdate (), 5), subdate (curdate (), 5)

+-+

| | curdate () | adddate (curdate (), 5) | subdate (curdate (), 5) | |

+-+

| | 2017-03-17 | 2017-03-22 | 2017-03-12 | |

+-+

1 row in set (0.00 sec)

Example 2:

Mysql > select curdate (), adddate (curdate (), interval '2je 3' year_month), subdate (curdate (), interval' 2je 3' year_month)

+-+

| | curdate () | adddate (curdate (), interval '2Magazine 3' year_month) | subdate (curdate (), interval' 2Magazine 3' year_month) |

+-+

| | 2017-03-17 | 2019-06-17 | 2014-12-17 | |

+-+

1 row in set (0.00 sec)

Example 3:

Mysql > select curtime (), addtime (curtime (), 5), subtime (curtime (), 5)

+-+

| | curtime () | addtime (curtime (), 5) | subtime (curtime (), 5) | |

+-+

| | 16:03:49 | 16:03:54 | 16:03:44 |

+-+

1 row in set (0.03 sec)

4. Use the system information function:

Select version (), database (), user ()

Example:

Mysql > select version (), database (), user ()

+-+

| | version () | database () | user () |

+-+

| | 5.6.27-log | mysql | mdba@localhost | |

+-+

1 row in set (0.00 sec)

At this point, I believe you have a deeper understanding of the "introduction to the usage of functions commonly used in MySQL5.6". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.