MySQL - Select Database (USE Statement)

Table of content
Previous
Next

Once you get connected with the MySQL server, it is required to select a database to work with. This is because there might be more than one database available with the MySQL Server.

MySQL USE Statement

To select a database in MySQL, we use the SQL USE statement. Once a specific database is selected, we can perform different operations such as creating tables, adding data, updating, and deleting information. Every operation we perform after selecting a database will be stored in that particular database.

Syntax

Following is the syntax of the USE statement in SQL −

USE DatabaseName;

Here, the "DatabaseName" is a placeholder representing the name of the database that we want to use.

The database name must always be unique within the MySQL or any other RDBMS.

Example

Let us start by creating a database named TUTORIALS using the following CREATE query −

create database TUTORIALS;

Now, we will fetch all the databases present in MySQL server using the below query −

Show databases;

Following are the list of databases −

Field
information_schema
mysql
performance_schema
tutorials

The following will select/switch the current database to TUTORIALS −

USE TUTORIALS;

Output

The database has been selected/switched successfully without any error.

Database changed

Once we finish switching to the database TUTORIALS, we can perform operations such as creating a table, and inserting data in that table as shown below −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Once the table is created, execute the following query to retrieve the data present in the current database (TUTORIALS) −

SHOW TABLES;

As we can see in the output below, the CUSTOMERS table we have created after selecting the TUTORIALS database is stored in it.

Tables_in_tutorials
customers

Selecting a Non Existing MySQL Database

If we try to select/switch a non-existent database in a MySQL server, it will result in an error stating that "Unkwown Database".

Example

Here, we are trying to select/swith to the database which doesn't exist −

USE NonExistingDatabase;

The output for the above query is produced as given below −

ERROR 1049 (42000): Unknown database 'nonexistingdatabase'

Selecting Database Using a Client Program

Besides selecting/switching a database in a MySQL server using a MySQL query, we can also use a client program to perform the USE operation.

Syntax

Following are the syntaxes of this operation in various programming languages −

PHP NodeJS Java Python

To select/switch a database in a MySQL server through PHP program, we need to execute the USE statement using the mysqli function query() as follows −

$sql = "USE Database_name";
$mysqli->query($sql);

To select/switch a database in a MySQL server through Node.js program, we need to execute the USE statement using the query() function of the mysql2 library as follows −

sql = "USE Database_name";
con.query(sql);

To select/switch a database in a MySQL server through Java program, we need to execute the USE statement using the JDBC function executeUpdate() as follows −

String sql = "USE Database_name";
st.execute(sql);

To select/switch a database in a MySQL server through Python program, we need to execute the USE statement using the execute() function of the MySQL Connector/Python as follows −

sql = "USE Database_name";
cursorObj.execute(sql)

Example

Following are the programs −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf('Connected successfully.<br />');

if ($mysqli->query("USE TUTORIALS")) {
   printf("Database selected successfully...!<br />");
}
if ($mysqli->errno) {
   printf("Database could not connect: %s<br />", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Connected successfully.
Database selected successfully...!

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Selecting a Database
  sql = "Use TUTORIALS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("Database selected successfully...")
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
Database selected successfully...!

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SelectDatabase {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String password = "password";
		System.out.println("Connecting to select database.....!");
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st1 = con.createStatement();
            String sql = "USE TUTORIALS";
            st1.execute(sql);
            System.out.println("Database selected successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Output

The output obtained is as shown below −

Connecting to select database.....!
Database selected successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
host ="localhost",
user ="root",
password ="password")
# creating cursor object
cursorObj = connection.cursor()
# selecting the database 
cursorObj.execute("USE TUTORIALS")
# Fetching a single row 
print("Database selected Successfully...!")
# disconnecting from server
connection.close()

Output

Following is the output of the above code −

Database selected Successfully...!
Print Page
Previous
Next
Advertisements