SQL statements always begin with a
command
(a word, or group of words, that describes
what
action the statement will initiate). The command can be called the
verb
of the SQL statement, as it always describes an action to be taken. Statements typically contain one or more
clauses
, which are formal modifiers that further describe the function of the SQL statement.
Table 3-2 contains a list of some of the most commonly used PostgreSQL commands.
Table 3-2. Fundamental PostgreSQL commands
|
Command
|
Description
|
|
CREATE DATABASE
|
Creates a new database
|
|
CREATE INDEX
|
Creates a new index on a table column
|
|
CREATE SEQUENCE
|
Creates a new sequence in an existing database
|
|
CREATE TABLE
|
Creates a new table in an existing database
|
|
CREATE TRIGGER
|
Creates a new trigger definition
|
|
CREATE VIEW
|
Creates a new view on an existing table
|
|
SELECT
|
Retrieves records from a table
|
|
INSERT
|
Adds one or more new records into a table
|
|
UPDATE
|
Modifies the data in existing table records
|
|
DELETE
|
Removes existing records from a table
|
|
DROP DATABASE
|
Destroys an existing database
|
|
DROP INDEX
|
Removes a column index from an existing table
|
|
DROP SEQUENCE
|
Destroys an existing sequence generator
|
|
DROP TABLE
|
Destroys an existing table
|
|
DROP TRIGGER
|
Destroys an existing trigger definition
|
|
DROP VIEW
|
Destroys an existing table view
|
|
CREATE USER
|
Adds a new PostgreSQL user account to the system
|
|
ALTER USER
|
Modifies an existing PostgreSQL user account
|
|
DROP USER
|
Removes an existing PostgreSQL user account
|
|
GRANT
|
Grant rights on a database object to a user
|
|
REVOKE
|
Deny rights on a database object from a user
|
|
CREATE FUNCTION
|
Creates a new SQL function within a database
|
|
CREATE LANGUAGE
|
Creates a new language definition within a database
|
|
CREATE OPERATOR
|
Creates a new SQL operator within a database
|
|
CREATE TYPE
|
Creates a new SQL data type within a database
|
While obviously code-like in nature, SQL was designed with ease of use and readability in mind. As a result, SQL
statements often bear a strong resemblance to simple, instructional English sentences. A strong feature of SQL is that its
statements are designed to instruct the server
what
data to find, not literally
how
to find it, as you would be forced to do in an ordinary programming language. Reading a
well-designed SQL query should be nearly as easy as reading an ordinary sentence.
Note: In SQL texts, the word
query
is frequently used interchangeably with
statement
. In order to be clear, within this book the term
query
is used
only to refer to statements which
return data
(e.g., SELECT statements),
rather than general SQL statements, which may instead create, add, or modify data.
Internally, PostgreSQL interprets structured SQL statements as a sequence of
tokens
, usually
delimited by whitespace (spaces or newlines, outside of quotes), though some tokens may be placed adjacently if there is no
chance of ambiguity (such as when operators are placed directly next to identifiers). A token in this context is a
word or character that can be identified meaningfully by the server when the SQL statement is
parsed
,
or interpreted.
Technically, each token can either be considered a
keyword
, an
identifier
, a
quoted identifier
, a
constant
(also called a
literal
), or one of several special character
symbols. Keywords are words PostgreSQL recognizes as words with pre-defined SQL or PostgreSQL-specific meanings; these include SQL
commands, clauses, function names, and special
noise
terms, which are often accompanied
optionally with SQL commands (e.g., the noise term
WORK in the COMMIT command). In contrast, identifiers represent
variable names for tables, columns, and any other database object.
Both keywords and identifiers reference internally defined functions, values, or records, as far as
PostgreSQL is concerned. Constants, on the other hand, describe pieces of data that are interpreted literally, such as a number or character
string.
Finally, a SQL statement contains special character symbols. These are reserved characters (such as parentheses, the semicolon, and square brackets) that logically affect the meaning and arrangement of your keywords,
identifiers, and literals. You can think of these characters as the punctuation for your SQL statements.
Operators
fall under the category of special character symbols; they can be used to imply
logical operations or evaluations between data values (either literals, or represented by identifiers), and are generally
between one and four characters in length.
The following sections explain and expand upon the nature of these elementary components of SQL.
