mysql alter table command to modify table structure example_MySQL

mysql example: use 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, etc. This article introduces mysql to you through two simple examples. How to use alter table

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
  -> );

Use 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 fields to the table, modify field types, and set primary keys. 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);

Using the desc command again to view the table structure, we found that the above modification command took 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 example, we delete the Quantity field and PRIMARY KEY in the myTable table

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

Thanks for reading this article, I hope it can help everyone, thank you for your support of this site!