本章目录
一、单列插入，位置默认列底
二、单列插入，位于某列之后
三、单列插入，位于列顶端
四、多列插入，不能指定位置，只能位于列底
五、删除列、删除并且添加（单行和多行同上，列之间逗号隔开）
本章语句
- alter table tbl_name add[column] col_name column_definition[first|after col_name] 数据表删除或增加语句
- 解释：更改 表格 某某，添加 ，某某列名 ，列定义，【顶部|某某之后】
数据表的修改操作案例：
一、创建一个数据表city1，并添加了约束
mysql> create table city1(
-> id smallint unsigned primary key auto_increment,
-> usename varchar(20) not null,
-> pid smallint unsigned,
-> foreign key(pid) references sheng(id) on delete cascade)
//查看表结构：mysql> show columns from city1
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+---------+----------------------+------+-----+---------+----------------+一、单列插入
语句：alter table city1 add age tinyint UNSIGNED NOT NULL DEFAULT 10;
解释：更改 表格 city1， 添加参数age ，类型：tinyint ，无符号，非空，默认值10
//更改表格city1,添加age列，类型：tinyint ，无符号类，非空，默认值：10
mysql> alter table city1 add age tinyint UNSIGNED NOT NULL DEFAULT 10;
Query OK, 2 rows affected (0.53 sec)//插入成功
//查看插入结果，会发现我们数据表中增加了一列，默认值为10.
mysql> SHOW COLUMNS FROM CITY1;//不指定位置默认放在列末尾
//插入前不存在age列
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+---------+----------------------+------+-----+---------+----------------+
//插入后存在age列，插入成功
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+---------+----------------------+------+-----+---------+----------------+
二、单列插入，位于某列之后
语句：alter table city1 add password varchar(10) NOT NULL AFTER usename;
解释：更改 表格 city1 添加参数password 字符类型 位置位于usename后
注意：这里我们指定了某列之后：AFTER usename，就是位置位于usename下方
mysql> alter table city1 add password varchar(10) NOT NULL AFTER usename;
Query OK, 2 rows affected (0.32 sec)
//插入成功
mysql> show columns from city1
//插入前，查看city1表的结构不存在password列
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+---------+----------------------+------+-----+---------+----------------+
//插入后，查看city1表的结构，我们插入的password列位于usename 之后
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+三、单列插入，位于列顶端
语句：alter table city1 add tname varchar(22) NOT NULL FIRST;
解释：更改 表格 city1 添加参数tname 字符类型 不能为空 位于顶部
mysql> alter table city1 add tname varchar(22) NOT NULL FIRST;
Query OK, 2 rows affected (0.25 sec)
//插入成功过
mysql> SHOW COLUMNS FROM CITY1;//查看city1表的结构，我们插入的tname列位于列的顶端
//插入前，查询列表，暂无tname列
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
//插入后，查询列表，tname列位于列表的顶端
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| tname | varchar(22) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
四、多列插入，不能指定位置，只能位于列底
语句：alter table t_name add[column](col_name column_definition,……)
解释：更改 表格 某某 添加 【列】 （某列，某列，……）
mysql> alter table city1 add (
-> ttt varchar(20) NOT NULL,
-> uuu varchar(20) NOT NULL);
Query OK, 2 rows affected (0.33 sec)
//添加成功
mysql> show columns from city1;//添加的两列ttt和uuu位于列的最下方
//插入前
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| tname | varchar(22) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
//插入后，ttt和uuu两列插入成功
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| tname | varchar(22) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| ttt | varchar(20) | NO | | NULL | |
| uuu | varchar(20) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)
五、删除列、删除并且添加（单行和多行同上，列之间逗号隔开）
语句：alter table tal_name drop [column] col_name
解释：更改 表格 表名册 删除 【列】 列名册
1、单行删除
//单列删除age，删除成功
mysql> alter table city1 drop age; //翻译：更改表格city1 删除age列
mysql> show columns from city1;//查看city1表结构
//删除前，age列存在
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| tname | varchar(22) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| ttt | varchar(20) | NO | | NULL | |
| uuu | varchar(20) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
//删除后，age删除成功
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| tname | varchar(22) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| ttt | varchar(20) | NO | | NULL | |
| uuu | varchar(20) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2、多列删除
2、多列删除，同时删除ttt和uuu列，（注意多行删除，删除列需要用逗号隔开，如：drop ttt,drop uuu）
//更改表格city1,删除ttt,uuu两列
mysql> alter table city1 drop ttt,drop uuu;
mysql> show columns from city1;//查看
//删除前
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| tname | varchar(22) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| ttt | varchar(20) | NO | | NULL | |
| uuu | varchar(20) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
//删除后
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| tname | varchar(22) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+3、删除和添加并用
//更改表格city1,删除tname列，添加vvv列
mysql> alter table city1 drop tname,add vvv varchar(21) NOT NULL;
Query OK, 2 rows affected (0.27 sec)
mysql> show columns from city1;//查看效果
//删除前
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| tname | varchar(22) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
//删除后tname列消失，最下面出现vvv列
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| usename | varchar(20) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| vvv | varchar(21) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)