熟悉一些常用的sql写法便于工作中快速导出数据,本文不涉及到业务,所以对表库做了名字的修改,仅提供一些用法的说明。
以下直接举例子并讲解
1 单表批量数据迁移
场景:日志迁移
具体实例:将test_log2日志表2的数据全部迁移到test_log1日志表1
sql:
INSERT INTO `xxx`.test_log1
(
`operate_account_id`,
`student_id`, `transfer_type`, `gmt_create`, `gmt_modify`
)
SELECT
operate_account_id AS operate_account_id
,student_id AS student_id
,1 AS transfer_type
,gmt_create AS gmt_create
,gmt_modify AS gmt_modify
FROM `xxx`.test_log2;
说明:数据量只有几十万的话,还是很轻松的,可以直接使用上面sql
2 联表批量更新数据
场景:tab2表的数据
具体实例:需要把tab2表的最新记录的insertTime更新到tab1的lastFollowTime
sql:
UPDATE `xxx`.tab1 t
INNER JOIN (
SELECT MAX(insertTime) AS insertTime,userId FROM `xx`.tab2
WHERE type!=2 OR (type=2 and (content is not null and content != "" )) GROUP BY userId
) f
ON t.UserId = f.userId
SET t.lastFollowTime = f.insertTime
WHERE f.insertTime is not null
AND (t.lastFollowTime IS NULL
OR DATE_FORMAT(f.insertTime, '%Y-%m-%d %H:%i:%s')
3 多次left join联表导出数据
场景:查出某个时间段带有某些字段的报表数据
具体实例:查出2019/0.9/01至2019/10/01之间的数据,以下会说明一些查的技巧
sql:
SELECT
# S点
DATE_FORMAT(tc.start_date, "%Y/%m/%d %H:%i") AS "开始时间"
,DATE_FORMAT(date_add(tc.start_date, interval 1 hour), "%Y/%m/%d %H:%i") AS "结束时间"
# A点
,dep.name AS "部门"
# B点
,IF(aspc.payDate IS NOT NULL, "是", "否") AS "是否存在B点数据"
,IF(aspc.payDate IS NOT NULL, aspc.payDate, "") AS "B点数据对应时间"
FROM `xxxx`.`u_t_c_s` ut
INNER JOIN `xxxx`.`t_c_s` tc ON ut.t_c_s_id = tc.id
INNER JOIN `xxx`.userinfo ui ON ui.Id = tc.user_id
# 获得A点数据
INNER JOIN `xxxx`.account acc ON acc.userId = ui.Id
INNER JOIN
(
SELECT a2.name AS groupName,a1.id AS groupId FROM `xxxx`.`dept` a1,`xxxx`.`dept` a2
a1.pId = a2.id
) dep
ON dep.groupId = acc.deptId
# 获得B点数据
LEFT JOIN
(
SELECT MAX(IF(fitstDate IS NOT NULL, fitstDate, secondDate)) AS payDate服务器托管网, userId FROM `xxx`.table_b WHERE state=3 GROUP BY userId
) aspc
ON aspc.userId = ut.user_id
WHERE tc.start_date >= '2019-09-01 00:00:00' AND tc.start_date
4 按范围导出多个时间段的数量分布
范围也属于条件的一种,可用CASE..WHEN,或者用区分INTERVAL这个范围更简单,事例如下:
INTERVAL用法:
SELECT
INTERVAL(tmp.totalTime,5*60,10*60,20*60,30*60,40*60,50*60,60*60+1) AS TIME,
COUNT(*)
FROM
(
SELECT MAX(totalTime) AS totalTime,account FROM
`xxxx`.`tab_a`
WHERE
id IN (
# 这里是一系列的子查询
SELECT xx FROM XXXXXXXX
)
GROUP BY account
) tmp
GROUP BY TIME;
这里的步骤是:
- 红色部分子查询先用group by account 根据account分组,找出每个account对应的最大totalTime的那条记录
- 以上面作为结果集返回,然后使用INTERVAL划分区间,表示为如下:
- 区间为:(tmp.totalTime = 5*60 && tmp.totalTime = 10*60 && tmp.totalTime totalTime >= 0 THEN
'5分钟'
WHEN 10>totalTime >= 5 THEN
'10分钟'
WHEN 20>totalTime >= 10 THEN
'20分钟'
WHEN 30>totalTime >= 20 THEN
服务器托管网 '30分钟'
WHEN 40>totalTime >= 30 THEN
'40分钟'
WHEN 50>totalTime >= 40 THEN
'50分钟'
WHEN 60>totalTime >= 50 THEN
'60分钟'
END AS time,
count(*)
FROM
(
SELECT MAX(totalTime) AS totalTime,account FROM
`xxx`.`tab_a`
WHERE
id IN (
SELECT MAX(totalTime) AS totalTime,account FROM
`xxxx`.`tab_a`
WHERE
id IN (
# 这里是一系列的子查询
SELECT xx FROM XXXXXXXX
)
GROUP BY account
)
)
GROUP BY
CASE
totalTime
WHEN totalTime = 0 THEN
'0分钟'
WHEN 5>totalTime >= 0 THEN
'5分钟'
WHEN 10>totalTime >= 5 THEN
'10分钟'
WHEN 20>totalTime >= 10 THEN
'20分钟'
WHEN 30>totalTime >= 20 THEN
'30分钟'
WHEN 40>totalTime >= 30 THEN
'40分钟'
WHEN 50>totalTime >= 40 THEN
'50分钟'
WHEN 60>totalTime >= 50 THEN
'60分钟'
END;
总结:mysql性能毕竟还是不够高,使用连接的时候并没有使用hash连接,就像有一次我写了一条很多left join的sql,结果使用mysql导出要半个钟左右,后改改用分布式的SQL查询引擎(Presto),导出仅需十几秒,类似这些工具也可以多采用。
更多待续~~~~~~~
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
源创会,线下重启!2023年7月1日深圳站—基础软件技术面面谈!免费票限时抢购! 前言 SpringAOP作为Spring最核心的能力之一,其重要性不言而喻。然后需要知道的是AOP并不只是Spring特有的功能,而是一种思想,一种通用的功能。而SpringAO…