sql语句添加和删除数据表字段

sql语句添加和修改数据表字段

1.在某个字段后面添加一个字段

-- 为uu表的name字段后添加一个m字段，int类型非空默认值为20
mysql> alter table uu add m int not null default 20 after name;
Query OK, 5 rows affected (0.11 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> desc uu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(16)   | YES  |     | NULL    |       |
| m     | int(11)       | NO   |     | 20      |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('m','w') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select * from uu;
+------+-----------+----+------+------+
| id   | name      | m  | age  | sex  |
+------+-----------+----+------+------+
|    1 | zhangsan  | 20 |   20 | m    |
|    2 | lisi      | 20 |   25 | w    |
|    4 | zhaoliu   | 20 |   25 | w    |
|    5 | xiaozhang | 20 |   24 | m    |
|    6 | xiaoli    | 20 |   23 | w    |
+------+-----------+----+------+------+
5 rows in set (0.00 sec)

2.在表的第一列添加一个字段

-- 在uu表的第一列添加一个x字段
mysql> alter table uu add x int first;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from uu;
+------+------+-----------+----+------+------+
| x    | id   | name      | m  | age  | sex  |
+------+------+-----------+----+------+------+
| NULL |    1 | zhangsan  | 20 |   20 | m    |
| NULL |    2 | lisi      | 20 |   25 | w    |
| NULL |    4 | zhaoliu   | 20 |   25 | w    |
| NULL |    5 | xiaozhang | 20 |   24 | m    |
| NULL |    6 | xiaoli    | 20 |   23 | w    |
+------+------+-----------+----+------+------+
5 rows in set (0.00 sec)

大家可以试试before 和last 应该也是可以用的。

3.删除一个表字段

-- 删除uu表的x字段。
mysql> alter table uu drop x;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from uu;
+------+-----------+----+------+------+
| id   | name      | m  | age  | sex  |
+------+-----------+----+------+------+
|    1 | zhangsan  | 20 |   20 | m    |
|    2 | lisi      | 20 |   25 | w    |
|    4 | zhaoliu   | 20 |   25 | w    |
|    5 | xiaozhang | 20 |   24 | m    |
|    6 | xiaoli    | 20 |   23 | w    |
+------+-----------+----+------+------+
5 rows in set (0.00 sec)

4.修改表字段及属性：

欢迎转载，转载需注明地址： http://www.luowebs.com/Skill_desc/15/590.html

很赞哦！ (0)