The MySQL Model
This article was developed in a Linux environment with
MySQL 3.22; because we’re beginning with the basics,
most of the details should apply to MySQL in any Unix
environment, and probably even MySQL in Windows. MySQL is
not a subject that computer novices are likely to take up
on a holiday weekend, so we necessarily assume certain
background experience, including basic Perl, Unix/Linux, and
databases in general.
To best understand MySQL we should start from above, like
surveying a cityscape from a helicopter. MySQL operates
on a client-server model, not unlike many other network
applications. The MySQL server is the Grand Central Station
for the city of MySQL — the server handles incoming and
outgoing traffic to and from a database. Any machine which
you wish to process queries of a MySQL database must be
running the MySQL server; in other words, Grand Central
Station must be open for business for any traffic to arrive
or depart.
The MySQL server is all-powerful: it can create and destroy.
Databases are built, queried, and demolished using this
server. One potentially confusing matter for some newcomers
is that MySQL has no graphical interface; when we think of
databases in Windows such as Access, we think of database
creation and management in the model of the spreadsheet-like
visual interface. Not so with MySQL, whose server takes action
by commands only. Eventually, you may find that this is not
nearly so horrible as it sounds.
Like any network server, the MySQL server, named
mysqld, "listens" on a particular port for
incoming connection requests — the default port for MySQL
being 3306. If you think of the MySQL server like the
ticket manager at Grand Central Station, the listening port
is roughly akin to which window the ticket taker is
serving from. Unless you manually reconfigure to which port
the MySQL server listens, you won’t need to worry about
specifying a port when using a MySQL client.
The "client" in MySQL is whatever application is
sending a request to the database via the server.
There are several possible clients, then: your Perl script
could be a client, courtesy of the DBI module, for example.
The MySQL distribution also includes its own command-driven
client, eponymously named mysql, which is simply
an interactive interface for sending queries to a database.
Anytime you send commands to the MySQL server you are
using a client to do so, whether that is a Perl script or
the included client, think of the client as the messenger
boy between your wishes and the all-powerful server.
Starting at Zero: Birth of a Database
So you’re starting from nothing — how do you go about
creating a database with MySQL? After all, you can’t
query anything if there is nothing yet to query! We’ll
detail this process shortly, but first let’s consider the
bird’s eye view. At the most abstract level, creating a
database is a two-step process:
Create the skeletal structure for the data. Remember that
data in a relational database is stored inside one
or more tables, and each table has a data structure.
The table’s structure defines what data fields are
available for each record, and what types of data may
reside in these fields (integers, real numbers, text, and
so on).
Once the table structure is established, you can fill or
populate the table with actual data. Sounds
simple enough.
How do you populate a table with data using the MySQL server?
There are two possibilities:
1. The SQL insert/replace
statements are used to add data to an
existing table, either in new rows
or overwriting existing rows.
2. If your data already exists in a
delimited text file, such as tab-delimited or
comma-delimited, you can import
it into an existing MySQL table. Your MySQL
distribution may include a standalone utility
named mysqlimport
which can insert your text file into a named
table. Alternatively, you can use the
load data infile statement
from within a MySQL client to import data
from a text file into a table. Although there
are no filters provided
for importing other database formats, such
as Access or Oracle, you can usually export
from those databases into
a text file, and then import that text file
into MySQL after you have recreated those
table structures in a MySQL
database.
That’s the short of it. Now we’re ready for the details, the
dirty gritty bits that get stuck between the keys.
In other words, the fun stuff!
