MySQL

基础语法

添加字段

ALTER TABLE washing_machine_template ADD COLUMN `region_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '城市名称' AFTER once_pay;

修改字段

ALTER TABLE washing_machine_template MODIFY COLUMN region_name varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '注释';

修改字段名

alter  table table1 change column1 column2 decimal(10,1) DEFAULT NULL COMMENT '注释'

删除字段

ALTER TABLE washing_machine_template DROP COLUMN region_name;

数据空洞

删除数据会造成数据空洞(插入和更新也可能会)
可以这样重建表：

optimize table t

或者(推荐)

alter table t engine=InnoDB

授予用户数据库权限

grant select, insert, update, delete on db.* to user

索引

增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

查看索引

show index from tblname;
show keys from tblname;