mysql alter table command to modify table structure example

mysql alter table command to modify table structure example_MySQL

WBOY

Release： 2016-11-30 23:59:37

Original

1048 people have browsed it

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

Copy after login

Use the desc command to view the table structure:

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

Copy after login

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

Copy after login

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

Copy after login

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;

Copy after login

View table structure:

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

Copy after login

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