一、数据类型函数
1、数学函数
1）向上取整
-- 13
ceil(12.12)
2）向下取整
-- 12
floor(12.12)
3）四舍五入
round(num)
m：保留几位小数
round(num,m)
4）非四舍五入
-- m：保留几位小数
truncate(num,m)
-- 12
truncate(12.8,0)
5）随机数
-- 0<= val <1.0
rand()
N：种子值
rand(N)
2、字符串函数
1）字符串拼接
-- 1、有一个为null则返回null
concat('aa','bb') -- aabb
-- 2、指定分隔符拼接，存在null会忽略
concat_ws(separator,str1,str2 . . .)
concat_ws(',','aa','bb') -- aa,bb
2）获取字符串的长度
-- 1、计算字符长度
char_length('abc') -- 3
char_length('轻松工作') -- 4
-- 2、计算字节长度
-- 会把一个中文字符的长度按照设置的对应的字符集计算为2或3
length('abc') -- 3
length('轻松工作') -- 12
3）获取子串在字符串出现的位置
-- 1、instr(str,子串)
instr('abcdef','c') -- 3
-- 2、locate(子串,str)
locate('c','abcde') -- 3
-- 3、find_in_set(子串,str)
-- str必须以逗号分隔，返回该字串在集合中的位置，类似一个集合中是否包含某个元素
find_in_set('3','3,13,33,36,39') -- 1
find_in_set('3','13,33,36,39') -- 0
4）插入、替换字符串
/*
start：插入位置
length：替换掉的字符串的子串的长度
newstr：新子串
*/
insert(str,start,length,newstr)
insert('abcdef',2,2,'m') -- amdef
5）替换
-- 将指定的字符串全部替换
replace('abcdefg','c','x')
6）转大小写
upper(str)
lower(str)
7）去除字符串两端内容
-- 1、去两端空白
trim(' ab ') -- ab
-- 2、去除两端的特定子串
trim(子串 from str)
trim('e' from 'essse') -- sss
8）截取字符串
/*
从左边开始截取
n：截取的位数
*/
left(str,n)
/*
从右边开始截取
n：截取的位数
*/
right(str,n)
/*
start：起始位置
length：截取长度，不写截取到末尾
*/
substring(str,start,length)
/*
delim：分隔符
count：截取从左边(正数)/右边(负数)开始，第count个分隔符以左 / 右边的内容
*/
substring_index(str,delim,count)
substring_index('www.wikibt.com','.',1) -- www
substring_index('www.wikibt.com','.',-2) -- wikibt.com
substring_index(substring_index('www.wikibt.com','.',2),'.',-1) -- wikibt
9）重复
-- n重复的次数
repeat(str,n)
repeat('ab',2) -- abab
10）反转
reverse(str)
reverse('abc') -- cba
11）比较
- 字符串在进行比较时和java的字符串比较规则一样，‘121’>‘12000’，所以一般使用类型转换函数
3、日期函数
1）获取当前日期、时间
/*
1、年-月-日 时:分:秒
取的是sql执行时的时间，不会改变
*/
now()
/*
2、年-月-日 时:分:秒
取的是系统时间
*/
sysdate()
-- 3、年-月-日
curdate()
-- 4、时:分:秒
curtime()
2）提取
-- 1、年-月-日
date(now())
-- 2、时:分:秒
time(now())
-- 3、时间分量
extract(year from now())
extract(month from now())
extract(day from now())
extract(hour from now())
extract(minute from now())
extract(second from now())
3）运算
/*
date：日期
expr：时间间隔
unit：单位
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
YEAR
*/
1、加
DATE_ADD(date,INTERVAL expr unit)
2、减
SUBDATE(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
4）格式化
- 日期时间转字符串
date_format(date,format)
date_format(now(),'%Y年%m月%d日 %H时%i分%s秒')
date_format(date(now()),'%Y-%m-%d 00:00:00') -- 今天的00:00:00
- 字符串转日期时间
str_to_date(str,format)
str_to_date('16.11.2018 15.00.00','%d.%m.%Y %H.%i.%s')
5）时间戳转换
- 日期转换为时间戳
-- 秒
UNIX_TIMESTAMP(now())
- 时间戳转换为指定格式的日期
/*
格式：年-月-日 时:分:秒
unix_timestamp：时间戳，秒
*/
FROM_UNIXTIME(unix_timestamp)
/*
unix_timestamp：时间戳，秒
format：指定格式
*/
FROM_UNIXTIME(unix_timestamp,format)
6）比较
4、json函数
1）构建jsonObject对象
-- 单个
json_objectagg(key,value)
-- 多个，参数不能为奇数、key不能为null
json_object(key,val. . .)
json_object('id', 87, 'name', 'carrot') -- {"id": 87, "name": "carrot"}
2）构建jsonArray对象
-- 单个
json_arrayagg(val)
-- 多个
json_array(val. . .)
json_array(1,"abc",null) -- [1,"abc",null]
3）是否包含某个值
/*
包含-1；不包含-0
candidate：json值
*/
json_contains(target, candidate[, path])
json_contains(json_array('1','2'),json_arrayagg('3')) -- 0
json_contains(字段, ' "www" ', '$.url') -- 字段的key-url="www" ?
json_contains(字段, '{"url": "www.cctv"}') -- 字段={"url": "www.cctv"} ?
4）替换
/*
json_doc：json字段/值
path：指定属性
*/
json_replace(json_doc, path, val[, path, val] ...)
-- content字段的name属性替换成'tom''
json_replace(content, '$.name', "tom")
5）获取字段类型
-- OBJECT、ARRAY
json_type(TARGET / TARGET->'$.params')
6）加引号
json_quote
7）去除json字符串的引号，转成字符串
json_unquote(json_val)
json_unquote(json_extract(tt, '$.name'))
8）提取json值
-- 包含双引号
json_extract(json_doc, path[, path] ...)
JSON_EXTRACT(json_value, '$.name') -- "kimi"
9）获取所有key
-- 返回jsonArray
json_keys(json_doc[, path])
JSON_KEYS(json_object('name','kimi','age',18)) -- ["name","age"]
5、其它
1）类型转换
-- 1、
cast(expr as type)
cast(123.4 as int) -- 123
cast('2021-11-11' as date)
cast('{"name":"kimi"}' as JSON)
-- 2、
convert(expr,type)
convert('123.3',SIGNED) -- 123
二、聚合函数
1、平均值
avg(column)
select
avg(age)
from t_user
2、最大值
max(column)
3、最小值
min(column)
4、求和
-- 1、单字段
sum(colnum)
-- 2、多字段的值全部相加
sum(colnum+column+...)
-- 3、有一行的一列=null时，该行=null
/*
语文 数学
1 1
null 1
*/
-- 第2行=null
sum(语文+数学) -- 2
-- 配合ifnull函数，解决该问题
sum(ifnull(语文,0)+ifnull(数学,0)) -- 3
-- 4、sum的结果有可能是null，但我们一般想得到0
ifnul(sum(colnum),0)
5、统计数量
-- 该字段不为null的数量
count(column)
-- 统计表中所有字段的数据的最大个数
count(*)
-- 统计表中数量时，统计id最合适，因为主键不会为null
count(id)
三、其它
1、if
-- 动态删除表
drop table if exists t_user;
-- 动态显示值
select if(3>2,2,3);
select if(score is null,'',if(score=0,0,5));
-- 动态排序
order by if(status!='3' or status is null,end_time,update_time) desc;
-- 动态更新
update table set status=if(#{currentItem}=total_item,1,0);
2、ifnull
-- 如果x的值为null则age=y，如果x值不为null则age=x
age = ifnull(x,y)
update t set sal=ifnull(sal,25.000);
3、分组汇总统计with rollup
/*
统计sal字段就查sum(sal)
分组才能统计、最后添加该函数
ifnull是针对用了with rollup函数总计这个位置出现空字段时候修改它为总计字样
该函数与order by 互斥，需使用自定义排序
*/
select
ifnull(name,'合计') nn,
sum(sal) sal
from emp
group by name with rollup
4、自定义排序函数order by field
5、UUID
-- 36位
select UUID(); -- 8a1cb459-e5c1-11ea-8df7-00163e303ffa
-- 18位
select UUID_SHORT(); -- 100486252163235846