MySQL ALTER TABLE command modify table structure instance detailed _php instance

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

The above is the MySQL ALTER TABLE command to modify the table structure of the data collation, follow-up to continue to supplement the relevant information, thank you for your support for this site!