For this little lesson, we will create a database, a couple of tables, add some data to those tables, alter them, query them, and then drop them.
I’ll get to the syntax of each of those as we go here, but for the CREATE and ALTER portions, you already know from previous lessons – so here we go.
CREATE Database Linux GO USE Linux GO CREATE TABLE Distributions ( ID int PRIMARY KEY IDENTITY NOT NULL, Name varchar (255), ReleaseDate date, VersionNumber varchar (16), CodeName varchar (25) ) GO SET NOCOUNT ON INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName) VALUES ('Ubuntu','12/10/2012', '12.10', 'Quantal') INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName) VALUES ('Ubuntu','12/04/2012', '12.04', 'Precise') INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName) VALUES ('Ubuntu','10/01/2011', '11.10', 'Oneiric') INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName) VALUES ('Mint','12/10/2012', '14', 'Nadya') INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName) VALUES ('Mint','11/04/2012', '13', 'Maya') GO ALTER TABLE Distributions DROP COLUMN ReleaseDate GO DROP TABLE Distributions GO USE msdb GO DROP DATABASE Linux GO
Alright – so with that, we created a table and then added some random stuff to that table. After that we altered the table and dropped the column called “Release Date”. Note that dropping a column removes the entire column and all data in it. This is different than a delete statement. A delete statement to remove the values from one row or to make every single row NULL for a particular column is a totally different query. This removes the entire column and all data in it, so that our table no longer has that column. If you wanted to delete a single column from a row, you would do this:
DELETE ReleaseDate FROM Distributions WHERE ID = 3
If you wanted to set all columns to NULL, you could do this:
DELETE ReleaseDate FROM Distributions
Neither of these delete the column however, it just sets all the data in them to NULL – if you want to remove an entire column, you need to DROP it using ALTER TABLE.
The next one you see is that we dropped the table. This means that the entire table gets removed from the database. Once again, it’s not DELETE. It’s DROP TABLE.
The last drop you see is to DROP DATABASE. This one in particular you have to be careful with (for obvious reasons). Firstly, no one can be using it. If they are using it, you’ll get a warning. Since I assumed that you created this database and you are the only one using it, I just decided to USE msdb, and then drop it. If there are other open connections on that database, you need to set it offline to drop it.
ALTER DATABASE Linux SET OFFLINE WITH ROLLBACK IMMEDIATE GO DROP DATABASE Linux GO
Just be warned – this will force disconnect anyone who is connected and then drop the database. This means deleting all tables and data in those tables. If you don’t have a backup, all that data is gone. So once again, be extremely careful with this. Ask your system admin if you’re not comfortable with it, and if it’s your own server, then use your own judgement.