MariaDB - Create a database

MariaDB - Create a database

When we need to create a database, we can use the CREATE DATABASE command in MariaDB. It creates a database with the provided name. There are some default databases like MySQL and test. You need the CREATE privilege to employ the CREATE DATABASE statement. CREATE SCHEME is equivalent to CREATE DATABASE.

Syntax:

CREATE {DATABASE | SCHEME} database_name;

Let's see an example:

Initially, we will see what the database which MariaDB provides by default are

 MariaDB [(none)]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | test               |
 +--------------------+ 
MariaDB - Create a database

Here we can see default databases are information_schema, test, performance schema, and mysql.

Next, we will create a database named as University.

 MariaDB [Student]> CREATE DATABASE University;
 Query OK, 1 row affected (0.001 sec)
 MariaDB [Student]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 6 rows in set (0.001 sec) 

As we can see using the SHOW DATABASES command, the database University is added to the list of databases.

We can also use the word - SCHEMA instead of DATABASE in creating command

MariaDB - Create a database

We can also use the word - SCHEMA instead of DATABASE in creating command.

For example:

 MariaDB [Student]> CREATE SCHEMA Db1;
 Query OK, 1 row affected (0.001 sec)
 MariaDB [Student]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | db1                |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 7 rows in set (0.001 sec) 

OR REPLACE:

For the existing database, the OR REPLACE clause can also be used. This clause was added in MariaDB 10.1.3. This clause is applicable when we are ambiguous about whether the database exists or not. Even if the database exists, MariaDB won't return an error.

Syntax:

CREATE OR REPLACE {DATABASE | SCHEME} database_name;

For example:

 MariaDB [Student]> Create OR REPLACE Database Student;
 Query OK, 2 rows affected (0.003 sec)
 MariaDB [Student]> SHOW databases;
 +--------------------+
 | Database           |
 +--------------------+
 | db1                |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 7 rows in set (0.001 sec) 

As we can see student was an existing database, and we tried to create it again, so MariaDB did not develop a new table nor returned the error

 MariaDB [University]> CREATE OR REPLACE database Department;
 Query OK, 1 row affected (0.094 sec)
 MariaDB [University]> SHOW DATABASES;
 +--------------------+
 | Database           |
 +--------------------+
 | db1                |
 | db2                |
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 9 rows set (0.335 sec) 

In the above example, we have created a new database using OR REPALCE statement .It creates a new database as there is not database named department

MariaDB - Create a database

Here we can see the list of tables including department. 

IF NOT EXISTS:

If the mentioned database already exists and we use this command, MariaDB won't return an error. It will throw a warning.

For example:

  MariaDB [(none)]> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | db1                |
 | db2                |
 | department         |
 | information_schema |
 | mysql              |
 | performance_schema |
 | student            |
 | test               |
 | university         |
 +--------------------+
 9 rows in set (0.002 sec)
 MariaDB [(none)]> CREATE DATABASE db2;
 ERROR 1007 (HY000): Can't create database 'db2'; database exists
 MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS db2;
 Query OK, 0 rows affected, 1 warning (0.001 sec)
 MariaDB [(none)]> SHOW WARNINGS;
 +-------+------+----------------------------------------------+
 | Level | Code | Message                                      |
 +-------+------+----------------------------------------------+
 | Note | 1007 | Can't create database 'db2'; database exists |
 +-------+------+----------------------------------------------+
 1 row in set (0.055 sec) 

As we can see in the above example, if we try to create a table already existing, in this case, db2 is already created, MariaDB returns an error, but if we use IF NOT EXISTS, then a warning is returned.

Using the SHOW WARNING command, you can access the warnings.

MariaDB - Create a database