2.向表member中添加一个新的主键id
3.修改已存在字段id的名字和类型
4.删除字段键pass
5.将表member重新命名为memb
6.可以使用alter table命令的first和after子句控制字段的位置,在mid字段后增加一个status字段
7.使用set default和drop default子句为一个字段设置及删除默认值,将status的默认值设为1
8.使用add foreign key子句向表添加外键参照
9.在alter table命令中包含add index或者drop index子句添加或者删除索引
10.在alter table命令中指定一个新的type子句来更改表类型.将表memb改为innodb类型
11.向一个表添加unique键
先创建一个表,创建语句如下:
create table member(
id int(3),
name varchar(8),
pass varchar(25)
);
1.向表member中增加一个新列email,mysql语句如下:
alter table member add email varchar(50) not null;
describe member;
+-------+-------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id | int(3) | yes | | null | |
| name | varchar(8) | yes | | null | |
| pass | varchar(8) | yes | | null | |
| email | varchar(50) | no | | null | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)；
2.向表member中添加一个新的主键id,mysql语句如下:
alter table member add primary key(id);
describe member;
+-------+-------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-------+-------------+------+-----+---------+-------+
| id | int(3) | no | pri | 0 | |
| name | varchar(8) | yes | | null | |
| pass | varchar(8) | yes | | null | |
| email | varchar(50) | no | | null | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3.修改已存在字段id的名字和类型,mysql语句如下:
alter table member change id mid int(8) auto_increment unique;
describe member;
+-------+-------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+-------+-------------+------+-----+---------+----------------+
| mid | int(8) | no | pri | null | auto_increment |
| name | varchar(8) | yes | | null | |
| pass | varchar(8) | yes | | null | |
| email | varchar(50) | no | | null | |
+-------+-------------+------+-----+---------+----------------+
另一类：只改字段的属性
ALTER TABLE tableName MODIFY cloumnName DATETIME;
4 rows in set (0.00 sec)说明:修改字段类型时,mysql中也可以将change更改为modify,
两者的区别在于:change要求在修改表时指定旧的的新的字段名,而modify则只是修改相应字段的类型,但不更改该字段的名字.如下所示:
alter table member change id id int(8) auto_increment unique;
alter table member modify id int(8) auto_increment unique;
4.删除字段键pass,mysql语句如下:
alter table member drop pass;
describe member;
+-------+-------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+-------+-------------+------+-----+---------+----------------+
| mid | int(8) | no | pri | null | auto_increment |
| name | varchar(8) | yes | | null | |
| email | varchar(50) | no | | null | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)；
5.将表member重新命名为memb,使用mysql语句如下:
alter table member rename to memb
6.可以使用alter table命令的first和after子句控制字段的位置,在mid字段后增加一个status字段的mysql语句如下:
alter table memb add status int(2) after mid;
describe memb;
+--------+-------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+--------+-------------+------+-----+---------+----------------+
| mid | int(8) | no | pri | null | auto_increment |
| status | int(2) | yes | | null | |
| name | varchar(8) | yes | | null | |
| email | varchar(50) | no | | null | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
7.使用set default和drop default子句为一个字段设置及删除默认值,将status的默认值设为1的mysql语句如下:
alter table memb alter status set default 1;
describe memb;
+--------+-------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+--------+-------------+------+-----+---------+----------------+
| mid | int(8) | no | pri | null | auto_increment |
| status | int(2) | yes | | 1 | |
| name | varchar(8) | yes | | null | |
| email | varchar(50) | no | | null | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
8.使用add foreign key子句向表添加外键参照.由于前面仅创建了一张表,故无法给出修改后的结果.假设还有一张表book,则可使用以下mysql语句将book中的字段id作为一个外键加到gememb中:
alter table memb add foreign key(bookid) references book(id);
9.在alter table命令中包含add index或者drop index子句添加或者删除索引.
MySQL> ALTER TABLE table_name ADD field_name field_type;
MySQL> ALTER TABLE table_name DROP field_name;
10.在alter table命令中指定一个新的type子句来更改表类型.将表memb改为innodb类型的mysql语句如下:
alter table memb type = inndb;
11.向一个表添加unique键,如果这个包含重复的值,则可以通过包含ignore从句来从表中删除在那个键上具有重复的所有记录,只保留第一条记录.将表memb中的字段email设为unique,对email重复出现的只保留第一个记录的mysql语句如下:
alter ignore table memb modify email varchar(50) not null unique;
describe memb:
+--------+-------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+--------+-------------+------+-----+---------+----------------+
| mid | int(8) | no | pri | null | auto_increment |
| status | int(2) | yes | | 1 | |
| name | varchar(8) | yes | | null | |
| email | varchar(50) | no | uni | null | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------------------------------------------------
"mysql中常用的修改表的命令"是由电脑编程网整理，请尊重作者权益，转载注明出处；收藏本文请按ctrl+D；