In this article, we will find out how to start our work with a specific database when our MySQL schema consists of several databases.
We will discover the basics of the USE statement, and also we will learn how to check the names of all our databases in the schema.
MySQL USE statement syntax
The USE statement helps us to choose some needed database out of all we have. It gives us an opportunity to manipulate this database and to perform some operations.
Note: The USE command instructs MySQL to use a database as the default in subsequent queries. The specified database remains in this state until the end of this session or until another USE command is issued.
The syntax of this statement is:
- our_database is the name of the database we want to choose.
Note: The database name must be unique; the names must not be the same within one schema.
The fact that a particular database is selected as default with the USE command is not an obstacle to accessing tables of other databases.
The USE command does not have to end with a semicolon. Of course, it can also be completed with a semicolon, and there will be no harm from this.
The USE command is different from the other commands in a way that it must be specified in a single line.
The list of all databases
There are some cases when we have too many databases in one schema, and we might forget the exact database name spelling or the name itself. There is a way to recall all the available databases.
We can display the list of all databases in MySQL. To do this, we need to start with logging in as a user with permission to access the databases; namely, this is root permission or the user that has the “show databases” permission.
The easiest and the most common way is to use the MySQL client. We need to connect it to the server and then use the necessary command.
The syntax of this command is:
Figure 1. The SHOW DATABASES command example
This command has a synonymous one, its syntax is:
So the result is the same as well:
Figure 2. The SHOW SCHEMAS command example
The list of databases will probably be different from one computer to another, but it will most likely still contain a Test database, which is often used for experiments.
So now, we can easily select the “SportSchema” database to start using it as the default database using the USE command:
Also, now we can select the records from the table which is located within this database. The following example selects from the “user_role” table.
Figure 3. The USE command example
As we can see, we have our table with records in the result set. The “SportSchema” will remain default unless we change it or unless we finish our session.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.