SQL Create Database
This tutorial will help us understand how to CREATE a Database in SQL with a few examples.
The first step for storing structured records in the database is creating the databases.
We create a database to store data in the database. We also create a database to create tables, indexes, and views into the database.
We use the CREATE DATABASE query to create a database followed by the database name. The database name must be attractive, easy to remember, and, most importantly, unique. The database with the same name cannot be created on the server.
The database user must know which database name already exists in the database server.
Use the SHOW DATABASES query to find out the already residing database name in the SQL Server.
Before creating a new database, let's check which database already present in my SQL Server
SHOW DATABASES;
The output of the above query is as follows:
| Database |
| cricket |
| demo |
| ids |
| information_schema |
| mysql |
| performance_schema |
| phpadmin |
| registration |
| student |
| student_1 |
| WordPress |
Before creating a new database, we must learn about the syntax of the create database query.
The syntax of the CREATE DATABASE is as follows:
CREATE DATABASE Database_Name;
Now, we are ready to create new databases in the SQL Server.
Example 1: Write a query to create a new database named javatpoint
CREATE DATABASE javatpoint;
In the above query, we created a new database named javatpoint.
We will execute the SHOW DATABASES query to check whether the javatpoint database is successfully created or not is as follows:
SHOW DATABASES;
The output of the above query is as follows:
| Database |
| cricket |
| demo |
| ids |
| information_schema |
| javatpoint |
| mysql |
| performance_schema |
| phpadmin |
| registration |
| student |
| student_1 |
| WordPress |
Example 2: Write a query to create a new database named Mumbai
CREATE DATABASE Mumbai;
In the above query, we created a new database named Mumbai.
We will execute the SHOW DATABASES query to check whether the Mumbai database is successfully created or not is as follows:
SHOW DATABASES;
The output of the above query is as follows:
| Database |
| cricket |
| demo |
| demodb |
| ids |
| information_schema |
| javatpoint |
| Mumbai |
| mysql |
| performance_schema |
| phpadmin |
| registration |
| Student |
| student_1 |
| WordPress |
CREATE SCHEMA
CREATE SCHEMA is an alternative option or command to create the database. Both query task is the same to create a database in the SQL Server.
The syntax of the CREATE SCHEMA is as follows:
CREATE SCHEMA Schema_Name;
Example 1: Write a query to create a new schema named testDB
CREATE DATABASE testDB;
In the above query, we created a new database named testDB.
We will execute the SHOW DATABASES query to check whether the testDB database is successfully created or not is as follows:
SHOW DATABASES;
The output of the above query is as follows:
| Database |
| Cricket |
| Demo |
| Demodb |
| Ids |
| information_schema |
| Javatpoint |
| Mumbai |
| mysql |
| performance_schema |
| phpadmin |
| registration |
| student |
| student_1 |
| testdb |
| WordPress |
Example 2: Write a query to create a new schema named Employee
CREATE DATABASE Employee;
In the above query, we created a new database named Employee.
We will execute the SHOW DATABASES query to check whether the Employee database is successfully created or not is as follows:
SHOW DATABASES;
The output of the above query is as follows:
| Database |
| Cricket |
| Demo |
| Demodb |
| Employee |
| Ids |
| information_schema |
| Javatpoint |
| Mumbai |
| mysql |
| performance_schema |
| phpadmin |
| registration |
| student |
| student_1 |
| testdb |
| WordPress |
SHOW DATABASES
The SHOW DATABASES is the one way to see the already existing database. Another way is to SHOW CREATE DATABASE command. But, in the SHOW DATABASES, all the database's name is listed, and in the SHOW CREATE DATABASE query, only the database that wants to look is displayed with the following character set of the database name.
Example 1: Execute a query to show the employee database.
SHOW CREATE DATABASE Employee;
In the above query, we show the employee database with the character set information of the employee database.
The output of the above query is as follows:
| Database | Create Database |
| Employee | CREATE DATABASE `employee` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
Since when we created the database employee, we didn't specify the character set with the database. So, the default character set is displayed in the employee database.
We created a database without adding arguments or parameters in the above examples.
IF NOT EXISTS
IF NOT EXISTS is one of the parameters which is used while creating the database in the SQL Server. Suppose there are several databases in the SQL Server, and we don't know whether the database is creating resides in the SQL Server. The purpose of the IF NOT EXISTS parameter is to check whether the database we are going to create is already residing or not in the SQL Server.
The syntax of the CREATE DATABASE with the IF NOT EXISTS parameter is as follows:
CREATE DATABASE IF NOT EXISTS DataBase_Name;
Example 1: Write a query to create a new database with the IF NOT EXISTS parameter.
CREATE DATABASE IF NOT EXISTS World;
In the above query, we execute the create database query with the IF NOT EXISTS parameter database name World. If the database is already residing on the server, then the warning message will be displayed or will create the database successfully.
The output of the above query is as follows:
| Database |
| Cricket |
| Demo |
| Demodb |
| Employee |
| Ids |
| information_schema |
| Javatpoint |
| Mumbai |
| mysql |
| performance_schema |
| phpadmin |
| registration |
| student |
| student_1 |
| testdb |
| WordPress |
| world |
There was no database named world on the server. So, created the Database name World successfully. Now, in the last example, we will create a database which already resides in the database and check for the result.
Example 2: Write a query to create a new database with the IF NOT EXISTS parameter.
CREATE DATABASE IF NOT EXISTS Employee;
In the above query, we execute the create database query with the IF NOT EXISTS parameter database name Employee. If the database is already residing on the server, a warning message will display and create the database successfully.
The output of the above query is as follows:
As we can see in the output 1 warning is shown, this is because database name employee is already residing in the server.