mysql 切合业务精用语句收集

同一个表，group by 对不同字段进行不同条件统计

例如下面的 根据 self_wechatid 进行group by ，统计出 性别总数，男总数(gender = 1为男)，女总数(gender = 2为男)，不详总数。

代码语言：javascript复制

SELECT
    self_wechatid,
    count(*) AS total,
    sum(IF(gender = 1, 1, 0)) AS man,
    sum(IF(gender = 2, 1, 0)) AS girl,
    (
        count(*) - sum(IF(gender = 1, 1, 0)) - sum(IF(gender = 2, 1, 0))
    ) AS non
FROM
    al_wx_contact
WHERE
    create_time > 1526730791
GROUP BY
    self_wechatid;

同一张表中复制（有主键）

代码语言：javascript复制

insert into 表1(字段1,字段2,字段3) select 字段1,字段2,字段3 from 表1 where id=1

连表（含where条件）更新Update语句

代码语言：javascript复制

update sumholdtime s,province b set s.province = b.province where s.area_code=b.area_code;
update t_tc_telephone s,t_mobile_number_section b set s.province=b.province,s.city=b.city where s.area_code = b.area_code and s.area_code != '';
update t_non_tc_telephone s,t_mobile_number_section b set s.province=b.province,s.city=b.city where s.area_code = b.area_code and s.area_code != '';

从数据表t1 中把那些id值在数据表t2 里有匹配的记录全删除掉

代码语言：javascript复制

DELETE t1 FROM t1,t2 WHERE t1.id=t2.id  
  或
DELETE  FROM t1 USING t1,t2 WHERE t1.id=t2.id

从数据表t1里在数据表t2里没有匹配的记录查找出来并删除掉

代码语言：javascript复制

DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL 
或
DELETE  FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL 

从两个表中找出相同记录的数据并把两个表中的数据都删除掉

代码语言：javascript复制

DELETE t1,t2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25 

注意此处的delete t1,t2 from 中的t1,t2不能是别名 如：

代码语言：javascript复制

delete t1,t2 from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25 

在数据里面执行是错误的（MYSQL 版本不小于5.0在5.0中是可以的）

---

后续持续更新中

mysql self 统计

0 人点赞