转载于http://shmily2038.javaeye.com/blog/797682

 

 

 
  
  1. ALTER TABLE — 更改表属性                                                                                                                 
  2. 添加字段：                                                                                                                                
  3.                                                                                                                                           
  4. alter table `user_movement_log`                                                                                                           
  5. Add column GatewayId int  not null default 0 AFTER `Regionid` (在哪个字段后面添加)                                                        
  6.                                                                                                                                           
  7. 删除字段：                                                                                                                                
  8.                                                                                                                                           
  9. alter table `user_movement_log` drop column Gatewayid                                                                                     
  10.                                                                                                                                           
  11. 调整字段顺序：                                                                                                                            
  12.                                                                                                                                           
  13. ALTER TABLE `user_movement_log`  CHANGE `GatewayId` `GatewayId` int not null default 0  AFTER RegionID                                    
  14. mysql alter 语句用法,添加、修改、删除字段等                                                                                               
  15.                                                                                                                                           
  16. //主键549830479                                                                                                                           
  17.                                                                                                                                           
  18.    alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);              
  19.                                                                                                                                           
  20. //增加一个新列549830479                                                                                                                   
  21.                                                                                                                                           
  22.    alter table t2 add d timestamp;                                                                                                        
  23. alter table infos add ex tinyint not null default '0';                                                                                    
  24.                                                                                                                                           
  25. //删除列549830479                                                                                                                         
  26.                                                                                                                                           
  27.    alter table t2 drop column c;                                                                                                          
  28.                                                                                                                                           
  29. //重命名列549830479                                                                                                                       
  30.                                                                                                                                           
  31.    alter table t1 change a b integer;                                                                                                     
  32.                                                                                                                                           
  33.                                                                                                                                           
  34. //改变列的类型549830479                                                                                                                   
  35.                                                                                                                                           
  36.    alter table t1 change b b bigint not null;                                                                                             
  37. alter table infos change list list tinyint not null default '0';                                                                          
  38.                                                                                                                                           
  39. //重命名表549830479                                                                                                                       
  40.                                                                                                                                           
  41.    alter table t1 rename t2;                                                                                                              
  42.                                                                                                                                           
  43. 加索引549830479                                                                                                                           
  44.                                                                                                                                           
  45.    mysql> alter table tablename change depno depno int(5) not null;                                                                       
  46. mysql> alter table tablename add index 索引名 (字段名1[，字段名2 …]);                                                                    
  47. mysql> alter table tablename add index emp_name (name);                                                                                   
  48.                                                                                                                                           
  49. 加主关键字的索引549830479                                                                                                                 
  50.                                                                                                                                           
  51. mysql> alter table tablename add primary key(id);                                                                                         
  52.                                                                                                                                           
  53. 加唯一限制条件的索引549830479                                                                                                             
  54.                                                                                                                                           
  55.   mysql> alter table tablename add unique emp_name2(cardnumber);                                                                          
  56.                                                                                                                                           
  57. 删除某个索引549830479                                                                                                                     
  58.                                                                                                                                           
  59.    mysql>alter table tablename drop index emp_name;                                                                                       
  60.                                                                                                                                           
  61. 修改表：549830479                                                                                                                         
  62.                                                                                                                                           
  63. 增加字段：549830479                                                                                                                       
  64.                                                                                                                                           
  65.    mysql> ALTER TABLE table_name ADD field_name field_type;                                                                               
  66.                                                                                                                                           
  67. 修改原字段名称及类型：549830479                                                                                                           
  68.                                                                                                                                           
  69.    mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;                                                         
  70.                                                                                                                                           
  71. 删除字段：549830479                                                                                                                       
  72.                                                                                                                                           
  73.    mysql> ALTER TABLE table_name DROP field_name;