添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
mysql> alter table users add age tinyint unsigned not null default 10;
mysql> show columns from users;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+-------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table users add password varchar(20) not null after name;
mysql> show columns from users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ...)
mysql> alter table users add (weight tinyint unsigned not null, height tinyint unsigned);
mysql> show columns from users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| weight | tinyint(3) unsigned | NO | | NULL | |
| height | tinyint(3) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name
mysql> ALTER TABLE users DROP weight, DROP height;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name)
# 创建新表
mysql> CREATE TABLE users3 (id SMALLINT, name VARCHAR(20), pid SMALLINT UNSIGNED);
Query OK, 0 rows affected (0.23 sec)
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 添加主键
mysql> ALTER TABLE users3 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY;
添加唯一约束
ALTER TABLE tlb_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name, ...);
mysql> ALTER TABLE users3 ADD UNIQUE KEY (name);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
注意：这边存在index_name是因为可以存在多个唯一约束。故我们应该先学会怎么查看约束的名称。
mysql> SHOW INDEXES FROM users3G;
*************************** 1. row ***************************
Table: users3
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: users3
Non_unique: 0
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: users3
Non_unique: 1
Key_name: pid
Seq_in_index: 1
Column_name: pid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)
# 我们想删除name字段上的index，可以看到该字段的index名字为name
mysql> ALTER TABLE users3 DROP INDEX name;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEXES FROM users3G;
*************************** 1. row ***************************
Table: users3
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: users3
Non_unique: 1
Key_name: pid
Seq_in_index: 1
Column_name: pid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES reference_definition;
mysql> ALTER TABLE users3 ADD FOREIGN KEY (pid) REFERENCES province(id);
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+-------+----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> SHOW CREATE TABLE users3;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users3 | CREATE TABLE `users3` (
`id` smallint(6) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `pid` (`pid`),
CONSTRAINT `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
注意：这个fk_symbol可以在上面的 SHOW CREATE TABLE users3;中可以看到，为users3_ibfk_1。
mysql> SHOW CREATE TABLE users3;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users3 | CREATE TABLE `users3` (
`id` smallint(6) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`age` int(11),
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE users3 DROP FOREIGN KEY users3_ibfk_1;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE users3;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users3 | CREATE TABLE `users3` (
`id` smallint(6) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`age` int(11),
PRIMARY KEY (`id`),
KEY `pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
添加删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE users3 ALTER age SET DEFAULT 15;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | int(11) | YES | | 15 | |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE users3 ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
修改数据表
- 修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name];
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
mysql> ALTER TABLE users3 MODIFY age INT AFTER name;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];
mysql> ALTER TABLE users3 CHANGE pid province_id SMALLINT UNSIGNED;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| province_id | smallint(5) unsigned | YES | MUL | NULL | |
+-------------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 修改数据表名称
# 方法1
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
# 方法2
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...
mysql> ALTER TABLE users3 RENAME TO users33;
mysql> RENAME TABLE users2 TO users22, users TO users11;