mysql 表的增删改查 修改表结构

 

四、修改表结构

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

 

 

1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

修改表名 rename

mysql> create table t2(id int,name char(5));
Query OK, 0 rows affected (0.24 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| innodb__t_t1  |
| innodb__t_t2  |
| innodb__t_t3  |
| innodb__t_t4  |
| t1            |
| t2            |
+---------------+
6 rows in set (0.00 sec)

mysql> alter table t2 rename t3;
Query OK, 0 rows affected (0.20 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| innodb__t_t1  |
| innodb__t_t2  |
| innodb__t_t3  |
| innodb__t_t4  |
| t1            |
| t3            |
+---------------+
6 rows in set (0.00 sec)

 

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];

添加字段

mysql> alter table t3 add age int,add sex enum('male','female');
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(5)               | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.10 sec)

 

 

ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;

指定添加的字段在哪个位置

first  添加到第一个

mysql> alter table t3 add hobby char(50) first;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| hobby | char(50)              | YES  |     | NULL    |       |
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(5)               | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

 

放在哪个字段的后面

ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

mysql> alter table t3 add hobby char(50) after name;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(5)               | YES  |     | NULL    |       |
| hobby | char(50)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

 

3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

mysql> alter table t3 drop hobby ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(5)               | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…]

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(5)               | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)



mysql> alter table t3 modify name char(11) ;
Query OK, 0 rows affected (0.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(11)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

改数据类型

 

mysql> alter table t3 modify name int(11) ;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | int(11)               | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

mysql> alter table t3 change name NAME int(11) ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| NAME  | int(11)               | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

mysql> alter table t3 change NAME name char(11) ;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(11)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

总结：

如果只想改数据类型 用modify

如果想改字段名 或者 想改 字段名 和数据类型 用change