MySQL SHOW Databases Statement

To display the list of all databases present in a MySQL server, we need to use the SHOW DATABASES statement. It returns the result in a tabular form with one column. The databases in the result set will be sorted in alphabetical order.

Syntax

Following is the syntax to list all databases in MySQL sever −

SHOW DATABASES [LIKE ''pattern'' | WHERE expr]

Example

First of all, let us create a database with a name TUTORIALS using the following query −

CREATE DATABASE TUTORIALS;

Make sure you have the admin privilege before creating any database. Once a database is created, you can check it in the list of databases by using the following query −

SHOW DATABASES;

Output

The above query displayed all the databases present in the current MySQL server.

MySQL SHOW SCHEMAS Statement

We can also use the SHOW SCHEMAS statement to list out the databases in MySQL.

Syntax

Following is the syntax of the MySQL SHOW SCHEMAS statement −

SHOW SCHEMAS [LIKE ''pattern'' | WHERE expr]

Example

Lets try to verify the list of databases once again you can using the following query −

SHOW SCHEMAS;

Output

The output of the above query will be as shown below −

Showing Databases Using a Client Program

Besides fetching the list of all databases in current MySQL server with a MySQL query, we can also use a client program to perform the SHOW DATABASES operation.

Syntax

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

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

sql= "SHOW {DATABASES | SCHEMAS} LIKE ''pattern'' | WHERE expr]";
con.query(sql);

String sql = "SHOW Database_name";
st.executeQuery(sql);

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

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
if ($result = $mysqli->query("SHOW DATABASES")) {
   printf("Show Database executed successfully..!");
   echo "Database list are: ";
   while($row = mysqli_fetch_array($result)){
       print_r($row);
   }
}
if ($mysqli->errno) {
    printf("Could not find database: %s
", $mysqli->error);
}
$mysqli->close();

Show Database executed successfully..!Database list are:
Array
(
    [0] => bank
    [Database] => bank
)
Array
(
    [0] => company
    [Database] => company
)
Array
(
    [0] => information_schema
    [Database] => information_schema
)
Array
(
    [0] => mydb
    [Database] => mydb
)
Array
(
    [0] => mysql
    [Database] => mysql
)
Array
(
    [0] => performance_schema
    [Database] => performance_schema
)
Array
(
    [0] => sys
    [Database] => sys
)
Array
(
    [0] => testdb
    [Database] => testdb
)
Array
(
    [0] => tutorials
    [Database] => tutorials
)
Array
(
    [0] => usersdb
    [Database] => usersdb
)

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("--------------------------");

//Creating a Database
sql = "create database testDB1"
con.query(sql);
sql = "create database testDB2"
con.query(sql);
sql = "create database testDB3"
con.query(sql);
sql = "create database testDB4"
con.query(sql);

//Displaying Databases
sql = "show databases;"
con.query(sql, function(err, result){
  if (err) throw err
  console.log(result)
});
});

Connected!
--------------------------
[
  { Database: ''customers'' },
  { Database: ''information_schema'' },
  { Database: ''mysql'' },
  { Database: ''performance_schema'' },
  { Database: ''testdb1'' },
  { Database: ''testdb2'' },
  { Database: ''testdb3'' },
  { Database: ''testdb4'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowDatabase {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st1 = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SHOW DATABASES";
            rs = st1.executeQuery(sql);
            System.out.println("Show query executed successfully...!");
            System.out.println("Databases are: ");
            while(rs.next()) {
            	String db = rs.getNString(1);
            	System.out.println(db);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Show query executed successfully...!
Databases are:
bank
company
information_schema
mydb
mysql
performance_schema
sys
testdb
tutorials
usersdb

import mysql.connector
# Creating the connection object
connection = mysql.connector.connect(
host ="localhost",
user ="root",
password ="password"
)
# Creating cursor object
cursorObj = connection.cursor()
# Show databases
cursorObj.execute("SHOW DATABASES")
# Fetch all the databases
databases = cursorObj.fetchall()
# Printing the list of databases
print("The list of databases are: ")
for database in databases:
    print(database[0])
# Disconnecting from server
connection.close()

The list of databases are:
information_schema
mysql
mysqlpythondb
performance_schema
sqlserver
stdi
sys
textx
tut
tutorials

As we learned in the previous tutorial about ”Database Export”, now we”ll learn how to import the exported data, or backup, into an existing MySQL database. This process is known as database import.

In MySQL, to import an existing dump or backup file into a database, we use the mysql command-line tool.

Importing Backup Data

We can import the backup data into an MySQL database using the mysql command-line tool. It takes the username, database name, and the backup file with the data.

Syntax

Following is the syntax of mysql command-line tool −

$ mysql -u username -p new_database_name < dumpfile_path

Where,

• username: This is the MySQL username to use when connecting to the MySQL server.

• new_database_name: The name of the database where you want to import the data.

• dumpfile_path: It is the path of the backup file. The data will be imported from this file.

• <: This symbol imports the data from the file named output_file_path.

Example

In this example, we will import the file named “data-dump.sql” that was generated in the previous tutorial (Database Export). The file contains a table named ”CUSTOMERS”.

Before doing that, let us login to MySQL server as a user to create a new databases −

$ mysql -u root -p

After logging in, it will bring you into MySQL command-line. Now, create a new database named testdb using the below query −

CREATE DATABASE testdb;

When we execute the above query, the output is obtained as follows −

Query OK, 1 row affected (0.01 sec)

To exit from the MySQL command-line, execute q. Now, from the normal command line, we can import the dump file ”data-dump.sql” using the following query.

Once we execute the below statement, we need to enter our MySQL server password.

$ mysql -u root -p testdb < data-dump.sql

If the above command is runs successfully, it won”t show any visible output. Instead, it imports the data. If any error occur during the execution, MySQL will display them to the terminal.

Verification

To verify whether the import was successful, execute the following query to login into MySQL command-line −

$ mysql -u root -p

Now, select the current database to ”testdb” using the following MySQL ”Use” query −

Use testdb;

Execute the following query to check whether the table named CUSTOMERS in “data-dump.sql” file has been imported or not −

Show Tables;

As we can see the output below, the CUSTOMERS table has been succesfully imported into the new database ”testdb”.

Let us also verify whether the records has been imported or not by executing the below query −

select * from customers;

The records are also successfully imported.

MySQL DROP Database Statement

The DROP DATABASE statement in MySQL is used to delete a database along with all the data such as tables, views, indexes, stored procedures, and constraints.

While deleting an existing database −

• It is important to make sure that we have to perform the backup of the database that we are going to delete because once the “DROP DATABASE” statement is executed, all the data and database objects in the database will be permanently deleted and cannot be recovered.
• It is also important to ensure that no other user or application is currently connected to the database that we want to delete. If we try to delete the database while others users are connected to it, then it can cause data corruption or other issues.

In addition to these we need to make sure we have the necessary privileges before deleting any database using the DROP DATABASE statement.

Syntax

Following is the syntax to delete a database in MySQL −

DROP DATABASE DatabaseName;

Here, the “DatabaseName” is the name of the database that we want to delete.

Example

First of all, let us create a database named TUTORIALS into database system using the following query −

CREATE DATABASE TUTORIALS;

Once the database is created, execute the following query to verify whether it is created or not −

SHOW DATABASES;

As we can see the list of databases below, the TUTORIALS database has been created successfully −

Now, let us delete the existing database <TUTORIALS> using the following DROP DATABASE statement −

DROP DATABASE TUTORIALS;

Output

On executing the given query, the output is displayed as follows −

Query OK, 0 rows affected (0.01 sec)

Verification

Once we have deleted the TUTORIALS database, we can verify whether it is deleted or not using the following query −

SHOW DATABASES;

As we can in the output, the database has been deleted successfully.

Dropping a Database using mysqladmin

You would need special privileges to create or to delete a MySQL database. So, assuming you have access to the root user, you can create any database using the mysql mysqladmin binary.

Note: Be careful while deleting any database because you will lose your all the data available in your database.

Example

Here is an example to delete a database(TUTORIALS) created in the previous chapter −

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******

This will give you a warning and it will ask you to confirm (Y/N) that you really want to delete this database or not. If you enter ”y”, the database will be deleted, else no −

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the ''TUTORIALS'' database [y/N] y

Output

The TUTORIALS database has been deleted successfully.

Database "TUTORIALS" dropped

Dropping Database Using a Client Program

Besides using MySQL queries to perform the DROP DATABASE operation, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

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

$sql = "DROP DATABASE DatabaseName;";
$mysqli->query($sql);

sql = "DROP DATABASE DatabaseName;";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "DROP DATABASE DatabaseName;";
st.execute(sql);

sql = "DROP DATABASE DatabaseName;"
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("Drop DATABASE TUTORIALS")) {
   printf("Database dropped successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not drop database: %s<br />", $mysqli->error);
}
$mysqli->close();

Connected successfully.
Database dropped 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("--------------------------");
    //Creating a Database
    sql = "DROP DATABASE TUTORIALS"
    con.query(sql, function(err){
      if (err) throw err
      console.log("Database Dropped successfully...!")
    });
});

Connected!
--------------------------
Database Dropped successfully...!

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

Database dropped successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
host ="localhost",
user ="root",
password ="password"
)
# creating cursor object
cursorObj = connection.cursor()
# dropping the database
cursorObj.execute("DROP DATABASE MySqlPython")
print("Database dropped Successfully")
# disconnecting from server
connection.close()

Database dropped Successfully

The MySQL workbench is a graphical tool for working with MySQL servers and databases. It is developed and maintained by Oracle. This application includes various features such as data modelling, data migration, SQL development, server administration, database backup, database recovery and many more. MySQL Workbench supports the MySQL versions 5.7 and above.

The versions of MySQL prior to version 5.7 are deprecated and they are incompatible with MySQL workbench. Therefore, we need to ensure that they should be upgraded before we make a connection.

MySQL Workbench is a recommended application for database developers and administrators. We can download this application on Windows, macOS, and Linux operating systems.

Functionalities of MySQL Workbench

Following are the five main functionalities of MySQL workbench −

• SQL Development − This functionality allows to create and manage connections to database servers. SQL queries can be executed on the database connections using the built-in SQL editor in MySQL workbench.

• Data Modelling (Design) − This functionality allows to create models of our database schema graphically, reverse and forward engineer between a schema and a live database, and edits all aspects of the database using the comprehensive table. The table editor provides facilities to edit Tables, Columns, Indexes, Triggers, Partitioning, Options, Inserts, Privileges, Routines and Views.

• Server Administration − It allows us to administer the MySQL sever instances by administering users, viewing database health, performing backup and recovery, inspecting audit data, and monitoring the MySQL server performance.

• Data Migration − It allows us to migrate from the Microsoft SQL server, Microsoft Access, Sybase ASE, SQLite, SQL Anywhere PostreSQL, and other RDBMS tables, objects and data to MySQL. Migration also allows us to migrate from prior versions of MySQL to the latest releases.

• MySQL Enterprise support − This functionality provides the support for Enterprise products such as MySQL Enterprise backup, MySQL Firewall and MySQL Audit.

MySQL Workbench Editions

The MySQL Workbench is offered in three editions. The same is described below −

• MySQL Workbench Community Edition – Open Source (GPL License)

• MySQL Workbench Standard Edition – Commercial

• MySQL Workbench Enterprise Edition – Commercial

Community Edition

This is an open-source and freely downloadable version of the database system. It comes under the GPL (General public license) and it is supported by a large community of developers.

Standard Edition

This is the commercial edition that gives the capability to deliver high-performance and scalable OLT (online transaction processing) applications.

Enterprise Edition

The Enterprise edition includes a set of advanced features, management tools, and technical support to achieve the highest scalability, security, uptime and reliability. This edition will reduce the risk, cost, complexity in the development, deployment, and managing MySQL applications.

Comparison Chart

The following is the comparison chart of the above discussed functionalities −

MySQL Workbench Administration Tool

The administration tool in MySQL workbench plays a vital role in securing the data. Following are some administration tools provided by MySQL workbench −

User Administration

This tool allows to create, modify and delete the user related accounts. Using this we can manage the user”s privileges and permissions.

Server Configuration

It allows us to configure the server paramerters. It shows various details about the sever and status variable, number of threads, buffer allocation size, fine-tuning for optimal performance, etc.

Database Backup and Restorations

This tools is used for importing and exporting the MySQL dump files. These dump files contains the SQL script for table creation, view creation, stored procedure creation etc.

Server Logs

This tool shows log information for the MySQL server by each connection tab. For every tab connection, it includes an additional tab for the general error logs.

Performance Dashboard

This tab provides real-time statistical view and analysis of server performance such as CPU usage, memory usage, query execution time, etc.

Create Database in MySQL Workbench

We can create a database in MySQL Workbench, without explicitly using SQL statements. Following steps show how to create a database in MySQl Workbench −

Step 1 − Open the MySQL workbench application and log in using the username and password.

Step 2 − Now, to create a database, right click on schemas menu and select the create schema option. Another way to create a schema is by clicking on the schema button which is highlighted with a red outline in the following picture.

Step 3 − After selecting the create schema option, a new schema window will be opened as shown in the following figure. We can now enter the database name (say testdb) and use the default collation. Then click on the apply button.

Step 4 − A new window will be opened after clicking the apply button. Now click on the Apply button and then click on Finish button.

Step 5 − Now, we can see the above-created testdb database in the schema menu. If it is not visible, click on the refresh button on top-right corner in schemas menu.

Note − If we want to see more information about the testdb database, click on the testdb database and then click on the i icon. The information window displays various options such as Table, Column, Indexes, Triggers and many more.

Drop Database in MySQL Workbench

Similarly, we can also drop a database using workbench. Following are the steps to drop a database using MySQL workbench −

Step 1 − To drop a database in MySQL workbench, right click on the particular database that we want to delete and click on drop schema option as shown in the following figure.

Here, we are trying to delete the previously created database, testdb.

Step 2 − Now, a new window will be opened and click on the drop now option as shown in the below picture.

MySQL Workbench Create, Alter, and Drop Table

We can create, alter, and drop a table using the MySQL workbench application. Let us look into them in a step by step process.

Create Table

Following are the steps to create a table using MySQL workbench −

Step 1 − Open MySQL workbench, login with the username and password. Then, click on the schemas menu on left-corner of the window. Here, we can find all the databases which exist in the MySQL server.

Step 2 − Now, double click on the previously created testdb database, and we can see sub-menu under the database such as Tables, Views, Functions and Stored procedures as shown in the picture below.

Step 3 − Now, right click on the Tables and click on create table option. Else, we can create a table by clicking the table button which is highlighted with a red outline in the above picture.

Step 4 − After clicking the create table option, the following will be displayed. Then we need to enter the name of the table (say employees) and use the default collation and engine.

Step 5 − Now, click on the table fields and enter the column names. We can also select attributes to the columns such as Primary Key (PK), Not Null (NN), Unique Key (UQ) and so on. After providing all the details click on the apply button.

Step 6 − After we click the apply button, an SQL statement window will be opened and then we need to click on apply button and finish button to save the changes.

Step 7 − Now, go back to the schema menu and select the database that contains the newly created employees table. There we can find all the data that we provided to the table.

Alter Table

Following are the steps to alter a table using MySQL workbench −

Step 1 − Select the table which we want to modify and click on the i icon. Here, we are modifying the previously created employees table.

Step 2 − After clicking on the i icon, the following will be displayed where we can find the options to modify the table”s columns, indexes and other datatypes etc. After modifying, click on the analyze table button to save the changes.

Drop Table

Following are the steps to drop a table using MySQL workbench −

Step 1 − To drop a table, we need to select the particular table which we want to delete. Then, right click on it and select the drop table option. Here, we are deleting the employees table.

Step 2 − A new window will be opened and click on drop now option to remove the table from the database.

MySQL Workbench Insert and Delete Table Rows

Let”s have a look on how to inset and delete table rows using the MySQL Workbench application.

Insert Rows

Following are the steps to insert rows into a table using MySQL workbench −

Step 1 − Open MySQL workbench. Then click on schemas menu on left corner of the window. Here, we can see that our previously created database (testdb) and table (employees).

Step 2 − First double click on testdb database, then double click on tables. Now, if we hover the mouse on the employees table, a table icon will appear. Click on that table icon.

Step 3 − Now, click on the respected column or row to insert the values.

Step 4 − After inserting the values, click on the apply button. Then a new SQL statement window will be opened, click on apply and finish buttons to save the records. If we want to modify the records, we can follow the same procedure as discussed.

Delete Rows

Following are the steps to delete rows into a table using MySQL workbench −

Step 1 − To delete an individual row from the table, we can simply right click on that particular row and click on the Delete row(s) option as shown below. Here, we have deleted the first row (i.e ID = 1).

Step 2 − After selecting the delete row(s) option a new window will be opened. Then, click on apply and finish buttons to save the changes. In the following picture, we can see that the row1 (i.e. ID =1) got deleted.

MySQL Workbench Export and Import Database

Let”s have a look on how to export and import database using the MySQL Workbench application.

Export Database

Following are the steps to export a database using MySQL workbench −

Step 1 − To export a database, go to menu bar at the top of the window and click on the Server option. After clicking that, select the data export option.

Step 2 − A new data export window will be opened. Now, select the database (testdb), it will display all the corresponding tables exist in that.

Step 3 − Now, click on the drop-down setting, there we will be having three options such as Dump Structure and Data, Dump Data Only, and Dump Structure Only.

• Dump Structure and Data − This option will export both the table structure and data records.

• Dump Data Only − This will export only the records in the table.

• Dump Structure Only − This will export only the table structure, which are columns and datatypes defined by us.

Step 4 − Select the Dump Structure and Data option. Then in the Export options, we can see two options to select the export path as follows −

• Export to Dump Project Folder − This option will export all the tables as separate SQL files under one folder. It is recommended when we import the exported file one by one.

• Export to Self-Contained File − This options will export all the databases and tables in a single SQL file. It is recommended when we import all the databases, tables, and data rows using a single SQL file.

Step 5 − Select the “Export to Dump Project Folder” option and click on the start export button. Then it displays a process bar as shown in the figure below.

Step 6 − Now, we can find the exported file in the provided path while exporting.

Import Database

Following are the steps to import a database using MySQL workbench −

Step 1 − To export a database, go to menu bar at the top of the window and click on the Server option. After clicking that, select the data import option.

Step 2 − A new data import window will be opened. Here, we can find two options such as “Import from Dump Project Folder” and “Import from Self-Contained File”.

Step 3 − Now, we are going to select the “Import from Dump Project Folder” option and click on the “Load Folder Contents” to show all the available databases in the project folder.

Step 4 − Now, select the testdb database from the Data import option and select the respective employees table.

Step 5 − Select the “Dump Structure and Data” option and click on the “start import” button to import the database from the exported file.

Step 6 − Now, go the schema menu and click on the employees and again click on the table to see the imported table.

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 −

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.

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 −

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

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

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

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();

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...")
  });
});

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();
		}
	}
}

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()

Database selected Successfully...!

After establishing connection with MySQL, to manipulate data in it you need to connect to a database. You can connect to an existing database or, create your own.

You would need special privileges to create or to delete a MySQL database. So, if you have access to the root user, you can create any database using the MySQL CREATE DATABASE statement.

MySQL CREATE Database Statement

The CREATE DATABASE statement is a DDL (Data Definition Language) statement used to create a new database in MySQL RDBMS.

If you are creating your database on Linux or Unix, then database names are case-sensitive, even though keywords SQL are case-insensitive. If you are working on Windows then this restriction does not apply.

Syntax

Following is the syntax to create a database in MySQL −

CREATE DATABASE DatabaseName;

Where, the “DatabaseName” is just a placeholder representing the name of the database that we want to create.

Example

Let us create a database TUTORIALS in MySQl using the CREATE DATABASE statement as follows −

CREATE DATABASE TUTORIALS;

Make sure you have the necessary privilege before creating any database.

Verification

Once the database TUTORIALS is created, we can check it in the list of databases using the SHOW statement as shown below −

SHOW DATABASES;

Following are the list of databases present in the server −

CREATE Database with IF NOT EXISTS clause

If you try to create a database with an existing name an error will be generated. Suppose there is an existing database in MySQL with the name mydb and if we try to create another database with the same name as −

CREATE DATABASE myDatabase

An error will be generated as shown below −

ERROR 1007 (HY000): Can''t create database ''mydb database exists

If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new database will be created and if a database with the given name, already exists the query will be ignored.

CREATE DATABASE IF NOT EXISTS myDatabase

Create Database Using mysqladmin

You would need special privileges to create or to delete a MySQL database. So assuming you have access to the root user, you can create any database using the mysql mysqladmin binary.

Example

Here is a simple example to create a database named TUTORIALS using mysqladmin −

[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******

This will create a MySQL database called TUTORIALS.

Creating Database Using a Client Program

Besides creating a database in MySQL RDBMS with a MySQL query, you can also use a client program in programming languages such as Node.js, PHP, Java, and Python to achieve the same result.

Syntax

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

$sql = "CREATE DATABASE DatabaseName";
$mysqli->query($sql);

sql = "CREATE DATABASE DatabaseName";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "CREATE DATABASE DatabaseName";
st.executeUpdate(sql);

sql = "CREATE DATABASE DatabaseName"
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("CREATE DATABASE TUTORIALS")) {
   printf("Database created successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not create database: %s<br />", $mysqli->error);
}
$mysqli->close();

Connected successfully.
Database created 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("--------------------------");
  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql, function(err){
   if (err) throw err
     console.log("Database created successfully...")
   });
});

Connected!
--------------------------
Database created successfully...

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class createDatabase {
    public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
        	Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Connected successfully...!");
            String sql = "CREATE DATABASE TUTORIALS";
            st.execute(sql);
            System.out.println("Database created successfully...!");
        }catch(Exception e) {
        	e.printStackTrace();
        }
	}
}

Database created successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
host ="localhost",
user ="root",
password ="password"
)
# creating cursor object
cursorObj = connection.cursor()
# creating the database
cursorObj.execute("CREATE DATABASE MySqlPythonDB")
print("Database Created Successfully")
# disconnecting from server
connection.close()

Database Created Successfully

While working with MySQL database, we use a client program to communicate with the database server. To do so, we must first establish a connection between them.

To connect a client program with MySQL server, we must ensure all the connection parameters are properly used. These parameters work just like any other login parameters: consisting of a username and a password. Where, a username is the name of the host where the server is running and a password needs to be set according to the user.

Generally, each connection parameter holds a default value, but we can override them either on the command line or in an option file.

This tutorial only uses the mysql client program to demonstrate the connection, but these principles also apply to other clients such as mysqldump, mysqladmin, or mysqlshow.

Set Password to MySQL Root

Usually, during the installation of MySQL server, we will be asked to set an initial password to the root. Other than that, we can also set the initial password using the following command −

mysql -u root password "new_password";

Where, new_password is the password set initially.

Reset Password

We can also change the existing password using the SET PASSWORD statement. However, we can only do so after logging in to the user account using the existing password. Look at the query below −

SET PASSWORD FOR ''root''@''localhost'' = PASSWORD(''password_name'');
FLUSH PRIVILEGES;

Every time a connection is needed to be established, this password must be entered.

MySQL Connection Using MySQL Binary

We can establish the MySQL database using the mysql binary at the command prompt.

Example

Here is a simple example to connect to the MySQL server from the command prompt −

[root@host]# mysql -u root -p
Enter password:******

This will give us the ”mysql>” command prompt where we will be able to execute any SQL query. Following is the result of above command −

The following code block shows the result of above code −

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2854760 to server version: 5.0.9

Type ''help;'' or ''h'' for help. Type ''c'' to clear the buffer.

In the above example, we have used root as a user but we can use any other user as well. Any user will be able to perform all the SQL operations, which are allowed to that user.

We can disconnect from the MySQL database any time using the exit command at mysql> prompt.

mysql> exit
Bye

MySQL Connection Using PHP Script

We can open/establish connection to MySQL database using the PHP mysqli() constructor or, mysqli_connect() function. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure.

Syntax

Following is the syntax to open a MySQL connection using the constructor mysqli() −

$mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket);

Parameters

Following are its parameters −

Closing the Connection

We can disconnect from the MySQL database anytime using another PHP function close(). Following is the syntax −

$mysqli->close();

Example

Try the following example to connect to a MySQL server. Save the file as mysql_example.php −

<html>
   <head>
      <title>Connecting MySQL Server</title>
   </head>
   <body>
      <?php
         $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 />'');
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.

The MySQL-Python connector specifically refers to a library in Python that enables communication between a Python program and a MySQL database. It acts as a bridge, allowing Python programs to interact with and manipulate data stored in a MySQL database. Essentially, the MySQL-Python connector simplifies the process of connecting, querying, and managing databases, enabling developers to seamlessly integrate their Python applications with MySQL databases.

Installing “python-mysql” connector

To use MySQL with Python, you typically need to install a MySQL connector or library. Here are the general steps to install it −

Step 1: Install MySQL Server

Make sure you have MySQL Server installed on your machine or have access to a remote MySQL server.

Step 2: Install MySQL Connector for Python

Open a command prompt or terminal and use the following command to install the MySQL Connector for Python using pip, which is the package installer for Python:

pip install mysql-connector-python

If you are using Python 3, you might need to use ”pip3” instead of ”pip”.

Step 3: Verify Installation

After the installation is complete, you can verify that the library is installed by opening a Python interactive shell and trying to import the connector:

import mysql.connector

Python Functions to Access MySQL

When working with MySQL in Python, the ”mysql-connector-python” library provides various functions to interact with a MySQL database. Here are some important functions commonly used −

Basic Example

To connect and communicate with a MySQL database using Python, you can follow these steps −

• Use ”pip install mysql-connector-python” to install the MySQL Connector for Python.
• Import the MySQL Connector module in your Python script: “import mysql.connector”.
• Create a connection using “mysql.connector.connect()” with your database details.
• Create a cursor using “connection.cursor()”.
• Use the cursor”s “execute()” method to run SQL queries.
• If applicable, use “fetchone()” or “fetchall()” to retrieve query results.
• If you modify data, commit changes using “connection.commit()”.
• Close the cursor and connection with “cursor.close()” and “connection.close()”.

The following example shows a generic syntax of a Python program to call any MySQL query −

import mysql.connector
# Establish connection
connection = mysql.connector.connect(host=''localhost'', user=''user'', password=''pass'', database=''db'')
# Create cursor
cursor = connection.cursor()
# Execute query
cursor.execute("SELECT * FROM table")
# Fetch and print results
rows = cursor.fetchall()
print(rows)
# Close cursor and connection
cursor.close()
connection.close()

MySQL Server is the program that mainly executes the SQL queries in the database system. Hence it becomes necessary to optimize the working of this server. The general MySQL administration usually includes concepts like:

• Starting and Stopping the Server

• User Security

• Database Maintenance

• Backup & Restore

Start MySQL Server

We need to first start the MySQL server on the device in order to use it. One way to do so, is by executing the following command on the command prompt (run as an administrator) −

mysqld

We can also start the server by going through the services provided by the Windows and follow the steps below −

• Open the ”Run” Window using the ”Windows+R” shortcut and run ”services.msc” through it.



• Then, select the “MySQL80” service click “start” to start the server.

Stop, Pause, Restart MySQL Server

Now, if you want to pause, stop or restart an already running MySQL server, then you can do it by opening the Windows Services and selecting the desired action −

To stop the MySQL Server, select the ”stop” option as shown in the image below −

To pause the MySQL Server, select the ”pause” option as shown in the image below −

We can also restart the MySQL server as needed, by selecting the ”restart” option as shown below −

Setting Up a MySQL User Account

For adding a new user to MySQL, you just need to add a new entry to the user table in the database mysql.

In the following example, we are creating a new user guest with the password guest123 on the ”localhost”. We are also granting all privileges required to executing SQL queries −

CREATE USER ''guest''@''localhost'' IDENTIFIED BY ''guest123

Now, execute the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don”t use it, then you won”t be able to connect to MySQL using the new user account at least until the server is rebooted.

FLUSH PRIVILEGES;

Finally, you need to grant all privileges to this new user to execute SQL queries.

GRANT ALL PRIVILEGES ON * . * TO ''sample''@''localhost

You can also specify other privileges to a new user by setting the values of following columns in ”user” table to ”Y” using the UPDATE query.

• Select_priv
• Insert_priv
• Update_priv
• Delete_priv
• Create_priv
• Drop_priv
• Reload_priv
• Shutdown_priv
• Process_priv
• File_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv

NOTE − MySQL does not terminate a command until you give a semi colon (;) at the end of the SQL command.

The /etc/my.cnf File Configuration

In most of the cases, you should not touch this file. By default, it will have the following entries −

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

[mysql.server]
user = mysql
basedir = /var/lib

[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

Here, you can specify a different directory for the error log, otherwise you should not change any entry in this table.

Administrative MySQL Commands

Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database −

• USE database_name − This will be used to select a database in the MySQL.

• SHOW DATABASES − Lists out the databases that are accessible by the MySQL DBMS.

• SHOW TABLES − Displays the list of the tables in the current database.

• SHOW COLUMNS FROM table_name: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.

• SHOW INDEX FROM table_name − Presents the details of all indexes on the table, including the PRIMARY KEY.

• SHOW TABLE STATUS LIKE table_nameG − Reports details of the MySQL DBMS performance and statistics.

To communicate with databases Java provides a library known as JDBC (Java Database Connectivity). JDBC provides a set of classes and methods specifically designed for database connectivity, enabling Java developers to perform tasks such as establishing connections, executing queries, and managing data in MySQL databases.

JDBC Installation

To use MySQL with Java, you need to use a JDBC (Java Database Connectivity) driver to connect your Java application to a MySQL database. Below are the general steps for installing and using the MySQL Connector/J, which is the official MySQL JDBC driver for Java −

Step 1: Download MySQL Connector/J

Visit the official MySQL Connector/J download page: MySQL Connector/J Downloads.

Step 2: Select the Appropriate Version

Choose the appropriate version based on your MySQL server version and Java version. Download the ZIP or TAR archive containing the JDBC driver.

Step 3: Extract the Archive

Extract the contents of the downloaded archive to a location on your computer.

Step 4: Add Connector/J to Your Java Project

In your Java project, add the Connector/J JAR file to your classpath. You can do this in your IDE or by manually copying the JAR file into your project.

Step 5: Connect to MySQL Database in Java

In your Java code, use the JDBC API to connect to the MySQL database.

Java Functions to Access MySQL

Following are the major functions involved in accessing MySQL from Java −

Basic Example

To connect and communicate with a MySQL database using Java, you can follow these steps −

• Load the JDBC driver specific to your database.
• Create a connection to the database using “DriverManager.getConnection()”.
• Create a “Statement” or “PreparedStatement” for executing SQL queries.
• Use “executeQuery()” for SELECT queries, or “executeUpdate()” for other statements.
• Iterate through the “ResultSet” to process the retrieved data.
• Close “ResultSet”, “Statement”, and “Connection” to release resources.
• Wrap database code in try-catch blocks to handle exceptions.
• Use transactions if performing multiple operations as a single unit.

The following example shows a generic syntax of a Java program to call any MySQL query −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseInteractionExample {

    public static void main(String[] args) {
        try {
            // Load JDBC Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Connect to Database
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "your_username", "your_password");

            // Execute Query
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("Your SQL Query");

            // Process Results
            while (resultSet.next()) {
                // Process data
            }

            // Close Resources
            resultSet.close();
            statement.close();
            connection.close();

            // Handle Exceptions
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}