Implementation of the MySQL note modification table

We can't help but think poorly when creating a table, so we change the table later

The alter table statement is needed 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

Another method is rename table old_name to new_name

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 the original 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 here, followed by an not null integrity constraint

Add additional integrity constraints

mysql> ALTER TABLE person ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

This is also used for multi-field Settings

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

tips: sorting the fields in a 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

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, and 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 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 easily modify the storage engine if a lot of data already exists 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 primary key

mysql> ALTER TABLE person DROP PRIMARY KEY;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

Since there are no aliases for primary keys, using drop removes all primary keys

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

As far as deleting a table is concerned, it is written in section 3, and setting the foreign key is also written in section 4

Use the above method if you did not set a foreign key when creating the table