How to Create and Select MySQL Database

MySQL is the most popular and open-source relational database management system. In this article we will show you how to create MySQL or MariaDB database through the command line.

Prerequisite

Make sure MySQL or MariaDB is already installed on your system.
User account should have privileges to perform actions or have root user account.

Connect MySQL

To connect to the MySQL shell type the following command and enter your MySQL root user password when prompted:

mysql -u root -p

You should omit the -p option if you have not set a password for your MySQL root user.

Create a MySQL Database

To create a MySQL database is simple using a single command. Issue the following command to create a MySQL or MariaDB database.

CREATE DATABASE DATABASE_NAME;

In above command you should replace the DATABASE_NAME with your database name which you want as database name.

Query OK, 1 row affected (0.00 sec)

It will show the error message as following, if the database name is already exists.

ERROR 1007 (HY000): Can't create database 'DATABASE_NAME'; database exists

You can avoid this type of error messages using the IF NOT EXISTS statement:

CREATE DATABASE IF NOT EXISTS DATABASE_NAME;

Query OK, 1 row affected, 1 warning (0.00 sec)

In the output above, Query OK means that the query was successful, and 1 warning tells us that the database already exists, and no new database was created.

List All MySQL Databases

To list the all MySQL databases using MySQL shell, run the following command:

SHOW DATABASES;

This command will show all the databases available on the server. The output will be something like this :

+--------------------+
| Database           |
+--------------------+
| information_schema |
| database_name      |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

Select a MySQL Database

If you have created a new database, by default, it will not be selected to use. Use the following database before you start a MySQL session:

USE DATABASE_NAME;

Database changed

On the selecting database, now onwards all the operations and actions will be performed on the selected database.

You also can define the database name while you are connecting to the MySQL server. Just add the database name at the end of the connecting command as given below:

mysql -u root -p DATABASE_NAME

Create a MySQL Database with mysqladmin

Using the mysqladmin utility you can create a new MySQL database from the Linux terminal. For example, to create a database named DATABASE_NAME, you would use the following command:

mysqladmin -u root -p create DATABASE_NAME

Conclusion

We have shown you how to create and select MySQL databases using the MySQL shell and mysqladmin command.

If you have any questions or feedback, feel free to leave a comment below.

Tags: mariadb, mysql