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:
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.
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.
When you create a table you need at least one column. A standard table will have an ID column as its first column.
CREATE TABLE IF NOT EXISTS table_name (column_name datatype, column_2 datatype);
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:
This will show you the columns the table has as well as the data type.
You can delete tables using the drop command.
DROP TABLE IF EXISTS table_name;
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:
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:
You should see the new columns we made and the data types.
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.