This appendix summarizes the SQL language understood by SQLite. It provides the syntax for all supported SQL statements.
A standard identifier name must begin with a letter or an underscore character, and may contain any number of alphanumeric characters or underscores. No other characters can be used. There is no enforced upper limit on the length of an identifier name. Names can be as long as you like, but don't make them so long that you dread having to type them in full each time.
Square brackets or double quotes can be used to indicate a non-standard identifier name to the SQLite parser. Identifier names enclosed in this way can include characters other than the underscore, including spaces and even other square brackets, and this also allows you to use SQL keywords as identifiers.
Note
It is not generally a good idea to use non-standard identifier names in your database; although, square brackets can still be used around standard identifier names without consequence if you are familiar with this syntax from SQL Server or Microsoft Access.
Tables C.1 through C.3 list the reserved keywords in SQLite. Table C.1 shows the fallback keywords, which can be used as identifiers without being delimited.
Table C.1. Fallback Keywords in SQLite
ABORT | AFTER | ASC | ATTACH | BEFORE |
BEGIN | DEFERRED | CASCADE | CLUSTER | CONFLICT |
COPY | CROSS | DATABASE | DELIMITERS | DESC |
DETACH | EACH | END | EXPLAIN | FAIL |
FOR | FULL | IGNORE | IMMEDIATE | INITIALLY |
INNER | INSTEAD | KEY | LEFT | MATCH |
NATURAL | OF | OFFSET | OUTER | PRAGMA |
RAISE | REPLACE | RESTRICT | RIGHT | ROW |
STATEMENT | TEMP | TEMPORARY | TRIGGER | VACUUM |
VIEW |
Table C.2 shows the normal keywords, which can only be used as identifier names if they are contained in square brackets or double quotes.
Table C.3 shows the names of some system objects in SQLite that can only be used as an identifier name for a different type of object.
This section details the SQL command syntax understood by SQLite. For clarity SQL keywords are shown in uppercase; however, SQLite is not case sensitive. Keywords and identifiers can be typed in uppercase, lowercase, or mixed case, and different capitalizations of the same string can be used interchangeably.
The CREATE object and DROP object statements are used to create and drop database objects.
To create a new database table, use CREATE TABLE.
CREATE [TEMP | TEMPORARY] TABLE table-name (
column-def [, column-def]*
[, constraint]*
)
A column in the CREATE TABLE statement is defined as follows:
name [type] [[CONSTRAINT name] column-constraint]*
To drop a table, use DROP TABLE.
DROP TABLE [database-name.] table-name
The optional column-constraint is composed of one or more of these keywords: NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY, CHECK, and COLLATE.
NOT NULL [ conflict-clause ]
NOT NULL enforces that the column must always contain a value. An error will be raised on any attempt to insert a NULL value into the column.
DEFAULT value
DEFAULT defines a value that the column should take if no value is given when a row is inserted.
UNIQUE [ conflict-clause ]
UNIQUE creates a UNIQUE INDEX on the column, ensuring that the same value cannot be entered into this column more than once. There can be more than one UNIQUE INDEX on a table if required.
PRIMARY KEY [sort-order] [ conflict-clause ]
PRIMARY KEY creates a UNIQUE INDEX on the column designated as primary key for the table. Additionally if the column type is INTEGER, this column is used internally as the actual key of the table and the value is assigned automatically by SQLite if it is not specified when a row is inserted. Only one PRIMARY KEY can be specified on each table.
CHECK ( expr ) [ conflict-clause ]
At the present time, the CHECK clause is ignored; however, SQLite allows one to be specified in the syntax for possible future use.
COLLATE collation-name
COLLATE specifies the text-collating function to be used when comparing values in this column and can be either TEXT or NUMERIC.
A UNIQUE, PRIMARY KEY, or CHECK constraint clause can also be specified after the column-def section using the following syntax:
To create an index—a sorting key on a database table—use CREATE INDEX.
CREATE [UNIQUE] INDEX index-name ON [database-name.] table-name ( column-name [, column-name]* ) [ ON CONFLICT conflict-algorithm ]
There is no limit to the number of indexes that can be added to a single table, nor on the number of columns in any index.
To drop an index, use DROP INDEX.
To create a view—a pseudo-table based on a query on one or more tables—use CREATE VIEW.
CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement
You can SELECT from a view in the same way as a regular table, but you cannot perform a COPY, DELETE, INSERT, or UPDATE operation on a view.
To drop a view, use DROP VIEW.
DROP VIEW view-name
To create a trigger—a procedure that executes automatically on a type of database event—on a table use CREATE TRIGGER.
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ] database-event ON [database-name.] table-name trigger-action
To create a trigger on a view, use this syntax instead:
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF database-event ON [database-name.] view-name trigger-action
The database-event can be DELETE, INSERT, UPDATE, or UPDATE OF column-list, any of which can reference the before and after values of a column using OLD.column-name and NEW.column-name respectively.
The syntax of trigger-action is as follows:
[ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ]
BEGIN
trigger-step ; [ trigger-step ; ]*
END
A trigger-step can be any DELETE, INSERT, SELECT, or UPDATE statement or the special RAISE function to cause an exception to be raised in the SQL statement that caused the trigger to fire. The syntax for RAISE is as follows:
RAISE ( ABORT, error-message ) | RAISE ( FAIL, error-message ) | RAISE ( ROLLBACK, error-message ) | RAISE ( IGNORE )
To drop a trigger use DROP TRIGGER.
DROP TRIGGER [database-name.] trigger-name
To query records from a database use the SELECT statement, which has the following syntax:
SELECT [ALL | DISTINCT] result [FROM table-list] [WHERE expr] [GROUP BY expr-list] [HAVING expr] [compound-op select]* [ORDER BY sort-expr-list] [LIMIT integer [( OFFSET | , ) integer]]
The result takes the form of a list of one or more comma-separated columns, optionally prefixed with the table name or table alias to avoid ambiguity. A column alias can be specified with the AS keyword following a column identifier. The * character can be used in place of a column list to represent every column from the selected tables.
The table-list is either a comma-separated list of table or view names with optional aliases given following the keyword AS, or another SELECT statement supplied in parentheses. Tables can also be joined to the table-list using the following syntax.
[NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN table-name [ON expr] [USING ( id-list )]
The compound-op can be used to connect SELECT queries using set operations performed on the queries on either side of the operator. When three or more queries are compounded, they are evaluated in pairs working from left to right.
The operator can be one of the following keywords: UNION, UNION ALL, INTERSECT, or EXCEPT.
UNION
UNION takes the union of both queries, causing the result of both queries to be returned in one operation. Rows that appear in the result of both queries are returned only once, so the overall result will contain no duplicate rows.
UNION ALL
UNION ALL is the same as UNION except that duplicate rows may appear in the result.
INTERSECT
INTERSECT takes the intersection of the two datasets, causing only rows that appear in both queries—either side of the operator—to be returned once.
EXCEPT
EXCEPT takes the difference between the datasets returned by the queries. All rows from the result to the left query will be returned except those that also appear in the query to the right of the operator.
To insert records into a table with a list of values given as part of the statement, use the following syntax for the INSERT statement:
INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES(value-list)
To insert the dataset returned by a SELECT query into a database table, use this syntax for INSERT.
INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement
The REPLACE statement is actually an alias for INSERT OR REPLACE provided for convenience. It can be considered to have its own syntax, as follows:
REPLACE INTO [database-name.] table-name [( column-list )] VALUES ( value-list ) REPLACE INTO [database-name.] table-name [( column-list )] select-statement
Refer to the section “Resolving Conflicts” later in this appendix for more information on this behavior.
To alter records in a database table, use the UPDATE statement with the following syntax:
UPDATE [ OR conflict-algorithm ] [database-name.] table-name SET assignment [, assignment]* [WHERE expr]
Each assignment simply takes the form column-name = expr. The expression expr may reference other column values, and all expressions are evaluated before any assignments are made.
When used without a WHERE clause, the update will be performed on every record in the database.
To delete records from a database, use the DELETE statement.
DELETE FROM [database-name .] table-name [WHERE expr]
When used without a WHERE clause, every row in the table will be deleted.
To load a large amount of data into a table, use the COPY command.
COPY [ OR conflict-algorithm ] [database-name.] table-name FROM filename [ USING DELIMITERS delim ]
The filename can be a file in the current directory or can contain a relative or absolute path. Using stdin for the filename will read data from standard input.
The ON CONFLICT and OR clauses are both used to define the conflict resolution algorithm to be used when a column constraint has been violated.
An ON-CONFLICT clause can be given in the CREATE TABLE and CREATE INDEX statements to specify the default behavior for that table. An OR clause forms part of a COPY, INSERT, or UPDATE statement and overrides any ON-CONFLICT clause on the table.
The ON-CONFLICT or OR keyword is simply followed by one of the following keywords to specify the conflict resolution algorithm to be used: ROLLBACK, ABORT, FAIL, IGNORE, or REPLACE.
ROLLBACK
Upon a violation, end the current transaction with a ROLLBACK and abort the current SQL command with a return code of SQLITE_CONSTRAINT. If there is no explicit transaction, the action is the same as ABORT.
ABORT
Upon a violation, the current SQL command is aborted with return code SQLITE_CONSTRAINT and any changes already made as part of that command are reversed. However, a ROLLBACK is not issued, so any changes from previous commands within the same transaction are committed. This is the default behavior.
FAIL
The SQL command that caused the violation will abort with return code SQLITE_CONSTRAINT; however, any changes made up to that point will be committed. For example, in an UPDATE operation, records that match the WHERE criteria that SQLite encounters before the row that causes the violation will be updated, but subsequent matching rows will not be affected.
IGNORE
When a violation occurs, that row is simply ignored and the SQL statement continues executing normally. No error is returned and the operation will be carried out successfully on every row that does not cause a violation.
REPLACE
Upon violation of a UNIQUE constraint, the record that already exists that prevents the update or insert operation from taking place is removed—without any delete triggers being fired—and the SQL statement continues as normal. Therefore an INSERT or UPDATE statement always takes place and no error is returned. If a NOT NULL constraint is violated and there is no DEFAULT value on the column, the ABORT algorithm is used instead.
To begin a transaction—an atomic block of statements that alter the database—use BEGIN TRANSACTION.
BEGIN [TRANSACTION [name]]
The keyword TRANSACTION is optional, and the name argument, if given, is ignored. SQLite does not support nested transactions.
To end the transaction, use one of the following commands:
COMMIT [TRANSACTION [name]]
ends the transaction with any changes that have been made saved to the database.
ROLLBACK [TRANSACTION [name]]
ends the transaction, discarding any changes made within that transaction.
To attach another database file to the current SQLite session, use ATTACH DATABASE.
ATTACH [DATABASE] database-filename AS database-name
The DATABASE keyword is optional. SQLite will search for database-filename in the current working directory if no path is given. After a database has been successfully attached, its tables can be referenced in SQL as database-name.table-name.
To detach a database file so that it can no longer be accessed by SQLite, use DETACH DATABASE.
DETACH [DATABASE] database-name
Use the VACUUM command to clean up your database.
VACUUM [index-or-table-name]
VACUUM copies the named index or table or, if no name is given, the entire database to a temporary area of disk and reloads the original database from that copy. By doing so, free pages in the database file are removed and the data in the file is made contiguous.
The EXPLAIN command causes SQLite to report back the virtual machine instructions that would be used to execute the command.
EXPLAIN sql-statement
From the sqlite program, the .explain command can be used to quickly set a suitable output format for the EXPLAIN command. Note that the command in sql-statement is not actually executed.
Use the PRAGMA command to modify the operation of the SQLite Library and retrieve information about the connected database.
PRAGMA name [= value] PRAGMA function(arg)
These are the currently supported pragmas, which are discussed in depth in Chapter 10, “General Database Administration.”
PRAGMA database_list PRAGMA index_list(table-name) PRAGMA index_info(index-name) PRAGMA table_info(table-name) PRAGMA foreign_key_list(table-name) PRAGMA cache_size = Number-of-pages; PRAGMA default_cache_size = Number-of-pages; PRAGMA synchronous = FULL | NORMAL | OFF PRAGMA default_synchronous = FULL | NORMAL | OFF PRAGMA temp_store = DEFAULT | MEMORY | FILE PRAGMA default_temp_store = DEFAULT | MEMORY | FILE PRAGMA integrity_check PRAGMA parser_trace = ON | OFF PRAGMA vdbe_trace = ON | OFF
The SQL language implemented by SQLite is fairly comprehensive; however, a few commands and features of the ANSI-92 specification are not available.
SQLite does not allow the schema of a table to be changed, so the ALTER TABLE command is not supported.
To add, remove, or modify columns in a table, you must drop the table and re-create it with the revised schema. The usual way to do this is with a temporary table to hold the data from the old table while it is being re-created.
The DISTINCT keyword cannot appear inside a COUNT function. Instead you must use a nested subquery.
SELECT COUNT(DISTINCT mycol) FROM mytable;
becomes
SELECT COUNT(*) FROM ( SELECT DISTINCT mycol FROM mytable );
Because access to SQLite databases takes place at the filesystem level, the only permissions that can be applied are those available to the underlying operating system. Therefore the GRANT and REVOKE commands are meaningless for SQLite.
SQLite does not allow write actions to be performed directly on a view, even if there is only one base table in the view. However, a trigger can be created on a view using the INSTEAD OF syntax, in which you can perform the appropriate INSERT, UPDATE, or DELETE on the underlying table(s).
LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN. Therefore the table order in your queries must allow outer joins to be performed in this direction.
Although the SQL syntax allows CHECK and FOREIGN KEY clauses to be included, they are ignored. They may be implemented in a future version.
SQLite does not support the FOR EACH STATEMENT type of trigger, or INSTEAD OF triggers on tables. INSTEAD OF triggers can only be used on views.