We will inevitably be thoughtless in the process of creating a table, so we later modify the table
ALTER TABLE statements are required to modify tables
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
Another method is rename table Old_name to New_name
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
An age field is added here, followed by a NOT NULL integrity constraint
Add additional integrity constraints
Copy Code code as follows:
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 multiple-field settings
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
tips: The sorting of fields in a table has no effect on the table, but a more logical 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 primary key
Copy Code code as follows:
mysql> ALTER TABLE person DROP PRIMARY KEY;
Query OK, 0 rows affected (0.22 sec)
records:0 duplicates:0 warnings:0
Because the primary key does not have an alias, using drop deletes all primary keys
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 to delete the table, there are written in the third section, and the foreign key is also written in the fourth section.
If you do not set a foreign key when creating a table, you can use the above method
