In this tutorial, we will learn about how to create database in MariaDB using step by step guide. Those who comes from database background already known that MariaDB uses SQL for database operations. SQL stands for Structured Query Language which is used to access and manipulate databases. SQL commands are categorized into 5 broader categories. These are:
DDL: Data Definition Language
DQL: Data Query Language
DML: Data Manipulation Language
DCL: Data Control Language
TCL: Transaction Control Language
Let us start the tutorial by understanding what database is and SQL is used for creating databases in MariaDB. So, let’s begin the tutorial.
What is Database?
A Database is a structured collection of data that is organized and stored in a way that enables efficient retrieval, updating and management of that data. It act as a central repository for storing and managing information, making it easier to access, manipulate and analyze large volumes of data.
There are many features offered by different databases because of which it is widely used. These features are mentioned below.
Data Organization: Databases provide a structured way to organize data. They allow data to be categorized ,sorted and linked making it easier to understand and use.
Data Retrieval: Databases offer powerful query languages that enable complex searches and analysis. Users can extract specific information quickly and efficiently.
Data integrity: Databases enforce data integrity constraints, ensuring that the data is accurate, consistent and reliable. This is crucial for applications where data accuracy is essential.
Data Security: Databases provides security features to protect data from unauthorized access, ensuring that only authorized users can view or modify specific data.
Data Recovery: Databases often include backup and recovery mechanisms, allowing organizations to restore data in case of accidental deletion, hardware failures or other disasters.
Data Analysis: Databases serves as valuable sources of data for analysis. Analytical tools can process data stored in databases to gain insights, make predictions ans support decision-making processes.
How to Create Database in MariaDB [Step by Step Guide]
As we know, MariaDB is a fork of MySQL, therefore it supports Structured Query Language (SQL). In MariaDB, to create databases, tables or any other database resources, we will use different SQL queries. Let us look at different SQL commands one by one to understand how it creates and query database resources.
Also Read: Top 5 Statistical Methods of Data Science
- MariaDB Preinstalled in Linux System.
- Basic understanding of Linux Commands.
- User with sudo privilege/root user access required.
Step-1: Connect to MariaDB Database
In this step after verifying that MariaDB is installed properly, connect to MariaDB database using below command. Please note that by default, there is no password set for login. Hence when you enter the command
mysql -u root -p, it will ask you for the password. Press enter and you will be logged in to the database.
[root@linuxnasa ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 10.1.20-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Step-2: List Databases
In this step, we will check the existing databases in MariaDB using below command. By default there will be 4 databases already created as shown in below command output.
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec)
Step-3: Create New Database
In this step we will create a new database. We will call this dabase ‘linuxtutorial’. If the command execution is successful, you will see below output.
MariaDB [(none)]> create database linuxtutorial; Query OK, 1 row affected (0.00 sec)
Next, verify that the database has been created successfully by executing ‘show databases’ command again as shown below. If you see the new database name listed in the command output, it means database has been created successfully.
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | linuxtutorial | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
Step-4: Choose and Switch to Database
In this step, we will connect to the ‘linuxtutorial’ database which we created in previous step. We will use below command.
MariaDB [(none)]> use linuxtutorial; Database changed MariaDB [linuxtutorial]>
Step-5: Create Table in Database
In this step, once the database creation is successful, we will test database working by creating a new table in it. Use below command to create a table named ‘audience’. This table will have 3 columns namely, Name, Age, Country of different datatypes as shown below.
MariaDB [linuxtutorial]> create table audience(Name varchar(20), Age int, Country varchar(20)); Query OK, 0 rows affected (0.45 sec)
Next, verify if table is created successfully in the selected database using below command. If you get the output similar to shown below, it means table has been created successfully.
MariaDB [linuxtutorial]> desc audience; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | Name | varchar(20) | YES | | NULL | | | Age | int(11) | YES | | NULL | | | Country | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Step-6: Exit from Database
In this step, once you are done with your work, you can exit from the database using
exit command. It will print the message ‘Bye’ and takes you back to the terminal as shown below.
MariaDB [linuxtutorial]> exit; Bye [root@linuxnasa ~]#
We have learnt about how to create a database and table in MariaDB database system. Now that you are familiar with database and table creation, you can practice different SQL queries to operate on these databases. You can learn and practice SQL commands by referring the official MariaDB documentation .