1、in 和 not in 要慎用,否则会导致全表扫描
select id from table1 where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from table1 where num between 1 and 3
2、应尽量避免在 where 子句中对字段进行 null 值 判断,否则将导致引擎放弃使用索引而进行全表扫描
select id from table1 where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from table1 where num=0
3、时间字段显示为可视
select *,FROM_UNIXTIME(operate_time / 1000) from company_tb_distribution where company_id = 2 and data_status = 'EFFECTIVE';
4、查询数据库表列重复的sql
SELECT 重复列,COUNT(*) FROM company_use_log GROUP BY 重复列 HAVING COUNT(*) > 1;
5、给一个varchar类型字段的数据加值
update company_use_log set 要修改的字段 = CONCAT(加的值,要修改的字段)
6、in 不存在库的给一个默认值
select a.id,ifnull(b.`origin_file_name`,"默认值") from
(
select 41 as id
union all
select 1000000 as id
)a left join sys_report_sample b on a.id = b.ref_id
7、查找数据库表中最新一条数据
select * from 表 where id in(select max(id) from 表 where 后面条件)
8、union 消除重复行合并两个结果集,两个结果集查询的字段必须相同
select rec_id, company_id, project_id,task_id,user_id from exam_task_user_info union select rec_id, company_id, project_id,task_id,user_id from exam_task_user_appraise
9、union all 保留重复行合并两个结果集,两个结果集查询的字段必须相同
select rec_id, company