MySQL common functions

MySQL common functions

I. mathematical functions

1. Absolute value Function abs (value)

Mysql> select abs (-120 );
+ ----------- +
| Abs (-120) |
+ ----------- +
| 1, 120 |
+ ----------- +
1 row in set (0.00 sec)

2. floor function floor (value) to obtain the maximum integer smaller than value

Mysql & gt; select floor (23.8 );
+ ------------- +
| Floor (10, 23.8) |
+ ------------- +
| 23 |
+ ------------- +
1 row in set (0.00 sec)

3. Use the ceiling function ceiling (value) to obtain the smallest integer greater than value.

Mysql> select ceiling (23.8 );
+ --------------- +
| Ceiling (23.8) |
+ --------------- +
| 24 |
+ --------------- +
1 row in set (0.00 sec)

4. Rounding the function round (value, position), rounding the number of digits after the decimal point

Mysql> select round (23.27); -- the default value is 0.
+ -------------- +
| Round (23.27) |
+ -------------- +
| 23 |
+ -------------- +
1 row in set (0.00 sec)

Mysql> select round (23.27, 1 );
+ ---------------- +
| Round (23.27, 1) |
+ ---------------- +
| 1, 23.3 |
+ ---------------- +
1 row in set (0.00 sec)

5. Random Number function rand () to obtain a random number between 0 and 1

Mysql> select rand ();
+ --------------------- +
| Rand () |
+ --------------------- +
| 1, 0.12718137365503365 |
+ --------------------- +
1 row in set (0.00 sec)

Ii. String Functions

1. Connection function concat (str1, str2)

Mysql> select concat ('hello', 'World ');
+ ------------------------- +
| Concat ('hello', 'World') |
+ ------------------------- +
| Helloworld |
+ ------------------------- +
1 row in set (0.00 sec)

2. concat_ws (separator, str1, str2)

Mysql> select concat_ws ('#', 'Hello', 'World ');
+ -------------------------------- +
| Concat_ws ('#', 'Hello', 'World') |
+ -------------------------------- +
| Hello # world |
+ -------------------------------- +
1 row in set (0.00 sec)

3. length (str)

Mysql> select length ('helloworld me ');
+ ------------------------ +
| Length ('helloworld me') |
+ ------------------------ +
| 12 |
+ ------------------------ +
1 row in set (0.00 sec)

4. Character Count char_length (str)

Mysql> select char_length ('helloworld me ');
+ ----------------------------- +
| Char_length ('helloworld me') |
+ ----------------------------- +
| 12 |
+ ----------------------------- +
1 row in set (0.00 sec)

5. String judgment Function

A. IF (exp1, exp2, exp3): IF exp1 is true, exp2 is returned. IF exp1 is false, exp3 is returned.

Mysql> select if (2> 1, 'Hello', 'World ');
+ ------------------------- +
| If (2> 1, 'Hello', 'World') |
+ ------------------------- +
| Hello |
+ ------------------------- +
1 row in set (0.00 sec)

Mysql> select if (null = 1, 'Hello', 'World ');
+ ---------------------------- +
| If (null = 1, 'Hello', 'World') |
+ ---------------------------- +
| World |
+ ---------------------------- +
1 row in set (0.08 sec)

B. IFNULL (exp1, exp2): If exp1 IS NOT NULL, exp1 IS returned; otherwise, exp2 IS returned.

Mysql> select IFNULL ('hello', 'World ');
+ ------------------------- +
| IFNULL ('hello', 'World') |
+ ------------------------- +
| Hello |
+ ------------------------- +
1 row in set (0.00 sec)

Mysql> select IFNULL (null, 'World ');
+ ---------------------- +
| IFNULL (null, 'World') |
+ ---------------------- +
| World |
+ ---------------------- +
1 row in set (0.06 sec)

C. NULLIF (exp1, exp2): If exp1 = exp2, NULL is returned; otherwise, exp1 is returned.

Mysql> select NULLIF ('hello', 'Hello ');
+ ------------------------- +
| NULLIF ('hello', 'Hello') |
+ ------------------------- +
| NULL |
+ ------------------------- +
1 row in set (0.00 sec)

Mysql> select NULLIF ('hello', 'hello11 ');
+ --------------------------- +
| NULLIF ('hello', 'hello11') |
+ --------------------------- +
| Hello |
+ --------------------------- +
1 row in set (0.00 sec)

6. String Conversion Functions

A. LTRIM (exp1): removes spaces starting with the string (LEFT) in exp1.

Mysql> select LTRIM ('hello ');
+ ------------------ +
| LTRIM ('hello') |
+ ------------------ +
| HELLO |
+ ------------------ +
1 row in set (0.00 sec)

B. RTRIM (exp1): remove the space at the end of the string (RIGHT) in exp1

Mysql> select LTRIM ('hello ');
+ -------------------------- +
| LTRIM ('hello') |
+ -------------------------- +
| HELLO |
+ -------------------------- +
1 row in set (0.00 sec)

C. TRIM (exp1): removes spaces at the beginning and end of exp1.

Mysql> select TRIM ('hello ');
+ ------------------------- +
| TRIM ('hello') |
+ ------------------------- +
| HELLO |
+ ------------------------- +
1 row in set (0.00 sec)

7. string search functions

A. SUBSTRING_INDEX (exp1, delim, count), where exp1 is a string, delim is a segmentation symbol, and count represents the first few style symbols

Mysql> select substring_index ('2017 .qq..com ','. ', 1 );
+ --------------------------------------- +
| Substring_index ('1970 .qq..com ','. ', 1) |
+ --------------------------------------- +
| 1, 1121 |
+ --------------------------------------- +
1 row in set (0.00 sec)

B. SUBSTRING (exp1, pos, len): exp1 is a string, pos is a position, len is the length

Mysql> select substring ('127 .qq.com ', 1121 );
+ ------------------------------ +
| Substring ('1970 .qq.com ', 1121) |
+ ------------------------------ +
| 1121. qq. c |
+ ---------------------------- +
1 row in set (0.00 sec)

8. Date operation functions
Date Format Conversion Function
Convert the string to the date type, DATE_FORMAT () or STR_TO_DATE (). The format of the two functions is as follows:
DATE_FORMAT (expr1, format)
STR_TO_DATE (expr1, format)
Commonly used Date format YYYY-MM-DD HH: MM: SS corresponds to format % Y-% m-% d % H: % I: % S
Date calculation functions
CURDATE (): returns the date of the day, format: YYYY-MM-DD
NOW (): returns the current date and time in the format of YYYY-MM-DD HH: MM: SS
DATE_ADD (date, INTERVAL expr unit): expr expression, unit is the unit,

For example:
DATE_ADD ('2017-08-20 ', INTERVAL-1 DAY), return: 2013

9. Date calculation functions
MONTH (date): returns the number of months of the date, for example, MONTY ('1970-08-20 '). Return Value: 8.
DAY (date): returns the number of days of the date, for example, DAY ('2017-08-20 '), and returns: 20
YEAR (date): returns the number of years of the date, for example, YEAR ('1970-08-20 '). The return value is 2013.
TO_DAYS (date): the number of days corresponding to the date, for example, TO_DAYS ('2017-10-07 '). The return value is 2008.
WEEK (date): returns the day of the WEEK corresponding to the date, for example, WEEK ('2017-08-20 '), and 2
DATEDIFF (date1, date2): returns the number of days difference for the date1-date2, for example:
DATEDIFF ('2017-08-20 ', '2017-08-18 13:30:14'), return: 2

10. type conversion functions
CAST (expr AS type): Convert expr to a certain type
CONVERT (expr, type): CONVERT expr to a certain type
CONVERT (expr USING transcoding_name): CONVERT the expr character set to a character set

This article permanently updates the link address: