MySQL学习--修改表的语法

修改表的语法

1.增加列

• 加在列的最后

alter table 表名 add 列名称 列类型 列参数

• 加在某一列的后面

alter table 表名 add 列名称 列类型 列参数 after 某列

• 把新列放在第一位

alter table 表名 add 列名称 列类型 列参数 first

mysql> create table m1(
    -> id int unsigned auto_increment primary key
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.45 sec)

#将新列添加到最后

mysql> alter table m1 add username varchar(20) not null default '';
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | NO   |     |         |                |
+----------+--------------+------+-----+---------+----------------+
2 rows in set (0.12 sec)

mysql> alter table m1 add birth date;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)  | NO   |     |         |                |
| birth    | date         | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

#将新列添加到某列后

mysql> alter table m1 add gender char(1) not null default '' after username;
Query OK,