How to fix MySQL Error 1046 No database selected?

MySQL error 1046, which stands for “No Database Selected,” is one of the most common errors that can prevent database operations. It happens when you run a query and don’t specify the target database.

In this article, we will discuss "How to resolve, Error 1046: No database selected". The first and foremost step to resolve any error is, to read the error message carefully. It specifies that we haven't selected any database, which simply means that we have to select a database to implement our queries on (and fix the error). Let's take a look at the image below.

In the image, we can see that, we are trying to SELECT all the columns FROM the table named "employee_data" but we haven't specified from which database we have to select the columns and table. Let's think of an example., Say... There is a school and in a school, we will have many classes like.., Grade-1 will have class-A Grade-2 will have class-A and so on... The above query is like saying Select 5 students from class A but without specifying the grade. So there are two main ways to resolve this...

1. To explicitly select the database by using the "USE" command.
2. To include the database name in our query.

By doing any of these two methods, we can make our query clearer and resolve the error. Let's now proceed with the key concepts and syntax involved in our resolving process.

Resolving MySQL Error 1046: No Database Selected

Before getting started with the syntax let's take a look at the SHOW ERRORS command (image below). This command is used to separate the error message we've received. For example, the message: ERROR 1046: No database selected will be separated into 3 parts after using the SHOW ERRORS command, They are level, code, and message. In the level column, we would know whether it is a warning or an error, In the code column we get the code number of the error, and in the Message column, we get the error message. This is more or less the same as the direct error message but, this command is a bit professional in understanding the error.

Just like the "SHOW ERRORS" command, we have another command: SELECT DATABASE() This command will help us find the default database or currently selected database. It will return NULL when there is no default or currently selected database (image below).

Let us now discuss the syntax of the two different methods to resolve Error 1046.

Syntax for Explicit Selection of Database:

mysql> SHOW DATABASES ;

// will return the databases available or created

mysql> USE databaseName ;

Database changed

mysql>

Upon opening our MySQL we can start with the "SHOW DATABASES" command to know what databases we have. Then we can proceed with the "USE Database_Name" command, where "Database_Name" is the name of the database that we want to work with.

After selecting the database, we can now create tables, insert values, update values, and retrieve data from the database selected using SQL queries.

Syntax for Including Database Name in the Query:

mysql> SELECT *

---> FROM databaseName.tableName ;

This is another way of selecting the required database. We use the dot operator in this just like we would use it for classes and objects in languages like Java. Referencing our school example.., It is like saying SELECT 10 students FROM Grade-1 class A, Where Grade-1 will be our database name and class A will be our table name.

We would use the database name in the FROM clause and follow it up with a dot operator and the table name. No matter the length of the query we can simply add the database name in the FROM clause before specifying the table name.

In the above image, we are currently using the "library_records" database, which we did by explicitly mentioning using the "USE" command. Now, if we want to get the records of the student_info table which belongs to the Student_Database, We can simply use the above syntax of adding the database name in the from clause before specifying the table name. If we use the SHOW DATABASE() command now it will return "library_records" as output. Let's see why, in the upcoming examples.

Examples of Fixing MySQL Error 1046 No Database Selected

Example 1: Explicitly Select the Database using the "USE" Command

In this example, we will look into the "USE" command for explicitly selecting a database to work on... In the first step, we have created a sample database named "Employee_Database". We use the "IF NOT EXISTS" statement inside the CREATE DATABASE command because we ensure that there is no database in the same name and we also avoid ERROR 1007 which will inform us that.., there is a database with the same name, implying database already exists.

Next, we have attempted to create a table inside our DB but we got an ERROR 1046. This is because we haven't selected our database. We are familiar with this by now... right..?

So, we have used the "USE" command. Let's take a simple example for this.., Say.., I am speaking about "how to prepare a fruit juice?" but I did not specify the fruit name so, I am telling my listeners that, USE apple, meaning.., The lines which I speak further are all about the fruit apple. The same goes here... We are specifying the database to be used at the start and all the further queries we write are on the selected database that we have mentioned using the syntax: USE databaseName.

Now, we are proceeding with the creation of a table called "employee_data" " which has id, name, dept, and salary as its columns.

After creating a table we are now inserting values inside the table. We can insert multiple records at once inside the table.

This command is optional but still, it is good to know our currently selected database or the database we are working on right now...

Example 2: Including the Database name in the Query

In this example, we will look into the concept of including the database name in the query itself, instead of explicitly specifying it by using the "USE" command. Upon opening our MySQL, we can start writing our SQL queries without specifying the database name, by adding the database name in the FROM clause. In the image below, we can see that the first query returned an error message of "No database selected". In the subsequent query, we used the concept of including the database name in the query itself, which fixed the ERROR 1046.

In our example, we want to retrieve the data from the "employee_data" table of the "Employee_Database" database. So, we specify the DB name follow it up with a dot operator, and then the required table name.

Example 3: Combining both the Methods

In this example, we will look into a combination of both methods. (This is already highlighted in the "key concepts and syntax section" Kindly refer to the syntax of the second method). Upon opening our MySQL, we have used the "USE library_Records" command, and the database is changed meaning.., all our further queries are on the "library_records" database.

On our next query, we have included the Employee_Database DB and its table to retrieve info about that DB and table. As mentioned earlier, we could simply specify the database name and execute the required query. But, specifying the DB name is essential when trying to retrieve info from other database. Here library_records and Employee_Database or two different databases.

In this example, we have implemented three queries...
1. USE library_records.
2. SELECT query from Employee_Database.employee_data where the salary is greater than 50000 rupees.
3. finally, we have used the SELECT DATABASE() command to know the currently selected database.

In that, we could see the library_records database was returned as an output. This is because the SELECT DATABASE() query considers the the database that is selected, by using the "USE databaseName" command as the database that is currently in use, even though.., we have used the Employee_Database DB in our previous query.

The image below was added with the purpose of understanding the order/sequence of SQL queries.

Since the library_records DB was selected at the start.., we can continue to retrieve data or records from that database without calling or specifying it again. The image below is an example of it...

Conclusion

In conclusion, the best method from the above discussed methods is the first method. Which is the "USE databaseName" method. This is desirable because, once we've decided on which database to work on, we could simply specify it at the start, before executing further queries. For instance, If I decide to work on a "Tournament Management System" database I could specify it at the beginning to avoid any confusions.

This has another advantage.., Say.., we want to work on a library database, instead of specifying the database name in the from clause each time, we could simply start the query with USE library and then proceed without specifying the database name again elsewhere in MySQL.

Anyways, we have two different methods to fix the "Error 1046: No database selected" error. Let's recap the commands we've discussed.
1. SHOW DATABASES;
2. SELECT DATABASE();
3. USE databaseName;
4. From clause: FROM databaseName.tableName;