MySQL CREATE DATABASE Statement

MySQL CREATE DATABASE Statement

The MySQL CREATE DATABASE statement is used to create a new database in MySQL. This statement is essential for setting up a new database environment for storing and managing data.

Syntax

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

The CREATE DATABASE statement has the following components:

  • IF NOT EXISTS: An optional clause that prevents an error if the database already exists.
  • database_name: The name of the database to be created. It must be unique within the MySQL server instance.
  • CHARACTER SET charset_name: An optional clause that specifies the default character set for the database.
  • COLLATE collation_name: An optional clause that specifies the default collation for the database.

Example MySQL CREATE DATABASE Statement and Verification

Let's look at an example of the MySQL CREATE DATABASE statement and how to verify its creation:

Step 1: Creating the Database

CREATE DATABASE mydatabase;

This query creates a new database named mydatabase.

MySQL CREATE DATABASE

Step 2: Verifying Database Creation

To verify that the database has been created, you can list all databases in the MySQL server:

SHOW DATABASES;

This query lists all databases on the MySQL server. You should see mydatabase in the list of databases.

MySQL SHOW DATABASES

Step 3: Using the New Database

Switch to the new database to start using it:

USE mydatabase;

This query sets the context to the newly created database, allowing you to start creating tables and inserting data.

MySQL USE Database

CREATE DATABASE using IF NOT EXISTS clause

To create a database only if it does not already exist, use the IF NOT EXISTS option:

CREATE DATABASE IF NOT EXISTS mydatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

This query creates a new database named mydatabase with the specified character set and collation, and prevents an error if the database already exists.

MySQL CREATE DATABASE IF NOT EXISTS

Conclusion

The MySQL CREATE DATABASE statement is a fundamental tool for creating new databases. Understanding how to use the CREATE DATABASE statement and verifying its creation is essential for effective database setup and management in MySQL.