mysql学习总结

最新推荐文章于 2022-08-17 09:31:15 发布

狮子QH

最新推荐文章于 2022-08-17 09:31:15 发布

阅读量203

点赞数

本文链接：https://blog.csdn.net/weixin_42282999/article/details/104552844

版权

这篇博客总结了MySQL的各种操作技巧和函数使用，包括时间差计算、时间间隔统计、重复记录查询、多表查询（如内连接、外连接）、SQL执行顺序、日期函数（如DATEDIFF、TIMESTAMPDIFF）、类型转换和数据处理函数（如IFNULL、TRUNCATE）。还介绍了如何统计分组数量、处理重复字段、查询特定时间范围内的数据以及优化查询性能等实用技巧。

摘要由CSDN通过智能技术生成

1、MySql 如何返回时间差的平均值

//返回平均值为小时
SELECT AVG(TIMESTAMPDIFF(HOUR,submitTime,editTime)) as timediff from orders where editTime is not null;

//返回平均值为分钟
SELECT AVG(TIMESTAMPDIFF(MINUTE,submitTime,editTime)) as timediff from orders where editTime is not null;

//返回平均值为秒
SELECT AVG(TIMESTAMPDIFF(SECOND,submitTime,editTime)) as timediff from orders where editTime is not null;

https://blog.csdn.net/xzy565143480/article/details/90550871

2、MySql以一定的时间间隔统计

func (i *FlowInstanceBuidler) GetTrendChartByInterval(intervalType string, limit float64, whereCond string,fieldName string) (flowCountInfos []FlowTrendCount) {
   
   var sql string
   var fromSql string
   fieldSql := "flow_id,count(*) as nums"
 if whereCond != "" {
   
      whereCond += fmt.Sprintf("and DATE_SUB(CURDATE(), INTERVAL %d %s) <= date(%s) ", int(limit), intervalType,fieldName)
   } else {
   
      whereCond = fmt.Sprintf("where DATE_SUB(CURDATE(), INTERVAL %d %s) <= date(%s) ", int(limit), intervalType,fieldName)
   }
   fromSql = fmt.Sprintf("FROM flow_instance %s GROUP BY internal,flow_id ORDER BY internal DESC ", whereCond)

   switch intervalType {
   
   case "day":
      sql = fmt.Sprintf("SELECT %s, date_format(%s, '%%Y%%m%%d') internal %s ;", fieldSql,fieldName,fromSql)
   case "week":
      sql = fmt.Sprintf("SELECT %s, date_format(%s, '%%x%%v') internal %s ", fieldSql,fieldName,fromSql