In MySQL, you can create tables via the GUI or by running SQL code. Here's a rundown on both methods.

Now that we've created our database, let's create some tables.

Creating Tables via the GUI

One way of creating a table is via the MySQL Workbench GUI. This is an easy option for those who prefer graphical user interfaces.

Even if you start by using the GUI, I recommend that you become familiar with creating tables programatically using the CREATE TABLE statement.

The CREATE TABLE Statement

The CREATE TABLE statement does exactly as the name suggests — it creates a table.

However, you need to provide information about the table that you want MySQL to create. For example, the table name, the column names and their data types, default values, constraints, etc.

Here's the example we used in the previous page:

CREATE TABLE Fruit (FruitName VARCHAR(20), DateEntered DATETIME);

This creates a table called Fruit that contains two columns: FruitName and DateEntered.

The FruitName column uses a data type of VARCHAR(20), which means it accepts data that is a VARCHAR (variable-length string) up to 20 characters long.

The DateEntered column uses a data type of DATETIME which means that the column accepts DATETIME format (a date and time) in the following format YYYY-MM-DD HH:MM:SS.

You can specify that a column should always contain a value (that it should not be "null"). To do this, include NOT NULL in that column's specifications (example below).

If you attempt to insert data that is not in the format specified for that column, you will receive an error.

Create Multiple Tables

To create multiple tables you simply add a CREATE TABLE statement for each table you need to create.

CREATE TABLE table_1 (...);

CREATE TABLE table_2 (...);

Example

We previously created an empty database called "FruitShop". But we didn't create any tables at the time. Let's update the script so that it creates a database and two tables. We'll add some more stuff to the code.

DROP DATABASE IF EXISTS FruitShop;

CREATE DATABASE FruitShop;

USE FruitShop;

​

CREATE TABLE Units (

UnitId TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,

UnitName VARCHAR(10) NOT NULL,

DateEntered DATETIME NOT NULL,

DateUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (UnitId)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

​

CREATE TABLE Fruit (

FruitId SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

FruitName VARCHAR(45) NOT NULL, 

Inventory SMALLINT UNSIGNED NOT NULL,

UnitId TINYINT UNSIGNED NOT NULL,

DateEntered DATETIME NOT NULL,

DateUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (FruitId),

CONSTRAINT fkFruitUnits FOREIGN KEY (UnitId) REFERENCES Units (UnitId) ON DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Run the above script and then refresh the left column of MySQL Workbench. You should see your newly created FruitShop database listed under the SCHEMAS tab.

Expand the Tables node and you'll see the tables you just created:

You can also expand each table to see its columns and any other objects that you created for that table.

Display Table Information Programatically

You can also display table information programatically. That is, by entering an SQL command to display the information you need.

In the following example, we set the default database to be FruitShop by using USE FruitShop. We then ask MySQL to display all tables for the default database (by using SHOW TABLES):

USE FruitShop;

SHOW TABLES;

You can also find out the structure of a given table by using the following command (simply replace the table name with the table you need the structure of):

DESCRIBE Fruit;

Here's the result of that command on our database: