Summary: in this tutorial, you will learn how to select a MySQL database using the USE command from the MySQL Command Line tool and MySQL Workbench.
After connecting to the MySQL instance, you need to select a particular database to start working with. This is because there might be more than one databases in the MySQL Server.
1) Selecting database using MySQL Command Line tool
Generally, when you logged into the MySQL Command Line tool without specifying the default database name, MySQL sets the current database to NULL. Let’s check:
First, log in to the MySQL server Command Line Client tool using root user.
mysql -u root -p
It will ask you for the root password.
Enter password:
You give the correct root password of the MySQL server and press enter. If everything goes well then you will see mysql> on the screen. Now, check the current database using the following statement.
SELECT database();
It will return the following:
+------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.04 sec)
That means the current database is not set. If you issue any statement, MySQL will issue an error. For example:
SELECT * FROM customer;
This will give the following output:
ERROR 1046 (3D000): No database selected
To select a database, we need to use the USE statement.
USE database_name;
For example, the following statement set the current database to companydb in MySQL.
USE companydb;
If you see the following message, then it means that the command is successful.
Database changed
To verify it, you can use the select database() statement:
SELECT database();
Output:
+------------+ | database() | +------------+ | companydb | +------------+ 1 row in set (0.00 sec)
If the companydb database doesn’t exist, you’ll get the following error after executing the USE statement:
ERROR 1049 (42000): Unknown database 'companydb'
In this case, you need to find which databases are available on your server by using the show databases statement:
show databases;
The output may look as below:
+--------------------+ | Database | +--------------------+ | companydb | | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 6 rows in set (0.00 sec)
Selecting a database during login
There is another way to select a database in MySQL. In this method, you can specify the name of the database while logging into the MySQL server using the Command Line tool by mentioning the -D flag followed by the database name.
For example, if you want to select the companydb as the default database, you specify the companydb after the -D flag as below:
mysql -u root -D companydb -p
Same as before, this will ask for MySQL root password.
Enter password:*********
You give the correct root password and press enter. After successful login, you can then check the current database using the following statement.
SELECT database();
This will display the output as below:
+------------+ | database() | +------------+ | companydb | +------------+ 1 row in set (0.00 sec)
What will happen if you provide the database name during login that does not exist.
For example, you provide one database customerdb which does not exist in the MySQL.
mysql -u root -D customerdb -p
This will give the following error even after providing the correct root password for MySQL.
ERROR 1049 (42000): Unknown database 'customerdb'
Selecting a database in MySQL Workbench
If you are using the MySQL Workbench application to connect MySQL instance, you can select a database by specifying the database name in the Default Schema: text box as shown in the following screenshot.
You can also select the database after login into the MySQL Workbench by using Set as Default Schema feature or by issuing the USE statement.
