mysql 常用sql

-- 分组取最后一条数据 搜索文字
-- 数据按小时分组，统计每分钟个数 搜索文字
-- 字符串转时间 STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s')
-- 取当天 to_days(create_dttm) = to_days(now())
-- 前一天 date_format(expire_date,'%y%m%d') = date_format(date_sub(current_date(), interval - 1 day),'%y%m%d');
-- json 根据json 取值,json 设置值
update  t_internal_event set param = json_set(param,"$.userId","4553900395430912") where JSON_EXTRACT(param, "$.userId") = '4553900395430912';
-- 显示表结构
show create table t_market_link_info;
-- 普通索引
alter table table_name add index index_name (column_list) ;
-- 唯一索引
alter table table_name add unique (column_list) ;
-- 主键索引
alter table table_name add primary key (column_list) ;
-- 显示时间
SELECT NOW(); # 2018-05-21 14:41:00
SELECT CURDATE(); # 2018-05-21
SELECT CURTIME(); # 14:41:38
SELECT DATE(NOW()); # 2018-05-21
SELECT SYSDATE(); # 2018-05-21 14:47:11
SELECT CURRENT_TIME(); # 14:51:30
SELECT CURRENT_TIMESTAMP; # 2018-05-21 14:51:37
SELECT CURRENT_TIMESTAMP(); # 2018-05-21 14:51:43


-- 数据按小时分组，统计每分钟个数

SELECT time, COUNT( * ) AS num
FROM
   (
   SELECT
      DATE_FORMAT(
         concat( date( create_dttm ), ' ', HOUR ( create_dttm ), ':', floor( MINUTE ( create_dttm ) / 10 ) * 10 ),
         '%Y-%m-%d %H:%i'
      ) AS time
   FROM t_external_trans_total where  to_days(create_dttm) = to_days(now())
   ) a
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' )
ORDER BY time;

-- 分组取最后一条数据

SELECT
    *
FROM
    t_external_trans_total AS ds,
    (
        SELECT
            loan_code,
            max(create_dttm) as create_dttm
        FROM
            t_external_trans_total ids
        GROUP BY
            loan_code
    ) AS b
WHERE
    ds.loan_code = b.loan_code
AND ds.create_dttm = b.create_dttm
ORDER BY ds.loan_code ;