📄 lang.tcl
字号:
## Run this Tcl script to generate the sqlite.html file.#set rcsid {$Id: lang.tcl,v 1.67 2004/04/23 17:04:45 drh Exp $}puts {<html><head> <title>Query Language Understood By SQLite</title></head><body bgcolor=white><h1 align=center>SQL As Understood By SQLite</h1>}puts "<p align=center>(This page was last modified on [lrange $rcsid 3 4] UTC)</p>"puts {<p>The SQLite library understands most of the standard SQLlanguage. But it does <a href="omitted.html">omit some features</a>while at the same timeadding a few features of its own. This document attempts todescribe percisely what parts of the SQL language SQLite doesand does not support. A list of <a href="#keywords">keywords</a> is given at the end.</p><p>In all of the syntax diagrams that follow, literal text is shown inbold blue. Non-terminal symbols are shown in italic red. Operatorsthat are part of the syntactic markup itself are shown in black roman.</p><p>This document is just an overview of the SQL syntax implementedby SQLite. Many low-level productions are omitted. For detailed informationon the language that SQLite understands, refer to the source code andthe grammar file "parse.y".</p><p>SQLite implements the follow syntax:</p><p><ul>}foreach {section} [lsort -index 0 -dictionary { {{CREATE TABLE} createtable} {{CREATE INDEX} createindex} {VACUUM vacuum} {{DROP TABLE} droptable} {{DROP INDEX} dropindex} {INSERT insert} {REPLACE replace} {DELETE delete} {UPDATE update} {SELECT select} {comment comment} {COPY copy} {EXPLAIN explain} {expression expr} {{BEGIN TRANSACTION} transaction} {{COMMIT TRANSACTION} transaction} {{END TRANSACTION} transaction} {{ROLLBACK TRANSACTION} transaction} {PRAGMA pragma} {{ON CONFLICT clause} conflict} {{CREATE VIEW} createview} {{DROP VIEW} dropview} {{CREATE TRIGGER} createtrigger} {{DROP TRIGGER} droptrigger} {{ATTACH DATABASE} attach} {{DETACH DATABASE} detach}}] { puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"}puts {</ul></p><p>Details on the implementation of each command are provided inthe sequel.</p>}proc Syntax {args} { puts {<table cellpadding="10">} foreach {rule body} $args { puts "<tr><td align=\"right\" valign=\"top\">" puts "<i><font color=\"#ff3434\">$rule</font></i> ::=</td>" regsub -all < $body {%LT} body regsub -all > $body {%GT} body regsub -all %LT $body {</font></b><i><font color="#ff3434">} body regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body regsub -all "\n" [string trim $body] "<br>\n" body regsub -all "\n *" $body "\n\\ \\ \\ \\ " body regsub -all {[|,.*()]} $body {<big>&</big>} body regsub -all { = } $body { <big>=</big> } body regsub -all {STAR} $body {<big>*</big>} body ## These metacharacters must be handled to undo being ## treated as SQL punctuation characters above. regsub -all {RPPLUS} $body {</font></b>)+<b><font color="#2c2cf0">} body regsub -all {LP} $body {</font></b>(<b><font color="#2c2cf0">} body regsub -all {RP} $body {</font></b>)<b><font color="#2c2cf0">} body ## Place the left-hand side of the rule in the 2nd table column. puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>" } puts {</table>}}proc Operator {name} { return "<font color=\"#2c2cf0\"><big>$name</big></font>"}proc Nonterminal {name} { return "<i><font color=\"#ff3434\">$name</font></i>"}proc Keyword {name} { return "<font color=\"#2c2cf0\">$name</font>"} proc Section {name {label {}}} { puts "\n<hr />" if {$label!=""} { puts "<a name=\"$label\"></a>" } puts "<h1>$name</h1>\n"}proc Example {text} { puts "<blockquote><pre>$text</pre></blockquote>"}Section {ATTACH DATABASE} attachSyntax {sql-statement} {ATTACH [DATABASE] <database-filename> AS <database-name>}puts {<p>The ATTACH DATABASE statement adds a preexisting 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 <a href="#detach">DETACH DATABASE</a> statement.</p><p>You can read from and write to an attached database, but you cannot alter the schema of an attached database. You can only CREATE and DROP in the original database.</p><p>You cannot create a new table with the same name as a table in an attached database, but you can attach a database which containstables whose names are duplicates of tables in the main database. It is also permissible to attach the same database file multiple times.</p><p>Tables in an attached database can be referred to using the syntax <i>database-name.table-name</i>. 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' tableof 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.</p><p>When there are attached databases, transactions are not atomic. Transactions continue to be atomic within each individualdatabase file. But if your machine crashes in the middleof a COMMIT where you have updated two or more databasefiles, some of those files might get the changes where othersmight not.</p><p>There is a compile-time limit of 10 attached database files.</p><p>Executing a BEGIN TRANSACTION statement locks all databasefiles, so this feature cannot (currently) be used to increase concurrancy.</p>}Section {BEGIN TRANSACTION} transactionSyntax {sql-statement} {BEGIN [TRANSACTION [<name>]] [ON CONFLICT <conflict-algorithm>]}Syntax {sql-statement} {END [TRANSACTION [<name>]]}Syntax {sql-statement} {COMMIT [TRANSACTION [<name>]]}Syntax {sql-statement} {ROLLBACK [TRANSACTION [<name>]]}puts {<p>Beginning in version 2.0, SQLite supports transactions withrollback and atomic commit. See <a href="#attach">ATTACH</a> foran exception when there are attached databases.</p><p>The optional transaction name is ignored. SQLite currently doesn't allow nested transactions. Attempting to start a new transaction inside another is an error.</p><p>No changes can be made to the database except within a transaction.Any command that changes the database (basically, any SQL commandother than SELECT) will automatically start a transaction ifone is not already in effect. Automatically started transactionsare committed at the conclusion of the command.</p><p>Transactions can be started manually using the BEGINcommand. Such transactions usually persist until the nextCOMMIT or ROLLBACK command. But a transaction will also ROLLBACK if the database is closed or if an error occursand the ROLLBACK conflict resolution algorithm is specified.See the documention on the <a href="#conflict">ON CONFLICT</a>clause for additional information about the ROLLBACKconflict resolution algorithm.</p><p>The optional ON CONFLICT clause at the end of a BEGIN statementcan be used to changed the default conflict resolution algorithm.The normal default is ABORT. If an alternative is specified bythe ON CONFLICT clause of a BEGIN, then that alternative is usedas the default for all commands within the transaction. The defaultalgorithm is overridden by ON CONFLICT clauses on individualconstraints within the CREATE TABLE or CREATE INDEX statementsand by the OR clauses on COPY, INSERT, and UPDATE commands.</p>}Section comment commentSyntax {comment} {<SQL-comment> | <C-comment>} {SQL-comment} {-- <single-line>} {C-comment} {/STAR <multiple-lines> [STAR/]}puts {<p> 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.</p><p> SQL comments only extend to the end of the current line.</p><p> C comments can span any number of lines. If there is no terminatingdelimiter, they extend to the end of the input. This is not treated asan error. A new SQL statement can begin on a line after a multilinecomment 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.</p>}Section COPY copySyntax {sql-statement} {COPY [ OR <conflict-algorithm> ] [<database-name> .] <table-name> FROM <filename>[ USING DELIMITERS <delim> ]}puts {<p>The COPY command is an extension used to load large amounts ofdata into a table. It is modeled after a similar command foundin PostgreSQL. In fact, the SQLite COPY command is specificallydesigned to be able to read the output of the PostgreSQL dumputility <b>pg_dump</b> so that data can be easily transferred fromPostgreSQL into SQLite.</p><p>The table-name is the name of an existing table which is tobe filled with data. The filename is a string or identifier thatnames a file from which data will be read. The filename can bethe <b>STDIN</b> to read data from standard input.</p><p>Each line of the input file is converted into a single recordin the table. Columns are separated by tabs. If a tab occurs asdata within a column, then that tab is preceded by a baskslash "\"character. A baskslash in the data appears as two backslashes ina row. The optional USING DELIMITERS clause can specify a delimiterother than tab.</p><p>If a column consists of the character "\N", that column is filledwith the value NULL.</p><p>The optional conflict-clause allows the specification of an alternativeconstraint conflict resolution algorithm to use for this one command.See the section titled<a href="#conflict">ON CONFLICT</a> for additional information.</p><p>When the input data source is STDIN, the input can be terminatedby a line that contains only a baskslash and a dot:}puts "\"[Operator \\.]\".</p>"Section {CREATE INDEX} createindexSyntax {sql-statement} {CREATE [UNIQUE] INDEX <index-name> ON [<database-name> .] <table-name> ( <column-name> [, <column-name>]* )[ ON CONFLICT <conflict-algorithm> ]} {column-name} {<name> [ ASC | DESC ]}puts {<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followedby the name of the new index, the keyword "ON", the name of a previouslycreated table that is to be indexed, and a parenthesized list of names ofcolumns in the table that are used for the index key.Each column name can be followed by one of the "ASC" or "DESC" keywordsto indicate sort order, but the sort order is ignored in the currentimplementation. Sorting is always done in ascending order.</p><p>There are no arbitrary limits on the number of indices that can beattached to a single table, nor on the number of columns in an index.</p><p>If the UNIQUE keyword appears between CREATE and INDEX then duplicateindex entries are not allowed. Any attempt to insert a duplicate entrywill result in an error.</p><p>The optional conflict-clause allows the specification of an alternativedefault constraint conflict resolution algorithm for this index.This only makes sense if the UNIQUE keyword is used since otherwisethere are not constraints on the index. The default algorithm isABORT. If a COPY, INSERT, or UPDATE statement specifies a particularconflict resolution algorithm, that algorithm is used in place ofthe default algorithm specified here.See the section titled<a href="#conflict">ON CONFLICT</a> for additional information.</p>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -