工作上常用的sql写法/函数

保留两位小数

10 ：十进制
2：两位小数
100：除数

convert(taar.f_score/100,decimal(10,2)) 

日期转换

FROM_UNIXTIME(f_submit_time/1000, '%Y-%m-%d %T');
UNIX_TIMESTAMP(NOW()) * 1000;
FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()) * 1000/1000, '%Y-%m-%d %T'); 

group by having

作用

where在前是对group by分组之前的筛选，having在后是对group by分组之后的筛选

eg

查找分组之后只有一个的数据

 select * from exam.t_exam_regist ter group by ter.f_exam_id having count(*) = 1 limit 2;

case when = switch case

case 字段名
when 值 then 结果
…
else 其余结果（默认值）end

eg:

case taar.f_status 
	when 1 then '未开始'
	when 2 then '已通过'
	when 3 then '未通过'
	when 4 then '进行中'
ELSE '未开始' END
	as '状态'

case when 条件 then 结果
else 其余结果 end

EXISTS

作用

根据主查询的数据，每一行都放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE），TRUE的话该行数据就会保留

eg

SELECT * from emp e where exists (SELECT * from dept p where e.dept_id = p.dept_id and e.dept_name != p.dept_name)

GROUP_CONCAT

作用

组连接函数可以返回分组后指定字段的字符串连接形式，并且可以指定排序逻辑(必须和group by 一起用)

eg

SELECT name, GROUP_CONCAT(title ORDER BY id desc  SEPARATOR '-') from order_diy GROUP BY name ORDER BY NULL;

联表更新

作用

更新 emp 表和 dept 表关联数据

eg

update emp, dept set emp.dept_name = dept.dept_namewhere emp.dept_id = dept.dept_id;

ORDER BY 空值 NULL 排序

作用

当字段中存在 null 值时，会对我们的排序结果造成影响。我们可以通过 「ORDER BY IF(ISNULL(title), 1, 0)」 语法将 null 值转换成0或1，来达到将 null 值放到前面还是后面进行排序的效果。

eg

SELECT * FROM test_rollup ORDER BY  IF(ISNULL(title), 0, 1), money;

with rollup 分组统计数据的基础上再进行统计汇总

作用

MySql 中可以使用 with rollup 在分组统计数据的基础上再进行统计汇总，对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组求和。。(不能和orderBy一起用，8.0以后支持)

eg

SELECT name, SUM(money) as money FROM order_diy GROUP BY name WITH ROLLUP;
SELECT coalesce(name, '总金额') name, SUM(money) as money FROM order_diy GROUP BY name WITH ROLLUP;

with as 提取临时表别名

作用

「多个子查询都需要使用同一个子查询」的结果，那么就可以用with as，将共用的子查询提取出来，加个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用

eg

with aswith t1 as (SELECT * from order_diy where money > 30),t2 as (SELECT * from order_diy where money > 60)SELECT * from t1 where t1.id not in (SELECT id from  t2) and t1.name = '周伯通';

存在就更新，不存在就插入

INSERT INTO `news` (`news_title`, `news_auth`, `news_code`) VALUES ('新闻3', '小花', 'wx-0003') on duplicate key update news_title = '新闻3';-- 第二次执行修改语句INSERT INTO `news` (`news_title`, `news_auth`, `news_code`) VALUES ('新闻4', '小花', 'wx-0003') on duplicate key update news_title = '新闻4';