Updated June 3, 2023
Definition of MariaDB Commands
- Initially, MariaDB was split from the MySQL database management system, 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 with the MariaDB server with the help of the MySQL program, i.e. command-line having the 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 an example.
Along with supporting PHP, a well-liked web development language, MariaDB offers technology of the type seen in Galera Clusters. In addition, MariaDB commands can perform on several operating systems and support 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 a list of databases that are present currently on the server.
- SHOW TABLES – Provides a list of all non-temporary tables from the database server.
- SHOW COLUMNS FROM [Name of the table] – Delivers the column information concerning the stated table.
- SHOW INDEX FROM TABLENAME [Name of the table] – Delivers information about the table index associated with the definite table.
- SHOW TABLE STATUS LIKE [Name of table] – Provides database tables with information of non-temporary tables and the pattern appearing 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 with creating any new database in the MariaDB server, the user must have superior privileges that are only established for the root admins and users.
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, you need to select it from the database lists available on 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 a 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 the 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 the 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 to apply the insert command to enter any data value into the table in MariaDB having the 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 the 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 is 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 the 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 the 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 is 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
We hope that this EDUCBA information on “MariaDB Commands” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
