mysql新增修改表字段,两表关联更新表数据等常用sql操作

场景：表字段已存在，长度不够，修改表字段长度或者更改表字段默认值为null

alter table t_order_info modify column order_no varchar(50) DEFAULT NULL COMMENT '订单编号';

场景：给表新增字段

ALTER TABLE t_order_info ADD COLUMN order_type varchar(2) NULL default '0' comment '订单类型 0 APP 1 小程序';

场景：同时新增多个字段

alter table account_project add research_product_id varchar(50) comment '研究产品id'
, add country_id text comment '国家id'
, add data_source varchar(2) default '0' comment '数据来源 0 app 1 小程序';

场景：分组统计查询，查询数量大于 1 的，通常用来查询重复数据

SELECT role_id, count(*) FROM t_omp_tenant_role where  is_deleted = 0  GROUP BY  role_id HAVING count(*) >1 

场景：清空表数据，优点速度快

truncate table t_order_info;

场景：两表关联更新数据

update t_project_tenant_app tpta LEFT JOIN account_dictitem  ad  ON  tpta.therapy_area_id = ad.id and  tpta.tenant_id  = ad.tenant_id
    set tpta.therapy_area_name = ad.name
where tpta.therapy_area_id is not null and  ad.ref_type = 'tenant' and ad.dict_type_name = '治疗领域' and tpta.is_deleted = 0 and ad.is_deleted = 0;

场景：修改表名

ALTER TABLE t_order_info RENAME  t_order_info_temp;

场景：两个表结构完全一致，迁移表数据到另外一张表

insert into t_order_info select * from t_order_info_temp ;

场景：多表联合查询，将结果插入到另外一张表

INSERT INTO t_order_info ( `id`, `tenant_id`, `app_id`, `role_id`, `create_by`, `create_time`, `update_by`, `update_time`, `is_deleted` ) 
SELECT
uuid( ),
ta.tenant_id,
ta.app_id,
tr.id,
'qk',
now( ),
'qk',
now( ),
0 
FROM
  t_omp_tenant_application ta
  LEFT JOIN t_omp_application_role tr ON ta.app_id = tr.app_id 
WHERE
  ta.is_deleted = 0 
  AND tr.is_deleted = 0
 GROUP BY ta.tenant_id, ta.app_id, tr.id

场景： 删除表，通常用来删除临时表

DROP TABLE t_order_info_temp;

场景： 给表添加多个字段的唯一索引

alter table t_order_info add unique index uniq_order_info (tenant_id, app_id,is_deleted);

场景： 给表添加普通索引

ALTER TABLE t_order_info ADD INDEX idx_order_no ( `order_no` ) 

场景： 删除索引

DROP INDEX idx_order_no ON t_order_info