Java Reference
In-Depth Information
Software Engineering Observation 24.2
In general, you process results by knowing in advance the order of the columns in the
result—for example, selecting
AuthorID
and
LastName
from table
Authors
ensures that
the columns will appear in the result with
AuthorID
as the first column and
LastName
as
the second column. Programs typically process result columns by specifying the column
number in the result (starting from number 1 for the first column). Selecting columns by
name avoids returning unneeded columns and protects against changes in the actual order
of the columns in the table(s) by returning the columns in the exact order specified.
Common Programming Error 24.1
If you assume that the columns are always returned in the same order from a query that
uses the asterisk (
*
), the program may process the results incorrectly. If the column order
in the table(s) changes or if additional columns are added at a later time, the order of the
columns in the result will change accordingly.
In most cases, it's necessary to locate rows in a database that satisfy certain
selection crite-
ria
. Only rows that satisfy the selection criteria (formally called
predicates
) are selected.
SQL uses the optional
WHERE
clause
in a query to specify the selection criteria for the query.
The basic form of a query with selection criteria is
SELECT
columnName1
,
columnName2
,
…
FROM
tableName
WHERE
criteria
For example, to select the
Title
,
EditionNumber
and
Copyright
columns from table
Titles
for which the
Copyright
date is greater than
2013
, use the query
SELECT
Title, EditionNumber, Copyright
FROM
Titles
WHERE
Copyright > '2013'
Strings in SQL are delimited by single (
'
) rather than double (
"
) quotes. Figure 24.12
shows the result of the preceding query.
Title
EditionNumber
Copyright
Java How to Program
10
2015
Java How to Program, Late Objects Version
10
2015
Visual Basic 2012 How to Program
6
2014
Visual C# 2012 How to Program
5
2014
C++ How to Program
9
2014
Android How to Program
2
2015
Android for Programmers: An App-Driven
Approach, Volume 1
2
2014
Fig. 24.12
|
Sampling of titles with copyrights after 2005 from table
Titles
.
Pattern Matching: Zero or More Characters
The
WHERE
clause criteria can contain the operators
<
,
>
,
<=
,
>=
,
=
,
<>
and
LIKE
. Operator
LIKE
is used for
pattern matching
with wildcard characters
percent
(
%
)
and
underscore
(
_
). Pattern matching allows SQL to search for strings that match a given pattern.
