This tutorial shows basic syntax and commands and of the SQL language.

Creating and Dropping Databases

First, let's see what databases already exist in the MySQL server. We can view this information by using the SHOW command. Enter the following (except the mysql> part) into the MySQL console client, followed by a return:

mysql> SHOW DATABASES;

there are two databases already created in the MySQL server. The mysql database contains administration information for the MySQL server and should not be modified. The other database, test, is exactly what it sounds like: a test for the MySQL server. The test database does not contain anything, and it can be removed safely if required.

Creating a Database

So how do we add our own database in the MySQL server? To do this, we use the CREATE command. Let's say we wish to create a database called mydata; we would use the following syntax.

mysql> CREATE DATABASE mydata;

Notice that the semicolon is added after every command in the SQL language. When we press Return after entering this command, the console informs us that the query was okay.

Now that we have created the database, we can ensure it is on the server by again using the SHOW command, as follows:

mysql> SHOW DATABASES;

When we press Return with this command, we can see our database has been added to the list (note that the list is in alphabetical order, not the order in which the databases were created).

Dropping a Database

Now we will remove the database from the server. Note that when we do this, all data (if any) will be lost. To remove a database, we "drop" it from the server by using the DROP command. So to drop our new mydata database, we would use the following command:

mysql> DROP DATABASE mydata;

When we execute this command by pressing Return, the query will be reported as okay.

Now, if we again list the databases using the SHOW command:

mysql> SHOW DATABASES;

...we can see that our mydata database is no longer visible on the list.

Column (Field) Types in MySQL

Before we discuss the creation of tables within databases, now is a good time to mention the different column types that we can have in tables.

Each column in a table must be assigned a type, which represents the type of information that field is going to hold. Here is a complete list of available types you can use:

Creating, Modifying, and Dropping Tables

Creating Tables

Now that we know the possible types for the columns in our tables, let's look at how we actually go about creating a table.

Let's say that we wish to create a table to hold some user details within a database.

We want to store the user's title, first name, surname, age, e-mail address, and the date the user was added to the database. So we will require the following columns in our table:

Title  Firstname  Surname  Age  EmailAddress  DateAdded

Before we get into how to actually add it, let's first think how we are going to store the information—or more to the point, what types we require for each of the columns.

For the title, first name, and surname, we can use the TEXT type, as it contains plenty of characters to allow for all possibilities.

For age, an unsigned TINYINT would be an obvious choice, as ages are numerical and no one has ever been known to live past 255. For e-mail address, we can again use a TEXT type, as it will give us substantial storage space for the address. Finally, for the date that the user was added to the table, we can use a TIMESTAMP.

The TIMESTAMP also has a great property in that the time and date can be retrieved automatically into the database.

Now that we know which types we want for our columns, we need to create a database to add the table into. Let's create a database called myinfo with the following command:

mysql> CREATE DATABASE myinfo;

When we execute this command, the console should report that the query was okay. We can now check that our database has been created with the following command:

mysql> SHOW DATABASES;

Now we need to tell MySQL that we wish to perform actions on the myinfo database. This is accomplished by using the USE command:

mysql> USE myinfo;  

After executing this command, any DDL (Data Definition Language) and DML (Data Manipulation Language) statements that are executed will affect the database in use, which in this case is our myinfo database.

Now that we have our database set up and ready to accept commands, we can create our table (which we will name userinfo) with the following statement:

mysql> CREATE TABLE userinfo (      
-> id INT auto_increment,      
-> title TEXT,      
-> firstname TEXT,      
-> surname TEXT,      
-> age TINYINT,      
-> email TEXT,      
-> dateadded TIMESTAMP,      
-> PRIMARY KEY(id));

Let's break this up a little so we can see what is happening. First we declare that we wish to create a table by entering CREATE TABLE. Next we specify the name that we wish to call the table; in this case, the name is userinfo. Then we use parentheses to contain all of the columns that we require in our table and simply list all of the column names and types that we require. Note how we have added an extra field named id. Finally, note the addition of the primary key as the last parameter. This is used to determine how the table is optimized within the database.

We can now check that our table was created successfully by executing the following command:

mysql> SHOW TABLES;

Note you can also view the columns in a table by using the following command:

mysql> DESCRIBE userinfo;

When you execute this command, the console will display all the details for each of the columns in the userinfo table.

This information can be useful for both ensuring the table was created as you envisioned and to recap the columns a table contains at a later date.

Modifying Tables

So now that we know how to create a table, let's look at how we go about modifying it. Modifying a table can range from simply changing the type of one of the columns to adding a completely new column (or removing an existing column).

Let's first look at how we change the name of an existing column. In our userinfo table, we have a column called firstname, but let's now change this to read forename, a synonym for a person's first name.

To make this change, we need to use the following syntax:

mysql> ALTER TABLE userinfo CHANGE firstname forename TEXT;

Note we also must supply the data type for the column as well as its old and new names.

If we describe the userinfo table with the following command:

mysql> DESCRIBE userinfo;

...we can see that the column firstname has been renamed to forename.

We can also change the data types of columns in tables. Let's say that we want to change the age column from a TINYINT to an INT. We would use the following command:

mysql> ALTER TABLE userinfo MODIFY age INT;

After executing this command, if we describe the table, we can see the type has changed to INT.

Finally, it is good to know how to remove fields from a table (for example, if they are no longer required). Let's now say that we no longer require the e-mail field in our userinfo table. What we want to do is "drop" the field from our table. Here is the syntax for removing the e-mail field.

mysql> ALTER TABLE userinfo DROP email;

Once this command is executed, we describe the table with the following command:

mysql> DESCRIBE userinfo;

Dropping (Removing) Tables

Removing tables from a database is very simple, but without careful use it can have disastrous effects. The main thing to note is that when dropping a table, you also lose all the data contained within the table. Therefore, it is always wise to back up a database before executing any DROP commands.

Let's now look at how we drop the userinfo table from our myinfo database. To do this, we need to execute the following command in the MySQL console client.

mysql> DROP TABLE userinfo;

Once this command is executed, we can check that the table has been removed by listing what tables are currently in our myinfo database by executing the following command:

mysql> SHOW TABLES;

The table no longer exists in the database.