Mysql learning 3 -- view the table structure, modify and delete data tables

Mysql learning 3-view the table structure, modify and delete data tables view the table structure
You can use DESCRIBE or show create table statements to view the TABLE structure. 1. view the basic structure statement of the TABLE: describe table name; 2. view the detailed structure statement of the TABLE: show create table name; instance: www.2cto.com show create table example \ G modify the TABLE
1. Modify the TABLE name in Mysql by using alter table. The specific syntax is: alter table old TABLE name RENAME [TO] new TABLE name; TO parameter is an optional parameter, whether the statement execution will not be affected. 2. Modify the Data Type of a field in Mysql. The alter table statement can also be used to modify the Data Type of a field. Syntax: alter table name MODIFY attribute name data type; 3. MODIFY the field name in Mysql. The alter table statement can also be used to MODIFY the field name. Syntax: alter table name CHANGE old property name new attribute name new data type; 4. Add a field in Mysql, the alter table statement can also be used to add a field. Syntax: alter table name attribute name 1 Data Type [integrity constraints] [FIRST | AFTER attribute name 2];
The "attribute 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, used to set integrity constraints for new fields. Example: 1. ADD fields without complete constraints (Added to the end by default) alter table user ADD phone VARCHAR (20 ); 2. ADD a field www.2cto.com alter table user ADD age INT (4) not null. 3. ADD the field alter table user ADD num INT (8) at the first position of the TABLE) primary key first; Add the num field to the beginning of the table and set the num field as the primary key. 4. ADD the field alter table user ADD address VARCHAR (30) not null after phone AFTER the specified position of the TABLE; 5. Delete the field in Mysql, the alter table statement can also be used to delete fields in a TABLE. Syntax: alter table Name DROP attribute name. 6. Modify the field arrangement position in Mysql. The alter table statement can also be used to modify the field arrangement position. Syntax: alter table name MODIFY attribute name 1 Data Type FIRST | AFTER attribute name 2;
The "attribute name 1" parameter refers to the name of the field to be modified, and the "Data Type" refers to the data type of "attribute name 1. Example: 1. Modify the field to the first position. Alter table user MODIFY stu_name VARCHAR (20) FIRST; 2. MODIFY the field to the specified location alter table user MODIFY sex TINYINT (1) AFTER id; 7. Change the table storage engine Mysql storage engine refers to the storage type of tables in the Mysql database. Mysql Storage types include InnoDB, MyISAM, and MEMORY. In Mysql, the alter table statement can also be used to modify the storage engine type of a TABLE. The syntax is: alter table name ENGINE = storage ENGINE name; Use show engines \ G to view the data types supported by the Mysql database. Instance: alter table user ENGINE = InnoDB; 8. Delete the TABLE's foreign key constraint www.2cto.com. in Mysql, the alter table statement can also be used to delete the foreign key constraint of the TABLE. Syntax: alter table Name drop foreign key alias; instance: alter table example3 drop foreign key c_fk; Delete TABLE
In Mysql, the drop table statement is used to delete a TABLE. 1. to delete a non-associated Mysql TABLE, you can directly use the drop table statement to delete the TABLE. The other syntax is: drop table name; 2. to delete a parent table associated with another table, delete the foreign key constraint first, and then delete the parent table. Author junjieguo