The DBMS we use in this book is SQL, the Structured Query Language (the acronym may be pronounced either "sequel" or "ess-queue-ell"; both sides are bitterly adamant about their pronunciation, and whichever way you say "SQL," someone will correct your pronunciation). The version of SQL that we use is MySQL, and its creators specifically ask that it be pronounced "my-ess-queue-ell."
SQL is an easy language to learn. The queries seem like English, albeit broken English. Most commands are obvious, such as select , group by , and where . The next chapter explores this syntax. The popularity of SQL ensures that our investment in learning it will not be wasted and that plenty of resources are available to support our efforts.
Another benefit of SQL is that is an open standard, and there are several open-source ver sions available. Although this does not mean that all versions of SQL are free, it does mean that some are. Besides the monetary advantage, open source means that many specialized functions may have been added to the version of SQL you use (for example, a hypothetical command like REPLACE ). It is important to note that if we use any special commands, we must install that version of SQL when we transfer our code to another server, and we lose many of the benefits of standardization.
SQL is a standard. It has a set of commands, defined by ANSI, that all versions of SQL should obey. We can reuse our SQL knowledge on other projects in many deveopment environments. Most languages (C++, Perl, ASP, PHP, Visual Basic, and so on) provide SQL support. They all have functions (or libraries with functions) that allow us to interface easily to the SQL database.
SQL is a speedy language. It is generally faster than the alternatives, such as OBDC, an object-oriented database programming system. Many require several stages of translation between the underlying database model, the access language, and the application. SQL is more direct.
Perhaps best of all, SQL is more than a DBMS. It is an R DBMS, a Relational DataBase Man agement System. Relational describes how the database is organized. Before we can really get into the relational aspect of SQL, we have to look at the parts of a database. What is there to know about a database? A lot. We might think of a database as a spreadsheet (e.g., Excel), but we'd be wrong.
In reality, a database is a collection of tables. Each table is what we typically think of when we imagine a simple spreadsheet. It has both rows and columns . A row is a record. It is a single instance of whatever the table is trying to store. For instance, our Quiz database will have a table of Questions. In the table, a single row would correspond to a single question.
Each column is a unique data element within the record. For instance, our table of questions might have a column for the actual question text, a separate column for the name of its author, and yet another column containing the time each question was added. Each column stores one type of value. It may hold an integer, a floating-point number, a string, a date/time, or a binary file.
In addition, each column has several parameters. One is the NULL setting, which determines whether or not the value NULL is permitted in case the value of that column is unknown or nonexistent.
Another parameter is the constraint. A constraint parameter produces an error message when trying to add a new row unless the value in the column is within the constraint. Suppose AOL pays us a lot of money so that only its subscribers can play our game. (We can dream!) We could add the constraint to the player's email-address column that its contents must include "@aol.com". Or, less fancifully, a gender column might accept only "M" and "F". This allows us to put checks on the input to a table.
There is also an AUTO_INCREMENT parameter. It enables this function: When a row assigns a NULL value to the column, the value is changed to that of the highest value in the column plus 1. Obviously, AUTO_INCREMENT works only on numerical values, and it functions only when the application supplies a NULL value to the column.
Similar to the AUTO_INCREMENT parameter is the UNIQUE parameter. (They are often used in conjunction.) UNIQUE ensures that no two rows in a column have the same value. It can work on numeric or any other type of data. It is especially useful when creating keys.