Add, delete, modify, and sort table fields in Mysql
1. Create a new table with the following fields: Student ID, name, and age. 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. table Structure 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. add an address 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. rename the field name ne_id to ne_normysql> 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. delete the field 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. modify Field Type: Change the ne_age int to varcharmysql> 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. sort 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)