表修改语法之列的增删改

最新推荐文章于 2022-10-16 22:26:18 发布

lyuharvey

最新推荐文章于 2022-10-16 22:26:18 发布

阅读量114

点赞数

原文链接：http://blog.51cto.com/shansongxian/1428246

版权

新建一个表，列使用上节我们使用的白领私密社交表
mysql> create table m1(
-> id int unsigned auto_increment primary key
-> )engine mysiam charset utf8;
一张表创建完毕，有了N列，之后还有可能要增加或删除或修改列
新增列：
2.1: alter table 表名 add 列名称列类型列参数【加的列在表的最后】
mysql> alter table m1 add birth date not null default '0000-00-00';
mysql> desc m1;
+-------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| birth | date | NO | | 0000-00-00 | |
+-------+------------------+------+-----+------------+----------------+
2.2: alter table 表名 add 列名称列类型列参数 after 某列【加新列加在某列后】
mysql> alter table m1 add username char(20) not null default '' after id;
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
2.3: alter table 表名 add 列名称列类型列参数 first 【把新列加在最前面】
mysql> alter table m1 add pid int not null default 0 first;
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| pid | int(11) | NO | | 0 | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
如果想新建一个列，且在表的最前面，用first
删除列：
用法：alter table 表名 drop 列名
mysql> alter table m1 drop pid;
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
修改列
修改列类型：
用法： alter table 表名 modirfy 列名新类型新参数
mysql> alter table m1 modify username varchar(30) not null default '';
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+----------------+
修改列名及列类型：
用法：alter table 表名 change 旧列名新列名新类型新参数
mysql> alter table m1 change id uid int unsigned;
mysql> desc m1;
+----------+------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------+-------+
| uid | int(10) unsigned | NO | PRI | 0 | |
| username | varchar(30) | NO | | | |
| birth | date | NO | | 0000-00-00 | |
+----------+------------------+------+-----+------------+-------+