MySQL中修改数据表(增删列)

1、数据表的修改：包含了列的添加、列的删除、约束的添加、约束的删除。

2、添加单列

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

默认在最后一列之后添加：
mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

查看结果：
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

指定在username之后添加：
mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

查看结果：
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

指定在最前面添加：
mysql> ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

查看结果：
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(20)          | NO   |     | NULL    |                |
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

3、添加多列

mysql> ALTER TABLE users1 ADD (pwd VARCHAR(20) NOT NULL,name VARCHAR(20) NOT NULL);
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0
展示结果：
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(20)          | NO   |     | NULL    |                |
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| pwd      | varchar(20)          | NO   |     | NULL    |                |
| name     | varchar(20)          | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

4、删除单列

列的添加、列的删除、约束的添加、约束的删除
mysql> ALTER TABLE users1 DROP truename;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| pwd      | varchar(20)          | NO   |     | NULL    |                |
| name     | varchar(20)          | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
7 rows in set (0.02 sec)

5、删除多列

列的添加、列的删除、约束的添加、约束的删除
mysql> ALTER TABLE users1 DROP pwd,DROP age;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| name     | varchar(20)          | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

6、删除并添加列

列的添加、列的删除、约束的添加、约束的删除
mysql> ALTER TABLE users1 DROP username,ADD pwd VARCHAR(20) NOT NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| password | varchar(32)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| name     | varchar(20)          | NO   |     | NULL    |                |
| pwd      | varchar(20)          | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

列的添加、列的删除、约束的添加、约束的删除