MySQL study note 5: modify the table of alter table

It is inevitable that we will be careless in the process of creating the table, so we will modify the table later and we need to use alter table statement to modify the table
Modify the name of the table

 
mysql> alter table student rename person; 
Query OK, 0 rows affected (0.03 sec) 

Here student is the original name and person is the modified name
Rename with rename, or you can use rename to
Modify the data type of the field

 
mysql> alter table person modify name varchar(20); 
Query OK, 0 rows affected (0.18 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

Here, name after modify is the name of the field. We will change varchar(25) to varchar(20).
Modify the field name

 
mysql> alter table person change stu_name name varchar(25); 
Query OK, 0 rows affected (0.20 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

Here stu_name is the original name and name is the new name
Note that whether you change the data type or not, the data type will always be written
If you don't modify the data type, just write the original data type
tips: we can also use change to achieve modify by writing the field name of the 1 sample after it
Adds fields without integrity constraints

 
mysql> alter table person add sex boolean; 
Query OK, 0 rows affected (0.21 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

Here, sex is followed only by data types, with no integrity constraints
Adds fields with integrity constraints

 
mysql> alter table person add age int not null; 
Query OK, 0 rows affected (0.17 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

An age field is added, followed by an not null integrity constraint
Add a field to the header

 
mysql> alter table person add num int primary key first; 
Query OK, 0 rows affected (0.20 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

By default, fields are added to the end of the table, and first is added to the top of the table after the add statement
Adds a field at the specified location

 
mysql> alter table person add birth date after name; 
Query OK, 0 rows affected (0.20 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

Here a new field is added after the name field
tps: the sorting of the fields in the table has no effect on the table, but a more reasonable sorting makes it easier to understand the table
Delete the field

 
mysql> alter table person drop sex; 
Query OK, 0 rows affected (0.18 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

drop is also needed here, just like the previous delete table or database 1
The difference is that to delete a field, alter table is also followed by the table name
Modify the field to the first location

 
mysql> alter table person modify id int first; 
Query OK, 0 rows affected (0.20 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

first was mentioned earlier, but the important thing to note here is that you write the data type after the field
Modify the field to the specified location

 
mysql> alter table person modify name varchar(25) after id; 
Query OK, 0 rows affected (0.18 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

We put the name field after id, where varchar(25) is full, varchar is not
desc table is recommended before performing the above steps
Modify the storage engine for the table

 
mysql> alter table user rename person; 
Query OK, 0 rows affected (0.05 sec) 

Here does not talk about the specific characteristics of each storage engine, the content is more
Don't forget to use the show create table statement after the modification
tips: do not modify the storage engine if there is a lot of data already in the table
Adds a foreign key to the table

 
mysql> alter table score add constraint fk foreign key(stu_id) references student(id); 
Query OK, 10 rows affected (0.18 sec) 
Records: 10 Duplicates: 0 Warnings: 0 

All you need to do here is to add add. See the foreign key Settings in section 4 for the syntax below
Remove the foreign key constraint for the table

 
mysql> alter table student3 drop foreign key fk; 
Query OK, 0 rows affected (0.18 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

Since the basic table structure description cannot display the foreign keys, it is best to view the table using show create table before doing so
fk here is the foreign key you just set
Note that if you want to delete an associated table, you must first delete the foreign key
After removing the foreign key, the original key becomes a normal key
For deleting a table, write in section 3, and set the foreign key in section 4. If you did not set the foreign key when you created the table, you can use the above method