MySQL Creating & Deleting Tables

Our database CES currently has no tables. We will create a new table named Games. Before we create the table we must select the database:

USE ces;

The USE command changes the database we are using. We are now using the database CES, and we can add tables and see existing tables.

SHOW TABLES;

This will show the current tables that we have in the database. If you type this you will get a message saying Empty set since we do not have any tables yet.

Create Tables

When you create a table you need at least one column. A standard table will have an ID column as its first column.

Syntax

CREATE TABLE IF NOT EXISTS table_name (column_name datatype, column_2 datatype);

Example

CREATE TABLE IF NOT EXISTS Games (Id INT);

Now we have a table called Games with a column called ID and data type INT (integer). You can see the columns of the table by typing:

EXPLAIN Games;

This will show you the columns the table has as well as the data type.

Deleting Tables

You can delete tables using the drop command.

Syntax

DROP TABLE IF EXISTS table_name;

Example

Now we will delete the table Games and re-create it using a different method.

DROP TABLE IF EXISTS Games;

This will delete our table Games.

Creating a Table Using SQL File

As you can see from the above commands it can be quite difficult creating tables using the CLC, as one little typo can cause an error. You can use a SQL file (extension sql) to write your code and then in the CLC use the SOURCE command to execute your query. We will use Notepad++ to create the files. When you make the file save it to your desktop for ease of access, because you will need to refer to it using the CLC.

In Notepad++ type the following:

CREATE TABLE IF NOT EXISTS Games
(

Id INT PRIMARY KEY AUTO_INCREMENT,
gName TEXT,
gYear Year,
gRating INT

);

This is similar to the example above, except it is easier to read and maintain. The top command is the same as before, then you have your parentheses and inside that you have your columns. You have your column name and data type (except for the ID column you have some modifiers which will be demonstrated in the next tutorial). Each column is separated by a comma except the last column, as no other columns exist. Then you have your semi-colon which shows the end of the query. Save this file to your desktop in a folder called mysql, and save it as Tables.sql. 

Now in the CLC type the following:

SOURCE C:\users\yourname\desktop\mysql\tables.sql

Make sure to replace yourname with your user name. This directory should be the default for Windows users, and do not include the semi-colon here. If you have done things correctly the code should execute and you can now type:

EXPLAIN Games;

You should see the new columns we made and the data types.

Summary

This tutorial covered how to create and delete tables, as well as how to add columns and specify the data type. Remember that a table must have at least one column, which will usually be an ID column. It is best to use a SQL file to write your code then use the SOURCE command to execute your query.