运算符和函数

字符函数

函数名称	描述
CONCAT(…)	字符连接
CONCAT_WS(设置分隔符，…)	使用指定的分隔符进行字符连接
FORMAT(…,保留位数)	数字格式化
LOWER()	转换成小写
UPPER()	转换成大写
LEFT(…,获取位数)	获取左侧字符
RIGHT(…,获取位数)	获取右侧字符
LENGTH()	获得字符串长度
LTRIM()	删除前空格
RTRIM()	删除尾空格
TRIM( [LEADING/TRAILING/BOTH ‘目标字符串’] )	删除前后空格/删除指定字符串（前或后）
SUBSTRING(字符串,起始位（可以负值）,截取位数)	进行字符串的截取
[NOT] LIKE	模式的匹配
REPLACE(字符串,要替换的字段,替换成)	替换

举个模式匹配的栗子：

mysql> SELECT 'MYSQL' LIKE 'M%';
+-------------------+
| 'MYSQL' LIKE 'M%' |
+-------------------+
|                 1 |
+-------------------+

# 匹配users表中username开头为T的记录
mysql> SELECT * FROM users WHERE username LIKE 'T%';

匹配%字段，这时系统会把%当做通配符处理，为了避免冲突，就有了ESCAPE

用法：LIKE '%1%%' ESCAPE '1'
上代码说明1后面的%不是通配符，而是当做百分号匹配

注：%：代表任意个字符，_：代表任意一个字符

数值运算符和函数

名称	描述
CEIL()	进一取整(向上取)
DIV	整数除法
FLOOR()	舍一取整(向下取)
MOD/%	取余数（模）
POWER(.,指数)	幂运算
ROUND()	四舍五入
TRUNCATE()	数字截取

比较运算符和函数

名称	描述
[NOT] BETWEEN…AND…	[不]在范围内
[NOT] IN()	[不]在列出范围内
IS [NOT] NULL	[不]为空

日期时间函数

名称	描述
NOW()	当前日期和时间
CURDATE()	当前日期
CURTIME()	当前时间
DATE_ADD(‘XXXX-X-X’,±DAY/WEEK/MONTH/YEAR)	日期变化
DATEDIFF(XX,XX)	日期差值
DATE_FORMAT(…,’%m/%d/%Y’)	日期格式化

栗子：

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
+--------------------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')       |
+--------------------------------------------------------+
| 2016年10月13日 09点:22分:42秒                          |
+--------------------------------------------------------+

信息函数

名称	描述
CONNECTION_ID()	连接ID
DATEBASE()	当前数据库
LAST_INSERT_ID()	最后插入记录的ID号
USER()	当前用户
VERSION()	版本信息

LAST_INSERT_ID():写入多条记录后，使用此语句只返回第一条写入的ID，而不是后面所写入的ID

聚合函数

特点：只有一个返回值

名称	描述
AVG()	平均值
COUNT()	计数
MAX()	最大值
MIN()	最小值
SUM	求和

加密函数

名称	描述
MD5()	信息摘要算法
PASSWORD()	密码算法

修改密码：mysql> SET PASSWORD=PASSWORD('comeon');

自定义函数

概念

用户自定义函数（user-defined function,UDF)是一种对MySQL扩展的途径，其用法和内置函数相同
自定义函数的两个必要条件：
1.参数（不一定）
2.返回值（都有）

这两个条件之间没有必然联系，例如某一函数可以没有参数，但是存在返回值，有两个参数的函数返回值是一个，参数的数量理论上不超过1024个

• 函数可以返回任意类型的值，同样可以接收这些类型的参数

创建UDF

语法结构：

CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body

• RETURNS：返回值的类型
• routine_body：当前的函数体

关于函数体：
1.函数体由合法的SQL语句构成
2.函数体可以是简单的SELECT语句或INSERT语句
3.函数体如果为复合结构则使用BEGIN…END语句
4.复合结构可以包含声明，循环，控制结构

查看UDF：
SHOW FUNCTION STATUS;
删除UDF：
DROP FUNCTION fct_name

创建不带参数的UDF

例如把之前日期函数的栗子变成UDF：

mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%YAD%mM%dD-%H:%i:%s');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT f1();
+-----------------------+
| f1()                  |
+-----------------------+
| 2016AD10M13D-09:42:44 |
+-----------------------+
1 row in set (0.00 sec)

创建带有参数的UDF

跟之前相比，向()内添加参数，直接看例子

mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
    -> RETURNS FLOAT(10,2) UNSIGNED
    -> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT f2(100,25);
+------------+
| f2(100,25) |
+------------+
|      62.50 |
+------------+
1 row in set (0.00 sec)

创建复合结构函数体的UDF

改变MYSQL分隔符：DELIMITER 分隔符

mysql> CREATE FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED 
BEGIN INSERT test(username) VALUES(username); RETURN LAST_INSERT_ID();
END//
Query OK, 0 rows affected (0.00 sec)