Mysql数据库常用的函数及示例
一、条件判断函数CASE WHENH
第一种写法
SELECT
REG_OID AS OID,
MODIFY_DATE AS MODIFY_DATE,
CONCLUDE_DATE AS BJ_TIME,
(
CASE
WHEN REG_STATUS = '1' THEN
'1'
WHEN REG_STATUS = '2' THEN
'4'
WHEN REG_STATUS = '3' THEN
'2'
WHEN REG_STATUS = '4' THEN
'3'
WHEN REG_STATUS = '5' THEN
'4'
WHEN REG_STATUS = '6' THEN
'4'
END
) AS CASE_STATUS
FROM
t_sx_service
第二种写法
SELECT
/* 申请人类型 */
CASE s.APPLICANT_TYPE
WHEN '1' THEN
'1'
WHEN '2' THEN
'2'
ELSE
'9'
END AS APPLY_USER_TYPE,
/* 证件类型 */
CASE s.APPLICANT_PAPER_TYPE
/* 身份证 */
WHEN '0' THEN
'402881945c147ae2015c156f5272000d'
/* 护照 */
WHEN '1' THEN
'402881945c147ae2015c157407f80010'
/* 军官证 */
WHEN '4' THEN
'402881945c147ae2015c15716a86000e'
/* 营业执照 */
WHEN '8' THEN
'402881f260248f1a016025c0be6100a2'
ELSE
'2c92d1d371735dd50171771386fb3602'
END AS CREDENTIAL_TYPE
FROM
bj_test
二、case判断函数和count统计函数相结合使用
SELECT
count(*) AS SL_NUM,
count(
CASE
WHEN c.ACCEPTANCE_DATE IS NOT NULL
AND c.CONCLUDE_DATE IS NOT NULL
AND c.CASE_STATUS IN ( '4', '5', '6' ) THEN
'总数' ELSE NULL
END
) AS BJ_NUM
FROM
T_TEST_SERVICE c
三、当查询条件存在多个数值时,使用in来进行链接
SELECT * FROM
t_sx_test_user
WHERE
USER_OID = '2c92d1d37c837dd5017c968159fb613e'
AND CASE_TYPE = '0'
AND HANDLE_STATUS IN ('1','2','4')
四、GROUP_CONCAT函数
GROUP_CONCAT函数是将相同行的数据归集起来,比如字段name下有数据张三、李四、王五,然后他们都有一个共同的属性为sex性别男,那么可以使用GROUP_CONCAT函数将其拼接起来。
SELECT
GROUP_CONCAT(name)
FROM
uesr
GROUP BY
sex
数据表示例图:
查询结果示例图:
GROUP_CONCAT函数的默认分割字符的方式为逗号隔开,当然我们也可以自定义字符分隔符
五、CONCAT_WS函数
相较于GROUP_CONCAT函数,是以分组的形式将字段关联起来,CONCAT_WS函数并没有分组形式,该函数是将数据库的字段以自定义的形式拼接起来
SELECT
CONCAT_WS('_',name,sex)
FROM
uesr
查询结果示例:
六、LENGTH()函数
在数据查询中,大家可能有时会需要查询到某一数据字段的长度,在这里我们使用LENGTH()函数来进行查询,注意LENGTH()函数返回的是字符串所占的字节数
SELECT
name,
LENGTH( id )
FROM
uesr
GROUP BY
NAME
结果示例图:
为了大家能够更直观的看到length()函数的作用,可以看以下示例图作对比,前面查询的是int类型的字段,后面查询的是字符类型
七、在当前时间增加一定时间的函数ADDTIME()
在数据处理过程中,可能存在批量处理数据的情况,如在创建时间上增加两个小时,大家可以采用ADDTIME()函数。
UPDATE user
SET
createTime = ADDTIME(
date(createTime) + INTERVAL 2 HOUR,
time(createTime)
)
修改前时间示例图:
修改后时间示例图:
八、SUBSTRING函数
SUBSTRING函数查询某一字段下数据从第几个位置,长度为某的值,解释说明如下:
substring('zhangsan',2) 返回从第二个位置开始到结尾的子字符串
substring('zhangsan',2,3) 返回从第二个位置开始长度为三的子字符串
substring('zhangsan',-5) 返回从倒数第五个位置到结尾的子字符串
substring('zhangsan',-5,3) 返回从倒数第五个位置开始长度为三的子字符串
查询示意图:
SELECT SUBSTRING(name,2,3)
FROM
uesr
WHERE name = '张三天涯无些当啦啦啦'
查询结果示例图:
九、IFNULL()函数
IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值
IFNULL(expression, alt_value)
参数 描述
expression 必须,要测试的值
alt_value 必须,expression 表达式为 NULL 时返回的值
查询示例:
十、date_sub()函数
DATE_SUB()函数从DATE或DATETIME值中减去时间值(或间隔)。 下面说明了DATE_SUB()函数的语法:
DATE_SUB(start_date,INTERVAL expr unit);
DATE_SUB()函数接受两个参数:
- start_date是DATE或DATETIME的起始值。
- expr是一个字符串,用于确定从起始日期减去的间隔值。unit是expr可解析的间隔单位,例如DAY,HOUR等。
代码示例(在创建时间减少3天):
UPDATE uesr SET createTime = date_sub(BJ_TIME,interval 3 day) WHERE name = '李四'
代码运行前:
代码运行后:
代码示例:减少120分钟的写法
UPDATE uesr SET createTime = date_sub(createTime,interval 120 MINUTE) WHERE name = '李四'
十一、IF()函数
当一个字段进行判断时,仅仅有两个可能,进行条件判断,使用IF()函数。
代码示例:
SELECT
IF( `name` = '李四', '是', '否' ) AS 'aaa'
FROM
uesr
表数据示意图:
查询结果示意图:
十二、在查询结果上增加序号一列
代码示例:
SET @rownum = 0;
SELECT
@rownum := @rownum + 1 AS '序号',
`name`,
id
FROM
uesr
ORDER BY
id DESC
查询结果示例: