mysql-修改字段属性(8）

modify和change关键字修改属性，使用modify和change关键字都可以修改字段属性，可以字段的数据格式类型，以及约束性条件。
但是change和modify也有不同之处的，change可以修改字段名，但是modify不能。下面分别测试一下change和Modify

--修改字段类型，字段属性
alter table table_name modify 字段名称 字段类型 [字段属性] [first | after]
--修改字段名称，字段类型，字段属性
alter table table_name change 原字段名称 新字段名称 字段属性[first | after]

mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   | UNI | 小明    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
----修改字段名name的同时修改varchar最大长度以及删除了默认值
mysql> alter table test_alter change name usrname varchar(30) unique not null;
Query OK, 0 rows affected, 1 warning (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc test_alter;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| usrname | varchar(30)      | NO   | UNI | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
-----可以看到name字段名称改成了usrname，varchar长度也已经修改，default默认值也删除了。

mysql> create table test_alter(
    -> id int unsigned auto_increment key,
    -> name varchar(30) not null unique)engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
---使用modify修改varchar数据长度
mysql> alter table test_alter
    -> modify name varchar(40) not null unique key;
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc test_alter;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
----添加两列addr(地址),email(邮箱)
mysql> alter table test_alter  add addr varchar(30) not null default '北京',  add email varchar(40) not null unique default '1122@126.com';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_alter;
+-------+------------------+------+-----+--------------+----------------+
| Field | Type             | Null | Key | Default      | Extra          |
+-------+------------------+------+-----+--------------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL         | auto_increment |
| name  | varchar(40)      | NO   | UNI | NULL         |                |
| addr  | varchar(30)      | NO   |     | 北京         |                |
| email | varchar(40)      | NO   | UNI | 1122@126.com |                |
+-------+------------------+------+-----+--------------+----------------+
4 rows in set (0.00 sec)
--使用Modify关键字同时修改addr字段和email字段的属性以及约束条件，中间采用逗号分隔符.
mysql> alter table test_alter modify addr varchar(40) default '深圳',  modify email varchar(50) not null unique default '12345@11.com';
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc test_alter;
+-------+------------------+------+-----+--------------+----------------+
| Field | Type             | Null | Key | Default      | Extra          |
+-------+------------------+------+-----+--------------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL         | auto_increment |
| name  | varchar(40)      | NO   | UNI | NULL         |                |
| addr  | varchar(40)      | YES  |     | 深圳         |                |
| email | varchar(50)      | NO   | UNI | 12345@11.com |                |
+-------+------------------+------+-----+--------------+----------------+
4 rows in set (0.00 sec)