函数补充

函数

字符串函数

TRIM

删除指定字符

//leading 前面的
mysql> SELECT TRIM(LEADING '?' FROM '??MySQL');
+----------------------------------+
| TRIM(LEADING '?' FROM '??MySQL') |
+----------------------------------+
| MySQL                            |
+----------------------------------+
1 row in set (0.04 sec)
//trailing 后面的
mysql> SELECT TRIM(TRAILING '?' FROM '???MySQL????');
+----------------------------------------+
| TRIM(TRAILING '?' FROM '???MySQL????') |
+----------------------------------------+
| ???MySQL                               |
+----------------------------------------+
1 row in set (0.00 sec)

//两边的
mysql> SELECT TRIM(BOTH '?' FROM '???MySQL????');
+------------------------------------+
| TRIM(BOTH '?' FROM '???MySQL????') |
+------------------------------------+
| MySQL                              |
+------------------------------------+
1 row in set (0.01 sec)

SUBSTRING()字符串的截取

SUBSTRING(str, p, c) str要截取的字符串 p开始位置（从1开始）c 截取的个数
SUBSTRING(str, p,) 从p截取一直到最后

mysql> SELECT SUBSTRING('MySQL',1,2);
+------------------------+
| SUBSTRING('MySQL',1,2) |
+------------------------+
| My                     |
+------------------------+

//倒着
mysql> SELECT SUBSTRING('MySQL',-1);
+-----------------------+
| SUBSTRING('MySQL',-1) |
+-----------------------+
| L                     |
+-----------------------+

REPLACE()字符串的替换

mysql> SELECT REPLACE('???My ??SQL????','?','');
+-----------------------------------+
| REPLACE('???My ??SQL????','?','') |
+-----------------------------------+
| My SQL                            |
+-----------------------------------+
1 row in set (0.03 sec)

mysql>

LIKE()字符串的匹配

SELECT ‘MySQL’ LIKE ‘M%’;
这里%相当于其他语言的* 相当于任意字符
_相当于任意一个字符

mysql> SELECT 'MySQL' LIKE 'M%';
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.02 sec)

运算符 与函数

运算符 +、-、*、/， …

DIV、MOD是运算符

mysql> SELECT 5/2;
+--------+
| 5/2    |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 5 MOD 2;
+---------+
| 5 MOD 2 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

CEIL() 、FLOOR()

mysql> SELECT CEiL(30.1);
+------------+
| CEiL(30.1) |
+------------+
|         31 |
+------------+
1 row in set (0.02 sec)

mysql> SELECT FLOOR(30.1);
+-------------+
| FLOOR(30.1) |
+-------------+
|          30 |
+-------------+
1 row in set (0.00 sec)

比较函数与运算符

[NOT] BETWEEN .. AND …

mysql> SELECT 15 BETWEEN 1 AND 20;
+---------------------+
| 15 BETWEEN 1 AND 20 |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.02 sec)

mysql> SELECT 15 BETWEEN 1 AND 2;
+--------------------+
| 15 BETWEEN 1 AND 2 |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

[NOT] IN()

mysql> SELECT 10 IN(5,10,15);
+----------------+
| 10 IN(5,10,15) |
+----------------+
|              1 |
+----------------+
1 row in set (0.03 sec)

IS [NOT] NULL

mysql> SELECT NULL IS NULL
    -> ;
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

常用的日期时间函数

mysql> SELECT DATEDIFF('2013-3-12', '2014-3-12');
+------------------------------------+
| DATEDIFF('2013-3-12', '2014-3-12') |
+------------------------------------+
|                               -365 |
+------------------------------------+
1 row in set (0.03 sec)

mysql> SELECT DATE_FORMAT('2016-10-12','%m/%d/%Y');
+--------------------------------------+
| DATE_FORMAT('2016-10-12','%m/%d/%Y') |
+--------------------------------------+
| 10/12/2016                           |
+--------------------------------------+
1 row in set (0.00 sec)

信息函数

LAST_INSERT_ID() id号 主键号