In truth managing databases is a skill all its own that isn't directly connected to operating system administration. But because so many of the deployments you'll administrate involve or even depend on databases you really have no choice but to pick up at least a simple understanding of how they work.

Broadly speaking there are two kinds of databases. Heavily structured relational databases, which can be managed by structured query language, SQL environments, and nonrelational, noSQL databases whose popularity's been growing due to a simpler design that emphasizes speed over consistency. While both database types are important, the LPIC exam expects you to focus on relational databases and specifically the MySQL relational database management system.

It doesn't hurt to be aware that the original developers of MySQL actually left the project a few years ago to create MariaDB. At this point, MariaDB is completely compatible with MySQL and can be used and accessed in exactly the same ways. It has even begun to replace MySQL in some major Linux software stacks. So while my references will be to MySQL they're all equally relevant to MariaDB.

A relational database is made up of tables, records and fields. Let's imagine a database with two tables used by a company to track incoming communications. The first table contains information about customer complaints and the second customer compliments. Since the compliments table is likely to be mostly empty, we'll focus on the complaints. Each record represents a single email complaint. The record contains fields for the date and timestamp, the senders email address, the message itself and any response sent by the company.

Working with MySQL relational databases in Linux

Let's spend some time working on an actual MySQL database. First, we'll install MySQL on the Ubuntu virtual machine. When prompted during the setup process, I'll create a password for the root account. Once everything's in place, you can enter the MySQL environment using "mysql -u username -p." I'll use root for username. "-p" will prompt you for the root password.

We're now in MySQL land where every command has to end with a semicolon. If you forget to add a semicolon - and you definitely will at some point - the shell will simply dump you onto a new line and patiently wait for the rest of the command until you wake up and remember what it's expecting. "Show databases" by the way will display all the databases currently part of this installation.

Let's create a new database called "Communications" and run "show databases" once again. Our new communications database is there. Since we'll want to work with communications, we'll have to let MySQL in on our plans. Otherwise, it won't know that it's supposed to apply the coming commands to this particular database. We'll do that with use. We'll run show tables to confirm that communications doesn't yet have any tables. So we'll create a new table. We'll use create table, give it the name complaints and map out the fields each record will have. The date field will use the date format. The email field, will use the varchar format and allow as many as 20 characters. The message and response fields will allow up to 250 characters.

When we hit enter, MySQL will parse the command to make sure the syntax is correct. If there's anything wrong, you'll get an error message. Now let's enter some data using insert into the complaints table by first defining the specific values we'd like to populate. You don't need to add data for every column but you do need to tell MySQL which columns you're adding. We'll then add these values. As I enter the date, email, message and response values note how fields are created by commas but no spaces. Users won't usually enter data this way. It's far more common for database managers or developers to create a front-end application that makes data entry intuitive but under the bright and shiny surface of that front end this is exactly the structure that will be used. We'll have to make sure that our insert operation worked so we'll run select everything from complaints where the asterisks represents everything.

Let's enter one more record but this time we'll insert data into only three fields, rather than all four. We'll run select again to make sure this one worked. Although if there was anything wrong, the MySQL command line would almost certainly have complained loudly about it already. I've added some more records to our table to make it easier to illustrate the next operations. Let's take another look at the table to see how it looks now.

Let's review. You list the databases in your MySQL environment using show databases. Create databases. Database name will create a new, empty database. You tell MySQL that you want to work with a specific database using "use" database name. You create a new table within a database with the create table command and by specifying the columns, their character types and maximum length. You can add data to a table using "insert into" specifying to which columns you're going to add data. And then the data for each field enclosed by apostrophes. You can display data from a table using "select."

Accessing and manipulating MySQL data using select

You can also select records based on precise filters. So for instance, you could specify all records associated with a particular customer by selecting all records whose email field equals that customer's email address. If you need to delete records, suppose Joe at cranky.com finally realized that he wasn't getting anywhere with his constant complaints and decided to take his business elsewhere, you could run the select operation we just did to confirm it's picking up exactly the records we need and then run it again using delete instead of select to remove all records whose email field is Joe@cranky.com.

We can use update and set to edit the contents of individual or groups of fields. Here we'll change Joe's email and message fields to update them to new values. By the way, if you don't specify where these values should change, MySQL will assume you want to give these new values to every email and message field in the whole table. You can also choose a column to display our records according to a specified order using either "order by" or "group by." This will arrange all of our records in ascending order by date. You could now, if necessary, submit just these records to some operation. Adding desc, on the other hand will arrange our records by date in descending order.

To illustrate group by I'll create a new table that contains customer orders including the item purchased and the price paid. Note how the customer and price columns use integer as their format type so we can use their contents as numeric values. I'll insert some data so we can work with it. Let's take a look at our table. We can see that books seems to be our most popular item. We've already sold three of them. If you wanted MySQL to report the total revenue per item type, you could select all the items and using the contents of the price and item columns calculate the sum of the prices. The three books we sold generated a total of $30 in revenue.

Finally, we should at least briefly describe how to use data from two separate tables. If we look once again at our purchases table, we see that every customer has an ID number. I'll create another table called customers in which I'll place some customer information. For simplicity sake, I'll use only their first names. Again, I'll create some data. Let's take another look at both our purchases and customers tables. Now suppose you wanted to use our data from purchases but incorporate customer names into the output. We'll use a simple join operation that looks like this. We'll select from purchases and from customers separated by a comma all records where the customer field from the purchases table equals the ID field from the customers table. This will display each purchase with the correct customer ID data appended to the right.

It's review time. You can select only records corresponding to specified filters using select with "where." "Delete" follows the same syntax as select but will actually delete the return data. You can edit a record using "update set" and "where." You can control the order data is displayed and used with "order by" and tally totals with "select item" and "group by." We can intelligently join data from two tables by using select and where to reference the specific tables and columns you need to use.