sql语句

函数find_in_set（str,字段名）和locate(str,字段名)
原表数据：

对比的SQL语句：
SELECT from es_conventional_message WHERE LOCATE(58,receive_son_id)>0
执行的结果：

SELECT from es_conventional_message WHERE FIND_IN_SET(58,receive_son_id)
执行结果：

2 date_sub(date(),interval 表达式 type)
最近七天内
SELECT * from es_golble_message where create_time> UNIX_TIMESTAMP(date_sub(curdate(),interval 7 day))
图片描述

3 查询所有订单，跟据月份分组

SELECT FROM_UNIXTIME( real_time_entry, '%Y年%m月' ) from es_bill_entry WHERE bill_type=0 GROUP BY FROM_UNIXTIME( real_time_entry, '%Y%m' )

执行结果：

4.datetime 转为long类型
SELECT UNIX_TIMESTAMP(last_updated) from inbox

5.根据条件求和，例如：求一个月中的某个单位某一天早餐的分数，中餐分数，晚餐的分数，价钱
SELECT FROM_UNIXTIME( creat_time, '%Y年%m月%d日' ), SUM( IF (order_type = 1, total_price, 0)) AS 早餐（元）, SUM(IF(order_type = 1, count, 0)) AS 早餐分数, SUM(IF(order_type = 0, count, 0)) AS 午餐分数, SUM(IF(order_type = 1, count, 0)) AS 晚餐分数, SUM( IF (order_type = 0, total_price, 0)) AS 午餐, SUM( IF (order_type = 2, total_price, 0)) AS 晚餐, SUM(total_price) AS 合计, SUM(count) AS 合计分数 FROM es_food_orders WHERE units_id = 9 GROUP BY FROM_UNIXTIME( creat_time, '%Y年%m月%日' )

6 查询两个时间差值 (FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、 MONTH、 QUARTER或 YEAR几种类型，第二个和第三个参数是待比较的两个时间，比较是后一个时间减前一个时间，具体用法如下:)
该sql 为项目中使用的update es_bxx_inbox_state SET states =2 WHERE fromuserid=? AND touserid=? AND TIMESTAMPDIFF(SECOND,from_unixtime(createtime),now())>?

这个为测试用的
select TIMESTAMPDIFF(SECOND,from_unixtime(createtime),now())