MySQL Create Table

How to Create Table in MySQL with example?. Here, we will use both MySQL command prompt and Workbench for MySQL Create Table statement. MySQL uses Tables to store and Manage Data. The table is a combination of Rows and Columns. We have to use the Create Table Statement to create a table in MySQL.

MySQL Create Table Syntax

The basic syntax to create a table in MySQL is as shown below:

CREATE TABLE 'Table Name'
(
    Column_Name1 Data_Type(Size) [NULL | NOT NULL],
    Column_Name2 Data_Type(Size) [NULL | NOT NULL],
     …
    Column_NameN Data_Type(Size) [NULL | NOT NULL]
);

Let us see the individual items from the MySQL Create table syntax:

  • Table Name: Please provide a Unique table name. It throws an error if the table name already exists.
  • Column_Names: Unique or distinct Column Names required for this table
  • Data Type: A valid Data type for every individual column. For example, Int, Varchar, float
  • Size: Please provide a valid integer number here
  • NULL or NOT NULL: If the NULL option is selected, the column accepts both normal values and NULL values. Otherwise, it throws an error stating the Column should not be empty.

How to Create Table in MySQL using Command Prompt

Before we start creating a table in MySQL company Database, Let us see the list of existing tables inside the database. To get this information, use the SHOW TABLES command.

SHOW TABLES;

The below screenshot shows the available Tables in MySQL database.

MySQL Create Table Example 1

As you see, the company Database had no existing tables. For the demo purpose, we create a new table called sales using MySQL create table statement.

CREATE TABLE sales
(
   SaleID INT NOT NULL AUTO_INCREMENT,
   Product_Name VARCHAR(100) NULL,
   Product_Description TEXT
);

From the below screenshot, see that the MySQL create table Command executed successfully.

MySQL Create Table Example 2

Now, let’s see the list of tables available in the company database. And notice that the sales table name in the list.

MySQL Create Table Example 3

MySQL Create Table Example – Workbench

This time, we use the below-shown code to create a table in MySQL called Customers inside the company Database

-- Create Table in MySQL Example
USE company;
CREATE TABLE customers (
    CustID INT NOT NULL AUTO_INCREMENT,
    First_Name VARCHAR(50) NULL,
    Last_Name VARCHAR(50) NULL,
    Education VARCHAR(50) NULL,
    Profession VARCHAR(50) NULL,
    Yearly_Income INT NULL,
    Sales FLOAT(10, 2) NULL,
  PRIMARY KEY (CustID)
)

NOTE: Before you start creating a TABLE, It is always advisable to check if a Table exists or not.

MySQL Create Table Example 4

In the above code, we declared 7 Columns in our newly created table in MySQL:

  • Our first column of MySQL creates a table is CustID of Integer data type, and it won’t allow NULL values. We also defined this column as Auto Incremental. (This is optional If you want You can delete Auto_Increment portion)
  • FirstName, LastName, Education, Profession columns belong to Varchar data type, and it won’t allow NULL values. We also assigned the size to 50. It means these columns accept up to 50 characters
  • The sixth column is the Yearly Income of INT data type, and it allows NULL values.
  • Sales columns belong to float data type, and it allows NULL values.

From the below screenshot, see that the command executed successfully. Now, you can see the Newly created table under the Tables folder.

MySQL Create Table Example 5

TIP: If you didn’t find the newly created table, Please click on the refresh button beside the SCHEMAS

How to find MySQL Table Definition

If you want to find the existing MySQL table definition, right-click on the table name and select the Table Inspector option from the context menu.

MySQL Create Table Example 6

Once you select the Table Inspector option, a new tab opened with table Information. Please go to the DDL tab to see the table definition.

MySQL Create Table Example 7

Check whether MySQL Table name exists or not?

Let us see what happens if we create a new table in MySQL with an existing name. From the below screenshot, you can see it is throwing an error saying: Table Sales already exists.

USE company;
CREATE TABLE sales
(
    Id INT NOT NULL,
    Name VARCHAR(50) NULL,
    Country VARCHAR(50)    
)
MySQL Create Table Example 19

NOTE: It is always advisable to check whether MySQL table name exists already or not

If you have the privilege to see the list of tables available in a database, then use the SHOW TABLES command. If it is not the case, use the below option.

The following statement only executes Create Table Statement if the sales table not available in the company database.

USE company;
CREATE TABLE IF NOT EXISTS sales
(
    Id INT NOT NULL,
    Name VARCHAR(50) NULL,
    Country VARCHAR(50)    
);
MySQL Create Table Example 20

Try different table name using MySQL create table command

USE company;
CREATE TABLE IF NOT EXISTS salesDetails
(
	Id INT NOT NULL,
    Name VARCHAR(50) NULL,
    Country VARCHAR(50)    
);
MySQL Create Table Example 21

MySQL Create Table Using Workbench GUI

To create a table in MySQL, Within the SCHEMAS, Expand the Database folder on which you want to create a table. Right-click on the Tables folder opens the context menu. Please select the Create Table… option.

MySQL Create Table Example 8

Once you select the Create Table… option, the following window opens to design a table. Please change the Table_Name from new_table to Employees

MySQL Create Table Example 9

You can use the Collation drop-down box to change the Schema Default collation.

MySQL Create Table Example 10

You can change the Default server Engine using Engine drop-down list

MySQL Create Table Example 11

For now, we are leaving all the default configurations. Next, click on the arrow to get more space for designing the table.

MySQL Create Table Example 12

Click on the Empty row below the Company Name automatically create a new column for you. Let me rename it as Empid.

MySQL Create Table Example 13

Please change the Data Type as per your requirement. And also use checkboxes too.

MySQL Create Table Example 14

From the below screenshot, you can see, there are four columns. Click the apply button to create new table employees.

MySQL Create Table Example 15

TIP: Please use the checkboxes beside the Column Names or under the storage section, to make the column as Primary, Unique, Not Null, Binary, Unsigned, Auto Incremental, Zero Fill, and Generated.

Click Apply button

MySQL Create Table Example 16

Next, click the Finish button to create Table in Workbench

MySQL Create Table Example 17

From the below screenshot, you can see the employees table.

MySQL Create Table Example 18

TIP: You can use INSERT Statement to insert data into this newly created table