Create a Table in MySQL
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:
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.
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.
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):
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):
Here's the result of that command on our database:
