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

梦里梦外; 2023-10-02 20:23 1阅读 0赞

场景：表字段已存在，长度不够，修改表字段长度或者更改表字段默认值为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