当前位置：网站首页>Mysql5.6 alter statement

Mysql5.6 alter statement

2022-07-19 11:46:00 【51CTO】

1. Rename table name

mysql> rename table tanks to Tanks;
Query OK, 0 rows affected (0.01 sec)
#  perhaps 
mysql> alter table Tanks rename to TANKS;
Query OK, 0 rows affected (0.01 sec)

     1.
2.
3.
4.
5.

2. Add fields

#  Insert at the end 
mysql> alter table tanks add introduction varchar(255) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
#  Insert after specified column 
mysql> alter table tanks add summoner_skills ENUM('flush','fire') not null default 'flush' after skills;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

     1.
2.
3.
4.
5.
6.
7.
8.

3. Delete field

4. Batch addition and deletion of fields

mysql> alter table tanks add camp varchar(50),add pic varchar(255) after price;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | 0       |       |
| name         | varchar(100) | NO   |     | NULL    |       |
| skills       | varchar(255) | NO   |     | NULL    |       |
| price        | int(11)      | NO   |     | NULL    |       |
| pic          | varchar(255) | YES  |     | NULL    |       |
| introduction | varchar(255) | NO   |     | NULL    |       |
| camp         | varchar(50)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
#  It was found that only one was specified after, The other column is still at the end , Delete RE addition 
mysql> alter table tanks drop camp,drop pic;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | 0       |       |
| name         | varchar(100) | NO   |     | NULL    |       |
| skills       | varchar(255) | NO   |     | NULL    |       |
| price        | int(11)      | NO   |     | NULL    |       |
| introduction | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> alter table tanks add camp varchar(50) after price,add pic varchar(255) after camp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | 0       |       |
| name         | varchar(100) | NO   |     | NULL    |       |
| skills       | varchar(255) | NO   |     | NULL    |       |
| price        | int(11)      | NO   |     | NULL    |       |
| camp         | varchar(50)  | YES  |     | NULL    |       |
| pic          | varchar(255) | YES  |     | NULL    |       |
| introduction | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

     1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

5. Modify field type （ At the same time, the name of the gang has also been changed ）

mysql> alter table tanks change pic pic_url char(200);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> desc tanks;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | 0       |       |
| name         | varchar(100) | NO   |     | NULL    |       |
| skills       | varchar(255) | NO   |     | NULL    |       |
| price        | int(11)      | NO   |     | NULL    |       |
| camp         | varchar(50)  | YES  |     | NULL    |       |
| pic_url      | char(200)    | YES  |     | NULL    |       |
| introduction | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

     1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.