|
Introduction to MySQL (using Perl DBI)
My theory is, if the title doesn't sound alien to you, then you're
probably in the right place.
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.
The best way for a C programmer to think of it is:
Each row is a structure reference.
Every column is a member of that structure.
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 |
+----+----------+----------+-------+-----------------+
That's it? Yup. In fact, 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.
|
