How To Delete Tables in MySQL

MySQL should need no introduction. It is the power behind most websites that run WordPress or many custom CMS and the engine behind many a company database. It is a simple but very effective system that makes managing and storing data very straightforward once you know how. Today we are going to cover how create and delete tables in MySQL.

Tables are collections of related data structured in a logical way. Tables use rows and columns to order data (values) in cells. Each table has a unique name. Each column will have a name, data type and any other attributes relevant to the column. Rows will also have names and will contain data relevant to the columns.

It is this foundation that forms the core of most databases, including MySQL.

Create and delete tables in MySQL

I shall walk you through creating and deleting tables in MySQL. You can use phpMyAdmin or queries to work in MySQL. I’ll show you both. You will likely find that you gravitate towards the GUI or queries. There is no right way or best way, use what you feel most comfortable with.

Be aware that deleting a table is permanent. SQL does not keep copies or save files to protect data. Unless you have taken a backup of your database, there is no going back. It might be a good idea to take that backup before you start creating and deleting tables.

First let us create a table in phpMyAdmin:

1. Open phpMyAdmin and log in.
2. Select the database from the left menu.
3. Underneath the database name in the left menu, select New.
4. Fill in the form, giving the table a unique name.
5. Complete as much of the page as you need.
6. Select Save in the bottom right corner.

You have successfully created a table in My SQL!

Now let us delete one.

1. Open phpMyAdmin and log in.
2. Select the database from the left menu.
3. Select a table from the list in the left menu or in the center box.
4. Select Drop from the center row of the table you want to delete. Drop is SQL-speak for delete.
5. Confirm in the popup box that appears.
6. The table is deleted.

Create and delete tables using an SQL query

Using SQL queries is definitely one for the purist but it is good practice for all DBAs to keep their hand in. Here is how create and delete tables in MySQL using queries.

1. Log in and access the query command window in MySQL.
2. Let us create an example database. Type ‘CREATE DATABASE NAME;’. Where you see NAME, name your database. For example, ‘CREATE DATABASE TechJunkie;’
3. Type ‘USE DATABASENAME;’. Where you see DATABASENAME, add the name you created above. For example ‘USE TechJunkie;’.
4. Type ‘CREATE TABLE IF NOT EXISTS TABLENAME (Id INT);’. For example ‘CREATE TABLE IF NOT EXISTS TechJunkieUsers (Id INT);’.

This creates a database called TechJunkie and a table called TechJunkieUsers within that database. Where you see (Id INT), this creates a column called ID with the integer data type.

You could go further in this example and type something like:

‘CREATE TABLE IF NOT EXISTS TechJunkieUsers (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, lastname VARCHAR(20), firstname VARCHAR(20), Joindate DATE).

This would create a table if it did not already exist and auto increment each row with an ID number while creating columns for the last name, first name and their join date. VARCHAR means any character can fill the cell with a maximum of 20 characters.

Delete a table using an SQL query:

1. Log in and access the query command window in MySQL.
2. Type ‘USE DATABASENAME’ to access the database that contains the table you want to delete. For example, ‘USE TechJunkie;’
3. Type ‘DROP TABLE IF EXISTS TABLENAME;’ For example ‘DROP TABLE IF EXISTS TechJunkieUsers;’.

Both phpMyAdmin and using an SQL query will achieve the same result. You can create and delete tables in SQL with very little effort. Sometimes too little effort!

You can also use a PHP file for creating databases, tables and entries in one go and I will cover that another time. There is no ‘best’ way to use SQL. Some purists say you should always use queries to perform actions in databases and there is sense in that. It is where the average DBA spends most of their time, but for beginners or those managing their own WordPress website, phpMyAdmin is easier to use to begin with.

Got any tips for MySQL or phpMyAdmin for beginners? Have something to add to the above processes? Tell us about it below!