Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to view table structure, modify and delete data tables by Mysql". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

View table structure

To view the table structure, you can use DESCRIBE or SHOW CREATE TABLE statements.

1. View the basic structure statement of the table:

DESCRIBE table name

2. View the table detailed structure statement:

SHOW CREATE TABLE table name

Example: www.2cto.com

SHOW CREATE TABLE example\ G

Modify the table

1. Modify the table name

In Mysql, changing the table name is done through ALTER TABLE. The specific syntax is:

ALTER TABLE old table name RENAME [TO] new table name

The TO parameter is optional, and the presence or absence of the parameter does not affect the execution of the statement.

2. Modify the data type of the field

In Mysql, ALTER TABLE statements can also be used to modify the data type of a field. Its syntax is:

ALTER TABLE table name MODIFY attribute name data type

3. Modify the field name

In Mysql, ALTER TABLE statements can also be used to modify field names. Its syntax is:

ALTER TABLE table name CHANGE old attribute name new attribute name new data type

4. Add a field

In Mysql, the ALTER TABLE statement can also be used to add fields, and its syntax is:

ALTER TABLE table name attribute name 1 data type [integrity constraints] [FIRST | AFTER attribute name 2]

The "property name 1" parameter refers to the field name that needs to be added, the "data type" parameter refers to the data type of the new field, and the "integrity constraint" is an optional parameter, which is used to set the integrity constraint of the new field.

Example:

1. Add fields without complete constraints (add to the end by default)

ALTER TABLE user ADD phone VARCHAR (20)

2. Add the field www.2cto.com with integrity constraints

ALTER TABLE user ADD age INT (4) NOT NULL

3. Add a field to the first position of the table

ALTER TABLE user ADD num INT (8) PRIMARY KEY FIRST

Add the num field to the beginning of the table and set the num field as the primary key.

4. Add a field after the specified location of the table

ALTER TABLE user ADD address VARCHAR (30) NOT NULL AFTER phone

5. Delete a field

In Mysql, the ALTER TABLE statement can also be used to delete fields from a table. Its syntax is:

ALTER TABLE table name DROP property name.

6. Modify the arrangement position of the fields

In Mysql, the ALTER TABLE statement can also be used to modify the arrangement of fields. Its syntax is:

ALTER TABLE table name MODIFY attribute name 1 data type FIRST | AFTER attribute name 2

Where the "attribute name 1" parameter refers to the name of the field that needs to be modified, and "data Type" refers to the data type of "property name 1".

Example:

1. Modify the field to the first location.

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 storage engine of the table

The Mysql storage engine refers to the storage type of tables in Mysql. Mysql storage types include InnoDB, MyISAM, MEMORY, and so on. In Mysql, ALTER TABLE statements can also be used to modify the storage engine type of a table. Its syntax is:

ALTER TABLE table name ENGINE= storage engine name

View the data types supported by My through SHOW ENGINES\ G.

Example:

ALTER TABLE user ENGINE=InnoDB

8. Delete the foreign key constraint www.2cto.com of the table

In Mysql, ALTER TABLE statements can also be used to delete foreign key constraints on a table. Its syntax is:

ALTER TABLE table name DROP FOREIGN KEY foreign key alias

Example:

ALTER TABLE example3 DROP FOREIGN KEY c_fk

Delete tabl

In Mysql, delete the table through the DROP TABLE statement.

1. Delete ordinary tables that are not associated

In Mysql, drop the table directly through the DROP TABLE statement, and the remaining syntax is:

DROP TABLE table name

2. Delete the parent table associated with other tables

Delete the foreign key constraint before deleting the parent table.

This is the end of the content of "how to view table structure, modify and delete data tables by Mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.