MariaDB Syntax

MariaDB

MariaDB is a relational database same as MySQL, which specifies the relations between two or more tables. It is also developed by the developers of MySQL, but the main objective of developing this database is that it is completely open-source.

The critical difference between MySQL and MariaDB

• There are 12 new storage engines in MariaDB that are not present in MySQL.
• Compared to MariaDB, MySQL's memory storage engine is slower.
• Replication is faster and safer with MariaDB. Additionally, updates can be completed 2x faster than with conventional MySQL.
• The JSON, WITH, and KILL statements are among the new features and extensions included with MariaDB, but in MySQL, these features are not present.

Syntax used in MariaDB

1. Creating the database

To create any database, we will use the 'CREATE DATABASE' keyword followed by the database name.

This is a similar syntax used in MySQL.

Syntax:

Output:

2. Checking all databases

To know the name of all the databases in our system, we use the 'SHOW' keyword.

Syntax:

Output:

3. Selecting a database

As we are aware of the fact that there can be multiple databases present on our single system, so we have to choose a particular database on which we want to work. We can create many tables or delete any table or manipulate any table in that particular table. We will use the 'USE' keyword followed by the database name on which we want to work.

Syntax:

Output:

4. Creating a new table

After selecting any particular database, if we want to create a new table, we can use the keyword 'CREATE TABLE' followed by the table name and then the columns name.

In column name, we will write the column name first, then its constraint. For any particular column, there may be more than one constraint.

Syntax:

Output:

Explanation

In the above code, we created the table in the database named db1. Our table name is 'jtp_intern', and it contains three columns.

• The first column name is 'name', and the constraint is varchar with 100, which means it will accept characters, and the maximum length is of 100 characters.
• The second column is named 'profile,' and it is also accepting characters of a length maximum of 50 characters.
• The third column is 'phone_number', which will accept only integer values, and the null value is not received in this column.

5. Get the list of all tables

If we want to know the name of all tables present in the database, we can use the 'SHOW' keyword, and it will show all the tables in the current database.

Syntax:

Output:

6. Show the complete structure of any table

If we want to get the scheme of any table in the database, we will use the 'DESC' keyword followed by the table name. It will display the complete schema (all the attributes in tabular form except records).

Syntax:

Output:

7. Adding the new row into the table

If we want to add a new record/row/tuple in a table, we can use the 'INSERT INTO' keyword followed by the table name and then the columns description and corresponding values.

Syntax:

We can add a single or multiple rows at a time, but the order of columns and values should be the same.

Output:

In the above, create the single row using the INSERT command.

8. Getting the specific attribute

If we want to fetch the particular attribute, we will use the SELECT keyword, where we will give the column name, and then after the 'FROM' clause, we will write the table name. We can use the 'WHERE' clause to select some specific rows of these columns using some conditions also. If we want all the columns, then we can use '*' instead of all column names.

Syntax:

Code:

Output:

9. Updating any attribute

In the table, if we want to change any value in any particular rows, we can change them easily with the help of the 'UPDATE' keyword followed by the table name. Then we will use the SET keyword to choose the particular attribute, and if we want specific rows, we can use the 'WHERE' clause.

Syntax:

Or

Output:

10. Deleting the rows in the table

If we want to delete any particular row, we can use the DELETE keyword to delete them, and we can use the WHERE clause to specify which row we want to delete.

Syntax:

Output: