In summary, a SQL statement is comprised of tokens, where each token can represent either a keyword, identifier, quoted identifier, constant, or special character symbol. Table 3-7 uses a simple
SELECT statement to illustrate a basic, but complete, SQL statement and its
components.
Table 3-7. A simple SQL query
|
|
SELECT
|
id, name
|
FROM
|
states
|
|
Token Type
|
Keyword
|
Identifiers
|
Keyword
|
Identifier
|
|
Description
|
Command
|
Id and name columns
|
Clause
|
Table name
|
As shown in the table, the SELECT statement contains the keywords
SELECT and FROM. Together, the
FROM keyword and states token compose
a clause, as they modify and further describe the SELECT command.
The id, name, and
states tokens are the identifiers of the statement. The
id and name identifiers specify the selected columns, while the states identifier specifies the table name to select from. Therefore,
with the preceding SQL query, you are instructing PostgreSQL to display the columns named
id and name for each row from the
states table. Example 3-13 shows the output
this query generates within the booktown database.
Example 3-13. Example SQL query
booktown=#
SELECT id, name FROM states;
id | name
----+------------
42 | Washington
51 | Oregon
(2 rows)
booktown=#
Getting more complicated, Table 3-8 and Table 3-9
break down another example statement. This statement uses the UPDATE command, along with
SET and WHERE clauses, which respectively specify with
what
to update the records, and
how
to find the records to update.
Table 3-8. UPDATE example: the SET clause
|
UPDATE
|
states
|
SET
|
id
|
=
|
51
|
|
keyword
|
identifier
|
keyword
|
identifier
|
operator
|
integer constant
|
|
command
|
table name
|
clause
|
column
|
assignment
|
new id value
|
Table 3-9. UPDATE example: the WHERE clause
|
WHERE
|
name
|
=
|
'Oregon'
|
|
keyword
|
identifier
|
operator
|
string constant
|
|
clause
|
column name
|
equivalence
|
string value to match
|
When executed, this statement examines each record's name column to find matches for
the WHERE clause's stated condition (equivalence to the string constant 'Oregon'). Then,
for each row which matches that condition, it updates the id column with the value
51.
Breaking it down, this UPDATE statement has three keywords, three identifiers, two
operators, and two constants. The keywords are UPDATE (the SQL command),
SET (specifies the updates to make), and WHERE (identifies
the rows to update). The identifiers are the states table name, the id column
name, and the name column name.
The operators are both represented by the = operator. When used with the
SET clause, this operator is used for assignment (to assign a new value to an existing
record's identified column); this is a special use which is unique to the SET clause. In
contrast, when used with the WHERE clause, the = operator
is used to check equivalence between values. In this case, this means that the equivalence operator will check the value of
a record's name column against a string constant with the value of
Oregon
.
Finally, the constants in this statement are the integer constant 51 (the new value for the
id column), and the string constant
Oregon
(compared to the
name column through the WHERE clause).
Example 3-14 therefore updates the states table by setting the
id column to 51 whenever the name
column matches the value
Oregon
. It then checks the results of that
UPDATE statement with another SELECT statement.
Example 3-14. A SQL update
booktown=#
UPDATE states
booktown-#
SET id = 51
booktown-#
WHERE name = 'Oregon';
UPDATE 1
booktown=#
SELECT * FROM states
booktown-#
WHERE name = 'Oregon';
id | name | abbreviation
----+--------+--------------
51 | Oregon | OR
(1 row)
