原文:/wyswlp/article/details/8881103
修改表名:
ALTERTABLEadmin_userRENAMETOa_use
//增加主键
[sql]view plaincopy altertabletabelnameaddnew_field_idint(5)unsigneddefault0notnullauto_increment,addprimarykey(new_field_id);
//修改ID为自增,并设置为主键
[sql]view plaincopy altertablebrand_title_temp_noreadmodifyidintauto_incrementprimarykey
列操作:
增加字段:
[sql]view plaincopy mysql>ALTERTABLEtable_nameADDfield_namefield_type;[sql]view plaincopy ALTERTABLEsearch_recordADD`send`INTdefault0;
修改字段类型:
[sql]view plaincopy altertablesearch_recordaltercolumnbigsetdefault0;
在某个字段后增加字段:
[sql]view plaincopy altertable`user_movement_log`AddcolumnGatewayIdintnotnulldefault0AFTER`Regionid`(在哪个字段后面添加)
调整字段顺序:
[sql]view plaincopy ALTERTABLE`user_movement_log`CHANGE`GatewayId``GatewayId`intnotnulldefault0AFTERRegionID
修改原字段名称及类型:
[sql]view plaincopy mysql>ALTERTABLEtable_nameCHANGEold_field_namenew_field_namefield_type;[sql]view plaincopy ALTERTABLEmanual_recordCHANGE`Note-sort``Note_sort`varchar(50)DEFAULTNULL;[sql]view plaincopy <p>修改字段类型</p><p>altertablescholar_keyschangeholdholdintdefault0;</p>
删除字段:
[sql]view plaincopy mysql>ALTERTABLEtable_nameDROPfield_name;
修改一个字段
[sql]view plaincopy altertableuserMODIFYnew1VARCHAR(10);//修改一个字段的类型[sql]view plaincopy altertablemanual_recordMODIFY`ORGANIZATION`VARCHAR(500)DEFAULTNULL[sql]view plaincopy altertableuserCHANGEnew1new4int;//修改一个字段的名称,此时一定要重新指定该字段的类型
索引操作:
1.添加PRIMARY KEY(主键索引)
[sql]view plaincopy mysql>ALTERTABLE`table_name`ADDPRIMARYKEY(`column`)
2.添加UNIQUE(唯一索引)
[sql]view plaincopy mysql>ALTERTABLE`table_name`ADDUNIQUE(`column`)3.添加INDEX(普通索引)[sql]view plaincopy 3.添加INDEX(普通索引)mysql>ALTERTABLE`table_name`ADDINDEXindex_name(`column`)
4.添加FULLTEXT(全文索引)
[sql]view plaincopy mysql>ALTERTABLE`table_name`ADDFULLTEXT(`column`)
5.添加多列索引[sql]view plaincopy mysql>ALTERTABLE`table_name`ADDINDEXindex_name(`column1`,`column2`,`column3`)