EDUCBA

Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB Commands

MariaDB Commands

By Aanchal SharmaAanchal Sharma

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:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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:

MariaDB Commands 1

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:

MariaDB Commands 2

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:

MariaDB Commands 3

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:

MariaDB Commands 4

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:

MariaDB Commands 5

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:

MariaDB Commands 6

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:

MariaDB Commands 7

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:

select 1

  • 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:

UPDATE 1

  • 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:

DELETE 1

  • 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:

WHERE 1

  • 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:

LIKE 1

  • 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:

ORDER BY

  • 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:

DISTINCT

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

SELECT * FROM EngBooks;

Output:

FROM

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
0 Shares
Share
Tweet
Share