一般对表结构的处理分为,增加,删除,修改字段。不过要处理表必须先有张表来给我们修改,就像做饭,最起码得有料。
student CREATE TABLE `student` (
`name` varchar(30) NOT NULL default '',
`age` int(3) default NULL,
`sex` tinyint(1) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
#此表,大家应该比较熟悉,上学的时候,老师最喜欢拿它做例子讲了。
Field Type Collation Null Key Default Extra
------ ----------- ----------------- ------ ------ ------- ------
name varchar(30) gb2312_chinese_ci NO
age int(3) (NULL) YES (NULL)
sex tinyint(1) (NULL) YES (NULL)
# 1 在表的第一行添加一个新的字段(自增主键),需要关键字 first。此字段非空,。
alter table student add id int(5) not null auto_increment primary key first ;
Field Type Collation Null Key Default Extra
------ ----------- ----------------- ------ ------ ------- --------------
id int(5) (NULL) NO PRI (NULL) auto_increment
name varchar(30) gb2312_chinese_ci NO
age int(3) (NULL) YES (NULL)
sex tinyint(1) (NULL) YES (NULL)
# 2 增加一个字段,普通的会添加到最后一行
alter table student add QQ int(16);
# 2.1 增加一个字段在某个字段的前面
alter table student add email varchar(30) after sex;
alter table student add phone varchar(11) after sex;
现在的表结构
Field Type Collation Null Key Default Extra
------ ----------- ----------------- ------ ------ ------- --------------
id int(5) (NULL) NO PRI (NULL) auto_increment
name varchar(30) gb2312_chinese_ci NO
age int(3) (NULL) YES (NULL)
sex tinyint(1) (NULL) YES (NULL)
phone varchar(11) gb2312_chinese_ci YES (NULL)
email varchar(30) gb2312_chinese_ci YES (NULL)
QQ int(16) (NULL) YES (NULL)
# 3 删除一个字段sex
alter table student drop sex;
Field Type Collation Null Key Default Extra
------ ----------- ----------------- ------ ------ ------- --------------
id int(5) (NULL) NO PRI (NULL) auto_increment
name varchar(30) gb2312_chinese_ci NO
age int(3) (NULL) YES (NULL)
phone varchar(11) gb2312_chinese_ci YES (NULL)
email varchar(30) gb2312_chinese_ci YES (NULL)
QQ int(16) (NULL) YES (NULL)
# 4 修改某个字段的属性
alter table student modify QQ varchar(9) not null Default 0;
Field Type Collation Null Key Default Extra
------ ----------- ----------------- ------ ------ ------- --------------
id int(5) (NULL) NO PRI (NULL) auto_increment
name varchar(30) gb2312_chinese_ci NO
age int(3) (NULL) YES (NULL)
phone varchar(11) gb2312_chinese_ci YES (NULL)
email varchar(30) gb2312_chinese_ci YES (NULL)
QQ varchar(9) gb2312_chinese_ci NO (NULL)
# 5 修改字段名称
alter table student change phone Iphone varchar(11) not null;
Field Type Collation Null Key Default Extra
------ ----------- ----------------- ------ ------ ------- --------------
id int(5) (NULL) NO PRI (NULL) auto_increment
name varchar(30) gb2312_chinese_ci NO
age int(3) (NULL) YES (NULL)
Iphone varchar(11) gb2312_chinese_ci NO (NULL)
email varchar(30) gb2312_chinese_ci YES (NULL)
QQ varchar(9) gb2312_chinese_ci NO (NULL)