第六章——修改表

6.1修改表的列结构

6.1.1Alter Table命令

        我们可以根据Alter Table命令修改列的结构。根据需要修改的类型，可以像下面这样使用带有MODIFY、ADD、CHANGE、DROP的语句。<br />        ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12862216/1622442845175-b6f2408d-5c9d-444c-9e7c-85bb60fa88cf.png#clientId=u7e1c4989-02e8-4&from=paste&height=155&id=u762d4606&margin=%5Bobject%20Object%5D&name=image.png&originHeight=206&originWidth=760&originalType=binary&size=150410&status=done&style=none&taskId=u6d4006cc-97bb-42c3-a177-21159b9e732&width=570)

6.2修改列的数据类型

6.2.1修改列的数据类型

任何时候都可以修改列的数据类型，但是数据类型的修改必须具有兼容性。不具有兼容性的修改会导致错误发生。
语法：

alter table 表名 modify 类名 数据类型：

示列 修改tbc1的name列

mysql> desc tbc1;//执行前
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>
mysql> alter table tbc1 modify name varchar(100);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tbc1;//执行后
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| empid | varchar(10)  | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>

6.2.1修改列的数据类型或位置时也修改列名

  如果想在修改列的数据类型后位置的同时也修改列名，就需要使用alter table change 命令<br />语法：<br /> 

alter table 表明 change 修改前的列名 修改后的列名 修改后的数据类型

示列：修改表tbc1的name字段同时修改名字为 tb_name;

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| birth | datetime    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table tb1 modify birth datetime first;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| birth | datetime    | YES  |     | NULL    |       |
| empid | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

6.3添加列

6.3.1添加列

**  语法：**

alter table 表名 add 列名 数据类型

示列：想tb1中添加brith列

mysql> alter table tb1 add birth datetime;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| birth | datetime    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

6.3.2把列添加到最前面

新建的列会添加到表的最后面。如果在后面加上fiist,新建的列就会添加到最前面

示列：

mysql> alter table tbf1 add birth datetime first;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tbf1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| birth | datetime    | YES  |     | NULL    |       |
| empid | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

6.3.3把列添加到任意位置

 使用after能够把列添加到指定位置。<br />**示列：将birth列添加到表tbE1的empid的后面**

mysql> alter table tbe1 add birth datetime after empid;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tbe1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | varchar(10) | YES  |     | NULL    |       |
| birth | datetime    | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

6.3.4修改列的顺序
示列：

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| birth | datetime    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table tb1 modify birth datetime first;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| birth | datetime    | YES  |     | NULL    |       |
| empid | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

6.4删除列

  在删除列的情况下，该列的保存的数据也会被删除。但这项操作不会影响其他列     <br />**语法：**

alter table 表名 drop 列名

示列：删除tb1中的birth字段

mysql> alter table tb1 drop birth;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>