DDL statement -- modify a table

DDL statement -- modify a table

Modifying a table is to modify the definition of an existing table in the database. It is simple to modify a table because it does not need to reload data or affect ongoing services.

MySQL uses the alter table statement to modify a TABLE. modifying a TABLE includes modifying the TABLE name, field data type, field name, adding a field, deleting a field, and modifying the field arrangement position, change the default storage engine and the foreign key constraint for deleting tables.

OK. This blog will detail several ways to modify tables. Now let's start.

1. Modify the table nameThe table name can be unique in a database to determine a table. The database system uses table names to differentiate different tables. Note that the tables in the database are unique and there cannot be two identical tables in the database.

In MySQL, the alter table statement is used to modify the TABLE name. The syntax format is as follows:

Alter table old TABLE name RENAME [TO] new TABLE name;

The 'old table name' parameter indicates the name of the table before modification, the 'new table name' parameter indicates the name of the new table after modification, and the TO parameter is an optional parameter, whether this attribute appears in the statement does not affect statement execution. Rename means rename, so this is easy to remember.

OK. Now let's operate the database. Create a table in the database. The table creation statement is as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we can modify the table name of the above user table:

ALTER TABLE linkinframe.user RENAME linkinframe.usr;

OK. The modification is successful. Check the new table. The table structure is consistent with the previous table structure, and the data in the table is consistent with the previous data.
2. Modify the field data type

The Data Types of fields include integer, floating-point, String, binary, date, and time. The data type determines the data storage format, constraints, and valid range. Each field in the table has a data type.

In MySQL, the alter table statement can also modify the Data Type of a field. The basic syntax is as follows:

Alter table name MODIFY attribute name data type;

The 'table name' parameter indicates the name of the table to be modified, and the 'attribute name' parameter indicates the name of the field to be modified, the new data type after the 'data type' parameter value is modified.

OK. Now let's take a look at MySQL. First, create a user table. The table creation statement is as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Modify the name field to varhcar (10) and run the following SQL statement:

ALTER TABLE linkinframe.`user` MODIFY name varchar(10) NOT NULL;

After the modification, we can check the current table structure of the database and successfully modify the length of the name attribute.


3. Modify the field name The field name is a unique field in a table. The database system uses field names to differentiate different fields in a table. In MySQL, the alter table statement can also modify the field name of a TABLE. The basic syntax is as follows:

Alter table name CHANGE old property name new data type;

The 'old property name' parameter indicates the field name before modification, the 'new property name' parameter indicates the modified field name, And the 'new data type' parameter indicates the modified data type, if no modification is required, set the new data type to the same as the original one.


There are two specific cases:
1), only modify the field name Use the alter table statement to directly modify the field name without changing the field data type. Table creation statement:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(10) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we can modify the name field to the username field and execute the following statement to modify it:

ALTER TABLE linkinframe.`user` CHANGE name username varchar(10) NOT NULL;

The modified table structure is as follows:

Of course, the data has not changed:

2) modify the field name and field data type Use the alter table statement to directly modify the field name and the Data Type of the field. Table creation statement:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we modify the name field to the username field, and then we change the original name length to 10, and execute the following statement to modify it:

ALTER TABLE linkinframe.`user` CHANGE name username varchar(10) NOT NULL;

The modified table structure is as follows:

3), Summary Both MODIFY and CHANGE can CHANGE the data type of the field. The difference is:

1. CHANGE can CHANGE the field name while changing the field data type. MODIFY can only be used to change the data type of a field, and cannot MODIFY the field name.

2. If the field name is not changed, CHANGE only modifies the field type. The CHAGE must be followed by two same field names.

3. develop a good habit. If it is to MODIFY the data structure of a field, use MODIFY. If you want to MODIFY the field name + data structure, use CHANGE.

4. When modifying a table's fields, if there are records in the table, be careful when modifying the data type. Because changing the data type may affect the data in the table.

5. Note that it is best not to change the character type field to the integer type or floating point type field.


4. Add Fields When creating a table, the fields in the table have been defined. To add a new field, you can use the alter table statement. In MySQL, the basic syntax for adding a field to the alter tbale statement is as follows:

Alter table name ADD attribute name 1 Data Type [integrity constraints] [FIRST | AFTER attribute name 2];

Here, the 'Property name 1' parameter refers to the name of the field to be added, the 'data type' parameter refers to the Data Type of the newly added field, and the 'Integrity constraint condition' is an optional parameter, this parameter is used to set the integrity constraints for new fields. The 'first' parameter is also an optional parameter. It sets the new field as the FIRST field of the table, and the 'after attribute name

The 2' parameter is also an optional parameter. It is used to add a new field to the field indicated by 'Property name2. If the execution of the SQL statement does not contain 'first', The 'After attribute name 2' parameter specifies the position of the new field. The new field is the last field in the table by default.

 

There are four situations:

1) add fields without integrity constraints A complete field includes the field name, data type, and integrity constraints. The added field generally includes the preceding content. Of course, according to the actual situation, some fields do not need integrity constraints.

Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we add a phone field to the user table:

ALTER TABLE linkinframe.`user` ADD phone varchar(11);

OK. The field is successfully added. Here, the 'frist 'and 'after attribute name 2' parameters are not set to specify the insert position. Therefore, the new field is the last field in the table by default.

2) add fields with integrity constraints When adding a field, you can set integrity constraints for the field, such as whether the field is empty, whether it is a primary key, whether it is a foreign key, the default value, and whether it is an auto-increment type.

Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we add a phone field to the user table:

ALTER TABLE linkinframe.`user` ADD phone varchar(11) NOT NULL;

OK. The field is successfully added. Here, the 'frist 'and 'after attribute name 2' parameters are not set to specify the insert position. Therefore, the new field is the last field in the table by default.
3) Add a field to the first position of the table. By default, the new field is the last field in the table. If the FIRST parameter is added, you can set the new field to the FIRST field of the table.
Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE linkinframe.`user` (    `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we add an id field to the user table, set the id to the primary key of the user table, and place it in the first position of the table.

ALTER TABLE linkinframe.`user` ADD `id` int(11) primary KEY AUTO_INCREMENT first;

OK. It is successfully added and successfully placed on the first field in the table.
4) Add a field after the specified position of the table When adding a field, you need to add a field to the specified position of the table for special reasons. If the 'after attribute name 2' parameter is added, the new field is inserted AFTER 'attribute name 2.

Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we add a phone field after the name field in the user table:

ALTER TABLE linkinframe.`user` ADD phone varchar(11) after name;

OK. The field is successfully added. After the phone field is successfully added to the name field.

Summary: 1. When adding a field, if you can add integrity constraints, you must add them. This ensures the security of this field and even improves the security of the entire table.

2. For a database, the field sorting order does not affect the table. However, if the table creator puts directly or indirectly related fields together, the table structure can be better understood, we will try our best to put relevant fields together for our convenience.

5. Delete Fields Deleting a field is to delete a field in a defined table. After the table is created, if you find that a field needs to be deleted, you can delete the entire table, create a new table. This can achieve the goal, but it will inevitably affect the data in the table, and the operation is troublesome.

In MySQL, the alter table statement can also delete fields in the TABLE. The syntax is as follows:

Alter table Name DROP attribute name;

The 'Property name' parameter indicates the name of the field to be deleted from the table.

Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now, the name field is deleted from the user table:

ALTER TABLE linkinframe.`user` DROP name;

OK. Deleted successfully.


6. Modify the field arrangement positionWhen creating a TABLE, all the fields in the TABLE are sorted. To change the field's position in the TABLE, you can also use alter table. In MySQL, the basic syntax of the alter table statement for modifying the field arrangement position is as follows:

Alter table name MODIFY attribute name 1 Data Type FIRST | AFTER attribute name 2;

Here, the 'Property name 1' parameter refers to the name of the field whose location needs to be modified, and the 'data type' parameter refers to the Data Type of 'Property name 1, the 'first' parameter specifies the position as the FIRST position of the table. The 'After attribute name2' parameter specifies that 'attribute name1' is inserted AFTER 'attribute name2.

There are two cases:
1) modify the field to the first position. The FIRST parameter specifies the field as the FIRST field of the table.

Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now, the name field in the user table is modified to the first location in the database:

ALTER TABLE linkinframe.`user` modify name varchar(5) first;

OK. The name field is in the first position of the user table.

2) modify the field to the specified position The 'after' parameter can be used to rank fields AFTER the specified fields in the table.

Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now the name field in the user table is changed to age:

ALTER TABLE linkinframe.`user` modify name varchar(5) after age;

OK. The name field is displayed after the age in the user table.


Summary:

Modifying the field location is actually modifying the table field. Because the field name is not modified, we can use MODIFY to operate the field location, of course, we can also use CHANGE to operate the field location, but the attribute name must be written twice at this time.

For example, if I do not write the SQL statement myself now, and then use the MySQL client to adjust the position of Field 2 in the following table, CHANGE is used.

ALTER TABLE `linkinframe`.`user` CHANGE COLUMN `name` `name` VARCHAR(5) CHARACTER SET 'utf8' NULL DEFAULT NULL AFTER `id`;

7. Change the storage engine of the table

The MySQL storage engine refers to the storage type of tables in the MySQL database. The MySQL storage engine includes InnoDB, MyISAM, and Memory. Different table types have different advantages and disadvantages. I have already sorted them out in my previous blog.

When creating a table, the storage engine has been set. If you want to change it, you can create a new table. This can achieve the goal, but it will inevitably affect the data in the table, and the operation is troublesome.

In MySQL, the alter table statement can also change the type of the TABLE storage engine. The basic syntax is as follows:

Alter table name ENGINE = storage ENGINE name

The 'Storage engine name' parameter indicates the name of the new storage engine.

Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now the storage engine in the user table is set to MyISAM:

ALTER TABLE linkinframe.`user` ENGINE = MyISAM;

OK. Now let's SHOW CREATE TABLE to view the TABLE structure of the user. Now, the storage engine of the user TABLE has successfully changed to MyISAM.
Note: The Alter statement can be used to change the storage engine of a table, which avoids the re-creation of the table. However, if the table already contains a large amount of data, changing the storage engine may have unexpected effects. If a table already has a lot of data, it is best not to change its storage engine easily.


8. Delete the foreign key constraint of the table.

A foreign key is a special field that associates a table with its parent table. When creating a table, the foreign key constraint has been set. Due to special requirements, the association with the parent table needs to be removed, and the foreign key constraint must be deleted.

In MySQL, the alter table statement can also delete the TABLE's foreign key constraints. The basic syntax is as follows:

 

Alter table Name drop foreign key alias:

 

The 'foreign key alias 'parameter indicates the foreign key code set during table creation.

Now, in practice, the statements for creating a database table are as follows:

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(5) COLLATE utf8_bin NOT NULL,  `card_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,  `age` int(11) DEFAULT '25',  PRIMARY KEY (`id`),  UNIQUE KEY `age_UNIQUE` (`card_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;CREATE TABLE `address` (  `id` int(11) NOT NULL,  `user_id` int(11) DEFAULT NULL,  `name` varchar(45) COLLATE utf8_bin DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `user_id_idx` (`user_id`),  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we delete the foreign key association from the table address and execute the following SQL:

ALTER TABLE linkinframe.address DROP foreign key user_id;

OK. The query result shows that the foreign key from the table address does not exist.