Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Additional fragments in the cli.html page. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
84a18427266f9eb5907bb584e9141b63 |
User & Date: | drh 2016-03-25 13:00:36.754 |
Context
2016-03-26
| ||
22:53 | Update TH3 license information. (check-in: 150f52f8da user: drh tags: trunk) | |
2016-03-25
| ||
13:00 | Additional fragments in the cli.html page. (check-in: 84a1842726 user: drh tags: trunk) | |
2016-03-24
| ||
23:46 | Fix typo. (check-in: b4cae844cd user: mistachkin tags: trunk) | |
Changes
Changes to pages/cli.in.
︙ | ︙ | |||
8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <p>The SQLite project provides a simple command-line utility named <b>sqlite3</b> (or <b>sqlite3.exe</b> on windows) that allows the user to manually enter and execute SQL statements against an SQLite database. This document provides a brief introduction on how to use the <b>sqlite3</b> program. <h3>Getting Started</h3> <p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally followed by the name the file that holds the SQLite database. If the file does not exist, a new database file with the given name will be created automatically. If no database file is specified, a temporary database is created, then deleted when the "sqlite3" program exits. | > | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | <p>The SQLite project provides a simple command-line utility named <b>sqlite3</b> (or <b>sqlite3.exe</b> on windows) that allows the user to manually enter and execute SQL statements against an SQLite database. This document provides a brief introduction on how to use the <b>sqlite3</b> program. <tcl>hd_fragment intro</tcl> <h3>Getting Started</h3> <p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally followed by the name the file that holds the SQLite database. If the file does not exist, a new database file with the given name will be created automatically. If no database file is specified, a temporary database is created, then deleted when the "sqlite3" program exits. |
︙ | ︙ | |||
74 75 76 77 78 79 80 81 82 83 84 85 86 87 | ...> ((( f1 varchar(30) primary key,))) ...> ((( f2 text,))) ...> ((( f3 real))) ...> ((();))) sqlite> }</tcl> <h3>Double-click Startup On Windows</h3> <p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause the command-line shell to pop-up a terminal window running SQLite. Note, however, that by default this SQLite session is using an in-memory database, not a file on disk, and so all changes will be lost when the session exits. To use a persistent disk file as the database, enter the ".open" command | > | 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 | ...> ((( f1 varchar(30) primary key,))) ...> ((( f2 text,))) ...> ((( f3 real))) ...> ((();))) sqlite> }</tcl> <tcl>hd_fragment dblclick</tcl> <h3>Double-click Startup On Windows</h3> <p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause the command-line shell to pop-up a terminal window running SQLite. Note, however, that by default this SQLite session is using an in-memory database, not a file on disk, and so all changes will be lost when the session exits. To use a persistent disk file as the database, enter the ".open" command |
︙ | ︙ | |||
203 204 205 206 207 208 209 210 211 212 213 214 215 216 | .vfslist List all available VFSes .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set column widths for "column" mode Negative values right-justify sqlite> }</tcl> <h3>Rules for "dot-commands"</h3> <p>Ordinary SQL statements are free-form, and can be spread across multiple lines, and can have whitespace and comments anywhere. But dot-commands are more restrictive: | > | 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 | .vfslist List all available VFSes .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set column widths for "column" mode Negative values right-justify sqlite> }</tcl> <tcl>hd_fragment dotrules</tcl> <h3>Rules for "dot-commands"</h3> <p>Ordinary SQL statements are free-form, and can be spread across multiple lines, and can have whitespace and comments anywhere. But dot-commands are more restrictive: |
︙ | ︙ | |||
225 226 227 228 229 230 231 232 233 234 235 236 237 238 | </ul> <p>And, of course, it is important to remember that the dot-commands are interpreted by the sqlite3.exe command-line program, not by SQLite itself. So none of the dot-commands will work as an argument to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()]. <h3>Changing Output Formats</h3> <p>The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.</p> | > | 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 | </ul> <p>And, of course, it is important to remember that the dot-commands are interpreted by the sqlite3.exe command-line program, not by SQLite itself. So none of the dot-commands will work as an argument to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()]. <tcl>hd_fragment dotmode</tcl> <h3>Changing Output Formats</h3> <p>The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.</p> |
︙ | ︙ | |||
368 369 370 371 372 373 374 375 376 377 378 379 380 381 | for looking at the output of an [EXPLAIN] command. <p>Beginning with [Version 3.11.0], the command-line shell defaults to "auto-explain" mode, in which the EXPLAIN commands are automatically detected and the output is automatically formatted. So the ".explain" command has become superfluous. <h3>Writing results to a file</h3> <p>By default, sqlite3 sends query results to standard output. You can change this using the ".output" and ".once" commands. Just put the name of an output file as an argument to .output and all subsequent query results will be written to that file. Or use the .once command instead of .output and output will only be redirected for the single next | > | 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 | for looking at the output of an [EXPLAIN] command. <p>Beginning with [Version 3.11.0], the command-line shell defaults to "auto-explain" mode, in which the EXPLAIN commands are automatically detected and the output is automatically formatted. So the ".explain" command has become superfluous. <tcl>hd_fragment dotoutput</tcl> <h3>Writing results to a file</h3> <p>By default, sqlite3 sends query results to standard output. You can change this using the ".output" and ".once" commands. Just put the name of an output file as an argument to .output and all subsequent query results will be written to that file. Or use the .once command instead of .output and output will only be redirected for the single next |
︙ | ︙ | |||
434 435 436 437 438 439 440 | <p>Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. | | | 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 | <p>Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. <tcl>hd_fragment schema</tcl> <h3>Querying the database schema</h3> <p>The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.</p> |
︙ | ︙ | |||
681 682 683 684 685 686 687 688 689 690 691 692 693 694 | <tcl>DisplayCode { $ (((createdb ex2))) $ (((sqlite3 ex1 .dump | psql ex2))) }</tcl> <h3>Loading Extensions</h3> <p>You can add new custom [application-defined SQL functions], [collating sequences], [virtual tables], and [VFSes] to the command-line shell at run-time using the ".load" command. First, convert the extension in to a DLL or shared library (as described in the [Run-Time Loadable Extensions] document) then type: | > | 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 | <tcl>DisplayCode { $ (((createdb ex2))) $ (((sqlite3 ex1 .dump | psql ex2))) }</tcl> <tcl>hd_fragment dotload</tcl> <h3>Loading Extensions</h3> <p>You can add new custom [application-defined SQL functions], [collating sequences], [virtual tables], and [VFSes] to the command-line shell at run-time using the ".load" command. First, convert the extension in to a DLL or shared library (as described in the [Run-Time Loadable Extensions] document) then type: |
︙ | ︙ | |||
708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 | <p>Source code for several useful extensions can be found in the <a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a> subdirectory of the SQLite source tree. You can use these extensions as-is, or as a basis for creating your own custom extensions to address your own particular needs. <h3>Other Dot Commands</h3> <p>There are many other dot-commands available in the command-line shell. See the ".help" command for a complete list for any particular version and build of SQLite. <h3>Using sqlite3 in a shell script</h3> <p> One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as | > > | 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 | <p>Source code for several useful extensions can be found in the <a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a> subdirectory of the SQLite source tree. You can use these extensions as-is, or as a basis for creating your own custom extensions to address your own particular needs. <tcl>hd_fragment dotother</tcl> <h3>Other Dot Commands</h3> <p>There are many other dot-commands available in the command-line shell. See the ".help" command for a complete list for any particular version and build of SQLite. <tcl>hd_fragment insh</tcl> <h3>Using sqlite3 in a shell script</h3> <p> One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as |
︙ | ︙ | |||
738 739 740 741 742 743 744 | $ (((sqlite3 ex1 'select * from tbl1' |))) > ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'))) <tr><td>hello<td>10 <tr><td>goodbye<td>20 $ }</tcl> | | | | 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 | $ (((sqlite3 ex1 'select * from tbl1' |))) > ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'))) <tr><td>hello<td>10 <tr><td>goodbye<td>20 $ }</tcl> <tcl>hd_fragment endsh</tcl> <h3>Ending shell commands</h3> <p> SQLite commands are normally terminated by a semicolon. In the shell you can also use the word "GO" (case-insensitive) or a slash character "/" on a line by itself to end a command. These are used by SQL Server and Oracle, respectively. These won't work in <b>sqlite3_exec()</b>, because the shell translates these into a semicolon before passing them to that function.</p> <tcl>hd_fragment compiling</tcl> <h3>Compiling the sqlite3 program from sources</h3> <p> The source code to the sqlite3 command line interface is in a single file named "shell.c" which you can <a href="http://www.sqlite.org/src/finfo?name=src/shell.c"> download</a> from the SQLite website. |
︙ | ︙ |