selecting a MySQL Database Using USE Statement

 Selecting a MySQL Database Using USE Statement

Summary: in this tutorial, you will learn how to select a database in the mysql program and MySQL Workbench by using the USE statement.

To select a particular database to work with you issue the USE a statement as follows:

USE database_name;

In this statement, following the USE the keyword is the name of the database that you want to select.

Selecting a MySQL database via command line

The following steps demonstrate how to login to MySQL Server using the mysql program and select a database to work with:

First, log in to the MySQL using a particular user e.g., root:

>mysql -u root -p

In this command, we have specified the user root with -u flag and then used the -p flag. MySQL prompted for the password of the user. You need to type the password and press Enter to log in.

Second, issue the USE the command to select a database, for example classicmodels:

mysql> use classicmodels;
Database changed

In this command, we selected the classsicmodels database. If the database exists, MySQL issued the message “Database changed“.

In case the database does not exist, MySQL issued the following error:

mysql> use abc;
ERROR 1049 (42000): Unknown database 'abc'

Third, you can get the name of the currently connected database by using the database() function:

mysql> select database();
+---------------+
| database()    |
+---------------+
| classicmodels |
+---------------+
1 row in set (0.00 sec)    

Finally, to select another database, you just need to issue the USE statement and the new database name, for example:

mysql> use tempdb;
Database changed

Selecting a database when you login

If you know which database you want to work with before you log in, you can use the -D flag to specify it as follows:

>mysql -u root -D classicmodels -p

In this command, we specified the database name classicmodels following the -D flag. Because we used -p flag, MySQL prompted for the password of the root user. You just need to provide the password and press Enter to log in.

Once logged in, you can check the current database:

> select database();
+---------------+
| database()    |
+---------------+
| classicmodels |
+---------------+
1 row in set (0.00 sec)

Selecting a database in MySQL Workbench

If you connect to a MySQL Server via the MySQL Workbench application, you can select a database when you create the database connection as shown in the screenshot below:

Once you logged in, you can select another database by issuing the USE a statement or use the Set As Default Schema the feature provided by MySQL Workbench.

In this tutorial, you have learned various ways to select a MySQL database via the mysql program and MySQL Workbench application.