Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates, clarifications, and typo fixes in the SQL language documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
7b2b285803fb5c88917254a42dd818d9 |
User & Date: | drh 2009-08-26 02:00:30.000 |
Context
2009-08-26
| ||
13:13 | Tweaks to the testing documentation. (check-in: 3b5097e255 user: drh tags: trunk) | |
02:00 | Updates, clarifications, and typo fixes in the SQL language documentation. (check-in: 7b2b285803 user: drh tags: trunk) | |
00:29 | Updates to the testing.html and different.html documents. (check-in: 197d293511 user: drh tags: trunk) | |
Changes
Changes to pages/lang.in.
︙ | ︙ | |||
130 131 132 133 134 135 136 | BubbleDiagram alter-table-stmt 1 </tcl> <p>SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible | | | 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | BubbleDiagram alter-table-stmt 1 </tcl> <p>SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table. </p> <p>The RENAME TO syntax is used to rename the table identified by <i>[database-name.]table-name</i> to <i>new-table-name</i>. This command cannot be used to move a table between attached databases, only to rename a table within the same database.</p> |
︙ | ︙ | |||
354 355 356 357 358 359 360 | </p> <p> The ROLLBACK will fail with an error code [SQLITE_BUSY] if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things | | | 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 | </p> <p> The ROLLBACK will fail with an error code [SQLITE_BUSY] if there are any pending queries. Both read-only and read/write queries will cause a ROLLBACK to fail. A ROLLBACK must fail if there are pending read operations (unlike COMMIT which can succeed) because bad things will happen if the in-memory image of the database is changed out from under an active query. </p> <p> If [PRAGMA journal_mode] is set to OFF (thus disabling the rollback journal file) then the behavior of the ROLLBACK command is undefined. </p> |
︙ | ︙ | |||
588 589 590 591 592 593 594 | will result in an error. For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguious) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and Microsoft SQL Server follow the other interpretation of the standard.</p> | < < < < < < < < | 588 589 590 591 592 593 594 595 596 597 598 599 600 601 | will result in an error. For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguious) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and Microsoft SQL Server follow the other interpretation of the standard.</p> <p>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.</p> <p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> command.</p> |
︙ | ︙ | |||
619 620 621 622 623 624 625 | BubbleDiagram foreign-key-clause </tcl> <p>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. Tables names that begin with "<b>sqlite_</b>" are reserved | | | > | 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 | BubbleDiagram foreign-key-clause </tcl> <p>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. Tables names that begin with "<b>sqlite_</b>" are reserved for use by SQLite itself and cannot appears in a CREATE TABLE statement.</p> <p>Each column definition is the name of the column optionally followed by the [datatype] for that column, then one or more optional column constraints. SQLite uses [dynamic typing]; the datatype for the column does not restrict what data may be put in that column. The UNIQUE constraint causes an unique index to be created on the specified columns. All NULL values are considered different from each other and from all other values for the purpose of determining uniqueness, hence a UNIQUE column may contain multiple entries with the value of NULL. 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. <p> The DEFAULT constraint specifies a default value to use when doing an [INSERT]. The value may be NULL, a string constant, a number, or a constant expression enclosed in parentheses. 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 |
︙ | ︙ | |||
686 687 688 689 690 691 692 | 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.</p> <p>The optional [conflict clause] following each constraint allows the specification of an alternative default constraint conflict resolution algorithm for that constraint. | > | | | | > | < < < < < < < < < < < < | 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 708 709 710 711 712 713 714 715 716 717 | 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.</p> <p>The optional [conflict clause] following each constraint allows the specification of an alternative default constraint conflict resolution algorithm for that constraint. If no conflict clause is specified, the ABORT algorithm is used. Different constraints within the same table may have different conflict resolution algorithms. If an [INSERT] or [UPDATE] statement specifies a conflict resolution algorithm, then the algorithm specified on the INSERT or UPDATE statement overrides the algorithm specified in the CREATE TABLE statement. See the section titled [ON CONFLICT] for additional information.</p> <p>CHECK constraints are supported as of [version 3.3.0]. Prior to version 3.3.0, CHECK constraints were parsed but not enforced.</p> <p>The number of columns in a table is limited by the [SQLITE_MAX_COLUMN] compile-time parameter. A single row of a table cannot store more than [SQLITE_MAX_LENGTH] bytes of data. Both of these limits can be lowered at runtime using the [sqlite3_limit()] C/C++ interface.</p> <p>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.</p> <p>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.</p> <p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> statement. </p> <tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl> |
︙ | ︙ | |||
807 808 809 810 811 812 813 | <p>The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations that are automatically performed when a specified database event occurs. </p> <p>A trigger may be specified to fire whenever a [DELETE], [INSERT], or [UPDATE] of a | | | | 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 | <p>The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers are database operations that are automatically performed when a specified database event occurs. </p> <p>A trigger may be specified to fire whenever a [DELETE], [INSERT], or [UPDATE] of a particular database table occurs, or whenever an [UPDATE] occurs on on one or more specified columns of a table.</p> <p>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 in the trigger 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.</p> |
︙ | ︙ | |||
1018 1019 1020 1021 1022 1023 1024 | <p>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.</p> <p>In general, you can do anything with a [virtual table] that can be done with an ordinary table, except that you cannot create indices or triggers on a virtual table. Some virtual table implementations might impose additional | | < < | 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 | <p>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.</p> <p>In general, you can do anything with a [virtual table] that can be done with an ordinary table, except that you cannot create indices or triggers on a virtual table. Some virtual table implementations might impose additional restrictions. For example, many virtual tables are read-only.</p> <p>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()] or [sqlite3_create_module_v2()] prior to issuing the CREATE VIRTUAL TABLE statement. The module takes zero or more comma-separated arguments. |
︙ | ︙ | |||
1415 1416 1417 1418 1419 1420 1421 | 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.</p> <tcl>hd_fragment match MATCH</tcl> <p>The MATCH operator is a special syntax for the match() user function. The default match() function implementation | | | | | < < | | 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 | 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.</p> <tcl>hd_fragment match MATCH</tcl> <p>The MATCH operator is a special syntax for the match() user function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.</p> <tcl>hd_fragment between BETWEEN</tcl> <h3>The BETWEEN operator</h3> <p>The BETWEEN operator is equivalent to a pair of comparisons. "<i>a</i> <b>BETWEEN</b> <i>b</i> <b>AND</b> <i>c</i>" is equivalent to "<i>a</i><b>>=</b><i>b</i> <b>AND</b> <i>a</i><b><=</b><i>c</i>". The precedence of the BETWEEN operator is the same as the precedence as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right. <h3>Table Column Names</h3> <p>A column name can be any of the names defined in the [CREATE TABLE] statement or one of the following special identifiers: "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>". These special identifiers all describe the unique integer key (the [rowid]) 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. The rowid can be used anywhere a regular column can be used.</p> <h3>Subqueries</h3> <p>[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 values 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.</p> <p>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 |
︙ | ︙ | |||
1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 | <p> All five functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. </p> <p> The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS". The julianday() function returns the | > > > | | 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 | <p> All five functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. </p> <p> The date and time functions use a subset of <a href="http://en.wikipedia.org/wiki/IOS_8601">IS0-8601</a> date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS". The julianday() function returns the <a href="http://en.wikipedia.org/wiki/Julian_day">Julian day</a> - the number of days since noon in Greenwich on November 24, 4714 B.C. (<a href="http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar">Proleptic Gregorian calendar</a>). The strftime() routine returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in the <a href="http://opengroup.org/onlinepubs/007908799/xsh/strftime.html"> |
︙ | ︙ | |||
2046 2047 2048 2049 2050 2051 2052 | <tcl> funcdef {avg(X)} {} { Return the average value of all non-NULL <i>X</i> 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 as long as at there is at least one non-NULL input even if all | | | > | 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 | <tcl> funcdef {avg(X)} {} { Return the average value of all non-NULL <i>X</i> 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 as long as at there is at least one non-NULL input even if all inputs are integers. The result of avg() is NULL if and only if there are no non-NULL inputs. } funcdef {count(X) count(*)} {} { The first form return a count of the number of times that <i>X</i> is not NULL in a group. The second form (with no argument) returns the total number of rows in the group. } funcdef {group_concat(X) group_concat(X,Y)} {} { The result is a string which is the concatenation of all non-NULL values of <i>X</i>. If parameter <i>Y</i> is present then is is used as the separator between instances of <i>X</i>. A comma (",") is used as the separator if <i>Y</i> is omitted. The order of the concatenated elements is arbitrary. } funcdef {max(X)} {maxAggFunc agg_max} { Return the maximum value of all values in the group. |
︙ | ︙ | |||
2099 2100 2101 2102 2103 2104 2105 | 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.</p> <p>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. | | | 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 | 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.</p> <p>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 integer overflow. } </tcl> </table> <tcl> ############################################################################## Section INSERT insert {INSERT INSERTs} |
︙ | ︙ |