How to Create a Database in MySQL Using the Command Line

How to Create a Database in MySQL

In this article we will going to show you how just easy it is to create a database in MySQL by executing a simple SQL query.

In order to create a database, you’ll have to open the mysql command line interface and enter your database commands while the server is running.

Here is a generic CREATE DATABASE statement syntax.

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]

The database name must be unique within the MySQL server instance. If you try to create a database with a name that already exists, MySQL issues an error. To avoid such an error in case you accidentally create a database that already exists, you can specify the IF NOT EXISTS option. When IF NOT EXISTS is used database is created only if given name does not conflict with an existing database’s name.

It is possible to specify the character set as well as the collation for the new database at the creation time. However, if you omit the CHARACTER SET and COLLATE clauses, MySQL will use the default character set and collation for the new database.

But first, log into MySQL server as the root user.

mysql -u root -p

You will be prompted to enter password. Type the MySQL root user password and hit Enter.

Create MySQL database

Now we are ready to create the new database in MySQL. We decide to name it animals. The command would be:

CREATE DATABASE animals;
Query OK, 1 row affected (0.001 sec)

Congratulations! Your database has been created.

Finally, you can see list of existing databases by running following SQL command:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| animals            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

As you can see, our newly created MySQL database named animals is listed.

Conclusion

After completing this tutorial, you should have a sense of how to create a database in MySQL using command line. As we have seen above, it is pretty easy.

In addition to, you can use the following article to learn how to backup and restore MySQL database from command line in Linux.

For more information about MySQL databases, see the MySQL documentation.

Leave a Reply

Your email address will not be published.

Latest from Tutorials