/ Check-in [fd28c522]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fd28c5229ece1d90e24d0ecaa518d8df5a4f55f0
User & Date: jplyon 2003-05-03 04:55:19
Context
2003-05-03
19:04
More documentation updates. (CVS 951) check-in: 24b9b569 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: fd28c522 user: jplyon tags: trunk
2003-05-02
16:44
Additional tests of the new flattener added. Ticket #272. (CVS 949) check-in: 8d9ee45a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
54
55
56
57
58
59
60


61
62
63
64
65
66
67
...
105
106
107
108
109
110
111






























112
113
114
115
116
117
118
119
...
221
222
223
224
225
226
227

228
229
230
231
232
233
234
235
236
237
238
239
...
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
...
376
377
378
379
380
381
382


383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
...
527
528
529
530
531
532
533

534
535
536
537
538
539
540
541
542
543
544
545
546
547
548

549
550
551
552
553
554
555
...
560
561
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
...
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
...
685
686
687
688
689
690
691
692


693
694
695
696
697
698
699
...
722
723
724
725
726
727
728









729
730
731
732
733
734
735
...
838
839
840
841
842
843
844






845
846
847
848
849
850
851
...
893
894
895
896
897
898
899

900
901
902
903
904
905
906
...
926
927
928
929
930
931
932
933
934
935

936
937
938
939
940
941
942
....
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
....
1206
1207
1208
1209
1210
1211
1212

1213
1214
1215
1216
1217
1218
1219
....
1221
1222
1223
1224
1225
1226
1227

1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
....
1312
1313
1314
1315
1316
1317
1318

1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
....
1336
1337
1338
1339
1340
1341
1342

1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356

1357




1358



1359
1360
1361
1362
1363
1364
1365
1366
1367
1368






#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.50 2003/02/13 02:54:04 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  {{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>
................................................................................
  puts "<h1>$name</h1>\n"
}

proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}































Section {BEGIN TRANSACTION} createindex

Syntax {sql-statement} {
BEGIN [TRANSACTION [<name>]] [ON CONFLICT <conflict-algorithm>]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
................................................................................
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 [UNIQUE] INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
[ ON CONFLICT <conflict-algorithm> ]
} {column-name} {
<name> [ ASC | DESC ]
}


................................................................................
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>] [<column-constraint>]*
} {type} {
<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] |
................................................................................
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 TRIGGER <trigger-name> [ BEFORE | AFTER ]
<database-event> ON <table-name>
<trigger-action>
}

Syntax {sql-statement} {
CREATE TRIGGER <trigger-name> INSTEAD OF
<database-event> ON <view-name>
<trigger-action>
}

Syntax {database-event} {
DELETE | 
INSERT | 
................................................................................
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 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>]
}

................................................................................
</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 {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>
}

................................................................................
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> 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
................................................................................
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 {&lt;&gt;}].
The [Operator ||] operator is \"concatenate\" - it joins together
the two strings of its operands.</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
................................................................................
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
................................................................................
<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,
................................................................................

<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>
}
................................................................................
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 "REPLACE" 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
................................................................................
# <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
................................................................................
    <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 [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} {
................................................................................
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 
................................................................................
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.
Beginning with version 2.0 of SQLite, GDBM is no longer used for

the database backend and VACUUM has become a no-op.




</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>}









|







 







>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|







 







>




|







 







|







 







>
>



|





|







 







>




|










>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>













>












>









>











>







 







>












>







 







|
>
>







 







>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>







 







>







 







|


>







 







|







 







>







 







>




|







 







>





|







 







>












|
<
>
|
>
>
>
>
|
>
>
>










>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
..
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
...
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
...
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
...
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
...
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
...
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
...
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
...
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
...
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
...
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
...
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
...
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
....
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
....
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
....
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
....
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
....
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
....
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
#
# 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>
................................................................................
  {{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>
................................................................................
  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>]]
}
................................................................................
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 ]
}


................................................................................
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> ] |
................................................................................
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 | 
................................................................................
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>]
}

................................................................................
</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>
}

................................................................................
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
................................................................................
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 {&lt;&gt;}].
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
................................................................................
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
................................................................................
<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,
................................................................................

<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>
}
................................................................................
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
................................................................................
# <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
................................................................................
    <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} {
................................................................................
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 
................................................................................
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>}