实验表为tt:
CREATE TABLE tt
(
a INT NOT NULL,
b INT NOT NULL,
c INT NOT NULL,
PRIMARY KEY(a),
UNIQUE uni_b(b),
INDEX idx_c(c)
);
显示表结构:
mysql> show create table tt;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `uni_b` (`b`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1 添加字段
添加一个新字段d,int类型,非空。
mysql> ALTER TABLE tt ADD COLUMN d INT NOT NULL;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tt;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `uni_b` (`b`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
命令格式为: ALTER TABLE 表名 ADD COLUMN 字段名 其他属性;
2 修改字段属性
修改字段d的类型为varchar(64),其他属性不变。
mysql> ALTER TABLE tt MODIFY d VARCHAR(64) NOT NULL;
Query OK, 0 rows affected (1.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tt;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` varchar(64) NOT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `uni_b` (`b`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
命令格式为:ALTER TABLE 表名 MODIFY 字段名 新的属性;
3 修改字段名称
修改字段d的名称为dd,其他属性不变。
mysql> ALTER TABLE tt CHANGE d dd VARCHAR(64) NOT NULL;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tt;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`dd` varchar(64) NOT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `uni_b` (`b`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
命令格式为:ALTER TABLE 表名 CHANGE 旧字段名 新字段名 其他属性
;
4 删除字段
删除字段dd。
mysql> ALTER TABLE tt DROP COLUMN dd;
Query OK, 0 rows affected (0.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tt;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `uni_b` (`b`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
命令格式为:ALTER TABLE 表名 DROP COLUMN 字段名
;
总结:
添加字段: ALTER TABLE 表名 ADD COLUMN
字段名 属性
修改字段属性:ALTER TABLE 表名 MODIFY
字段名 属性
修改字段名称:ALTER TABLE 表名 CHANGE
旧字段名 新字段名 属性
删除字段: ALTER TABLE 表名 DROP COLUMN
字段名