MySQL Database operations 3-data Table Operations 3-Modifying data tables

Modify Table Name:

Code:

Alter  table Old table name rename new table name;

Example:

Alter table peo rename people;

Note: After completion can be used to show tables check table name is modified;

To modify the data type of a table field:

Code:

Alter  table name modify field name new data type;

Example:

Alter Table varchar (4);

Note:

1. After modification, you can use the desc table name to see if the data type in the table has been modified.

2. Note that data type compatibility can be converted to each other, such as the ID of the int can be more char, but the Auto_increment property will be invalidated. The original ID value is converted to char type.

Modify Field Name:

Code:

Alter  table name change old field new field new data type;

Example:

Alter Table Char (a);

To add a field:

Code:

Alter Table Add new field name data type;

Example:

Alter Table Add Char (a);

1.

The above fields are added by default in the final, and do not contain constraint items, such as the need to constrain the order of the item after the data type can be added.

For example:

Alter Table Add Char (notnull;

2.

You need to use the first option to add a field before the column one.

For example:

Alter Table Add Char (notnull first;

3.

Adding a field after a column is made requires the after option.

For example:

Alter Table Add Char (notnull after sex;

To delete a field:

Code:

Alter Table Drop The fields that need to be deleted;

Example:

Alter Table drop xingming;

Modify Field Location:

1.

Move the field to the first bit, using the primary option,

Code:

Alter  table name modify field name data type first;

Note: The data type can be different from the original field type, so this statement can change the data type at the same time.

2.

After you move the field to the specified column position, use the after option,

Code:

Alter  Table name modify field data type after field;

Example:

Alter Table Char (2) first; Alter Table Char (2) after email;

To change the storage engine for a table:

Code:

Alter  table name engine= new engine name

It is best to check the supported engines again before: using show engines;

You can also view the default engine, using show variables like ' storage_engine ';

To delete a foreign KEY constraint:

Code:

Alter Table Drop Foreign key foreign KEY constraint name;

Example:

Alter Table Drop Foreign key S;

To delete a data table:

Code:

Drop Table [if exists] table name 1, ...;

[] is an optional parameter and is recommended if the table name does not exist and will be warned instead of stopping execution.

This method is used to delete tables that do not contain associations.

Attention:

1. If a table is associated with a foreign key (the parent table), then he cannot be deleted directly, you need to first delete the foreign Key association in the Word table, or delete the child table first.

MySQL Database operations 3-data Table Operations 3-Modifying data tables