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
”;
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 −
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 −
$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
”;