When we create a table, we will inevitably think of it poorly, so we later modify the table modification table to use the ALTER TABLE statement
Modify Table name
Copy Code code as follows:
mysql> ALTER TABLE student rename person;
Query OK, 0 rows affected (0.03 sec)
The student here is the original name, the person is after the modified names
Rename with rename, or you can use rename to
Modify the data type of a field
Copy Code code as follows:
Mysql> ALTER TABLE person modify name varchar (20);
Query OK, 0 rows affected (0.18 sec)
records:0 duplicates:0 warnings:0
Here modify after name is the field name, we change the original varchar (25) to varchar (20)
Modify Field name
Copy Code code as follows:
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 formerly known, name is the new name
It should be noted that regardless of the data type change, the following data types are written
If you do not modify the data type just write the original data type
Tips: We can also use change to achieve the modify effect by simply writing the same field name later
add fields with no integrity constraints
Copy Code code as follows:
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 only by data types, without integrity constraints
add fields with integrity constraints
Copy Code code as follows:
Mysql> ALTER TABLE person add age int is not NULL;
Query OK, 0 rows affected (0.17 sec)
records:0 duplicates:0 warnings:0
is located with the addition of an age field followed by a NOT NULL integrity constraint
Add a field to a table header
Copy Code code as follows:
Mysql> ALTER TABLE person add num int primary key;
Query OK, 0 rows affected (0.20 sec)
records:0 duplicates:0 warnings:0
By default, adding fields is added to the end of the table, adding first to the table after adding the statement
Add a field at the specified location
Copy Code code as follows:
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 here, put it behind the name field
TPS: The sorting of fields in a table has no effect on the table, but a more reasonable sort makes it easier to understand the table
Delete a field
Copy Code code as follows:
mysql> ALTER TABLE person drop sex;
Query OK, 0 rows affected (0.18 sec)
records:0 duplicates:0 warnings:0
As with the previous delete table or database, you need to use drop
The difference is that the delete field also uses ALTER TABLE to follow the table name
Modify a field to the first position
Copy Code code as follows:
Mysql> ALTER TABLE person modify ID int A;
Query OK, 0 rows affected (0.20 sec)
records:0 duplicates:0 warnings:0
First, as I've said before, here's what you should be aware of after you write the data type
Modify a field to a specified location
Copy Code code as follows:
Mysql> ALTER TABLE person modify name varchar after ID;
Query OK, 0 rows affected (0.18 sec)
records:0 duplicates:0 warnings:0
We put the name field behind the ID, here's varchar (25) to write full, varchar not
It is recommended that you desc table before the above steps
Modifying the storage engine for a table
Copy Code code as follows:
mysql> ALTER TABLE user rename person;
Query OK, 0 rows affected (0.05 sec)
This is not specifically about the characteristics of each storage engine, more content
Do not forget to use the show CREATE TABLE statement after the modification, the third section has write usage
Tips: If there's a lot of data already in the table, don't easily modify the storage engine
to add a foreign key to a table
Copy Code code as follows:
Mysql> ALTER TABLE score add constraint FK foreign key (stu_id) references student (ID);
Query OK, rows affected (0.18 sec)
Records:10 duplicates:0 warnings:0
Here you can just use Add to add, and later syntax see the foreign key setting in section Fourth
Delete a foreign KEY constraint for a table
Copy Code code as follows:
mysql> ALTER TABLE STUDENT3 drop foreign key FK;
Query OK, 0 rows affected (0.18 sec)
records:0 duplicates:0 warnings:0
Because a basic table structure description cannot display foreign keys, it is a good idea to use show create table to view the table before doing this
The 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 you delete the foreign key, the original key becomes a normal
As for the operation of the delete table, there are written in the third section, set the foreign key in the fourth section also have write if you create a table without setting a foreign key, you can use the above method
