63. | CGI Programming with Perl

CGI Programming with Perl

10.3. Introduction to SQL

Because of the sheer number of different database systems that exist, most database vendors have standardized on a query language (SQL) to update as well as access their databases. Before we go any further, let's look more deeply into how this query language is used to communicate with various database systems.

SQL is the standardized language to access and manipulate data within relational database systems. The original SQL prototype defined a "structured" language, thus the term Structured Query Language, but this is no longer true of the current SQL-92 standard. SQL was designed specifically to be used in conjunction with a primary high-level programming language. In fact, most of the basic constructs that you would find in a high-level language, such as loops and conditionals, do not exist in SQL.

All major commercial relational database systems, such as Oracle, Informix, and Sybase, and many open source databases, such as PostgreSQL, MySQL, and mSQL, support SQL. As a result, the code to access and manipulate a database can be ported easily and quickly to any platform. Let's look at SQL.

10.3.1. Creating a Database

We will start out by discussing how a database is created. Suppose you have the following information:

Player	Years	Points	Rebounds	Assists	Championships
Larry Bird	12	28	10	7	3
Magic Johnson	12	22	7	12	5
Michael Jordan	13	32	6	6	6
Karl Malone	15	26	11	3	0
Shaquille O'Neal	8	28	12	3	0
John Stockton	16	13	3	11	0

The SQL code to create this database is:

create table Player_Info (     Player                    varchar (30) not null,     Years                     integer,     Points                    integer,     Rebounds                  integer,     Assists                   integer,     Championships             integer );

The create table command creates a database, or a table. The Player field is stored as a non-null varying character string. In other words, if the data in the field is less than thirty characters, the database will not pad it with spaces, as it would for a regular character data type. Also, the database forces the user to enter a value for the Player field; it cannot be empty.

The rest of the fields are defined to be integers. Some of the other valid data types include datetime, smallint, numeric, and decimal. The numeric and decimal data types allow you to specify floating-point values. For example, if you want a five-digit floating-point number with a precision to the hundredth place, you can specify decimal (5, 2).

10.3.2. Inserting Data

Before we discuss how to obtain data from a database table, we need to discuss how to populate the database in the first place. In SQL, we do this with the insert statement. Say we need to add another player to the database. We could do it this way:

insert into Player_Info     values     ('Hakeem Olajuwon', 16, 23, 12, 3, 2);

As you can see, it is very simple to insert an element into the table. However, if you have a database with a large number of columns, and you want to insert a row into the table, you can manually specify the columns:

insert into Player_Info     (Player, Years, Points, Rebounds, Assists, Championships)     values     ('Hakeem Olajuwon', 10, 27, 11, 4, 2);

When used in this context, the order of the fields does not necessarily have to match the order in the database, as long as the fields and the values specified match each other.

10.3.3. Accessing Data

The language required for accessing data has a lot more features than what we have discussed so far for simply creating and inserting data into a table. These additional elements make SQL an incredibly rich language for retrieving data once it is stored inside of database tables. We will also see later that updating and deleting data relies on the information in this section in order to determine which rows in a table actually become modified or removed from the database.

Let's say you want a list of the entire database. You can use the following code:

select *      from Player_Info;

The select command retrieves specific information from the database. In this case, all columns are selected from the Player_Info database. The "*" should be used with great caution, especially on large databases, as you might inadvertently extract a lot of information. Notice that we are dealing only with columns, and not rows. For example, if you wanted to list all the players in the database, you could do this:

select Player     from Player_Info;

Now, what if you want to list all the players who scored more than 25 points? Here is the code needed to accomplish the task:

select *     from Player_Info     where Points > 25;

This would list all the columns for the players who scored more than 25 points:

Player	Years	Points	Rebounds	Assists	Championships
Larry Bird	12	28	10	7	3
Michael Jordan	13	32	6	6	6
Karl Malone	15	26	11	3	0
Shaquille O'Neal	8	28	12	3	0

But, say you wanted to list just the Player and Points columns:

select Player, Points     from Player_Info     where Points > 25;

Here is an example that returns all the players who scored more than 25 points and won a championship:

select Player, Points, Championships     from Player_Info     where Points > 25     and Championships > 0;

The output of this SQL statement would be:

Player	Points	Championships
Larry Bird	28	3
Michael Jordan	32	6

You could also use wildcards in a select command. For example, the following will return all the players that have a last name of "Johnson":

select *     from Player_Info     where Player like '% Johnson';

This will match a string ending with "Johnson".

10.3.4. Updating Data

Let's suppose that Shaquille O'Neal won a championship. We need to update our database to reflect this. This is how it can be done:

update Player_Info     set Championships = 1     where Player = 'Shaquille O''Neal';

Note the where clause. In order to modify data, you have to let SQL know what rows will be set to new values. To do this, we use the same syntax that is used to access data in a table except that instead of retrieving records, we are just changing them. Also note that we must escape a single quote by using another single quote.

SQL also has methods to modify entire columns. After every basketball season, we need to increment the Years column by one:

update Player_Info     set Years = Years + 1;

10.3.5. Deleting Data

If you wanted to delete "John Stockton" from the database, you could do this:

delete from Player_Info     where Player = 'John Stockton';

If you want to delete all the records in the table, the following statement is used:

delete from Player_Info;

And finally, the drop table command deletes the entire database:

drop table Player_Info;

For more information on SQL, see the reference guide on SQL-92 at http://sunsite.doc.ic.ac.uk/packages/perl/db/refinfo/sql2/sql1992.txt.


10.2. DBM Files		10.4. DBI