CRUD is an acronym for Create, Read, Update, Delete operations in Database. We will look at CRUD operations in mariadb with some examples (part-1), in this post.

‘show databases’ and ‘use’ statements

List available databases

show databases

Below is a screenshot to give an example of how the query window looks like in HeidiSQL.

‘choose’ database statement can be used to choose a Database from the list to work on. The below statement chooses ‘unixutilsdb’ and all tables that we intend to create will be created under ‘unixutilsdb’ 

use unixutilsdb

‘create’ table statement

syntax : CREATE TABLE table_name (column_name column_type);
Example:

CREATE TABLE servers(  
server_id INT NOT NULL AUTO_INCREMENT,  
server_name VARCHAR(100) NOT NULL,  
server_location VARCHAR(40) NOT NULL,  
PRIMARY KEY ( server_id ));  


# MariaDB 10.3 RedHat repository list - created 2018-09-03 06:47 UTC
Note:
AUTO_INCREMENT, makes the row to fill itself with the next serial number and can be left empty while inserting values. 
NOT NULL, forces a non empty field.
PRIMARY KEY, is used to uniqely identify a record and is something that does not change. for Ex: a phone number can be set as primary key, since it can used to uniquely identify its owner. Similarly, a roll number or a passport number etc.

‘show tables’

List available tables

SHOW TABLES;

‘insert’ statement

create records in table.
Syntax: INSERT INTO tablename (field,field2,…) VALUES (value, value2,…);
Example:

INSERT INTO servers ( server_name, server_location ) VALUES ( 'test_Server1', 'Building1-floor7-bay8');
INSERT INTO servers ( server_name, server_location ) VALUES ( 'test_Server2', 'Building2-floor6-bay2');

‘select’ statement

Query and display records in DB with ‘Select’ statement.
Example: select all rows and columns from table ‘servers’

select * from servers

Note that, we did not insert values to the first field which is ‘server_id’ and yet it has been updated incrementally. This is because we set this field to ‘AUTO INCREMENT’

Below is a screenshot of how the result looks like in heidiSQL, when the ‘select’ statement is run on ‘servers’ table.

Example: select only a specific column from the table

select server_name from servers;

Below is a screenshot of output for this statement.

Example: select all columns that matches a specific row from the table.

select * from servers where server_name = 'test_Server4'

In this post we looked at accessing a database, creating tables, inserting values into table and querying for records with ‘select’ statement. We will see more sophisticated select and insert statements in the next post.

RELATED ARTICLESMORE FROM AUTHOR