Join Bill Weinman for an in-depth discussion in this video Deleting a table with DROP TABLE, part of SQL Essential Training.
When you no longer need a table, you may delete it with the drop table statement. Here again I'm using the in memory database, and I'm going to go ahead and create a table, and I'll insert some values into it. And we'll go ahead and select from it, so we can see that it's working. We'll press go here, and you can see we've created a table in the in memory database, and it has its values in it. Now remember that this, the in memory database, so every time we need to go ahead and create the table and everything. Each time we use it, the database is destroyed and created again.
So, if I want to delete this table, I'll go ahead and insert the drop table statement in here. So, I'll say, Drop Table test. And I would expect my select to fail, because after creating the table and inserting values into it, I've deleted the table with the Drop Table statement. And so, the select will have nothing to select from, and it should bring back an error. So when I say go, you'll notice that there's an error in query number four. One, two, three, four.
That's the select statement. And it says general error one. No such table test, because the table has been deleted with the drop table statement. So drop table is, indeed, very simple. If I try to drop the table again after it's Already been dropped. I'll just go ahead and Copy this, and Paste it. Then you'll notice we get two errors. They're the same general error for, no such table test, for query four and five. That would be the second drop table and the select.
You can include a clause in the drop table statement, to test if the table exists, before executing it. So I can say, drop table If exists, like this, and then we won't get this query number four error anymore, because that's no longer an error. It's conditionally dropping the table. So you can see that deleting a table from the database is very simple, using the drop table statement. Of course, you want to be very careful that you don't delete a table you may need in the future, and be sure to always keep good backups of your data.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.