Mysql alter table command to modify the table structure instance details, altertable

Mysql alter table command to modify the table structure instance details, altertable

The mysql alter table statement can modify the basic structure of a table, such as adding a field, deleting a field, adding a primary key, adding an index, modifying the field data type, and renaming a table, this article introduces how to use mysql alter table through two simple examples.

Example 1: Use the alter table command to add fields to the TABLE, modify field types, and set primary keys.

First, create a table. The SQL statement is as follows:

mysql> CREATE TABLE myTable(  ->  ID SMALLINT  -> );

Run the desc command to view the table structure:

Mysql> desc myTable;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| ID | smallint (6) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +

Use the alter table command to add a field to the TABLE, modify the field type, and set the primary key. The SQL statement is as follows:

mysql> ALTER TABLE myTable  ->  ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,  ->  MODIFY ID SMALLINT UNSIGNED NOT NULL,  ->  ADD PRIMARY KEY (ID);

Run the desc command again to view the table structure. The preceding modification command takes effect:

Desc myTable;
+ ---------- + ---------------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ---------------------- + ------ + ----- + --------- + ------- +
| ID | smallint (5) unsigned | NO | PRI |
| Quantity | smallint (5) unsigned | NO |
+ ---------- + ---------------------- + ------ + ----- + --------- + ------- +
 

Example 2: Based on the above instance, we delete the Quantity field and primary key in the myTable table.

mysql> ALTER TABLE myTable  ->  DROP COLUMN Quantity,  ->  DROP PRIMARY KEY;

View the table structure:

Mysql> desc myTable;
+ ------- + ---------------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ---------------------- + ------ + ----- + --------- + ------- +
| ID | smallint (5) unsigned | NO |
+ ------- + ---------------------- + ------ + ----- + --------- + ------- +

The above is how the mysql alter table command modifies the table structure. We will continue to add relevant information later. Thank you for your support for this site!