mysql常用命令积累

总结下工作中常用到的mysql命令

1.// 查询今天的数据
SELECT * FROM 表名 WHERE to_days(时间字段) = to_days(now());

2.// 查询昨天的数据
SELECT * FROM 表名 WHERE to_days(now()) - to_days(时间字段) <= 1;

如:

SELECT * FROM t_store_voucher_record WHERE to_days(now()) - to_days(create_time) = 1;

3.// 查询最近七天的数据
SELECT * FROM 表名 WHERE date_sub(curdate(), interval 7 day) <= date(时间字段);

4.//查询当前月份的数据

select * from 表名  where DATE_FORMAT(时间字段,'%Y-%m')=DATE_FORMAT(now(),'%Y-%m')

5.//查询距离现在6个月内的数据

select * from 表名  where 时间字段  between  DATE_SUB(now(),INTERVAL 6 MONTH) and now();

6.//查询10分钟以内的数据

select * from 表名 where 时间字段>=CURRENT_TIMESTAMP-INTERRVAL  10 MINUTE;

7.//查询上周数据

select * from 表名 where  YEARWEEK(date_format(时间字段,'%Y-%m-%d'))=YEARWEEK(now())-1；

8.// 查询最近30天的数据

SELECT * FROM 表名 WHERE date_sub(curdate(), interval 30 day) <= date(时间字段);

9.//查询上月数据  

SELECT * FROM 表名  WHERE PERIOD_DIFF(DATE_FORMAT(now(),'%Y-%m'),DATE_FORMAT(时间字段名,'%Y-%m'))=1；

10.//查询本季度数据

select * from 表名 where QUARTER(时间字段) = QUARTER(now())
 

11.//查询上季度数据

select * from 表名 where QUARTER(时间字段) = QUARTER(DATE_SUB(now(),interval 1 QUARTER));
 

12、查询本年数据
select * from 表名 where YEAR(时间字段) = YEAR(now());

13、查询上年数据
select * from 表名 where YEAR(时间字段) = YEAR(DATE_SUB(now(), INTERVAL 1 year))

14、查询当前这周数据
select * from 表名 where YEARWEEK(date_format(时间字段,'%Y-%m-%d')) = YEARWEEK(now())
 

15、查询上个⽉的数据
select * from 表名 where date_format(submittime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-
%m')
select * from 表名 where DATE_FORMAT(时间字段,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
select * from 表名 where WEEKOFYEAR(FROM_UNIXTIME(时间字段,'%y-%m-%d')) = WEEKOFYEAR(now());
select * from 表名 where MONTH(FROM_UNIXTIME(时间字段,'%y-%m-%d')) = MONTH(now())
select * from 表名 where YEAR(FROM_UNIXTIME(时间字段,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(时间
字段,'%y-%m-%d')) = MONTH(now());
select * from 表名 where 时间字段 between 上⽉最后⼀天 and 下⽉第⼀天;

16.更改部分数据

update commodity_group_commodity set sort_no= 0 where ref_commodity_id in ('8aaaf90c7fbfe655017fd5',
'8aaaf90c7fbfe655017fd5',
'8aaaf90c7fbfe655017fd5,
'8aaaf90c7fbfe655017fd4',
'8aaaa2237e525d48017f',
'8aaae79f7e525e68017e') and ref_commodity_group_id = '8aaab123794bc0ee0179';

17.查询本⽉数据
select * from 表名 where DATE_FORMAT(时间字段名, '%Y%m') = DATE_FORMAT(CURDATE( ), '%Y%m');

18.按天统计

SELECT
  FROM_UNIXTIME(时间字段名,'%c月%e日') 日期,
  count(*) 订单数,
  sum(统计字段名) 订单金额
FROM
  `表名` 
GROUP BY
  日期;

19.按周统计

SELECT
  FROM_UNIXTIME(时间字段名,'%Y年%u周') 日期,
  count(*) 订单数,
  sum(统计字段名) 订单金额
FROM
  `order` 
GROUP BY
  日期;

20.按月统计

SELECT
  FROM_UNIXTIME(时间字段名,'%Y年%c月') 日期,
  count(*) 订单数,
  sum(统计字段名) 订单金额
FROM
  `order` 
GROUP BY
  日期;

21.按季度统计

SELECT
  CONCAT(YEAR(FROM_UNIXTIME(时间字段名)),'年第',QUARTER(FROM_UNIXTIME(时间字段名)),'季度') 日期,
  count(*) 订单数,
  sum(统计字段名) 订单金额
FROM
  `order` 
GROUP BY
  日期;

22.按半年统计

SELECT
  CONCAT(YEAR(FROM_UNIXTIME(add_time)),'年',IF(CEIL(QUARTER(FROM_UNIXTIME(时间字段名))/2)=1,'上','下'),'半年') 日期,
  count(*) 订单数,
  sum(统计字段名) 订单金额
FROM
  `order` 
GROUP BY
  日期;

23.按年统计

SELECT
  FROM_UNIXTIME(时间字段名,'%Y年') 日期,
  count(*) 订单数,
  sum(统计字段名) 订单金额
FROM
  `order` 
GROUP BY
  日期;

24.分类统计

select  <column>  from  <table>  [where conditions]  group by  <column> ;

SELECT
count( CASE WHEN t1.order_type = 2 THEN 0 END ) sumCountOfOrderForCustomize,
sum( CASE WHEN t1.order_type = 2 AND t1.pay_time IS NOT NULL THEN t1.amt ELSE 0 END ) sumAmtOfSalesForCustomize
FROM
t_order t1
LEFT JOIN t_customizer_order_record t2 ON t1.id = t2.ref_order_id
WHERE
t1.del_flag = 0
AND t2.unit_org_code = '3901004'

25.分类统计

SELECT count( case_result = '1' OR NULL ) AS  result_good,count( case_result = '0' OR NULL ) AS  result_bad, CAST(testday AS CHAR)  AS date FROM testcase WHERE datediff( now(), testday )<= 9 GROUP BY testday;

SELECT count( business_type = '8' OR NULL ) hh1,count( business_type = '0' OR NULL ) hh2, CAST(DATE_FORMAT(create_time, '%Y%m') AS CHAR) hh3 FROM approve_record  GROUP BY DATE_FORMAT(create_time, '%Y%m')  ORDER BY DATE_FORMAT(create_time, '%Y%m') DESC;  根据月份进行分组统计