MySQL Use Database

In this article, we explained how to select a database in MySQL and how to view the current database.

In a MySQL Database server, there may be multiple databases. If you want to perform operations such as queries, you should select a database to be operated on first. You can use the USE statement to select or switch databases.

MySQL USE syntax

To select a database as the default database, please use the USE statement as follow:

USE database_name;

The database_name is name of a the database which you want to select.

If success, MySQL will give you a response as following:

Database changed

If you specified a database name that is not existing, MySQL will give you an error like the following:

ERROR 1049 (42000): Unknown database 'db1'

MySQL USE database Example

Open mysql client and follow the steps below to select the database to operate on.

  1. Log in to the MySQL server:

    mysql -u root -p

    Please follow the prompts to enter the password for the root account.

    At this time, the database has not been selected, and you cannot perform query, insert, etc. operations. For example, if you try to view all the tables, an error will be returned.

    SHOW tables;

    ERROR 1046 (3D000): No database selected

  2. Choose testdb Database.

    USE testdb;

    Database changed

    Now the default database is testdb.

  3. Switch to sakila Database.

    USE sakila;

    Database changed

    At this point, the default database has been switched from testdb to sakila.

Specify the database when logging in

You can directly specify the database you want to operate when you log in to the MySQL server. Please use the following command:

mysql -u root -p -D testdb

Here, we use the -D testdb parameter to specify the database which database we want to connect to.

View current database

If you want to get the database currently use, please use the following 3 methods:

  • Using DATABASE() or SCHEMA() function:

    SELECT DATABASE();

    +------------+
| DATABASE() |
+------------+
| testdb     |
+------------+
1 row in set (0.00 sec)

  • Using STATUS statement in mysql client:

    STATUS

    --------------
mysql  Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Connection id:          550
Current database:       testdb
...

  • Using SHOW TABLES statement in mysql client:

    SHOW TABLES;

    +------------------+
| Tables_in_testdb |
+------------------+
| order_item       |
| user             |
+------------------+
2 rows in set (0.00 sec)

    In the output, the header Tables_in_testdb tells us that the current database is testdb.

Conclusion

In this article, we learned to select a database using the USE statement and several ways to get the current database.