MySQL讲义第5讲——修改表结构
文章目录
- MySQL讲义第5讲——修改表结构
- 一、增加和删除字段
- 1、增加字段
- (1)添加一个列 phone
- (2)添加一个列 salary,默认值为0,不能取空值
- (3)新增一个字段 birth,并放在 name 字段之后
- 2、删除字段
- 二、修改字段的数据类型、名称和字段的位置
- 1、修改字段的数据类型
- 2、修改字段的名称
- 3、更改字段的位置
- 三、修改表名
在 MySQL 中可以使用 ALTER TABLE 语句来改变原有数据表的结构。修改表结构涉及到的操作主要有:(1)增加和删除字段;(2)修改字段的数据类型;(3)修改字段名;(4)修改字段的排列位置;(5)添加和删除完整性约束;(6)修改表名;(7)更改表的存储引擎等。
语法格式如下:
ALTER TABLE <表名> [修改选项];
[修改选项]的格式为:
ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> [ SET DEFAULT <默认值> | DROP DEFAULT ]
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
以下的例子使用 employee 表,该表的结构如下:
mysql> desc employee;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| addr | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
一、增加和删除字段
1、增加字段
语法格式如下:
ALTER TABLE <表名> ADD COLUMN <列名> <类型> <完整性约束> [FIRST | AFTER 列名];
说明:使用 [FIRST | AFTER 列名] 选项可以设置新增字段的位置。
举例:
(1)添加一个列 phone
mysql> alter table employee add phone char(20);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| addr | varchar(100) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
(2)添加一个列 salary,默认值为0,不能取空值
mysql> alter table employee add salary decimal(10,2) default 0 not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| addr | varchar(100) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| salary | decimal(10,2) | NO | | 0.00 | |
+--------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
(3)新增一个字段 birth,并放在 name 字段之后
mysql> ALTER TABLE employee ADD COLUMN birth datetime after name;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| addr | varchar(100) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| salary | decimal(10,2) | NO | | 0.00 | |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
2、删除字段
语法格式如下:
ALTER TABLE <表名> DROP COLUMN <列名>;
举例:删除 employee 表中的 salary 字段
mysql> alter table employee drop column salary;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| addr | varchar(100) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
二、修改字段的数据类型、名称和字段的位置
1、修改字段的数据类型
修改字段的数据类型使用 MODIFY 选项。语法格式如下:
ALTER TABLE <表名> MODIFY COLUMN <列名> <类型>;
说明:使用 MODIFY 选项不能更改字段的名称。
举例:把字段 name 的类型更为 varchar,长度为20
mysql> alter table employee modify name varchar(20);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc employee;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| addr | varchar(100) | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| salary | decimal(10,2) | NO | | 0.00 | |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
2、修改字段的名称
修改字段的名称使用 CHANGE 选项。语法格式如下:
ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列名> <新列类型>;
说明:使用 CHANGE 选项时,如果新列名和旧列名相同,则作用和 MODIFY 相同。
举例:把 addr 列的名称修改为 address,类型修改为 varchar(200),放到字段 phone 之后
mysql> alter table employee change addr address varchar(200) after phone;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
| salary | decimal(10,2) | NO | | 0.00 | |
+---------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
3、更改字段的位置
在使用 ADD 选项添加字段、使用 MODIFY 选项修改字段的类型和长度、使用 CHANGE 选项修改字段名称和类型时,可以使用 [FIRST | AFTER 列名] 选项 指定字段的位置。语法格式如下:
--添加字段同时指定新增字段的位置
ALTER TABLE <表名> ADD COLUMN <列名> <类型> <完整性约束> [FIRST | AFTER 列名];
--修改字段的类型和长度同时指定字段的位置
ALTER TABLE <表名> MODIFY COLUMN <列名> <类型> [FIRST | AFTER 列名];
--修改字段的名称、类型和长度同时指定字段的位置
ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列名> <新列类型> [FIRST | AFTER 列名];
举例:
(1)添加一个字段 comm,类型为 varchar(500),位置放在第一位
mysql> alter table employee add comm varchar(500) first;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| comm | varchar(500) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
| salary | decimal(10,2) | NO | | 0.00 | |
+---------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
(2)把字段 comm 的长度修改为 1000,并放在字段 salary 之前
mysql> alter table employee modify comm varchar(1000) after address;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
| comm | varchar(1000) | YES | | NULL | |
| salary | decimal(10,2) | NO | | 0.00 | |
+---------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
(3)把字段 comm 的名称修改为 comme,并放在表的结尾
mysql> alter table employee change comm comme varchar(1000) after salary;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| phone | char(20) | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
| salary | decimal(10,2) | NO | | 0.00 | |
| comme | varchar(1000) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
三、修改表名
语法格式如下:
ALTER TABLE <表名> RENAME [TO] <新表名>;
举例:把数据表 employee 名称修改为 emp
mysql> alter table employee rename emp;
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+-----------------+
| Tables_in_hist |
+-----------------+
| dept |
| emp |
| stu |
| student |
| user_permission |
+-----------------+
5 rows in set (0.00 sec)