MySQL常见操作

常见操作"/>

MySQL常见操作

下面是工作时遇到的一些MySQL的操作，特意记录下来。给他大家参考使用

查询操作

查询生日

本周过生日

SELECT * FROM student
WHERE WEEK(CONCAT(YEAR(CURDATE()),"-" ,MID(sage,6,5))) = WEEK(NOW())
# 1、选取原生日里的月日成分(按照字符串进行处理)
# >>> MID(sage,6,5)
# 2、将选取的月日成分与今年的年份相接
# >>> CONCAT(YEAR(CURDATE()),"-" ,MID(sage,6,5))

本月过生日

SELECT * FROM student AS s
WHERE MONTH(CONCAT(YEAR(CURDATE()),"-",MID(s.sage,6,5))) = MONTH(CURDATE())
# 改用 month函数，查询下月过生日则+1

下周过生日

SELECT * FROM student AS s
WHERE WEEK(CONCAT(YEAR(CURDATE()),"-",MID(s.sage,6,5))) = WEEK(CURDATE())+1

计算年龄

SELECT ROUND(DATEDIFF(CURDATE(), birthday)/365.2422) FROM sys_user
TIMESTAMPDIFF(YEAR, birthday, CURDATE()) as age

递归查询

递归查询 包含自己在内

WITH RECURSIVE cte AS (SELECT id,parent_id FROM tea_grid WHERE id = '111'UNION ALLSELECT tg.id,tg.parent_id FROM tea_grid tg, cte WHERE tg.parent_id = cte.id
) SELECT id FROM cte;

递归查询 不包含自己在内

WITH RECURSIVE cte AS (SELECT id,parent_id FROM tea_grid WHERE id = '111'UNION ALLSELECT tg.id,tg.parent_id FROM tea_grid tg, cte WHERE tg.parent_id = cte.id
) SELECT id FROM cte where cte.id!='111';

根据某个字段查询重复数据记录

SELECT house_name,COUNT(house_name)
FROM tea_house
GROUP BY house_name
HAVING COUNT(house_name) > 1

根据地点打卡记录并排序地点

select *,( SELECT count( scenic_id ) FROM spot_punch_card as spc WHERE spc.is_deleted = 0 and spc.scenic_id = ls.id ) AS num  
from landscape_scenic as ls
where is_deleted = 0
ORDER BY num DESC

计算统计数–需要用到提供的sys_date表

获取日期统计数

SELECTt1.*,ifnull( t2.count, 0 ) count 
FROM( SELECT date FROM sys_date WHERE date BETWEEN '2023-09-01' AND '2023-10-23' ) t1LEFT JOIN 
( SELECT date_format( create_time, '%Y-%m-%d' ) AS date, count( * ) AS count FROM sys_event_warn WHERE is_deleted = 0 GROUP BY date ) t2 
ON t1.date = t2.date

数据操作

前面补零

LPAD(员工编号,len,padstr)
select LPAD(member, 8, 0)
结果为 00000001

后面补零

RPAD(员工编号,len,padstr)
select RPAD(member, 8, 0)
结果为10000000

判断是否为数字

SELECT id FROM sys_user WHERE is_deleted = 0 AND host_count REGEXP '^[0-9]+$'

删除

子查询删除

delete from blade_user_grid where grid_id in (select t.id from (select id from tea_grid where is_deleted = 1) as t)