mysql
条件
if
WHERE IF(条件, true执行条件, false执行条件 )
SELECT * FROM book WHERE IF(price > 120, btypeid=10, btypeid=11);
case when
SELECT
pp_.id,
pp_.project_name
FROM
pv_project pp_
CASE 1
WHEN
true
THEN
1 = 1
ELSE
pp_.project_name = 'bbbbb'
END
日期相关
1.mysql查询当天的数据
select * from table where to_days(时间字段) = to_days(now());
2.mysql查询昨天的数据
select * from table where to_days(now( ) ) - to_days( 时间字段名) = 1
3.mysql查询近一周的数据
SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
4.mysql查询近一个月的数据
SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 1 MONTH)
5.mysql查询本月的数据
select * from table where DATE_FORMAT(时间字段, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
6.mysql查询上月的数据
select * from table where PERIOD_DIFF(date_format(now(),'%Y%m') , date_format(时间字段,'%Y%m')) =1
7.mysql查询本年的数据
select * from table where YEAR(时间字段)=YEAR(now());
8.mysql查询上一年的数据
select * from table where year(时间字段)=year(date_sub(now(),interval 1 year));
9.mysql查询本周数据（周一为第一天）
SELECT * FROM table WHERE YEARWEEK(date_format(时间字段,'%Y-%m-%d'),1) = YEARWEEK(now(),1);
10.mysql查询近五分钟的数据
SELECT * FROM table WHERE 时间字段 >= DATE_SUB(now(),INTERVAL 5 MINUTE)
11 过去的12个月
SELECT
mon.`month`,
IFNULL( tmp.countDelayComplete, 0 ) AS countDelayComplete,
IFNULL( tmp.countDelayUnComplete, 0 ) AS countDelayUnComplete,
IFNULL( tmp.rate, 0 ) AS rate
FROM
(
SELECT
DATE_FORMAT( CURDATE(), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 1 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 2 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 3 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 4 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 5 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 6 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 7 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 8 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 9 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 10 MONTH ), '%Y.%m' ) AS `month` UNION
SELECT
DATE_FORMAT(( CURDATE() - INTERVAL 11 MONTH ), '%Y.%m' ) AS `month`
) AS mon
LEFT JOIN (
SELECT
date_format( pvr.plan_completion_time, '%Y.%m' ) AS MONTH)
格式化
SELECT date_format( createTime,'%Y-%m-%d') AS createTime
日期差值
TIMESTAMPDIFF(DAY, pr_.plan_completion_time, NOW()),
SELECT TIMESTAMPDIFF(DAY,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
SELECT DATEDIFF('2018-12-01 12:23:59','2018-11-03 12:23:59') FROM DUAL;
类型可选
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
示例
SELECT
pp_.id AS project_id,
pr_.report_num,
CONCAT(
TIMESTAMPDIFF( DAY, NOW(), pr_.plan_completion_time ),
'天',
ABS( TIMESTAMPDIFF( HOUR, NOW(), pr_.plan_completion_time ) % 24 ),
'时',
ABS( TIMESTAMPDIFF( MINUTE, NOW(), pr_.plan_completion_time ) % 60 ),
'分'
) AS date,
pp_.project_name,
pwf_.work_flow_name,
pr_.receive_report_time,
JSON_UNQUOTE(
json_extract( form_data, '$."117"' )) AS '117',
JSON_UNQUOTE(
json_extract( form_data, '$."155"' )) AS '155',
JSON_UNQUOTE(
json_extract( form_data, '$."158"' )) AS '158',
JSON_UNQUOTE(
json_extract( form_data, '$."162"' )) AS '162',
JSON_UNQUOTE(
json_extract( form_data, '$."163"' )) AS '163',
JSON_UNQUOTE(
json_extract( form_data, '$."165"' )) AS '165',
JSON_UNQUOTE(
json_extract( form_data, '$."169"' )) AS '169',
JSON_UNQUOTE(
json_extract( form_data, '$."175"' )) AS '175',
JSON_UNQUOTE(
json_extract( form_data, '$."176"' )) AS '176',
JSON_UNQUOTE(
json_extract( form_data, '$."142"' )) AS '142',
JSON_UNQUOTE(
json_extract( form_data, '$."145"' )) AS '145',
pr_.plan_completion_time,
CASE
pr_.STATUS
WHEN 'not_finish' THEN
'未完成'
WHEN 'finish' THEN
'已完成' ELSE '延期完成'
END AS STATUS,
CASE
pr_.is_delay
WHEN 0 THEN
'即将延期' ELSE '已延期'
END AS is_delay
FROM
plm.pv_report pr_
LEFT JOIN plm.pv_project pp_ ON pr_.project_id = pp_.id
LEFT JOIN plm.pv_project_work_flow pwf_ ON pr_.work_flow_id = pwf_.id
LEFT JOIN plm.pv_report_form_data prfd_ ON pr_.id = prfd_.report_id
count
条件count
select count(age < 19 or null) from sys_user
select count(if(age < 19, 1, null)) from sys_user // 返回 1条数据
select count(case when age < 19 then 1 end) from sys_user // 返回 1条数据
位数
一、FORMAT（四舍五入）
SELECT FORMAT(4.4289, 2)
二、ROUND（四舍五入）
SELECT ROUND(4.4289, 2)
三、TRUNCATE（直接舍去）
TRUNCATE（X,D）函数会将小数部分2位以后的值直接舍去
SELECT TRUNCATE(4.4289, 2)
四、CONVERT（四舍五入）
SELECT CONVERT(4.4289,DECIMAL(10,2));
五、CAST（四舍五入）
SELECT CAST(4.4289 as DECIMAL(10,2));
分组
字符串拼接
效果： 何庆青,史小辉,蒋铁荣
group_concat( pvcu_.user_name SEPARATOR ‘,’ )
SELECT
pvp_.erp_id,
pvp_.program_code,
pvp_.project_name,
group_concat( pvcu_.user_name SEPARATOR ',' ) AS pm
FROM
pv_project pvp_
LEFT JOIN pv_project_csp_user pvcu_ ON pvp_.csp_id = pvcu_.csp_id
GROUP BY
pvp_.id
case when
CASE case_value
WHEN when_value THEN
statement_list
ELSE
statement_list
END CASE;
示例
SELECT
ppp_.id,
ppcu_.user_id,
ppcu_.user_name,
ppcu_.email,
CASE
ppcu_.enabled
WHEN 1 THEN
'正常' ELSE '禁用'
END AS STATUS
FROM
plm.pv_project ppp_
LEFT JOIN pv_project_user ppu_ ON ppp_.id = ppu_.project_id
LEFT JOIN pv_project_csp_user ppcu_ ON ppu_.user_id = ppcu_.user_id
AND ppp_.csp_id = ppcu_.csp_id
select t.*,
SUM( CASE WHEN t.finish_time IS NULL
or (SELECT count(1) FROM plm.pv_report WHERE quote_id = t.prId AND is_deleted = 0 AND finish_time IS NULL) > 0
THEN 1 ELSE NULL END) AS countUnComplete,
SUM( CASE WHEN t.finish_time IS NOT NULL
and (SELECT count(1) FROM plm.pv_report WHERE quote_id = t.prId AND is_deleted = 0 AND finish_time IS NULL) = 0
THEN 1 ELSE NULL END) AS finishCount,
SUM( CASE WHEN t.finish_time IS NULL AND t.plan_completion_time < NOW()
or (SELECT count(1) FROM plm.pv_report WHERE quote_id = t.prId AND is_deleted = 0 AND finish_time IS NULL AND plan_completion_time < NOW() ) > 0
THEN 1 ELSE NULL END ) AS countDelay,
SUM( CASE WHEN t.finish_time > t.plan_completion_time
and (SELECT count(1) FROM plm.pv_report WHERE quote_id = t.prId AND is_deleted = 0 AND (finish_time is null or finish_time <= plan_completion_time )) = 0
THEN 1 ELSE NULL END ) AS countDelayComplete,
SUM( CASE WHEN t.finish_time IS NULL AND t.plan_completion_time < NOW()
or (SELECT count(1) FROM plm.pv_report WHERE quote_id = t.prId AND is_deleted = 0 AND t.finish_time IS NULL AND t.plan_completion_time < NOW() ) > 0
THEN 1 ELSE NULL END ) AS countDelayUnComplete,
SUM(CASE WHEN t.`status` = 'end' THEN 1 ELSE NULL END ) AS endCount,
count(prId) AS reportCount
from
(
SELECT
pp.id,
pp.erp_id,
pr.id AS prId,
pr.`STATUS`,
pp.program_code,
pp.project_name,
pr.finish_time,
pr.plan_completion_time,
group_concat( DISTINCT ppcu.user_name SEPARATOR ',' ) AS pm,
CASE WHEN DATEDIFF(plan_completion_time, NOW()) <= 1 THEN '1天内'
WHEN DATEDIFF(plan_completion_time, NOW()) <= 2 AND DATEDIFF(plan_completion_time, NOW()) >1 THEN '2天内'
WHEN DATEDIFF(plan_completion_time, NOW()) <= 3 AND DATEDIFF(plan_completion_time, NOW()) >2 THEN '3天内'
ELSE '大于3天' END AS report_cycle
FROM
plm.pv_project pp
where 1 =1
AND pp.is_deleted = 0
AND pr.quote_id IS NULL
AND (pp.csp_id in
(
SELECT ppcurp.csp_id FROM
plm.pv_project_csp_user_role_project ppcurp
LEFT JOIN plm.pv_project_csp_user ppcu ON ppcurp.user_id = ppcu.user_id
WHERE ppcurp.is_deleted = 0 AND ppcu.is_deleted = 0
AND (ppcu.user_name IN ($REPORT_PROJECT_USER_NAME$) OR "name_default" = $REPORT_PROJECT_USER_NAME$ )
GROUP BY ppcurp.csp_id
) )
GROUP BY
pr.id
)
t
GROUP BY t.id
1、简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
2、搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END
JSON处理
insert into userinfo (id, info) values (3,'{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}');
取出元素
select json_extract(info,'$.a') from userinfo where id=3; # 1
select json_extract(info,'$.c.d') from userinfo where id=3; # 4
select json_extract(info,'$.d') from userinfo where id=3; # [1,2,3]
select json_extract(info,'$.d[0]') from userinfo where id=3; # 1
select json_extract(info,'$.d[3]') from userinfo where id=3; # NULL
字符串
SELECT CONCAT(字段1,字段2,字段3,...) from 表名;
数学函数
取整 取余
SELECT 10 DIV 3; -- 返回结果为 3
SELECT 10 / / 3; -- 返回结果为 3
SELECT MOD(10, 3); -- 返回结果为 1
SELECT 10 % 3; -- 返回结果为 1
287
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
