How to Create & Manipulate Data in MariaDB?

Data Creation and manipulation are essential for businesses and organizations in today’s data-driven environment. The well-liked open-source relational database management system MariaDB provides a strong foundation for managing data. It’s critical to grasp how to create and modify data in MariaDB. In this article, we will go through the creation and manipulation of data in MariaDB.

Creating a Database

To create a database in MariaDB we are following these steps:

Step 1: Open MariaDB click on new create a session with the name MariaDB and then enter the password and port.

Step 2: To create a database, right-click on the session name, select Create New, then Database. Enter ‘mydb’ as the database’s name and click OK.

Step 3: Using SQL command

Click on the query button and write this query to insert a data and click on execute button on toolbar.

CREATE DATABASE mydb;

Step 4: Create Table

Create a table by performing a right-click on your mydb database and selecting Create New -> Table. As we are providing books, name our table.

Using SQL command

CREATE TABLE books ( id_book INT, title VARCHAR(50), year DATE );

Step 5: Create Fields

Select the desired fields by clicking on Add. Assume for the moment that we are adding a first field which is book id with the data type int, write length of 5. Add new fields, such as title, and year of the data type varchar and date. Lastly, select Save.

Our table has three columns into which we will add records, but before a record can be added, it must have at least one primary key. ID is going to be our primary key. Right Click on id_book ->create new index -> PRIMARY. Increase the default value’s auto-increment. Save.

Using SQL command:

CREATE TABLE books ( id_book INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(50), year DATE );

Data Manipulation

To add records, click on data -> plus icon then double click on title filed and add value and same add date also. Add more records. The SQL statements below are also visible on the console.

Using SQL command:

INSERT INTO books(title, year) VALUES (' A History of Earth', ' 2018-03-02'); 

Let’s see CRUD operations in MariaDB using SQL commands for manipulating data.

Query – 1: Insert Data

INSERT INTO books (title, year) VALUES ('Computer Networks', ' 2011-03-02');  

Output:

Now you can see on data tab that this record get inserted in the books table.

Query – 2: See All Records

SELECT * FROM books;

Output:

Query – 3: Update Record

UPDATE books  SET title = 'OS' WHERE YEAR = '2018-03-02'; 

Output:

Query – 4: Delete Record

DELETE FROM books WHERE title = 'physics';   

Output:

Query – 5: Sort Record

select title, year from books order by title;

Output:

Query – 6: Retrieve data between specific year

SELECT * FROM books WHERE year BETWEEN '2018-03-02' AND '2022-11-02';    

Output:

Conclusion

Users may create databases, define tables, input data, and manipulate it as necessary with confidence while using MariaDB with the help of this article. Your data management abilities will improve over time as a result of continued practice and investigation of MariaDB’s features, enabling you to utilize this robust database system to its fullest.