mysql中常用函数

一、if、ifnull、nullif、isnull的使用

1. IFNULL(expr1,expr2)，如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值。

SELECT IFNULL(NULL,‘B’); – 输出结果：B
SELECT IFNULL(‘HELLO’,‘B’); – 输出结果：HELL

2. IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。

SELECT IF(TRUE,‘A’,‘B’); – 输出结果：A
SELECT IF(FALSE,‘A’,‘B’); – 输出结果：B

3. NULLIF(expr1,expr2)，如果expr1=expr2成立，那么返回值为null，否则返回值为expr1的值。

SELECT NULLIF(‘A’,‘A’); – 输出结果：null
SELECT NULLIF(‘A’,‘B’); – 输出结果：A

4. ISNULL(expr)，如果expr的值为null，则返回1，如果expr1的值不为null，则返回0。

SELECT ISNULL(NULL); – 输出结果：1
SELECT ISNULL(‘HELLO’); – 输出结果：0

二、foreach 和 if 的使用

1. foreach循环

<foreach item = "code" collection="userIDs" separator="," open="(" close=")">
      #{code}
</foreach>

参数说明：
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串

2. if判断

# <if test="条件判断"> 执行语句</if>
<if test="myType != null and myType != ''">
   //sql语句
</if>

gt 对应 >
gte 对应 >=
lt 对应 <
lte 对应 <=
eq 对应 ==
neq 对应 !=

三、常见函数

1. FORMAT() 函数　函数用于对字段的显示进行格式化

语法： SELECT FORMAT(column_name,format) FROM table_name

date_format(t.date,'%Y')=date_format(curdate(),'%Y%m%d') 

column_name 必需。要格式化的字段 format 必需。规定格式。

格式参数
秒

%S、%s 两位数字形式的秒（ 00,01, …, 59）

分

%I、%i 两位数字形式的分（ 00,01, …, 59）

小时

%H 24小时制，两位数形式小时（00,01, …,23）
%h 12小时制，两位数形式小时（00,01, …,12）
%k 24小时制，数形式小时（0,1, …,23）
%l 12小时制，数形式小时（0,1, …,12）
%T 24小时制，时间形式（HH:mm:ss）
%r 12小时制，时间形式（hh:mm:ss AM 或 PM）
%p AM上午或PM下午

周
%W 一周中每一天的名称（Sunday,Monday, …,Saturday）

%a 一周中每一天名称的缩写（Sun,Mon, …,Sat）
%w 以数字形式标识周（0=Sunday,1=Monday, …,6=Saturday）
%U 数字表示周数，星期天为周中第一天
%u 数字表示周数，星期一为周中第一天

天
%d 两位数字表示月中天数（01,02, …,31）

%e 数字表示月中天数（1,2, …,31）
%D 英文后缀表示月中天数（1st,2nd,3rd …）
%j 以三位数字表示年中天数（001,002, …,366）

月

%M 英文月名（January,February, …,December）
%b 英文缩写月名（Jan,Feb, …,Dec）
%m 两位数字表示月份（01,02, …,12）
%c 数字表示月份（1,2, …,12）

年

%Y 四位数字表示的年份（2015,2016…）
%y 两位数字表示的年份（15,16…）

文字输出 %文字 直接输出文字内容

2.UCASE() 函数　UCASE 函数把字段的值转换为大写。

语法：SELECT UCASE(column_name) FROM table_name

3.LCASE() 函数　LCASE 函数把字段的值转换为小写。

语法： SELECT LCASE(column_name) FROM table_name

4.MID() 函数　MID 函数用于从文本字段中提取字符。

语法：SELECT MID(column_name,start[,length]) FROM table_name

column_name 必需。要提取字符的字段。
start 必需。规定开始位置(起始值是 1)。 length 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

5.LEN() 函数 LEN 函数返回文本字段中值的长度

语法：SELECT LEN(column_name) FROM table_name

6.ROUND() 函数 ROUND 函数用于把数值字段舍入为指定的小数位数。

语法：SELECT ROUND(column_name,decimals) FROM table_name

column_name　　必需。要舍入的字段。
decimals　　必需。规定要返回的小数位数。

7.NOW() 函数 　NOW 函数返回当前的日期和时间。

提示：如果您在使用 Sql Server 数据库，请使用 getdate() 函数来获得当前的日期时间。
语法：SELECT NOW() FROM table_name

8.CANCAT() 函数 cancat函数返回的是一个字符串

语法：select cancat(column_name1,column_name2) from table_name

cancat是将column_name2拼接到column_name1的后边

9. DISTINCT去重

语法：select DISTINCT id from table_name

将所有重复的数据只留下一个

10. 获取前12个月的数据

语法：SELECT DATE_SUB(CURDATE(), INTERVAL 12 MONTH)

#当前日期往前推12个月，查询结果形如2022-04-27

参数说明
date：日期表达式，可为字段或者获取日期的表达式，也可直接引用日期字符串比如“2023-04-27”。
interval_expr：时间间隔，可为整数，比如20。
date_type:日期类型，可为second（秒）、minute（分）、hour（小时）、day（天）、week（周）、month（月）、year（年）等。