sql小知识

自学习mysql并用之于工作，已有一年之久。遇到的一些小问题，学到的一些小技巧，根据成功使用的语句，整合成小文档，供大家学习使用，也便于自己随时查找和翻阅。

不定期更新！只要学到一些小知识、小技巧便会更新在此文中。

---

1. group by先于order by执行,order by是针对group by之后的结果进行的排序。

因此如果想要按组分类且组内排序，可以先执行order by再执行group by，这样会查询出每组最高/排名第一的数据。

eg. select * from (select * from student order by Score) a group by a.ClassId;

2. 对于已分组数据，按照组内想要排序的数据最高的取出来

eg. select a.* from merchant_ind a inner join (select allies_code,max(mem_num) mem_num from merchant_ind group by allies_code) b on a.allies_code=b.allies_code and a.mem_num=b.mem_num order by a.allies_code;                         按allies_code分组，取最大的mem_num

3. Regexp用作模糊匹配，与like类似：

Regexp模糊匹配

select * from anti.name where store_name regexp '北京'    =    select * from anti.name where store_name like '%北京%'

4. 末尾用order by排序时，如果是字符串形式，排序是按照1,10,11,12…，想让它按照数值排序，在字段名后面加上‘+0’，比如order by id返回1,10,100,101…；order by id+0返回1,2,3…10,11…

5. 临时表不能打开两次，否则会报错Can’t reopen table，可以建实体表

6. 删除表数据：DELETE FROM表名 where 条件

比如：DELETE FROM anti.ceshi where date(createtime)='2018-08-31'

7. 在固定位置新增字段（黑体字为字段名）： 

alter table anti.test add depname varchar(10) after store_name;

    删除字段（黑体字为字段名）： 

alter table anti.test drop depname;

8. with rollup放在group by后面一行可以在分组的统计数据的基础上再进行相同的统计

9. replace函数

replace（字段，N，M）  指把这个字段里的N替换成M，N是要替换的字符，M是替换成的值，可以用来屏蔽关键字。比如replace(a.city,'市',' ') where a.area in(‘北京市’,’上海市’,’天津市’)把市替换成空格。

10. group_concat和concat_ws用法

--按照member_id分组,涉及的商户的allies_code都展示在一个字段里

SELECT member_id,group_concat(allies_code) as涉及商户

FROM anti.merchant

group by member_id;

--按照member_id分组,涉及的商户的allies_code都展示在一个字段里并将allies_code从大到小排序且用'/'作为分隔符

SELECT member_id,group_concat(allies_code order by allies_code desc separator '/') as涉及商户

FROM anti.merchant

group by member_id;

--按照member_id分组,所有组涉及的商户allies_code和订单编号

SELECT member_id,group_concat(concat_ws('-',allies_code,order_no) order by allies_code desc) as涉及商户

FROM anti.merchant

group by member_id;

11. 常用函数

（1）mod函数

mod（N，M）   返回的是N除以M的余数，N、M都可以是字段；比如2017年是不是闰年，可以这样：mod（2017，4）返回1，没有除尽，说明不是闰年。

（2）left函数

left（N，M）    N是字符串，M是截取的长度，返回的是从这个字符串第一个开始截取M位，比如left（xubingiqng，2）返回xu

（3）right函数

用法同left函数，只是从右边开始而已，比如right（xubingqing，4）返回qing

（4）substring函数

substring（N，M，P）  N是字符串，M是截取开始的位置，P是截取的长度，比如substring（xubingqing，3,4）返回bing

（5）date_format函数

格式化日期的函数，一般会这样用：date_format（字段,'%Y-%m-%d'）

（6）round函数

四舍五入函数，round（N，M）  M是保留的小数位数，比如round（3.005,2）返回3.01

12. union和union all函数的区别

首先二者所连接的两个select结果的结构和顺序都必须一致，然后区别是：union返回的没有重复值，而union all则可以返回重复值

13. datediff和timestampdiff函数区别

datediff函数是返回两个日期之间的时间，timestampdiff函数是返回日期或日期时间差（字段二减去字段一）

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate返回 1；

SELECT DATEDIFF(day,'2008-12-30','2008-12-29') AS DiffDate返回 -1；

比如SELECT TIMESTAMPDIFF(DAY,NOW(),'2016-9-1') AS 'days'返回-684；

SELECT TIMESTAMPDIFF(DAY,'2016-9-1',NOW()) AS 'days'返回 684；

select TIMESTAMPDIFF(SECOND,,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'),'2018-07-17 18:00:00') from dual返回 -475；

select TIMESTAMPDIFF(SECOND,'2018-07-17 18:00:00',DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')) from dual返回475