What are the common functions in MySQL 04/29 Update SLTechnology News&Howtos

What are the common functions in MySQL

2025-04-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Editor to share with you what the commonly used functions in MySQL are, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

In MySQL, functions can appear not only in select statements and their clauses, but also in update and delete statements.

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:

Although each database supports SQL, each database has its own supported functions.

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

In MySQL, you can concatenate the passed-in parameters into a string through the functions concat () and concat_ws ().

The syntax is defined as:

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:

The definition of concat_ws ():

Concat_ws (sep,s1,s2,...sn) / / this function has an extra seq parameter representing the delimiter compared to concat (), which not only concatenates the other parameters passed in, but also splits each string 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:

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 (' Chinese characters'), char_length ('mysql'), char_length (' Chinese characters') +-- +-+ | length ('mysql') | length (' Chinese character') | char_length ('mysql') | char_length (' Chinese character') | +- -+-+ | 5 | 4 | 5 | 4 | +-- -+-+ 1 row in set 2 warnings (0.00 sec) / / the string 'MySQL' has 5 characters But takes up 6 bytes of space. 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.

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

Letter size conversion function: upper (s); ucase (s)

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

Example:

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,mysql,db2') +-- + | find_in_set ('mysql','oracle,mysql Db2') | +-+ | 2 | +-+ 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:

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). 'redus') | +-+-+ | 101 | mysql Oracle | +-+-+ 1 row in set (0.00 sec) / / make_set () first converts the numeric num to a binary number Then select 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.00 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:

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:

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') bieming;+--+ | bieming | +-+ | the oracleql database system | +-+ 1 row in set, 1 warning (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 mysql database', 'mysql','db2') bieming;+-+ | bieming | +-+ | this is the db2 database | +-+ 1 row in set, 1 warning (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 (3), rand (3) +-+ | rand () | rand () | rand (3) | rand (3) | +- -- + | 0.9600886758045188 | 0.7006410161970565 | 0.9057697559760601 | 0.9057697559760601 | +- -+ 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 (4.3), ceil (- 2.5), floor (4.3), floor (- 2.5) +-+ | ceil (4.3) | ceil (- 2.5) | floor (4.3) | floor (- 2.5) | +- +-+ | 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.3434), truncate (903.343) +-+-+ | truncate (903.343434) | truncate (903.343) -1) | +-+ | 903.34 | 903.34 | +-+-- + 1 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 (903.53567), round (- 903.53567), round (903.53567), round (903.53567) +-+ | round (903.53567) | round (- 903.53567) | round (903.53567) | round (903.53567) -1) | +-+ | 904 |-904 | 903.54 | 900 | + -+ 1 row in set (0.00 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 () | +- -+ | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | +- -+ 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 () | +-+-+ | 2016-08-25 | 2016-08-25 | +-+-+ 1 row in set (2016 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 () | +-+-+ | 16:15:04 | 16:15:04 | +-+-+ 1 row in set (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 ()) | +- -+-+ | 2016-08 -25 16:27:37 | 2016 | 3 | 8 | 34 | 25 | 16 | 27 | 37 | +-- -+ 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 ()) | +- -+ | 0-08-25 16:29:37 | 8 | August | +-+ 1 row in set (0.00 sec) / / month () function returns a numeric month The monthname () function returns the month in English.

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 ()) | +- +-+ | 2016-08-25 16:34:35 | 34 | 34 | Thursday | 5 | 3 | +- -+-+ 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 ()) | +-- +-+ | 0-08-25 16:37:12 | 2016 | 25 | +-+ 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 | +- -- + | 2016-08-25 16:43:45 | 2016 | 8 | 25 | 16 | 43 | 45 | +- -+ 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 () | + -+ | 2016-08-25 16:50:30 | 736566 | 2016-08-25 | +- -+ 1 row in set (0.00 sec) / / specify the number of days between two dates Mysql > select now (), datediff (now (), '2000-12-01') +-+-+ | now () | datediff (now ()) '2000-12-01') | +-- + | 2016-08-25 16:52:16 | 5746 | + -+ 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) | +-+-- +-- + | 2016-08-25 | 2016-08-30 | 2016-08-20 | +-+ -+ 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'2p 3' year_month) | subdate (curdate () Interval'2 year_month) | +-+ | 2016-08- 25 | 2018-11-25 | 2014-05-25 | +-+ 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) | +-+ | 17:12:21 | 17:12:26 | 17:12:16 | +-+-- -+ 1 row in set (0.00 sec)

4. Use the system information function:

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

Example:

Mysql > select version (), database (), user () +-+ | version () | database () | user () | +-+ | 5.5.51-log | NULL | root@localhost | +- -+ 1 row in set (0.00 sec) / / get the last IDselect last_insert_id () of the auto_increment constraint These are all the contents of the article "what are the common functions in MySQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.