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:
If you have the latest version installed, you can check the status of your system’s MySQL service by entering the following:
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:
After successfully initiating the service, sign in to the MySQL client as a root user by issuing the command below:
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:
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:
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:
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.
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.
