Using a basic select statement

To query a database, you use the select statement. In this statement, you list the table or tables from which you want to retrieve the data; the specific table columns you want to retrieve (you may not be interested in everything that’s in the table); and other clauses that indicate which 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;

In the following list, I take this statement apart piece by piece:

• select title, year names the columns you want to include in the query result.
• from movie names the table from which you want to retrieve the rows.
• order by year indicates that the result is sorted by the year column so that the oldest movie appears first.

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

mysql> select title, year from movie order by year;
+-------------------------+------+
| title | year |
+-------------------------+------+
| It's a Wonderful Life | 1946 |
| Young Frankenstein | 1974 |
| Star Wars | 1977 |
| The Princess Bride | 1987 |
| Glory | 1989 |
| The Game | 1997 |
| Shakespeare in Love | 1998 |
| Zombieland | 2009 |
| The King's Speech | 2010 |
| Star Trek Into Darkness | 2013 |
+-------------------------+------+
10 rows in set (0.00 sec)

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

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

select * from movie order by year;

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

Both examples so far include an order by clause. In a SQL database, the rows stored in a table aren’t 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 the query

Suppose that 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, as in this example:

mysql> select title, year from movie
-> where year <= 1980
-> order by year;
+-----------------------+------+
| title | year |
+-----------------------+------+
| It's a Wonderful Life | 1946 |
| Young Frankenstein | 1974 |
| Star Wars | 1977 |
+-----------------------+------+
3 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. 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 Life | 1946 |
| The Princess Bride | 1987 |
| Glory | 1989 |
| The Game | 1997 |
| Shakespeare in Love | 1998 |
| Zombieland | 2009 |
| The King's Speech | 2010 |
| Star Trek Into Darkness | 2013 |
+-------------------------+------+
8 rows in set (0.01 sec)

Using 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 |
+---------------------+------+
| Shakespeare in Love | 1998 |
+---------------------+------+
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.

Finding what something sounds like

Suppose that you want to retrieve information about a movie and can’t quite remember the name, but you know that it includes the word princess. One of the most interesting variations of the where clause throws 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.01 sec)

Using 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: The functions operate on the values returned in a result set, which isn’t necessarily the entire table.

To use one of these functions, specify the function rather than a column name in a select statement. 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. This statement finds out how many movies in the table were made before 1970:

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

The result is 2.

Selecting data from more than one table

In the real world, most select statements retrieve data from two or more tables. Suppose that 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, like so:

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, as follows:

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

Here you can see which movies have been loaned 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 |
+-----------+
| Star Wars |
| Glory |
+-----------+
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. You must still mention both tables in the from clause, however.

Eliminating duplicates

If you want to know just the names of all your friends who have a movie checked out, you can do a simple select from the friend table, like this:

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)

This result set has a problem, however: 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.

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

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.

Using the delete statement

The basic syntax of the delete statement is

delete from table-name where condition;

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, like so:

mysql> select * from movie;
+----+-----------------------+------+-------+
| id | title | year | price |
+----+-----------------------+------+-------+
| 1 | It's a Wonderful Life | 1946 | 14.95 |
| 2 | Young Frankenstein | 1974 | 16.95 |
| 3 | Star Wars | 1977 | 17.95 |
| 4 | The Princess Bride | 1987 | 16.95 |
| 5 | Glory | 1989 | 14.95 |
| 6 | The Game | 1997 | 14.95 |
| 7 | Shakespeare in Love | 1998 | 19.95 |
| 8 | Zombieland | 2009 | 18.95 |
| 9 | The King's Speech | 2010 | 17.85 |
+----+-----------------------+------+-------+
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, as in this 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, like so:

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

If you don’t include a where clause, the entire table is deleted. 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, as follows:

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

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

Using the update statement

The update statement selects one or more rows in 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. 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, as follows:

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

To update more than one column, use commas to separate the expressions. 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, as follows:

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. The following statement increases the prices 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 | 18.65 |
| 3 | 19.75 |
| 4 | 18.65 |
| 5 | 16.45 |
| 6 | 16.45 |
| 7 | 21.95 |
| 8 | 20.85 |
| 9 | 19.64 |
| 10 | 21.95 |
+----+-------+
10 rows in set (0.01 sec)