一、前言
本文记录项目需求中遇到过的MySQL语法实例,持续更新。
二、语法实例
0x01 常用语法
留个沙发空位
0x02 根据外键关联表转移字段
表T1中有个t2_id字段,想将关联表T2中对应的字段内容转移到t1中来
UPDATE t1 LEFT JOIN t2 ON t1.t2_id = t2.id
SET t1.name=t2.name,t1.phone=t2.phone where t1.t2_id>0;
表T2
name,phone
场景:此处有这个需求,是因为前面开发人员订单设计时关联的是一个地址表id,用户已经提交订单,但修改地址后还会导致订单地址更改。订单设计时应该创建数据快照,防止其他表的更改导致快照更改,以免与用户产生纠纷。
0x03 无规则字段批量修改数据
根据给定的一些无规则id批量修改数据
UPDATE user SET is_pass=0 WHERE id IN(1,2,7,9);
场景:前期后台功能并不完善,一些机器需要更改数据库修改状态。显然在这里'IN'比'='要省事。
0x04 一小时内的平均值/总和等统计
#每分钟内的平均值
SELECT DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y-%m-%d %H:%i') AS TIME,AVG(tx_kb) FROM traffic WHERE machine_id=1234 GROUP BY DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y-%m-%d %H:%i');
#每小时内的平均值
SELECT DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y-%m-%d %H') AS TIME,AVG(tx_kb) FROM traffic WHERE machine_id=1234 GROUP BY DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y-%m-%d %H');
场景:假设机器每隔10秒流量上传一次流量。(正常情况下不会那么频繁不然服务器受不了) 我们需要查看某台机器一天24个小时时间段的流量统计图,进而分析日使用高峰。
0x05 一对多关系分组统计
SELECT machine.id,machine.name,COUNT(bind.user_id) AS user_num FROM machine,bind WHERE machine.id=bind.machine_id GROUP BY machine.id;
假设机器A、B、C、D,每台机器绑定了若干用户,我们统计每台机器绑定用户数
0x06 按日期分类统计用户
SELECT FROM_UNIXTIME(regdate,'%Y-%m-%d') days,COUNT(id) num FROM USER GROUP BY days ORDER BY days DESC;
0x07 用户金币TOP10排行榜
SELECT user_id,nickname,img,SUM(gold) AS gold FROM gold_log LEFT JOIN user ON user.id=user_id WHERE GROUP BY user_id ORDER BY gold DESC LIMIT 0,10;
0x08 迁移表字段数据
INSERT INTO user2(`u_id`,`name`) SELECT `id`,`name` FROM user;