Here's how to set a default database for all subsequent queries.

MySQL allows you to set a default database to be used as the context for any subsequent SQL statements. There could be many databases on the server and MySQL needs to know which database you want any SQL statement/s to be run against. Setting a default database is a good start as it saves you from having to specify the database in the query.

You can change the default DB as often as you like. It's not necessarily a "set and forget" thing. Depending on how many databases you're working with, you could be switching the default DB quite frequently.

You can set the default database either programmatically or via the graphical interface.

Using the MySQL Workbench GUI

Right-click on the database name in the SCHEMAS tab (in the left menu):

The default database will now be listed in a bold typeface:

Programmatically

The USE statement tells MySQL to use a particular database as the default when you run subsequent SQL statements.

USE FruitShop;

Now any subsequent queries will be run against the FruitShop DB. Like this:

USE FruitShop;

SELECT * FROM Fruit;

The CREATE DATABASE Script

The USE statement can really come in handy in our scripts that create databases. Once we've created our database, we can set it as the default database by specifying the USE statement against it.

Doing this will ensure that any subsequent SQL statements will use our newly created database as the current DB (unless specified otherwise in the script). This is important because it tells MySQL which DB to create the tables against:

DROP DATABASE IF EXISTS FruitShop;

CREATE DATABASE FruitShop;

USE FruitShop;

CREATE TABLE table_1 (...);

CREATE TABLE table_2 (...);

Switching the Default Database

There's nothing to stop you switching the default database multiple times within your scripts. So if you had two databases, you could do something like this:

USE FruitShop;

SELECT * FROM Fruit;

​

USE VegeShop;

SELECT * FROM Vegetables;

The above query selects all records from the Fruit table in the FruitShop database and all records from the Vegetables table in the VegeShop database. Without the USE statement, MySQL wouldn't know which database to query for each statement.

Of course, this is a simplified example. Often you'll find yourself doing more complex queries against your databases and this technique can really help.

Qualifying the DB Name in the Statement

Regardless of whether you set a default database, you can also specify the database within the statement.

To do this, use the following syntax: database.table.column

Here's an example:

SELECT FruitName, VegeName FROM FruitShop.Fruit, VegeShop.Vegetables

WHERE FruitShop.Fruit.SupplierId = VegeShop.Vegetables.SupplierId ;

Override the Default DB — Specify the Database within a Query

Setting a default database doesn't preclude you from querying other databases. Even after you've set a default database, you can still query other databases without having to set them as a default. You can still specify which database to use within your queries. In fact, you can use multiple databases within a single query using this method.