工作了一些时间,感觉很多sql能完成的工作还不熟练,所以之后会把用到的一些sql记录起来供参考。
1.替换手机号为中间几位为****
SELECT REPLACE(手机号的字段,SUBSTR(手机号的字段,4,4),'****') FROM tableName
// REPLACE()函数为把 数值中的 ‘字符’ 替换为 ****,SUBSTR()从数值中的第四位开始,往后截取四位。 手机号如果是13333333333,要把3333替换为****则会造成1********33(啊这...);
2.新增数据后想获取新增的id;
select LAST_INSERT_ID();
select @@identity;
mybatis的话使用useGeneratedKeys="true" keyProperty="id",这个id指主键字段,获取id时.getId()
<insert id="insert" parameterType="com.lyc.learn.entity.CustomLibrary" useGeneratedKeys="true" keyProperty="id" >
详细的可以看MyBatis+MySQL 返回插入的主键ID_D调的华丽-CSDN博客_mybatis 插入返回id
3.分页查询优化,当数据超过百万时,limit 1000000,10也会查询贼慢,应 select * from a表 where
条件 AND id >=(SELECT id FROM a表 where 条件 order by id limit #{fromIndex},1 ) order by id limit #{toIndex}
比如需求就要查百万数据,弄个线程池,一次查一万条,速度还是很快的
这样能使用到id索引,直接从某个id后开始查,当然*号不建议使用,查需要的字段就行
4.IF()的使用,如:IF(sex=1,男,女),多条件IF(sex=1 AND age>60,‘大龄男人群’,‘其他’ )
用的地方比较广泛,一些条件casewhen也能取代。最近一个sql这样
select
prove.tenant_id,elp.hhr_empid,prove.hhr_esign_serial_num
FROM
(
SELECT
hhr_empid,
tenant_id,
max(hhr_send_date) hhr_send_date,
max(id) id
FROM
hhr_esign.hhr_esgin_leava_prove
WHERE
tenant_id = 0
group by hhr_empid
) elp
left join hhr_esign.hhr_esgin_leava_prove prove
on elp.tenant_id = prove.tenant_id
AND elp.hhr_empid = prove.hhr_empid
and if(elp.hhr_send_date is null,elp.id=prove.id, elp.hhr_send_date = prove.hhr_send_date)
根据发送时间获取最新数据,但发送时间可能为空,就需要获取最大的id了。
后续随着用到更新吧,很多sql为手打,可能存在错误,或者写的不怎么好,都可以提醒我更新下