MySQL - Select Database

MySQL: Selecting a Database with the USE Statement

After establishing a connection to a MySQL server, your first step is often to specify which database you intend to work with. A single MySQL server can host multiple databases, so you must select one to perform operations like creating tables, querying data, or managing records.

The USE Statement

The primary SQL command for selecting a database is the USE statement. Once a database is selected, all subsequent SQL commands in that session (such as CREATE TABLE, SELECT, INSERT, etc.) will be executed within the context of that database.

Syntax

The syntax for the USE statement is straightforward:

USE database_name;

Here, database_name is the name of the database you wish to activate for your session. Database names must be unique within the RDBMS.

Example: Creating and Selecting a Database

Let’s walk through a complete example. First, we create a new database called ACME_CORP.

CREATE DATABASE ACME_CORP;

To verify its creation and see all available databases, you can run SHOW DATABASES;:

SHOW DATABASES;

The output will list all databases, including the one we just created:

Database
information_schema
acme_corp
mysql
performance_schema
sys

Now, select the ACME_CORP database to make it the active context:

USE ACME_CORP;

Output

If successful, MySQL will confirm the change:

Database changed

With ACME_CORP selected, you can now create tables. For example, let’s create an employees table:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Running SHOW TABLES; will now list the tables within the ACME_CORP database.

SHOW TABLES;

Output:

Tables_in_acme_corp
employees

Common Errors: Selecting a Non-Existent Database

Attempting to USE a database that does not exist will result in an error.

Example

USE NonExistentDB;

MySQL will return a specific error code and message:

ERROR 1049 (42000): Unknown database 'nonexistentdb'

Selecting a Database in Application Code (Client Programs)

While USE is useful in command-line clients, in application code, it is a best practice to specify the database directly in the connection configuration. This is more efficient and avoids potential state-related bugs. However, you can still execute the USE statement if needed.

Best Practice: Connection Configuration

Here’s how to select a database via the connection string or configuration object in various languages, which is the recommended approach.

Python
Node.js
Java
PHP



In Python, using the `mysql-connector-python` library, specify the database name with the `database` parameter during connection.

import mysql.connector
from mysql.connector import errorcode

try:
    # Recommended: Specify database in connection config
    cnx = mysql.connector.connect(
        user='root',
        password='your_password',
        host='127.0.0.1',
        database='ACME_CORP'
    )
    print("Connection successful. Database 'ACME_CORP' selected.")
    cursor = cnx.cursor()
    cursor.execute("SHOW TABLES;")
    print("Tables:", cursor.fetchall())

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Authentication failed: incorrect username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    if 'cnx' in locals() and cnx.is_connected():
        cursor.close()
        cnx.close()
        print("MySQL connection is closed")

Output

Connection successful. Database 'ACME_CORP' selected.
Tables: [('employees',)]
MySQL connection is closed



In Node.js with the `mysql2` library, the `database` property in the connection options is the standard method.

const mysql = require('mysql2/promise');

async function main() {
  let connection;
  try {
    // Recommended: Specify database in connection config
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_password',
      database: 'ACME_CORP'
    });

    console.log("Connected successfully to 'ACME_CORP' database.");

    const [rows, fields] = await connection.execute('SHOW TABLES;');
    console.log('Tables found:', rows.map(row => Object.values(row)[0]));

  } catch (error) {
    console.error(`Failed to connect or query: ${error.message}`);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Connection closed.');
    }
  }
}

main();

Output

Connected successfully to 'ACME_CORP' database.
Tables found: [ 'employees' ]
Connection closed.



In Java, using JDBC, the database name is appended to the connection URL. This is the standard and most reliable method.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class SelectDatabase {
    // Recommended: Specify database in the JDBC URL
    static final String DB_URL = "jdbc:mysql://localhost:3306/ACME_CORP";
    static final String USER = "root";
    static final String PASS = "your_password";

    public static void main(String[] args) {
        // Use try-with-resources for automatic resource management
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {

            System.out.println("Connection successful. Database 'ACME_CORP' selected via URL.");

            // You can now execute queries directly
            try (ResultSet rs = stmt.executeQuery("SHOW TABLES;")) {
                System.out.println("Tables in database:");
                while (rs.next()) {
                    System.out.println("- " + rs.getString(1));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output

Connection successful. Database 'ACME_CORP' selected via URL.
Tables in database:
- employees



In PHP, using the modern `mysqli` object-oriented interface, the database name is passed as the fourth parameter to the constructor.

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'your_password';
// Recommended: Specify database in connection config
$dbname = 'ACME_CORP';

// Create connection
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

// Check connection
if ($mysqli->connect_error) {
  die("Connection failed: " . $mysqli->connect_error);
}
echo "Connected successfully. Database '$dbname' selected.<br>";

// Alternative (less common): using the USE statement
// $mysqli->query("USE ACME_CORP");

$sql = "SHOW TABLES;";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
  echo "Tables found:<br>";
  while($row = $result->fetch_assoc()) {
    echo "- " . $row["Tables_in_acme_corp"] . "<br>";
  }
} else {
  echo "No tables found";
}

$mysqli->close();
?>

Output

Connected successfully. Database 'ACME_CORP' selected.
Tables found:
- employees