mysql> descstaff_info;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00sec)
# 表重命名
mysql> alter tablestaff_info rename staff;
Query OK,0 rows affected (0.00sec)
mysql> descstaff;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00sec)
# 删除sex列
mysql> alter table staff dropsex;
Query OK,0 rows affected (0.02sec)
Records:0 Duplicates: 0 Warnings: 0mysql> descstaff;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
rows in set (0.01sec)
# 添加列
mysql> alter table staff add sex enum('male','female');
Query OK,0 rows affected (0.03sec)
Records:0 Duplicates: 0 Warnings: 0# 修改id的宽度
mysql> alter table staff modify id int(4);
Query OK,0 rows affected (0.02sec)
Records:0 Duplicates: 0 Warnings: 0mysql> descstaff;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.01sec)
# 修改name列的字段名
mysql> alter table staff change name sname varchar(20);
Query OK,4 rows affected (0.03sec)
Records:4 Duplicates: 0 Warnings: 0mysql> descstaff;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00sec)
# 修改sex列的位置
mysql> alter table staff modify sex enum('male','female') after sname;
Query OK,0 rows affected (0.02sec)
Records:0 Duplicates: 0 Warnings: 0mysql> descstaff;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00sec)
# 创建自增id主键
mysql> alter table staff modify id int(4) primary keyauto_increment;
Query OK,4 rows affected (0.02sec)
Records:4 Duplicates: 0 Warnings: 0mysql> descstaff;+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
rows in set (0.00sec)
# 删除主键,可以看到删除一个自增主键会报错
mysql> alter table staff drop primary key;
ERROR1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key# 需要先去掉主键的自增约束,然后再删除主键约束
mysql> alter table staff modify id int(11);
Query OK,4 rows affected (0.02sec)
Records:4 Duplicates: 0 Warnings: 0mysql> descstaff;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.01sec)
mysql> alter table staff drop primary key;
Query OK,4 rows affected (0.06sec)
Records:4 Duplicates: 0 Warnings: 0# 添加联合主键
mysql> alter table staff add primary key(sname,age);
Query OK,0 rows affected (0.02sec)
Records:0 Duplicates: 0 Warnings: 0# 删除主键
mysql> alter table staff drop primary key;
Query OK,4 rows affected (0.02sec)
Records:4 Duplicates: 0 Warnings: 0# 创建主键id
mysql> alter table staff add primary key(id);
Query OK,0 rows affected (0.02sec)
Records:0 Duplicates: 0 Warnings: 0mysql> descstaff;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00sec)
# 为主键添加自增属性
mysql> alter table staff modify id int(4) auto_increment;
Query OK,4 rows affected (0.02sec)
Records:4 Duplicates: 0 Warnings: 0mysql> descstaff;+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
rows in set (0.00 sec)