常用查询收集(一)

学校表 lqy_educationadd  学校id_  区域  area

教师表  lqy_teacher 外键schoolId

一个学校关联多个教师

查询区域下的  区域 教师数量

select 
    a.area,count(b.id_) from 
    lqy_educationadd a, lqy_teacher b 
where 
    a.status = 0 and b.status = 0 and a.id_ = b.schoolId group by a.area 

查询结果

--------------------------------------------

知识点：

• TO_DAYS函数 返回一个天数从年份0开始的天数

例如    SELECT TO_DAYS(NOW());   从0年开始到现在的天数  

• IFNULL(expression, alt_value) 函数  如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值

下面是 SELECT 语句：

SELECT NOW()

SELECT  CURDATE()

SELECT CURTIME()

结果如下所示：

Mysql查询当天,本周,本月等所有数据记录

模拟数据  当天为2020-10-20 该表为sys_user

一、查询当天的记录:

#获取今天数据
select * from sys_user where TO_DAYS(create_time) = TO_DAYS(NOW());

注意:这里的create_time是数据库中的时间字段(datetime类型),会根据这个时间去和今天的时间对比获取数据.

二、查询昨天（固定几天前）的记录:

#获取昨天数据
select * from sys_user where TO_DAYS(NOW()) - TO_DAYS(create_time) = 1; 

注意:这里的时间实际上和查询当天的记录一样，但是用了算术差的概念，同样的要获取前天的时间就将结果的1改成2即可，4天前的数据(某一天)，则改为4即可

三、查询当前这周的数据:(当前是周二)

#获取这周数据
SELECT * FROM sys_user where YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1);

注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据,除此之外,这个SQL语句获取的是美国的一周数据,中国和美国的周期不一样,美国的周日是中国的周一,所以如果要按照中国的周获取结果,需要推迟一天,

四、查询本月的数据:

#获取本月数据
SELECT * FROM sys_user  where DATE_FORMAT(create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );

注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据.

函数 PERIOD_DIFF(P1,P2) - 返回两个周期的月数

若要查询上月数据 PERIOD_DIFF(P1,P2)  = 1       本月=0    下月 = -1  注意此处的事件格式'%Y%m' 中间没有分隔符'-'

#查询上月数据
select * from sys_user where PERIOD_DIFF(DATE_FORMAT(NOW() ,'%Y%m'), DATE_FORMAT(create_time,'%Y%m')) = 1;

或者
select * from sys_user where DATE_FORMAT(create_time ,'%Y-%m') = DATE_FORMAT(DATE_SUB(NOW(),interval 1 MONTH),'%Y-%m');

若查询距离当前现在6个月的数据(第六个月，不是前面6个月加起来) 则改为  DATE_SUB(NOW(),interval 6 MONTH)  即可

五、查询本年的数据:

#获取本年数据
SELECT * FROM sys_user  where DATE_FORMAT(create_time, '%Y' ) = DATE_FORMAT( CURDATE( ) , '%Y' );

或者
SELECT * FROM sys_user  where YEAR(create_time) = YEAR( CURDATE( ));

注意:这里的create_time是数据库中的时间字段,会根据这个时间去和今天的时间对比获取数据.

六、其余相关查询

1.查询本季度数据

select * from sys_user where QUARTER(create_time) = QUARTER(NOW());

2.查询上季度数据

DATE_SUB() 函数从日期减去指定的时间间隔。 DATE_SUB(date,INTERVAL expr type)  date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔  type可以是如下值

select * from sys_user where QUARTER(create_time) = QUARTER(DATE_SUB(NOW(),interval 1 QUARTER));

3.查询上年数据

select * from sys_user where YEAR(create_time) = YEAR(DATE_SUB(NOW(),interval 1 YEAR));

4.查询近3天的数据(从今天开始往前推算，共计查询3天的数据)  若查询近30天的数据  则改为  DATE_SUB(CURDATE(), INTERVAL 30 DAY)

select * from sys_user where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < DATE(create_time);

此处没有使用=，费泽就变成了查询4天的数据

七、查询当前日期之前一周内每天的数据

#查询当前日期之前一周内每天的数据条数
SELECT a.click_date,IFNULL(b.count,0) AS COUNT
FROM (
    SELECT CURDATE() AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date
) a LEFT JOIN (
  SELECT DATE(create_time) AS totalMoney, COUNT(id) AS COUNT
  FROM sys_user 
  GROUP BY DATE(create_time) 
) b ON a.click_date = b.totalMoney;

假设该表中还有个字段money，要统计当前日期之前一周内每天的数据money的和，模拟数据如下

查询SQL如下

#查询当前日期之前一周内每天的数据money  合计
SELECT a.click_date,IFNULL(b.count,0) AS COUNT
FROM (
    SELECT CURDATE() AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date
) a LEFT JOIN (
  SELECT DATE(create_time) AS date, SUM(money) AS COUNT
  FROM sys_user 
  GROUP BY DATE(create_time) 
) b ON a.click_date = b.date;

查询结果：

以上采用左外连接，若该日期无数据，则显示0，下面还有一种方式

select DATE(create_time) AS click_date, SUM(money) AS COUNT from sys_user where DATE_SUB(NOW(),INTERVAL 7 DAY) < DATE_FORMAT(create_time,'%Y-%m-%d') 
GROUP BY DATE(create_time) ORDER BY DATE(create_time) DESC;

查询结果：

后面就会缺少日期和相应的数据，推荐使用第一种左外连接