How to Create a Table in MySQL

Creating tables is perhaps the most popular task in database development. In this article, we will review the ways of creating MySQL tables.

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

• Executing a query that includes the CREATE TABLE statement
• Using the corresponding functionality of MySQL-related tools and IDEs, such as Table Editor for MySQL

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.

Contents

• Create a table in MySQL with syntax examples
  • Connect to MySQL
  • Create a MySQL database
  • Create a MySQL table
• What data types are available?
• Create a duplicate table
• Create a temporary table
• Create a MySQL table from CSV
• Conclusion

Create a table in MySQL with syntax examples

Connect to MySQL

To connect to a MySQL server, open dbForge Studio for MySQL and proceed with the following steps:

1. To open the Database Connection Properties dialog, navigate to Database > New Connection:

Alternatively, you can click New Connection:

2. Configure connection properties in the dialog:

• Type: select the connection type (TCP/IP, Named pipe)
• Host: enter a hostname
• Port: if you have selected TCP/IP from the Type drop-down menu, enter a port of a host you want to connect to
• User and Password: enter your credentials for a host you are connecting to
• Database: you can leave this field by default
• Connection Name: the value of this field is generated automatically after you have entered a hostname. However, you can change it manually if required
• Environment Category: optionally, you can select an environment category (Development, Production, Sandbox, and Test)

3. After you set up the connection properties, click Connect.

You should see a new connection in the Database Explorer:

Create a MySQL database

To create a database in MySQL, you can use this command:

CREATE DATABASE <database_name>;

There are several ways for creating databases in MySQL, but in this example, we’re going to create the car_sale database with dbForge Studio for MySQL:

CREATE DATABASE car_sale;

To see the created database in the Database Explorer, click Refresh Object:

Also, you can check whether the database has been successfully created with the following command:

SHOW DATABASES;

Create a MySQL table

Now, that we have created the database, it’s high time to create the first table in it. We’ll create a small table called Managers with the Primary Key that is required for data integrity:

MANAGER_ID	FIRST_NAME	LAST_NAME	LAST_UPDATE
08276	Brad	Craven	11/29/1976
19222	Kraig	Boucher	10/10/1990
23003	Enrique	Sizemore	5/26/1990
80460	Letha	Wahl	3/9/1971
86849	Harlan	Ludwig	1/11/1997

In dbForge Studio for MySQL, it’s possible to do it in two ways:

• Using the CREATE TABLE statement
• Using the New Database Object option

Way 1 – using the CREATE TABLE statement

Run this MySQL statement and click Execute:

CREATE TABLE MANAGERS (
  MANAGER_ID varchar(45) NOT NULL,
  FIRST_NAME varchar(45) NOT NULL,
  LAST_NAME varchar(45) NOT NULL,
  LAST_UPDATE date NOT NULL,
  PRIMARY KEY (MANAGER_ID)
);

And then click Refresh Object:

Let’s populate the table with data:

INSERT INTO MANAGERS(MANAGER_ID,FIRST_NAME, LAST_NAME, LAST_UPDATE)
VALUES
(08276,'Brad','Craven',11/29/1976),
(19222,'Kraig','Boucher',10/10/1990),
(23003,'Enrique','Sizemore',5/26/1990),
(80460,'Letha','Wahl',3/9/1971),
(86849,'Harlan','Ludwig',1/11/1997);

To check if data has been added, execute the following statement:

SELECT *
FROM MANAGERS;

Way 2 – using the New Database Object option

1. Navigate to Database > New Database Object:

2. Choose a database where you want to create a new table and select Table in the list of object types on the right, and then click Create.

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

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

In our exploration of the MySQL database, we also present a clear-cut tutorial on how to show or list tables, giving you an additional tool in your database navigation toolkit.

What data types are available?

When you create a new table in MySQL, you must specify a type of data for each column. It’s required for SQL to determine how to cooperate with stored data.

MySQL supports such categories of data types:

• String
• Numeric
• Date and time
• Spatial
• JSON

We have described MySQL data types in our topic Data Types in MySQL: Tutorial and Full List with Examples of Data Formats. Feel free to read it to get more details.

Create a duplicate table

With dbForge Studio for MySQL, you can quickly and easily copy a table:

1. Right-click the required table and click Duplicate Object:

2. Choose the database where you want to create the table copy from the Destination database drop-down menu.

3. Enter a name for the table copy or leave it by default in the New object name field.

4. If you want to copy both the table structure and data, select Copy data.

5. Finally, click OK. Do not forget to click Refresh Object. The duplicate table will be created in the specified database:

Create a temporary table

In some cases, database developers might need to create a temporary table, for example, for storing temporary data. This will allow using these data multiple times within a stored procedure in MySQL.

Before you create a temporary table, learn the following rules:

• A user must be granted the “create temporary table” privilege
• If you work with the InnoDB database engine, you cannot create temporary tables
• You can name temporary tables in the same way as MySQL typical tables
• Temporary tables have a separate relationship with a database schema. Thus, if you delete a database, temporary tables will not necessarily be deleted within the database
• The CREATE TEMPORARY TABLE statement doesn’t invoke the implicit commit

Let’s create a temporary table Car_model:

CREATE TEMPORARY TABLE CAR_MODEL
  (CAR_ID varchar(45) NOT NULL,
  LAST_UPDATE DATE NOT NULL,
  PRIMARY KEY(CAR_ID)   
);

And add some data to the created table. In this statement, we convert the type of data with the STR_TO_DATE function:

INSERT INTO CAR_MODEL(CAR_ID,LAST_UPDATE)
VALUES
(08266,STR_TO_DATE('11/29/1976','%m/%d/%Y')),
(19622,STR_TO_DATE('10/10/1990','%m/%d/%Y')),
(23003,STR_TO_DATE('5/26/1990','%m/%d/%Y')),
(80568,STR_TO_DATE('3/9/1971','%m/%d/%Y')),
(76849,STR_TO_DATE('1/11/1997','%m/%d/%Y'));

Note that the table with data will be deleted after you have closed the session. To check if you have created the table and added data, execute this query:

SELECT * 
FROM CAR_MODEL;

Create a MySQL table from CSV

There is another way to create a table in dbForge Studio for MySQL – import it from a CSV file:

1. Navigate to Database > Import Data:

2. Click CSV and click three dots next to the File name field to select the required file.

3. Click Next and then choose a database where you want to import the table from the Database drop-down menu.

4. Select New table and enter the name of the table you want to import data to.

5. To customize other options, click Next. To import immediately, click Import.

6. Click Refresh Object. You can check the imported table in the specified database:

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 walkthrough, we were using dbForge Studio for MySQL.

The second method actually allows you to get the same results as with the first one, but in a way that can be more convenient and intuitive in many cases. You can try dbForge Studio by downloading its free 30-day trial version.
For more information, you can watch the following video tutorial: