mysql常用操作语法、增删索引、增删改字段、增删改语法

概述：本文主要包含mysql添加索引、删除索引、修改字段类型或长度、update语句等

一、增删索引

0、查看索引

SHOW INDEX FROM 表名;

1、添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY (COLUMN)

2、添加单个索引-非唯一

ALTER TABLE 表名 ADD INDEX INDEX_NAME (COLUMN)

##INDEX_NAME表示索引的名称，我的索引命名规范是 INDEX_字段名

3、添加联合索引-非唯一

ALTER TABLE 表名 ADD INDEX INDEX_NAME (COLUMN1,COLUMN2,COLUMN3)

## 多列的命名规范 INDEX_COLUMN1_COLUMN2_COLUMN3

4、添加单个索引-唯一

ALTER TABLE 表名 ADD UNIQUE INDEX UNIQUE_INDEX_NAME (COLUMN1)

##命名规范 UNIQUE_INDEX_COLUMN1

5、添加联合索引-唯一

ALTER TABLE 表名 ADD UNIQUE INDEX UNIQUE_INDEX_NAME(COLUMN1,COLUMN2);

##命名规范 UNIQUE_INDEX_COLUMN1_COLUMN2

6、建表时加联合索引-唯一

UNIQUE KEY `UNIQUE_INDEX_COLUMN1_COLUMN2` (`COLUMN1`,`COLUMN2`)

###这个是在使用CREATE TABLE建表时在尾部追加的，注意引号不能少

例如：

CREATE TABLE `XXXX` (
	`ID` CHAR ( 24 ) NOT NULL COMMENT 'ID',
	`LOG_SERIAL` CHAR ( 24 ) NOT NULL COMMENT 'XXX',
PRIMARY KEY ( `ID` ),
UNIQUE KEY `UNIQUE_INDEX_LOGSERIAL` ( `LOG_SERIAL` ) 
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COMMENT = 'XXXX';

7、建表时加单个索引-唯一

UNIQUE KEY `UNIQUE_INDEX_COLUMN1` (`COLUMN1`)

8、删除索引

ALTER TABLE 表名 DROP INDEX 索引名; 

###索引名不区分唯一还是普通，都能删

9、设置主键

alter table st5 add primary key(id);

10、USING BTREE

在 MySQL 的 InnoDB 存储引擎中，当你新增非主键字段并将其作为索引列时，
默认情况下，InnoDB 会使用 B+tree 索引结构。你不需要显式指定 USING BTREE，因为 B+tree 是 InnoDB 默认的索引算法。

InnoDB 存储引擎对于主键和辅助索引都使用 B+tree 结构
USING BTREE指定使用B树结构，这个B树是B+树，不是真正的Btree，而且mysql也无法设置成功B树，只能是B+树，
所以指定USING BTREE和不指定都一样，因为其默认就是USING BTREE。

二、增删改字段

1、设置字段默认值为NULL

ALTER TABLE 表名 MODIFY 字段名 字段类型 DEFAULT  NULL

例如：

ALTER TABLE MESSAGE 
MODIFY CAPITAL_SERIAL VARCHAR(64) DEFAULT NULL COMMENT '业务流水';

2、设置不为NULL

ALTER TABLE 表名 MODIFY 字段名 字段类型 NOT NULL

例如：

ALTER TABLE MESSAGE 
MODIFY CAPITAL_SERIAL VARCHAR(64) NOT NULL COMMENT '业务流水';

3、修改字段默认值 （修改是这个字段已经有默认值，然后把默认值改成其他默认值）

ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT 默认值

4、删除字段的默认值

ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT

5、新增字段

ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 COMMENT '备注';

例如：

ALTER TABLE MC_ORDER_JNL ADD COLUMN MC_BIZ_TYPE VARCHAR(8) 
DEFAULT NULL COMMENT '交易业务类型' AFTER T_CODE;

6、修改字段长度

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 COMMENT '备注';

例如：

ALTER TABLE MULTIDB.MC_FLOW_ACCOUNT_INFO 
MODIFY COLUMN RECE_JSON VARCHAR(2048) COMMENT '收方信息';

新增字段和修改字段长度

ALTER TABLE MC_ORDER_JNL MODIFY MERCH_SHORT_NAME VARCHAR(100) NULL COMMENT '商户简称',
ADD COLUMN SUB_PL_MID VARCHAR(20) NULL COMMENT '云账户扩展平台商户号' AFTER PL_MID;

7、修改多个字段长度

ALTER TABLE 表名 
MODIFY COLUMN 字段名1 字段类型 COMMENT '备注1',
MODIFY COLUMN 字段名2 字段类型 COMMENT '备注2';

例如

ALTER TABLE MULTIDB.MC_FLOW_ACCOUNT_INFO 
MODIFY COLUMN RECE_JSON VARCHAR(2048) COMMENT '收方信息',
MODIFY COLUMN PAYER_JSON VARCHAR(2048) COMMENT '付方信息';

删除字段

ALTER TABLE <表名> DROP COLUMN <字段名>；

10、修改主键类型，切设置为自增

ALTER TABLE payment_channel.tb_funding_channel_product_route 
MODIFY id bigint(20) unsigned NOT NULL 
AUTO_INCREMENT COMMENT '主键';

三、修改语句

1、更新语句

UPDATE 表名 SET 字段名=XX WHERE ID=XXX

例如

UPDATE PMCDB SET SEX = '1' WHERE ID = 1;

四、SQL 其他语法

1、case when then else end的用法

SELECT            
    case                   ##如果
    when sex='1' then '男' ##sex='1'，则返回值'男'
    when sex='2' then '女' ##sex='2'，则返回值'女'  
    else 0                 ##其他的返回'其他’
    end                    ##结束
from sys_user            ##整体理解： 在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女' 否则返回'其他’

update  user_info 
set
Emp_responsible_name  = 
    case  　　　　　　　　　　　　　　　　　　　　　　　　　　 ##如果
    when Emp_responsible_name ='1212' then '12121212' ##Emp_responsible_name=第一个参数则返回第二个参数
    else Emp_responsible_name end 　　　　　　　　　　　　##否则 则为原值
where emp_code in ('200073','200935')

2、sql语法单列多行，拼接成单个字段

SELECT GROUP_CONCAT(DISTINCT(state)) 
FROM wechat_backend_channel where state in("zaap_wdbdyzz")

或者比如

select GROUP_CONCAT(registry_key separator ',') from xxl_job_registry where 1=1;
select GROUP_CONCAT(registry_key separator '":"') from xxl_job_registry where 1=1;