Databases with Linux

Installation and configuration

To offer database services, you need to install the MySQL server. To be able to do queries and such, the client software must be installed. Being so popular, all the major distributions offer you the possibility of installing these packages.

Once installed, you'll need to set the MySQL root user password. This 'root' should not be confused with the 'root' of the whole machine. It is similar in that you have privileges for everything, but that's where it ends. Once installed, you set the root password like so:

mysqladmin -u root password YOUR_PASSWORD

where YOUR_PASSWORD is the password you want. If you want to change this password in the future, this is also the way to go about it. When you change the password, you will be prompted for the old one.

Creating databases and tables

Now we're ready to create a database. Again, we will use the program mysqladmin.

mysqladmin -u root -p create DB_NAME

where DB_NAME is the name of your shiny, new database. Of course, a database is really just an empty box waiting to be things to be put into it. Actually, before we put things in our empty box, we should create some dividers to separate items. Database tables are like these dividers, so we should create a table. To do this, we'll need to open a MySQL client shell.

mysql -u root -p albums

You will be prompted for the root password and then you'll see something like this:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 3.23.49-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Now we're ready to create a table. Before we do, we should look at some of the basic field types that you'll be using. The most widely used is VARCHAR. VARCHAR can store alphanumerical data from 1 to 255 characters in length. You must specify this length when you create the table. The INTEGER or INT field type can accept values from -2147483648 to 2147483647. This is used when you want to enter data that you know will only be whole numbers. DOUBLE is a good field type when you want to deal with data with decimal points like money. There is also another type DECIMAL. The difference is that DOUBLE can deal with decimals where you may not be interested in rounding. If you want to store large descriptions, TEXT is for this purpose. A TEXT field can store up to 2¹⁶ (65536) characters. Another field type that you might end up using often is ENUM. This is particularly useful when you know that the data can only be 2 or 3 things and never changes (like true/false, yes/no or yes/no/maybe). Finally, to get a handle on dates and times when your data is stored we might use field types like TIMESTAMP, which is in the form of YYYYMMDDHHmmSS (Year, Month, Day, Hour, Minute and Seconds) or DATE, if you're looking for something with less precision. Of course there are other types and you may want to use them. I invite you to look at the excellent documentation on MySQL's website to get more in-depth information on these field types and others. In the meanwhile, let's create a table:

Let's imagine that you collect LPs. You might want to create a database to keep track of things or share your information over the Internet. A MySQL database is ideal for this, because you can later use it with Perl or PHP scripts to present this on the WWW. Let's set up a table to deal with info about an album collection. There are a couple of ways to do this. We can create the table on the command line - all on one line:

CREATE TABLE collection (title VARCHAR(100), artist VARCHAR(100), 
genre VARCHAR(30), condition VARCHAR(10), worth DOUBLE(2,2), notes TEXT, 
released DATE, added DATE, opened ENUM('yes','no'), updated TIMESTAMP(14), 
ID int(11) NOT NULL auto_increment, PRIMARY KEY (ID))

First, we have VARCHAR fields for title, artist, genre, condition. Then we have a DOUBLE field suitable for currency for the LPs value, called 'worth'. Next, we have a TEXT field for making notes about the album, followed by DATE fields for when the LP was released and when we added it to our collection. I added a field ENUM 'opened' as an example. We could use this to say if the album had been opened or if it was still in the shrinkwrap. The last one is a complete TIMESTAMP field of 14 characters to mark when we update the record. TIMESTAMP fields can start at 2 characters (just the year) and grow by 2 characters (4,6,8,10,12,14) with more information. 14 characters is accurate to the second. The last one is a special field I usually use as it makes updates via a web script easier. What I've done is to create an ID field that is an integer. NOT NULL means that it must always have a value. The others could be theoretically left blank, but this one can't. Plus, it will automatically increment every time we add an LP to the database. This also serves as the PRIMARY KEY of the table because of this lack of empty data. A key, which is used for sorting, must always have some sort of information in it. Here, we'll do the default sorting by 1,2,3 etc.

If you've got this in your MySQL shell, just press enter to create the table. You'll see something like this if you're successful:

Query OK, 0 rows affected (0.01 sec)

If you've done something wrong, MySQL is pretty good about telling you where your mistake might be. Let's say I left out the comma between the 'worth DOUBLE' field and the 'notes TEXT' field. I would get a message like this:

ERROR 1064: You have an error in your SQL syntax near 'notes TEXT, 
released DATE, added DATE, opened ENUM('yes','no'), updated TIMESTAMP' at line 1

Now to make sure everything is in place, we can ask MySQL to describe the table for us:

describe collection;

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| title     | varchar(100)     | YES  |     | NULL    |                |
| artist    | varchar(100)     | YES  |     | NULL    |                |
| genre     | varchar(30)      | YES  |     | NULL    |                |
| condition | varchar(10)      | YES  |     | NULL    |                |
| worth     | double(4,2)      | YES  |     | NULL    |                |
| notes     | text             | YES  |     | NULL    |                |
| released  | date             | YES  |     | NULL    |                |
| added     | date             | YES  |     | NULL    |                |
| opened    | enum('yes','no') | YES  |     | NULL    |                |
| updated   | timestamp(14)    | YES  |     | NULL    |                |
| ID        | int(11)          |      | PRI | NULL    | auto_increment |
+-----------+------------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)

We don't have to create the table in the MySQL shell if we don't want to. You can create a text file with the same and then have MySQL read it

## table albums.collection

CREATE TABLE collection (
   title      VARCHAR(100),    
   artist     VARCHAR(100),    
   genre      VARCHAR(30),     
   condition  VARCHAR(10),     
   worth      DOUBLE(4,2),     
   notes      TEXT,            
   released   DATE,            
   added      DATE,            
   opened     ENUM('yes','no'),    
   ID int(11) NOT NULL auto_increment,
   PRIMARY KEY  (ID));

Save it as collection.sql. Now, to have MySQL read it, we would do the following from a normal terminal (not a MySQL shell).

mysql -u root -p albums < collection.sql

You will be prompted for the MySQL root password. If you haven't made any mistakes in syntax, your table will be created.

A database and a table is still and empty box with separators. Now it's time to fill the table up with data.

Inserting your data

Actually, the word database is a pretty broad term. If I open a text file and I write information about my album collection in a fairly organized way, then I have created a database of sorts. Though we call what we've just created a "database" and a table, it really is isn't yet because we haven't put information into it. MySQL really is a program to manipulate data more than anything because, as I mentioned, we could use a simple text file to store data. What makes MySQL and other database software attractive is it helps us put the data to use in all kinds of interesting ways.

To add data to our album collection, we can open up a MySQL shell and do the following:

INSERT INTO collection (title, artist, genre, condition, worth, notes, released, 
added, opened) VALUES ('Yesterday and Today', 'Beatles', 'Pop/Rock', 'good', 
'3000.00', 'The famous Butcher Cover', '1966-06-01', '1978-09-23', 'yes');

Again, this is one way of entering data. It is perhaps not the most comfortable way of doing it. To refer back to my reference about the text file, you can also use a text file to insert data into the table. You just have to make sure that the fields are clearly separated from one another and the each "record" (one entry in the table) is on a separate line. For example, we could create a text file like this:

'Armed Forces', 'Elvis Costello', 'Pop/Rock', 'exlnt', 
'20.00', 'Probably his best', '1979-01-05', '1982-03-14', 'yes', '20030925201500'
'Zenyatta Mondatta', 'Police', 'Pop/Rock', 'fair', 
'5.00', 'interesting tracks', '1980-10-03', '1981-12-25', 'yes', '20030925201500'

We could insert the data in our table this way:

LOAD DATA INFILE '/home/bob/record_collection' INTO TABLE collecton 
FIELDS TERMINATED BY ',' ENCLOSED BY '\'' LINES TERMINATED BY '\n'

What we've done is tell MySQL to load data in this text file and how our data is organized within it. The organization is pretty self-explanatory. Just remember to escape single quotes ('\''). The line break or carriage return is marked by a '\n'.

To make this page look nice in a browser, I've had to force a line break at 'exlnt' and 'fair'. If you're going to cut and paste this example and try it out, please note that the only line break should be be at '20030925201500'

These are two main ways of inserting data on the command line. Further along we'll see how creating Perl or PHP scripts can speed this process up quite a bit and make it much more comfortable.

Viewing Data

Now that you've got your album collection nicely stored in a database, it would be shame not to do something with the data. The first thing we could do is look at it. For this, we'll use the SQL statement SELECT. Let's fire up the MySQL shell and enter this:

SELECT * FROM collection;

You're not going to see anything very pretty here. Actually, if you're working with an x-term, it's probably going to be pretty ugly. But if we select only certain fields, we can fine-tune our query.

SELECT title, artist, worth FROM collection;

This will give us a little nicer output:

+---------------------+-------------------+---------+
| title               | artist            | worth   |
+---------------------+-------------------+---------+
| Yesterday and Today | Beatles           | 3000.00 |
| Armed Forces        | Elvis Costello    |   20.00 |
| Zenyatta Mondatta   | Police            |    5.00 |
+---------------------+-------------------+---------+

We can use the WHERE statement to refine the query:

SELECT title, artist, worth FROM collection WHERE artist="Beatles";

+---------------------+---------+---------+
| title               | artist  | worth   |
+---------------------+---------+---------+
| Yesterday and Today | Beatles | 3000.00 |
+---------------------+---------+---------+
1 row in set (0.01 sec)

We can even do approximate queries. This will look for LPs by artists with the letter 'i' in the title:

SELECT title, artist, worth FROM collection WHERE artist LIKE "%i%";

+-------------------+----------------+-------+
| title             | artist         | worth |
+-------------------+----------------+-------+
| Armed Forces      | Elvis Costello | 20.00 |
| Zenyatta Mondatta | Police         |  5.00 |
+-------------------+----------------+-------+
2 rows in set (0.00 sec)

Since 'Beatles' doesn't have the letter 'i' in it, it doesn't show up. You can do something like LIKE "B%" and that only show artists that begin with 'B' or LIKE "%o" to show only those artist that end in 'o'. By removing or placing % and groups of letters or words, you can do a lot of refining. You can also have MySQL do your math for you. Let's say you wanted to get the total dollar amount of your entire collection. You could do something like this:

SELECT SUM(worth) AS total FROM collection;

And we get this output:

+---------+
| total   |
+---------+
| 3025.00 |
+---------+
1 row in set (0.00 sec)

It's the sum of the worth of our albums with the header 'total'. The header appeared by including 'AS total' to our query. This header could be anything. I just chose 'total' as it's meaningful to me. You could have said 'AS larry' if that's meaningful to you.

You can also use the >, < and != to refine your queries. Here's an example of finding albums whose value is below 100.00.

SELECT title, artist, worth FROM collection WHERE worth < '100.00';

or

SELECT title, artist, genre FROM collection WHERE genre != 'Pop/Rock';

which will show us those albums which weren't made by Pop/Rock artists.

You can also sort the output of the query to show you the albums in alphabetical order:

SELECT title, artist, genre FROM collection ORDER BY title;

the ORDER statement will put it in the order of the field you choose. Just substitute the field 'updated' to order them by when you last made changes to an entry. If you had a lot of entries, you could also limit the number of entries MySQL shows you:

SELECT title, artist, updated from collection ORDER BY updated LIMIT 0,10;

This will show us the first 10 entries. Then you may substitute LIMIT 11,20 to show you the next ten, and so on and so on.

Again, there are all sorts of ways of looking at your data. The MySQL documentation has a lot more examples and I'm sure that as you get proficient, you'll be needing more complex queries.

Deleting and altering data

Let's imagine that you leave your album Zenyatta Mondatta lying around. The new puppy you just bought for the kids decides to try out his teeth on that nice soft, black vinyl. The album is a total loss and you decide to get rid of it (and maybe the puppy too). Since the album is no longer in your collection, you would delete it from the table. Here is how to do it on the command line:

DELETE FROM collection WHERE title="Zenyatta Mondatta";

That might be a little drastic though. You might want to buy another copy of the album and replace it. If this is the case, you should probably leave the entry there and just update it when you get a new one. Here's how you would update an entry:

UPDATE collection SET condition = "good", added = "2003-10-05", worth = "8.50" 
WHERE title = "Zenyatta Mondatta";

That will update our entry for Zenyatta Mondatta.

Altering databases and tables

You should use some care with the things what we'll deal with in this section. We're going to be talking about altering and deleting live databases. If your information is important to you, then you should back it up regularly. MySQL makes this very easy. Here are a few ways to back up your data.

Generally, MySQL stores its tables in /var/lib/mysql (/var means variable - if you remember our first lessons - databases vary in size). You could make a tarball of this directory and back it up that way. This might be a good idea in the event of a system-wide disaster (a hard disk failure, for example). However, if you made a mistake altering data, the most comfortable way, in my opinion, is to make a 'dump' of a table and/or database and later restore it via MySQL itself. Here's how we can back up an entire database:

mysqldump --add-lock -u root -p --databases albums > albums_all.sql

That would make a nice file called albums_all.sql with the database and table creation information plus the data itself. We've added the option here --add-lock so that there is no data corruption when we make the copy. In a "mission critical" situation, people could be adding data to the table at the moment you do this. You need to lock them out before you make the copy. Now, in the event of something happening to that database, you would restore it by typing in a normal terminal:

mysql -u root -p < albums_all.sql

You can also do this with tables and certain entries within a table. For example, this will back up a table:

mysqldump --add-lock albums -u root -p collection > albums_collection.sql

The following will give us a dump of only those records that we've updated in October of 2003.

mysqldump --add-lock albums -u root -p collection -n -t "--where=updated 
like '200310%'" > oct_collection.sql

The two options at the end (-n -t) will make sure that the dump doesn't include either a database creation statement (-n) or a table creation statement (-t). We do this so we can just load the info back into the table if we need to.

Now we can work on our table structure or even delete a table and/or database without worry (as long as the medium you backed it up to is sound!).

So how does this deleting and altering tables and databases work? Well, at some time or another you will probably have to do this. Some countries even have laws that obligate you to destroy personal data when that is no longer being used. Let's say you ran a club and you had a list of the members names and addresses. The club disbands and you're the one who's in charge of that data. Well, you may need to safely erase all that data in order to be in accordance with the law. If you had a database called linuxclub, you would delete the whole database, tables and all with this statement in your MySQL shell:

DROP database linuxclub;

In MySQL speak, deleting a database or table is called dropping it. The table syntax is the same

DROP table lexington;

Use this with extreme care. This is not reversible. You will lose all of your data.

You may also need to update your table structure from time to time. For my album collection database, I should have included a field for the artist's record label. Others could have been included as well. There's no need to worry, even if you've been using the table for a while. You can add fields without any problem. Let's add the field 'label' to our collection table.

ALTER TABLE collection ADD COLUMN label VARCHAR(30);

If you quickly do a DESCRIBE collection; you'll see our new field down at the bottom ready to go. As I mentioned, adding fields is not a problem technically. The only thing you have to keep in mind is that you have to fill that empty field.

UPDATE collection SET label = "Capitol" WHERE artist = "Beatles";

The Beatles also released records on their own label Apple, but MySQL can only go so far to help you!

I have also discovered another problem with my table 'collection'. I didn't make the field 'condition' big enough to include the word 'unbelievable'. Now if MySQL is nice enough to give us 255 characters to use, why should I be skimping? I'll just add another 10 characters on to the field 'condition'.

ALTER TABLE collection MODIFY condition VARCHAR(20);

Of course, the adjective 'unbelievable' is rather vague, but I still have room to use 'unbelievably good' or 'unbelievably bad'.

There actually may be a good reason for skimping. If you've got an application for data entry and your users are only going to enter a short explanation in some field, it would be a waste to create a TEXT field for it if you can keep it to under 255 characters and used a VARCHAR. MySQL will give you better performance. Of course, if there's any chance that you will need more space, it is best to aim high.

User Management

Root isn't the only one who can use MySQL. You can also give other users certain privileges for databases and tables. To to this, open a MySQL shell and then go to the database named mysql and type: USE mysql. In this database, you'll find a table called 'user'. If you type DESCRIBE user you'll see the lists of fields in the table. Most of them are ENUM types and are the privileges you can give somebody to use MySQL. Let's say I wanted to give some other user the ability to do most anything with my album database, first, I'd have to enter this person into this table as a user.

INSERT INTO user VALUES('localhost','stan', PASSWORD('77JyC8'),'Y','Y','Y','Y', 
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

The 'Y's correspond to the privileges available to users. In this example, we've given the user 'stan' the ability to do anything. You may not want to give him that much freedom, so you may want to add an 'N' in there for DROP privileges (Y number 6), for example. It's your call if you've got root access to MySQL. Once you have given a user the rights you deem appropriate, you need invoke the following command to reset the privilege system:

FLUSH PRIVILEGES;

Now we can give him access on our album database

GRANT ALL ON album.* TO stan@localhost IDENTIFIED BY '77JyC8';

From now on the user 'stan' will be able to fire up a MySQL shell and do the what he's allowed to with any the album database's tables.

Miscellaneous MySQL features

MySQL has a few built-in features that may come in handy from time to time. For example, if you want to find out the version you're using, you could just type this query:

SELECT version();

If you're curious about the time and date, try this one:

SELECT now();

Actually, you can have a little fun with these SELECT queries. MySQL has got a lot of stuff built into it. Give some of these a whirl:

• SELECT CHAR(67); - will show you the letter who's ascii value is in the parenthesis

• SELECT ASCII("C"); - will do the opposite.

• SELECT rand() as a_Random_Number; - will show you, not surprisingly, a random number. Keep doing this and you'll see how random it is.

• SELECT LEAST(3,15,10); - will show you the smallest number of the three (just in case you can't tell by looking at it).

• SELECT POW(10,2); and SELECT POW(3,3); - will get you 10 squared and 3 cubed respectively

• SELECT REVERSE("breakfast"); - will give you an unpronounceable word while SELECT REVERSE("2002"); does essentially nothing.

• SELECT REPLACE("He left Portsmouth on a ship", "i", "ee"); - will get some laughs

• SELECT CONCAT("loo","ser"); - might get you some more

My goal here was to liven up the lesson a bit, but that's doesn't mean that some of these and others aren't important on certain occasions. Those dealing with time I find particularly important. Let's go back to our album database. If we're going to show this publicly, nobody is really going to want to see something like: Information last updated: 20031005164023. We can use some MySQL tricks to change a time stamp into something more readable.

SELECT date_format(updated, '%d %M %Y') AS Normal_Date FROM collection 
WHERE artist="Beatles";

And you'll see something like this:

+-----------------+
| Normal_Date     |
+-----------------+
| 10 October 2003 |
+-----------------+

Which is a lot more pleasant than 20031010140621

Using MySQL for projects

MySQL is the ideal database to use for any kind of projects like dynamic websites or web-based inventory programs. It is well suited to use with Perl and PHP for this kind of application. You can also use the C programming language to write stand-alone applications to use with it. We've really only scratched the surface here and you can already get a feel for how powerful it is. Now it's up to you to put that power to work for your own needs.