preface

In the previous chapter, I learned how to modify the data in the table, and I left the problem of how to modify the table in the previous chapter.
The field in the data table refers to a column of the table. Each row in the data table is a record. Each record contains all the information in this row, and the field is a unit smaller than the record. The field set forms a record. Each field describes a certain feature of the document, that is, a data item, and is identified by a field identifier for computer recognition.

1: View table structure and modify table name

View the basic structure of the data table:
1)describe table name (view the basic structure of the data table);
2) Use show create table instead; Statement, you can not only return the detailed statements we wrote when creating the table, but also view the storage engine and character coding.
Modify table name:
alter table old table name rename new table name;

Programming requirements
According to the prompt, supplement the code in the editor on the right:
Put the data sheet tb_emp renamed jd_emp；
View the list of data tables under the database;
View data table JD_ The basic structure of EMP.

USE Company;

#Please add the implementation code here
########## Begin ##########

########## modify the table name ##########

alter table tb_emp rename jd_emp;#The database remembers a semicolon, d and b distinguish#

########## show tables in this database ##########

show tables;

########## describe the table ##########

describe jd_emp;

########## End ##########

2: Modify field name and field data type

Modify field name:
alter table name change old field new field new field data type;
attention:
If you do not need to modify the data type of the field, you can set the data type of the new field to be the same as the original, but! Never leave it empty!
Modify field data type:
alter table name modify field name new field data type;

Programming requirements
According to the prompt, supplement the code in the editor on the right:
Put the data sheet TB_ The field Id of EMP is renamed prod_id, data type unchanged;
Put the data sheet TB_ The data type of EMP field Name is changed to varchar(30).
The structure of the data sheet is as follows:

USE Company;

#Please add the implementation code here
########## Begin ##########

########## change the column name ##########

alter table tb_emp change Id prod_id int(11); #Even if the data type remains unchanged, it should be written down. Note that the database command has a semicolon, Linux No#

########## change the data type of column ##########
alter table tb_emp modify Name varchar(30);


########## End ##########

DESCRIBE tb_emp;

3: Add and delete fields

Add field:
If the location is not specified, the last column is added by default
alter table name add new field name data type [constraint];
Add to first column:
alter table name add new field name data type [constraint] first;
After a field:
alter table name add new field name data type [constraint] after existing field name;
Delete field:
alter table name drop field name;
Programming requirements
According to the prompt, supplement the code in the editor on the right:
In data sheet TB_ Add the field Country after the Name field of EMP, and the data format is varchar(20);
Delete data table tb_ The field Salary in EMP.
The structure of the data sheet is as follows:

USE Company;

#Please add the implementation code here
########## Begin ##########

########## add the column ##########

alter table tb_emp add Country varchar(20) after Name;
 
########## delete the column ##########

alter table tb_emp drop Salary;

########## End ##########

DESCRIBE tb_emp;

4: Modify the arrangement position of fields

Modify field location:
Modify to first column
alter table name modify the field name field type to be modified first;
After modifying to a field
alter table name modify the field name field type to be modified after field 2;
Programming requirements
According to the prompt, supplement the code in the editor on the right:
Add data sheet TB_ The Name field of EMP is moved to the first column, and the data format remains unchanged;
Move the DeptId field to the back of the Salary field, and the data format remains unchanged.
The structure of the data sheet is as follows:

USE Company;

#Please add the implementation code here
########## Begin ##########

########## modify the column to top ##########

alter table tb_emp modify Name varchar(25) first;

########## modify the column to the rear of another column ##########

alter table tb_emp modify DeptId int(11) after Salary;

########## End ##########

DESCRIBE tb_emp;

5: Delete foreign key constraint for table

To delete a foreign key constraint:
alter table name drop foreign key constraint name:
Programming requirements
We have created a master table for you tb_dept and sub table tb_emp, in table tb_emp is added with the name EMP_ The foreign key constraint of dept, whose name is DeptId, depends on table tb_ The primary key Id of Dept. the following is the structure of the two tables:


Please supplement the code in the editor begin end on the right according to the prompt:
Delete data table tb_ Foreign key constraint of emp_dept.

USE Company;

#Please add the implementation code here
########## Begin ##########

########## delete the foreign key ##########

alter table tb_emp drop foreign key emp_dept;

########## End ##########
SHOW CREATE TABLE tb_emp \G;

The layout of the returned results is a little messy. When we add \ G, the effect will be improved

Summary (memorization formula)

After learning this chapter, there is no need to reset the environment and rebuild the table after modifying the table. For example, when we write the table name wrong, we only use alter table old table name rename new table name; When our field type is wrong, we can change the table name and modify the field name to create a new field type; When we want to modify the field name, we can alter table field name change old field name new field name new field type; There are many more. Let's see a brief summary of a convenient memory formula:

To view, use describe or show create,
Modify the table, use alter,
Modify the table name with rename;
When modifying the field name and using change, remember to have a new field type
Modify the field type with modify
Add add delete drop
Modify the location with modify and first after
The first column is first, followed by after
drop foreign key for deleting foreign key constraints