SQL函数
表： people
| ID | name | age | sex | dname |
|---|---|---|---|---|
| 1 | 小明 | 20 | 男 | 研发部 |
| 2 | 小红 | 17 | 女 | 研发部 |
| 3 | 小李 | 18 | 男 | 销售部 |
| 4 | 小南 | 16 | 女 | 销售部 |
| 5 | 小门 | 18 | 男 | 人事部 |
| 6 | 小彦 | 21 | 男 | 人事部 |
一、聚合函数，字符串拼接
1、sql语句
SELECT
GROUP_CONCAT(p.name ORDER BY p.age DESC SEPARATOR ',') AS names
FROM
`people` p
GROUP BY
p.sex
// 截取前三个
substring_index(GROUP_CONCAT(p.name ORDER BY p.age DESC SEPARATOR ','), ',', 3)
2、查询结果
| names |
|---|
| 小红,小南 |
| 小彦,小明,小门,小李 |
二、字符串函数
| 关键字 | 说明 |
|---|---|
| LENGTH(s) | 返回字符串s的字节数 |
| CHAR_LENGTH(s) | 返回字符串s的字符数 |
| CHARACTER_LENGTH(s) | 返回字符串s的字符数 |
| CONCAT(s1,s2,sn) | 多个字符串拼接 |
| CONCAT_WS(x,s1,s2,sn) | 多个字符串拼接, x为分隔符 |
| FIELD(s,s1,s2,sn) | 返回字符串s，在字符串列表中的位置 |
| TRIM(s), LTRIM, RTRIM | 字符串s中的空格 |
| MID(s,n,len) | 从字符串s的n位置，截取len长度。 同SUBSTRING(s,n,len) |
| POSITION(s1 IN s) | 返回字符串s1在s中的位置 |
| REPLACE(s,s1,s2) | 将字符串s中的 s2替换成s1 |
| REVERSE(s) | 字符串反转 |
| RIGHT(s,n) | 返回字符串s的后n个字符 |
| STRCMP(s1,s2) | s1和s2进行比较， 相等返回0 s1>s2返回1 s1<s2返回-1 |
| SUBSTR(s,start,length) | 从字符串的start位置，截取lenth长度 |
| SUBSTRING(s,start,length) | 从字符串的start位置，截取lenth长度 |
三、日期函数
| 关键字 | 说明 |
|---|---|
| UNIX_TIMESTAMP() | 返回从1970-01-01 00:00:00到当前的毫秒值 |
| UNIX_TIMESTAMP(date_str) | 将指定日期转为毫秒值的时间戳 |
| FROM_UNIXTIME(timestamp, date_format) | 将时间戳转为指定的日期格式，%Y-%m-%d %H:%i:%s |
| CURDATE() \ CURRENT_DATE() | 返回当前日期 |
| CURTIME() \ CURRENT_TIME() | 返回当前时间 |
| CURRENT_TIMESTAMP() | 返回当前日期和时间 |
| DATE() | 日期字符串中获取年月日 |
| DATEDIFF(d1, d2) | 计算日期d1和d2之间相隔的天数 |
| TIMEDIFF(t1, t2) | 计算两个时间之间，相差多少秒 |
| DATE_FORMAT(d, f) | 按表达式f要求显示日期，返回字符串 |
| STR_TO_DATE(s, f) | 将字符串转为日期 |
| DATE_SUB(date, INTERVAL expr type) | 从date减去指定的时间 从日期中减去两天,例如：DATE_SUB(date_field, INTERVAL 2 DAY) |
| DATE_ADD(date, INTERVAL expr type) | 从date增加指定的时间 SECOND:秒 MINUTE:分 HOUR:时 DAY:天 WEEK:周 MONTH:月 YEAR:年 |
| EXTRACT(type FROM d) | 从日期d中获取指定的type值, 例如：EXTRACT(YEAR FROM '2022-03-09 12:30:49') -> 2022 |
| LAST_DAY(d) | 返回指定日期当前月份的最后一天 |
| MAKEDATE(year, n) | 返回指定year,第n天的日期 例如： MAKEDATE(2022, 3) -> 2022-01-03 |
四、控制流函数
| 关键字 | 说明 |
|---|---|
| IF(expr, v1, v2) | 如果表达式expr成立，则返回结果v1，否则返回v2。 |
| IFNULL(v1, v2) | 如果v1的结果为NULL，则返回v2。 |
| ISNULL(v1) | 字段是否为NULL |
| NULLIF(v1, v2) | 比较两个字符串，如果字符串v1和v2相等，返回NULL。否则返回v1。 |
| CASE field WHEN 1 THEN '结果是1' WHEN 5 THEN '结果是5' WHEN 10 THEN '结果是10' ELSE '其他' END; |
条件判断，类似IF(field==1, 1) |
| CASE WHEN field=1 THEN '结果是1' WHEN field=5 THEN '结果是5' WHEN field=10 THEN '结果是10' ELSE '其他' END; |
条件判断，类似IF(field==1, 1) |
五、窗口函数（mysql8.0支持）
/*用法示例*/
SELECT
`name`,
`age`,
ROW_NUMBER() OVER(PARTITION BY `dname` ORDER BY `age`) AS age_rank1,
RANK() OVER(PARTITION BY `dname` ORDER BY `age`) AS age_rank2,
DENSE_RANK() OVER(PARTITION BY `dname` ORDER BY `age`) AS age_rank3,
LAG(age, 1, 18) OVER(PARTITION BY `dname` ORDER BY `age`) AS age2,
LEAD(age, 1, 18) OVER(PARTITION BY `dname` ORDER BY `age`) AS age3,
FROM
`people`
| 关键字 | 说明 |
|---|---|
| ROW_NUMBER() | 排序后的序列号, 数值相同时，没有并列名次 |
| RANK() | 排序后的序列号, 数值相同时，有并列名次 |
| DENSE_RANK() | 排序后的序列号, 数值相同时，有并列名次，之后会跳名次 |
| LAG(field, n, default) | 返回当前行的上n行数据的field字段，没有上n行，则返回default值。 |
| LEAD(field, n, default) | 返回当前行的下n行数据的field字段，没有下n行，则返回default值。 |
六、存储过程(函数)
-- 创建
DELIMITER $$(自定义结束符号)
CREATE PROCEDURE 函数名()
BEGIN
-- 定义局部变量
DECLARE val_01 varchar(20) DEFAULT '李四';
-- 定义用户变量(当前会话，全局访问)
SET @val_02 = '张三'
-- 变量赋值
SET val_01 = '张三'
-- SQL结果，赋值给 val_01
SELECT `name` INTO val_01 FROM `people` WHERE `ID` = 1
-- 输出变量值
SELECT val_01;
END $$(自定义结束符号)
DELIMITER;
-- 调用
call 函数名();
