| mysql

mysql按时间查询

  1. mysql mysqldump 只导出表结构 不导出数据:
    mysqldump –opt -d 数据库名 -u root -p > xxx.sql

  1. 导出数据不导出结构:
    mysqldump　-t　数据库名　-uroot　-p　>　xxx.sql　

  2. 导出数据和表结构:
    mysqldump　数据库名　-uroot　-p　>　xxx.sql　

  3. 导出特定表的结构:
    mysqldump　-uroot　-p　-B　数据库名　–table　表名　>　xxx.sql　　

1）创建个示例表register_order

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DROP TABLE IF EXISTS `register_order`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `register_order` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `out_refund_no` char(18) NOT NULL COMMENT '退款订单',
  `out_trade_no` char(18) NOT NULL COMMENT '订单号',
  `total` decimal(10,2) NOT NULL COMMENT '订单金额',
  `return_total` decimal(10,2) NOT NULL COMMENT '退款金额',
  `return_desc` text COMMENT '退款描述',
  `pay_type` varchar(3) NOT NULL COMMENT '支付方式：ali，wc',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '退款状态: 0未成功，1成功',
  `created_at` int(10) NOT NULL COMMENT '创建时间',
  `updated_at` int(10) NOT NULL COMMENT '更新时间',DROP TABLE IF EXISTS `register_order`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `register_order` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `out_refund_no` char(18) NOT NULL COMMENT '退款订单',
  `out_trade_no` char(18) NOT NULL COMMENT '订单号',
  `total` decimal(10,2) NOT NULL COMMENT '订单金额',
  `return_total` decimal(10,2) NOT NULL COMMENT '退款金额',
  `return_desc` text COMMENT '退款描述',
  `pay_type` varchar(3) NOT NULL COMMENT '支付方式：ali，wc',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '退款状态: 0未成功，1成功',
  `created_at` int(10) NOT NULL COMMENT '创建时间',
  `updated_at` int(10) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8;
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8;

2）添加一些数据

3）按时间分类，%d是按天

1
2
3
$sql = 'SELECT DATE_FORMAT(from_unixtime(created_at), "%Y-%m-%d") as d,sum(return_total) as t
FROM register_order
GROUP BY d';

结果
result

这个结果不是每天都有，不是我想要的

1
2
3
4
5
$sql='select DATE_FORMAT( ADDDATE(
"2018-03-01",INTERVAL @d DAY), "%Y-%m-%d") as date,@d :=@d + 1 day from register_order,(SELECT @d := 0) temp
WHERE ADDDATE("2018-03-01",INTERVAL @d DAY) < DATE_FORMAT("2018-04-01", "%Y-%m-%d")
GROUP BY
date';

结果
result

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$sql = 'SELECT A.date ,B.t
FROM (  
SELECT DATE_FORMAT(ADDDATE("'.$BeginDate.'", INTERVAL @d DAY),
"%Y-%m-%d") AS date ,@d :=@d + 1 DAY  
FROM register_order ,(SELECT @d := 0) temp
WHERE ADDDATE("'.$BeginDate.'",INTERVAL @d DAY) < DATE_FORMAT("'.$endDate.'", "%Y-%m-%d")
)  A
LEFT JOIN (
SELECT DATE_FORMAT(from_unixtime(created_at), "%Y-%m-%d")  as d,
ifnull(sum(return_total),0) as t
FROM register_order
GROUP BY d
) B
ON A.date = B.d';

结果
result

这样终于ok了