mysql 函数

1.通过出生日期计算年龄 TIMESTAMPDIFF()

SELECT record.captureUrlFull, camera.cameraName, record.alarmTime, rdetail.simScore, rdetail.faceUrlFull, rdetail.facedbName, rdetail.faceName, 
   TIMESTAMPDIFF(
    YEAR,
    str_to_date(rdetail.faceBirthday, '%Y-%m-%d'),
    CURDATE()
   ) AS age FROM `cw_alarm_record` record

 

2.TO_DAYS() 函数计算天数

SELECT
*
FROM
`cw_vehicle_record` record
WHERE
villageCode = '320106003015001'
AND plateNumber NOT IN (
SELECT
plateNo
FROM
cw_base_vehicle
)
AND plateNumber NOT IN (
SELECT
plateNumber
FROM
cw_vehicle_discovery
)
AND TO_DAYS(NOW()) - TO_DAYS(inOutTime) < 7
GROUP BY
plateNumber

 

3.mysql 中order by 与group by的顺序 是：

select * from ** where 

group by
order by 

注意：group by 比order by先执行，order by不会对group by 内部进行排序，如果group by后只有一条记录，那么order by 将无效。要查出group by中最大的或最小的某一字段使用 max或min函数。

4.group by 最新一条数据（查询最新的id 连接 查询）

SELECT
 *
FROM
 cw_vehicle_record r
RIGHT JOIN (
 SELECT
  MAX(id) AS id
 FROM
  cw_vehicle_record
 WHERE
  plateNumber NOT IN (
   SELECT
    plateNumber
   FROM
    cw_vehicle_discovery
  )
 AND plateNumber NOT IN (
  SELECT
   plateNo
  FROM
   cw_base_vehicle
 )
 AND villageCode = '320106003015001'
 AND TO_DAYS(NOW()) - TO_DAYS(inOutTime) <= 7
GROUP BY
 plateNumber) b ON r.id = b.id

 

4.DATE_SUB(CURDATE(),INTERVAL 1 DAY)   DATE_FORMAT(STR_TO_DATE(DATE, '%Y-%m-%d'), '%m.%d') as date

SELECT id,num,type,DATE_FORMAT(STR_TO_DATE(DATE, '%Y-%m-%d'), '%m.%d') as date 
        FROM cw_capture_tendency a
        WHERE 
            a.type = #{type}
            AND a.date BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) and DATE_SUB(CURDATE(),INTERVAL 1 DAY)
        ORDER BY a.date

5.MAX(CAST(buildingNo AS SIGNED)) mysql 查询String数值最大的数

                SELECT
            MAX(CAST(buildingNo AS SIGNED)) AS buildingNo
        FROM
            cw_base_building        

 6.in  find_in_set()

select id, list, name from table where 'daodao' IN ('libk', 'zyfon', 'daodao');
select id, list, name from table where find_in_set('daodao',list);

如果list是常量，则可以直接用IN， 否则要用find_in_set()函数

7.DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据

select date_format( dateline, “%Y-%m-%d” ) , count( * ) from test 
group by date_format( dateline , “%Y-%m-%d” )