Dabbling in Live Databases: MySQL

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:



  1. 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).


  2. 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!

Latest Articles