Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

5.5 Querying with SQL SELECT

The
SELECT

statement is used to query and
retrieve one or more rows from a database. We introduce it in this
section, and then show you the WHERE clause for
selecting data that matches a condition. The section concludes with
an introduction to the more advanced features of
SELECT statements and a short case study.

5.5.1 Basic Querying

Consider an example SELECT statement:

SELECT surname, firstname FROM customer;

This outputs the values of the attributes surname
and firstname from all rows in the
customer table. Assuming we previously inserted
four rows when we created the winestore
database, the output from the MySQL command interpreter is:

+-----------+-----------+
| surname   | firstname |
+-----------+-----------+
| Marzalla  | Dimitria  |
| LaTrobe   | Anthony   |
| Fong      | Nicholas  |
| Stribling | James     |
+-----------+-----------+
4 rows in set (0.04 sec)

Any attributes of a table may be listed in a
SELECT statement by separating them with a comma.
If all attributes are required, the shortcut of an asterisk character
(*) can be used. Consider the statement:

SELECT * FROM region;

This outputs all the data from the table region:

+-----------+---------------------+
| region_id | region_name         |
+-----------+---------------------+
|         1 | All                 |
|         2 | Goulburn Valley     |
|         3 | Rutherglen          |
|         4 | Coonawarra          |
|         5 | Upper Hunter Valley |
|         6 | Lower Hunter Valley |
|         7 | Barossa Valley      |
|         8 | Riverland           |
|         9 | Margaret River      |
|        10 | Swan Valley         |
+-----------+---------------------+
10 rows in set (0.01 sec)

SELECT statements can also output data that
isn't from a database. Consider the following
example:

SELECT curtime( );

This example runs a function that displays the current time:

+-----------+
| curtime( ) |
+-----------+
| 08:41:50  |
+-----------+
1 row in set (0.02 sec)

The SELECT statement can even be used as a simple
calculator, using the MySQL mathematical functions described in Chapter 15:

SELECT pi( )*(4*4);

This outputs:

+------------+
| pi( )*(4*4) |
+------------+
|  50.265482 |
+------------+
1 row in set (0.01 sec)

5.5.2 WHERE Clauses

A
WHERE clause is used as part of most
SELECT queries to limit the rows that are
retrieved to those that match a condition.

Consider this grape-growing region table
containing the details of ten regions:

mysql> SELECT * from region;
+-----------+---------------------+
| region_id | region_name         |
+-----------+---------------------+
|         1 | All                 |
|         2 | Goulburn Valley     |
|         3 | Rutherglen          |
|         4 | Coonawarra          |
|         5 | Upper Hunter Valley |
|         6 | Lower Hunter Valley |
|         7 | Barossa Valley      |
|         8 | Riverland           |
|         9 | Margaret River      |
|        10 | Swan Valley         |
+-----------+---------------------+
10 rows in set (0.09 sec)

To show only the first three regions, you can type:

SELECT * FROM region WHERE region_id <= 3;

This outputs all attributes for the first three rows:

+-----------+-----------------+
| region_id | region_name     |
+-----------+-----------------+
|         1 | All             |
|         2 | Goulburn Valley |
|         3 | Rutherglen      |
+-----------+-----------------+
3 rows in set (0.03 sec)

You can combine the attribute and row restrictions and select only
the region_name attribute for the first three
regions:

mysql> SELECT region_name FROM region WHERE region_id <= 3;
+-----------------+
| region_name     |
+-----------------+
| All             |
| Goulburn Valley |
| Rutherglen      |
+-----------------+
3 rows in set (0.01 sec)

The SQL Boolean operators AND and
OR have the same function as the PHP
&& and || operators
introduced in Chapter 2. These can be used to
develop more complex WHERE clauses (and these can
be combined with the MySQL functions described in Chapter 15). Consider an example query:

SELECT * FROM customer WHERE surname='Marzalla' AND firstname='Dimitria';

This retrieves rows that match both criteria, that is, those
customers with a surname Marzalla and a
firstname Dimitria. In this example, you need to
be careful to type the strings 'Marzalla' and
'Dimitria' using the correct case because string
values are case sensitive.

Consider a more complex example:

SELECT cust_id FROM customer 
WHERE (surname='Marzalla' AND firstname LIKE 'M%') OR 
birth_date='1980-07-14';

This finds rows with either the surname Marzalla
and a firstname beginning with M, or customers who
were born on 14 July 1980; the
LIKE operator is
discussed in more detail in Chapter 15. The
OR operator isn't exclusive, so a
row can contain a birth date of 14 July 1980, a
surname of Marzalla, and a
firstname beginning with M. This query, when run
on the winestore database, returns:

+---------+
| cust_id |
+---------+
|     440 |
|     493 |
+---------+
2 rows in set (0.01 sec)

SELECT queries are often sophisticated and a long
WHERE clause may include many
AND and OR operators. More
complex examples of queries are shown later in this chapter. As
discussed previously, the WHERE clause is also a
common component of UPDATE and
DELETE statements.

5.5.3 Sorting and Grouping Output

Listing attributes in the
SELECT statement and using
WHERE allows you to decide what rows and columns
in a table are returned from a query. However, you might also want to
sort the data after it's returned, or you might want
to group it together beforehand so that you can count the number of
rows with different values, find a minimum or maximum value, or sum a
numeric field. This section shows you how to pre- and post-process
your data.

5.5.3.1 ORDER BY

The ORDER BY clause
sorts the data after the query has been evaluated. Consider an
example:

SELECT surname, firstname FROM customer
WHERE city = 'Portsea' and firstname = 'James' ORDER by surname;

This query finds all customers who live in Portsea and who have the
first name James. It then presents the results sorted alphabetically
by ascending surname:

+-----------+-----------+
| surname   | firstname |
+-----------+-----------+
| Leramonth | James     |
| Mockridge | James     |
| Ritterman | James     |
+-----------+-----------+
3 rows in set (0.00 sec)

Sorting can be on multiple attributes. For example:

SELECT surname, firstname, initial FROM customer 
WHERE city = 'Coonawarra' OR city = 'Longwood' 
ORDER BY surname, firstname, initial;

This presents a list of customers who live in Coonawarra or Longwood,
sorted first by ascending surname, then (for those
customers with the same surname) by firstname, and
(for those customers with the same surname and first name), by
initial. The output for the winestore
customer table is:

+------------+-----------+---------+
| surname    | firstname | initial |
+------------+-----------+---------+
| Archibald  | Belinda   | Q       |
| Chester    | Marie     | S       |
| Dalion     | Marie     | C       |
| Eggelston  | Martin    | E       |
| Florenini  | Melinda   | O       |
| Holdenson  | Jasmine   | F       |
| Mellaseca  | Craig     | Y       |
| Mockridge  | Dimitria  | I       |
| Morfooney  | Chris     | K       |
| Nancarral  | Samantha  | W       |
| Oaton      | Joel      | V       |
| Oaton      | Rochelle  | F       |
| Patton     | Joel      | Z       |
| Patton     | Penelope  | E       |
| Patton     | Samantha  |         |
| Rosenthal  | Chris     | A       |
| Tonkin     | Michelle  | Z       |
| Tonnibrook | Belinda   | T       |
+------------+-----------+---------+
18 rows in set (0.00 sec)

By default, the ORDER BY clause
sorts in ascending order, or ASC. To sort in
reverse or descending order, DESC can be used.
Consider an example:

SELECT * FROM customer WHERE city='Melbourne' ORDER BY surname DESC;

5.5.3.2 GROUP BY

The GROUP BY clause is
different from ORDER BY because
it doesn't sort the data for output. Instead, it
sorts the data early in the query process, for the purpose of
grouping or
aggregation
.
Grouping data using a sort is the easiest way to discover properties
such as maximums, minimums, averages, and counts of values.

Consider an example:

SELECT city, COUNT(*) FROM customer GROUP BY city;

This query first sorts the rows in the customer
table by city and groups the rows with matching
values together. The output of the query consists of two columns. The
first is a sorted list of unique cities. The second shows, for each
city, the COUNT of the number of customers who
live in that city. The number of rows that are output is equal to the
number of different city values in the
customer table, and the effect of
COUNT(*) is to count the number of rows per group.

Here are the first few lines output by the query:

+--------------+----------+
| city         | COUNT(*) |
+--------------+----------+
| Alexandra    |       14 |
| Armidale     |        7 |
| Athlone      |        9 |
| Bauple       |        6 |
| Belmont      |       11 |
| Bentley      |       10 |
| Berala       |        9 |
| Broadmeadows |       11 |

So, for example, there are 14 customers who live in Alexandra, that
is, 14 rows in the customer table are grouped
together because they have a city value of
Alexandra.

The GROUP BY clause can find
different properties of the aggregated rows. Here's
an example:

SELECT city, MIN(birth_date) FROM customer GROUP BY city;

This query first groups the rows by city and then shows the oldest
customer in each city. The first few rows of the output are as
follows:

+---------------+-----------------+
| city          | MIN(birth_date) |
+---------------+-----------------+
| Alexandra     | 1938-04-01      |
| Armidale      | 1943-04-04      |
| Athlone       | 1943-04-04      |
| Bauple        | 1922-11-26      |

The GROUP BY clause should be used only when the query is designed to find a characteristic of a group of rows, not the details of individual rows.

There are several functions that can be used in aggregation with the
GROUP BY clause. Five
particularly useful functions are:

AVG( )

Finds the average value of a numeric attribute in a set

MIN( )

Finds a minimum value of a string or numeric attribute in a set

MAX( )

Finds a maximum value of a string or numeric attribute in a set

SUM( )

Finds the sum total of a numeric attribute

COUNT( )

Counts the number of rows in a set

The SQL standard places a constraint on the GROUP
BY clause that MySQL doesn't
enforce. In the standard, all attributes that are selected (those
that are listed immediately after the SELECT
statement) must appear in the GROUP
BY clause. Most examples in this chapter
don't meet this unnecessary constraint.

5.5.3.3 HAVING

The

HAVING clause
permits conditional aggregation of data into groups. For example,
consider the following query:

SELECT city, count(*), min(birth_date) FROM customer 
GROUP BY city HAVING count(*) > 10;

The query groups rows by city, but only for cities
that have more than 10 resident customers. For those groups, the
city, count of customers, and earliest birth date of a customer in
that city is output. Cities with less than 10 customers are omitted
from the result set. The first few rows of the output are as follows:

+--------------+----------+-----------------+
| city         | count(*) | min(birth_date) |
+--------------+----------+-----------------+
| Alexandra    |       14 | 1938-04-01      |
| Belmont      |       11 | 1938-04-01      |
| Broadmeadows |       11 | 1955-10-13      |
| Doveton      |       13 | 1943-04-04      |
| Eleker       |       11 | 1938-04-01      |
| Gray         |       12 | 1943-04-04      |

The HAVING clause must contain an attribute or
expression (such as a function or an alias) from the
SELECT clause; in this example,
count(*) is listed after the
SELECT and is used in the
HAVING condition.

The HAVING clause should be used exclusively with
the GROUP BY clause. It is slow and should never
be used instead of a WHERE clause. For example,
don't do this:

SELECT cust_id, surname FROM customer HAVING surname = "Leramonth";

Do this instead:

SELECT cust_id FROM customer WHERE surname = "Leramonth";

5.5.3.4 Combining clauses

You can combine
ORDER BY,
GROUP BY,
HAVING, and WHERE. When all
four are used, they must appear in the order
WHERE, then GROUP
BY, then HAVING, and then
ORDER BY. This is intuitive
because the WHERE clause picks the rows from the
table, then GROUP BY organizes
the rows into sets, then HAVING picks the sets
that match a condition, and then the data is sorted by the
ORDER BY condition just before
it's output.

Consider an example. Suppose we want to find the number of customers
with the same name who live in each city in the state of Victoria,
where the same name is defined as the same first name and surname.
For example, this might determine that there are five John Smiths who
live in Inverloch and three Tuong Nguyens in Carlton.
Here's the query:

SELECT city, surname, firstname, count(*) FROM customer
WHERE state = 'VIC'
GROUP BY surname, firstname HAVING count(*) >= 2
ORDER BY city;

The query first uses the WHERE clause to pick the
rows of customers that live in the state of Victoria. The rows are
then grouped together into sets, where the grouping condition is that
the customer surname and firstname are the same. Then, only those
sets that have more than one customer with the same name are kept by
the HAVING clause; this gets rid of unique names.
Last, the ORDER BY clause sorts the customers by
their city, and the city, first name, surname, and count of the
number of customers is output. Here is the output from the winestore
customer table:

+--------------+-----------+-----------+----------+
| city         | surname   | firstname | count(*) |
+--------------+-----------+-----------+----------+
| Broadmeadows | Mellaseca | Anthony   |        2 |
| Eleker       | Leramonth | Harry     |        2 |
| Kalimna      | Galti     | Nicholas  |        2 |
| Lucknow      | Mellili   | Derryn    |        2 |
| McLaren      | Chester   | Betty     |        2 |
+--------------+-----------+-----------+----------+
5 rows in set (0.00 sec)

The output shows, for example, that there are two Betty Chesters who
live in McLaren city in the state of Victoria.

The GROUP BY clause sorts
before it groups the rows into sets. Therefore, you
don't need to use ORDER BY if you
want the data to be output in the sort order used by the
GROUP BY. For example, you
don't need to do this:

SELECT * FROM customer GROUP BY surname ORDER BY surname;

If you leave out the ORDER BY clause,
you'll get the same output:

SELECT * FROM customer GROUP BY surname;

However, in practice, it doesn't really matter: the
MySQL query optimizer will ignore the ORDER
BY clause if it's unnecessary. We
discuss the query optimizer in Chapter 15.

5.5.3.5 DISTINCT

Suppose we want to find out which
different cities our customers live in. The following query shows the
cities for all of the customers:

SELECT city FROM customer;

The problem is that a city name appears more than once if more than
one customer lives in that city. What we really want is a list of
unique cities that the customers live in.

The DISTINCT clause presents only one example of
each identical row from a query. We can use it to find out the unique
cities the customers live in:

SELECT DISTINCT city FROM customer;

This shows one example of each different city in the
customer table.

This example has exactly the same result as:

SELECT ciry FROM customer GROUP BY city;

The DISTINCT clause is often slow to run, much
like the GROUP BY and
HAVING clauses. We discuss how indexes and query
optimization can speed queries in Chapter 15.

5.5.4 Limiting Output in MySQL

The LIMIT
operator is MySQL-specific and is used to control the size of the
output. For example, the following query returns only the first five
rows from the customer table:

SELECT * FROM customer LIMIT 5;

This saves query evaluation time and reduces the size of the result
set that's buffered in memory by MySQL.
It's particularly useful in a web database
application where one page of results is presented from a large
table.

You can also specify which row to begin at, and then how many rows
you want:

SELECT * FROM customer LIMIT 100,5;

This returns the 100^th to
104^th rows from the
customer table.

Row numbering begins at row zero. For example, if you want the first
five rows of the customer table, use:

SELECT * FROM customer LIMIT 0,5;

The following statement produces five rows beginning with row two:

SELECT * FROM customer LIMIT 1,5;

Be careful: forgetting to count from zero is a common mistake.

If you want all rows after a particular row, the second parameter can
be set to -1:

SELECT * FROM customer LIMIT 600,-1;

For the winestore customer table, this returns
50 rows with cust_id values of 601 to 650.

The LIMIT operator is included at the end of an
SQL statement, after the optional WHERE,
GROUP BY, HAVING, and
ORDER BY clauses.