MySQL study Note 5: modify the table bitsCN.com
When creating a table, we will inevitably consider the problem of weeks. Therefore, the table will be modified later.
The alter table statement is required to modify a table.
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.
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 need to modify it, you only need to write it as the original data type.
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.
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.
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.
BitsCN.com