MySQL之表操作 (2)

语法：alter table 表名 add 新属性名 新属性类型 原有字段

mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table user add phone varchar(11); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

新增有完整性约束的字段

语法：alter table 表名 add 新属性名 新属性类型 [完整性约束]

mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table user add age int(3) not null; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)

将字段添加到第一位

语法：alter table 表名 add 新属性名 新属性类型 [完整性约束] first

mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table user add num int primary key first; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | num | int(11) | NO | PRI | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)

将字段添加到某个字段之后

语法：alter table 表名 add 新属性名 新属性类型 [完整性约束] after 原有字段

mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | num | int(11) | NO | PRI | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table user add address varchar(255) after phone; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | num | int(11) | NO | PRI | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | address | varchar(255) | YES | | NULL | | | age | int(3) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)

删除字段