百分比（保留两位小数）

select concat (ROUND((sum(CASE WHEN  state = 2 THEN 1 ELSE 0 END)/COUNT(*)*100) ,2),'%') as rate
from table_name

修改字段为1-10的随机数

update table set pri= floor(1 + rand()*10)

mysql 随机更新表里的几条数据

update test_table set type = "aa"
order by rand() limit 3

MySQL获取性别及其个数，占比

select (case when sex=0 then '女' else '男' end) name,
count(id) value, 
count(id)/(select count(id) from t_member) proportion    
from  t_member
group by sex

统计每月数据

select DATE_FORMAT(create_time,'%Y-%m') as month
from table_name
where DATE_FORMAT(create_time,'%Y')='2021' group by month 
order by month 
-- 第二种
select ifnull(t2.num, 0),
       t1.date
from (
         SELECT CONCAT('2021', '-01') AS date
         UNION
         SELECT CONCAT('2021', '-02') AS date
         UNION
         SELECT CONCAT('2021', '-03') AS date
         UNION
         SELECT CONCAT('2021', '-04') AS date
         UNION
         SELECT CONCAT('2021', '-05') AS date
         UNION
         SELECT CONCAT('2021', '-06') AS date
         UNION
         SELECT CONCAT('2021', '-07') AS date
         UNION
         SELECT CONCAT('2021', '-08') AS date
         UNION
         SELECT CONCAT('2021', '-09') AS date
         UNION
         SELECT CONCAT('2021', '-10') AS date
         UNION
         SELECT CONCAT('2021', '-11') AS date
         UNION
         SELECT CONCAT('2021', '-12') AS date
     ) t1
         left join
     (select count(*) AS num, date_format(completion_time, '%Y-%m') AS completion_time
      from 业务表
      where is_deleted = 0) t2 on t1.date = t2.completion_time
order by t1.date

清除缓存

reset query cache

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

-- DATE_FORMAT(date,format)
select DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') time

STR_TO_DATE(str,format)函数是将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值

-- STR_TO_DATE(str,format)
SELECT STR_TO_DATE('2021-01-01 10:20:30','%Y-%m-%d %H:%i:%s') AS result;

查找某一天的数据

-- 日期格式 2020-10-30
SELECT 
    *
FROM 
    user 
WHERE
    TO_DAYS(create_time)=TO_DAYS(NOW())

查询每一天某个时间段的所有记录(例:9:00:00到21:00:00)

SELECT * FROM 表名
WHERE DATE_FORMAT(create_time,'%H:%i:%S')>='09:00:00'
and DATE_FORMAT(create_time,'%H:%i:%S')<='21:00:00'
SELECT * FROM 表名
WHERE DATE_FORMAT(create_time,'%H:%i:%S') BETWEEN '09:00:00' and '21:00:00'