MySQL study Note 5: alter table _ MySQL

MySQL study Note 5: modifying the table (altertable) bitsCN.com we will inevitably consider the week before creating the table. Therefore, the alter table statement is required to modify the table later.
Modify table name

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

Student is the original name and person is the modified name.
Rename or rename
Modify the data type of a field

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

Here, the name after modify is the field name. we will change the original varchar (25) to varchar (20)
Modify 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

Stu_name is the original name and name is the new name.
It should be noted that no matter whether the data type is changed, the subsequent data types must be written
If you do not modify the data type, you only need to write it as the original data type.
Tips: We can also use change to achieve the modify effect. we only need to write the same field name after it.
Add 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

The sex here is followed by only the data type, without the integrity constraints.
Add 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

Added an age field, followed by the not null integrity constraint.
Add fields 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, all fields are added to the end of the table. you can add the first field after the add statement to the table header.
Add a field to a specified position

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

Add a new field next to the name field.
Tps: sorting of fields in a table does not affect the table, but it is easier to understand the table with more reasonable sorting.
Delete field

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

Like deleting a table or database, you also need to use drop
The difference is that alter table must be used to delete a field with the table name.
Modify the field to the first position

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

First has been mentioned earlier. Note that the data type should be written after the field.
Modify a field to a specified position

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 behind the id. the varchar (25) here should be fully written, but varchar will not work.
We recommend that you desc table before performing the preceding steps.
Modify the storage engine of a table

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

The features of each storage engine are not described here. There are many contents.
After modification, do not forget to use the show create table Statement. Section 3 describes write usage.
Tips: If a table already contains a large amount of data, do not modify the storage engine easily.
Add 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

Here, you only need to use add. For more information about the syntax, see the foreign key settings in section 4.
Delete a table's foreign key constraint

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

Because the basic table structure description cannot display foreign keys, you 'd better use show create table to view the table before performing this operation.
The fk here is the foreign key just set.
Note: to delete an associated table, you must first delete the foreign key.
After the foreign key is deleted, the original key becomes a normal key.
To delete a table, write in Section 3 and set a foreign key in section 4. if no foreign key is set during table creation, use the above method bitsCN.com.