Mei Sen

[xzhuz]

https://meisen.pro/article/531976ca-d96a-43e3-81cc-45e90b457bae

1. 添加字段

alter table 表名 add column 列名1 bigint(20) after 列名0; (新增的列1要放到列0之后)

2. 修改数据库字段为可为null

alter table 表名 modify column 列名 bigint(20) default null;

3. 修改数据库字段名

alter table 表名 CHANGE COLUMN `date` create_time date ;

4. 添加主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 

5. 添加唯一索引

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

6. 添加全文索引

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

7. 添加普通索引

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

8. 添加组合索引

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

9. 查询 if else --> CASE WHEN

SELECT buyer_nick, CASE types
    WHEN 1 THEN
        '恶意打假'
    WHEN 2 THEN
        '抽检'
    ELSE
        ''
 END as '黑名单类型'
 from cloud_blacklist_mark;

10. 查看索引

show index from tablename

11. 唯一约束

11.1 添加唯一性约束

alter table tableName add unique(column_name)

11.2 查看唯一性约束

show keys from tableName;
#或
show index from tableName;

11.3 删除唯一性约束

 alter table table_name drop index key_name;