How to Create Table in MySQL - DatabaseFAQs.com

In this tutorial, we will learn how to create a table in MySQL with its syntax. In addition to this, we will also learn the procedure of creating the table using MySQL command line and workbench.

Table of Contents

Create Table in MySQL

In MySQL Database Tables, data are stored in the form of rows and columns and we used them for both storing and displaying records in the structure format. For table creation in MySQL, we need three different things as follows:

Table Name
Field Name
Data Type with defined size

Let’s take an example to deeply understand this concept:

Suppose, a bookseller wants to store the details of the books such as name, writer, and price in the database, so for this, we will create a Table named “BooksDetails” with multiple fields named as BookName, Writer, and Price.

To define the detailed structure of the table, we also need to define the datatype with its size. So, we will define the BookName field as VARCHAR(25) as a datatype with character value 25, the Writer field as VARCHAR(100) as a datatype, and the Price field as FLOAT() datatype.

With this, we have understood the concept of a table in MySQL with an example. Now, we will proceed and learn the way of creating the table in MySQL using the command line client.

Create Table in MySQL using Command Line Client

MySQL allows the creation of a table in the database by using a CREATE TABLE command and flowing is the syntax for creating a MySQL Table using a command line client.

CREATE TABLE SYNTAX in MySQL

CREATE TABLE [IF NOT EXISTS] table_name
(
    column1 datatype,
    column2 datatype,
    column3 datatype,

   table_constraints  
);

The parameters are as follows:

Parameter	Description
CREATE TABLE	CREATE TABLE is the command which we used to create a table in MySQL database.
IF NOT EXISTS	It is a clause that is used to check whether the table we are going to create already exists.
table_name	It defines the name of the new table that we want to create.
column	It defines the name of the columns or the fields that we want to define in our new table.
datatype	It defines the type of the data we can store in the column or fields such as int, float, char, etc
table_constraints	It is used to define the constraints if we used it in the new table.

Now, let’s proceed and see an example to create the table in MySQL using the command line client.

Step 1 First open the MySQL console and write down the password If we have it during installation time.

Step 2 Now open the database in which you want to create a table. Here I am using the database named “AUTOMOBILE”.

Step 3 Next, we are going to create a table named “CAR_INVENTORY” in the database “AUTOMOBILE” using the following statement.

mysql> CREATE TABLE CAR_INVENTORY (
    ->   CarID INT,
    ->   ModelName VARCHAR(255),
    ->   VehicleColour VARCHAR(20),
    ->   Price FLOAT,
    ->   ManufacturingYear YEAR
    ->   FuelType VARCHAR(60),
    ->   VehicleMileage INT,
    ->   FuelCapacity INT,
    ->   VehicleWarranty INT,
    ->   City VARCHAR(255),
    ->   Description TEXT,
    ->   FounderName VARCHAR(55),
    ->   Image BLOB
    -> );

The parameters shown in the above code are described below:

CarID INT = CarID is a field name and INT is the datatype which is used to id of the car in the integer type value.
ModelName VARCHAR = ModelName is a field name and VARCHAR is the datatype that is used to store data in the variable and character type in the table with its maximum size of 255.
VehicleColour VARCHAR = VehicleColour is a field name and VARCHAR is the datatype that is used to store the color of the vehicle with a maximum limit of size 20.
Price FLOAT = Price is a field name and FLOAT is a data type that is used to store the price of the car in the table.
ManufacturingYear YEAR = ManufacturingYear is the field name and YEAR is the datatype which is used to store manufacturing of the car model in the table.
FuelType VARCHAR = FuelType is the field name and it takes data in the VARCHAR datatype and stores the type of fuel we used in the specific car model.
VehicleMileage INT= VehicleMileage is the field name and it stores the mileage of the car in the INT datatype.
FuelCapacity INT = FuelCapacity is the field name and INT is the datatype it is the store of the maximum fuel taken by the car.
VehicleWarranty INT = VehicleWarranty is the field name and INT is the datatype that stores the maximum warranty period of the vehicle.
City VARCHAR = City is the field name and VARCHAR is the datatype which stores the location of the car manufacturing in the table.
Description TEXT = Description is the field name and it takes data in the TEXT datatype which is used to store detailed information about the car model.
FounderName VARCHAR = FounderName is the field name and VARCHAR is the datatype that stores the name of the founder of the car model with a maximum limit size of 55.
Image BLOB = Image is the field name and BLOB is the data that is used to store the images of the car in the large binary type datatype.

Step 4: To create the table successfully press ENTER key.

Step 5: If you want to check whether the table is created successfully or not with the defined structure, type the below-given command in the cmd.

MySQL> DESCRIBE CAR_INVENTORY;

So, with this, we have learned to create a table in MySQL database using the command line client. Now we will move ahead and learn to create the table in the MySQL database using the workbench.

Create a Table using MySQL Workbench

MySQL Workbench is a unified Visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and Comprehensive administration tools for server configuration, user administration backup, and much more.

Let’s learn to create the table in MySQL using the MySQL Workbench. The steps are as follows:

Step 1: We should first need to launch the MySQL Workbench and log in using the username and password that you want.

Step 2: Now, move to the Navigation Tab and click on the Schema Menu. Here we can see all previously created databases and creates a new database. As I want to use the previously created database “voter_database”, so I have selected this one by double-clicking on it.

Step 3: After selecting the database we will get all menus under the database. These sub-menus are tables, views, functions, and stored procedures, as shown in the below screen.

Step 4: Now select the table sub-menu and click the right side and select the “Create Table” option. We can also click the “Create Table” icon for creating a table as shown in the screenshot.

Step 5: On the new table screen, we need to fill in all the details. To create a table here we will enter the table name first. In this, I enter the table name as “Voters”.

Step 6: Then click inside the middle windows and fill in the column or field details. Here the many columns name contains many data type that are available in Windows on-screen such as primary key, Not NULL, unique index, Binary, Unsigned Data type, AUTO Incremental, etc.

Here, I create a table with the following fields with the below-defined data structure.

Field Name	Data Type	Description
Voter ID	INT	It is used to store the Voter ID of the persons.
First_name	VARCHAR	It is used to store the first name of the person.
Last_name	VARCHAR	It is used to store the Last_name of the Voter persons and VARCHAR is a Datatype that is used to store data in the variable and character type in the table with its maximum size of 50
Age	INT	Age is the field name and INT is the datatype which is used to vote for persons of the Age in the integer type value.
Address	VARCHAR	The address is the field name and VARCHAR is the datatype that stores the Addresses of voters’ persons with a maximum limit Size of 100.
City	VARCHAR	The city is the field name and VARCHAR is the datatype that stores the location of the persons in the table.
State	VARCHAR	The state is the stored persons to which the state belongs in the table.
Country	VARCHAR	The Country is the field name and VARCHAR is the datatype that stores the country of the person. with a maximum limit size of 50.
email	VARCHAR	email is the field name or it is stored in the email of the person it’s taking the maximum limit of 100.
Phone number	VARCHAR	phone number is the store of a person’s mobile number and VARCHAR is the data type with take maximum limit of 10.
Occupation	VARCHAR	Occupation is the field name that is stored in the person’s occupation it takes a VARCHAR datatype.
Gender	VARCHAR	it is stored persons of Gender it takes Datatype VARCHAR With a taken maximum limit of 7.
Nationality	VARCHAR	Nationality is the store of that person’s Nationality and VARCHAR is the datatype it takes the maximum limit of 50.
Registration_date	DATE	Registration Date is Column Name it takes a datatype DATE That is used to store date time column.
Voting status	VARCHAR	Voting status is the Column name that is stored as the voting status it takes the data type VARCHAR with a maximum limit of 45.

Step 7: After filling in all the details, click on the “Apply” button.

example of create table in MySQL in workbench.

Step 8: After clicking on the apply button, we can see the SQL script to be applied to the database. To proceed to the next step click on the “Apply” button.

create table in MySQL using command query in workbench — Create table example

Step 9: If you want to check whether the table is created successfully or not with the defined structure, go to the schema menu and click on the newly created table and we get the table with all fields mentioned.

So, with this, we have learned to create a table in the MySQL database using the MySQL workbench.

Conclusion:

In this MySQL database tutorial, we have learned about how to create a table in MySQL using MySQL Workbench and MySQL Command-Line with examples. In addition, we have learned the syntax of CREATE TABLE command to create a table in the MySQL command line client.

We have also learned how to view newly created tables in the MySQL Command line client and in MySQL Workbench.