RHCE Login to MariaDB

We have now installed MariaDB onto the RHEL 7 or 7.1 system; however,The only MariaDB account we have is root. We can test the login to the database server using the client, mysql.

$ mysql -u root -p

We should be prompted for the MariaDB root account password. When entered correctly we should be presented with as mysql prompt. From here we can use exit or quit to leave the client session.

Create a MariaDB Database on RHEL 7.1

Other than the system databases we do not have any storage containers on the new database server. To display databases on the server we can use the commands

$ mysql -u root -p
MariaDB> SHOW DATABASES;

We, obviously, need to login to the server first and then run the second command. MariaDB and MySQL commands are not case sensitive but often keywords are written in upper case. The end of a line in the SQL language is terminated with a semi-colon (;).

We can see from the output that we have three system databases on the database server. It is an easy task to create a new database:

MariaDB > CREATE DATABASE staff;
MaraDB > USE staff;
MariaDB [staff]>

The USE command is very similar to a cd command in the file system, changing your context to the new database, staff in this case. The MariaDB prompt shows the database name reminding us of the context.

Databases can be thought of as logical containers organizing content, much as with folders in the file system, databases themselves do not hold the data but they group tables which are the primary data storage mechanism. As well as tables, other objects such as indices store data.

Create a Simple Table

Within the staff database we will create a table to store organizational departments to which staff members belong. The table will be kept to a very simple design ensuring that we do not lose track of our design process with too much detail. The table itself is not the data but allows for the data to be stored by defining columns.

From within the staff database we can create the table with the following SQL code:

MariaDB [staff]> CREATE TABLE department (
did TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
dname VARCHAR(20),PRIMARY KEY(did));

We can see that the table definition is enclosed within he parenthesis and each column is delimited using the comma (,). We have also defined a Primary Key constraint which is defined on the did column. The constraint ensures uniqueness of each value stored within the column. The data-type for this column is TINYINT. This takes 1 byte of storage for each value and subsequently allows values from -128 to +127; however, adding the additional UNSIGNED attribute allows for only positive values 0-255 which suits the needs of our Department ID.

If we wanted top create the table from a context other than the staff database we could provide a full name for the table. In this case the code would start:

MariaDB > CREATE TABLE staff.department

I would strongly advise keeping object names simple but should you need to have spaces in the table or column you will need to quote them with back-ticks (`). On a UK keyboard layout this if found on the top left of the keyboard.

MariaDB > CREATE TABLE `staff`.`department name`

Note that when using dotted names the dot must not make up part of the quoted text.

We can show the schema or layout of the table using the DESCRIBE command in SQL:

MariaDB [staff]> DESCRIBE department;

For more detailed information and the code to recreate the table you can use:

MariaDB [staff]> SHOW CREATE TABLE department;