ALTER
当我们需要修数据表的名字或者修改数据表的字段时，就需要使用alter命令。
一：删除，添加，或者修改表字段(字段名，字段类型)。
删除表字段语法：alter table 表名 drop 字段名； 下面是示例！
mysql> alter table test drop i;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(50) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
添加表字段语法： alter table 表名 add 字段名 子弹类型；
mysql> alter table test add age int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
如果你想让你添加的字段到最前面或者具体某个字段之后，那么可以用参数，比如first,after，类似如下命令：
mysql> alter table test add level int first; #将新增的字段level放在最前面mysql> alter table test add num int after level; #将新增的num字段放在level字段之后
如果需要修改字段类型及名称，可以用modify或者change，
修改表字段类型语法：alter table 表名 modify 字段名 字段类型 (这里是要修改的字段名);
mysql> alter table test modify name char(30); #将name字段类型修改为char(30)
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| level | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
修改字段名称语法：alter table 表名 change 原字段名 新字段名 字段类型;
mysql> alter table test change level jibie int; #将level字段名修改为jibie，类型为int型；
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| jibie | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
二：修改字段默认值和表名
修改字段默认值语法： alter talve 表名 alter 字段名 set default 值;
mysql> alter table test alter jibie set Default 1000; #将test表中的jibie字段的默认值修改为1000
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| jibie | int(11) | YES | | 1000 | |
| num | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)如果不需要默认值，您也可以用drop来删除它
语法：alter table 表名 alter 字段名 drop default;
mysql> ALTER TABLE test ALTER jibie DROP DEFAULT; #把test表中的jibie子段的默认值删除掉
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| jibie | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
修改表名语法：alter table 表名 rename to 新表名;
mysql> alter table test rename to dabai; #修改表名为dabai
Query OK, 0 rows affected (0.00 sec)mysql> show tables;
+-----------------+
| Tables_in_dabai |
+-----------------+
| dabai |
+-----------------+
1 row in set (0.00 sec)