Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | - added entry for ATTACH DATABASE. - added entry for DETACH DATABASE. - added soundex() function. - added some new TEMP modifiers. - added new VACUUM behavior. - added the Oracle8 outer join "(+)" syntax. - documented the modulus/remainder operator %. - added ALL as alternative to DISTINCT in SELECT. - fixed assignment list in UPDATE to use * metacharacter. - fixed value in PRAGMA to be optional. - added link from INSERT topic to REPLACE. - added optional CONSTRAINT <name> before column constraints. - skip 2 lines before each Section in the TCL source. (CVS 950) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
fd28c5229ece1d90e24d0ecaa518d8df |
| User & Date: | jplyon 2003-05-03 04:55:19.000 |
Context
|
2003-05-03
| ||
| 19:04 | More documentation updates. (CVS 951) (check-in: 24b9b56924 user: drh tags: trunk) | |
| 04:55 | - added entry for ATTACH DATABASE. - added entry for DETACH DATABASE. - added soundex() function. - added some new TEMP modifiers. - added new VACUUM behavior. - added the Oracle8 outer join "(+)" syntax. - documented the modulus/remainder operator %. - added ALL as alternative to DISTINCT in SELECT. - fixed assignment list in UPDATE to use * metacharacter. - fixed value in PRAGMA to be optional. - added link from INSERT topic to REPLACE. - added optional CONSTRAINT <name> before column constraints. - skip 2 lines before each Section in the TCL source. (CVS 950) (check-in: fd28c5229e user: jplyon tags: trunk) | |
|
2003-05-02
| ||
| 16:44 | Additional tests of the new flattener added. Ticket #272. (CVS 949) (check-in: 8d9ee45ab4 user: drh tags: trunk) | |
Changes
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.51 2003/05/03 04:55:19 jplyon Exp $}
puts {<html>
<head>
<title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
|
| ︙ | ︙ | |||
54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
{{BEGIN TRANSACTION} transaction}
{PRAGMA pragma}
{{ON CONFLICT clause} conflict}
{{CREATE VIEW} createview}
{{DROP VIEW} dropview}
{{CREATE TRIGGER} createtrigger}
{{DROP TRIGGER} droptrigger}
}] {
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 in
the sequel.</p>
| > > | 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
{{BEGIN TRANSACTION} transaction}
{PRAGMA pragma}
{{ON CONFLICT clause} conflict}
{{CREATE VIEW} createview}
{{DROP VIEW} dropview}
{{CREATE TRIGGER} createtrigger}
{{DROP TRIGGER} droptrigger}
{{ATTACH DATABASE} attachdatabase}
{{DETACH DATABASE} detachdatabase}
}] {
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 in
the sequel.</p>
|
| ︙ | ︙ | |||
105 106 107 108 109 110 111 |
puts "<h1>$name</h1>\n"
}
proc Example {text} {
puts "<blockquote><pre>$text</pre></blockquote>"
}
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 |
puts "<h1>$name</h1>\n"
}
proc Example {text} {
puts "<blockquote><pre>$text</pre></blockquote>"
}
Section {ATTACH DATABASE} attachdatabase
Syntax {sql-statement} {
ATTACH [DATABASE] <database-filename> AS <database-name>
}
puts {
<p>The ATTACH DATABASE statement lets you add a preexisting
database file to the current database connection.</p>
<p>You can read and write to the attached database, but you cannot
CREATE TABLE or DROP TABLE in the attached database. You can only
CREATE and DROP in the original database.</p>
<p>With an attached database, transactions are not atomic.
Transactions continue to be atomic within each individual
database file. But if your machine crashes in the middle
of a COMMIT where you have updated two or more database
files, some of those files might get the changes where others
might not.</p>
<p>There is a compile-time limit of 10 attached database files.</p>
<p>Executing a BEGIN TRANSACTION statement locks all database
files, so this feature cannot (currently) be used to increase
concurrancy.</p>
}
Section {BEGIN TRANSACTION} transaction
Syntax {sql-statement} {
BEGIN [TRANSACTION [<name>]] [ON CONFLICT <conflict-algorithm>]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
|
| ︙ | ︙ | |||
221 222 223 224 225 226 227 228 229 230 231 |
constraint 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 terminated
by a line that contains only a baskslash and a dot:}
puts "\"[Operator \\.]\".</p>"
Section {CREATE INDEX} createindex
Syntax {sql-statement} {
| > | | 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 |
constraint 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 terminated
by a line that contains only a baskslash and a dot:}
puts "\"[Operator \\.]\".</p>"
Section {CREATE INDEX} createindex
Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] [UNIQUE] INDEX <index-name>
ON <table-name> ( <column-name> [, <column-name>]* )
[ ON CONFLICT <conflict-algorithm> ]
} {column-name} {
<name> [ ASC | DESC ]
}
|
| ︙ | ︙ | |||
279 280 281 282 283 284 285 |
CREATE [TEMP | TEMPORARY] TABLE <table-name> (
<column-def> [, <column-def>]*
[, <constraint>]*
)
} {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE <table-name> AS <select-statement>
} {column-def} {
| | | 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 |
CREATE [TEMP | TEMPORARY] TABLE <table-name> (
<column-def> [, <column-def>]*
[, <constraint>]*
)
} {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE <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> ] |
|
| ︙ | ︙ | |||
376 377 378 379 380 381 382 383 384 385 |
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
The text of CREATE TEMPORARY TABLE statements are stored in the
<b>sqlite_temp_master</b> table.
</p>
}
Section {CREATE TRIGGER} createtrigger
Syntax {sql-statement} {
| > > | | | 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 |
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
The text of CREATE TEMPORARY TABLE statements are stored in the
<b>sqlite_temp_master</b> table.
</p>
}
Section {CREATE TRIGGER} createtrigger
Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] TRIGGER <trigger-name> [ BEFORE | AFTER ]
<database-event> ON <table-name>
<trigger-action>
}
Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] TRIGGER <trigger-name> INSTEAD OF
<database-event> ON <view-name>
<trigger-action>
}
Syntax {database-event} {
DELETE |
INSERT |
|
| ︙ | ︙ | |||
527 528 529 530 531 532 533 534 535 536 537 |
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.
</p>
}
Section {CREATE VIEW} {createview}
Syntax {sql-command} {
| > | > > > > > > > > > > > > > > > > > > > > > | 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 |
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.
</p>
}
Section {CREATE VIEW} {createview}
Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] VIEW <view-name> AS <select-statement>
}
puts {
<p>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.
</p>
<p>You cannot COPY, INSERT or UPDATE a view. Views are read-only.</p>
}
Section DELETE delete
Syntax {sql-statement} {
DELETE FROM <table-name> [WHERE <expr>]
}
puts {
<p>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.
</p>
<p>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.</p>
}
Section {DETACH DATABASE} detachdatabase
Syntax {sql-command} {
DETACH [DATABASE] <database-name>
}
puts {
<p>This statement detaches an additional database file previoiusly attached
using the ATTACH DATABASE statement.</p>
<p>This statement will fail if SQLite is in the middle of a transaction.</p>
}
Section {DROP INDEX} dropindex
Syntax {sql-command} {
DROP INDEX <index-name>
}
puts {
<p>The DROP INDEX statement consists of the keywords "DROP INDEX" followed
by the name of the index. The index named is completely removed from
the disk. The only way to recover the index is to reenter the
appropriate CREATE INDEX command.</p>
}
Section {DROP TABLE} droptable
Syntax {sql-command} {
DROP TABLE <table-name>
}
puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table. The table named is completely removed from
the disk. The table can not be recovered. All indices associated with
the table are also deleted.</p>}
Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER <trigger-name>
}
puts {
<p>Used to drop a trigger from the database schema. Note that triggers
are automatically dropped when the associated table is dropped.</p>
}
Section {DROP VIEW} dropview
Syntax {sql-command} {
DROP VIEW <view-name>
}
puts {
<p>The DROP VIEW statement consists of the keywords "DROP VIEW" followed
by the name of the view. The view named is removed from the database.
But no actual data is modified.</p>}
Section EXPLAIN explain
Syntax {sql-statement} {
EXPLAIN <sql-statement>
}
puts {
<p>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.</p>
<p>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 <a href="arch.html">architecture description</a> or the documentation
on <a href="opcode.html">available opcodes</a> for the virtual machine.</p>
}
Section expression expr
Syntax {expr} {
<expr> <binary-op> <expr> |
<expr> <like-op> <expr> |
<unary-op> <expr> |
( <expr> ) |
<column-name> |
<table-name> . <column-name> |
<literal-value> |
<function-name> ( <expr-list> | STAR ) |
<expr> (+) |
<expr> ISNULL |
<expr> NOTNULL |
<expr> [NOT] BETWEEN <expr> AND <expr> |
<expr> [NOT] IN ( <value-list> ) |
<expr> [NOT] IN ( <select-statement> ) |
( <select-statement> ) |
CASE [<expr>] ( WHEN <expr> THEN <expr> )+ [ELSE <expr>] END
|
| ︙ | ︙ | |||
685 686 687 688 689 690 691 |
compare using the <b>strcmp()</b> function.
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
| | > > | 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 |
compare using the <b>strcmp()</b> function.
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.</p>"
puts {
<p>The LIKE operator does a wildcard comparision. The operand
to the right contains the wildcards.}
puts "A percent symbol [Operator %] in the right operand
matches any sequence of zero or more characters on the left.
An underscore [Operator _] on the right
|
| ︙ | ︙ | |||
722 723 724 725 726 727 728 729 730 731 732 733 734 735 | 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.</p> <p>SELECT statements can appear in expressions as either the right-hand operand of the IN operator or as a scalar quantity. In both cases, the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. A SELECT in an expression is evaluated once before any other processing is performed, so none of the expressions within the select itself can | > > > > > > > > > | 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 | 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.</p> <p>SQLite supports a minimal Oracle8 outer join behavior. A column expression of the form "column" or "table.column" can be followed by the special "<b>(+)</b>" operator. If the table of the column expression is the second or subsequent table in a join, then that table becomes the left table in a LEFT OUTER JOIN. The expression that uses that table becomes part of the ON clause for the join. The exact Oracle8 behavior is not implemented, but it is possible to construct queries that will work correctly for both SQLite and Oracle8.</p> <p>SELECT statements can appear in expressions as either the right-hand operand of the IN operator or as a scalar quantity. In both cases, the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. A SELECT in an expression is evaluated once before any other processing is performed, so none of the expressions within the select itself can |
| ︙ | ︙ | |||
838 839 840 841 842 843 844 845 846 847 848 849 850 851 | <tr> <td valign="top" align="right">round(<i>X</i>)<br>round(<i>X</i>,<i>Y</i>)</td> <td valign="top">Round off the number <i>X</i> to <i>Y</i> digits to the right of the decimal point. If the <i>Y</i> argument is omitted, 0 is assumed.</td> </tr> <tr> <td valign="top" align="right">substr(<i>X</i>,<i>Y</i>,<i>Z</i>)</td> <td valign="top">Return a substring of input string <i>X</i> that begins with the <i>Y</i>-th character and which is <i>Z</i> characters long. The left-most character of <i>X</i> is number 1. If <i>Y</i> 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, | > > > > > > | 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 | <tr> <td valign="top" align="right">round(<i>X</i>)<br>round(<i>X</i>,<i>Y</i>)</td> <td valign="top">Round off the number <i>X</i> to <i>Y</i> digits to the right of the decimal point. If the <i>Y</i> argument is omitted, 0 is assumed.</td> </tr> <tr> <td valign="top" align="right">soundex(<i>X</i>)</td> <td valign="top">Compute the soundex encoding of the string <i>X</i>. This returns "?000" for a NULL argument. </tr> <tr> <td valign="top" align="right">substr(<i>X</i>,<i>Y</i>,<i>Z</i>)</td> <td valign="top">Return a substring of input string <i>X</i> that begins with the <i>Y</i>-th character and which is <i>Z</i> characters long. The left-most character of <i>X</i> is number 1. If <i>Y</i> 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, |
| ︙ | ︙ | |||
893 894 895 896 897 898 899 900 901 902 903 904 905 906 |
<tr>
<td valign="top" align="right">sum(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all values in the group.</td>
</tr>
</table>
}
Section INSERT insert
Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] <select-statement>
}
| > | 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 |
<tr>
<td valign="top" align="right">sum(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all values in the group.</td>
</tr>
</table>
}
Section INSERT insert
Syntax {sql-statement} {
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] VALUES(<value-list>) |
INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] <select-statement>
}
|
| ︙ | ︙ | |||
926 927 928 929 930 931 932 | the ORDER BY is ignored.</p> <p>The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled <a href="#conflict">ON CONFLICT</a> for additional information. For compatibility with MySQL, the parser allows the use of the | | > | 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 |
the ORDER BY is ignored.</p>
<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="#replace">REPLACE</a> as an alias for "INSERT OR REPLACE".
</p>
}
Section {ON CONFLICT clause} conflict
Syntax {conflict-clause} {
ON CONFLICT <conflict-algorithm>
} {conflict-algorithm} {
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
|
| ︙ | ︙ | |||
1031 1032 1033 1034 1035 1036 1037 |
# <p>For additional information, see
# <a href="conflict.html">conflict.html</a>.</p>
Section PRAGMA pragma
Syntax {sql-statement} {
| | | 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 |
# <p>For additional information, see
# <a href="conflict.html">conflict.html</a>.</p>
Section PRAGMA pragma
Syntax {sql-statement} {
PRAGMA <name> [= <value>] |
PRAGMA <function>(<arg>)
}
puts {
<p>The PRAGMA command is used to modify the operation of the SQLite library.
The pragma command is experimental and specific pragma statements may be
removed or added in future releases of SQLite. Use this command
|
| ︙ | ︙ | |||
1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 |
<p>Turn tracing of the virtual database engine inside of the
SQLite library on and off. This is used for debugging.</p></li>
</ul>
<p>No error message is generated if an unknown pragma is issued.
Unknown pragmas are ignored.</p>
}
Section REPLACE replace
Syntax {sql-statement} {
REPLACE INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
REPLACE INTO <table-name> [( <column-list> )] <select-statement>
}
puts {
<p>The REPLACE command is an alias for the "INSERT OR REPLACE" variant
of the <a href="#insert">INSERT command</a>. This alias is provided for
compatibility with MySQL. See the
<a href="#insert">INSERT command</a> documentation for additional
information.</p>
}
Section SELECT select
Syntax {sql-statement} {
| > > | | 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 |
<p>Turn tracing of the virtual database engine inside of the
SQLite library on and off. This is used for debugging.</p></li>
</ul>
<p>No error message is generated if an unknown pragma is issued.
Unknown pragmas are ignored.</p>
}
Section REPLACE replace
Syntax {sql-statement} {
REPLACE INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
REPLACE INTO <table-name> [( <column-list> )] <select-statement>
}
puts {
<p>The REPLACE command is an alias for the "INSERT OR REPLACE" variant
of the <a href="#insert">INSERT command</a>. This alias is provided for
compatibility with MySQL. See the
<a href="#insert">INSERT command</a> documentation for additional
information.</p>
}
Section SELECT select
Syntax {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} {
|
| ︙ | ︙ | |||
1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 |
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 are more SELECTs
are connected into a compound, they group from left to right.</p>
}
Section UPDATE update
Syntax {sql-statement} {
UPDATE [ OR <conflict-algorithm> ] <table-name>
| > | > | | > > | > > | > > > > > > > > > | 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 |
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 are more SELECTs
are connected into a compound, they group from left to right.</p>
}
Section UPDATE update
Syntax {sql-statement} {
UPDATE [ OR <conflict-algorithm> ] <table-name>
SET <assignment> [, <assignment>]*
[WHERE <expr>]
} {assignment} {
<column-name> = <expr>
}
puts {
<p>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.</p>
<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>
}
Section VACUUM vacuum
Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}
puts {
<p>The VACUUM command is an SQLite extension modelled 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
<b>gdbm_reorganize()</b> to clean up the backend database file.</p>
<p>
This command was readded after version 2.8.0. of SQLite. It now cleans
the database by copying its contents to a temporary database file, and
reloading the database file from it. This will eliminate free pages,
align table data to be contiguous, and otherwise clean up the database
file structure.</p>
<p>This command will fail if there is an active transaction. This
command has no effect on an in-memory database.</p>
}
puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>
</body></html>}
|