MySQL – Select Database (USE Statement)
Table of content
- MySQL USE Statement
- Selecting MySQL Database
- Selecting a Non Existing MySQL Database
- Selecting Database Using a Client Program
”;
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 −
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 −
$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...!
”;