MySQL Create Database Tutorial and Examples

In this article, we covers creating a new database using the CREATE DATABASE statement.

A database is a container that includes tables, views, triggers, functions, and stored procedures. To store some data, you must create a table. To create a table, you must create a database first.

In MySQL, the CREATE DATABASE and CREATE SCHEMA statements are used to create a database.

CREATE DATABASE syntax

The CREATE DATABASE statement is used to create a database. The following is the syntax of the CREATE DATABASE statement:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]
[ENCRYPTION {'Y' | 'N'}]

illustrate:

  • CREATE DATABASE is the same as CREATE SCHEMA.
  • After CREATE DATABASE specifying the name of the database to be created.
  • IF NOT EXISTS indicates that it will be created only when the specified database does not exist. It is optional.
  • CHARACTER SET charset_name specifies the character set of the database. It is optional. By default, the configuration of the database server is used.
  • COLLATE collation_name specifies the collation of the database. It is optional. By default, the configuration of the database server is used.
  • ENCRYPTION specifies whether to encrypt the database. It is optional. By default, the configuration of the database server is used.

The rules for MySQL database naming:

  • The database name can be up to 64 characters. The length of the name also depends on the operating system.
  • The database name can consist of letters, numbers, underscores, and dollar sign.

Although the syntax looks complicated, the most commonly used statement is simple as the following:

CREATE DATABASE db_name;

CREATE DATABASE Examples

Below we use the mysql client tool to create a database named newdb. Please follow the steps below:

  1. Log in to the MySQL database as the root user.

    mysql -u root -p

    Enter password of the root user.

    Note: You can also log in as any other user with permissions to create databases.

  2. List all databases in the current server using the following statement: the following statement:

    SHOW DATABASES;

    +-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| performance_schema    |
| sakila                |
| sys                   |
+-----------------------+
5 rows in set (0.01 sec)

  3. Create a database name newdb using the following statement:

    CREATE DATABASE newdb;

    Query OK, 1 row affected (0.02 sec)

    Here 1 row affected indicates that the database has been created successfully.

  4. Verify that the creation was successful by looking at the list of databases in the current server.

    SHOW DATABASES;

    +-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| newdb                 |
| performance_schema    |
| sakila                |
| sys                   |
+-----------------------+
6 rows in set (0.01 sec)

    From the output, we can see that the newdb database.

  5. Finally, we use the newdb database just created.

    USE testdb;

    Database changed

    This shows that we have now switched to the newdb database. Now we can create tables, insert rows, update rows, etc.

View information about creating a database

We can use the SHOW CREATE DATABASE statement to view information about a database.

SHOW CREATE DATABASE newdb;

+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| newdb    | CREATE DATABASE `newdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

From the output we can see that:

  • We created the database using the CREATE DATABASE ``newdb`` command.
  • DEFAULT CHARACTER SET utf8mb4 indicates that the default character set is utf8mb4.
  • COLLATE utf8mb4_0900_ai_ci indicates that the default collation is utf8mb4_0900_ai_ci.
  • DEFAULT ENCRYPTION='N' indicates that encryption is not enabled by default.

Conclusion

In this article, we discussed using the usage of the CREATE DATABASE statement. The main points of this article are as follows:

  • CREATE DATABASE is the same as CREATE SCHEMA.
  • You specify the name of the database to be created after CREATE DATABASE.
  • IF NOT EXISTS indicates that it will be created only when the specified database does not exist. It is optional.
  • The SHOW CREATE DATABASE statement is used to view information about creating a database.

If this database is no longer needed, you can drop the database.