引言
一般在操作数据库的过程中对表的修改操作常常涉及, 如何处理/修改已存在大量数据的表通常是个老大难的问题
各种报错信息层出不穷, 担心误操作而导致数据丢失.
因此, 修改表结构, 看似简单实则处处细节
修改表结构
修改表结构大致有如下基本语法
1 -- 1. 修改表名
2 ALTER TABLE 表名
3 RENAME 新表名;
4
5 -- 2. 增加字段
6 ALTER TABLE 表名
7 ADD 字段名 数据类型 [完整性约束条件…],
8 ADD 字段名 数据类型 [完整性约束条件…];
9
10 -- 3. 删除字段
11 ALTER TABLE 表名
12 DROP 字段名;
13
14 -- 4. 修改字段
15 ALTER TABLE 表名
16 MODIFY 字段名 数据类型 [完整性约束条件…];
17 ALTER TABLE 表名
18 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
19 ALTER TABLE 表名
20 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
21
22 -- 5.修改字段排列顺序/在增加的时候指定字段位置
23 ALTER TABLE 表名
24 ADD 字段名 数据类型 [完整性约束条件…] FIRST;
25 ALTER TABLE 表名
26 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
27 ALTER TABLE 表名
28 CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
29 ALTER TABLE 表名
30 MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
31
ALTER TABLE的语法
示例
1.利用ALTER TABLE处理NULL和UNIQUE约束
1 CREATE TABLE t(
2 id INT UNIQUE,
3 name CHAR(10) NOT NULL
4 );
5
6 -- 去掉NULL约束
7 ALTER TABLE t
8 MODIFY name CHAR(10) NULL;
9
10 -- 添加NULL约束
11 ALTER TABLE t
12 MODIFY name CHAR(10) NOT NUll;
13
14 -- 去掉UNIQUE约束
15 ALTER TABLE t
16 DROP INDEX id;
17
18 -- 添加UNIQUE约束
19 ALTER TABLE t
20 MODIFY id INT UNIQUE;
利用ALTER TABLE处理NULL和UNIQUE约束
2.利用ALTER TABLE处理主键
1 CREATE TABLE t(
2 id INT UNIQUE,
3 name CHAR(10) NOT NULL
4 );
5
6 -- 去掉NULL约束
7 ALTER TABLE t
8 MODIFY name CHAR(10) NULL;
9
10 -- 添加NULL约束
11 ALTER TABLE t
12 MODIFY name CHAR(10) NOT NUll;
13
14 -- 去掉UNIQUE约束
15 ALTER TABLE t
16 DROP INDEX id;
17
18 -- 添加UNIQUE约束
19 ALTER TABLE t
20 MODIFY id INT UNIQUE;
利用ALTER TABLE处理主键
3.利用ALTER TABLE为表添加外键
1 CREATE TABLE press(
2 id INT(11) NOT NULL,
3 name CHAR(10) DEFAULT NULL,
4 PRIMARY KEY (id)
5 );
6
7 CREATE TABLE book(
8 id INT(11) DEFAULT NULL,
9 bk_name CHAR(12) DEFAULT NULL,
10 press_id INT(11) NOT NULL,
11 );
12
13 -- 为book表添加外键
14 ALTER TABLE book
15 ADD CONSTRAINT [foreign key name] FOREIGN KEY(press_id) REFERENCES press(id);
16
17 -- 删除外键
18 ALTER TABLE book
19 DROP FOREIGN KEY [foreign key name];
20
21 -- 注: [内容] 为可选填内容, 除保留字外, 符合语法规范均可
利用ALTER TABLE为表添加外键
其他示例
1.表重命名
1 DESC old_name;
2 +-------+-----------------------+------+-----+---------+-------+
3 | Field | Type | Null | Key | Default | Extra |
4 +-------+-----------------------+------+-----+---------+-------+
5 | id | int(11) | YES | | NULL | |
6 | name | varchar(50) | YES | | NULL | |
7 | age | int(3) | YES | | NULL | |
8 | sex | enum('male','female') | YES | | NULL | |
9 | phone | bigint(11) | YES | | NULL | |
10 | job | varchar(11) | YES | | NULL | |
11 +-------+-----------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
13
14 -- 表重命名
15 ALTER TABLE old_name RENAME new_name;
16 Query OK, 0 rows affected (0.00 sec)
17
18 DESC new_name;
19 +-------+-----------------------+------+-----+---------+-------+
20 | Field | Type | Null | Key | Default | Extra |
21 +-------+-----------------------+------+-----+---------+-------+
22 | id | int(11) | YES | | NULL | |
23 | name | varchar(50) | YES | | NULL | |
24 | age | int(3) | YES | | NULL | |
25 | sex | enum('male','female') | YES | | NULL | |
26 | phone | bigint(11) | YES | | NULL | |
27 | job | varchar(11) | YES | | NULL | |
28 +-------+-----------------------+------+-----+---------+-------+
29 rows in set (0.00 sec)
表重命名
2.删除或添加某列
1 -- 删除sex列
2 ALTER TABLE staff DROP sex;
3 Query OK, 0 rows affected (0.02 sec)
4 Records: 0 Duplicates: 0 Warnings: 0
5
6 DESC staff;
7 +-------+-------------+------+-----+---------+-------+
8 | Field | Type | Null | Key | Default | Extra |
9 +-------+-------------+------+-----+---------+-------+
10 | id | int(11) | YES | | NULL | |
11 | name | varchar(50) | YES | | NULL | |
12 | age | int(3) | YES | | NULL | |
13 | phone | bigint(11) | YES | | NULL | |
14 | job | varchar(11) | YES | | NULL | |
15 +-------+-------------+------+-----+---------+-------+
16 rows in set (0.01 sec)
17
18 -- 添加列
19 ALTER TABLE staff ADD sex ENUM('male','female');
20 Query OK, 0 rows affected (0.03 sec)
21 Records: 0 Duplicates: 0 Warnings: 0
删除或添加某列
3.修改字段属性1
1 -- 修改id的宽度
2 ALTER TABLE staff MODIFY id INT(4);
3 Query OK, 0 rows affected (0.02 sec)
4 Records: 0 Duplicates: 0 Warnings: 0
5
6 DESC staff;
7 +-------+-----------------------+------+-----+---------+-------+
8 | Field | Type | Null | Key | Default | Extra |
9 +-------+-----------------------+------+-----+---------+-------+
10 | id | int(4) | YES | | NULL | |
11 | name | varchar(50) | YES | | NULL | |
12 | age | int(3) | YES | | NULL | |
13 | phone | bigint(11) | YES | | NULL | |
14 | job | varchar(11) | YES | | NULL | |
15 | sex | enum('male','female') | YES | | NULL | |
16 +-------+-----------------------+------+-----+---------+-------+
17 rows in set (0.01 sec)
修改字段属性1
4.修改字段属性2
1 -- 修改name列的字段名
2 ALTER TABLE staff CHANGE name sname VARCHAR(20);
3 Query OK, 4 rows affected (0.03 sec)
4 Records: 4 Duplicates: 0 Warnings: 0
5
6 DESC staff;
7 +-------+-----------------------+------+-----+---------+-------+
8 | Field | Type | Null | Key | Default | Extra |
9 +-------+-----------------------+------+-----+---------+-------+
10 | id | int(4) | YES | | NULL | |
11 | sname | varchar(20) | YES | | NULL | |
12 | age | int(3) | YES | | NULL | |
13 | phone | bigint(11) | YES | | NULL | |
14 | job | varchar(11) | YES | | NULL | |
15 | sex | enum('male','female') | YES | | NULL | |
16 +-------+-----------------------+------+-----+---------+-------+
17 rows in set (0.00 sec)
修改字段属性2
5.修改字段属性3
1 -- 修改sex列的位置
2 ALTER TABLE staff MODIFY sex ENUM('male','female') AFTER sname;
3 Query OK, 0 rows affected (0.02 sec)
4 Records: 0 Duplicates: 0 Warnings: 0
5
6 DESC staff;
7 +-------+-----------------------+------+-----+---------+-------+
8 | Field | Type | Null | Key | Default | Extra |
9 +-------+-----------------------+------+-----+---------+-------+
10 | id | int(4) | YES | | NULL | |
11 | sname | varchar(20) | YES | | NULL | |
12 | sex | enum('male','female') | YES | | NULL | |
13 | age | int(3) | YES | | NULL | |
14 | phone | bigint(11) | YES | | NULL | |
15 | job | varchar(11) | YES | | NULL | |
16 +-------+-----------------------+------+-----+---------+-------+
17 rows in set (0.00 sec)
修改字段属性3
6.创建自增id主键
1 -- 创建自增id主键
2 ALTER TABLE staff MODIFY id INT(4) PRIMARY KEY AUTO_INCREMENT;
3 Query OK, 4 rows affected (0.02 sec)
4 Records: 4 Duplicates: 0 Warnings: 0
5
6 DESC staff;
7 +-------+-----------------------+------+-----+---------+----------------+
8 | Field | Type | Null | Key | Default | Extra |
9 +-------+-----------------------+------+-----+---------+----------------+
10 | id | int(4) | NO | PRI | NULL | auto_increment |
11 | sname | varchar(20) | YES | | NULL | |
12 | sex | enum('male','female') | YES | | NULL | |
13 | age | int(3) | YES | | NULL | |
14 | phone | bigint(11) | YES | | NULL | |
15 | job | varchar(11) | YES | | NULL | |
16 +-------+-----------------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)
创建自增id主键
7.删除主键
1 -- 删除主键，可以看到删除一个自增主键会报错
2 ALTER TABLE staff DROP PRIMARY KEY;
3 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
4
5 -- 需要先去掉主键的自增约束，然后再删除主键约束
6 ALTER TABLE staff MODIFY id INT(11);
7 Query OK, 4 rows affected (0.02 sec)
8 Records: 4 Duplicates: 0 Warnings: 0
9
10 DESC staff;
11 +-------+-----------------------+------+-----+---------+-------+
12 | Field | Type | Null | Key | Default | Extra |
13 +-------+-----------------------+------+-----+---------+-------+
14 | id | int(11) | NO | PRI | 0 | |
15 | sname | varchar(20) | YES | | NULL | |
16 | sex | enum('male','female') | YES | | NULL | |
17 | age | int(3) | YES | | NULL | |
18 | phone | bigint(11) | YES | | NULL | |
19 | job | varchar(11) | YES | | NULL | |
20 +-------+-----------------------+------+-----+---------+-------+
21 rows in set (0.01 sec)
22
23 ALTER TABLE staff DROP PRIMARY KEY;
24 Query OK, 4 rows affected (0.06 sec)
25 Records: 4 Duplicates: 0 Warnings: 0
删除主键
其他有关主键操作
1 -- 添加联合主键
2 ALTER TABLE staff ADD PRIMARY KEY(sname, age);
3 Query OK, 0 rows affected (0.02 sec)
4 Records: 0 Duplicates: 0 Warnings: 0
5
6 -- 删除主键
7 ALTER TABLE staff DROP PRIMARY KEY;
8 Query OK, 4 rows affected (0.02 sec)
9 Records: 4 Duplicates: 0 Warnings: 0
10
11 -- 设置id为主键
12 ALTER TABLE staff ADD PRIMARY KEY(id);
13 Query OK, 0 rows affected (0.02 sec)
14 Records: 0 Duplicates: 0 Warnings: 0
15
16 DESC staff;
17 +-------+-----------------------+------+-----+---------+-------+
18 | Field | Type | Null | Key | Default | Extra |
19 +-------+-----------------------+------+-----+---------+-------+
20 | id | int(11) | NO | PRI | 0 | |
21 | sname | varchar(20) | NO | | | |
22 | sex | enum('male','female') | YES | | NULL | |
23 | age | int(3) | NO | | 0 | |
24 | phone | bigint(11) | YES | | NULL | |
25 | job | varchar(11) | YES | | NULL | |
26 +-------+-----------------------+------+-----+---------+-------+
27 rows in set (0.00 sec)
28
29 -- 为主键添加自增属性
30 ALTER TABLE staff MODIFY id INT(4) AUTO_INCREMENT;
31 Query OK, 4 rows affected (0.02 sec)
32 Records: 4 Duplicates: 0 Warnings: 0
33
34 DESC staff;
35 +-------+-----------------------+------+-----+---------+----------------+
36 | Field | Type | Null | Key | Default | Extra |
37 +-------+-----------------------+------+-----+---------+----------------+
38 | id | int(4) | NO | PRI | NULL | auto_increment |
39 | sname | varchar(20) | NO | | | |
40 | sex | enum('male','female') | YES | | NULL | |
41 | age | int(3) | NO | | 0 | |
42 | phone | bigint(11) | YES | | NULL | |
43 | job | varchar(11) | YES | | NULL | |
44 +-------+-----------------------+------+-----+---------+----------------+
45 rows in set (0.00 sec)
其他有关主键操作
以上
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
