MySQL ALTER TABLE and DELETE Columns

The ALTER TABLE statement is used to modify the structure of an existing table, such as adding or dropping columns, changing column data types or constraints, renaming the table, or changing the table engine.  

What is in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

Our ALTER statement is adding a new column named "Manager" to the "Stores" table in the MySQL database, with a data type of VARCHAR (variable character) and a maximum length of 50 characters. 

mysql> ALTER TABLE Stores ADD COLUMN Manager VARCHAR (50);
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

The "ALTER TABLE" command allows you to modify the structure of an existing table in a database.

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+---------+
| s_id | city   | store_name | product         | available | price | Manager |
+------+--------+------------+-----------------+-----------+-------+---------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 | NULL    |
|    2 | London | London_2   | Gold PSU        |        75 |   100 | NULL    |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 | NULL    |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 | NULL    |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 | NULL    |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 | NULL    |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 | NULL    |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 | NULL    |
+------+--------+------------+-----------------+-----------+-------+---------+
8 rows in set (0.00 sec)

mysql>

Ok, now our MySQL query uses the ALTER TABLE statement to modify the Stores table by changing the data type of the Manager column to VARCHAR(100). 

mysql> ALTER TABLE Stores CHANGE Manager Manager VARCHAR (100);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

The new data type specification indicates that the Manager column can now store character strings up to 100 characters in length. 

The query reports that 0 rows were affected, which means that the table structure was updated successfully without modifying any existing data. 

Structure:

mysql> DESCRIBE Stores;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| s_id       | int          | NO   | PRI | NULL    | auto_increment |
| city       | varchar(30)  | YES  |     | NULL    |                |
| store_name | varchar(30)  | YES  |     | NULL    |                |
| product    | varchar(50)  | YES  |     | NULL    |                |
| available  | int          | YES  |     | NULL    |                |
| price      | int          | YES  |     | NULL    |                |
| Manager    | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (0.06 sec)

mysql>

We will use the INSERT INTO statement to add a new record to the Stores table. 

mysql> INSERT INTO Stores
    -> VALUES (NULL, 'XXX', 'XXX_1', 'Some', 4562, 500, "John Smith");
Query OK, 1 row affected (0.09 sec)

mysql>

The NULL value in the s_id column indicates that this value will be automatically generated by MySQL. 

The remaining values represent the data for the new record, including the city, store name, product, available quantity, price, and manager name. 

The query reports that 1 row was affected, indicating that the new record was successfully added to the table.

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+------------+
| s_id | city   | store_name | product         | available | price | Manager    |
+------+--------+------------+-----------------+-----------+-------+------------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 | NULL       |
|    2 | London | London_2   | Gold PSU        |        75 |   100 | NULL       |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 | NULL       |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 | NULL       |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 | NULL       |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 | NULL       |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 | NULL       |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 | NULL       |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 | John Smith |
+------+--------+------------+-----------------+-----------+-------+------------+
9 rows in set (0.00 sec)

mysql>

Drop column:

mysql> ALTER TABLE Stores DROP COLUMN Manager;
Query OK, 0 rows affected (2.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

This MySQL query uses the ALTER TABLE statement to remove the Manager column from the Stores table. 

Situation after drop column command:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
|    9 | XXX    | XXX_1      | Some            |      4562 |   500 |
+------+--------+------------+-----------------+-----------+-------+
9 rows in set (0.00 sec)

mysql>

Add column:

mysql> ALTER TABLE Stores ADD COLUMN BLABLA CHAR(100);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Drop column:

mysql> ALTER TABLE Stores DROP COLUMN BLABLA;
Query OK, 0 rows affected (1.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Table structure at the moment:

mysql> DESCRIBE Stores;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| s_id       | int         | NO   | PRI | NULL    | auto_increment |
| city       | varchar(30) | YES  |     | NULL    |                |
| store_name | varchar(30) | YES  |     | NULL    |                |
| product    | varchar(50) | YES  |     | NULL    |                |
| available  | int         | YES  |     | NULL    |                |
| price      | int         | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>