sql特殊用法: 查询添加, 查询更新, 添加或更新
1. insert select语句: 查询添加
sql完成数据查询并入功能:
INSERT INTO `temp`(`vid`, `vcount`)
select vid, sum(vcount) from log where date > '2020-04-23' GROUP BY vid;
2. insert update语句: 查询更新
sql完成数据查询并更新功能:
update video t1
set t1.vcount= t1.vcount + (
select vcount from temp_video t2
where t2.vid=t1.vid
)
3. insert on duplicate key update语句: 添加或更新
sql数据存在则更新,不存在就添加功能:
INSERT INTO t_user(id, name, age) VALUES (20, 'hello', 12)
ON DUPLICATE KEY UPDATE name='repeat';