SQL Command Syntax

This section details the SQL command syntax understood by SQLite. For clarity SQL keywords are shown in uppercase; however, SQLite is not case sensitive. Keywords and identifiers can be typed in uppercase, lowercase, or mixed case, and different capitalizations of the same string can be used interchangeably.

Creating and Dropping Database Objects

The CREATE object and DROP object statements are used to create and drop database objects.

CREATE TABLE

To create a new database table, use CREATE TABLE.

 CREATE [TEMP | TEMPORARY] TABLE table-name (     column-def [, column-def]*     [, constraint]* ) 

A column in the CREATE TABLE statement is defined as follows:

 name [type] [[CONSTRAINT name] column-constraint]* 

To drop a table, use DROP TABLE.

 DROP TABLE [database-name.] table-name 

Column Constraints

The optional column-constraint is composed of one or more of these keywords: NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY, CHECK, and COLLATE.

 NOT NULL [ conflict-clause ] 

NOT NULL enforces that the column must always contain a value. An error will be raised on any attempt to insert a NULL value into the column.

 DEFAULT value 

DEFAULT defines a value that the column should take if no value is given when a row is inserted.

 UNIQUE [ conflict-clause ] 

UNIQUE creates a UNIQUE INDEX on the column, ensuring that the same value cannot be entered into this column more than once. There can be more than one UNIQUE INDEX on a table if required.

 PRIMARY KEY [sort-order] [ conflict-clause ] 

PRIMARY KEY creates a UNIQUE INDEX on the column designated as primary key for the table. Additionally if the column type is INTEGER, this column is used internally as the actual key of the table and the value is assigned automatically by SQLite if it is not specified when a row is inserted. Only one PRIMARY KEY can be specified on each table.

 CHECK ( expr ) [ conflict-clause ] 

At the present time, the CHECK clause is ignored; however, SQLite allows one to be specified in the syntax for possible future use.

 COLLATE collation-name 

COLLATE specifies the text-collating function to be used when comparing values in this column and can be either TEXT or NUMERIC.

A UNIQUE, PRIMARY KEY, or CHECK constraint clause can also be specified after the column-def section using the following syntax:

 UNIQUE ( column-list ) [ conflict-clause ] PRIMARY KEY ( column-list ) [ conflict-clause ] CHECK ( expr ) [ conflict-clause ] 

CREATE INDEX

To create an indexa sorting key on a database tableuse CREATE INDEX.

 CREATE [UNIQUE] INDEX index-name ON [database-name.] table-name ( column-name [, column-name]* ) [ ON CONFLICT conflict-algorithm ] 

There is no limit to the number of indexes that can be added to a single table, nor on the number of columns in any index.

To drop an index, use DROP INDEX.

 DROP INDEX [database-name.] index-name 

CREATE VIEW

To create a viewa pseudo-table based on a query on one or more tablesuse CREATE VIEW.

 CREATE [TEMP | TEMPORARY] VIEW [database-name.] view-name AS select-statement 

You can SELECT from a view in the same way as a regular table, but you cannot perform a COPY, DELETE, INSERT, or UPDATE operation on a view.

To drop a view, use DROP VIEW.

 DROP VIEW view-name 

CREATE TRIGGER

To create a triggera procedure that executes automatically on a type of database eventon a table use CREATE TRIGGER.

 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name [ BEFORE | AFTER ] database-event ON [database-name.] table-name trigger-action 

To create a trigger on a view, use this syntax instead:

 CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF database-event ON [database-name.] view-name trigger-action 

The database-event can be DELETE, INSERT, UPDATE, or UPDATE OF column-list, any of which can reference the before and after values of a column using OLD.column-name and NEW.column-name respectively.

The syntax of trigger-action is as follows:

 [ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN expression ] BEGIN     trigger-step ; [ trigger-step ; ]* END 

A TRigger-step can be any DELETE, INSERT, SELECT, or UPDATE statement or the special RAISE function to cause an exception to be raised in the SQL statement that caused the trigger to fire. The syntax for RAISE is as follows:

 RAISE ( ABORT, error-message ) | RAISE ( FAIL, error-message ) | RAISE ( ROLLBACK, error-message ) | RAISE ( IGNORE ) 

To drop a trigger use DROP TRIGGER.

 DROP TRIGGER [database-name.] trigger-name 

The SELECT Statement

To query records from a database use the SELECT statement, which has the following syntax:

 SELECT [ALL | DISTINCT] result [FROM table-list] [WHERE expr] [GROUP BY expr-list] [HAVING expr] [compound-op select]* [ORDER BY sort-expr-list] [LIMIT integer [( OFFSET | , ) integer]] 

The result takes the form of a list of one or more comma-separated columns, optionally prefixed with the table name or table alias to avoid ambiguity. A column alias can be specified with the AS keyword following a column identifier. The * character can be used in place of a column list to represent every column from the selected tables.

The table-list is either a comma-separated list of table or view names with optional aliases given following the keyword AS, or another SELECT statement supplied in parentheses. Tables can also be joined to the table-list using the following syntax.

 [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN table-name [ON expr] [USING ( id-list )] 

The compound-op can be used to connect SELECT queries using set operations performed on the queries on either side of the operator. When three or more queries are compounded, they are evaluated in pairs working from left to right.

The operator can be one of the following keywords: UNION, UNION ALL, INTERSECT, or EXCEPT.

 UNION 

UNION takes the union of both queries, causing the result of both queries to be returned in one operation. Rows that appear in the result of both queries are returned only once, so the overall result will contain no duplicate rows.

 UNION ALL 

UNION ALL is the same as UNION except that duplicate rows may appear in the result.

 INTERSECT 

INTERSECT takes the intersection of the two datasets, causing only rows that appear in both querieseither side of the operatorto be returned once.

 EXCEPT 

EXCEPT takes the difference between the datasets returned by the queries. All rows from the result to the left query will be returned except those that also appear in the query to the right of the operator.

The INSERT Statement

To insert records into a table with a list of values given as part of the statement, use the following syntax for the INSERT statement:

 INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES(value-list) 

To insert the dataset returned by a SELECT query into a database table, use this syntax for INSERT.

 INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement 

The REPLACE statement is actually an alias for INSERT OR REPLACE provided for convenience. It can be considered to have its own syntax, as follows:

 REPLACE INTO [database-name.] table-name [( column-list )] VALUES ( value-list ) REPLACE INTO [database-name.] table-name [( column-list )] select-statement 

Refer to the section "Resolving Conflicts" later in this appendix for more information on this behavior.

The UPDATE Statement

To alter records in a database table, use the UPDATE statement with the following syntax:

 UPDATE [ OR conflict-algorithm ] [database-name.] table-name SET assignment [, assignment]* [WHERE expr] 

Each assignment simply takes the form column-name = expr. The expression expr may reference other column values, and all expressions are evaluated before any assignments are made.

When used without a WHERE clause, the update will be performed on every record in the database.

The DELETE Statement

To delete records from a database, use the DELETE statement.

 DELETE FROM [database-name .] table-name [WHERE expr] 

When used without a WHERE clause, every row in the table will be deleted.

The COPY Statement

To load a large amount of data into a table, use the COPY command.

 COPY [ OR conflict-algorithm ] [database-name.] table-name FROM filename [ USING DELIMITERS delim ] 

The filename can be a file in the current directory or can contain a relative or absolute path. Using stdin for the filename will read data from standard input.

Resolving Conflicts

The ON CONFLICT and OR clauses are both used to define the conflict resolution algorithm to be used when a column constraint has been violated.

An ON-CONFLICT clause can be given in the CREATE TABLE and CREATE INDEX statements to specify the default behavior for that table. An OR clause forms part of a COPY, INSERT, or UPDATE statement and overrides any ON-CONFLICT clause on the table.

The ON-CONFLICT or OR keyword is simply followed by one of the following keywords to specify the conflict resolution algorithm to be used: ROLLBACK, ABORT, FAIL, IGNORE, or REPLACE.

 ROLLBACK 

Upon a violation, end the current transaction with a ROLLBACK and abort the current SQL command with a return code of SQLITE_CONSTRAINT. If there is no explicit transaction, the action is the same as ABORT.

 ABORT 

Upon a violation, the current SQL command is aborted with return code SQLITE_CONSTRAINT and any changes already made as part of that command are reversed. However, a ROLLBACK is not issued, so any changes from previous commands within the same transaction are committed. This is the default behavior.

 FAIL 

The SQL command that caused the violation will abort with return code SQLITE_CONSTRAINT; however, any changes made up to that point will be committed. For example, in an UPDATE operation, records that match the WHERE criteria that SQLite encounters before the row that causes the violation will be updated, but subsequent matching rows will not be affected.

 IGNORE 

When a violation occurs, that row is simply ignored and the SQL statement continues executing normally. No error is returned and the operation will be carried out successfully on every row that does not cause a violation.

 REPLACE 

Upon violation of a UNIQUE constraint, the record that already exists that prevents the update or insert operation from taking place is removedwithout any delete triggers being firedand the SQL statement continues as normal. Therefore an INSERT or UPDATE statement always takes place and no error is returned. If a NOT NULL constraint is violated and there is no DEFAULT value on the column, the ABORT algorithm is used instead.

Transactions

To begin a transactionan atomic block of statements that alter the databaseuse BEGIN TRANSACTION.

 BEGIN [TRANSACTION [name]] 

The keyword trANSACTION is optional, and the name argument, if given, is ignored. SQLite does not support nested transactions.

To end the transaction, use one of the following commands:

 COMMIT [TRANSACTION [name]] 

ends the transaction with any changes that have been made saved to the database.

 ROLLBACK [TRANSACTION [name]] 

ends the transaction, discarding any changes made within that transaction.

Attaching to Other Databases

To attach another database file to the current SQLite session, use ATTACH DATABASE.

 ATTACH [DATABASE] database-filename AS database-name 

The DATABASE keyword is optional. SQLite will search for database-filename in the current working directory if no path is given. After a database has been successfully attached, its tables can be referenced in SQL as database-name.table-name.

To detach a database file so that it can no longer be accessed by SQLite, use DETACH DATABASE.

 DETACH [DATABASE] database-name 

Performance Tuning

Use the VACUUM command to clean up your database.

 VACUUM [index-or-table-name] 

VACUUM copies the named index or table or, if no name is given, the entire database to a temporary area of disk and reloads the original database from that copy. By doing so, free pages in the database file are removed and the data in the file is made contiguous.

The EXPLAIN command causes SQLite to report back the virtual machine instructions that would be used to execute the command.

 EXPLAIN sql-statement 

From the sqlite program, the .explain command can be used to quickly set a suitable output format for the EXPLAIN command. Note that the command in sql-statement is not actually executed.

Use the PRAGMA command to modify the operation of the SQLite Library and retrieve information about the connected database.

 PRAGMA name [= value] PRAGMA function(arg) 

These are the currently supported pragmas, which are discussed in depth in Chapter 10, "General Database Administration."

 PRAGMA database_list PRAGMA index_list(table-name) PRAGMA index_info(index-name) PRAGMA table_info(table-name) PRAGMA foreign_key_list(table-name) PRAGMA cache_size = Number-of-pages; PRAGMA default_cache_size = Number-of-pages; PRAGMA synchronous = FULL | NORMAL | OFF PRAGMA default_synchronous = FULL | NORMAL | OFF PRAGMA temp_store = DEFAULT | MEMORY | FILE PRAGMA default_temp_store = DEFAULT | MEMORY | FILE PRAGMA integrity_check PRAGMA parser_trace = ON | OFF PRAGMA vdbe_trace = ON | OFF 

Comments

Commentstext included for reference purposes only, and to be ignored by the parserin SQLite can be written in the single-line SQL style, prefixed by two hyphens, or the multiple-line C style, inside /* ... */ characters.

 -- single line SQL-style comment /* multiple line C-style comment */