MySQL常用函数
1、字符函数
CONCAT() 连接字符
mysql> select concat('a','b','c');
+---------------------+
| concat('a','b','c') |
+---------------------+
| abc |
+---------------------+
1 row in set (0.00 sec)
CONCAT_WS() 用指定的分隔符连接
mysql> select concat_ws('-','a','b','c');
+----------------------------+
| concat_ws('-','a','b','c') |
+----------------------------+
| a-b-c |
+----------------------------+
FORMAT() 数字格式化
mysql> select format(1234.567,2); +--------------------+ | format(1234.567,2) | +--------------------+ | 1,234.57 | +--------------------+
LOWER() 字母转化为小写
mysql> select lower('ASDFGH');
+-----------------+
| lower('ASDFGH') |
+-----------------+
| asdfgh |
+-----------------+
UPPER() 字母转化为大写
mysql> select upper('asdfgh');
+-----------------+
| upper('asdfgh') |
+-----------------+
| ASDFGH |
+-----------------+
LEFT() 获取左侧字符
mysql> select left('asdfgh',3);
+------------------+
| left('asdfgh',3) |
+------------------+
| asd |
+------------------+
RIGHT() 获取右侧字符
+-------------------+
| right('asdfgh',3) |
+-------------------+
| fgh |
+-------------------+
LENGTH() 获取字符长度
mysql> select length('asdfgh');
+------------------+
| length('asdfgh') |
+------------------+
| 6 |
+------------------+
LTRIM() 删除前导空格
+------------------+
| ltrim(' china') |
+------------------+
| china |
+------------------+
RTRIM() 删除后导空格
+----------------------+
| rtrim(' china ') |
+----------------------+
| china |
+----------------------+
TRIM() 删除前导和后导空格或指定字符
mysql> select trim(' china ');
+---------------------+
| trim(' china ') |
+---------------------+
| china |
+---------------------+
mysql> select trim(leading'a' from 'aaaaab');
+--------------------------------+
| trim(leading'a' from 'aaaaab') |
+--------------------------------+
| b |
+--------------------------------+
mysql> select trim(trailing 'a' from 'baaaaa');
+----------------------------------+
| trim(trailing 'a' from 'baaaaa') |
+----------------------------------+
| b |
+----------------------------------+
REPLACE() 字符替换
mysql> select replace('aaaab','a','b');
+--------------------------+
| replace('aaaab','a','b') |
+--------------------------+
| bbbbb |
+--------------------------+
SUBSTRING() 截取字符
mysql> select substring('china',3);
+----------------------+
| substring('china',3) |
+----------------------+
| ina |
+----------------------+
[NOT] LIKE() 模式匹配
2、数值运算与函数
+、-、*、/ 就不多说了
CEIL() 进一取整
mysql> select ceil(12345.34); +----------------+ | ceil(12345.34) | +----------------+ | 12346 | +----------------+
FLOOR() 舍一取整
mysql> select floor(12345.34); +-----------------+ | floor(12345.34) | +-----------------+ | 12345 | +-----------------+
MOD 取余数，相当于%
mysql> select 9 mod 3; +---------+ | 9 mod 3 | +---------+ | 0 | +---------+
DIV 整数除法，相当于/
mysql> select 9 div 3; +---------+ | 9 div 3 | +---------+ | 3 | +---------+
POWER() 幂远算
mysql> select power(2,3); +------------+ | power(2,3) | +------------+ | 8 | +------------+
ROUND() 四舍五入
mysql> select round(12345,345); +------------------+ | round(12345,345) | +------------------+ | 12345 | +------------------+
TRUNCATE() 数字截取
mysql> select truncate(12345.3456,-1); +-------------------------+ | truncate(12345.3456,-1) | +-------------------------+ | 12340 | +-------------------------+
3、比较运算符函数
[NOT] BETWEEN .. AND .. 判断是否在某区间
[NOT] .. IN ..判断是否包括在某数组中
IS [NOT] NULL 判断是否为空
4、日期时间函数
NOW() 显示当前日期时间
CURDATE() 当前日期
CURTIME() 当前时间
DATE_ADD() 日期时间变更(可增减)
DATEDIEF() 日期时间的时间差
DATE_FORMAT() 日期时间格式化
5、信息函数
CONNECTION_ID() 显示连接ID
DATEBASE() 当前打开的数据库
LAST_INSERT_ID() 显示最后插入的记录
USER() 显示当前用户
VERSION() 显示版本号
6、聚合函数
AVG() 平均值
COUNT() 计数
MAX() 最大值
MIN() 最小值
SUM() 求和
7、自定义函数
CREATE FUNCTION fun_name
RETURNS 返回值的类型
routine_body
例：CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username)
RETURN
LAST_INSERT_ID()
END
DORP fun_name(); 删除自定义函数
DELIMITER 符号； 指定‘符号’为分割符
