MariaDB - How to create a new database and manage it?

This post will cover the topic of creating database in MariaDB and this is where you get started with MariaDB. if you are familiar with MySQL already, it will be super easy to pickup as MySQL and MariaDB are identical, they use the same SQL syntax, file system, protocol, etc.

1. CREATE DATABASE

In order to create a new database in MariaDB, you have to make sure the current users has the CREATE privilege for the database. You can check your privileges by running one of these statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Everything is ok, now start creating the database with a name for it:

CREATE DATABASE db_name;

When you are not sure whether the database with that name already exists or not, use CREATE OR REPLACE:

CREATE OR REPLACE DATABASE db_name;

If there’s an existing database with that name, it will drop that database and create a new one. That case, the CREATE OR REPLACE statement will turn into this statement:

DROP DATABASE db_name;
CREATE DATABASE db_name;

In case you don’t want to replace any existing database and skip instead, use IF NOT EXISTS:

CREATE DATABASE IF NOT EXISTS db_name;

If the database exists, it will return an error saying Can't create database 'db_name'; database exists

You can also add some special settings for character sets and collation:

CHARACTER SET = charset_name
COLLATE = collate_name

For example, this is a complete CREATE DATABASE statement will full setting:

CREATE OR REPLACE DATABASE db01
CHARACTER SET = 'keybcs2'
COLLATE = 'keybcs2_bin'

2. USE DATABASE

After you have you database setup, run this query to show all existing databases, run this:

SHOW DATABASES;

To select a database to use for subsequent operations, use the following query:

USE new_database;

2. DROP DATABASE

To drop an existing database, use this query:

DROP DATABASE db_name;

When you want to drop a database but you are not sure if it exists or not, use DROP IF EXISTS:

DROP DATABASE IF EXISTS db_name;

Need a good GUI Tool for MySQL or MariaDB? Try TablePlus. It’s is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server… faster and easier. 

TablePlus