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