mysql 切合业务精用语句收集

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

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

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
32
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;

/*延伸拓展*/
SELECT
b.uid,
u.username,
count(*) AS total,
sum(IF(c.gender = 1, 1, 0)) AS man,
sum(IF(c.gender = 2, 1, 0)) AS girl,
(
count(*) - sum(IF(c.gender = 1, 1, 0)) - sum(IF(c.gender = 2, 1, 0))
) AS non
FROM
al_wx_contact c left join al_wx_weixin_base b on c.self_wechatid = b.self_wechatid left join al_users u on b.uid = u.uid
WHERE
c.create_time > 0
GROUP BY
b.uid
ORDER BY total desc;

同一张表中复制(有主键)

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

连表(含where条件)更新Update语句

1
2
3
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 里有匹配的记录全删除掉

1
2
3
DELETE t1 FROM t1,t2 WHERE t1.id=t2.id  

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

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

1
2
3
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

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

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

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

1
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中是可以的)


后续持续更新中