How to Create a Table in MySQL?

Introduction

Whether you are a hosting provider, e-commerce platform owner, web developer, or an institution, you have a huge amount of data at your disposal. How would you manage such enormous data and information? The answer is MySQL, a piece of software that acts like a cabinet for your valuable data. 

MySQL works on databases to offer exceptional organizational features to the user. The database feature of MySQL is entirely based on tables. This article is a perfect guide to learning about simple ways to create a table in MySQL. Also, you will discover the syntax to create a table and a database in MySQL through this easy-to-understand piece of writing.

How to Create a Table in MySQL? Discover Syntax with Examples

Under this headline, we will learn to create a table in MySQL. This process involves three major steps, namely, connecting to MySQL, creating a database, and, at last, creating the table. Let’s look at each step one by one by one with syntax to create a table in MySQL.

Connect to MySQL

The user, first of all, needs to establish a connection with MySQL through the terminal window. Use the below syntax to perform the required function.

mysql -u your_username -p

Now, the system will ask the user to enter the password. At this point, the user can either go for the existing ID or the root ID. The user then needs to replace the chosen ID with “your_username.” 

Also Read: How To Create New MySQL User and Grant Privileges?

Create a MySQL Database

After a successful connection with MySQL, the user now has to follow the process to build a database. The following syntax will help the user complete the process.

CREATE DATABASE database_name;

The user can easily change the name of the database as per the requirement by replacing the term “database_name” in the syntax. Once the database is created, it is advisable to verify the creation through the command “SHOW DATABASES;” in the terminal window. The system will present the user with the complete list of databases after this command.

If the user intends to make any changes to any of the shown databases, the command “USE database_name;” will allow the user to do so. Here, the term “database_name” will be replaced by the particular credentials of the database. 

Create a MySQL Table 

We have reached the final question of “how to create a table in MySQL“. Let’s look at the basic syntax below.

In the above syntax, the term “create table” instructs the system to perform the action variable “table_name,” which should be replaced with the actual table name provided by the user. Moving on, the term “(….)” is used for defining the different columns. If the table is based on books, the column could be like the author, publisher, title, etc. On the other hand, if the table is associated with elections, the column could be something like party, symbol, vote, etc.

We have arrived at a very basic question that is associated with the insertion of information in the table. Go through the below instructions to learn about the process. 

The name of the above table that we are trying to create is “people.” In the above command, we will use the statement “INSERT INTO” to add name and age to the table. Now, look at the altered command below. 

What Data Types are Available?

You might have noticed that in the above content, we have provided different data for the table. A very peculiar question might arise in your mind relating to the types of data that are available for MySQL tables. Let’s see the creation of tables in MySQL with different data examples.

String Data 

This type of data is used for inserting information that varies in length. Common string data are “CHAR,” “VARCHAR,” and “TEXT.”

Numeric

Under this data type, the user will find help for decimal and whole numbers. The most common examples of numeric data are “FLOAT,” “INT,” and “DECIMAL.” 

Date and Time

Whenever the user needs to include a calendar or clock in a table, this data type can be used. A common example of this data is “TIMESTAMP,” which is used for automatic updation of time.

Spatial 

Spatial data is used for geographic references in the table. The user can include data like “POINT” and “LINESTRING” in the table.

How to Create a Duplicate Table in MySQL?

The user can easily create a duplicate copy of the existing table using the basic syntax mentioned below. 

In the above syntax, the term “CREATE TABLE” has been used with “AS” to ensure that the duplicate table has the same content and structure as the existing one. We will use the following command to produce a duplicate table named “FAMILY” based on the current “FRIENDS” table.

Also Read: 70+ Windows CMD Commands List with Screenshots

How to Create a Temporary Table?

The above action can be performed by using the simple statement “CREATE TEMPORARY TABLE.” After you execute this statement, please be aware that any temporary tables created will only exist for the duration of the session and will be automatically deleted once the session is over. These tables are mostly used to store temporary data or testing and debugging. The below syntax will lead to the creation of a temporary table.

In the above syntax, the term “table_name” as usual, is replaced with the required name. Also, the user has the option to choose any data type like string, numeric, spatial, etc.

Recommended: How to Delete or Drop a Table in MySQL

Conclusion

MySQL is a powerful DBMS, or database management system, that allows the user to manage and store huge datasets. This article acts as a simple guide to creating a table in MYSQL. The basic steps of the process include connection to MySQL and creation of the database. Further, this piece has focused on types of data, which include spatial, numeric, and string. The article has clearly explained various syntaxes in simple language to help the reader. The content of the article ends with the headline on creating a temporary table in MySQL through the simple statement, “CREATE TEMPORARY TABLE.” We hope the entire article has been an easy journey for our readers to clearly understand various processes regarding the creation of tables in MySQL.