mysql改变键值属性_三十一：MySQL约束条件修改表结构键值

1.修改表结构

添加字段

修改字段名

修改字段类型

删除字段

1.1 test库中创建t1表

mysql> CREATE TABLE test.t1(

-> id int(4) PRIMARY

KEY,

-> age int(2) NOT

NULL

-> );

1.2 t1表中添加address字段

mysql> ALTER TABLE t1 ADD address varchar(48);

默认作为最后一个字段

+---------+-------------+------+-----+---------+-------+

| Field  | Type  | Null |

Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| id  |

int(4)  | NO

| PRI | NULL  |  |

| age  | int(2)

| NO

|  | NULL

|  |

| address | varchar(48) | YES  |

| NULL  |  |

+---------+-------------+------+-----+---------+-------+

1.3 在t1表的age列之后添加一个gender字段

mysql> ALTER TABLE t1 ADD gender enum('boy','girl') AFTER

age;

mysql> desc t1;

+---------+--------------------+------+-----+---------+-------+

| Field  | Type  | Null |

Key | Default | Extra |

+---------+--------------------+------+-----+---------+-------+

| id  |

int(4)  | NO

| PRI | NULL  |  |

| age  | int(2)

| NO

|  | NULL

|  |

| gender  | enum('boy','girl') | YES

|  | NULL

|  |

| address | varchar(48)  | YES  |

| NULL  |  |

+---------+--------------------+------+-----+---------+-------+

*****AFTER\FIRST*****

2.修改字段名和字段类型

2.1 将t1表的gender字段改名为sex，并添加非空约束

mysql> ALTER ATBLE t1 CHANGE gender

-> sex enum('boy','girl')

NOT NULL;

mysql> desc t1;

+---------+--------------------+------+-----+---------+-------+

| Field  | Type  | Null |

Key | Default | Extra |

+---------+--------------------+------+-----+---------+-------+

| id  |

int(4)  | NO

| PRI | NULL  |  |

|