2.2 MySQL修改表结构

添加新字段

alter table 表名 add 字段 类型 其它;

mysql> alter table t1 add hobby varchar(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| name   | char(10)                 | NO   |     | NULL    |       |
| gender | enum('man','woman','no') | YES  |     | no      |       |
| age    | tinyint(3) unsigned      | YES  |     | NULL    |       |
| hobby  | varchar(10)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

添加字段默认是添加到所有字段的结尾,如果要添加在首部需要使用first

mysql> alter table t1 add sex varchar(10) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| sex    | varchar(10)              | YES  |     | NULL    |       |
| name   | char(10)                 | NO   |     | NULL    |       |
| gender | enum('man','woman','no') | YES  |     | no      |       |
| age    | tinyint(3) unsigned      | YES  |     | NULL    |       |
| hobby  | varchar(10)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

也可添加在指定字段后面，比如添加到name后面使用after name

mysql> alter table t1 add info varchar(10) after name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| sex    | varchar(10)              | YES  |     | NULL    |       |
| name   | char(10)                 | NO   |     | NULL    |       |
| info   | varchar(10)              | YES  |     | NULL    |       |
| gender | enum('man','woman','no') | YES  |     | no      |       |
| age    | tinyint(3) unsigned      | YES  |     | NULL    |       |
| hobby  | varchar(10)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

 

修改字段类型

基本用法
    ALTER TABLE 表名
    MODIFY  字段名     类型（宽度）约束条件；
    可加    AFTER    字段名；
    或者    FIRST；

注意事项：
1. 如果表中字段已经有值，修改的字段类型和字段里边的值不匹配，不允许修改
2. 需要修改的地方写新值，不需要修改的原样抄下来

mysql> alter table db1.t1 modify name int(4) unsigned not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.t1;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| name  | int(4) unsigned | NO   |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

 

修改字段名

基本用法
ALTER    TABLE     表名
CHANGE     原字段名    新字段名    类型（宽度）     约束条件；

修改字段名时，改变的写新的，不变的原样抄下来

修改字段名时也可修改字段类型和约束条件，但如果类型与约束条件与字段的值发生冲突时不允许修改

mysql> alter table db1.t1 change name age int(4);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> desc db1.t1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| age   | int(4) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

修改字段名时也可修改字段类型和约束条件，但如果类型与约束条件与字段的值发生冲突时不允许修改
mysql> select * from db1.t1;
+------+
| age |
+------+
| 20 |
+------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> alter table db1.t1 change age name varchar(4);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from db1.t1;
+------+
| name |
+------+
| 20 |
+------+
1 row in set (0.00 sec)

mysql> alter table db1.t1 change name date date;
ERROR 1292 (22007): Incorrect date value: '20' for column 'date' at row 1

删除字段

ALTER    TABLE    表名
    DROP    字段名；

当字段中有多条值时，所有此字段的值都会被删除

mysql> desc db1.t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(4) | YES  |     | NULL    |       |
| age   | int(4)     | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table db1.t1 drop age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

修改表名

基本用法    
    ALTER    TABLE    表名
    RENAME    新表名；

修改表名后，对应的表文件也会改变

mysql> alter table db1.t1 rename db1.t;
Query OK, 0 rows affected (0.00 sec)