Java Reference
In-Depth Information
Section 24.2 Relational Databases
• A relational database (p. 1047) stores data in tables (p. 1047). Tables are composed of rows
(p. 1047), and rows are composed of columns in which values are stored.
• A table's primary key (p. 1047) has a unique in each row.
• Each column (p. 1047) of a table represents a different attribute.
• The primary key can be composed of more than one column.
• An identity column is the SQL standard way to represent an autoincremented (p. 1048) column.
The SQL
IDENTITY
keyword (p. 1048) marks a column as an identity column.
• A foreign key is a column in a table that must match the primary-key column in another table.
This is known as the Rule of Referential Integrity (p. 1050).
• A one-to-many relationship (p. 1051) between tables indicates that a row in one table can have
many related rows in a separate table.
• Every column in a primary key must have a value, and the value of the primary key must be
unique. This is known as the Rule of Entity Integrity (p. 1051).
• Foreign keys enable information from multiple tables to be joined together. There's a one-to-
many relationship between a primary key and its corresponding foreign key.
Section 24.4.1 Basic
SELECT
Query
• The basic form of a query (p. 1052) is
SELECT
*
FROM
tableName
where the asterisk (
*;
p. 1052) indicates that all columns from
tableName
should be selected,
and
tableName
specifies the table in the database from which rows will be retrieved.
• To retrieve specific columns, replace the
*
with a comma-separated list of column names.
Section 24.4.2
WHERE
Clause
•The optional
WHERE
clause (p. 1053) in a query specifies the selection criteria for the query. The
basic form of a query with selection criteria (p. 1052) is
SELECT
columnName1
,
columnName2
,
…
FROM
tableName
WHERE
criteria
•The
WHERE
clause can contain operators
<
,
>
,
<=
,
>=
,
=
,
<>
and
LIKE
.
LIKE
(p. 1053) is used for
string pattern matching (p. 1053) with wildcard characters percent (
%
) and underscore (
_
).
• A percent character (
%;
p. 1053) in a pattern indicates that a string matching the pattern can
have zero or more characters at the percent character's location in the pattern.
•An underscore (
_
; p. 1053) in the pattern string indicates a single character at that position in
the pattern.
Section 24.4.3
ORDER BY
Clause
• A query's result can be sorted with the
ORDER
BY
clause (p. 1055). The simplest form of an
ORDER
BY
clause is
SELECT
columnName1
,
columnName2
,
…
FROM
tableName
ORDER
BY
column
ASC
SELECT
columnName1
,
columnName2
,
…
FROM
tableName
ORDER
BY
column
DESC
where
ASC
specifies ascending order,
DESC
specifies descending order and
column
specifies the col-
umn on which the sort is based. The default sorting order is ascending, so
ASC
is optional.
• Multiple columns can be used for ordering purposes with an
ORDER
BY
clause of the form
ORDER BY
column1
sortingOrder
,
column2
sortingOrder
,
…
