MYSQL结构修改
mysql改表结构主要是5大操作
ADD 添加字段
MODIFY 修改字段类型
CHANGE 修改字段名(也可以修改字段名)
DROP 删除字段
RENAME 修改表名
ADD添加新字段:(新字段默认添加在所有字段末尾,可以设定位置)
语法结构为:
ALTER TABLE 库名.表名
ADD 列名 类型(范围) 约束条件
[ AFTER 字段名 | FRIST ]
mysql> DESC f_fri_list;
+---------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| type | enum('男','女') | NO | | NULL | |
| fri_id | int(10) unsigned | NO | | NULL | |
| fri_name | char(10) | NO | | NULL | |
| fri_phone_num | char(11) | YES | | NULL | |
| fri_email | varchar(20) | YES | | NULL | |
| fri_wecat | varchar(20) | YES | | NULL | |
| fri_brithday | date | YES | | NULL | |
| create_date | date | NO | | NULL | |
| create_time | time | NO | | NULL | |
+---------------+-------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
例如我在fri_name后添加一个fri_from
mysql> ALTER TABLE f_fri_list
-> ADD fri_from CHAR(30)
-> AFTER fri_name;
mysql> DESC f_fri_list;
+---------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| type | enum('男','女') | NO | | NULL | |
| fri_id | int(10) unsigned | NO | | NULL | |
| fri_name | char(10) | NO | | NULL | |
| fri_from | char(30) | YES | | NULL | |
| fri_phone_num | char(11) | YES | | NULL | |
| fri_email | varchar(20) | YES | | NULL | |
| fri_wecat | varchar(20) | YES | | NULL | |
| fri_brithday | date | YES | | NULL | |
| create_date | date | NO | | NULL | |
| create_time | time | NO | | NULL | |
+---------------+-------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
如果是要在列首添加一个行叫fri_ot
mysql> ALTER TABLE f_fri_list
-> ADD fri_ot char(100)
-> first;
mysql> DESC f_fri_list;
+---------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------------+------+-----+---------+----------------+
| fri_ot | char(100) | YES | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| type | enum('男','女') | NO | | NULL | |
| fri_id | int(10) unsigned | NO | | NULL | |
| fri_name | char(10) | NO | | NULL | |
| fri_from | char(30) | YES | | NULL | |
| fri_phone_num | char(11) | YES | | NULL | |
| fri_email | varchar(20) | YES | | NULL | |
| fri_wecat | varchar(20) | YES | | NULL | |
| fri_brithday | date | YES | | NULL | |
| create_date | date | NO | | NULL | |
| create_time | time | NO | | NULL | |
+---------------+-------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
MODIFY修改字段类型(修改的字段类型不能与已存储的数据冲突)
语法结构为
ALTER TABLE 库名.表名
MODIFY 列名 类型(范围) 约束条件
[ AFTER 字段名 | FRIST ]
除了关键字变成了MODIFY之外其他与ADD一致
例如我需要将fri_ot 类型改成 varchar类型(限定范围为200) 并放到最后一列去则
mysql> ALTER TABLE f_fri_list
-> MODIFY fri_ot varchar(100)
-> AFTER create_time;
mysql> DESC f_fri_list;
+---------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| type | enum('男','女') | NO | | NULL | |
| fri_id | int(10) unsigned | NO | | NULL | |
| fri_name | char(10) | NO | | NULL | |
| fri_from | char(30) | YES | | NULL | |
| fri_phone_num | char(11) | YES | | NULL | |
| fri_email | varchar(20) | YES | | NULL | |
| fri_wecat | varchar(20) | YES | | NULL | |
| fri_brithday | date | YES | | NULL | |
| create_date | date | NO | | NULL | |
| create_time | time | NO | | NULL | |
| fri_ot | varchar(100) | YES | | NULL | |
+---------------+-------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
CHANGE 修改字段名(也可以用来修改字段类型)
ALTER TABLE 库名.表名
CHANGE 源列名 新列名 类型(范围) 约束条件
例如 我需要将fri_ot 列名改为 fri_ot2
mysql> ALTER TABLE f_fri_list
-> CHANGE fri_ot fri_ot2 varchar(100);
mysql> DESC f_fri_list;
+---------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| type | enum('男','女') | NO | | NULL | |
| fri_id | int(10) unsigned | NO | | NULL | |
| fri_name | char(10) | NO | | NULL | |
| fri_from | char(30) | YES | | NULL | |
| fri_phone_num | char(11) | YES | | NULL | |
| fri_email | varchar(20) | YES | | NULL | |
| fri_wecat | varchar(20) | YES | | NULL | |
| fri_brithday | date | YES | | NULL | |
| create_date | date | NO | | NULL | |
| create_time | time | NO | | NULL | |
| fri_ot2 | varchar(100) | YES | | NULL | |
+---------------+-------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
删除字段(表中有记录时,所有记录中此字段的值都会被删除)
ALTER TABLE 库名.表名
DROP 列名
例如要删除fri_ot2字段
mysql> ALTER TABLE f_fri_list
-> DROP fri_ot2;
mysql> DESC f_fri_list;
+---------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| type | enum('男','女') | NO | | NULL | |
| fri_id | int(10) unsigned | NO | | NULL | |
| fri_name | char(10) | NO | | NULL | |
| fri_from | char(30) | YES | | NULL | |
| fri_phone_num | char(11) | YES | | NULL | |
| fri_email | varchar(20) | YES | | NULL | |
| fri_wecat | varchar(20) | YES | | NULL | |
| fri_brithday | date | YES | | NULL | |
| create_date | date | NO | | NULL | |
| create_time | time | NO | | NULL | |
+---------------+-------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
修改表名(表对应的文件名,也被改变)
ALTER TABLE 表名
RENAME 新表名
例如将f_fri_list 改成 fri_list
mysql> ALTER TABLE f_fri_list
-> RENAME fri_list;
mysql> SHOW TABLES;
+--------------------+
| Tables_in_f_friend |
+--------------------+
| f_fri_type |
| fri_list |
+--------------------+
2 rows in set (0.00 sec)
