How to add new fields to a table using mysql SQL

To add columns to a table, use the following syntax:

Alter table table_name
ADD column_name datatype
To delete columns in a table, use the following syntax:

Alter table table_name
Drop column column_name
Note: Some database tutorial systems do not allow this method of deleting columns in database tables (drop column column_name)

.

Instance
Mysql tutorial> create table Employee (
-> Id int,
-> First_name VARCHAR (15 ),
-> Last_name VARCHAR (15 ),
-> Start_date DATE,
-> End_date DATE,
-> Salary FLOAT (8, 2 ),
-> City VARCHAR (10 ),
-> Description VARCHAR (15)
-> );
Query OK, 0 rows affected (0.02 sec)

Mysql>
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date,

Salary, City, Description)
-> Values (1, 'jason ', 'martin', '123', '123 ',

1234.56, 'toronto ', 'programmer ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date,

Salary, City, Description)
-> Values (2, 'alison ', 'mathews', '123', '123 ',

6661.78, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date,

Salary, City, Description)
-> Values (3, 'James ', 'Smith', '123', '123 ',

6544.78, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date,

Salary, City, Description)
-> Values (4, 'cela', 'Rice ', '123', '123 ',

2344.78, 'vancouver ', 'manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date,

Salary, City, Description)
-> Values (5, 'Robert ', 'black', '123', '123 ',

2334.78, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date,

Salary, City, Description)
-> Values (6, 'linda ', 'green', '123', '123 ',

4322.78, 'New York ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date,

Salary, City, Description)
-> Values (7, 'David ', 'Larry', '123', '123 ',

7897.78, 'New York ', 'manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date,

Salary, City, Description)
-> Values (8, 'James ', 'cat', '123', '123 ',

1232.78, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> select * from Employee;
+ ------ + ------------ + ----------- + ------------ + --------- + -----------

+ ------------- +
| Id | first_name | last_name | start_date | end_date | salary | city |

Description |
+ ------ + ------------ + ----------- + ------------ + --------- + -----------

+ ------------- +
| 1 | Jason | Martin | 1234.56 | Toronto |

Programmer |
| 2 | Alison | Mathews | 6661.78 | Vancouver |

Tester |
| 3 | James | Smith | 6544.78 | Vancouver |

Tester |
| 4 | Celia | Rice | 1982-10-24 | 2344.78 | Vancouver |

Manager |
| 5 | Robert | Black | 2334.78 | Vancouver |

Tester |
| 6 | Linda | Green | 4322.78 | New York |

Tester |
| 7 | David | Larry | 7897.78 | New York |

Manager |
| 8 | James | Cat | 1232.78 | Vancouver |

Tester |
+ ------ + ------------ + ----------- + ------------ + --------- + -----------

+ ------------- +
8 rows in set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql> desc employee;
+ ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------- + ------ + ----- + --------- + ------- +
| Id | int (11) | YES | NULL |
| First_name | varchar (15) | YES | NULL |
| Last_name | varchar (15) | YES | NULL |
| Start_date | date | YES | NULL |
| End_date | date | YES | NULL |
| Salary | float (8, 2) | YES | NULL |
| City | varchar (10) | YES | NULL |
| Description | varchar (15) | YES | NULL |
+ ------------- + ------ + ----- + --------- + ------- +
8 rows in set (0.03 sec)

Mysql>
Mysql> alter table employee
-> ADD new_date date;
Query OK, 8 rows affected (0.16 sec)
Records: 8 Duplicates: 0 Warnings: 0

Mysql>
Mysql> Describe employee;
+ ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------- + ------ + ----- + --------- + ------- +
| Id | int (11) | YES | NULL |
| First_name | varchar (15) | YES | NULL |
| Last_name | varchar (15) | YES | NULL |
| Start_date | date | YES | NULL |
| End_date | date | YES | NULL |
| Salary | float (8, 2) | YES | NULL |
| City | varchar (10) | YES | NULL |
| Description | varchar (15) | YES | NULL |
| New_date | date | YES | NULL |
+ ------------- + ------ + ----- + --------- + ------- +
9 rows in set (0.06 sec)

Mysql>
Mysql> select id, new_date FROM employee;
+ ------ + ---------- +
| ID | new_date |
+ ------ + ---------- +
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
| 7 | NULL |
| 8 | NULL |
+ ------ + ---------- +
8 rows in set (0.00 sec)

Example 2: Use the table above

Mysql> alter table employee ADD Middle_Name VARCHAR (30 );
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0

Mysql>
Mysql> select * from Employee;
+ ------ + ------------ + ----------- + ------------ + --------- + -----------

+ ------------- +
| Id | first_name | last_name | start_date | end_date | salary | city |

Description | Middle_Name |
+ ------ + ------------ + ----------- + ------------ + --------- + -----------

+ ------------- +
| 1 | Jason | Martin | 1234.56 | Toronto |

Programmer | NULL |
| 2 | Alison | Mathews | 6661.78 | Vancouver |

Tester | NULL |
| 3 | James | Smith | 6544.78 | Vancouver |

Tester | NULL |
| 4 | Celia | Rice | 1982-10-24 | 2344.78 | Vancouver |

Manager | NULL |
| 5 | Robert | Black | 2334.78 | Vancouver |

Tester | NULL |
| 6 | Linda | Green | 4322.78 | New York |

Tester | NULL |
| 7 | David | Larry | 7897.78 | New York |

Manager | NULL |
| 8 | James | Cat | 1232.78 | Vancouver |

Tester | NULL |
+ ------ + ------------ + ----------- + ------------ + --------- + -----------

+ ------------- +
8 rows in set (0.00 sec)

Mysql>
Mysql> desc employee;
+ ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------- + ------ + ----- + --------- + ------- +
| Id | int (11) | YES | NULL |
| First_name | varchar (15) | YES | NULL |
| Last_name | varchar (15) | YES | NULL |
| Start_date | date | YES | NULL |
| End_date | date | YES | NULL |
| Salary | float (8, 2) | YES | NULL |
| City | varchar (10) | YES | NULL |
| Description | varchar (15) | YES | NULL |
| Middle_Name | varchar (30) | YES | NULL |
+ ------------- + ------ + ----- + --------- + ------- +
9 rows in set (0.02 sec)