SQL CREATE DATABASE Statement

---

---

A database is like a container that stores all the important elements of your project suh as tables, data, views, stored procedures, etc. This is usually the first step when you start with a new project or application that needs organised data storage.

For example, if you are developing a website or mobile app that manages user accounts, orders, messages, or any kind of information, you need a dedicated space to store and organize that data. That space is the database.

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement in SQL is used to create a new database in your database management system (DBMS).

It is part of SQL's Data Definition Language (DDL), which is used to define and manage database objects like tables, databases, indexes, and more.

Syntax

Following is the syntax to create a database in SQL:

CREATE DATABASE database_name;

Here:

• CREATE DATABASE: It is the SQL command to create a new database.
• database_name: It is the name you want to give to your database. It should follow proper naming rules.

Example

Following is an example to create a database testDB using SQL CREATE DATABASE statement:

CREATE DATABASE testDB;

After executing the above command in MySQL database, we get the following output:

Query OK, 1 row affected (0.02 sec)

How to Verify the Database Was Created

You can check if the database was created by listing all existing databases using the SHOW DATABASES command as shown below:

SHOW DATABASES;

After executing the above command on MySQL server, if you see testDB in the list, then your database has been created successfully:

Since testdb is listed, it confirms that the database has been successfully created.

How to Select and Use a Database

Before you can create tables or insert data into a database, you must tell the system which database to use. This is important because a database server (like MySQL or SQL Server) can host multiple databases at once. If you don't select a specific one, your commands may not know where to apply changes

Syntax

Following is the basic syntax to select and use a database:

USE database_name;

Example

In the following command, we select the database named testDB and set it as the active database for the current session:

USE testDB;

We get to see the output as shown below:

Database changed

Removing a Created Database

You may need to delete (remove) a database if you created it by mistake, don't need it anymore, or want to make a new one from the beginning. To do this, you can use the DROP DATABASE statement, which permanently deletes the database and all its contents.

Syntax

Following is the basic syntax to remove a database that was accidentally created:

DROP DATABASE database_name;

Example

In the following command, we delete the database named testDB:

DROP DATABASE testDB;

Following is the output obtained:

Query OK, 0 rows affected (0.04 sec)

SQL CREATE DATABASE with IF NOT EXISTS Clause

If you try to create a database that already exists, SQL will return an error. To avoid this, you can use the IF NOT EXISTS clause with the CREATE DATABASE statement.

This clause tells SQL to create the database only if it doesn't already exist, helping you avoid errors during execution.

Syntax

Following is the syntax to create a database only if it does not already exist, using the IF NOT EXISTS clause in SQL:

CREATE DATABASE IF NOT EXISTS database_name;

Example

The following command creates a database named testDB only if it doesn't already exist:

CREATE DATABASE IF NOT EXISTS testDB;

Following is the output of the above code:

Query OK, 1 row affected, 1 warning (0.00 sec)

Rules for Naming a Database

When naming your database in SQL, it is important to follow standard rules to avoid errors:

• Use only letters, numbers, and underscores (_).
• Don't use spaces or special characters like @, #, %, etc.
• Stick to lowercase or snake_case format (e.g., school_system).
• Make sure the database name is unique in your system.

Example: Valid Database Names

Following are some examples of names that follow the proper rules:

• CustomerDB
• employee_data
• inventory2025

Example: Invalid Database Names

Following are some examples of names that break the naming rules and should be avoided:

• 123database (starts with a number)
• user data (contains a space)
• db@info (contains a special character)