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.
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
The parameters are as follows:
|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.
|It defines the name of the new table that we want to create.
|It defines the name of the columns or the fields that we want to define in our new table.
|It defines the type of the data we can store in the column or fields such as int, float, char, etc
|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.
|It is used to store the Voter ID of the persons.
|It is used to store the first name of the person.
|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 is the field name and INT is the datatype which is used to vote for persons of the Age in the integer type value.
|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.
|The city is the field name and VARCHAR is the datatype that stores the location of the persons in the table.
|The state is the stored persons to which the state belongs in the table.
|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 is the field name or it is stored in the email of the person it’s taking the maximum limit of 100.
|phone number is the store of a person’s mobile number and VARCHAR is the data type with take maximum limit of 10.
|Occupation is the field name that is stored in the person’s occupation it takes a VARCHAR datatype.
|it is stored persons of Gender it takes Datatype VARCHAR With a taken maximum limit of 7.
|Nationality is the store of that person’s Nationality and VARCHAR is the datatype it takes the maximum limit of 50.
|Registration Date is Column Name it takes a datatype DATE That is used to store date time column.
|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 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.
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.
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.
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.