MySql常用操作备忘

在table1中修改或新增字段

ALTER TABLE table1
CHANGE COLUMN `upate_time` `update_time` DATETIME NOT NULL;
ALTER TABLE table1
ADD COLUMN `aabb` VARCHAR(45) NULL DEFAULT 'xx' COMMENT 'comment11' AFTER `field1`;

把数据从 tableB 迁移到 tableA

INSERT INTO tableA (id,field1,field2,field3) 
SELECT id,'THCK',field2,field3 FROM tableB
WHERE  companyItemCode='xxx';

替换某个字段值的一部分

REPLACE(str,old_string,new_string);
update table_name set field1= replace(field1,'页面-','')  WHERE  field1  LIKE  '页面-%';

表关联update

update table1 t1,table2 t2 set t1.field1=val,t2.field2=val2 where t1.uid=t2.uid;
update mall.goods g
left join mall.supplier_promote_goods s on  g.id = s.goods_id
set g.status=3 
where  s.id is null and g.status=2;

利用内连接查询具有层级关系的表是否有下级

SELECT count(*) FROM dh_dept p1,dh_dept p2 
WHERE p1.did=p2.pdid AND p1.did='123';

对结果集分组并判断组条数进行刷选

SELECT *,count(*) as total FROM table_name 
where work_date = '2020-04-24' 
group by user_id having total=4 order by enterp_id;

实现若记录存在则更新，不存在则插入

REPLACE INTO table1 (列名1, 列名2, ..., 列名n) VALUES 
(值1, 值2, ..., 值n),
(值1, 值2, ..., 值n);

tableA,tableB 两张有关联的表，从 tableB 筛选数据更新到 tableA

update  tableA a set a.f1 = (SELECT b.f2 FROM tableB b where a.field1 = b.field2);