MySQL ALTER TABLE command modifies table structure instance _mysql

MySQL instance using ALTER TABLE command to modify the table structure

The MySQL ALTER TABLE statement can modify the basic structure of the table, such as adding fields, deleting fields, adding primary keys, adding indexes, modifying field data types, renaming tables, and so on, and this article introduces you to the use of MySQL ALTER TABLE with two simple examples

Example: Use the ALTER TABLE command to add a field to a table, modify a field type, and set a primary key.

First, create a table with the following SQL statement:

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

To view the table structure using the DESC command:

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

Use the ALTER TABLE command to add a field to a table, modify a field type, and set a primary key, as described in the following SQL statement:

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

Again using the DESC command to view the table structure, we found that the above modification command is in effect:

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

Example two: On the basis of the above example, we delete the Quantity field in the MyTable table and the primary key primary key

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

View Table structure:

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

Thank you for reading this article, I hope to help you, thank you for your support for this site!