Mysql database ALTER basic operations

Background:

As one of the DDL languages, ALTER is often encountered in work. Here we briefly introduce several common usage scenarios

Create two new test tables offices and employees

CREATE TABLE offices (officeCode INT(10) NOT NULL UNIQUE ,city ​​VARCHAR(50) NOT NULL,address VARCHAR(50) NOT NULL,country VARCHAR(50) NOT NULL,PRIMARY KEY(officeCode))CREATE TABLE employees (employeeNumber INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,lastName VARCHAR(50) NOT NULL,firtName VARCHAR(50) NOT NULL,officeCode INT(10) NOT NULL,sex VARCHAR(5),CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices (officeCode))

Case 1: Modify the lastName field of the table employees to the back of the firstName field, and reduce the character length.

To modify the position, you need to use the ALTER TABLE MODIFY statement. The input statement is as follows

ALTER TABLE employees MODIFY lastName VARCHAR(25) AFTER firtName; 

You can see that the lastName field has been modified to the back of the firstName field.

.

Case 2: Rename the sex field of the table employees to employee_sex.

To modify the field name, you need to use the ALTER TABLE CHANGE statement. The input statement is as follows

ALTER TABLE employees CHANGE sex employee_sex VARCHAR(5); 

As you can see, there is only employee_sex field in the table, there is no field named sex, the name is modified successfully

Case 3: Modify the employee_sex field, the data type is CHAR(1), and the non-null constraint

To modify the field data type, you need to use the ALTER TABLE MODIFY statement. The input statement is as follows:

ALTER TABLE employees MODIFY employee_sex CHAR(1) NOT NULL; 

It can be seen from the execution results that the data type of the employee_sex field is changed from VARCHAR(5) to CHAR(1), and the Null column is displayed as NO, indicating that the column does not allow null values, and the modification is successful

Case 4: Delete the field employee_sex

To delete a field, you need to use the ALTER TABLE DROP statement. The input statement is as follows:

ALTER TABLE employees DROP employee_sex; 

You can see that the column field returned by the table, the employee_sex field is no longer in the table structure, and the deletion of the field is successful

Case 5: Add the field name city, and the data type is VARCHAR(10).

To modify the field data type, you need to use the ALTER TABLE ADD statement. The input statement is as follows:

ALTER TABLE employees ADD city VARCHAR(10);

You can see that a new column city has been added to the data table employees, the data type is VARCHAR(10), null values ​​are allowed, and the new column is successfully added

Case 6: Change the name of the table employees to employees_info

To modify the data table name, you need to use the ALTER TABLE RENAME statement. The input statement is as follows:

ALTER TABLE employees RENAME employees_info; 

You can see that there is no data table named employees in the database.

Case 7, delete foreign key

See https://www.cnblogs.com/xiong97/p/16564428.html for details, and I will not repeat them here

The above seven cases are often encountered in work.