MySQL 在开发中使用经验总结

时间戳转日期格式

DATE_FORMAT(FROM_UNIXTIME(createdate), "%Y-%m-%d" ) as days

时间戳按周分组统计

GROUP BY weekofyear(createdate)    //每周一

正则匹配

REGEXP '^[a-zA-Z]'    //匹配首字母， NOT REGEXP 查询效率很慢

模糊查询

在建立索引的情况下， LIKE  'ss%'  不影响查询效率

行转列，拼接多结果

SELECT * FROM (SELECT COUNT(*) AS draw FROM HFUsers WHERE registered_source=1) AS a

    INNER JOIN (SELECT COUNT(*) AS login FROM HFUsers WHERE registered_source=1 AND createdate < updatedate) AS b

        INNER JOIN (SELECT COUNT(*) AS addup FROM HFLOG.HFFreeVipUploadLog) AS c

     INNER JOIN (SELECT COUNT(*) AS buy FROM HFUsers AS u LEFT JOIN resource.hf_buy_vip_order AS o ON o.user_id = u.id WHERE u.registered_source=1) AS d

SUM() 函数

SUM(createdate>='2018年1月1日' AND createdate<='2018年2月1日')