Previously, we covered creating tables in SQL. To create a column in a table, use the ALTER TABLE statement, along with the ADD and then define a column name followed by the data type:
ALTER TABLE nameoftable
ADD nameofcolumn datatype
To delete a column in a table::
ALTER TABLE nameoftable
DROP COLUMN nameofcolumn
To change the type of data stored in a column, use MODIFY:
ALTER TABLE nameoftable
MODIFY COLUMN nameofcolumn datatype
In this article, we’ll use the same “Customers” table from our first articles to add and edit columns:
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
Let’s add a column called “ExpDate” in the “Customers” table with a date type, which will oddly hold a date. To do so, use the following SQL statement:
ALTER TABLE Customers
ADD ExpDate date
The new columns are empty. Once you’ve created a column, you can then change the data type of that column.Let’s just change this to year, assuming all the ExpDate fields have the same actual day for their expiration:
ALTER TABLE Customers
ALTER COLUMN ExpDate year
Crap, I promised love. How about this, give all the things away for free. Now let’s delete the column:
ALTER TABLE Customers
DROP COLUMN ExpDate
Obviously, this would delete all of the data in the column as well, so be careful!