mysql查询的坑

— 以下sql 会 查询出 所有时间段内的记录

1
2
3
4
select *
from gift_trade
where pay_customer_id = 86791532 or gain_customer_id = 86791532
                                    and create_time > '2018-04-01 00:00:00' and create_time < '2018-05-01 00:00:00'>

— 以下sql会只查询出 4月份的记录

1
2
3
4
select *
from gift_trade
where( `created_date`>  '2018-04-01 00:00:00' and `created_date`  < '2018-05-01 00:00:00')
and (gain_customer_id = 86791532 or  pay_customer_id = 86791532   )

– join

join = inner join

and 等于 inner join

mysql join 之后是乘积

innner join 时必须字段在多个表都存在才有数

只要join的字段是唯一索引, 就不用担心重复

使用join on 为表建立别名， on 字段时要引用字段对应表的别名

– left join

以左边为准, 但是如果右边有where语句, 应该是在 join后再走的where

比如如下的只会查出所有ac.certification_status IN (0,2), 而不是所有左边的

1
2
3
DELETE FROM `reward` WHERE `customer_id` IN 
(SELECT * FROM (SELECT r.`customer_id`  FROM `reward` r LEFT JOIN `action_certification` ac on r.`customer_id` =ac.`customer_id` 
WHERE ac.`certification_status` IN (0,2)) a);

– 单引号的坑

单引号只能单独用在字段, 表上. 比如a.field1, 像a.field1 就是错的

– group

group 查询的非聚合函数字段一定要在group中

– 去重
distinct 去重是单列的， 如何多列去重呢, 联合 group + 聚合函数