字符串函数
函数 | 描述 | 举例 |
---|---|---|
left(str, length) | 从左开始截取字符串,截取length个 | left('2023-08-04', 7) = 2023-08 |
right(str, length) | 从右开始截取字符串,截取length个 | right('2023-08-04', 5) = 08-04 |
substring(str, pos, length) | substring(被截取字段,从第几位开始截取,截取长度) | SELECT substring('2023-08-01', 1, 7)=2023-08 |
substring_index(str,delim,count) | substring_index(被截取字段,关键字,关键字出现的次数) | select substring_index("河北省-石家庄市-长安区", "-", 2)=河北省-石家庄市 |
IFNULL(expr1,expr2) | 当expr1为空时返回expr2,不为空时返回expr1 | IFNULL(NULL, 0)=0 |
find_in_set(str1,str2) | 返回str2中str1所在的位置索引,如果找到了,则返回true(1),否则返回false(0),其中str2必须以半角符号的逗号【,】分割开 | SELECT FIND_IN_SET('水泥', '水泥,矿粉,混凝土')=1 |
locate(substr,str) | 返回str中substr所在的位置索引,如果找到了,则返回一个大于0的数,否则返回0。 | SELECT locate('水泥', '水泥矿粉混凝土')=1 |
POSITION(substr IN str) | 返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果子串 substr 在 str 中不存在,返回值为 0。 | SELECT POSITION('水泥' IN '水泥矿粉混凝土')=1 |
STR_TO_DATE(str,format) | 函数是将时间格式的字符串(str),按照所提供的显示格式(format)转换为DATETIME类型的值。 | STR_TO_DATE('2023-08-04', '%Y-%m-%d %H:%i:%s')=2023-08-04 00:00:00 |
中是否包含某字符。find_in_set可以用于指定内容的排序。
数字函数
函数 | 描述 | 举例 |
---|---|---|
FORMAT(N,D,locale) | N是要格式化的数字。D是要舍入的小数位数。locale是一个可选参数,用于确定千个分隔符和分隔符之间的分组。需要注意的是,返回的结果为string类型。 | FORMAT(1000, 0)=1,000 |
ROUND(x,n) | 数据四舍五入 | ROUND(3.1415927, 4)=3.1416 |
TRUNCATE(x,n) | 返回小数点后n位的数据 | TRUNCATE(3.1415927, 4)=3.1415 |
LPAD(column, len, padstr) | 字符串位数不够补0,前面补0 | LPAD(1, 2, 0)=01 |
RPAD(column, len, padstr) | 字符串位数不够补0,后面补0 | RPAD(1, 2, 0)=10 |
日期函数
函数 | 说明 | 举例 |
---|---|---|
DATE_FORMAT(date,format) | 函数则是把数据库的日期转换为对应的字符串格式 | DATE_FORMAT(CURRENT_TIME(), '%Y-%m-%d %H:%i:%s')=2023-8-04 14:00:41 |
DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔 | DATE_SUB(curdate(), INTERVAL 7 DAY)=7日前日期 |
DATEDIFF(expr1,expr2) | 获取两日期相差天数 | DATEDIFF('2023-08-04', '2023-08-01')=3 |
curdate() | 当前日期 | |
NOW() | 当前时间 | |
YEAR(date) | 获取日期年份 | YEAR(curdate())=当前日期所处年 |
MONTH(date) | 获取日期月份 | MONTH(curdate())=当前日期所处月 |
其它
GROUP_CONCAT(colum_name)
与group by搭配使用,分组时结果多列转一行,逗号分隔内容
(@i:=@i+1)
查询结果添加序号,如:
select id,name,(@i:=@i+1) orderNo from table,(select @i:=0) b //生成orderNo序号
WITH
临时表,用于保存一些临时数据,提取子查询,简化代码
with t1 as (
select * from table1
), t2 as (
select * from table2
)
select * from t1
union all
select * from t2
窗口函数over(partition by XXX order by XXX )
partition by:根据字段来分组,类似于group by
order by:分组后根据某个字段进行排序
// 根据类别分组,按照销量排序,添加排序序号
ROW_NUMBER() OVER (partition by ctype ORDER BY sum(iQuantity) DESC) AS id
加序函数:
ROW_NUMBER():排序后顺序为,1,2,3
RANK():排序后顺序为,1,1,3
DENSE_RANK():排序后顺序为,1,1,2
NTILE():函数将排序分区中的行划分为特定数量的组,从每个组分配一个从一开始的桶号。对于每一行,NTILE()函数返回一个桶号,表示行所属的组。
如:取销量排名前20%的客户
with t1 as (
select
ntile(5) over(partition by t3.ctype order by t3.id) nt -- 将客户按照数量分成5组
from table
)
select * from t1 where nt=1 // 第一组为前20%的客户
特定顺序排序
按照一组、二组、三组、四组、五组排序
......
order by find_in_set(column_name, '一组,二组,三组,四组,五组')
不确定排序中的内容有哪些,想要将排序后的第一个放到最后一个,如类别编号已排序,现将排在第一位的010101产品类别排在最后
......
order by field(column_name, '010101')
注:order by field(column_name, str1, str2, str3) 与str1、str2、str3比较进行排序
Mybatis XML转义字符
< < 小于号
> > 大于号
& & 和
' ’ 单引号
" “ 双引号