MYSQL 常用命令

mysql添加字段

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
例如：
    ALTER TABLE product_toback ADD memo1 varchar(10);

mysql 指定位置添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;
AFTER 的作用是将新字段添加到某个已有字段后面

例如：
ALTER TABLE product_toback ADD memo2 varchar(10) after org_id;

mysql删除字段

ALTER TABLE product_toback drop COLUMN memo1;

添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
例：ALTER table yx_marketing_details add index(id);

添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE (`column` ) 

添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

mysql 备份表

    create table product_toback_new like product_toback;  //创建表
    insert into product_toback_new select * from product_toback;// 查询插入数据

mysql 查看执行计划

  explain (查询sql)
  
    EXPLAIN
    select
    YEAR(STR_TO_DATE(t1.biz_date, '%Y-%m-%d %H')) AS years,
    MONTH(STR_TO_DATE(t1.biz_date, '%Y-%m-%d %H')) AS month,
    LEFT(t1.biz_date,10)AS bizDate,
    org.pi2,
    org.pi3,
    org.pi5,
    city.dict_name AS cityType,
    u.user_account AS userAccount
    
    from product_toback t1
    left join product_scan_code t2  on t1.product_toback_id = t2.product_toback_id
    INNER JOIN mdm_store s ON t1.store_id = s.store_id
    INNER JOIN base_user u ON t1.user_id = u.user_id
    LEFT JOIN base_org org ON t1.org_id = org.org_id
    LEFT JOIN base_org pi5 ON org.pi5 = pi5.org_id
    LEFT JOIN mdm_distributor dist ON dist.dist_code = s.ext_code
    LEFT JOIN base_org dorg ON dorg.ext_code = dist.dist_code AND dorg.org_type=1 AND dorg.state=1
    LEFT JOIN base_dict city ON s.nature_id = city.dict_id
    left join base_dict t5 on  product_scan_code like  concat(concat( t5.ext_code, t5.dict_code ),'%')
    left join base_dict t6 on t5.pi3 = t6.dict_id
    left join base_dict t7 on t6.pi2 = t7.dict_id
    where 1=1 
    and t1.biz_date >=  date_format(concat('2022-03','-01'),'%Y-%m-%d')
    and t1.biz_date  <= last_day(concat('2022-03','-01'))

select_type	说明
SIMPLE	简单查询
PRIMARY	最外层查询
SUBQUERY	映射为子查询
DERIVED	子查询
UNION	联合
UNION RESULT	使用联合的结果

all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const