MySQL之表操作 (3)

语法：alter table 表名 drop 字段名

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) mysql> alter table user drop id; 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 | | | 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 | | +---------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 修改字段排列顺序

将字段按修改到首位

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

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

将字段修改到指定位置

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

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

删除外键

语法：alter table 表名 drop foreign key 外键名;

清空表

清空表有两种方式

delete

语法：delete from 表名

Truncate

语法：truncate table 表名

删除表

语法：drop table 表名

mysql> show tables; +-------------------+ | Tables_in_example | +-------------------+ | example1 | | example2 | | example3 | | example4 | | user | +-------------------+ 5 rows in set (0.00 sec) mysql> drop table example4; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_example | +-------------------+ | example1 | | example2 | | example3 | | user | +-------------------+ 4 rows in set (0.00 sec)

在删表的时候必须谨慎，以免误删而导致数据丢失，所以在删除前做好备份工作

在删除表时，如果当前表存在外键，则先删除外键，再删除表

在删除关联外键表时，则先删除子表[存在外键的表]，再删除主表