MariaDB Create Database Statement Explained

Summary: in this tutorial, you will learn how to use the MariaDB create database statement to create a new database.

Introduction to the MariaDB `create database` statement

To create a new database in a MariaDB server, you use the create database statement with the following syntax:

create [or replace] database [if not exists] database_name
Code language: SQL (Structured Query Language) (sql)

[character set = charset_name]

[collate = collation_name];

In this syntax:

First, specify the name of the database that you want to create after the create database keywords. The database name must be unique in the MariaDB server instance. If you create a database that has the same name as an existing database, MariaDB will issue an error.

Second, the optional or replace clause instructs MariaDB to drop the database first if it exists before creating the new database. It is a shortcut for the following statements:

drop database if exists database_name;
create database database_name;
Code language: SQL (Structured Query Language) (sql)

Note that MariaDB has supported the or replace clause since version 10.1.3.

Third, use the if not exist option to conditionally create a database if it does not exist. In other words, if you try to create a database that already exists with the if not exist option, MariaDB will do nothing.

Finally, optionally specify a character set and collation for the new database. A character set defines how and which characters that the database will store to support particular languages. A collation defines the rule for comparing strings, for example, the letter a appears before the letter b, etc.

Notice that you need the create privilege for the database in order to create a new database.

Creating a new database using mysql command-line program

To create a new database via the mysql command-line program, you follow these steps:

First, log in to the MariaDB server using a user account that has the create privilege for the database:

>mysql -u root -p
Enter password: ********
Code language: SQL (Structured Query Language) (sql)

Type the password for the root user and hit the Enter keyboard.

Second, show databases in the current server using the show databases statement:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nation             |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

Third, create a new database name crm:

mysql> create database crm;
Query OK, 1 row affected (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

Fourth, show the databases again:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| crm                |
| information_schema |
| mysql              |
| nation             |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

The database crm is on the list.

After creating the new database, you can select the database as the current database to work with.

In this tutorial, you have learned how to use the MariaDB create database statement to create a new database.

Was this tutorial helpful ?

Introduction to the MariaDB create database statement

Creating a new database using mysql command-line program

Introduction to the MariaDB `create database` statement