MySQL Select Database

Overview

Selecting (switching to) a database in MySQL is necessary before performing operations on that database. This chapter covers how to select databases and work with database context.

Database Context

The database context determines which database is the default for SQL statements. When you select a database, all subsequent operations without explicit database qualification will use that database.

SELECT DATABASE Methods

USE Statement

-- Select (use) a database
USE database_name;

-- Example
USE myapp;

-- Verify current database
SELECT DATABASE();

Command Line

# Connect directly to specific database
mysql -u username -p database_name

# Switch database after connection
mysql> USE database_name;

# Connect and execute query
mysql -u username -p -e "USE database_name; SHOW TABLES;"

Programming Languages

Python / Python

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='myapp'  # Select database on connection
)

cursor = conn.cursor()
cursor.execute("SHOW TABLES")

conn.close()

PHP / PHP

<?php
$conn = new mysqli('localhost', 'username', 'password', 'database_name');

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected to database: " . $conn->host_info;

// Select different database
$conn->select_db('another_database');

$conn->close();
?>

Current Database Information

Get Current Database

-- Get current database name
SELECT DATABASE();
SELECT SCHEMA();

-- Equivalent
SELECT current_database();

Get Database Details

-- Get current database character set
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

-- Get database size
SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb,
    COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = DATABASE()
GROUP BY table_schema;

-- List tables in current database
SHOW TABLES;

-- List tables with additional info
SHOW TABLE STATUS;

Multiple Database Operations

Qualified Names

-- Use fully qualified table names
SELECT * FROM database1.table1;
SELECT * FROM database2.table2;

-- Join tables from different databases
SELECT 
    db1.users.name,
    db2.orders.order_number
FROM db1.users
INNER JOIN db2.orders ON db1.users.id = db2.orders.user_id;

-- Use specific database tables in queries
SELECT * FROM other_database.users WHERE status = 'active';

Cross-Database Queries

-- Query across databases
SELECT 
    a.column1,
    b.column2
FROM database1.table1 a
JOIN database2.table2 b ON a.id = b.related_id;

-- Aggregate across databases
SELECT 
    'database1' AS db_name,
    COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'database1'
UNION ALL
SELECT 
    'database2' AS db_name,
    COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'database2';

Temporary Table Scope

-- Create temporary table in current database
USE database1;
CREATE TEMPORARY TABLE temp_table AS SELECT * FROM users;

-- Temporary table is visible only in current session
-- It uses the current database context

-- Switch database (temporary table still accessible)
USE database2;
SELECT * FROM temp_table;  -- Still accessible

Database Context in Procedures

Stored Procedures

-- Create procedure in specific database
USE myapp;
DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) AS user_count FROM users;
END //
DELIMITER ;

-- Call procedure (uses database where it was created)
CALL GetUserCount();

Prepared Statements

-- Prepare statement with dynamic database
SET @sql = CONCAT('SELECT * FROM ', DATABASE(), '.users');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Database Connection Patterns

Single Database

# Connect to specific database
conn = mysql.connector.connect(
    host='localhost',
    user='app_user',
    password='password',
    database='myapp'
)

Multiple Databases

# Connect to multiple databases
conn1 = mysql.connector.connect(
    host='localhost',
    user='app_user',
    password='password',
    database='app1'
)

conn2 = mysql.connector.connect(
    host='localhost',
    user='app_user',
    password='password',
    database='app2'
)

# Query both databases
cursor1.execute("SELECT * FROM table1")
cursor2.execute("SELECT * FROM table2")

Dynamic Database Selection

def get_connection(database_name):
    return mysql.connector.connect(
        host='localhost',
        user='app_user',
        password='password',
        database=database_name
    )

# Usage
conn = get_connection('myapp')

Session Management

Session Variables

-- Set database for session
USE myapp;

-- Session persists until disconnected or changed
-- All queries use 'myapp' database

-- Switch database in same session
USE another_database;

-- Current database changes for rest of session

Connection Pool

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=10,
    pool_reset_session=True,
    host='localhost',
    user='app_user',
    password='password',
    database='myapp'  # Default database
)

# Each connection from pool has default database
conn = pool.get_connection()

Database Context and Security

User Privileges

-- User can access specific databases
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'user'@'localhost';
REVOKE DROP ON myapp.* FROM 'user'@'localhost';

-- User cannot access other databases
SHOW GRANTS FOR 'user'@'localhost';

Prepared Statements

# Safe database selection with prepared statements
def get_user_data(database_name, user_id):
    conn = mysql.connector.connect(
        host='localhost',
        user='app_user',
        password='password',
        database=database_name
    )
    
    cursor = conn.cursor(prepared=True)
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    result = cursor.fetchone()
    
    conn.close()
    return result

Best Practices

Explicit Database Qualification

-- Good: Use explicit database names
SELECT * FROM myapp.users;

-- Avoid: Rely on current database context
USE myapp;
SELECT * FROM users;  -- Works but less clear

Connection Management

# Good: Use context manager or try-finally
import mysql.connector

conn = None
try:
    conn = mysql.connector.connect(
        host='localhost',
        user='app_user',
        password='password',
        database='myapp'
    )
    # Use connection
finally:
    if conn:
        conn.close()

Error Handling

try:
    conn = mysql.connector.connect(
        host='localhost',
        user='app_user',
        password='password',
        database='myapp'
    )
except mysql.connector.Error as err:
    print(f"Error connecting to database: {err}")
    # Handle error

Troubleshooting

Common Errors

-- No database selected
-- Error: No database selected
-- Solution: USE database_name;

-- Unknown database
-- Error: Unknown database 'database_name'
-- Solution: Check database name with SHOW DATABASES

-- Access denied
-- Error: Access denied for user to database
-- Solution: Check user privileges

Debug Commands

-- Check current database
SELECT DATABASE();

-- List available databases
SHOW DATABASES;

-- Check user privileges
SHOW GRANTS;

-- Check table existence
SHOW TABLES FROM database_name;

Summary

Database selection in MySQL involves:

• USE Statement: Primary method for selecting database
• Connection Parameter: Specify database on connect
• Context Awareness: Current database affects queries
• Cross-Database: Use qualified names for multiple databases
• Security: Proper user privileges for access

---

Previous: Drop Database

Next: Data Types