SQL Syntax of SQLite

SQL Syntax of SQLite

The SQLite library can parse most standard SQL languages. However, it also saves some features and adds some new features. This document attempts to describe the SQL syntax that SQLite supports/does not support. View the keyword list.

In the following syntax table, plain text is displayed in blue bold. The non-ultimate symbol is italic red. Operators that are part of the syntax are represented in the black Roman font.

This document is only a summary of the SQL syntax implemented by SQLite, which is ignored. For more details, see the source code and syntax file "parse. y ".

SQLite executes the following syntax:

• ALTER TABLE
• ANALYZE
• ATTACH DATABASE
• BEGIN TRANSACTION
• Note
• COMMIT TRANSACTION
• COPY
• CREATE INDEX
• CREATE TABLE
• CREATE TRIGGER
• CREATE VIEW
• 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

SQLite3 installation and basic operations

Simple Application of SQLite databases in Ubuntu 12.04

How to install SQLite in Ubuntu 12.04

ALTER TABLE

SQL-statement: =	Alter table [Database-name .]Table-nameAlteration
Alteration: =	RENAMENew-table-name
Alteration: =	ADD [COLUMN]Column-def

The alter table command of SQLite allows you to rename or add new fields to an existing TABLE. fields cannot be deleted from the TABLE.

Rename to syntax is used to rename a table name.[Database-name.] table-nameToNew-table-name. This command cannot be used to move tables between attached databases. It can only be renamed in the same database.

If the tables to be renamed have triggers or indexes, they still belong to the table after renaming. However, if the view is defined or the name of the table is mentioned in the statement executed by the trigger, they will not be automatically changed to the new table name. To modify this type, you must manually undo it and use the new table name to recreate the trigger or view.

The ADD [COLUMN] syntax is used to ADD new fields to an existing table. New fields are always added to the end of the existing field list.Column-defIt can be any form allowed in create table and must comply with the following restrictions:

• Fields cannot have primary keys or unique constraints.
• Fields cannot have these default values: CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP
• If the not null constraint is defined, the field must have a non-NULL default value.

The execution time of the alter table statement has nothing to do with the data volume in the TABLE. The running time of an alter table with 10 million rows is the same as that of a TABLE with only one row.

After you run add column on the database, the database cannot be read by SQLite 3.1.3 or earlier versions unless you run the VACUUM command.

 

ANALYZE

SQL-statement: =

ANALYZE

SQL-statement: =

ANALYZEDatabase-name

SQL-statement: =

ANALYZE [Database-name .]Table-name

 

The ANALYZE command allows the collection of index statistics and stores them in a special table of the database. The query optimizer can use this table to create better index selection. If no parameter is provided, all indexes in all attached databases are analyzed. If the parameter specifies the database name, all indexes in the database are analyzed. If the table name is given as a parameter, only the indexes associated with the table are analyzed.

The initial implementation stores all statistics in a table named sqlite_stat1. In future enhanced versions, other tables with similar names may be created, but "1" can be changed to other numbers. The sqlite_stat1 table cannot be undone, but all the content can be deleted, which is equivalent to revoking the table.

 

ATTACH DATABASE

The attach database statement adds an existing DATABASE to the current DATABASE connection. If the file name contains punctuation marks, quotation marks are applied. The database names 'main' and 'temp 'represent the primary database and the database used to store temporary tables. They cannot be split. Use the detach database statement to split the DATABASE.

You can read and write the attached database or change its structure. This is a new feature provided by SQLite 3.0. In SQLite 2.8, changing the structure of the attached database is not allowed.

Adding a table with the same name as an existing table to the attached database is not allowed. However, you can attach a database with the same name as a table in the primary database. You can also attach the same database multiple times.

UseDatabase-name.table-nameTo reference tables in the attached database. If the tables in the attached database and the primary database do not have the same names, you do not need to add the database name as the prefix. When a database is attached, all its tables with no duplicate names become the default tables to which the name points. The prefix is required for any table with the same name as the appended table. If the "default" table is split, the last appended table with the same name is changed to the "default" table.

If the primary database is not ": memory:", the transactions that add more databases are atomic. If the primary database is ": memory:", the transaction remains atomic in each independent file. However, if the host crashes when the COMMIT Statement of two or more databases is changed, some files may be changed while others remain unchanged. The atomic commit of the attached database is a new feature of SQLite 3.0. In SQLite 2.8, the submission of all additional databases is similar to when the primary database is ": memory.

There is a limit on the number of additional databases during compilation. A maximum of 10 additional databases can be created.

 

BEGIN TRANSACTION

SQL-statement: =

BEGIN [DEFERRED|IMMEDIATE|EXCLUSIVE] [TRANSACTION [Name]

SQL-statement: =

END [TRANSACTION [Name]

SQL-statement: =

COMMIT [TRANSACTION [Name]

SQL-statement: =

ROLLBACK [TRANSACTION [Name]

 

Starting from version 2.0, SQLite supports transaction processing with rollback and atomicity.

Optional transaction names are ignored. SQLite currently does not allow nested transactions.

You cannot change the database except transactions. If there is no valid transaction, any command to modify the database (basically all SQL commands except SELECT) will automatically start a transaction. When the command ends, the automatically started transaction is committed.

You can use the BEGIN command to manually start a transaction. In this way, the started transaction will remain valid until the next COMMIT or ROLLBACK command. However, if the database is closed or an error occurs and the ROLLBACK conflict determination algorithm is selected, the database will also roll back. View the on conflict clause