MariaDB Commands

Definition of MariaDB Commands

• Initially, MariaDB is a split of the MySQL database management system which is industrialized by its innovative developers. This tool of DBMS delivers data processing abilities for both enterprise and small type tasks.
• We can say that MariaDB is an enhanced version of MySQL server which comes with several inbuilt powerful features and many stabilities, performance progresses, and safety that a user cannot get in MySQL.
• In MariaDB, you can find more memory storage engine for MariaDB commands as compared to MySQL. We can associate to the MariaDB server with the help of the MySQL program i.e. command-line having appropriate username, password, hostname, and name of the database.
• MariaDB commands are the administrative commands that are significant commands which a user will implement on a regular basis when functioning with MariaDB.

MariaDB commands..explain each with example

MariaDB delivers the Galera Cluster type technology and also supports a popular language in web development, PHP. In addition, MariaDB commands can perform on several operating systems along with support for many programming languages.

Let us discuss some of the administrative commands in MariaDB mentioned as follows:

• USE [name of the database] – Arranges the current default database.
• SHOW DATABASES – Provides list of databases that are present currently on the server.
• SHOW TABLES – Provides list of all non-temporary tables from the database server.
• SHOW COLUMNS FROM [Name of the table] – Delivers the information of column concerning to the stated table.
• SHOW INDEX FROM TABLENAME [Name of the table] – Delivers information about table index associated to the definite table.
• SHOW TABLE STATUS LIKE [Name of table] – Provides database tables with information of non-temporary tables along with the pattern which appears after using the clause LIKE that is applied to fetch the table names.

MariaDB Commands

Now, let us discuss and illustrate the commands in detail as follows:

1. Creating Database as well as Tables

Before we proceed for creating any new database in the server MariaDB, the user must have superior privileges that are only established to the root admins and user.

We will use the following syntax for this:

CREATE DATABASE Nameofdatabase;

We can execute as:

CREATE DATABASE Books;

Output:

2. To select a Database

If any user wants to use or perform on a definite database then you need to select it from the database lists available on the MariaDB. After this, we can proceed to create tasks like creating tables in that particular database selected. The command is as follows:

Syntax:

USE Nameofdatabase;

Command

USE Books;

Output:

3. To create a database table

After we have selected the specified database, we can create table within it by using the following syntax:

CREATE TABLE name_of_table (Name_of_Column, Column_data_type);

From the columns created, one of the columns needs to be a primary key which will not permit NULL values to be inserted. For example,

CREATE TABLE EngBooks(EngID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, BookName VARCHAR(255) NOT NULL, BookPrice INT NOT NULL);

Output:

4. To show database tables

When you have created the tables in database you can view the tables whether created effectively or not to confirm. The following MariaDB command will display the list of tables present in the database:

SHOW TABLES;

Output:

5. To show table structure

If a user requires to view the structure of any stated table in MariaDB we will use the DESCRIBE command with syntax as follows:

DESC Name_of_Table;
For instance,
DESC EngBooks;

Output:

6. CRUD commands and some clause commands

• INSERT: We need apply the insert command to enter any data value into the table in MariaDB having below syntax:

INSERT INTO Name_of_Table(Column1, Column2,….ColumnN) VALUES(Value1,…ValueN),(Value1,…,ValueN),……;

Let us show by implementation into the create table above:

INSERT INTO EngBooks(EngID, BookName, BookPrice) VALUES(101, ‘Networking’, 5000);
select * from EngBooks;

Output:

Also, we can enter multiple record rows by using the syntax as follows:

INSERT INTO EngBooks(EngID, BookName, BookPrice) VALUES(101, ‘Networking’, 5000), (102, ‘Computer’, 4000), (103, ‘Maths’, 3500);

select * from EngBooks;

Output:

We must use quotes either single or double for string values while executing the Insert statements in the server.

• SELECT: We can view the contents or data records of the database table using the MariaDB command SELECT with syntax:

SELECT * FROM Name_of_table;

Here, * denotes all means all rows and columns as data will be fetched when the command executes like this:

SELECT * FROM EngBooks;

Output:

• UPDATE: If any user or admin wants to change or alter any record data within the database table that are already inserted into it then, we have to implement the update command as follows:

UPDATE Name_of_Table SET fieldname1 = ValueX, fieldname2 = ValueY,…;

For instance,

UPDATE EngBooks SET BookPrice = 7000 WHERE EngID = 101;

Output:

• DELETE: We use the delete command to remove one or more rows from the table records in the database with syntax as:

DELETE FROM Name_of_Table [WHERE clause conditions] [ORDER BY Expr {ASC/DESC}] [LIMIT Rows_number];

For example,

DELETE FROM EngBooks WHERE EngID = 103;

Output:

• WHERE: This clause is useful to state the definite location where a user wants to make the alteration and applied together with SELECT, INSERT, DELETE, and UPDATE like queries with syntax:

SELECT * FROM EngBooks WHERE BookPrice < 5000;

Output:

• LIKE: This clause is used to define certain data pattern and fetch the related matching items from the database table like below:

SELECT BookName, BookPrice FROM EngBooks WHERE BookName LIKE ‘N%’;

Output:

• ORDER BY: This clause provides the data accessible in sorted form in either ascending or descending order with SELECT statement as:

SELECT * FROM EngBooks WHERE BookPrice < 5000 ORDER BY BookPrice DESC;

Output:

• DISTINCT: This clause will help to remove the duplicate data records while fetching certain data values from the database table to receive unique ones:

SELECT DISTINCT BookPrice FROM EngBooks;

Output:

• FROM: This clause applied to retrieve records from a specific database table as follows:

SELECT * FROM EngBooks;

Output:

Conclusion

• MariaDB server operates under the licenses such as LGPL, BSD or, GPL. MariaDB commands are based on a standard and famous querying language i.e. SQL. It has many storage engines with high-performance functioning.
• MariaDB approaches with many additional commands which are not accessible in MySQL. Since MySQL contains some features that cause a negative influence on the DBMS performance so these things have been substituted in MariaDB.

Recommended Articles

This is a guide to MariaDB Commands. Here we discuss the definition, syntax, Explain MariaDB Commands, and Examples with code implementation. You may also have a look at the following articles to learn more –

1. MariaDB vs MySQL
2. MariaDB vs MongoDB
3. DBMS vs File System
4. Database Management Software