MySQL - Create Database

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