| |
First, Mathematical Functions
1. The absolute value function abs (value)
mysql> select abs (-120);
+ ----------- +
| Abs (-120) |
+ ----------- +
| 120 |
+ ----------- +
1 row in set (0.00 sec)
2. Floor function floor (value), get less than the maximum integer value
mysql> select floor (23.8);
+ ------------- +
| Floor (23.8) |
+ ------------- +
| 23 |
+ ------------- +
1 row in set (0.00 sec)
3. The ceiling function ceiling (value), the smallest integer value greater than acquisition
mysql> select ceiling (23.8);
+ --------------- +
| Ceiling (23.8) |
+ --------------- +
| 24 |
+ --------------- +
1 row in set (0.00 sec)
4. rounding function round (value, position), rounded to decimal places
mysql> select round (23.27); - default is 0
+ -------------- +
| Round (23.27) |
+ -------------- +
| 23 |
+ -------------- +
1 row in set (0.00 sec)
mysql> select round (23.27,1);
+ ---------------- +
| Round (23.27,1) |
+ ---------------- +
| 23.3 |
+ ---------------- +
1 row in set (0.00 sec)
5. The random number function rand (), get a random number between 0-1
mysql> select rand ();
+ --------------------- +
| Rand () |
+ --------------------- +
| 0.12718137365503365 |
+ --------------------- +
1 row in set (0.00 sec)
Second, String Functions
1. Connect function concat (str1, str2)
mysql> select concat ( 'hello', 'world');
+ ------------------------- +
| Concat ( 'hello', 'world') |
+ ------------------------- +
| Helloworld |
+ ------------------------- +
1 row in set (0.00 sec)
2. delimited copula 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. Number of Bytes string length (str)
mysql> select length ( 'helloworld I');
+ ------------------------ +
| Length ( 'helloworld I') |
+ ------------------------ +
| 12 |
+ ------------------------ +
1 row in set (0.00 sec)
4. The number of characters in a string char_length (str)
mysql> select char_length ( 'helloworld I');
+ ----------------------------- +
| Char_length ( 'helloworld I') |
+ ----------------------------- +
| 12 |
+ ----------------------------- +
1 row in set (0.00 sec)
5. String Functions judgment
. A IF (exp1, exp2, exp3): If exp1 true return exp2; exp1 if false, return exp3
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, returns exp1, otherwise exp2
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, returns NULL, otherwise exp1
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): removed beginning exp1 string (LEFT) space
mysql> select LTRIM ( 'HELLO');
+ ------------------ +
| LTRIM ( 'HELLO') |
+ ------------------ +
| HELLO |
+ ------------------ +
1 row in set (0.00 sec)
b.RTRIM (exp1): remove exp1 the end of the string (RIGHT) space
mysql> select LTRIM ( 'HELLO');
+ -------------------------- +
| LTRIM ( 'HELLO') |
+ -------------------------- +
| HELLO |
+ -------------------------- +
1 row in set (0.00 sec)
c.TRIM (exp1): remove exp1 the leading and trailing spaces
mysql> select TRIM ( 'HELLO');
+ ------------------------- +
| TRIM ( 'HELLO') |
+ ------------------------- +
| HELLO |
+ ------------------------- +
1 row in set (0.00 sec)
7. string search function
a.SUBSTRING_INDEX (exp1, delim, count), which exp1 a string, delim as a split symbol, count represents the first of several style symbol
mysql> select substring_index ( '1121.qq..com',, 1 '.');
+ --------------------------------------- +
| Substring_index ( '1121.qq..com',, 1 '.') |
+ --------------------------------------- +
| 1121 |
+ --------------------------------------- +
1 row in set (0.00 sec)
. B SUBSTRING (exp1, pos, len): exp1 a string, pos for the location, len is the length
mysql> select substring ( '1121.qq.com', 1,9);
+ ------------------------------ +
| Substring ( '1121.qq.com', 1,9) |
+ ------------------------------ +
| 1121.qq.c |
+ ---------------------------- +
1 row in set (0.00 sec)
8. Date Manipulation Functions
Date Format Conversion Functions
Way to convert a string to a date format DATE_FORMAT () or STR_TO_DATE (), the following two functions
DATE_FORMAT (expr1, format)
STR_TO_DATE (expr1, format) a
Common date format YYYY-MM-DD HH: MM: SS format corresponding to% Y-% m-% d% H:% i:% S
Date arithmetic functions
CURDATE (): Returns the day's date in the format: YYYY-MM-DD
NOW (): Returns the current date and time in the format: YYYY-MM-DD HH: MM: SS
DATE_ADD (date, INTERVAL expr unit): expr expression, unit to unit,
E.g:
DATE_ADD ( '2013-08-20', INTERVAL -1 DAY), returns: 2013-08-19
9. The date arithmetic functions
MONTH (date): Returns the number of months of the date such MONTY ( '2013-08-20'), returns: 8
DAY (date): Returns the number of days of date of such DAY ( '2013-08-20'), return: 20
YEAR (date): Returns the number of years of the date, such as YEAR ( '2013-08-20'), returns: 2013
TO_DAYS (date): date corresponding to the number of days, for example TO_DAYS ( '2008-10-07'), return: 733 687
WEEK (date): Returns the date corresponding to the day of the week, such as: WEEK ( '2013-08-20'), returns: 2
DATEDIFF (date1, date2): Returns the number of days difference between date1-date2, for example:
DATEDIFF ( '2013-08-20', '2013-08-18 13:30:14'), returns: 2
10. Type Conversion Functions
CAST (expr AS type): expr converted to a certain type
CONVERT (expr, type): expr converted to a certain type
CONVERT (expr USING transcoding_name): expr character set is converted to a character set |
|
|
|