Sql--alter

ALTER TABLE Statement

The ALTER table statement is used to add, modify, or delete columns in an existing table.

SQL ALTER TABLE Syntax

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

ALTER TABLE Table_nameadd column_name datatype

To delete a column from a table, use the following syntax:

ALTER TABLE table_name DROP COLUMN column_name

Note: Some database systems do not allow this method of deleting columns in a database table (DROP column column_name).

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_namealter COLUMN column_name datatype

The original table (used in the example):

Persons table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

SQL ALTER TABLE Instance

Now, we want to add a new column named "Birthday" in the table "Persons".

We use the following SQL statements:

ALTER TABLE personsadd Birthday Date

Note that the type of the new column "Birthday" is date and can hold the date. The data type specifies the type of data that can be stored in the column.

The new "Persons" table looks like this:

City

Id	LastName	FirstName	Address		Birthday
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Changing data type instances

Now we want to change the data type of the "Birthday" column in the "Persons" table.

We use the following SQL statements:

ALTER TABLE Personsalter COLUMN Birthday Year

Note that the data type of the "Birthday" column is year, which can hold 2-bit or 4-bit formats.

DROP COLUMN Instance

Next, we delete the "Birthday" column in the "Person" table:

ALTER TABLE Persondrop COLUMN Birthday

The Persons table will become like this:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Sql--alter