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

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

Use the alter table command to modify the table structure of a mysql instance

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  |   |     |    |+-------+----------------------+------+-----+---------+-------+

Thank you for reading this article and hope to help you. Thank you for your support for this site!