In this appendix, we explain the notation method we have used to define the statements, we present the definitions of the SQL statements we have discussed in this book, and we show the list of reserved words.
The definitions in this appendix can differ from those in the previous chapters. The main reason for this is that, in the chapters, we explained the statements and concepts step by step. To avoid too much detail, we sometimes used simple versions of the definitions. This appendix contains the complete definitions.
In this appendix and throughout the book, we have used a formal notation method to describe the syntax of all SQL statements and the common elements. This notation is a derivative of the so-called Backus Naur Form (BNF), which is named after John Backus and Peter Naur. The meaning of the metasymbols that we use is based on that of the metasymbols in the SQL standard.
BNF adopts a language of substitution rules or production rules, consisting of a series of symbols. Each production rule defines one symbol. A symbol could be, for example, an SQL statement, a table name, or a colon. A terminal symbol is a special type of symbol. All symbols, apart from the terminal symbols, are defined in terms of other symbols in a production rule. Examples of terminal symbols are the word CLOSE and the semicolon.
You could compare a production rule with the definition of an element, in which the definition of that element uses elements defined elsewhere. In this case, an element equates to a symbol.
The following metasymbols do not form part of the SQL language but belong to the notation technique:
< >
::=
|
[ ]
...
{ }
;
"
We now explain each of these symbols.
Nonterminal symbols are presented in brackets (< and >). A production rule exists for every nonterminal symbol. We show the names of the nonterminal symbols in lowercase letters. Two examples of nonterminal symbols are <select statement> and <table reference>.
The ::= symbol is used in a production rule to separate the nonterminal symbol that is defined (left) from its definition (right). The ::= symbol should be read as “is defined as.” See the following example of the production rule for the CLOSE statement:
<close statement> ::= CLOSE <cursor name> |
Explanation: The CLOSE statement consists of the terminal symbol CLOSE followed by the nonterminal symbol cursor name. There should also be a production rule for <cursor name>.
Alternatives are represented by the | symbol. Here, we give an example of the production rule for the element <character>:
<character> ::= <digit> | <letter> | <special |
symbol> | ''
Explanation: We should conclude from this that a character is a digit, a letter, a special symbol, or two quotation marks; it must be one of the four.
Whatever is placed between square brackets ([ and ]) may be used. Here is the production rule for the ROLLBACK statement:
<rollback statement> ::= ROLLBACK [ WORK ] |
Explanation: A ROLLBACK statement always consists of the word ROLLBACK and can optionally be followed by the word WORK.
The three dots indicate what may be repeated one or more times. Here, our example is the production rule for an integer:
<whole number> ::= <digit>... |
Explanation: An integer consists of a series of digits (with a minimum of one).
All symbols between braces ({ and }) form a group. For example, braces used with the | symbol show precisely what the alternatives are. The following example is a part of the production rule for the FROM clause:
<from clause> ::=
FROM <table reference> [ { , <table reference>
|
Explanation: A FROM clause begins with the terminal symbol FROM and is followed by at least one table reference. It is possible to follow this table reference with a list of elements, with each element consisting of a comma followed by a table reference. Do not forget that the comma is part of SQL and not part of the notation.
Some symbols have the same definition. Instead of repeating them, the semicolon (;) can be used to shorten the definitions. The following definition
<character literal> ; <varchar literal> ; <long varchar literal> ::= <character string> |
is equivalent to these three definitions:
<character literal> ::= <character string> <varchar literal> ::= <character string> <long varchar literal> ::= <character string> |
A small number of metasymbols, such as the " symbol, are part of particular SQL statements themselves. To avoid misunderstanding, these symbols are enclosed by double quotation marks. Among other things, this means that the symbol " that is used within SQL is represented in the production rules as """.
Whatever is presented in uppercase letters, as well as the symbols that are not part of the notation method, must be adopted unaltered.
The sequence of the symbols in the right part of the production rule is fixed.
Blanks in production rules have no significance. Generally, they have been added to make the rules more readable. Therefore, the two following production rules mean the same:
<alphanumeric literal> ::= ' [ <character>... ] '
and
<alphanumeric literal> ::= '[<character>...]'
Each programming language and database language (and this includes SQL) supports so-called reserved words or keywords. Examples in SQL are SELECT and CREATE. In most SQL products, these reserved words may not be used as names for database objects such as tables, columns, views, and users. Each product has its own set of reserved words (although two SQL products will have many reserved words in common, of course). You should refer to the product documentation to find out which these are. The following list contains reserved words as defined in the SQL3 standard.
ABSOLUTE, ACTION, ADD, ALL, ALLOCATE, ALTER, AND, ANY, ARE, AS, ASC, ASSERTION, AT, AUTHORIZATION, AVG BEGIN, BETWEEN, BIT, BIT_LENGTH, BOTH, BY CASCADE, CASCADED, CASE, CAST, CATALOG, CHAR, CHARACTER, CHAR_LENGTH, CHARACTER_LENGTH, CHECK, CLOSE, COALESCE, COLLATE, COLLATION, COLUMN, COMMIT, CONNECT, CONNECTION, CONSTRAINT, CONSTRAINTS, CONTINUE, CONVERT, CORRESPONDING, COUNT, CREATE, CROSS, CURRENT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, CURSOR DATE, DAY, DEALLOCATE, DEC, DECIMAL, DECLARE, DEFAULT, DEFERRABLE, DEFERRED, DELETE, DESC, DESCRIBE, DESCRIPTOR, DIAGNOSTICS, DISCONNECT, DISTINCT, DOMAIN, DOUBLE, DROP ELSE, END, END-EXEC, ESCAPE, EXCEPT, EXCEPTION, EXEC, EXECUTE, EXISTS, EXTERNAL, EXTRACT FALSE, FETCH, FIRST, FLOAT, FOR, FOREIGN, FOUND, FROM, FULL GET, GLOBAL, GO, GOTO, GRANT, GROUP HAVING, HOUR IDENTITY, IMMEDIATE, IN, INDICATOR, INITIALLY, INNER, INPUT, INSENSITIVE, INSERT, INT, INTEGER, INTERSECT, INTERVAL, INTO, IS, ISOLATION JOIN KEY LANGUAGE, LAST, LEADING, LEFT, LEVEL, LIKE, LOCAL, LOWER MATCH, MAX, MIN, MINUTE, MODULE, MONTH NAMES, NATIONAL, NATURAL, NCHAR, NEXT, NO, NOT, NULL, NULLIF, NUMERIC OCTET_LENGTH OF, ON, ONLY, OPEN, OPTION, OR, ORDER, OUTER, OUTPUT, OVERLAPS PARTIAL, POSITION, PRECISION, PREPARE, PRESERVE, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE, PUBLIC READ, REAL, REFERENCES, RELATIVE, RESTRICT, REVOKE, RIGHT, ROLLBACK, ROWS SCHEMA, SCROLL, SECOND, SECTION, SELECT, SESSION, SESSION_USER, SET, SIZE, SMALLINT, SOME, SQL, SQLCODE, SQLERROR, SQLSTATE, SUBSTRING, SUM, SYSTEM_USER TABLE, TEMPORARY, THEN, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TO, TRAILING, TRANSACTION, TRANSLATE, TRANSLATION, TRIM, TRUE UNION, UNIQUE, UNKNOWN, UPDATE, UPPER, USAGE, USER, USING VALUE, VALUES, VARCHAR, VARYING, VIEW WHEN, WHENEVER, WHERE, WITH, WORK, WRITE YEAR ZONE
This is the list of reserved words of MySQL. The words that already appear in the previous list have been left out.
ANALYZE, ASENSITIVE BEFORE, BIGINT, BINARY, BLOB CALL, CHANGE, CONDITION DATABASE, DATABASES, DAY_HOUR, DAY_MICROSECOND, DAY_MINUTE, DAY_SECOND, DELAYED, DETERMINISTIC, DISTINCTROW, DIV, DUAL EACH, ELSEIF, ENCLOSED, ESCAPED, EXIT, EXPLAIN FLOAT4, FLOAT8, FORCE, FULLTEXT HIGH_PRIORITY, HOUR_MICROSECOND, HOUR_MINUTE, HOUR_SECOND IF, IGNORE, INDEX, INFILE, INOUT, INT1, INT2, INT3, INT4, INT8, ITERATE KEYS, KILL LABEL, LEAVE, LIMIT, LINES, LOAD, LOCALTIME, LOCALTIMESTAMP, LOCK, LONG, LONGBLOB, LONGTEXT, LOOP, LOW_PRIORITY MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, MIDDLEINT, MINUTE_MICROSECOND, MINUTE_SECOND, MOD, MODIFIES NO_WRITE_TO_BINLOG OPTIMIZE, OPTIONALLY, OUT, OUTFILE PURGE RAID0, READS, REGEXP, RELEASE, RENAME, REPEAT, REPLACE, REQUIRE, RETURN, RLIKE SCHEMAS, SECOND_MICROSECOND, SENSITIVE, SEPARATOR, SHOW, SONAME, SPATIAL, SPECIFIC, SQLEXCEPTION, SQLWARNING, SQL_BIG_RESULT, SQL_CALC_FOUND_ROWS, SQL_SMALL_RESULT, SSL, STARTING, STRAIGHT_JOIN TERMINATED, TINYBLOB, TINYINT, TINYTEXT, TRIGGER UNDO, UNLOCK, UNSIGNED, USE, UTC_DATE, UTC_TIME, UTC_TIMESTAMP VARBINARY, VARCHARACTER WHILE X509, XOR YEAR_MONTH ZEROFILL
We strongly advise that you follow these recommendations when choosing the names of database objects:
Avoid one-letter words, even if they do not occur in the list.
Avoid words that could be seen as abbreviations of words in the list; for example, do not use
DATAbecause the wordDATABASEappears in the list.Avoid derivations of words in the list, such as plural and verbal forms. Therefore, do not use
CURSORS(plural ofCURSOR) orORDERING(present participle of the verbORDER).
This section contains the definitions of all the SQL statements as they are described in this book. Certain common elements, such as condition and column list, are “used” by several statements. If an element belongs to only one statement, it is included in Section A.4.2 together with its statement. All others are explained in Section A.4.3. We begin with the different groups of SQL statements.
In Section 4.16, in Chapter 4, “SQL in a Nutshell,” we indicated that the set of SQL statements can be divided into groups, such as DDL, DML, and DCL statements. Furthermore, in Chapter 26, “Introduction to Embedded SQL,” we made a distinction between executable and nonexecutable SQL statements. In this section, we indicate precisely which group each statement belongs to.
SQL statement:
<sql statement> ::= <executable statement> | <non-executable statement> |
Executable statement:
<executable statement> ::= <declarative statement> | <procedural statement> |
<declarative statement> ::= <ddl statement> | <dml statement> | <dcl statement> |
DDL statement:
<ddl statement> ::= <alter database statement> | <alter sequence statement> | <alter table statement> | <create database statement> | <create function statement> | <create index statement> | <create procedure statement> | <create sequence statement> | <create table statement> | <create trigger statement> | <create view statement> | <drop database statement> | <drop function statement> | <drop index statement> | <drop procedure statement> | <drop sequence statement> | <drop table statement> | <drop trigger statement> | <drop view statement> | <rename table statement> |
DML statement:
<dml statement> ::= <call statement> | <close statement> | <commit statement> | <delete statement> | <execute immediate statement> | <fetch statement> | <insert statement> | <lock table statement> | <open statement> | <rollback statement> | <savepoint statement> | <select statement> | <select into statement> | <set statement> | <set transaction statement> | <start transaction statement> | <update statement> |
<dcl statement> ::= <alter user statement> | <create role statement> | <create user statement> | <drop role statement> | <drop user statement> | <grant statement> | <revoke statement> |
Nonexecutable statement:
<non-executable statement> ::= <begin declare statement> | <declare cursor statement> | <end declare statement> | <include statement> | <whenever statement> |
<procedural statement> ::= <begin-end block> | <call statement> | <close statement> | <declare condition statement> | <declare cursor statement> | <declare handler statement> | <declare variable statement> | <fetch cursor statement> | <flow control statement> | <open cursor statement> | <set statement> | <return statement> |
Flow-control statement:
<flow control statement> ::= <if statement> | <case statement> | <while statement> | <repeat statement> | <loop statement> | <leave statement> | <iterate statement> |
Alter database statement:
<alter database statement> ::=
ALTER DATABASE [ <database name> ]
[ <database option>... ]
|
<alter sequence statement> ::=
ALTER SEQUENCE [ <user name>. ] <sequence name>
[ <sequence option>... ]
<sequence option> ::=
RESTART [ WITH <integer literal> ] |
INCREMENT BY <integer literal> |
{ MAXVALUE <integer literal> | NOMAXVALUE } |
{ MINVALUE <integer literal> | NOMINVALUE } |
{ CYCLE | NOCYCLE } |
{ ORDER | NOORDER } |
{ CACHE <integer literal> | NOCACHE }
|
Alter table statement:
<alter table statement> ::= ALTER TABLE <table specification> <table |
<alter user statement> ::= ALTER USER <user name> IDENTIFIED BY <password> |
Begin declare statement:
<begin declare statement> ::= BEGIN DECLARE SECTION |
Call statement:
<call statement> ::=
CALL [ <database name> . ] <procedure name>
( <expression list> )
|
<case statement> ::=
{ CASE <expression>
WHEN <expression> THEN <statement list>
[ WHEN <expression> THEN <statement list> ]...
[ ELSE <statement list> ]
END CASE } |
{ CASE
WHEN <condition> THEN <statement list>
[ WHEN <condition> THEN <statement list> ]...
[ ELSE <statement list>
END CASE }
|
Close statement:
<close statement> ::= CLOSE <cursor name> |
Commit statement:
<commit statement> ::= COMMIT [ WORK ] |
Create database statement:
<create database statement> ::= CREATE DATABASE <database name> [ <database |
<create function statement> ::=
CREATE FUNCTION <function name>
( [ <parameter list for function> ] )
RETURNS <data type>
<function body>
<parameter list for function> ::=
<parameter specification for function>
[ { , <parameter specification for function>
|
Create index statement:
<create index statement> ::=
CREATE <index type> INDEX <index name>
ON <table specification>
( <column in index> [ { , <column in index>
|
Create procedure statement:
<create procedure statement> ::=
CREATE PROCEDURE <procedure name>
( [ <parameter list for procedure> ] )
<procedure body>
<parameter list for procedure> ::=
<parameter specification for procedure>
[ { , <parameter specification for procedure>
|
<create role statement> ::= CREATE ROLE <role name> |
Create sequence statement:
<create sequence statement> ::=
CREATE SEQUENCE [ <user name>. ] <sequence name>
[ <sequence option>... ]
<sequence option> ::=
START WITH <integer literal> |
INCREMENT BY <integer literal> |
{ MAXVALUE <integer literal> | NOMAXVALUE } |
{ MINVALUE <integer literal> | NOMINVALUE } |
{ CYCLE | NOCYCLE } |
{ ORDER | NOORDER } |
{ CACHE <integer literal> | NOCACHE }
|
Create table statement:
<create table statement> ::=
CREATE [ TEMPORARY ] TABLE
<table specification> <table structure>
<table structure> ::=
LIKE <table specification> |
( LIKE <table specification> ) |
<table contents> |
<table schema> [ <table contents> ]
|
<create trigger statement> ::=
CREATE TRIGGER <trigger name>
<trigger moment>
<trigger event>
[ <trigger condition> ]
<trigger action>
<trigger moment> ::=
BEFORE | AFTER | INSTEAD OF
<trigger event> ::=
{ INSERT | DELETE | UPDATE [ OF <column list> ] }
{ ON | OF | FROM | INTO } <table specification>
[ REFERENCING { OLD | NEW | OLD_TABLE | NEW_TABLE }
AS <variable> ]
FOR EACH { ROW | STATEMENT }
<trigger condition> ::= ( WHEN <condition> )
<trigger actie> ::= <begin-end block>
|
Create user statement:
<create user statement> ::= CREATE USER <user name> IDENTIFIED BY <password> |
Create view statement:
<create view statement> ::= CREATE [ OR REPLACE ] VIEW <view name> [ |
<declare condition statement> ::=
DECLARE <condition name> CONDITION FOR
{ SQLSTATE [ VALUE ] <sqlstate value> } |
<mysql error code> }
|
Declare cursor statement:
<declare cursor statement> ::=
DECLARE [ INSENSITIVE ] [ SCROLL ] <cursor name>
CURSOR FOR
<table expression>
[ <for clause> ]
|
Declare handler statement:
<declare handler statement> ::= DECLARE <handler type> HANDLER FOR <condition |
<declare variable statement> ::=
DECLARE <local variable list> <data type>
[ DEFAULT <expression> ]
|
Delete statement:
<delete statement> ::=
DELETE
FROM <table reference>
[ WHERE { <condition> | CURRENT OF <cursor name> } ]
|
Drop database statement:
<drop database statement> ::= DROP DATABASE <database name> |
Drop function statement:
<drop function statement> ::= DROP FUNCTION [ <database name> . ] <function name> |
Drop index statement:
<drop index statement> ::= DROP INDEX <index name> |
<drop procedure statement> ::= DROP PROCEDURE [ <database name> . ] <procedure |
Drop role statement:
<drop role statement> ::= DROP ROLE <role name> |
Drop sequence statement:
<drop sequence statement> ::= DROP SEQUENCE [ <user name>. ] <sequence name> |
Drop table statement:
<drop table statement> ::= DROP TABLE <table specification> |
Drop trigger statement:
<drop trigger statement> ::= DROP TRIGGER [ <table name> . ] <trigger name> |
<drop user statement> ::= DROP USER <user name> |
Drop view statement:
<drop view statement> ::= DROP VIEW <table specification> |
End declare statement:
<end declare statement> ::= END DECLARE SECTION |
Execute immediate statement:
<execute immediate statement> ::= EXECUTE IMMEDIATE <host variables> |
Fetch statement:
<fetch statement> ::= FETCH [ <direction> ] <cursor name> INTO <host variable list> <direction> ::= NEXT | PRIOR | FIRST | LAST | ABSOLUTE <whole number> | RELATIVE <whole number> |
<grant statement> ::=
<grant table privilege statement> |
<grant database privilege statement> |
<grant user privilege statement> |
<grant role statement> |
<grant execute statement> |
<grant sequence privilege statement>
<grant table privilege statement> ::=
GRANT <table privileges>
ON <table specification>
TO <grantees>
[ WITH GRANT OPTION ]
<grant database privilege statement> ::=
GRANT <database privileges>
ON [ <database name> . ] *
TO <grantees>
[ WITH GRANT OPTION ]
<grant user privilege statement> ::=
GRANT <user privileges>
ON *.*
TO <grantees>
[ WITH GRANT OPTION ]
<grant execute statement> ::=
GRANT EXECUTE
ON { <procedure name> | FUNCTION <function
|
<if statement> ::=
IF <condition> THEN <statement list>
[ ELSEIF <condition> THEN <statement list> ]...
[ ELSE <statement list> ]
END IF
|
Include statement:
<include statement> ::= INCLUDE <file> |
Insert statement:
<insert statement> ::= INSERT INTO <table specification> <insert |
Iterate statement:
<iterate statement> ::= ITERATE <label> |
Leave statement:
<leave statement> ::= LEAVE <label> |
<lock table statement> ::= LOCK TABLE <table specification> IN <lock type> |
Loop statement:
<loop statement> ::= [ <label> : ] LOOP <statement list> END LOOP [ |
Open statement:
<open statement> ::=
OPEN <cursor name>
[ USING <host variable> [ { , <host variable> }.
|
Rename table statement:
<rename table statement> ::= RENAME TABLE <table name change> <table name change> ::= <table name> TO <table name> |
Repeat statement:
<repeat statement> ::= [ <label> : ] REPEAT <statement list> UNTIL <condition> END REPEAT <label> |
<return statement> ::= RETURN <scalar expression> |
Revoke statement:
<revoke statement> ::=
<revoke table privilege statement> |
<revoke database privilege statement> |
<revoke user privilege statement> |
<revoke role statement> |
<revoke execute statement> |
<revoke sequence privilege statement>
<revoke table privilege statement> ::=
REVOKE <table privileges>
ON <table specification>
FROM <grantees>
<revoke database privilege statement> ::=
REVOKE <database privileges>
ON [ <database name> . ] *
FROM <grantees>
<revoke user privilege statement> ::=
REVOKE <user privileges>
ON *.*
FROM <grantees>
<revoke role statement> ::=
REVOKE <role name> [ { , <role name> }... ]
FROM <grantees>
<revoke execute statement> ::=
REVOKE EXECUTE
ON { <procedure name> | FUNCTION <function
|
<rollback statement> ::= ROLLBACK [ WORK ] [ TO [ SAVEPOINT ] <savepoint |
Savepoint statement:
<savepoint statement> ::= SAVEPOINT <savepoint name> |
Select statement:
<select statement> ::= <table expression> [ <for clause> ] |
Select into statement:
<select into statement> ::= <select clause> <into clause> [ <from clause> [ <where clause> ] [ <group by clause> [ <having clause> ] ] ] |
Set statement:
<set statement> ::=
SET <local variable definition>
[ {, <local variable definition> }... ]
<local variable definition> ::=
<local variable> { = | := } <scalar expression>
|
<set transaction statement> ::= SET TRANSACTION ISOLATION LEVEL <isolation level> <isolation level> ::= READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE |
Start transaction statement:
<start transaction statement> ::= START TRANSACTION |
Update statement:
<update statement> ::=
UPDATE <table reference>
SET <column assignment> [ { , <column
|
Whenever statement:
<whenever statement> ::= WHENEVER <whenever condition> <whenever action> <whenever condition> ::= SQLWARNING | SQLERROR | |
<while statement> ::= [ <label> : WHILE <condition> DO <statement list> END WHILE [ <label> ] |
This section contains the general common elements used in various SQL statements. The elements that are defined as a name are all grouped at the end of this section.
<aggregation function> ::=
COUNT ( [ DISTINCT | ALL ] { * | <scalar
|
Each of the previous singular scalar expressions has the following different forms:
<singular scalar expression> ::= <literal> | <column specification> | <system variable> | <cast expression> | <case expression> | NULL | ( <scalar expression> ) | <scalar function> | <aggregation function> | <scalar subquery> | <local variable> | <host variable> | <system parameter> | <sequence reference> <singular table expression> ::= <select block head> <sort direction> ::= ASC | DESC <sorting> ::= <scalar expression> [ <sort direction> ] | <sequence number> [<sort direction> ] | <column heading> [ <sort direction> ] <special symbol> ::= all special characters, such |