+ 
+ 
+ 
+ 
+ 
+ 
+ 
+
+
+ +
+ [H1 Header]
++ [Document Text] +
++ [Sub Section]
++ [Sub Section Text] +
++ +
| + [Running Header] | +
| + [Header Row] + | +
| + [Running Header] | +
| + [Header Row] + | +
+ [Document Text] +
++ [Sub Section Text] +
+| + ALTER TABLE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ ALTER TABLE [database-name .] table-name alteration | +
| + alteration ::= | ++ RENAME TO new-table-name | +
| + alteration ::= | ++ ADD [COLUMN] column-def | +
+ SQLite's version of the ALTER TABLE command allows the user to rename or add a new + column to an existing table. It is not possible to remove a column from a table. +
++ The RENAME TO syntax is used to rename the table identified by [database-name.]table-name + to new-table-name. This command cannot be used to move a table between attached + databases, only to rename a table within the same database.
++ If the table being renamed has triggers or indices, then these remain attached to + the table after it has been renamed. However, if there are any view definitions, + or statements executed by triggers that refer to the table being renamed, these + are not automatically modified to use the new table name. If this is required, the + triggers or view definitions must be dropped and recreated to use the new table + name by hand. +
++ The ADD [COLUMN] syntax is used to add a new column to an existing table. The new + column is always appended to the end of the list of existing columns. Column-def + may take any of the forms permissable in a CREATE TABLE statement, with the following + restrictions: +
++ The execution time of the ALTER TABLE command is independent of the amount of data + in the table. The ALTER TABLE command runs as quickly on a table with 10 million + rows as it does on a table with 1 row. +
++ After ADD COLUMN has been run on a database, that database will not be readable + by SQLite version 3.1.3 and earlier until the database is + VACUUMed.
++
+| + ANALYZE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ ANALYZE | +
| + sql-statement ::= | ++ ANALYZE database-name | +
| + sql-statement ::= | ++ ANALYZE [database-name .] table-name | +
+ The ANALYZE command gathers statistics about indices and stores them in a special + tables in the database where the query optimizer can use them to help make better + index choices. If no arguments are given, all indices in all attached databases + are analyzed. If a database name is given as the argument, all indices in that one + database are analyzed. If the argument is a table name, then only indices associated + with that one table are analyzed.
++ The initial implementation stores all statistics in a single table named sqlite_stat1. + Future enhancements may create additional tables with the same name pattern except + with the "1" changed to a different digit. The sqlite_stat1 table cannot be DROPped, but all the content can be + DELETEd which has the same effect.
++
| + ATTACH DATABASE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ ATTACH [DATABASE] database-filename AS database-name | +
+ The ATTACH DATABASE statement adds another database file to the current database + connection. If the filename contains punctuation characters it must be quoted. The + names 'main' and 'temp' refer to the main database and the database used for temporary + tables. These cannot be detached. Attached databases are removed using the + DETACH DATABASE statement.
++ You can read from and write to an attached database and you can modify the schema + of the attached database. This is a new feature of SQLite version 3.0. In SQLite + 2.8, schema changes to attached databases were not allowed.
++ You cannot create a new table with the same name as a table in an attached database, + but you can attach a database which contains tables whose names are duplicates of + tables in the main database. It is also permissible to attach the same database + file multiple times.
++ Tables in an attached database can be referred to using the syntax database-name.table-name. + If an attached table doesn't have a duplicate table name in the main database, it + doesn't require a database name prefix. When a database is attached, all of its + tables which don't have duplicate names become the default table of that name. Any + tables of that name attached afterwards require the table prefix. If the default + table of a given name is detached, then the last table of that name attached becomes + the new default.
++ Transactions involving multiple attached databases are atomic, assuming that the + main database is not ":memory:". If the main database is ":memory:" then transactions + continue to be atomic within each individual database file. But if the host computer + crashes in the middle of a COMMIT where two or more database files are updated, + some of those files might get the changes where others might not. Atomic commit + of attached databases is a new feature of SQLite version 3.0. In SQLite version + 2.8, all commits to attached databases behaved as if the main database were ":memory:". +
++ There is a compile-time limit of 10 attached database files.
++
| + comment | +
| + SQLite Language Reference Documentation + | +
+
| + comment ::= | ++ SQL-comment | C-comment | +
| + SQL-comment ::= | ++ -- single-line | +
| + C-comment ::= | ++ /* multiple-lines [*/] | +
+ Comments aren't SQL commands, but can occur in SQL queries. They are treated as + whitespace by the parser. They can begin anywhere whitespace can be found, including + inside expressions that span multiple lines. +
++ SQL comments only extend to the end of the current line.
++ C comments can span any number of lines. If there is no terminating delimiter, they + extend to the end of the input. This is not treated as an error. A new SQL statement + can begin on a line after a multiline comment ends. C comments can be embedded anywhere + whitespace can occur, including inside expressions, and in the middle of + other SQL + statements. C comments do not nest. SQL comments inside a C comment will be ignored. +
++
| + ON CONFLICT clause | +
| + SQLite Language Reference Documentation + | +
+
| + conflict-clause ::= | ++ ON CONFLICT conflict-algorithm | +
| + conflict-algorithm ::= | ++ ROLLBACK | + ABORT | FAIL | IGNORE | REPLACE | +
+ The ON CONFLICT clause is not a separate SQL command. It is a non-standard clause + that can appear in many + other SQL commands. It is given its own section in this + document because it is not part of standard SQL and therefore might not be familiar.
++ The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE command. + For the INSERT and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", + to make the syntax seem more natural. For example, instead of "INSERT ON CONFLICT + IGNORE" we have "INSERT OR IGNORE". The keywords change but the meaning of the clause + is the same either way.
++ The ON CONFLICT clause specifies an algorithm used to resolve constraint conflicts. + There are five choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default + algorithm is ABORT. This is what they mean:
++ When a constraint violation occurs, an immediate ROLLBACK occurs, thus ending the + current transaction, and the command aborts with a return code of SQLITE_CONSTRAINT. + If no transaction is active (other than the implied transaction that is created + on every command) then this algorithm works the same as ABORT.
++ When a constraint violation occurs, the command backs out any prior changes it might + have made and aborts with a return code of SQLITE_CONSTRAINT. But no ROLLBACK is + executed so changes from prior commands within the same transaction are preserved. + This is the default behavior.
++ When a constraint violation occurs, the command aborts with a return code SQLITE_CONSTRAINT. + But any changes to the database that the command made prior to encountering the + constraint violation are preserved and are not backed out. For example, if an UPDATE + statement encountered a constraint violation on the 100th row that it attempts to + update, then the first 99 row changes are preserved but changes to rows 100 and + beyond never occur.
++ When a constraint violation occurs, the one row that contains the constraint violation + is not inserted or changed. But the command continues executing normally. Other + rows before and after the row that contained the constraint violation continue to + be inserted or updated normally. No error is returned.
++ When a UNIQUE constraint violation occurs, the pre-existing rows that are causing + the constraint violation are removed prior to inserting or updating the current + row. Thus the insert or update always occurs. The command continues executing normally. + No error is returned. If a NOT NULL constraint violation occurs, the NULL value + is replaced by the default value for that column. If the column has no default value, + then the ABORT algorithm is used. If a CHECK constraint violation occurs then the + IGNORE algorithm is used.
++ When this conflict resolution strategy deletes rows in order to satisfy a constraint, + it does not invoke delete triggers on those rows. This behavior might change in + a future release.
++ The algorithm specified in the OR clause of a INSERT or UPDATE overrides any algorithm + specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm + is used.
++
| + CREATE INDEX | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | +
+ CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .]
+ index-name
+ + ON table-name + ( column-name + [, + column-name]* ) |
+
| + column-name ::= | ++ name [ COLLATE collation-name] [ ASC | DESC ] | +
+ The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the + name of the new index, the keyword "ON", the name of a previously created table + that is to be indexed, and a parenthesized list of names of columns in the table + that are used for the index key. Each column name can be followed by one of the + "ASC" or "DESC" keywords to indicate sort order, but the sort order is ignored in + the current implementation. Sorting is always done in ascending order.
++ The COLLATE clause following each column name defines a collating sequence used + for text entires in that column. The default collating sequence is the collating + sequence defined for that column in the CREATE TABLE statement. Or if no collating + sequence is otherwise defined, the built-in BINARY collating sequence is used.
++ There are no arbitrary limits on the number of indices that can be attached to a + single table, nor on the number of columns in an index.
++ If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries + are not allowed. Any attempt to insert a duplicate entry will result in an error.
++ The exact text of each CREATE INDEX statement is stored in the sqlite_master + or sqlite_temp_master table, depending on whether the table being indexed + is temporary. Every time the database is opened, all CREATE INDEX statements are + read from the sqlite_master table and used to regenerate + SQLite's internal + representation of the index layout.
++ If the optional IF NOT EXISTS clause is present and another index with the same name aleady exists, then this command becomes a no-op.
++ Indexes are removed with the DROP INDEX command.
++
| + CREATE TABLE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-command ::= | +
+ CREATE [TEMP
+ | TEMPORARY]
+ TABLE [IF NOT EXISTS]
+ [database-name .]
+ table-name ( + column-def + [, + column-def]* + [, + constraint]* + ) |
+
| + sql-command ::= | ++ CREATE [TEMP + | TEMPORARY] + TABLE [database-name.] + table-name AS + select-statement | +
| + column-def ::= | ++ name [type] [[CONSTRAINT name] + column-constraint]* | +
| + type ::= | +
+ typename | + typename + ( number + ) | + typename + ( number + , number + ) |
+
| + column-constraint ::= | +
+ NOT NULL [
+ conflict-clause
+ ] | + PRIMARY KEY [sort-order] [ + conflict-clause + ] [AUTOINCREMENT] | + UNIQUE [ conflict-clause ] | + CHECK ( expr + ) | + DEFAULT value + | + COLLATE collation-name |
+
| + constraint ::= | +
+ PRIMARY KEY (
+ column-list ) [ conflict-clause ] | + UNIQUE ( column-list ) [ + conflict-clause + ] | + CHECK ( expr + ) |
+
| + conflict-clause ::= | ++ ON CONFLICT conflict-algorithm | +
+ A CREATE TABLE statement is basically the keywords "CREATE TABLE" followed by the + name of a new table and a parenthesized list of column definitions and constraints. + The table name can be either an identifier or a string. Tables names that begin + with "sqlite_" are reserved for use by the engine.
++ Each column definition is the name of the column followed by the datatype for that + column, then one or more optional column constraints. The datatype for the column + does not restrict what data may be put in that column. See + Datatypes In SQLite Version 3 for additional information. The UNIQUE constraint + causes an index to be created on the specified columns. This index must contain + unique keys. The COLLATE clause specifies what text + collating function to use when comparing text entries for the column. The + built-in BINARY collating function is used by default. +
++ The DEFAULT constraint specifies a default value to use when doing an INSERT. The + value may be NULL, a string constant or a number. Starting with version 3.1.0, the + default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, + it is literally inserted into the column whenever an INSERT statement that does + not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE + or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the + columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. + The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS". +
++ Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding + columns. However, if primary key is on a single column that has datatype INTEGER, + then that column is used internally as the actual key of the B-Tree for the table. + This means that the column may only hold unique integer values. (Except for this + one case, SQLite ignores the datatype specification of columns and allows any kind + of data to be put in a column regardless of its declared datatype.) If a table does + not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically + generated integer. The B-Tree key for a row can always be accessed using one of + the special names "ROWID", "OID", or "_ROWID_". This is true + regardless of whether or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY + KEY column man also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword + modified the way that B-Tree keys are automatically generated. Additional detail + on automatic B-Tree key generation is available + separately.
++ If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then + the table that is created is only visible within that same database connection and + is automatically deleted when the database connection is closed. Any indices created + on a temporary table are also temporary. Temporary tables and indices are stored + in a separate file distinct from the main database file.
++ If a <database-name> is specified, then the table is created in the named + database. It is an error to specify both a <database-name> and the TEMP keyword, + unless the <database-name> is "temp". If no database name is specified, and + the TEMP keyword is not present, the table is created in the main database.
++ The optional conflict-clause following each constraint allows the specification + of an alternative default constraint conflict resolution algorithm for that constraint. The default is abort ABORT. Different constraints within the same table may have + different default conflict resolution algorithms. If an COPY, INSERT, or UPDATE + command specifies a different conflict resolution algorithm, then that algorithm + is used in place of the default algorithm specified in the CREATE TABLE statement. + See the section titled ON CONFLICT for additional + information.
++ CHECK constraints are supported as of version 3.3.0. Prior to version 3.3.0, CHECK + constraints were parsed but not enforced.
++ There are no arbitrary limits on the number of columns or on the number of constraints + in a table. The total amount of data in a single row is limited to about 1 megabytes + in version 2.8. In version 3.0 there is no arbitrary limit on the amount of data + in a row.
++ The CREATE TABLE AS form defines the table to be the result set of a query. The + names of the table columns are the names of the columns in the result.
++ The exact text of each CREATE TABLE statement is stored in the sqlite_master + table. Every time the database is opened, all CREATE TABLE statements are read from + the sqlite_master table and used to regenerate + SQLite's internal representation + of the table layout. If the original command was a CREATE TABLE AS then then an + equivalent CREATE TABLE statement is synthesized and store in sqlite_master + in place of the original command. The text of CREATE TEMPORARY TABLE statements + are stored in the sqlite_temp_master table. +
++ If the optional IF NOT EXISTS clause is present and another table with the same + name aleady exists, then this command becomes a no-op.
++ Tables are removed using the DROP TABLE statement. +
++
| + CREATE TRIGGER | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | +
+ CREATE [TEMP
+ | TEMPORARY]
+ TRIGGER trigger-name
+ [ BEFORE |
+ AFTER ] + database-event + ON [database-name .] + table-name + trigger-action |
+
| + sql-statement ::= | +
+ CREATE [TEMP
+ | TEMPORARY]
+ TRIGGER trigger-name
+ INSTEAD OF + database-event + ON [database-name .] + view-name + trigger-action |
+
| + database-event ::= | +
+ DELETE |
+ + INSERT | + + UPDATE | + + UPDATE OF column-list |
+
| + trigger-action ::= | +
+ [ FOR EACH ROW
+ | FOR EACH STATEMENT ] [ WHEN
+ expression ]
+ + BEGIN + + trigger-step ; [ + trigger-step ; ]* + END |
+
| + trigger-step ::= | +
+ update-statement | insert-statement
+ |
+ + delete-statement + | + select-statement |
+
+ The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers + are database operations (the trigger-action) that are automatically performed + when a specified database event (the database-event) occurs. +
++ A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular + database table occurs, or whenever an UPDATE of one or more specified columns of + a table are updated.
++ At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT + triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies + that the SQL statements specified as trigger-steps may be executed (depending + on the WHEN clause) for each database row being inserted, updated or deleted by + the statement causing the trigger to fire.
++ Both the WHEN clause and the trigger-steps may access elements of the row + being inserted, deleted or updated using references of the form "NEW.column-name" + and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be + used in triggers on trigger-events for which they are relevant, as follows:
++
| + INSERT | ++ NEW references are valid | +
| + UPDATE | ++ NEW and OLD references are valid | +
| + DELETE | ++ OLD references are valid | +
+
++ If a WHEN clause is supplied, the SQL statements specified as trigger-steps + are only executed for + rows for which the WHEN clause is true. If no WHEN clause + is supplied, the SQL statements are executed for all rows.
++ The specified trigger-time determines when the trigger-steps will + be executed relative to the insertion, modification or removal of the associated + row.
++ An ON CONFLICT clause may be specified as part of an UPDATE or INSERT trigger-step. + However if an ON CONFLICT clause is specified as part of the statement causing the + trigger to fire, then this conflict handling policy is used instead.
++ Triggers are automatically dropped when the table that they are associated with + is dropped.
++ Triggers may be created on views, as well as ordinary tables, by specifying INSTEAD + OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE or ON UPDATE + triggers are defined on a view, then it is not an error to execute an INSERT, DELETE + or UPDATE statement on the view, respectively. Thereafter, executing an INSERT, + DELETE or UPDATE on the view causes the associated triggers to fire. The real tables + underlying the view are not modified (except possibly explicitly, by a trigger program).
++ Example:
++ Assuming that customer records are stored in the "customers" table, and that order + records are stored in the "orders" table, the following trigger ensures that all + associated orders are redirected when a customer changes his or her address:
++++CREATE TRIGGER update_customer_address UPDATE OF address ON customers + BEGIN + UPDATE orders SET address = new.address WHERE customer_name = old.name; + END; ++
+ With this trigger installed, executing the statement:
++++UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones'; ++
+ causes the following to be automatically executed:
++++UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones'; ++
+ Note that currently, triggers may behave oddly when created on tables with INTEGER + PRIMARY KEY fields. If a BEFORE trigger program modifies the INTEGER PRIMARY KEY + field of a row that will be subsequently updated by the statement that causes the + trigger to fire, then the update may not occur. The workaround is to declare the + table with a PRIMARY KEY column instead of an INTEGER PRIMARY KEY column.
++ A special SQL function RAISE() may be used within a trigger-program, with the following + syntax
++
| + raise-function ::= | +
+ RAISE ( ABORT, error-message )
+ |
+ + RAISE ( FAIL, error-message ) | + + RAISE ( ROLLBACK, error-message ) | + + RAISE ( IGNORE ) |
+
+ When one of the first three forms is called during trigger-program execution, the + specified ON CONFLICT processing is performed (either ABORT, FAIL or ROLLBACK) and + the current query terminates. An error code of SQLITE_CONSTRAINT is returned to + the user, along with the specified error message.
++ When RAISE(IGNORE) is called, the remainder of the current trigger program, the + statement that caused the trigger program to execute and any subsequent trigger + programs that would of been executed are abandoned. No database changes are rolled + back. If the statement that caused the trigger program to execute is itself part + of a trigger program, then that trigger program resumes execution at the beginning + of the next step. +
++ Triggers are removed using the DROP TRIGGER + statement.
++
| + CREATE VIEW | +
| + SQLite Language Reference Documentation + | +
+
| + sql-command ::= | ++ CREATE [TEMP + | TEMPORARY] + VIEW [database-name.] + view-name AS + select-statement | +
+ The CREATE VIEW command assigns a name to a pre-packaged + SELECT statement. Once the view is created, it can be used in the FROM clause + of another SELECT in place of a table name. +
++ If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then + the view that is created is only visible to the process that opened the database + and is automatically deleted when the database is closed.
++ If a <database-name> is specified, then the view is created in the named database. + It is an error to specify both a <database-name> and the TEMP keyword, unless + the <database-name> is "temp". If no database name is specified, and the TEMP + keyword is not present, the table is created in the main database.
++ You cannot COPY, DELETE, INSERT or UPDATE a view. Views are read-only in SQLite. + However, in many cases you can use a TRIGGER + on the view to accomplish the same thing. Views are removed with the + DROP VIEW command.
++
| + CREATE VIRTUAL TABLE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-command ::= | ++ CREATE VIRTUAL TABLE [database-name .] table-name USING module-name [( + arguments )] | +
+ A virtual table is an interface to an external storage or computation engine that + appears to be a table but does not actually store information in the database file.
++ In general, you can do anything with a virtual table that can be done with an ordinary + table, except that you cannot create triggers on a virtual table. Some virtual table + implementations might impose additional restrictions. For example, many virtual + tables are read-only.
++ The <module-name> is the + name of an object that implements the virtual table. + The <module-name> must be registered with the SQLite database connection using + sqlite3_create_module prior to + issuing the CREATE VIRTUAL TABLE statement. The module takes zero or more comma-separated + arguments. The arguments can be just about any text as long as it has balanced parentheses. + The argument syntax is sufficiently general that the arguments can be made to appear + as column definitions in a traditional CREATE TABLE + statement. SQLite passes the module arguments directly to the module without any + interpretation. It is the responsibility of the module implementation to parse and + interpret its own arguments.
++ A virtual table is destroyed using the ordinary DROP + TABLE statement. There is no DROP VIRTUAL TABLE statement.
++
| + DELETE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ DELETE FROM [database-name .] table-name [WHERE + expr] | +
+ The DELETE command is used to remove records from a table. The command consists + of the "DELETE FROM" keywords followed by the + name of the table from which records + are to be removed. +
++ Without a WHERE clause, all rows of the table are removed. If a WHERE clause is + supplied, then only those rows that match the expression are removed.
++
| + DETACH | +
| + SQLite Language Reference Documentation + | +
+
| + sql-command ::= | ++ DETACH [DATABASE] database-name | +
+ This statement detaches an additional database connection previously attached using + the ATTACH DATABASE statement. It is possible to have the same database file attached multiple times using different names, and detaching + one connection to a file will leave the others intact.
++ This statement will fail if SQLite is in the middle of a transaction.
++
| + DROP INDEX | +
| + SQLite Language Reference Documentation + | +
+
| + sql-command ::= | ++ DROP INDEX [IF EXISTS] [database-name .] + index-name | +
+ The DROP INDEX statement removes an index added with the + CREATE INDEX statement. The index named is completely removed from the disk. + The only way to recover the index is to reenter the appropriate CREATE INDEX command.
++ The DROP INDEX statement does not reduce the size of the database file in the default + mode. Empty space in the database is retained for later INSERTs. To remove free + space in the database, use the + VACUUM command. If + AUTOVACUUM mode is enabled for a database then space will be freed automatically by DROP INDEX.
++
| + DROP TABLE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-command ::= | ++ DROP TABLE [IF EXISTS] [database-name.] + table-name | +
+ The DROP TABLE statement removes a table added with the CREATE TABLE statement. The + name specified is the table name. It is completely removed + from the database schema and the disk file. The table can not be recovered. All + indices associated with the table are also deleted.
++ The DROP TABLE statement does not reduce the size of the database file in the default + mode. Empty space in the database is retained for later INSERTs. To remove free + space in the database, use the + VACUUM command. If + AUTOVACUUM mode is enabled for a database then space will be freed automatically by DROP TABLE.
++ The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.
++
| + DROP TRIGGER | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ DROP TRIGGER [database-name .] trigger-name | +
+ The DROP TRIGGER statement removes a trigger created by the + CREATE TRIGGER statement. The trigger is deleted from the database schema. + Note that triggers are automatically dropped when the associated table is dropped.
++
| + DROP VIEW | +
| + SQLite Language Reference Documentation + | +
+
| + sql-command ::= | ++ DROP VIEW view-name | +
+ The DROP VIEW statement removes a view created by the CREATE VIEW statement. The + name specified is the view name. It is removed from the + database schema, but no actual data + in the underlying base tables is modified.
++
| + EXPLAIN | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ EXPLAIN sql-statement | +
+ The EXPLAIN command modifier is a non-standard extension. The idea comes from a similar command found in PostgreSQL, but the operation is completely different.
++ If the EXPLAIN keyword appears before any + other SQLite SQL command then instead + of actually executing the command, the SQLite library will report back the sequence + of virtual machine instructions it would have used to execute the command had the + EXPLAIN keyword not been present. For additional information about virtual machine + instructions see the architecture description + or the documentation on available opcodes + for the virtual machine.
++
| + expression | +
| + SQLite Language Reference Documentation + | +
+
| + expr ::= | +
+ expr binary-op expr
+ | + expr + [NOT] + like-op + expr [ESCAPE expr] | + unary-op + expr + | + ( expr + ) | + column-name + | + table-name + . column-name + | + database-name + . table-name + . column-name + | + literal-value + | + parameter + | + function-name + ( expr-list + | * ) + | + expr ISNULL + | + expr NOTNULL + | + expr + [NOT] BETWEEN + expr AND + expr + | + expr + [NOT] IN ( + value-list + ) | + expr + [NOT] IN ( + select-statement + ) | + expr + [NOT] IN + [database-name .] + table-name + | + [EXISTS] + ( select-statement + ) | + CASE [expr] ( + WHEN expr + THEN expr + )+ [ELSE + expr] END | + CAST ( expr + AS type + ) |
+
| + like-op ::= | ++ LIKE | GLOB + | REGEXP | MATCH | +
+ This section is different from the others. Most other sections of this document + talks about a particular SQL command. This section does not talk about a standalone + command but about "expressions" which are subcomponents of most other commands.
++ SQLite understands the following binary operators, in order from highest to lowest + precedence:
++++|| * / % + - << >> & | < <= > + >= = == != <> IN AND OR ++
+ Supported unary operators are these:
++++- + ! ~ NOT ++
+ The unary operator [Operator +] is a no-op. It can be applied to strings, numbers, + or blobs and it always gives as its result the value of the operand.
++ Note that there are two variations of the equals and not equals operators. Equals + can be either = or + ==. The non-equals operator can be either + != or <>. The + || operator is "concatenate" - it joins together the two + strings of its operands. The operator % + outputs the remainder of its left operand modulo its right operand.
++ The result of any binary operator is a numeric value, except for the + || concatenation operator which gives a string result.
+ ++ A literal value is an integer number or a floating point number. Scientific notation + is supported. The "." character is always used as the decimal point even if the + locale setting specifies "," for this role - the use of "," for the decimal point + would result in syntactic ambiguity. A string constant is formed by enclosing the + string in single quotes ('). A single quote within the string can be encoded by + putting two single quotes in a row - as in Pascal. C-style escapes using the backslash + character are not supported because they are not standard SQL. BLOB literals are + string literals containing hexadecimal data and preceded by a single "x" or "X" + character. For example:
++++X'53514697465' ++
+ A literal value can also be the token "NULL". +
++ A parameter specifies a placeholder in the expression for a literal value that is + filled in at runtime using the sqlite3_bind + API. Parameters can take several forms: +
++
| + ?NNN | ++ | ++ A question mark followed by a number NNN holds a spot for the NNN-th parameter. + NNN must be between 1 and 999. | +
| + ? | ++ | ++ A question mark that is not followed by a number holds a spot for the next unused + parameter. | +
| + :AAAA | ++ | ++ A colon followed by an identifier name holds a spot for a named parameter with the + name AAAA. Named parameters are also numbered. The number assigned is the next unused + number. To avoid confusion, it is best to avoid mixing named and numbered parameters. | +
| + @AAAA | ++ | ++ An "at" sign works exactly like a colon. | +
| + $AAAA | ++ | ++ A dollar-sign followed by an identifier name also holds a spot for a named parameter + with the name AAAA. The identifier name in this case can include one or more occurances + of "::" and a suffix enclosed in "(...)" containing any text at all. This syntax + is the form of a variable name in the Tcl programming language. | +
++
+ Parameters that are not assigned values using + sqlite3_bind are treated as NULL.
+ ++ The LIKE operator does a pattern matching comparison. The operand to the right contains + the pattern, the left hand operand contains the string to match against the pattern. + A percent symbol % in the pattern matches + any sequence of zero or more characters in the string. An underscore + _ in the pattern matches any single character in the string. + Any other character matches itself or it's lower/upper case equivalent (i.e. case-insensitive + matching). (A bug: SQLite only understands upper/lower case for 7-bit Latin characters. + Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 + characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE + 'Æ' is FALSE.).
++ If the optional ESCAPE clause is present, then the expression following the ESCAPE + keyword must evaluate to a string consisting of a single character. This character + may be used in the LIKE pattern to include literal percent or underscore characters. + The escape character followed by a percent symbol, underscore or itself matches + a literal percent symbol, underscore or escape character in the string, respectively. + The infix LIKE operator is implemented by calling the user function + like(X,Y).
++ The LIKE operator is not case sensitive and will match upper case characters on + one side against lower case characters on the other. (A bug: SQLite only understands + upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive + for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' + LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.). +
++
++ The infix LIKE operator is implemented by calling the user function + like(X,Y). If an ESCAPE clause is present, it adds a third parameter + to the function call. If the functionality of LIKE can be overridden by defining + an alternative implementation of the like() SQL function.
++
+ ++ The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for + its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may + be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator + is implemented by calling the user function glob(X,Y) + and can be modified by overriding that function.
+ ++ The REGEXP operator is a special syntax for the regexp() user function. No regexp() + user function is defined by default and so use of the REGEXP operator will normally + result in an error message. If a user-defined function named "regexp" is added at + run-time, that function will be called in order to implement the REGEXP operator.
+ ++ The MATCH operator is a special syntax for the match() user function. The default + match() function implementation raises and exception and is not really useful for + anything. But extensions can override the match() function with more helpful logic.
++ A column name can be any of the names defined in the CREATE TABLE statement or one + of the following special identifiers: "ROWID", "OID", or "_ROWID_". + These special identifiers all describe the unique random integer key (the "row key") + associated with every row of every table. The special identifiers only refer to + the row key if the CREATE TABLE statement does not define a real column with the + same name. Row keys act like read-only columns. A row key can be used anywhere a + regular column can be used, except that you cannot change the value of a row key + in an UPDATE or INSERT statement. "SELECT * ..." does not return the row key.
++ SELECT statements can appear in expressions as either the right-hand operand of + the IN operator, as a scalar quantity, or as the operand of an EXISTS operator. + As a scalar quantity or the operand of an IN operator, the SELECT should have only + a single column in its result. Compound SELECTs (connected with keywords like UNION + or EXCEPT) are allowed. With the EXISTS operator, the columns in the result set + of the SELECT are ignored and the expression returns TRUE if one or more rows exist + and FALSE if the result set is empty. If no terms in the SELECT expression refer + to value in the containing query, then the expression is evaluated once prior to + any other processing and the result is reused as necessary. If the SELECT expression + does contain variables from the outer query, then the SELECT is reevaluated every + time it is needed.
++ When a SELECT is the right operand of the IN operator, the IN operator returns TRUE + if the result of the left operand is any of the values generated by the select. + The IN operator may be preceded by the NOT keyword to invert the sense of the test.
++ When a SELECT appears within an expression but is not the right operand of an IN + operator, then the first row of the result of the SELECT becomes the value used + in the expression. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the SELECT + is NULL.
+
+ A CAST expression changes the datatype of the
+
+ Both simple and aggregate functions are supported. A simple function can be used + in any expression. Simple functions return a result immediately based on their inputs. + Aggregate functions may only be used in a SELECT statement. Aggregate functions + compute their result across all rows of the result set.
+ ++ The core functions shown below are available by default. Additional functions may + be written in C and added to the database engine using the + sqlite3_create_function() API.
++
| + abs(X) | ++ Return the absolute value of argument X. | +
| + coalesce(X,Y,...) | ++ Return a copy of the first non-NULL argument. If all arguments are NULL then NULL + is returned. There must be at least 2 arguments. | +
| + glob(X,Y) | ++ This function is used to implement the "X GLOB Y" syntax of SQLite. The sqlite3_create_function() interface + can be used to override this function and thereby change the operation of the GLOB operator. | +
| + ifnull(X,Y) | ++ Return a copy of the first non-NULL argument. If both arguments are NULL then NULL + is returned. This behaves the same as coalesce() above. | +
| + last_insert_rowid() | ++ Return the ROWID of the last row insert from this connection to the database. This + is the same value that would be returned from the sqlite_last_insert_rowid() + API function. | +
| + length(X) | ++ Return the string length of X in characters. If SQLite is configured to support + UTF-8, then the number of UTF-8 characters is returned, not the number of bytes. | +
| + like(X,Y [,Z]) | ++ This function is used to implement the "X LIKE Y [ESCAPE Z]" syntax of SQL. + If the optional ESCAPE clause is present, then the user-function is invoked with + three arguments. Otherwise, it is invoked with two arguments only. The + sqlite_create_function() interface can be used to override this function and + thereby change the operation of the LIKE operator. When doing + this, it may be important to override both the two and three argument versions of + the like() function. Otherwise, different code may be called to implement the LIKE + operator depending on whether or not an ESCAPE clause was specified. | +
|
+ load_extension(X) + load_extension(X,Y) |
+ + Load SQLite extensions out of the shared library file named X using the entry + point Y. The result is a NULL. If Y is omitted then the default entry + point of sqlite3_extension_init is used. This function raises an exception + if the extension fails to load or initialize correctly. + | +
| + lower(X) | ++ Return a copy of string X will all characters converted to lower case. The + C library tolower() routine is used for the conversion, which means that + this function might not work correctly on UTF-8 characters. | +
| + max(X,Y,...) | ++ Return the argument with the maximum value. Arguments may be strings in addition + to numbers. The maximum value is determined by the usual sort order. Note that + max() is a simple function when it has 2 or more arguments but converts to + an aggregate function if given only a single argument. | +
| + min(X,Y,...) | ++ Return the argument with the minimum value. Arguments may be strings in addition + to numbers. The minimum value is determined by the usual sort order. Note that + min() is a simple function when it has 2 or more arguments but converts to + an aggregate function if given only a single argument. | +
| + nullif(X,Y) | ++ Return the first argument if the arguments are different, otherwise return NULL. | +
| + quote(X) | ++ This routine returns a string which is the value of its argument suitable for inclusion + into another SQL statement. Strings are surrounded by single-quotes with escapes + on interior quotes as needed. BLOBs are encoded as hexadecimal literals. The current + implementation of + VACUUM uses this function. The function is also useful when writing + triggers to implement undo/redo functionality. + | +
| + random(*) | ++ Return a pseudo-random integer between -9223372036854775808 and +9223372036854775807. | +
|
+ round(X) + round(X,Y) |
+ + Round off the number X to Y digits to the right of the decimal point. + If the Y argument is omitted, 0 is assumed. | +
| + soundex(X) | ++ Compute the soundex encoding of the string X. The string "?000" is returned + if the argument is NULL. This function is omitted from SQLite by default. It is + only available the -DSQLITE_SOUNDEX=1 compiler option is used when SQLite is built. | +
| + sqlite_version(*) | ++ Return the version string for the SQLite library that is running. Example: "2.8.0" | +
| + substr(X,Y,Z) | ++ Return a substring of input string X that begins with the Y-th character + and which is Z characters long. The left-most character of X is number + 1. If Y is negative the the first character of the substring is found by + counting from the right rather than the left. If SQLite is configured to support + UTF-8, then characters indices refer to actual UTF-8 characters, not bytes. | +
| + typeof(X) | ++ Return the type of the expression X. The only return values are "null", "integer", + "real", "text", and "blob". + SQLite's type handling is explained in + Datatypes in SQLite Version 3. | +
| + upper(X) | ++ Return a copy of input string X converted to all upper-case letters. The + implementation of this function uses the C library routine toupper() which + means it may not work correctly on UTF-8 strings. | +
+ Date and time functions are documented in the + SQLite Wiki.
+ ++ The aggregate functions shown below are available by default. Additional aggregate + functions written in C may be added using the + sqlite3_create_function() API.
++ In any aggregate function that takes a single argument, that argument can be preceeded + by the keyword DISTINCT. In such cases, duplicate elements are filtered before being + passed into the aggregate function. For example, the function "count(distinct X)" + will return the number of distinct values of column X instead of the total number + of non-null values in column X. +
++
| + avg(X) | +
+ Return the average value of all non-NULL X within a group. String and BLOB
+ values that do not look like numbers are interpreted as 0. The result of avg() is
+ always a floating point value even if all inputs are integers.
+ + + |
+
|
+ count(X) + count(*) |
+ + The first form return a count of the number of times that X is not NULL in + a group. The second form (with no argument) returns the total number of rows in + the group. | +
| + max(X) | ++ Return the maximum value of all values in the group. The usual sort order is used + to determine the maximum. | +
| + min(X) | ++ Return the minimum non-NULL value of all values in the group. The usual sort order + is used to determine the minimum. NULL is only returned if all values in the group + are NULL. | +
|
+ sum(X) + total(X) |
+
+ Return the numeric sum of all non-NULL values in the group. If there are no non-NULL
+ input rows then sum() returns NULL but total() returns 0.0. NULL is not normally
+ a helpful result for the sum of no rows but the SQL standard requires it and most
+ other SQL database engines implement sum() that way so SQLite does it in the same
+ way in order to be compatible. The non-standard total() function is provided as
+ a convenient way to work around this design problem in the SQL language.
+ + ++ The result of total() is always a floating point value. The result of sum() is an + integer value if all non-NULL inputs are integers. If any input to sum() is neither + an integer or a NULL then sum() returns a floating point value which might be an + approximation to the true sum. ++ Sum() will throw an "integer overflow" exception if all inputs are integers or NULL + and an integer overflow occurs at any point during the computation. Total() never + throws an exception. + |
+
+
| + INSERT | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | +
+ INSERT [OR
+ conflict-algorithm] INTO [database-name .]
+ table-name
+ [(column-list)] VALUES(value-list)
+ | + INSERT [OR + conflict-algorithm] + INTO [database-name .] + table-name + [(column-list)] select-statement |
+
+ The INSERT statement comes in two basic forms. The first form (with the "VALUES" + keyword) creates a single new row in an existing table. If no column-list is specified + then the number of values must be the same as the number of columns in the table. + If a column-list is specified, then the number of values must match the number of + specified columns. Columns of the table that do not appear in the column list are + filled with the default value, or with NULL if not default value is specified. +
++ The second form of the INSERT statement takes it data + from a SELECT statement. The + number of columns in the result of the SELECT must exactly match the number of columns + in the table if no column list is specified, or it must match the number of columns + name in the column list. A new entry is made in the table for every row of the SELECT + result. The SELECT may be simple or compound. If the SELECT statement has an ORDER + BY clause, the ORDER BY is ignored.
++ The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled + ON CONFLICT for additional information. For compatibility + with MySQL, the parser allows the use of the single keyword + REPLACE as an alias for "INSERT OR REPLACE". +
++
| + REINDEX | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ REINDEX collation + name | +
| + sql-statement ::= | ++ REINDEX [database-name .] table/index-name | +
+ The REINDEX command is used to delete and recreate indices from scratch. This is + useful when the definition of a collation sequence has changed. +
++ In the first form, all indices in all attached databases that use the named collation + sequence are recreated. In the second form, if [database-name.]table/index-name + identifies a table, then all indices associated with the table are rebuilt. If an + index is identified, then only this specific index is deleted and recreated. +
++ If no database-name is specified and there exists both a table or index and + a collation sequence of the specified name, then indices associated with the collation + sequence only are reconstructed. This ambiguity may be dispelled by always specifying + a database-name when reindexing a specific table or index. +
++
| + REPLACE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | +
+ REPLACE INTO [database-name .] table-name [(
+ column-list )] VALUES ( value-list ) | + REPLACE INTO [database-name .] + table-name + [( column-list )] + select-statement |
+
+ The REPLACE command is an alias for the "INSERT OR REPLACE" variant of the + INSERT command. This alias is provided for compatibility with MySQL. See the + INSERT command documentation for additional information.
++
| + SELECT | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | +
+ 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]] |
+
| + result ::= | ++ result-column [, + result-column]* | +
| + result-column ::= | ++ * | + table-name + . * | + expr + [ [AS] string + ] | +
| + table-list ::= | ++ table [join-op table + join-args]* | +
| + table ::= | +
+ table-name [AS
+ alias]
+ | + ( select + ) [AS + alias] |
+
| + join-op ::= | ++ , | + [NATURAL] + [LEFT | + RIGHT | FULL] + [OUTER | + INNER | CROSS] JOIN | +
| + join-args ::= | ++ [ON expr] + [USING ( + id-list )] | +
| + sort-expr-list ::= | ++ expr [sort-order] [, + expr + [sort-order]]* | +
| + sort-order ::= | ++ [ COLLATE + collation-name + ] [ ASC + | DESC ] | +
| + compound_op ::= | ++ UNION | UNION + ALL | INTERSECT | EXCEPT | +
+ The SELECT statement is used to query the database. The result of a SELECT is zero + or more rows of data where each row has a fixed number of columns. The number of + columns in the result is specified by the expression list in between the SELECT + and FROM keywords. Any arbitrary expression can be used as a result. If a result + expression is * then all columns of all + tables are substituted for that one expression. If the expression is the name of + a table followed by .* then the result is + all columns in that one table.
++ The DISTINCT keyword causes a subset of result rows to be returned, in which each + result row is different. NULL values are not treated as distinct from each other. + The default behavior is that all result rows be returned, which can be made explicit + with the keyword ALL.
++ The query is executed against one or more tables specified after the FROM keyword. + If multiple tables names are separated by commas, then the query is against the + cross join of the various tables. The full SQL-92 join syntax can also be used to + specify joins. A sub-query in parentheses may be substituted for any table name + in the FROM clause. The entire FROM clause may be omitted, in which case the result + is a single row consisting of the values of the expression list. +
++ The WHERE clause can be used to limit the number of rows over which the query operates.
++ The GROUP BY clauses causes one or more rows of the result to be combined into a + single row of output. This is especially useful when the result contains aggregate + functions. The expressions in the GROUP BY clause do not have to be expressions + that appear in the result. The HAVING clause is similar to WHERE except that HAVING + applies after grouping has occurred. The HAVING expression may refer to values, + even aggregate functions, that are not in the result.
++ The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY + is a list of expressions that are used as the key for the sort. The expressions + do not have to be part of the result for a simple SELECT, but in a compound SELECT + each sort expression must exactly match one of the result columns. Each sort expression + may be optionally followed by a COLLATE keyword and the name of a collating function + used for ordering text and/or keywords ASC or DESC to specify the sort order.
++ The LIMIT clause places an upper bound on the number of rows returned in the result. + A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies + how many rows to skip at the beginning of the result set. In a compound query, the + LIMIT clause may only appear on the final SELECT statement. The limit is applied + to the entire query not to the individual SELECT statement to which it is attached. + Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the + first number and the offset is the second number. If a comma is used instead of + the OFFSET keyword, then the offset is the first number and the limit is the second + number. This seeming contradition is intentional - it maximizes compatibility with + legacy SQL database systems. +
++ A compound SELECT is formed from two or more simple SELECTs connected by one of + the operators UNION, UNION ALL, INTERSECT, or EXCEPT. In a compound SELECT, all + the constituent SELECTs must specify the same number of result columns. There may + be only a single ORDER BY clause at the end of the compound SELECT. The UNION and + UNION ALL operators combine the results of the SELECTs to the right and left into + a single big table. The difference is that in UNION all result rows are distinct + where in UNION ALL there may be duplicates. The INTERSECT operator takes the intersection + of the results of the left and right SELECTs. EXCEPT takes the result of left SELECT + after removing the results of the right SELECT. When three or more SELECTs are connected + into a compound, they group from left to right.
++
| + BEGIN TRANSACTION | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ BEGIN [ DEFERRED + | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [name]] | +
| + sql-statement ::= | ++ END [TRANSACTION + [name]] | +
| + sql-statement ::= | ++ COMMIT [TRANSACTION + [name]] | +
| + sql-statement ::= | ++ ROLLBACK [TRANSACTION + [name]] | +
+ Beginning in version 2.0, SQLite supports transactions with rollback and atomic + commit.
++ The optional transaction name is ignored. SQLite currently does not allow nested + transactions.
++ No changes can be made to the database except within a transaction. Any command + that changes the database (basically, any SQL command + other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions + are committed at the conclusion of the command. +
++ Transactions can be started manually using the BEGIN command. Such transactions + usually persist until the next COMMIT or ROLLBACK command. But a transaction will + also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict + resolution algorithm is specified. See the documentation on the + ON CONFLICT clause for additional information about the ROLLBACK conflict + resolution algorithm. +
++ In SQLite version 3.0.8 and later, transactions can be deferred, immediate, or exclusive. + Deferred means that no locks are acquired on the database until the database is + first accessed. Thus with a deferred transaction, the BEGIN statement itself does + nothing. Locks are not acquired until the first read or write operation. The first + read operation against a database creates a SHARED lock and the first write operation + creates a RESERVED lock. Because the acquisition of locks is deferred until they + are needed, it is possible that another thread or process could create a separate + transaction and write to the database after the BEGIN on the current thread has + executed. If the transaction is immediate, then RESERVED locks are acquired on all + databases as soon as the BEGIN command is executed, without waiting for the database + to be used. After a BEGIN IMMEDIATE, you are guaranteed that no other thread or + process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. + Other processes can continue to read from the database, however. An exclusive transaction + causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, + you are guaranteed that no other thread or process will be able to read or write + the database until the transaction is complete. +
++ A description of the meaning of SHARED, RESERVED, and EXCLUSIVE locks is available + separately. +
++ The default behavior for SQLite version 3.0.8 is a deferred transaction. For SQLite + version 3.0.0 through 3.0.7, deferred is the only kind of transaction available. + For SQLite version 2.8 and earlier, all transactions are exclusive. +
++ The COMMIT command does not actually perform a commit until all pending SQL commands + finish. Thus if two or more SELECT statements are in the middle of processing and + a COMMIT is executed, the commit will not actually occur until all SELECT statements + finish. +
++ An attempt to execute COMMIT might result in an SQLITE_BUSY return code. This indicates + that another thread or process had a read lock on the database that prevented the + database from being updated. When COMMIT fails in this way, the transaction remains + active and the COMMIT can be retried later after the reader has had a chance to + clear. +
++
| + UPDATE | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | +
+ UPDATE [ OR
+ conflict-algorithm
+ ] [database-name .] table-name + SET assignment + [, + assignment]* + [WHERE expr] |
+
| + assignment ::= | ++ column-name = expr | +
+ The UPDATE statement is used to change the value of columns in selected rows of + a table. Each assignment in an UPDATE specifies a column + name to the left of the + equals sign and an arbitrary expression to the right. The expressions may use the + values of + other columns. All expressions are evaluated before any assignments are + made. A WHERE clause can be used to restrict which rows are updated.
++ The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled + ON CONFLICT for additional information.
++
| + VACUUM | +
| + SQLite Language Reference Documentation + | +
+
| + sql-statement ::= | ++ VACUUM [index-or-table-name] | +
+ The VACUUM command is an SQLite extension modeled after a similar command found + in PostgreSQL. If VACUUM is invoked with the name of a table or index then it is + suppose to clean up the named table or index. In version 1.0 of SQLite, the VACUUM + command would invoke gdbm_reorganize() to clean up the backend database file.
++ VACUUM became a no-op when the GDBM backend was removed from SQLITE in version 2.0.0. + VACUUM was reimplemented in version 2.8.1. The index or table name argument is now + ignored. +
++ When an object (table, index, or trigger) is dropped from the database, it leaves + behind empty space. This makes the database file larger than it needs to be, but + can speed up inserts. In time inserts and deletes can leave the database file structure + fragmented, which slows down disk access to the database contents. The VACUUM command + cleans the main database by copying its contents to a temporary database file and + reloading the original database file from the copy. This eliminates free pages, + aligns table data to be contiguous, and otherwise cleans up the database file structure. + It is not possible to perform the same process on an attached database file.
++ This command will fail if there is an active transaction. This command has no effect + on an in-memory database.
++ As of SQLite version 3.1, an alternative to using the VACUUM command is auto-vacuum + mode, enabled using the auto_vacuum pragma.
++
| + PRAGMA | +
| + SQLite Language Reference Documentation + | +
+ The PRAGMA command is a special command used to modify the + operation of the SQLite library or to query the library for internal (non-table) + data. The PRAGMA command is issued using the same interface as other SQLite commands + (e.g. SELECT, INSERT) but is different in the following important respects: +
++ The available pragmas fall into four basic categories:
++
| + sql-statement ::= | +
+ PRAGMA name [=
+ value]
+ | + PRAGMA function(arg) |
+
+ The pragmas that take an integer value also accept symbolic names. + The strings "on", "true", and "yes" are equivalent to 1. + The strings "off", "false", and "no" are equivalent to 0. + These strings are case- insensitive, and do not require quotes. An unrecognized + string will be treated as 1, and will not generate an error. When the value + is returned it is as an integer.
+
+ PRAGMA auto_vacuum;
+
+ PRAGMA auto_vacuum = 0 | 1;
+ Query or set the auto-vacuum flag in the database.
++ Normally, when a transaction that deletes data from a database is committed, the + database file remains the same size. Unused database file pages are marked as such + and reused later on, when data is inserted into the database. In this mode the VACUUM command is used to reclaim unused space.
++ When the auto-vacuum flag is set, the database file shrinks when a transaction that + deletes data is committed (The VACUUM command is not useful in a database with the + auto-vacuum flag set). To support this functionality the database stores extra information + internally, resulting in slightly larger database files than would otherwise be + possible.
++ It is only possible to modify the value of the auto-vacuum flag before any tables + have been created in the database. No error message is returned if an attempt to + modify the auto-vacuum flag is made after one or more tables have been created. +
+
+ PRAGMA cache_size;
+
+ PRAGMA cache_size = Number-of-pages;
+ Query or change the maximum number of database disk pages that SQLite will hold + in memory at once. Each page uses about 1.5K of memory. The default cache size is + 2000. If you are doing UPDATEs or DELETEs that change many rows of a database and + you do not mind if SQLite uses more memory, you can increase the cache size for + a possible speed improvement.
++ When you change the cache size using the cache_size pragma, the change only endures + for the current session. The cache size reverts to the default value when the database + is closed and reopened. Use the default_cache_size + pragma to check the cache size permanently.
+
+ PRAGMA case_sensitive_like;
+
+ PRAGMA case_sensitive_like = 0 | 1;
+ The default behavior of the LIKE operator is to ignore case for latin1 characters. + Hence, by default 'a' LIKE 'A' is true. The case_sensitive_like pragma can + be turned on to change this behavior. When case_sensitive_like is enabled, 'a' + LIKE 'A' is false but 'a' LIKE 'a' is still true.
+
+ PRAGMA count_changes;
+
+ PRAGMA count_changes = 0 | 1;
+ Query or change the count-changes flag. Normally, when the count-changes flag is + not set, INSERT, UPDATE and DELETE statements return no data. When count-changes + is set, each of these commands returns a single row of data consisting of one integer + value - the number of rows inserted, modified or deleted by the command. The returned + change count does not include any insertions, modifications or deletions performed + by triggers.
+
+ PRAGMA default_cache_size;
+
+ PRAGMA default_cache_size = Number-of-pages;
+ Query or change the maximum number of database disk pages that SQLite will hold + in memory at once. Each page uses 1K on disk and about 1.5K in memory. This pragma + works like the cache_size pragma with the + additional feature that it changes the cache size persistently. With this pragma, + you can set the cache size once and that setting is retained and reused every time + you reopen the database.
++ PRAGMA default_synchronous;
++ This pragma was available in version 2.8 but was removed in version 3.0. It is a + dangerous pragma whose use is discouraged. To help dissuide users of version 2.8 + from employing this pragma, the documentation will not tell you what it does.
+
+ PRAGMA empty_result_callbacks;
+
+ PRAGMA empty_result_callbacks = 0 | 1;
+ Query or change the empty-result-callbacks flag.
++ The empty-result-callbacks flag affects the sqlite3_exec API only. Normally, when + the empty-result-callbacks flag is cleared, the callback function supplied to the + sqlite3_exec() call is not invoked for commands that return zero rows of data. When + empty-result-callbacks is set in this situation, the callback function is invoked + exactly once, with the third parameter set to 0 (NULL). This is to enable programs + that use the sqlite3_exec() API to retrieve column-names even when a query returns + no data. +
+
+ PRAGMA encoding;
+
+ PRAGMA encoding = "UTF-8";
+
+ PRAGMA encoding = "UTF-16";
+
+ PRAGMA encoding = "UTF-16le";
+
+ PRAGMA encoding = "UTF-16be";
+ In first form, if the main database has already been created, then this pragma returns + the text encoding used by the main database, one of "UTF-8", "UTF-16le" (little-endian + UTF-16 encoding) or "UTF-16be" (big-endian UTF-16 encoding). If the main database + has not already been created, then the value returned is the text encoding that + will be used to create the main database, if it is created by this session.
++ The second and subsequent forms of this pragma are only useful if the main database + has not already been created. In this case the pragma sets the encoding that the + main database will be created with if it is created by this session. The string + "UTF-16" is interpreted as "UTF-16 encoding using native machine byte-ordering". + If the second and subsequent forms are used after the database file has already + been created, they have no effect and are silently ignored.
++ Once an encoding has been set for a database, it cannot be changed.
++ Databases created by the ATTACH command always use the same encoding as the main + database.
+
+ PRAGMA full_column_names;
+
+ PRAGMA full_column_names = 0 | 1;
+ Query or change the full-column-names flag. This flag affects the way SQLite names + columns of data returned by SELECT statements when the expression for the column + is a table-column name or the wildcard "*". Normally, such result columns are named + <table-name/alias><column-name> if the SELECT statement joins two or + more tables together, or simply <column-name> if the SELECT statement queries + a single table. When the full-column-names flag is set, such columns are always + named <table-name/alias> <column-name> regardless of whether or not + a join is performed. +
++ If both the short-column-names and full-column-names are set, then the behaviour + associated with the full-column-names flag is exhibited. +
+
+ PRAGMA fullfsync
+
+ PRAGMA fullfsync = 0 | 1;
+ Query or change the fullfsync flag. This flag affects determines whether or not + the F_FULLFSYNC syncing method is used on systems that support it. The default value + is off. As of this writing (2006-02-10) only Mac OS X supports F_FULLFSYNC. +
+
+ PRAGMA legacy_file_format;
+
+ PRAGMA legacy_file_format = ON | OFF
+ This pragma sets or queries the value of the legacy_file_format flag. When this + flag is on, new SQLite databases are created in a file format that is readable and + writable by all versions of SQLite going back to 3.0.0. When the flag is off, new + databases are created using the latest file format which might to be readable or + writable by older versions of SQLite.
++ This flag only effects newly created databases. It has no effect on databases that + already exists.
+
+ PRAGMA page_size;
+
+ PRAGMA page_size = bytes;
+ Query or set the page-size of the database. The page-size may only be set if the + database has not yet been created. The page size must be a power of two greater + than or equal to 512 and less than or equal to 8192. The upper limit may be modified + by setting the value of macro SQLITE_MAX_PAGE_SIZE during compilation. The maximum + upper bound is 32768. +
+
+ PRAGMA read_uncommitted;
+
+ PRAGMA read_uncommitted = 0 | 1;
+ Query, set, or clear READ UNCOMMITTED isolation. The default isolation level for + SQLite is SERIALIZABLE. Any process or thread can select READ UNCOMMITTED isolation, + but SERIALIZABLE will still be used except between connections that share a common + page and schema cache. Cache sharing is enabled using the + sqlite3_enable_shared_cache() API and is only available between connections + running the same thread. Cache sharing is off by default. +
+
+ PRAGMA short_column_names;
+
+ PRAGMA short_column_names = 0 | 1;
+ Query or change the short-column-names flag. This flag affects the way SQLite names + columns of data returned by SELECT statements when the expression for the column + is a table-column name or the wildcard "*". Normally, such result columns are named + <table-name/alias>lt;column-name> if the SELECT statement joins two or + more tables together, or simply <column-name> if the SELECT statement queries + a single table. When the short-column-names flag is set, such columns are always + named <column-name> regardless of whether or not a join is performed. +
++ If both the short-column-names and full-column-names are set, then the behaviour + associated with the full-column-names flag is exhibited. +
+
+ PRAGMA synchronous;
+
+ PRAGMA synchronous = FULL; (2)
+
+ PRAGMA synchronous = NORMAL; (1)
+
+ PRAGMA synchronous = OFF; (0)
+ Query or change the setting of the "synchronous" flag. The first (query) form will + return the setting as an integer. When synchronous is FULL (2), the SQLite database + engine will pause at critical moments to make sure that data has actually been written + to the disk surface before continuing. This ensures that if the operating system + crashes or if there is a power failure, the database will be uncorrupted after rebooting. + FULL synchronous is very safe, but it is also slow. When synchronous is NORMAL, + the SQLite database engine will still pause at the most critical moments, but less + often than in FULL mode. There is a very small (though non-zero) chance that a power + failure at just the wrong time could corrupt the database in NORMAL mode. But in + practice, you are more likely to suffer a catastrophic disk failure or some other + unrecoverable hardware fault. With synchronous OFF (0), SQLite continues without + pausing as soon as it has handed data off to the operating system. If the application + running SQLite crashes, the data will be safe, but the database might become corrupted + if the operating system crashes or the computer loses power before that data has + been written to the disk surface. On the other hand, some operations are as much + as 50 or more times faster with synchronous OFF. +
++ In SQLite version 2, the default value is NORMAL. For version 3, the default was + changed to FULL. +
+
+ PRAGMA temp_store;
+
+ PRAGMA temp_store = DEFAULT; (0)
+
+ PRAGMA temp_store = FILE; (1)
+
+ PRAGMA temp_store = MEMORY; (2)
+ Query or change the setting of the "temp_store" parameter. When temp_store + is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine + where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary + tables and indices are kept in memory. When temp_store is FILE (1) temporary tables + and indices are stored in a file. The temp_store_directory + pragma can be used to specify the directory containing this file. FILE is + specified. When the temp_store setting is changed, all existing temporary tables, + indices, triggers, and views are immediately deleted.
++ It is possible for the library compile-time C preprocessor symbol TEMP_STORE to + override this pragma setting. The following table summarizes the interaction of + the TEMP_STORE preprocessor macro and the temp_store pragma:
++++
++ ++ TEMP_STORE ++ PRAGMA +
+ temp_store+ Storage used for +
+ TEMP tables and indices+ ++ 0 ++ any ++ file ++ ++ 1 ++ 0 ++ file ++ ++ 1 ++ 1 ++ file ++ ++ 1 ++ 2 ++ memory ++ ++ 2 ++ 0 ++ memory ++ ++ 2 ++ 1 ++ file ++ ++ 2 ++ 2 ++ memory ++ ++ 3 ++ any ++ memory +
+ PRAGMA temp_store_directory;
+
+ PRAGMA temp_store_directory = 'directory-name';
+ Query or change the setting of the "temp_store_directory" - the directory where + files used for storing temporary tables and indices are kept. This setting lasts + for the duration of the current connection only and resets to its default value + for each new connection opened. +
++ When the temp_store_directory setting is changed, all existing temporary tables, + indices, triggers, and viewers are immediately deleted. In practice, temp_store_directory + should be set immediately after the database is opened. +
++ The value directory-name should be enclosed in single quotes. To revert the + directory to the default, set the directory-name to an empty string, e.g., + PRAGMA temp_store_directory = ''. An error is raised if directory-name + is not found or is not writable. +
++ The default directory for temporary files depends on the OS. For Unix/Linux/OSX, + the default is the is the first writable directory found in the list of: /var/tmp, + /usr/tmp, /tmp, and current-directory. For Windows NT, the default + directory is determined by Windows, generally C:\Documents and Settings\user-name\Local + Settings\Temp\. Temporary files created by SQLite are unlinked immediately + after opening, so that the operating system can automatically delete the files when + the SQLite process exits. Thus, temporary files are not normally visible through + ls or dir commands.
++ PRAGMA database_list;
++ For each open database, invoke the callback function once with information about + that database. Arguments include the index and the name the database was attached + with. The first row will be for the main database. The second row will be for the + database used to store temporary tables.
++ PRAGMA foreign_key_list(table-name);
++ For each foreign key that references a column in the argument table, invoke the + callback function with information about that foreign key. The callback function + will be invoked once for each column in each foreign key.
++ PRAGMA index_info(index-name);
++ For each column that the named index references, invoke the callback function once + with information about that column, including the column name, and the column number.
++ PRAGMA index_list(table-name);
++ For each index on the named table, invoke the callback function once with information + about that index. Arguments include the index name and a flag to indicate whether + or not the index must be unique.
++ PRAGMA table_info(table-name);
++ For each column in the named table, invoke the callback function once with information + about that column, including the column name, data type, whether or not the column + can be NULL, and the default value for the column.
+
+ PRAGMA [database.]schema_version;
+
+ PRAGMA [database.]schema_version = integer ;
+
+ PRAGMA [database.]user_version;
+
+ PRAGMA [database.]user_version = integer ;
+
+ The pragmas schema_version and user_version are used to set or get the value of + the schema-version and user-version, respectively. Both the schema-version and the + user-version are 32-bit signed integers stored in the database header.
++ The schema-version is usually only manipulated internally by SQLite. It is incremented + by SQLite whenever the database schema is modified (by creating or dropping a table + or index). The schema version is used by SQLite each time a query is executed to + ensure that the internal cache of the schema used when compiling the SQL query matches + the schema of the database against which the compiled query is actually executed. + Subverting this mechanism by using "PRAGMA schema_version" to modify the schema-version + is potentially dangerous and may lead to program crashes or database corruption. + Use with caution!
++ The user-version is not used internally by SQLite. It may be used by applications + for any purpose.
++ PRAGMA integrity_check;
++ The command does an integrity check of the entire database. It looks for out-of-order + records, missing pages, malformed records, and corrupt indices. If any problems + are found, then a single string is returned which is a description of all problems. + If everything is in order, "ok" is returned.
+
+ PRAGMA parser_trace = ON; (1)
+
+ PRAGMA parser_trace = OFF; (0)
+ Turn tracing of the SQL parser inside of the SQLite library on and off. This is + used for debugging. This only works if the library is compiled without the NDEBUG + macro. +
+
+ PRAGMA vdbe_trace = ON; (1)
+
+ PRAGMA vdbe_trace = OFF; (0)
+ Turn tracing of the virtual database engine inside of the SQLite library on and + off. This is used for debugging. See the VDBE documentation + for more information.
+
+ PRAGMA vdbe_listing = ON; (1)
+
+ PRAGMA vdbe_listing = OFF; (0)
+ Turn listings of virtual machine programs on and off. With listing is on, the entire + content of a program is printed just prior to beginning execution. This is like + automatically executing an EXPLAIN prior to each statement. The statement executes + normally after the listing is printed. This is used for debugging. See the + VDBE documentation for more information.
++
| + SQLite Query Syntax | +
| + SQLite Language Reference Documentation + | +
+ The SQLite library understands most of the standard SQL language. But it does omit + some features while at the same time adding a few features of its own. This document + attempts to describe precisely what parts of the SQL language SQLite does and does + not support. A list of keywords is also provided. In all of the syntax diagrams + that follow, literal text is shown in bold blue. Non-terminal symbols are shown + in italic red. Operators that are part of the syntactic markup itself are shown + in black roman. This document is just an overview of the SQL syntax implemented + by SQLite. +
++
| + + ALTER TABLE | +
| + + ANALYZE | +
| + + ATTACH DATABASE | +
| + + BEGIN TRANSACTION | +
| + + comment | +
| + + COMMIT TRANSACTION | +
| + CREATE INDEX | +
| + CREATE TABLE | +
| + CREATE TRIGGER | +
| + CREATE VIEW | +
| + + CREATE VIRTUAL TABLE | +
| + + DELETE | +
| + + DETACH DATABASE | +
| + DROP INDEX | +
| + DROP TABLE | +
| + DROP TRIGGER | +
| + DROP VIEW | +
| + + END TRANSACTION | +
| + + EXPLAIN | +
| + + expression | +
| + + INSERT | +
| + + ON CONFLICT clause | +
| + + PRAGMA | +
| + + REINDEX | +
| + + REPLACE | +
| + + ROLLBACK TRANSACTION | +
| + + SELECT | +
| + + UPDATE | +
| + + VACUUM | +