Database for $100, Please

SQL stands for Structured Query Language. SQL is the lingua franca (that's not a type of pasta-it's a type of tongue) of relational databases. SQL is the standard language used for creating and accessing relational databases and is the foundation of database processing in Java.

Java provides JDBC-Java DataBase Connectivity-that lets you formulate SQL statements, send them off to a database server, and process the results. But in order to use JDBC, you need to know some basic concepts of SQL databases and a least enough SQL to formulate some sensible SQL statements.

This chapter won't make you a database guru or a SQL expert. SQL is a complicated language that is the subject of many of its own books, including SQL For Dummies, 6th Edition, by Allen G. Taylor (Wiley). This chapter covers just enough SQL to get you going with JDBC. Also, this chapter doesn't cover JDBC. I decided to defer that until the next chapter so that if you already know SQL, you can skip this chapter altogether.

What Is a Relational Database?

The term relational database is one of the most used and abused buzzwords in all of computerdom. A relational database can be

• A database in which data is stored in tables. Relationships can be established between tables based on common information. For example, a table of customers and a table of invoices might both contain a customer number column. This column can serve as the basis for a relationship between the tables.
• A database that is accessed via Structured Query Language (SQL). SQL was originally invented by IBM back in the 1970s to provide a practical way to access data stored in relational databases.
• Any database system developed since about 1980, with the exception of a few cutting-edge object-oriented databases. Marketers quickly figured out that the way to sell database programs was to advertise them as relational. Thus just about every database program ever made has claimed to be relational, whether it really is or not.

From a Java programmer's perspective, the second definition is the only one that matters. If you can use SQL to access the database, the database is relational.

What Is SQL, and How Do You Pronounce It?

SQL is a query language, which means it is designed to extract, organize, and update information in relational databases. Way back in the 1970s, when SQL was invented (SQL is old enough to be Java's grandfather), SQL was supposed to be an English-like query language that could be used by untrained end users to access and update relational database data without the need for programming. Of course, that didn't happen. SQL is nothing like English. It's way too complicated and esoteric for untrained end users to learn, but it has become the overwhelming favorite among programmers.

Ever since you first saw the letters SQL, you've probably been wondering how to pronounce it. If not, humor me. Two schools of thought exist on this subject:

• Spell out the letters: Es–Que–El.
• Pronounce it like the word sequel.

Either one does the job, but sequel makes you sound less like a database rookie.

SQL Statements

Unlike Java, SQL is not object-oriented. Remember, SQL was invented during the Nixon administration. However, like Java, SQL does use statements to get work done. Table 3-1 lists the SQL statements you use most often.

Table 3-1: Common SQL Statements
Open table as spreadsheet

SQL Statement	Description
Data Manipulation
select	Retrieves data from one or more tables. This is the statement you use most often.
insert	Inserts one or more rows into a table.
delete	Deletes one or more rows from a table.
update	Updates existing rows in a table.
Data Definition
create	Creates tables and other database objects.
alter	Changes the definitions of a table or other database object.
drop	Deletes a table or other database object.
use	Used in scripts to indicate what database subsequent statements apply to.

Note that unlike Java, statements in SQL are not case-sensitive. Thus, you can write select, Select, or SELECT. You could even write sElEcT for kicks, if you want.

Creating a SQL Database

Before you can store data in a relational database, you must create the database. You don't normally do that from a Java program. Instead, you do it by writing a script file that contains the Create statements necessary to create the table, and then run the script through the database server's administration program. (Note that some database servers also let you define databases interactively. However, the script approach is preferred because you often need to delete and re-create a database while testing your applications.)

Tip 

The scripts shown in this section (and in the rest of this chapter) are for version 5.0 of MySQL. MySQL is a free SQL database server you can download from http://www.mysql.com. MySQL includes a program called the MySQL Command Line Client that lets you enter SQL commands from a prompt and immediately see the results.

Tip 

Script statements end with semicolons. That's about the only thing SQL scripts have in common with Java. Be aware, however, that the semicolon isn't required when you use SQL statements in a Java program. The semicolon is required only when you use SQL statements in a script or interactively from the MySQL Command Line Client program.

I don't have room in this book to go into a complete tutorial on writing scripts that create SQL databases. So instead, I present a sample script, Listing 3-1, that creates a database named movies that's used in the rest of this chapter and in the next, and walk you through its most important lines.

Listing 3-1: A Database Creation Script

drop database movies; →1
create database movies; →2
use movies; →3
create table movie ( →4
 id int not null auto_increment, →5
 title varchar(50), →6
 year int, →7
 price decimal(8,2), →8
 primary key(id) →9
);

insert into movie (title, year, price) →12
 values ("It's
insert into movie (title, year, price)
 values ("The Great Race", 1965, 12.95);
insert into movie (title, year, price)
 values ("Young Frankenstein", 1974, 16.95);
insert into movie (title, year, price)
 values ("The Return of the Pink Panther", 1975, 11.95);
insert into movie (title, year, price)
 values ("Star Wars", 1977, 17.95);
insert into movie (title, year, price)
 values ("The Princess Bride", 1987, 16.95);
insert into movie (title, year, price)
 values ("Glory", 1989, 14.95);
insert into movie (title, year, price)
 values ("Apollo 13", 1995, 19.95);
insert into movie (title, year, price)
 values ("The Game", 1997, 14.95);
insert into movie (title, year, price)
 values ("The Lord of the Rings: The Fellowship of the
 Ring",
 2001, 19.95);

The following paragraphs describe the important lines of this script:

→ 1	It's common for a script that creates a database to begin with a drop database statement to delete any existing database with the same name. During testing, it's common to delete and re-create the database, so you want to include this statement in your scripts.
→ 2	This statement creates a new database named movies.
→ 3	The use statement indicates that the script statements that follow applies to the newly created movies database.
→ 4	This create table statement creates a table named movie with columns named id, title, year, and price. This statement also specifies that the primary key for the table is the id column.
→ 5	The id column's data type is int, which corresponds to Java's int type. This column also specifies not null, which means that it must have a value for every row, and it specifies auto increment, which means that the database server itself provides values for this column. Each time a new row is added to the table, the value for the id column is automatically incremented.
→ 6	The title column's data type is varchar, which is like a Java String.
→ 7	The year column's data type is int.
→ 8	The price column's data type is decimal. Java doesn't have a decimal type, so the values from this column are converted to double.
→ 9	The create table statement specifies that the id column is the table's primary key. A primary key is a column (or a combination of columns) that contains a unique value for each row in a table. Every table should have a primary key.
→ 12	The insert statements add data to the database. Each of these ten statements adds a row to the movie table. The syntax of the insert statement is weird, because you first list all the columns that you want to insert data for, and then you list the actual data. For example, each of the insert statements inserts data for three columns: title, year, and price. The first insert statement (the one in line 12) inserts the values "It's a Wonderful Life", 1946, and 14.95.

To run this script in MySQL, start the MySQL Command Line Client from the Start menu. Then use a source command that names the script. For example:

mysql> source c:datacreate.sql

Querying a Database

As the name Structured Query Language suggests, queries are what SQL is all about. A query is an operation that is performed against one or more SQL tables that extracts data from the tables and creates a result set containing the selected rows and columns. A crucial point to understand is that the result set is itself a table consisting of rows and columns. When you query a database from a Java program, the result set is returned to the program in an object created from the ResultSet class. This class has methods that let you extract the data from each column of each row in the result set.

Using your basic select

To query a database, you use the select statement. In the select statement, you list the table or tables from which you want to retrieve the data, the specific table columns you want to retrieve (you might not be interested in everything that's in the table), and other clauses that indicate which specific rows to retrieve, what order to present the rows in, and so on. Here's a simple select statement that lists all the movies in the movie table:

select title, year
 from movie
 order by year;

When you take this statement apart piece by piece, you get:

• select title, year names the columns you want included in the query result.
• from movie names the table you want the rows retrieved from.
• order by year indicates that the result is sorted into sequence by the year column so the oldest movie appears first.

In other words, this select statement retrieves the title and date for all the rows in the movie table and sorts them into year sequence. You can run this query by typing it directly into the MySQL Command Line Client. Here's what you get:

mysql> select title, year from movie order by year;
+---------------------------------------------------+------ +
| title | year |
+---------------------------------------------------+------ +
| It's a Wonderful
| The Great Race | 1965 |
| Young Frankenstein | 1974 |
| The Return of the Pink Panther | 1975 |
| Star Wars | 1977 | 
| The Princess Bride | 1987 |
| Glory | 1989 |
| Apollo 13 | 1995 |
| The Game | 1997 |
| The Lord of the Rings: The Fellowship of the Ring | 2001 |
+---------------------------------------------------+------+
10 rows in set (0.09 sec)

As you can see, the Command Line Client displays the rows returned by the select statement. This can be very handy when you're planning the select statements your program needs or when you're testing a program that updates a table and you want to make sure the updates are made correctly.

Warning 

If you want the query to retrieve all the columns in each row, you can use an asterisk instead of naming the individual columns:

	select * from movie order by year;

Using an asterisk in this manner in a program is not a good idea, however, because the columns that make up the table might change. If you use an asterisk, your program can't deal with changes to the table's structure.

Both examples so far include an order by clause. In a SQL database, the rows stored in a table are not assumed to be in any particular sequence. As a result, if you want to display the results of a query in sequence, you must include an order by in the select statement.

Narrowing down the query

Suppose you want to find information about one particular video title. To select certain rows from a table, use the where clause in a select statement. For example:

mysql> select title, year from movie
 -> where year <= 1980
 -> order by year;
+--------------------------------+------+
| title | year |
+--------------------------------+------+
| It's a Wonderful
| The Great Race | 1965 |
| Young Frankenstein | 1974 |
| The Return of the Pink Panther | 1975 |
| Star Wars | 1977 |
+--------------------------------+------+
5 rows in set (0.00 sec)

Here the select statement selects all the rows in which the year column is less than or equal to 1980. The results are ordered by the year column.

Excluding rows

Perhaps you want to retrieve all rows except those that match certain criteria. For example, here's a query that ignores movies made in the 1970s (which is probably a good idea):

mysql> select title, year from movie
 -> where year < 1970 or year > 1979
 -> order by year;
+---------------------------------------------------+------+
| title | year |
+---------------------------------------------------+------+
| It's a Wonderful
| The Great Race | 1965 |
| The Princess Bride | 1987 |
| Glory | 1989 |
| Apollo 13 | 1995 |
| The Game | 1997 |
| The Lord of the Rings: The Fellowship of the Ring | 2001 |
+---------------------------------------------------+------+
7 rows in set (0.41 sec)

Singleton selects

When you want to retrieve information for a specific row, mention the primary key column in the where clause, like this:

mysql> select title, year from movie where id = 7;
+-------+------+
| title | year |
+-------+------+
| Glory | 1989 |
+-------+------+
1 row in set (0.49 sec)

Here the where clause selects the row whose id column equals 7. This type of select statement is called a singleton select because it retrieves only one row. Singleton selects are commonly used in Java programs to allow users to access or update a specific database row.

Sounds like

Suppose you want to retrieve information about a movie, but you can't quite remember the name. You know it has the word princess in it, though. One of the more interesting variations of the where clause is to throw in the word like, which lets you search rows using wildcards. Here's an example in which the percent sign (%) is a wildcard character:

mysql> select title, year from movie
 -> where title like "%princess%";
+--------------------+------+
| title | year |
+--------------------+------+
| The Princess Bride | 1987 |
+--------------------+------+
1 row in set (0.00 sec)

Column functions

What if you want a count of the total number of movies in the movie table? Or a count of the number of movies that were made before 1970? To do that, you use a column function. SQL's column functions let you make calculations on columns. You can calculate the sum, average, or largest or smallest value, or count the number of values for an entire column. Table 3-2 summarizes these functions. Note that these functions operate on the values returned in a result set-which isn't necessarily the entire table.

Table 3-2: Column Functions
Open table as spreadsheet

Function	Description
sum(column-name)	Adds the values in the column.
avg(column-name)	Calculates the average value for the column. Null values are not figured in the calculation.
min(column-name)	Determines the lowest value in the column.
max(column-name)	Determines the highest value in the column.
count(column-name)	Counts the number of rows that have data values for the column.
countDistinct (column-name)	Counts the number of distinct values for the column.
count(*)	Counts the number of rows in the result set.

To use one of these functions, specify the function rather than a column name in a select statement. For example, the following select statement calculates the number of rows in the table and the year of the oldest movie:

mysql> select count(*), min(year) from movie;
+----------+-----------+
| count(*) | min(year) |
+----------+-----------+
| 10 | 1946 |
+----------+-----------+
1 row in set (0.00 sec)

As you can see, ten movies are in the table, and the oldest was made in 1946.

If the select statement includes a where clause, only the rows that match the criteria are included in the calculation. For example, this statement finds out how many movies in the table were made before 1970:

mysql> select count(*) from movie where year < 1970;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

The result is only two.

Selecting from more than one table

In the real world, most select statements retrieve data from two or more tables. Suppose you want a list of all the movies you've currently loaned out to friends. To do that, you have to create another table in your database that lists your friends' names and the ids of any movies they've borrowed. Here's a create table statement that creates just such a table:

create table friend (
 lastname varchar(50),
 firstname varchar(50),
 movieid int
);

Now load it up with some data:

insert into friend (lastname, firstname, movieid)
 values ("Haskell", "Eddie", 3);
insert into friend (lastname, firstname, movieid)
 values ("Haskell", "Eddie", 5);
insert into friend (lastname, firstname, movieid)
 values ("Cleaver", "Wally", 9);
insert into friend (lastname, firstname, movieid)
 values ("Mondello", "Lumpy", 2);
insert into friend (lastname, firstname, movieid)
 values ("Cleaver", "Wally", 3);

With that out of the way, you can get to the business of using both the friend and movie tables in a single select statement. All you have to do is list both tables in the from clause, and then provide a condition in the where clause that correlates the tables. For example:

mysql> select lastname, firstname, title
 -> from movie, friend 
 -> where movie.id = friend.movieid;
+----------+-----------+-------------------- +
| lastname | firstname | title |
+----------+-----------+-------------------- +
| Haskell | Eddie | Young Frankenstein |
| Haskell | Eddie | Star Wars |
| Cleaver | Wally | The Game |
| Mondello | Lumpy | The Great Race |
| Cleaver | Wally | Young Frankenstein |
+----------+-----------+-------------------- +
5 rows in set (0.00 sec)

Here you can see which movies have been lent out and who has them. Notice that the id and movieid columns in the where clause are qualified with the name of the table the column belongs to.

Here's a select statement that lists all the movies Eddie Haskell has borrowed:

mysql> select title from movie, friend
 -> where movie.id = friend.movieid
 -> and lastname = "Haskell";
+--------------------+
| title |
+--------------------+
| Young Frankenstein |
| Star Wars |
+--------------------+
2 rows in set (0.00 sec)

That rat has two of your best movies! Notice in this example that you can refer to the friend table in the where clause even though you're not actually retrieving any of its columns. However, you must still mention both tables in the from clause.

Eliminating duplicates

If you want to know just the names of everyone who has a movie checked out, you can do a simple select from the friend table:

mysql> select lastname, firstname from friend;
+----------+-----------+
| lastname | firstname |
+----------+-----------+
| Haskell | Eddie |
| Haskell | Eddie |
| Cleaver | Wally |
| Mondello | Lumpy |
| Cleaver | Wally |
+----------+-----------+
5 rows in set (0.00 sec)

However, this result set has a problem: Eddie Haskell and Wally Cleaver are listed twice. Wouldn't it be nice if you could eliminate the duplicate rows? Your wish is granted in the next paragraph.

Tip 

You can eliminate duplicate rows by adding the distinct keyword in the select statement:

mysql> select distinct lastname, firstname from friend;
+----------+-----------+
| lastname | firstname |
+----------+-----------+
| Haskell | Eddie |
| Cleaver | Wally |
| Mondello | Lumpy |
+----------+-----------+
3 rows in set (0.07 sec)

Notice that no duplicates appear; each distinct name appears only once in the result set.

Updating and Deleting Rows

You've already seen how to create databases, insert rows, and retrieve result sets. All that remains now is updating and deleting data in a table. For that, you use the update and delete statements, as described in the following sections. I explain the delete statement first, because it has a simpler syntax.

The delete statement

The basic syntax of the delete statement is

delete from table-name where condition;

For example, here's a statement that deletes the movie whose id is 10:

mysql> delete from movie where id = 10;
Query OK, 1 row affected (0.44 sec)

Notice that the Command Line Client shows that this statement affected one line. You can confirm that the movie was deleted by following up with a select statement:

mysql> select * from movie;
+----+--------------------------------+------+-------+
| id | title | year | price |
+----+--------------------------------+------+-------+
| 1 | It's a Wonderful
| 2 | The Great Race | 1965 | 12.95 |
| 3 | Young Frankenstein | 1974 | 16.95 |
| 4 | The Return of the Pink Panther | 1975 | 11.95 |
| 5 | Star Wars | 1977 | 17.95 |
| 6 | The Princess Bride | 1987 | 16.95 |
| 7 | Glory | 1989 | 14.95 |
| 8 | Apollo 13 | 1995 | 19.95 |
| 9 | The Game | 1997 | 14.95 |
+----+--------------------------------+------+-------+
9 rows in set (0.00 sec)

As you can see, movie 10 is gone.

If the where clause selects more than one row, all the selected rows are deleted. For example:

mysql> delete from friend where lastname = "Haskell";
Query OK, 2 rows affected (0.45 sec)

A quick query of the friend table shows that both records for Eddie Haskell are deleted:

mysql> select * from friend;
+----------+-----------+---------+
| lastname | firstname | movieid |
+----------+-----------+---------+
| Cleaver | Wally | 9 |
| Mondello | Lumpy | 2 |
| Cleaver | Wally | 3 |
+----------+-----------+---------+
3 rows in set (0.00 sec)

Warning 

If you don't include a where clause, the entire table is deleted. For example, this statement deletes all the rows in the movie table:

mysql> delete from movie;
Query OK, 9 rows affected (0.44 sec)

A quick select of the movie table confirms that it is now empty:

mysql> select * from movie;
Empty set (0.00 sec)

Fortunately, you can now just run the create.sql script again to create the table.

The update statement

The update statement selects one or more rows from a table, and then modifies the value of one or more columns in the selected rows. Its syntax is this:

update table-name
 set expressions...
 where condition;

The set expressions resemble Java assignment statements. For example, here's a statement that changes the price of movie 8 to 18.95:

mysql> update movie set price = 18.95 where id = 8;
Query OK, 1 row affected (0.44 sec)
Rows matched: 1 Changed: 1 Warnings: 0

You can use a quick select statement to verify that the price was changed:

mysql> select id, price from movie;
+----+-------+
| id | price |
+----+-------+
| 1 | 14.95 |
| 2 | 12.95 |
| 3 | 16.95 |
| 4 | 11.95 |
| 5 | 17.95 |
| 6 | 16.95 |
| 7 | 14.95 |
| 8 | 18.95 |
| 9 | 14.95 |
| 10 | 19.95 |
+----+-------+
10 rows in set (0.01 sec)

To update more than one column, use commas to separate the expressions. For example, here's a statement that changes Eddie Haskell's name in the friend table:

mysql> update friend set lastname = "Bully",
 -> firstname = "Big"
 -> where lastname = "Haskell";
Query OK, 2 rows affected (0.46 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Again, a quick select shows that the rows are properly updated:

mysql> select firstname, lastname from friend;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Big | Bully |
| Big | Bully |
| Wally | Cleaver |
| Lumpy | Mondello |
| Wally | Cleaver |
+-----------+----------+
5 rows in set (0.00 sec)

One final trick with the update statement you should know about is that the set expressions can include calculations. For example, the following statement increases the price of all the movies by 10 percent:

mysql> update movie set price = price * 1.1;
Query OK, 10 rows affected (0.46 sec)
Rows matched: 10 Changed: 10 Warnings: 0

Here's a select statement to verify that this update worked:

mysql> select id, price from movie;
+----+-------+
| id | price |
+----+-------+
| 1 | 16.45 |
| 2 | 14.25 |
| 3 | 18.65 |
| 4 | 13.15 |
| 5 | 19.75 |
| 6 | 18.65 |
| 7 | 16.45 |
| 8 | 20.85 |
| 9 | 16.45 |
| 10 | 21.95 |
+----+-------+
10 rows in set (0.01 sec)