函数
字符函数
CONCAT拼接字符串
SQL
SELECT CONCAT(name, '-', age) FROM student WHERE age = '18';
/** 结果如下：
如燕-18
李丽-18
李朗-18
沈涛-18
肖豹-18
*/
CONCAT_WS拼接字符串
SQL
SELECT CONCAT_WS('-', name, age, gender) FROM student WHERE age = '18';
/** 结果如下：
李丽-18-1
如燕-18-1
李朗-18-0
沈涛-18-0
肖豹-18-0
*/
UPPER转大写字母，LOWER转写字母
SQL
SELECT UPPER(hobby) FROM student;
SELECT LOWER(hobby) FROM student;
SUBSTR：截取字符
SQL
SUBSTR(string, start_position, length)
string：要从中提取子字符串的源字符串。start_position：起始位置，表示要提取的子字符串的起始位置。它可以是一个正整数或负整数。- 如果是
正整数，则表示从左侧开始的字符位置，其中第一个字符的位置为1。 - 如果是
负整数，则表示从右侧开始的字符位置，其中最后一个字符的位置为-1。
- 如果是
length：可选参数，表示要提取的子字符串的长度。如果省略该参数，则将提取从起始位置到源字符串的末尾的所有字符。
SQL
# hobby => pingpang
SELECT SUBSTR(hobby, 1) FROM student; # pingpang
SELECT SUBSTR(hobby, 2) FROM student; # ingpang
SELECT SUBSTR(hobby, 5) FROM student; # pang
SELECT SUBSTR(hobby, 1, 3) FROM student; # pin
SELECT SUBSTR(hobby, 2, 2) FROM student; # in
SELECT SUBSTR(hobby, -2) FROM student; # ng
SELECT SUBSTR(hobby, -3) FROM student; # ang 从倒数第三个字符截取到最后
SELECT SUBSTR(hobby, -3, 2) FROM student; # an 从倒数第三个字符开始截取两个字符
INSTR：返回字符串索引
- 若字符不在该字符串中，返回
0
SQL
# hobby => pingpang
SELECT INSTR(hobby, 'a') FROM student; # 6
SELECT INSTR(hobby, 'o') FROM student; # 0
去掉左右空格
SQL
SELECT TRIM(' pingpang ');
SELECT LTRIM(' pingpang ');
SELECT RTRIM(' pingpang ');
字符串补齐
SQL
SELECT LPAD('ping', 10, '*'); # ******ping
SELECT RPAD('ping', 8, '1'); # ping1111
替换
SQL
SELECT REPLACE('pingpang', 'p', '-'); # -ing-ang
SELECT REPLACE('pingpang', 'ping', '-'); # -pang
FORMAT：格式化数字
SQL
FORMAT(number, decimal_places)
number：要格式化的数字值。decimal_places：表示要保留的小数位数。
SQL
SELECT FORMAT(1234567.89, 1); # 1,234,567.9
SELECT FORMAT(1234.5678, 2); # 1,234.57
SELECT FORMAT(1234.56, 2); # 1,234.56
数学函数
ROUND 四舍五入
SQL
SELECT ROUND('3.2'); # 3
SELECT ROUND('3.18', '1'); # 3.2
SELECT ROUND('3.14', '1'); # 3.1
SELECT ROUND('3.18', '2'); # 3.18
CEIL 向上取整
SQL
SELECT CEIL('3.01'); # 4
SELECT CEIL('3.14'); # 4
SELECT CEIL('3.1'); # 4
FLOOR 向下取整
SQL
SELECT FLOOR('3.01'); # 3
SELECT FLOOR('3.94'); # 3
SELECT FLOOR('3.1'); # 3
TRUNCATE 截断小数位
TRUNCATE函数不进行四舍五入，而是直接截断小数部分
SQL
SELECT TRUNCATE('1.68', '1'); # 1.6
SELECT TRUNCATE('1.6896', '1'); # 1.6
SELECT TRUNCATE('123.456', '2'); # 123.456
MOD取余
SQL
SELECT MOD(10, 3); # 1
SELECT MOD(8, 4); # 0
日期函数
NOW 返回日期和时间
SQL
SELECT NOW(); # 1998-07-01 14:17:39
CURDATE 返回日期
SQL
SELECT CURDATE(); # 1998-07-01
CURTIME 返回时间
SQL
SELECT CURTIME(); # 14:20:52
CURRENT_TIMESTAMP 返回日期和时间
DATE：提取日期部分
TIME：提取时间部分
YEAR：提取年份
MONTH：提取月份
DAY：提取日期中的天数
HOUR：提取小时
MINUTE：提取分钟
SECOND：提取秒数
STR_TO_DATE
将日期格式的字符串转成指定格式的日期
示例
SQL
SELECT STR_TO_DATE('1988-9-9','%Y-%m-%d'); # 1988-09-09
DATE_FORMAT
将日期转换成指定字符串
示例
SQL
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日'); # 1998年07月07日
SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日'); # 1998年7月07日
| 格式符 | 功能 |
|---|---|
| %Y | 4位的年份 |
| %y | 2位的年份 |
| %m | 月份(01,02) |
| %c | 月份(1,2) |
| %d | 日(01,02) |
| %H | 小时(24小时制) |
| %h | 小时(12小时制) |
| %i | 分钟(00,01) |
| %s | 秒(00,01) |
DATEDIFF
计算两个日期之间相差的天数，表示expr1比expr2
晚多少天expr1和expr2是
日期或日期和时间表达式，计算中只使用值的日期部分
SQL
SELECT DATEDIFF(expr1, expr2));
示例：
SQL
SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-1'); # -1
SELECT DATEDIFF('1997-12-31 23:59:59','1997-11-30'); # 31
SELECT DATEDIFF('1997-12-31 23:59:59','1997-11-30 00:00:00'); # 31
DATE_ADD
计算一个时间和一个时间段相加的结果
SQL
SELECT DATE_ADD('1998-09-09',INTERVAL 1 YEAR); # 1999-09-09
SELECT DATE_ADD('1998-09-09',INTERVAL 1 MONTH); # 1998-10-09
SELECT DATE_ADD('1998-09-09',INTERVAL 1 DAY); # 1998-09-10
SELECT DATE_ADD('1998-09-09 20:30',INTERVAL 1 DAY); # 1998-09-10 20:30:00
SELECT DATE_ADD('1998-09-09 20:30',INTERVAL 1 HOUR); # 1998-09-09 21:30:00
SELECT DATE_ADD('1998-09-09 20:30',INTERVAL 1 MINUTE); # 1998-09-09 20:31:00
SELECT DATE_ADD('1998-09-09 20:30',INTERVAL 1 SECOND); # 1998-09-09 20:30:01
聚合函数
SUM
求和函数
SQL
SELECT SUM(age) FROM teachers;
AVG
求平均值函数
SQL
SELECT AVG(age) FROM teachers;
COUNT
输出查询到的数据条数
TIP
如果是COUNT(column_id)，则会返回column_id不为NULL的数据的条数
SQL
SELECT COUNT(*) FROM teachers WHERE age > 18;
MAX
输出查询到的列的最大值
SQL
SELECT MAX(age) FROM teachers WHERE age > 18; # 输出最大年龄
MIN
输出查询到的列的最小值
SQL
SELECT MIN(age) FROM teachers WHERE age > 18; # 输出最小年龄
自定义函数
创建自定义函数
- 如果函数体只有一行，可以省略
BEGIN和END
SQL
CREATE FUNCTION FUNCTION_NAME('参数1' '参数1类型', '参数2' '参数2类型', ... ) RETURNS '返回值类型'
BEGIN
DECLARE '变量名' '变量类型' default '变量默认值';
SET '参数设置';
'SQL语句';
RETURN 返回值;
END;
使用自定义函数
SQL
SELECT FUNCTION_NAME()
查看自定义函数
SQL
SHOW CREATE FUNCTION FUNCTION_NAME;
删除自定义函数
SQL
DROP FUNCTION FUNCTION_NAME;
示例
例如：(函数体只有一行，省略
BEGIN和END)
SQL
CREATE FUNCTION FORMAT_CURRENT_DATE(format VARCHAR(64)) RETURNS VARCHAR(64)
RETURN DATE_FORMAT(NOW(), format);
SELECT FORMAT_CURRENT_DATE('%y');
例如：(函数体多行，不能省略
BEGIN和END)
SQL
CREATE FUNCTION ADD_USER(userName VARCHAR(64), age TINYINT, gender TINYINT) RETURNS INT
BEGIN
INSERT INTO lyb.teachers(name, age, gender) VALUES (userName, age, gender);
RETURN LAST_INSERT_ID();
END;
# 调用函数
SELECT ADD_USER("宗主", 18, 1);
TIP
错误处理
创建自定义函数时可能会报以下错误：
shell
[HY000][1418] This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
错误原因： 这是因为开启了bin-log, 所以必须指定我们的函数是否是
- DETERMINISTIC 不确定的
- NO SQL 没有SQl语句，当然也不会修改数据
- READS SQL DATA 只是读取数据，当然也不会修改数据
- MODIFIES SQL DATA 要修改数据
- CONTAINS SQL 包含了SQL语句 其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
解决方法：
SQL
# 查询 log_bin_trust_function_creators
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
# 重点是这个命令，设置log_bin_trust_function_creators
SET GLOBAL log_bin_trust_function_creators=1;
# 再次查询 log_bin_trust_function_creators
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';