MYSQL常用语句

alter table `user_movement_log` 
Add column GatewayId int  not null default 0 AFTER `Regionid` (在哪个字段后面添加) 

 

修改原字段名称及类型：

   mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; 

删除字段：
   mysql> ALTER TABLE table_name DROP field_name;

 

清空某个mysql表中所有内容
delete from 表名;
truncate table 表名;

 

改变编辑的乱码

set names 'utf8';

 

genetube

msms 123321

 

修改字段长度

ALTER TABLE rom CHANGE attachment attachment varchar(5000);

 

添加索引

ALTER TABLE sm_app_download_stats ADD INDEX (aid,ip,down_time)

添加有默认值的字段

alter table `sm_app` add column downurlon tinyint default 0;

 

插入数据
insert into sm_admin_menu(title,link,target,pid,is_menu)values("添加产品","product/add","",47,1);

 

更新数据

UPDATE sm_admin_menu SET pid=0,target='' WHERE ID =47;

 

复制表

CREATE TABLE 新表SELECT * FROM 旧表

 

修改字段的编码： 
ALTER TABLE `tablename` CHANGE `dd` `dd` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin

删除一条记录

delete from 表名 where id=1

主键自增
alter table `sm_czjl` add column id int not null primary key auto_increment;

更新所有

FLUSH PRIVILEGES;