用上文的user1表，查看表结构：
mysql> DESC user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)添加一个默认值为10的非空字段
mysql> ALTER TABLE user1 ADD age SMALLINT UNSIGNED NOT NULL DEFAULT 10;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | MUL | NULL | |
| age | smallint(5) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)在某列后添加字段：AFTER(FIRST添加到最前面)
mysql> ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | MUL | NULL | |
| age | smallint(5) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)添加多列：
mysql> ALTER TABLE user1 ADD (aaa VARCHAR(32), bbb VARCHAR(32),ccc VARCHAR(32));
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| sax | varchar(32) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | MUL | NULL | |
| age | smallint(5) unsigned | NO | | 10 | |
| aaa | varchar(32) | YES | | NULL | |
| bbb | varchar(32) | YES | | NULL | |
| ccc | varchar(32) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)删除列aaa:
mysql> ALTER TABLE user1 DROP aaa;
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| sax | varchar(32) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | MUL | NULL | |
| age | smallint(5) unsigned | NO | | 10 | |
| bbb | varchar(32) | YES | | NULL | |
| ccc | varchar(32) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)同时删除两列：bbb，ccc
mysql> ALTER TABLE user1 DROP bbb,DROP ccc;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| sax | varchar(32) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | MUL | NULL | |
| age | smallint(5) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)删除主键约束:(必须是没有自增长的主键，自增长要依附于主键删不了，key_test符合)
mysql> ALTER TABLE user1 DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> DESC key_test;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE key_test DROP PRIMARY KEY;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DESC key_test;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)删除唯一约束
mysql> SHOW INDEXES FROM unique_test;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| unique_test | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| unique_test | 0 | username | 1 | username | A | 2 | NULL | NULL | YES | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> ALTER TABLE unique_test DROP INDEX username;
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEXES FROM unique_test;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| unique_test | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)删除外键约束：注意外键约束名：user1_ibfk_1
mysql> SHOW CREATE TABLE user1;
+-------+---------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-------------------------------------+
| Table | Create Table
|
+-------+---------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-------------------------------------+
| user1 | CREATE TABLE `user1` (
`sax` varchar(32) NOT NULL,
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(32) NOT NULL,
`p_id` smallint(5) unsigned DEFAULT NULL,
`age` smallint(5) unsigned NOT NULL DEFAULT '10',
PRIMARY KEY (`id`),
KEY `p_id` (`p_id`),
CONSTRAINT `user1_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `province` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-------------------------------------+
1 row in set (0.41 sec)
mysql> ALTER TABLE user1 DROP FOREIGN KEY user1_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user1;
+-------+-------------------------------------------------------------
----------------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------
----------------------------------------------------------------------
| user1 | CREATE TABLE `user1` (
`sax` varchar(32) NOT NULL,
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(32) NOT NULL,
`p_id` smallint(5) unsigned DEFAULT NULL,
`age` smallint(5) unsigned NOT NULL DEFAULT '10',
PRIMARY KEY (`id`),
KEY `p_id` (`p_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------
----------------------------------------------------------------------
1 row in set (0.00 sec)外键去除后，索引p_id就没有用了，也可以去除：
mysql> ALTER TABLE user1 DROP INDEX p_id;
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user1;
+-------+--------------------------------------------------------
-----------------------------------------------------------------
| Table | Create Table
+-------+--------------------------------------------------------
-----------------------------------------------------------------
| user1 | CREATE TABLE `user1` (
`sax` varchar(32) NOT NULL,
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`password` varchar(32) NOT NULL,
`p_id` smallint(5) unsigned DEFAULT NULL,
`age` smallint(5) unsigned NOT NULL DEFAULT '10',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------
-----------------------------------------------------------------
1 row in set (0.00 sec)修改列的位置：想把user1中的id调到最上面
mysql> DESC user1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| sax | varchar(32) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | | NULL | |
| age | smallint(5) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE user1 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
Query OK, 2 rows affected (0.44 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DESC user1;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| sax | varchar(32) | NO | | NULL | |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | | NULL | |
| age | smallint(5) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)修改列信息：将sax改为gender更好一点，再把长度改短点
mysql> ALTER TABLE user1 CHANGE sax gender VARCHAR(4) NOT NULL;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DESC user1;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| gender | varchar(4) | NO | | NULL | |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| p_id | smallint(5) unsigned | YES | | NULL | |
| age | smallint(5) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)表名user1不抢眼，改个名字吧
mysql> ALTER TABLE user1 RENAME 阿姆斯特朗回旋加速喷气式阿姆斯特朗炮;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+--------------------------------------------------------+
| Tables_in_zoom |
+--------------------------------------------------------+
| 阿姆斯特朗回旋加速喷气式阿姆斯特朗炮 |
| default_ |
| key_test |
| little |
| null_test |
| province |
| unique_id |
| unique_test |
| users |
+--------------------------------------------------------+
9 rows in set (0.00 sec)不行，太装X,用另一种方法改个低调的
mysql> RENAME TABLE 阿姆斯特朗回旋加速喷气式阿姆斯特朗炮 TO toly;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_zoom |
+----------------+
| default_ |
| key_test |
| little |
| null_test |
| province |
| toly |
| unique_id |
| unique_test |
| users |
+----------------+
9 rows in set (0.00 sec)