home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Book HomeCGI Programming with PerlSearch this book

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.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".



Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.