mysql一些较为复杂的用法（更新中）

雨点打透心脏的1/2处 2023-03-12 10:52 11阅读 0赞

**1、查询表7天的记录（t\_company 表名，create\_time 时间字段名，CURDATE() 当前系统时间）**

SELECT \* FROM t\_company where DATE\_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create\_time)

**2、查询表30天的记录（t\_company 表名，create\_time 时间字段名，CURDATE() 当前系统时间）**

SELECT \* FROM t\_company where DATE\_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create\_time)

**3、查询表数据，并且根据时间字段的年月日分组，通过GROUP BY DATE\_FORMAT(create\_time,"%Y%m%d")**

SELECT COUNT(id),create_time FROM t_company	where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) GROUP BY  DATE_FORMAT(create_time,"%Y%m%d")

![在这里插入图片描述][20200430163851590.png]  
**4、按天统计数据，如果当天没有记录，则统计为0**  
[参考链接][Link 1]  
[参考链接][Link 2]

t\_company表名，t\_operation\_log表名，create\_time 时间字段名

SELECT t1.day,COALESCE(t2.num,0) as num FROM 
    	(SELECT 
    	@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY FROM (SELECT @cdate := DATE_ADD(CURDATE(), INTERVAL + 1 DAY) FROM 
    	t_company) t0 LIMIT 30) t1 
    	LEFT JOIN (
    	SELECT count(id) num,DATE(t_operation_log.create_time) DAY 
    	FROM t_operation_log GROUP BY  DATE_FORMAT(create_time,"%Y%m%d"
    	) ORDER BY create_time DESC) t2 ON t2.day = t1.day GROUP BY t1.day

**5、Mysql实行模糊查询，一个输入值匹配多个字段**  
[参考链接][Link 3]

select * from 表名 where concat(字段1,字段2,) like '%关键字%'

[20200430163851590.png]: /images/20230312/30feae07ac814846b4c47cbd38997420.png
[Link 1]: https://www.iteye.com/blog/wsdtq123-2270169
[Link 2]: https://www.cnblogs.com/dennyzhangdd/p/8073181.html
[Link 3]: https://blog.csdn.net/qcp1020/article/details/78708468