①改变表结构
ALTER TABLE p2p_tree_log ADD OPERATE_TYPE CHAR (1)
COMMENT '操作类型(1:触发操作,2:参与操作)';
ALTER TABLE p2p_tree_log DROP COLUMN operate_type;
②IF和IFNULL语句
如果REMAIN_AMOUNT为null,REMAIN_AMOUNT=REMAIN_AMOUNT,否则REMAIN_AMOUNT=0
IF(REMAIN_AMOUNT,REMAIN_AMOUNT,0);
如果REMAIN_AMOUNT不为null,REMAIN_AMOUNT=REMAIN_AMOUNT,否则REMAIN_AMOUNT=0
IFNULL(REMAIN_AMOUNT,0)
③SQL两表之间:根据一个表的字段更新另一个表的字段
update A
set A.field1=B.field1,
A.field2=B.field2
from B
where A.id=B.id
- 常规方式,种写法相当于一个 Left join, 以外面的where为更新条数,如果不加where就是所有记录
update A
set A.field1=(select top 1 field1 from Bwhere B.id=A.id)
where A.id in (condition)