Relational Databases and SQL

SQL is the language of relational databases. A simple query like a one-table SELECT will be more or less the same whether you’re using a tiny database
like mSQL or an expensive behemoth like Oracle. The big advantage for you, the web developer, is that, after you learn SQL, you will be able to interact with numerous databases across all platforms without a steep retraining curve. Just imagine how horrible life would be if Oracle, MySQL, and SQL Server all had entirely different sets of commands for putting data in and getting data out of their stores — as if Oracle used SELECT to ask for data sets, MySQL used VALJ (the developers are Swedish, you know), and SQL Server used FIND IT IN THIS TABLE (to better match the vocabulary of Windows). SQL is the common vocabulary and syntax that will save you from this nightmare. There are differences among products, and in their implementations of the SQL standard and the extensions they each define to that standard, but it’s better to have 80 percent in common and 20 percent different than the other way around.

SQL Standards

According to Andrew Taylor, original inventor of SQL, SQL does not stand for Structured Query Language (or anything else for that matter). But for the rest of the world, it does now. As you would expect from the (non-) title, SQL represents a stricter and more general method of data storage than the previous standard of flat-file DBM-style databases. SQL is a standard under both the American National Standards Institute (ANSI) and the Equipment Managers Council of America (ECMA); both are international standards-maintenance organizations. You can read the standards on payment of a fee to these organizations:

• www.ansi.org
• www.ecma.org

However, within the general guidelines of the standard there are considerable differences among the products of individual companies and open source database development organizations. The past few years, for instance, have seen the rapid growth of so-called object-relational databases, as well as
of SQL products specifically slanted toward the web market. The key to choosing a database is to be selfish, or at least supremely self-centered. You will see plenty of unusually virulent postings out there opining that a certain advanced database feature (like triggers or cross joins) is a “must,” and any SQL installation without this feature hardly deserves the name. Take this stuff with a grain of salt. It’s far better to make a blind shopping list of functions you need in order of importance and then go out looking for the product that best meets your requirements.

That said, a good deal of SQL really is pretty standardized. You will be using a few SQL statements over and over and over, no matter which specific product you choose to deploy.

Installing MySQL on Microsoft Windows

MySQL installation on Windows is much, much easier than it used to be thanks to fully automated installers

Installing MySQL on Windows

Default installation on any version of Windows is now much easier than it used to be, as MySQL now comes neatly packaged with a native Windows installer. Simply download the installer package, usually an msi, and run it. This will walk you through the trivial process and by default will install everything under C:\Program Files\MySQL, which is probably as good a place as any. The MySQL installer will attempt to install itself as a service, which means you need Administrator rights on the computer upon which MySQL is being installed. Part of the installation process will configure the MySQL server. During this portion of the installation, you can configure things like the root password, the port on which MySQL will listen, and whether to include the MySQL utilities in the Windows path (I recommend that you do so). The Windows install is now so simplified that for most cases you can simply click “Next” to continue and, where you have an exception, refer to the online manual for MySQL at www.mysql.com.

Installing MySQL

Obtaining MySQL

I strongly recommend using the MySQL server package directly from your Linux distribution rather than downloading from MySQL AB unless you have a very specific reason for using a different version. If you can’t think what one of those specific reasons might be, then you probably don’t have one, and you therefore should use the MySQL server available with your distribution.

Installing MySQL on Linux

There are several distributions upon which you might find yourself installing MySQL. It’s always a challenge choosing which distributions to cover. No matter which ones we decide to cover there will always be someone installing on another distribution.

In this section I’ll examine MySQL installation on Debian, CentOS, and Ubuntu. Additionally, I’ll demonstrate compiling MySQL from source for those who don’t have a MySQL server package available with their distribution. It should be noted that because MySQL 6 is so new it may not be available as a package in your distribution. If this is the case, I recommend sticking with the latest MySQL available for your distribution. For the most part, this book will use functions available in MySQL 5 and later, so MySQL 6 isn’t a requirement. Where MySQL 6 is required, a special note will be shown.

Installing MySQL Server on Debian and Ubuntu

Debian’s dpkg and apt installation and package management tools make installation of MySQL (and everything else for that matter) incredibly easy. Debian is a system administrator’s dream because it’s so stable, package installation is so easy, and the packages are maintained and configured with excellent defaults. But enough evangelizing; installation of MySQL server on Debian requires superuser
privileges and is accomplished simply by running apt-get:

apt-get install mysql-server

Of course, that assumes that you have correctly configured sources in /etc/apt/sources.list. For more information on APT and configuration of the sources.list file, see www.debian.org/ doc/manuals/apt-howto/ch-basico.en.html. Debian’s package management system will install and configure any necessary prerequisites for you.

Debian separates MySQL into its components such as server, client, and libraries. Therefore, in order to use MySQL and PHP together, you should install the php5-mysql package:

apt-get install php5-mysql

As you can see by that installation command, the PHP5 version of the interface is being installed. That is the latest version available as of this writing.
Finally, you’ll likely also want to install the MySQL command-line interface (CLI), which is accomplished by installing the mysql-client package:

apt-get install mysql-client

MySQL will now be installed and ready to use on your Debian server. However, by default the MySQL server won’t listen on anything by localhost. To change this, edit /etc/mysql/my.cnf and comment out the skip-networking line with a pound sign or hash mark (#), so it looks like this:

#skip-networking

Now restart the MySQL server by typing this command:

/etc/init.d/mysql restart