MySQL常用及高级语法集锦

时间:2021-09-10 14:56:52

一、前言

本文记录项目需求中遇到过的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;