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
- ‘create’ table statement
- ‘show tables’
- ‘insert’ statement
- ‘select’ statement
‘show databases’ and ‘use’ statements
List available 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’
‘create’ table statement
syntax : CREATE TABLE table_name (column_name column_type);
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.
List available tables
create records in table.
Syntax: INSERT INTO tablename (field,field2,…) VALUES (value, value2,…);
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');
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.