How To Create a Table in MySQL and MariaDB on an Ubuntu Cloud Server

Status: Deprecated

This article covers a version of Ubuntu that is no longer supported. If you are currently operate a server running Ubuntu 12.04, we highly recommend upgrading or migrating to a supported version of Ubuntu:

• Upgrade to Ubuntu 14.04.
• Upgrade from Ubuntu 14.04 to Ubuntu 16.04
• Migrate the server data to a supported version

Reason: Ubuntu 12.04 reached end of life (EOL) on April 28, 2017 and no longer receives security patches or updates. This guide is no longer maintained.

See Instead:
This guide might still be useful as a reference, but may not work on other Ubuntu releases. If available, we strongly recommend using a guide written for the version of Ubuntu you are using. You can use the search functionality at the top of the page to find a more recent version.

What is MySQL and MariaDB

MySQL and MariaDB are two popular database systems that use the SQL language. Many applications on Ubuntu use MySQL or MariaDB to manage their information.

In this article, we will discuss how to create tables within the MySQL or MariaDB interface. We will be performing these tasks on an Ubuntu 12.04 VPS server, but most of the commands should be the same for any Ubuntu machine.

How to Install MySQL and MariaDB on Ubuntu

MySQL and MariaDB have the same command syntax, so either database system will work for this guide.

To install MySQL on Ubuntu, use the following command:

sudo apt-get install mysql-server

To install MariaDB on Ubuntu 12.04, type the following into the terminal:

sudo apt-get update
sudo apt-get install python-software-properties
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://repo.maxindo.net.id/mariadb/repo/5.5/ubuntu precise main'
sudo apt-get update
sudo apt-get install mariadb-server

For more information on how to install MySQL on Ubuntu follow this guide.

Creating a Database in MySQL and MariaDB

Before we can look at tables, we need to configure an initial database environment within MySQL.

Log into MySQL or MariaDB using the following command:

mysql -u root -p

Type in the password you set up during installation to continue.

We will create a database to learn on called "playground". Create the database with the following command:

CREATE DATABASE playground;

We will switch to the new database with the following command:

USE playground;

We are now ready to begin learning about tables.

How to Create a Table in MySQL and MariaDB

We have named our database "playground", so now let's create a table with this database that describes equipment found in a playground.

The table creation syntax follows this convention:

CREATE TABLE [IF NOT EXISTS] name_of_table (list_of_table_columns) [engine=database_engine]

The sections in brackets ("[" and "]") are optional. The "IF NOT EXISTS" option forces the table creation to abort if there is already a table with the same name. It is important to use this option to avoid getting an error if the table is already created.

The "engine=database_engine" section is for choosing a specific type of table to optimize your information handling. This is outside of the scope of this article and a good default (InnoDB) is selected if this option is omitted.

We will explain the different fields needed in the columns section in a moment, but for now, let's create our table:

CREATE TABLE IF NOT EXISTS equipment (
    equip_id int(5) NOT NULL AUTO_INCREMENT,
    type varchar(50) DEFAULT NULL,
    install_date DATE DEFAULT NULL,
    color varchar(20) DEFAULT NULL,
    working bool DEFAULT NULL,
    location varchar(250) DEFAULT NULL,
    PRIMARY KEY(equip_id)
    );

Query OK, 0 rows affected (0.03 sec)

Defining Columns

To see what we've accomplished, use the following command to print out the columns of our new table:

show columns in equipment;

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          | |+--------------+--------------+------+-----+---------+----------------+
| equip_id     | int(5)       | NO   | PRI | NULL    | auto_increment |
| type         | varchar(50)  | YES  |     | NULL    |                |
| install_date | date         | YES  |     | NULL    |                |
| color        | varchar(20)  | YES  |     | NULL    |                |
| working      | tinyint(1)   | YES  |     | NULL    |                |
| location     | varchar(250) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

The results give us some insight into the fields necessary to define a column. Each column description in the table creation command is separated by a comma, and follows this convention:

Column_Name Data_Type[(size_of_data)] [NULL or NOT NULL] [DEFAULT default_value] [AUTO_INCREMENT]

These are the values of each column definition:

<ul>
<li><strong>Column Name</strong>: Describes the attribute being assigned.  For instance, our first column is called "equip_id" because it will hold the unique ID number associated with each piece of equipment.</li>

<li><strong>Data Type</strong>: Specifies the type of data the column will hold.  Can be any of MySQL's data types.  For instance, "int" specifies that only integer values will be accepted, while "varchar" is used to hold string values.  There are many data types, but these are outside of the scope of this article.

<em>Note: Most data types need a size value in parentheses to specify the maximum amount of space needed to hold the values for that field.</em></li>

<li><strong>Null</strong>: Defines whether null is a valid value for that field.  Can be "null" or "not null".</li>

<li><strong>Default Value</strong>: Sets the initial value of all newly created records that do no specify a value. The "default" keyword is followed by the value.</li>

<li><strong>auto_increment</strong>: MySQL will handle the sequential numbering internally of any column marked with this option, in order to provide a unique value for each record.</li>
</ul>

Finally, before closing the column declarations, you need to specify which columns to use as the primary key by typing "PRIMARY KEY (columns_to_be_primary_keys).

We used our "equip_id" column as the primary key because the "auto_increment" option guarantees the value to be unique, which is a requirement of a primary key.

How to Insert Data Into a MySQL or MariaDB Table

Let's insert a record into our table. To do this, we'll use the following syntax:

INSERT INTO table_name (field1, field2, ...) VALUES (value1, value2, ...);

Every string value must be placed in quotation marks. Every column with "auto_increment" set does not need a value, as the database will provide it with the next sequential number.

We can add a slide to our playground equipment table like this:

INSERT INTO equipment (type, install_date, color, working, location)
VALUES
("Slide", Now(), "blue", 1, "Southwest Corner");

We used a special function called "Now()" that fills in the current date for the date column.

To see the information, query the table. The asterisk (*) is a special wildcard character that matches everything. This query selects everything in the equipment table:

SELECT * FROM equipment;

+----------+-------+--------------+-------+---------+------------------+
| equip_id | type  | install_date | color | working | location         |
+----------+-------+--------------+-------+---------+------------------+
|        1 | Slide | 2013-07-26   | blue  |       1 | Southwest Corner |
+----------+-------+--------------+-------+---------+------------------+
1 row in set (0.00 sec)

Let's add another entry:

INSERT INTO equipment (type, install_date, color, working, location)
VALUES
("Swing", Now(), "green", 1, "Northwest Corner");

We can see that our new data is present in the table:

SELECT * FROM equipment;

+----------+-------+--------------+-------+---------+------------------+
| equip_id | type  | install_date | color | working | location         |
+----------+-------+--------------+-------+---------+------------------+
|        1 | Slide | 2013-07-26   | blue  |       1 | Southwest Corner |
|        2 | Swing | 2013-07-26   | green |       1 | Northwest Corner |
+----------+-------+--------------+-------+---------+------------------+
2 rows in set (0.00 sec)

How to Delete Tables in MySQL and MariaDB

To delete a table we can use the following syntax:

DROP TABLE table_name;

Be very careful with this command, because once the table is deleted, the data inside cannot be recovered.

First, let's view our current table so that we can establish what the "show tables" command looks like:

SHOW tables;

+----------------------+
| Tables_in_playground |
+----------------------+
| equipment            |
+----------------------+
1 row in set (0.00 sec)

Let's delete our equipment table:

DROP TABLE equipment;

And now, check the "playground" tables list again:

SHOW tables;

Empty set (0.00 sec)

We no longer have any tables in the "playground" database, so the operation was successful.

Conclusion

You should now be comfortable with performing basic operations on a table.

These are fundamental skills needed to manage MySQL or MariaDB. Gaining familiarity with them now will pay off as you dive into other areas of database management.