MySQL Select Database

Last updated: October 19, 2026Oct 19, 2026

A MySQL server usually holds several databases, and a table name like students only makes sense once MySQL knows which database you mean. Selecting a database with the USE statement sets the default for your session, so you don't have to type the database name before every table.

1. The Problem: No Database Selected

Right after connecting, no database is selected. A query on a plain table name fails:

SELECT DATABASE() AS current_db;

Result: 1 row

SELECT * FROM students;

ERROR 1046 (3D000): No database selected

2. Selecting a Database with USE

USE database_name;

USE school;
SELECT DATABASE() AS current_db;
SELECT * FROM students WHERE class = '8A';

Result: 1 row

Result: 2 rows

• USE affects only your session (connection); other users are unaffected.
• It stays in effect until you run another USE or disconnect.
• The semicolon is optional for USE in the mysql client, but it is good practice.

3. Switching Between Databases

USE shop;
SELECT DATABASE() AS now_using, COUNT(*) AS orders FROM orders;

USE school;
SELECT DATABASE() AS now_using, COUNT(*) AS students FROM students;

Result: 1 row

Result: 1 row

4. Working Across Databases with Qualified Names

You don't have to switch at all if you write database.table. This works from any current database and even lets one query use tables from two databases:

USE school;
SELECT s.name, o.item, o.qty
FROM school.students s
JOIN shop.orders o ON o.id = s.id;

Result: 2 rows

5. Errors You May See

USE schol;   -- typo

ERROR 1049 (42000): Unknown database 'schol'

6. Choosing the Database When You Connect

Name the database on the command line to select it immediately:

mysql -u root -p school          # connect and USE school
mysql -u root -p -D school       # same thing with -D
mysql -u root -p -e "SELECT COUNT(*) FROM shop.orders"   # one-off query, no USE needed

Inside the client, the status (or \s) command shows the current database next to Current database:.

7. Selecting the Database from Java

In JDBC, put the database name in the connection URL. You can also switch later with setCatalog():

import java.sql.*;

public class SelectDatabaseDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/school";     // 'school' is selected on connect
        try (Connection con = DriverManager.getConnection(url, "root", "your-password")) {
            System.out.println("Using: " + con.getCatalog());   // school

            con.setCatalog("shop");                             // like USE shop
            try (Statement st = con.createStatement();
                 ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM orders")) {
                rs.next();
                System.out.println("Orders: " + rs.getInt(1));
            }
        }
    }
}

8. Practice Exercises

SELECT COUNT(*) AS in_8a FROM school.students WHERE class = '8A';

USE shop;
SELECT item, qty FROM orders WHERE qty > 5;

9. Summary

• USE db_name; sets the default database for the current session.
• SELECT DATABASE(); shows which database is selected (NULL if none).
• database.table works without USE and allows cross-database queries.
• Pick a database on connect with mysql -u user -p db_name or in the JDBC URL.