1.隐藏手机号码中间四位 (身份证等的脱敏处理)
-- 三种函数处理方法(1.insert 2.replace 3.concat)
SELECT insert(w.phone, 4, 4, '****') phone, ifnull(w.bind_user, 0) binduser FROM W;
SELECT INSERT(id_number,11,4,'****') idcard FROM personnel_info ;
SELECT REPLACE(id_number,SUBSTR(id_number,11,4),'****') idcard FROM personnel_info ;
SELECT CONCAT(LEFT(id_number,11), '****' ,RIGHT(id_number,4)) idcard FROM personnel_info ;
2.显示时间为两个月之内的xx
SELECT * FROM T1 WHERE datediff(CURDATE(), T1.time) < (<) 60
3.计算一年以内的xx之和
SELECT sum(ddjg00) totalFee FROM yw_orders WHERE xdsj00 >= DATE_ADD(NOW(), INTERVAL -1 YEAR)
4.CASE WHEN 的用法(这是当某个字段为null时的处理,但是最好在数据库中设置初始值,就不会这样麻烦了)
UPDATE customer SET balance = CASE WHEN balance IS NULL THEN 50 ELSE balance + 50 END WHERE id=1
5.CONCAT 用法
SELECT SUM(ddjg) FROM orders WHERE xdsj00 LIKE CONCAT('%','${value}','%')
SELECT * FROM leave WHERE startdate LIKE CONCAT(#{month},'%') OR enddate LIKE CONCAT(#{month},'%')
6.tiny int 时查出的0,1总是会显示false和true,想让其正常显示0,1的话,可以将tiny int 的字段 * 1
select status * 1 from x
7.日期格式化
SELECT DATE_FORMAT(CREATE_TIME,'%Y-%m-%d %H:%i:%s') createTime FROM ORDERS
8.条件更新
UPDATE salary
SET sex = ( CASE WHEN sex = 'm' THEN 'f' WHEN sex = 'f' THEN 'm' ELSE sex END )
9.分割以”,“记录的的一条记录为多条记录(’附赠‘case when)
SELECT
id,
(
CASE
WHEN typeName = '设计资质' THEN
'设计企业'
WHEN typeName = '建筑业企业资质' THEN
'施工企业'
WHEN typeName = '监理资质' THEN
'监理企业'
WHEN typeName = '勘察资质' THEN
'勘察企业'
WHEN typeName = '设计与施工一体化资质' THEN
'设计与施工一体化'
WHEN typeName = '招标代理资格' THEN
'招标代理'
WHEN typeName = '造价咨询资质' THEN
'造价咨询' ELSE '其他'
END
) typeDiscribe
FROM
(
SELECT
id,
substring_index( substring_index( a.com_type_name, ',', b.help_topic_id + 1 ), ',',- 1 ) typeName
FROM
company_info a
JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.com_type_name ) - length( REPLACE ( a.com_type_name, ',', '' ))+ 1 )
) t ORDER BY id ,com_type_name
10.查询多条记录合并到一条记录
select group_concat(字段名) from 表名
11.查看定时任务是否开启的
show VARIABLES LIKE '%event_scheduler%';
select @@event_scheduler;
--如果结果为off ,则开启语句如下 ,0代表关闭
set global event_scheduler = 1;
12. update 和 left join (我试过了,没有where无法成功,会报错)
UPDATE table t1
LEFT JOIN table2 t2 ON t2.column1 = t1.column1
SET t1.column2 = t2.column2
WHERE condition