07.数据表的修改

用上文的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)

转载于:https://www.cnblogs.com/toly-top/p/9782023.html