MySQL CREATE DATABASE with Examples

How to Use the MySQL CREATE DATABASE Statement

In this tutorial, you'll learn how to create a new database in MySQL using the CREATE DATABASE statement.

CREATE DATABASE Syntax

To create a database, you use the CREATE DATABASE statement as follows:

CREATE DATABASE [IF NOT EXISTS] database_name

[CHARACTER SET charset_name]

[COLLATE collation_name]

Remarks

• Database_name must be unique within the MySQL server instance.
• If you try to create a database with a name that already exists, MySQL issues an error.
• Charset_name and collation_name are optional. MySQL uses the default character set and collation for the new database, if not specified.
• The IF NOT EXISTS clause is optional. If omitted, when you try to create a database with a name that already exists, MySQL issues an error. However, MySQL terminates a database creation without showing an error, if specified.
• If you want to check the database that you've created, use SHOW CREATE DATABASE database_name.

Examples

Example 1: Creating a database with the default character set and collation

The following statement creates a new database, named mysql_tutorial, with the default character set and collation:

CREATE DATABASE IF NOT EXISTS mysql_tutorial;

You can check the created database using the SHOW CREATE DATABASE command as follow:

SHOW CREATE DATABASE mysql_tutorial;

Example 2: Creating a database with the specified character set and collation

The following statement creates a new database, named mysql_tutorial, with the utf8 character set and utf8_general_ci collation:

CREATE DATABASE IF NOT EXISTS mysql_tutorial
CHARACTER SET utf8
COLLATE utf8_general_ci;

Example 3: Creating a database with the same name

In this example, assume that we've created a database named mysql_tutorial before, and now let's try to create a new database with the same name without specifying the IF NOT EXIST clause.

CREATE DATABASE mysql_tutorial;

Since mysql_tutorial already exists, MySQL issues an error as shown below:

[Err] 1007 - Can't create database 'mysql_tutorial '; database exists

Example 4: Creating a database and a table

The following illustrates an example of creating a new database named hr_database and a table named employees for the hr_database:

CREATE DATABASE IF NOT EXISTS hr_database

CHARACTER SET utf8
COLLATE utf8_general_ci;

USE hr_database;

CREATE TABLE employees(

id INT NOT NULL AUTO_INCREMENT,

employee_name VARCHAR(100) NOT NULL,

PRIMARY KEY(id)

);

To show all tables in the hr_database, use the following statement:

SHOW TABLES IN hr_database;

In this tutorial, you've learned how to create a new database using the CREATE DATABASE statement. If you try to create a database with a name that already exists, MySQL issues an error. To avoid this, you specify the IF NOT EXISTS clause following the CREATE DATABASE clause. If you do this, MySQL terminates the database creation without issuing an error.