Selecting a Database — The USE Statement
Creating a database is only the first step. Before MySQL knows which database your queries target, you must select it as the active context. This chapter covers the USE statement, how MySQL’s session context works, the dot-notation shortcut for referencing objects across databases, and how to always check which database is currently active.
How MySQL Session Context Works
Every time you connect to MySQL — whether through the command-line client, MySQL Workbench, a Python script, or any other tool — you start a new session. Each session has an active database (also called the default database). When you write a query like SELECT * FROM orders, MySQL looks for the orders table inside whichever database is currently active in your session.
The USE statement sets which database is “active” in your session. All unqualified queries then resolve against that database.
The USE Statement
The USE statement tells MySQL to make a specific database the active database for the current session. It is one of the simplest statements in SQL — no semicolon is strictly required (though using one is good style), and it takes effect instantly.
1-- Select ecommerce_db as the active database 2USE ecommerce_db; 3-- Output: Database changed 4 5-- Now this query targets ecommerce_db.customers 6SELECT * FROM customers; 7 8-- Switch to a different database at any time 9USE blog_db; 10-- Output: Database changed 11 12-- Now queries target blog_db 13SELECT * FROM posts;
MySQL response: When USE succeeds, MySQL prints Database changed. If the database doesn’t exist, you get ERROR 1049 (42000): Unknown database 'xyz' — unlike DROP and CREATE, there is no IF EXISTS option for USE.
Checking the Active Database — DATABASE()
If you’re unsure which database is currently selected — common in long sessions or shared environments — use the DATABASE() (or SCHEMA()) function to check instantly.
1-- Check which database is currently active in this session 2SELECT DATABASE(); 3-- Returns: ecommerce_db (or NULL if no database is selected) 4 5-- SCHEMA() is a synonym for DATABASE() 6SELECT SCHEMA(); 7 8-- STATUS shows current user, server, db, and connection details 9STATUS; 10 11-- Also available via system variable 12SELECT @@version, DATABASE() AS current_db;
⚠️ No active database = queries without qualification will fail. If you connect to MySQL without running USE first, DATABASE() returns NULL and any query like SELECT * FROM customers will throw ERROR 1046: No database selected.
Dot Notation — Accessing Tables Across Databases
You don’t always need to switch the active database to query a table. MySQL lets you fully qualify any object using dot notation: database_name.table_name. This is especially useful when you need to query tables from multiple databases in a single statement.
1-- Query a table in a specific database without USE 2SELECT * 3FROM ecommerce_db.customers; 4 5-- JOIN tables from two different databases in one query 6SELECT 7 e.customer_id, 8 e.first_name, 9 a.total_sessions 10FROM ecommerce_db.customers e 11JOIN analytics_db.user_sessions a 12 ON e.customer_id = a.user_id; 13 14-- Mixing qualified and unqualified names (USE ecommerce_db first) 15USE ecommerce_db; 16SELECT o.order_id, c.first_name 17FROM orders o -- unqualified = ecommerce_db.orders 18JOIN ecommerce_db.customers c -- fully qualified 19 ON o.customer_id = c.customer_id;
When to use dot notation vs USE: In scripts and stored procedures, always use full dot notation — it makes your code self-documenting and avoids accidental errors if the script is run in the wrong database context. In interactive sessions, USE is quicker and easier to type.
Exploring a Database After Selecting It
Once you’ve selected a database with USE, several commands help you explore its contents:
1USE ecommerce_db; 2 3-- List all tables in the currently active database 4SHOW TABLES; 5 6-- List tables in a specific database without switching to it 7SHOW TABLES FROM blog_db; 8 9-- View the structure (columns, types, constraints) of a table 10DESCRIBE customers; 11 12-- Shorter alias for DESCRIBE 13DESC customers; 14 15-- View the full CREATE TABLE statement for a table 16SHOW CREATE TABLE customers;
Selecting a Database at Connection Time
Most MySQL clients and programming language connectors let you specify the database to select at the time of connection, so you don’t have to run a USE statement at all. Here’s how it looks across different connection methods:
1-- Command-line client: pass the database as the last argument 2-- mysql -u root -p ecommerce_db 3 4-- Python (mysql-connector-python): pass 'database' in connect() 5-- import mysql.connector 6-- conn = mysql.connector.connect( 7-- host = 'localhost', 8-- user = 'root', 9-- password = 'yourpassword', 10-- database = 'ecommerce_db' ← active from the start 11-- ) 12 13-- PDO (PHP): include dbname in the DSN string 14-- $pdo = new PDO('mysql:host=localhost;dbname=ecommerce_db', $user, $pass);
Key Takeaways
- The
USE database_name;statement sets the active database for the current session — all unqualified table references resolve against it. SELECT DATABASE();(orSCHEMA()) tells you which database is currently active. It returnsNULLwhen no database has been selected.- Fully qualified dot notation (
db_name.table_name) lets you reference any table without switching the active database — essential for cross-database queries. - After
USE, explore the database withSHOW TABLES,DESCRIBE table_name, andSHOW CREATE TABLE table_name. - Most client libraries (Python, PHP, Node.js) accept a
databaseparameter at connection time, eliminating the need for an explicitUSEstatement. - In scripts and stored procedures, prefer dot notation over relying on an implicit active database to make your code portable and explicit.
What’s Next?
In Chapter 2.3 — Commenting in SQL Scripts, we’ll cover single-line and multi-line comment syntax, why comments are critical for maintainable scripts, and how to use comments to temporarily disable SQL code during development and debugging.