Elements of MySQL and Its Environment

You need to master several skills to run a database system. In this section, we’ll lay out what goes into using MySQL and how we meet those needs in this book.

A MySQL installation has two components: a server that manages the data, and clients that ask the server to do things with the data, such as change entries or provide reports. The client that you’ll probably use most often is the mysql MySQL monitor program, provided by the MySQL AB company and available in most MySQL installations. This allows you to connect to a MySQL server and run SQL queries. Other simple clients are included in a typical installation; for example, the mysqladmin program is a client that allows you to perform various server administration tasks.

In fact, any program that knows how to talk to the MySQL server is a client; a program for a web-based shopping site or an application to generate sales graphs for a marketing team can both be clients. In Chapter 3, you’ll learn to use the MySQL monitor client to access the MySQL server. In Chapters 13 through 15, we’ll look at how we can use PHP to write our own custom clients that run on a web server to present a web frontend to the database for this. We’ll use the Apache web server (http://httpd.apache.org). Apache has a long history of reliable service and has been the most popular web server in the world for over 10 years. The Apache web server—or HTTP server— project is managed by the Apache Foundation (http://www.apache.org). Although the web server and MySQL server are separate programs and can run on separate computers, it’s common to find small- to medium-scale implementations that have both running on a single computer. In Chapters 16 through 18, we’ll explore how the Perl programming language can be used to build command-line and web interfaces to the MySQL server.

To follow the content in this book, you will need some software; fortunately, all the software we use is open source, free for noncommercial use, and easily downloaded from the Internet. To cover all parts of this book, you need a MySQL database server, Perl, and a web server that can talk to MySQL using the PHP and Perl programming languages. We’ll explore four aspects of using MySQL:

MySQL server

We explain how to create your own MySQL installation, and how to configure and administer it.

SQL

This is the core of MySQL use, and the major topic in this book. It’s introduced in Structured Query Language.”

Programming languages

SQL is not a simple or intuitive language, and it can be tedious to repeatedly perform complex operations. You can instead use a general-purpose programming language such as PHP or Perl to automatically create and execute SQL queries on the MySQL server. You can also hide the details of the interaction with the database behind a user-friendly interface. We’ll show you how to do this.

Web database applications

We explain how you can use PHP or Perl to create dynamic, database-driven web applications that can publish information from the database to the Web, and capture information provided by users.

HTML is the lingua franca of the Web. Although learning HTML is not within the scope of this book, there are many great HTML guides available, including HTML and XHTML: The Definitive Guide by Chuck Musciano (O’Reilly). We recommend that you pick up the basics of HTML before reading Chapters 13, 14, 15, or 18.

The LAMP Platform

It’s very common to find web database applications developed using the Linux operating system, the Apache web server, the MySQL database management system, and the Perl or PHP scripting language. This combination is often referred to by the acronym LAMP, a term invented at O’Reilly Media.

Linux is the most common development and deployment platform, but as we’ll show in this book, you can run all the tools on other operating systems. In fact, we’ll give directions for getting started on Linux, Windows, and Mac OS X. Most of the content in this book can be used for other operating systems with little modification.

The P in LAMP originally stood for Perl, but over the past decade, users have increasingly turned to PHP for developing dynamic web pages. PHP is very clean and efficient for retrieving data and displaying it with minimal processing. If you have to do heavy data crunching after the data is returned from MySQL, Perl may still be a better choice. We discuss PHP and Perl largely independently; you can pick up one without needing to learn the other, although we believe that you’ll benefit from learning both languages. In fact, almost any modern language can be used to perform this task; most of them have the necessary interfaces to both web servers and database engines.

Structured Query Language

IBM is to be credited not only with inventing the relational database, but developing the language still used today to interact with such databases. SQL is a little odd, bearing the stylistic marks of its time and its developers. It’s also gotten rather bloated over the years—a process made worse by its being standardized (multiple times)—but in this book we’ll show you the essentials you really need and help you become fluent in them.

SQL shows many of the problems that are commonly attributed to computing standards: it tries to accomplish too much, it forces new features into old molds to maintain backward compatibility, and it reflects uneasy compromises and trade-offs among powerful vendors. As a result, there are several standards that database management systems can adhere to. SQL-92 dates back to 1992 and provides just about everything that you will need for beginning work. However, it lacks features demanded by some modern applications. SQL:1999 was standardized in 1999 and adds a huge number of new features, many of them considered overkill by some experts. There is also a more recent standard, SQL:2003, that was published in 2003 and adds support for XML data.

Each development team has to decide on the trade-offs between the features requested by users and the need to keep software fast and robust, and so database engines generally don’t conform totally to any one standard. Furthermore, historical differences have stayed around in legacy database engines. That means that even if you use fairly simple, vanilla SQL, you may have to spend time when porting your skills and your code to another database engine.

In this book, we’ll show you how to use MySQL’s flavor of SQL to create databases and store and modify data. We’ll also show you how to use this SQL variant to administer the MySQL server and its users.

Get Learning MySQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.