How to Create Tables using MySQL CREATE TABLE Statement and via GUI Tool for MySQL

May 22nd, 2020

There are two main ways of creating tables in MySQL databases:

  • Executing a query that includes a CREATE TABLE statement
  • Using the corresponding functionality of MySQL-related tools and IDEs

The first approach is helpful when you need to create tables specifically via a script. For example, you want to create a table at a very specific time, but you won’t be able to do it manually due to being away at that moment. So, you can schedule the table creation process using the Windows Scheduler – this is where the table creation query will help.

The second method is great for creating tables on the spot. It can also be more convenient in many cases as it’s very visual and intuitive. There are many tools that provide this functionality, and we will take a look at one of them – dbForge Studio for MySQL.

Let’s learn more about these two methods in detail.

Creating a table using a CREATE TABLE statement

The general purpose of the CREATE TABLE statement is to create a table in a database.

The overall syntax for this statement looks like this:

CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   …,
   table_constraints
) ENGINE=storage_engine;

  • This is what it means:
    IF NOT EXISTS is an optional clause allowing you to check if the table you’re trying to create already exists in the database. If it does, the query won’t be executed further.
  • table_name is a placeholder for the name of the table you want to create. So, if you want to call it ‘table1’, just change ‘table_name’ to ‘table1’. Additionally, you can specify the name of the database you want to create the table in. In this case, add the database name before the table name like this: database1.table1
  • In the section located between the parentheses, you need to specify the list of columns that you want your table to contain by entering a column definition string for each one of them. You can also provide the necessary table constraints.
  • The ENGINE clause allows you to specify your preferred storage engine. If you don’t specify one explicitly, the default one will be used. Starting from MySQL version 5.5, InnoDB is the default storage engine. Previous versions use MyISAM.

Now, to elaborate on the column definition section.

Here’s the full syntax of a column definition string:

column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] column_constraint;

And here are the details:

  • The column_name is a placeholder for the name of the column
  • The data_type specifies the columns data type, and optionally the size
  • NOT NULL is an optional clause that allows you to specify that the column cannot contain NULL values. Additional optional clauses such as UNIQUE or CHECK can be specified.
  • In the DEFAULT value clause, you can enter the default value for the column.
  • If you want the column to be autoincremented, i.e., for the value of the column’s row being automatically increased by 1 each time a record is added to the table, mark the column with an AUTO_INCREMENT clause. Keep in mind that a table can only contain 1 auto-incremented column.

After all the columns are defined, you can provide table constraints like FOREIGN KEY or PRIMARY KEY.

Here’s an example of a MySQL CREATE TABLE query:

CREATE TABLE table1
( number INT(11) AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
city VARCHAR(32),
age VARCHAR(7),
CONSTRAINT key1 PRIMARY KEY (name)
);

Creating a table using an IDE for MySQL

Let’s look at how we can create a table with the help of dbForge Studio for MySQL, a multifunctional IDE.

First, launch dbForge Studio for MySQL and select the necessary database connection.

When this is done, click Database → New Database Object:

navigating to database object creation window

The database object creation window will be opened. Select Table in the list of object types on the right and then click Create:

creating a table

A tab will be opened that will allows you to design and configure the table you want to create. Essentially, the functionality allows you to achieve the same results as you can get with a CREATE TABLE query, but in a visually comprehensive and convenient way:

designing the table

After you’ve specified all necessary columns and settings, click Apply Changes. That’s it, the table will be created in your current database. If you need to update some settings of the table, just change the corresponding options and click Refresh Object:

updating the created table

Conclusion

We’ve looked at two different approaches to creating a table in a MySQL database: using the CREATE TABLE statement and via an IDE. In this case, we used dbForge Studio for MySQL.

The second method actually allows you to get the same results as the first one, but in a way that can be more convenient and intuitive in many cases. You can try it by downloading a free version of dbForge Studio for MySQL.

Leave a Comment