Introduction to MySQL (using Perl DBI)

MySQL is a relational database management system. It sounds fancy, but doesn't have to be. The novice, like me, can treat it as a black box. Put stuff in, ask for certain stuff out. It just works. That simple.

MySQL is based on a client/server model, so the black box is the MySQL server and your interface to it is the MySQL client program. All the examples of MySQL commands in this article can be entered straight into the MySQL client command line. MySQL supports multi-line commands and uses a semicolon to designate the end of the command.

Why MySQL and not another database system that supports SQL?

Because MySQL is free, well-supported and fast.

Even for a novice, it's probably more helpful to think of MySQL as a secretary ready to do your filing for you. You give her instructions on how to file the items. If you know that you'll be asking for items by their date, then tell the secretary to file by date and when you ask for things, it doesn't take her all day to find what you're looking for.

Data is organized into rows and columns, defining a matrix. In SQL-speak, the matrix is called a table.

Here's a typical C data structure:

struct users
{
int id;
char nickname[17]; // 16 char NULL terminated string
char password[17]; // ditto
int socks; // pairs of socks
int favorite_number;
};

It looks like user information collected by a web-site, perhaps.

The MySQL version of this structure is created with this command to the MySQL client program:

create table users
(
id int auto_increment not null,
nickname varchar(16) not null,
password varchar(16) not null,
socks int,
favorite_number int,
primary key (user_id),
unique (nickname)
);

See any similairities?

This is what it looks like as a row in MySQL:

+----+----------+----------+-------+-----------------+
| id | nickname | password | socks | favorite_number |
+----+----------+----------+-------+-----------------+

What is The Matrix?

The matrix of data for three hypothetical users:

+----+----------+----------+-------+-----------------+
| 1 | GdayMate | dingo | 57 | 42 |
+----+----------+----------+-------+-----------------+
| 2 | Javier | cigar | 1 | 945 |
+----+----------+----------+-------+-----------------+
| 3 | Rolo | pudding | 9 | 8 |
+----+----------+----------+-------+-----------------+

These tables are exactly what you'd see if you gave MySQL this command:

select * from users;

The asterisk means to select all the columns from the users table.

The table is the structural foundation for the multi-billion dollar a year database industry which includes companies like Oracle and Informix.