上一篇文章主要总结了常用语法,这一篇主要是总结一下常用函数,跟之前一样,控制篇幅,只讨论常用且核心的内容。(按照我自己的感受,函数这块说得太多也记不住的,先把最常用的学会,后面工作中有具体需要再谷歌吧)
1.常用聚合函数
sum 求和
count 计算频数
max 最⼤值 min 最⼩值
avg 平均值
同⼀⾏中进行多个字段⽐较:取最⼤值 greatest 最⼩值 least
--聚合函数使用格式
select 作为维度的字段,sum(需要聚合的字段)
where 限定条件
group by 作为维度的字段
having 过滤条件
2.常用字符串函数
1.字符串截取函数 substring(表达式,开始位置,长度)
--例如想要根据身份证获取一个人的出生年月日,假设身份证格式为xxxxxx19961225xxxx
select substring(`身份证`,7,14) as birthday
from A
--得到的结果即为19961225
2.字符拼接函数
concat(字段1,字段2...) :直接将多个字符串连接起来,形成一个单一的字符串
concat_ws(分隔符,字段1,字段2,...) CONCAT()的特殊形式。第一个参数是指字符串间的分隔符。分隔符的位置将会放在要连接的多个字符串之间。
--比如现有表A,需要查询表A中id name work_date三个字段拼在一起后的结果
SELECT CONCAT(id, name, work_date)
from A
;
select concat_ws(',','11','22','33')
--输出结果为 11,22,33
3.定位函数
POSITION(substr IN str) 定位到要查询的字符串第一次出现的位置:select position('a' in 'lanzhang'),若不存在,返回0
3.日期函数
1.提取时间函数
select extract(日期元素 from 日期类型字段); epoch、year、month、day、quarter、week、dow可获取秒、年、⽉、⽇、季度、第⼏周、周⼏。
2.返回当前时间、日期
select now(),curdate(),curtime() :分别为返回日期+时间 日期 时间
3.日期的舍与入
date_trunc(日期元素,日期) 日期元素主要为year、month、day、hour、minute、second
这里有一点要说一下,当这个日期元素为week时:
select date_trunc('week',now())
--今日为2021-03-23,则会舍到周,即取周一,将会输出 2021-03-22
4.数据类型转换函数
cast :CAST (字段名 AS 数据类型)
支持的数据类型为以下:
二进制 : BINARY :
字符型 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
除此之外,concert也有转换的功能,但他只在sql server中支持,感兴趣的可以自学https://www.w3school.com.cn/sql/func_convert.asp
5.常用特殊函数
1.将null转为其他值
coalesce :coalesce(字段名,需要转为的值) ,如:
select coalesce(a,0)--如果a为null,转为0
2.分⺟为0(转0为null):
null_if :null_if(字段名,当前者为何数值时转为null) ,如:
select b/null_if(a,0)--如果a=0,则转为null,null与任何运算结果仍为null
3.四舍五入
round: round(字段名,舍到哪一位小数) ,如:
select round(2.36,1);--2.4
select round(12.365,2);--12.37
select round(12.36,-1);--10,向小数点左边进位
4.替换
replace: replace ( 字段, 需要替换的内容 , 替换为啥内容 )
select replace(address,'区','')--将address字段中的 区 字符串全部置空