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. Many low-level productions are omitted. For detailed information +on the language that SQLite understands, refer to the source code and +the grammar file "parse.y".
+ +SQLite implements the follow syntax:
+Details on the implementation of each command are provided in +the sequel.
+} + +proc Operator {name} { + return "$name" +} +proc Nonterminal {name} { + return "$name" +} +proc Keyword {name} { + return "$name" +} +proc Example {text} { + puts "" +} + +proc Section {name label} { + global outputdir + + if {[string length $outputdir]!=0} { + if {[llength [info commands puts_standard]]>0} { + footer $::rcsid + } + + if {[string length $label]>0} { + rename puts puts_standard + proc puts {str} { + regsub -all {href="#([a-z]+)"} $str {href="lang_\1.html"} str + puts_standard $::section_file $str + } + rename footer footer_standard + proc footer {id} { + footer_standard $id + rename footer "" + rename puts "" + rename puts_standard puts + rename footer_standard footer + } + set ::section_file [open [file join $outputdir lang_$label.html] w] + header "Query Language Understood by SQLite: $name" + puts "$text
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.
+} + +Section {ANALYZE} analyze + +Syntax {sql-statement} { + ANALYZE +} +Syntax {sql-statement} { + ANALYZEThe 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.
+} + +Section {ATTACH DATABASE} attach + +Syntax {sql-statement} { +ATTACH [DATABASE]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.
+} + + +Section {BEGIN TRANSACTION} transaction + +Syntax {sql-statement} { +BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [+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. +
+ ++END TRANSACTION is an alias for COMMIT. +
+ +The optional transaction name is current ignored. SQLite +does not recognize nested transactions at this time. +However, future versions of SQLite may be enhanced to support nested +transactions and the transaction name would then become significant. +Application are advised not to use the transaction name in order +to avoid future compatibility problems.
+ ++Transactions can be deferred, immediate, or exclusive. +The default transaction behavior is deferred. +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 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. +
+ +If certain kinds of errors occur within a transaction, the +transaction may or may not be rolled back automatically. The +errors that cause the behavior include:
+ ++For all of these errors, SQLite attempts to undo just the one statement +it was working on and leave changes from prior statements within the +same transaction intact and continue with the transaction. However, +depending on the statement being evaluated and the point at which the +error occurs, it might be necessary for SQLite to rollback and +cancel the transaction. An application can tell which +course of action SQLite took by using the +sqlite3_get_autocommit() +C-language interface.
+ +It is recommended that applications respond to the errors +listed above by explicitly issuing a ROLLBACK command. If the +transaction has already been rolled back automatically +by the error response, then the ROLLBACK command will fail with an +error, but no harm is caused by this.
+ +Future versions of SQLite may extend the list of errors which +might cause automatic transaction rollback. Future versions of +SQLite might change the error response. In particular, we may +choose to simplify the interface in future versions of SQLite by +causing the errors above to force an unconditional rollback.
+} + + +Section comment comment + +Syntax {comment} {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. +
+} + + +Section COPY copy + +Syntax {sql-statement} { +COPY [ ORThe COPY command is available in SQLite version 2.8 and earlier. +The COPY command has been removed from SQLite version 3.0 due to +complications in trying to support it in a mixed UTF-8/16 environment. +In version 3.0, the command-line shell +contains a new command .import that can be used as a substitute +for COPY. +
+ +The COPY command is an extension used to load large amounts of +data into a table. It is modeled after a similar command found +in PostgreSQL. In fact, the SQLite COPY command is specifically +designed to be able to read the output of the PostgreSQL dump +utility pg_dump so that data can be easily transferred from +PostgreSQL into SQLite.
+ +The table-name is the name of an existing table which is to +be filled with data. The filename is a string or identifier that +names a file from which data will be read. The filename can be +the STDIN to read data from standard input.
+ +Each line of the input file is converted into a single record +in the table. Columns are separated by tabs. If a tab occurs as +data within a column, then that tab is preceded by a baskslash "\" +character. A baskslash in the data appears as two backslashes in +a row. The optional USING DELIMITERS clause can specify a delimiter +other than tab.
+ +If a column consists of the character "\N", that column is filled +with the value NULL.
+ +The optional conflict-clause allows the specification of an alternative +constraint conflict resolution algorithm to use for this one command. +See the section titled +ON CONFLICT for additional information.
+ +When the input data source is STDIN, the input can be terminated +by a line that contains only a baskslash and a dot:} +puts "\"[Operator \\.]\".
" + + +Section {CREATE INDEX} createindex + +Syntax {sql-statement} { +CREATE [UNIQUE] INDEX [IF NOT EXISTS] [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.
+} + + +Section {CREATE TABLE} {createtable} + +Syntax {sql-command} { +CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [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 can 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.
+ +According to the SQL standard, PRIMARY KEY should imply NOT NULL. +Unfortunately, due to a long-standing coding oversight, this is not +the case in SQLite. SQLite allows NULL values +in a PRIMARY KEY column. We could change SQLite to conform to the +standard (and we might do so in the future), but by the time the +oversight was discovered, SQLite was in such wide use that we feared +breaking legacy code if we fixed the problem. So for now we have +chosen to contain allowing NULLs in PRIMARY KEY columns. +Developers should be aware, however, that we may change SQLite to +conform to the SQL standard in future and should design new programs +accordingly.
+ +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.
+} + + +Section {CREATE TRIGGER} createtrigger + +Syntax {sql-statement} { +CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS]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:
+} +Example { +CREATE TRIGGER update_customer_address UPDATE OF address ON customers + BEGIN + UPDATE orders SET address = new.address WHERE customer_name = old.name; + END; +} +puts { +With this trigger installed, executing the statement:
+} + +Example { +UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones'; +} +puts { +causes the following to be automatically executed:
+} +Example { +UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones'; +} + +puts { +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.
+} + +puts { +A special SQL function RAISE() may be used within a trigger-program, with the following syntax
+} +Syntax {raise-function} { +RAISE ( ABORT,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.
+} + + +Section {CREATE VIEW} {createview} + +Syntax {sql-command} { +CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [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.
+} + +Section {CREATE VIRTUAL TABLE} {createvtab} + +Syntax {sql-command} { +CREATE VIRTUAL TABLE [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.
+} + +Section DELETE delete + +Syntax {sql-statement} { +DELETE FROM [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.
+} + + +Section {DETACH DATABASE} detach + +Syntax {sql-command} { +DETACH [DATABASE]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.
+} + + +Section {DROP INDEX} dropindex + +Syntax {sql-command} { +DROP INDEX [IF EXISTS] [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.
+} + + +Section {DROP TABLE} droptable + +Syntax {sql-command} { +DROP TABLE [IF EXISTS] [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.
+} + + +Section {DROP TRIGGER} droptrigger +Syntax {sql-statement} { +DROP TRIGGER [IF EXISTS] [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.
+} + + +Section {DROP VIEW} dropview + +Syntax {sql-command} { +DROP VIEW [IF EXISTS]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.
+} + + +Section EXPLAIN explain + +Syntax {sql-statement} { +EXPLAINThe 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.
+} + + +Section expression expr + +Syntax {expr} { +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 prefix operators are these:
+ ++ ++- + ! ~ NOT +
The COLLATE operator can be thought of as a unary postfix +operator. The COLLATE operator has the highest precedence. +It always binds more tightly than any prefix unary operator or +any binary operator.
+ +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} +puts "[Operator =] or [Operator ==]. +The non-equals operator can be either +[Operator !=] or [Operator {<>}]. +The [Operator ||] operator is \"concatenate\" - it joins together +the two strings of its operands. +The operator [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 [Operator ||] concatenation operator which gives a string +result.
" + +puts { + + ++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'53514C697465' +
+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. +} +puts "A percent symbol [Operator %] in the pattern matches any +sequence of zero or more characters in the string. An underscore +[Operator _] 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.).
" + +puts { +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).
+} + +puts { +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 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.
+ + +Core Functions + +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. | +
| + +hex(X) | +The argument is interpreted as a BLOB. The result +is a hexadecimal rendering of the content of that blob. | +
| 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) +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.
+
+ This function will fail if the extension attempts to modify +or delete a SQL function or collating sequence. The +extension can add new functions or collating sequences, but cannot +modify or delete existing functions or collating sequences because +those functions and/or collating sequences might be used elsewhere +in the currently running SQL statement. To load an extension that +changes or deletes functions or collating sequences, use the +sqlite3_load_extension() +C-language API. + |
| 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. | +
|
+
+ltrim(X) ltrim(X,Y) |
+Return a string formed by removing any and all +characters that appear in Y from the left side of X. +If the Y argument is omitted, spaces are removed. | +
| 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. | +
| + +replace(X,Y,Z) | +Return a string formed by substituting string Z for +every occurrance of string Y in string X. The BINARY +collating sequence is used for comparisons. | +
| + +randomblob(N) | +Return a N-byte blob containing pseudo-random bytes. +N should be a postive integer. | +
| 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. | +
|
+
+rtrim(X) rtrim(X,Y) |
+Return a string formed by removing any and all +characters that appear in Y from the right side of X. +If the Y argument is omitted, spaces are removed. | +
| 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 X is string +then characters indices refer to actual UTF-8 characters. If +X is a BLOB then the indices refer to bytes. | +
|
+
+trim(X) trim(X,Y) |
+Return a string formed by removing any and all +characters that appear in Y from both ends of X. +If the Y argument is omitted, spaces are removed. | +
| 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. | +
| zeroblob(N) | ++Return a BLOB consisting of N bytes of 0x00. SQLite +manages these zeroblobs very efficiently. Zeroblobs can be used to +reserve space for a BLOB that is later written using +incremental BLOB I/O. | +
Date and time functions are documented in the + +SQLite Wiki.
+ + +Aggregate Functions + ++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. + |
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 no +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.
+ +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". +
+} + + +Section {ON CONFLICT clause} conflict + +Syntax {conflict-clause} { +ON CONFLICTThe 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.
+} + +Section REINDEX reindex + +Syntax {sql-statement} { + REINDEX