MySQL - Create Database

Table of content

MySQL CREATE Database Statement
CREATE Database with IF NOT EXISTS clause
Create Database Using mysqladmin
Creating Database Using a Client Program

Quiz

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 −

Database
information_schema
mysql
performance_schema
tutorials

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 −

PHP NodeJS Java Python

To create a database in MySQL RDBMS through a PHP program, we need to execute the 'CREATE DATABASE' statement using the mysqli function named query() as shown below −

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

To create a database in MySQL RDBMS through a Node.js program, we need to execute the 'CREATE DATABASE' statement using the query() function of the mysql2 library as follows −

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

To create a database in MySQL RDBMS through a Java program, we need to execute the 'CREATE DATABASE' statement using the JDBC function executeUpdate() as follows −

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

To create a database in MySQL RDBMS through a Python program, we need to execute the 'CREATE DATABASE' statement using the execute() function of the MySQL Connector/Python as follows −

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

Output

The output obtained is as follows −

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

Output

The output produced is as follows −

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

Output

The output obtained is as shown below −

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

Output

Following is the output of the above code −

Database Created Successfully

Print Page

Save or share this article