How to Create Table in MySQL

In this tutorial, we will learn how to create a table in MySQL using both the MySQL command line and Workbench approaches, including the best practices with multiple examples.

How to Create Table in MySQL

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

1. Table Name
2. Field Name
3. Data Type with defined size

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

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 BookName, Writer, and Price.

To define the detailed structure of the table, we also need to determine the data type and its size. So, we will define the BookName field as VARCHAR(25) as a datatype with a character value of 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 to know the Prerequisites needed here.

Prerequisites for Creating MySQL Tables

Before creating your first table, you’ll need:

1. MySQL is installed on your system or you have access to a MySQL server
2. A MySQL user account with appropriate privileges
3. A database where your table will reside
4. Basic understanding of SQL syntax
5. A MySQL client (command line, MySQL Workbench, phpMyAdmin, etc.)

Let me guide you through the process using different methods and approaches.

Method 1: Using Command Line Client.

MySQL allows the creation of a table in a database using the CREATE TABLE command. The following is the syntax for creating a MySQL Table using a command-line client.

SYNTAX

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 that we used to create a table in the 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 names of the columns or the fields that we want to define in our new table.
datatype	It defines the type of 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 use it in the new table.

To create table in MySQL command line, follow the steps below.

Step 1: First, open the MySQL console and record the password if you have it from the time of installation.

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 that is used to identify 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 a 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 that is used to store the manufacturing year of the car model in the table.
• FuelType VARCHAR = FuelType is the field name, and it stores the type of fuel used in a specific car model in the VARCHAR datatype.
• VehicleMileage INT: VehicleMileage is the field name, and it stores the car’s mileage 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 that 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 data type that stores the name of the founder of the car model, with a maximum size limit 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 a large binary type.

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

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

MySQL> DESCRIBE CAR_INVENTORY;

Method 2: 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.

To create table in MySQL Workbench, follow the steps below.

Step 1: First, launch MySQL Workbench and log in using the username and password of your choice.

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

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

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

Step 5: On the new table screen, fill in all the details. To create a table, we will first enter the table name. 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 column names contain various data types available in Windows on-screen, such as primary key, Not NULL, unique index, Binary, Unsigned Data type, AUTO_INCREMENT, etc.

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

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 a maximum size of 50
Age	INT	Age is the field name, and INT is the data type, 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 data type 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 has a maximum limit of 100.
Phone number	VARCHAR	Phone number is the store of a person’s mobile number, and VARCHAR is the data type that takes a 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 as persons of Gender, it takes Datatype VARCHAR with a maximum limit of 7.
Nationality	VARCHAR	Nationality is the store of that person’s Nationality, and VARCHAR is the datatype that takes the maximum limit of 50.
Registration_date	DATE	Registration Date is Column Name, it takes a datatype DATE, which is used to store the date and 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.

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

Step 9: To verify whether the table was created successfully with the defined structure, navigate to the schema menu and click on the newly created table. You will then see the table with all the specified fields.

Best Practices for MySQL Table Creation

Here are the best practices:

1. Choose Appropriate Data Types

Selecting the right data types impacts storage requirements and query performance:

Data Type	Best For	Example
TINYINT	Small integers (0-255)	Flag fields, small counts
INT	Standard integers	IDs, counts
BIGINT	Large integers	Large IDs, big numbers
VARCHAR	Variable-length strings	Names, titles
CHAR	Fixed-length strings	State codes, zip codes
TEXT	Long text	Descriptions, content
DATETIME	Date and time	Event timestamps
DECIMAL	Precise numbers	Prices, financial data

2. Use Constraints to Ensure Data Integrity

• PRIMARY KEY: Uniquely identifies each record
• FOREIGN KEY: Maintains referential integrity between tables
• UNIQUE: Ensures all values in a column are different
• NOT NULL: Ensures a column cannot have NULL values
• CHECK: Ensures values meet specific conditions

3. Consider Table Normalization

While denormalization is sometimes necessary for performance, starting with normalized tables (usually to 3NF) helps maintain data integrity and reduces redundancy.

Conclusion:

Creating tables in MySQL is a fundamental skill for effective database design. Whether you’re using the command line, MySQL Workbench, or script files, the principles remain the same: define your columns with appropriate data types, establish relationships between tables, and implement constraints to ensure data integrity.

You may like to read:

• How to Install MySQL on Windows
• Different Datatypes in MySQL
• How to create a database using MySQL
• How to Insert Single Record into a Table in MySQL