首页 > MySQL > mysql alter 语句用法

mysql alter 语句用法

来源:原创 作者:thomas 分类:MySQL 阅读:522 日期:2014-05-21

mysql alter 语句用法:

十日谈技术博客

//添加主键(字段类型及索引)

alter table tabelname add new_field_id int(10) unsigned default 0 not null auto_increment ,add primary key (new_field_id);

//增加字段(列)

alter table table_name add field_name field_type;

alter table t2 add d varchar(20);

alter table infos add ex tinyint not null default '0';

//修改字段顺序

ALTER TABLE tb_name CHANGE `area` `area` VARCHAR(50) AFTER `city` ;

//删除列(字段)

mysql> alter table t2 drop cname;

mysql> ALTER TABLE table_name DROP field_name;

//修改原字段名称及类型

ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

alter table t1 change a b int(10);

alter table t1 change b b bigint not null;

alter table infos change list list tinyint not null default '0';

//重命名表

alter table t1 rename t2;

//添加索引

mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]);

mysql> alter table tablename add index emp_name (name);

//添加主关键索引

mysql> alter table tablename add primary key(id);

//添加唯一索引

mysql> alter table tablename add unique index ui_name(cardnumber);

//删除某个索引

mysql>alter table tablename drop index emp_name;

修改数据库字符集:

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]

如:ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

只是修改表的默认字符集:

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];

如:ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段的字符集:

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];

如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

查看数据库编码:

SHOW CREATE DATABASE db_name;

查看表编码:

SHOW CREATE TABLE tbl_name;

查看字段编码:

SHOW FULL COLUMNS FROM tbl_name;

 

热门文章 更多>

微信扫一扫,关注技术十日谈