SQL statements always begin with a command , (a word, or group of words, which 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 which 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
Creates a new database
Creates a new index on a table column
Creates a new sequence in an existing database
Creates a new table in an existing database
Creates a new trigger definition
Creates a new view on an existing table
Retrieves records from a table
Adds one or more new records into a table
Modifies the data in existing table records
Removes existing records from a table
Destroys an existing database
Removes a column index from an existing table
Destroys an existing sequence generator
Destroys an existing table
Destroys an existing trigger definition
Destroys an existing table view
Adds a new PostgreSQL user account to the system
Modifies an existing PostgreSQL user account
Removes an existing PostgreSQL user account
Grant rights on a database object to a user
Deny rights on a database object from a user
Creates a new SQL function within a database
Creates a new language definition within a database
Creates a new SQL operator within a database
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 in an ordinary programming language. Reading a well-designed SQL query should be nearly as easy as reading an ordinary sentence.
|"Statements" vs "Queries"|
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 a general SQL statement which may instead create, add or modify data (without returning rows).
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 which 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 a word with a pre-defined SQL or PostgreSQL-specific meaning; these includes SQL commands, clauses, function names, and special noise terms which are often accompanied optionally with SQL commands (e.g., the noise term COLUMN in the ALTER TABLE command). In contrast, identifiers represent variable names for tables, columns, and any other database object.
Both keywords and identifiers are essentially references to an internally defined function, value or record as far as PostgreSQL is concerned. A literal, on the other hand, describes a literal piece of data, such as a number, or character string.
Finally, a SQL statement will contain special character symbols. These are reserved characters (such as the parentheses, semi-colon, and square brackets) which logically affect the meaning and arrangement of your keywords, identifiers and literals. You can think of these characters as the punctuation for your SQL statement. Operators fall under the category of special character symbols. Operators 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.