MySQL MariaDB

Create a New Table in MySQL

MySQL is a well-known and popular database management software that is available for free. The MySQL software is adaptable, reliable, and fast. After installing a DBMS, the first thing to do is to create a database and then start creating tables in it. This article shows you how to add a new table to your MySQL database.

Before you begin learning about how to create a table in MySQL, it is assumed in this article that you have already installed MySQL on your system and that you have command over the creation and usage of databases in MySQL. If you have not yet installed MySQL, we have a dedicated article that shows you how to install MySQL in Ubuntu 20.04 (How to install and set up MySQL Database on Ubuntu 20.04 – Linux Hint).

To begin the process, open the terminal and run the ‘mysql -V’ command to check whether MySQL is installed on your system:

mysql -V

If you have the latest version installed, you can check the status of your system’s MySQL service by entering the following:

sudo systemctl status MySQL

If it is active and running, then you are all set to proceed to the next step. If the service is not active and running, then you can start the service by running the following command:

sudo systemctl start MySQL

After successfully initiating the service, sign in to the MySQL client as a root user by issuing the command below:

sudo mysql -u root -p

If you have not yet set the root password, you can skip the ‘-p’ part.

After logging in to the MySQL shell, look through the databases and choose the database in which you want to create a table. Run the following command to get more information about all the databases in the shell:

Select a database by running the ‘USE’ command in the MySQL shell, as follows:

USE database_name;

After obtaining the output “Database changed,” you can now move on to the step of creating a table in your database. But before getting started with creating the table, first, take a look at the tables that exist in your selected database. You can list all the tables using the ‘SHOW TABLES’ command.

As you can see, either there are two tables or there is no table in our selected database.

There are two ways to create a table in MySQL. One method uses the “IF NOT EXISTS” clause to check whether a table with the same name as the search query already exists. The other method is performed without using the “IF NOT EXISTS” clause.

Syntax

The syntax for creating a table in MySQL is as follows:

CREATE TABLE [IF NOT EXISTS] table_name(

column_name type,

olumn_name type,

...

constraints

)

In this syntax:

  • The ‘IF NOT EXISTS’ clause will prevent errors if a table in that database with the same name already exists.
  • The table_name is the name of the table that you want to create.
  • In the parenthesis, we define the columns (separated by a comma), their type, and the table constraints at the end.
  • The column_name informs about the creation of the column.
  • The type is the data type of a given column (which can be INT, CHAR, or VARCHAR), with the character lengths in parentheses; i.e., VARCHAR(20).
  • At the end of the syntax, we define the constraints of the table, such as the PRIMARY KEY, FOREIGN KEY, etc.

Example: Creating a Table

Suppose you want to create a table with the name “author_table,” that contains four columns: ‘author_id,’ ‘author_name,’ ‘author_sex,’ and ‘author_description.’ The ‘author_id’ column will be the Primary Key, which will be assigned later in the constraints. In sum, the MySQL statement for creating a table would look like this:

CREATE TABLE IF NOT EXISTS author_table(

author_id INT,

author_name VARCHAR(20),

author_sex CHAR(1),

author_description text,

PRIMARY KEY ( author_id )

);

After creating a table and setting the output “Query OK, 0 rows affected (0.35 sec),” we will now take a look at the tables using the following command:

Now, we have another table in our selected database.

Details about a single table can be obtained here by running the DESCRIBE command.

DESCRIBE author_table;

And that is it! You have now successfully created a table in MySQL.

Conclusion

This article explained the syntax for creating a table in detail, including a simple yet useful example to create a new table in MySQL.

About the author

Shehroz Azam

A Javascript Developer & Linux enthusiast with 4 years of industrial experience and proven know-how to combine creative and usability viewpoints resulting in world-class web applications. I have experience working with Vue, React & Node.js & currently working on article writing and video creation.