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
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.