mysql中对表字段的增加,修改(修改类型),删除,排序
1.建立一个new 表,表中字段:学号,姓名,年龄。
mysql> create table new (ne_id int(10),ne_name varchar(20),ne_age int(12));
Query OK, 0 rows affected (0.97 sec)
2.表的结构图
mysql> desc new;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ne_id | int(10) | YES | | NULL | |
| ne_name | varchar(20) | YES | | NULL | |
| ne_age | int(12) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
rows in set (0.01 sec)
3.向new 表中添加一个地址
mysql> alter table new add ne_address varchar(20);
Query OK, 0 rows affected (1.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc new;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ne_id | int(10) | YES | | NULL | |
| ne_name | varchar(20) | YES | | NULL | |
| ne_age | int(12) | YES | | NULL | |
| ne_address | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)
4.将字段名ne_id 改名为 ne_nor
mysql> alter table new change ne_id ne_nor int(10);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc new;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ne_nor | int(10) | YES | | NULL | |
| ne_name | varchar(20) | YES | | NULL | |
| ne_age | int(12) | YES | | NULL | |
| ne_address | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
5.删除字段ne_nor
mysql> alter table new drop ne_nor;
Query OK, 0 rows affected (1.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc new;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ne_name | varchar(20) | YES | | NULL | |
| ne_age | int(12) | YES | | NULL | |
| ne_address | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
6.修改字段类型:将ne_age 的 int 改为 varchar
mysql> alter table new modify ne_age varchar(20);
Query OK, 0 rows affected (1.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc new;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ne_name | varchar(20) | YES | | NULL | |
| ne_age | varchar(20) | YES | | NULL | |
| ne_address | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
7.排序
mysql> alter table new modify ne_age varchar(20) after ne_address;
Query OK, 0 rows affected (0.98 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc new;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ne_name | varchar(20) | YES | | NULL | |
| ne_address | varchar(20) | YES | | NULL | |
| ne_age | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)