?? sqlite.tcl
字號:
## Run this Tcl script to generate the sqlite.html file.#set rcsid {$Id: sqlite.tcl,v 1.21 2003/06/29 16:11:13 drh Exp $}puts {<html><head> <title>sqlite: A program of interacting with SQLite databases</title></head><body bgcolor=white><h1 align=center>sqlite: A program to administer SQLite databases</h1>}puts "<p align=center>(This page was last modified on [lrange $rcsid 3 4] UTC)</p>"puts {<p>The SQLite library includes a simple command-line utility named<b>sqlite</b> that allows the user to manually enter and execute SQLcommands against an SQLite database. This document provides a briefintroduction on how to use <b>sqlite</b>.<h2>Getting Started</h2><p>To start the <b>sqlite</b> program, just type "sqlite" followed bythe name the file that holds the SQLite database. If the file doesnot exist, a new one is created automatically.The <b>sqlite</b> program willthen prompt you to enter SQL. Type in SQL statements (terminated by asemicolon), press "Enter" and the SQL will be executed.</p><p>For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:</p>}proc Code {body} { puts {<blockquote><tt>} regsub -all {&} [string trim $body] {\&} body regsub -all {>} $body {\>} body regsub -all {<} $body {\<} body regsub -all {\(\(\(} $body {<b>} body regsub -all {\)\)\)} $body {</b>} body regsub -all { } $body {\ } body regsub -all \n $body <br>\n body puts $body puts {</tt></blockquote>}}Code {$ (((sqlite ex1)))SQLite version 2.0.0Enter ".help" for instructionssqlite> (((create table tbl1(one varchar(10), two smallint);)))sqlite> (((insert into tbl1 values('hello!',10);)))sqlite> (((insert into tbl1 values('goodbye', 20);)))sqlite> (((select * from tbl1;)))hello!|10goodbye|20sqlite>}puts {<p>You can terminate the sqlite program by typing your systemsEnd-Of-File character (usually a Control-D) or the interruptcharacter (usually a Control-C).</p><p>Make sure you type a semicolon at the end of each SQL command!The sqlite looks for a semicolon to know when your SQL command iscomplete. If you omit the semicolon, sqlite will give you acontinuation prompt and wait for you to enter more text to beadded to the current SQL command. This feature allows you toenter SQL commands that span multiple lines. For example:</p>}Code {sqlite> (((CREATE TABLE tbl2 ())) ...> ((( f1 varchar(30) primary key,))) ...> ((( f2 text,))) ...> ((( f3 real))) ...> ((();)))sqlite> }puts {<h2>Aside: Querying the SQLITE_MASTER table</h2><p>The database schema in an SQLite database is stored ina special table named "sqlite_master".You can execute "SELECT" statements against thespecial sqlite_master table just like any other tablein an SQLite database. For example:</p>}Code {$ (((sqlite ex1)))SQlite vresion 2.0.0Enter ".help" for instructionssqlite> (((select * from sqlite_master;))) type = table name = tbl1tbl_name = tbl1rootpage = 3 sql = create table tbl1(one varchar(10), two smallint)sqlite>}puts {<p>But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE againstthe sqlite_master table. The sqlite_mastertable is updated automatically as you create or drop tables andindices from the database. You can not make manual changesto the sqlite_master table.</p><p>The schema for TEMPORARY tables is not stored in the "sqlite_master" tablesince TEMPORARY tables are not visible to applications other than theapplication that created the table. The schema for TEMPORARY tablesis stored in another special table named "sqlite_temp_master". The"sqlite_temp_master" table is temporary itself.</p><h2>Special commands to sqlite</h2><p>Most of the time, sqlite just reads lines of input and passes themon to the SQLite library for execution.But if an input line begins with a dot ("."), thenthat line is intercepted and interpreted by the sqlite program itself.These "dot commands" are typically used to change the output formatof queries, or to execute certain prepackaged query statements.</p><p>For a listing of the available dot commands, you can enter ".help"at any time. For example:</p>}Code {sqlite> (((.help))).databases List names and files of attached databases.dump ?TABLE? ... Dump the database in a text format.echo ON|OFF Turn command echo on or off.exit Exit this program.explain ON|OFF Turn output mode suitable for EXPLAIN on or off..header(s) ON|OFF Turn display of headers on or off.help Show this message.indices TABLE Show names of all indices on TABLE.mode MODE Set mode to one of "line(s)", "column(s)", "insert", "list", or "html".mode insert TABLE Generate SQL insert statements for TABLE.nullvalue STRING Print STRING instead of nothing for NULL data.output FILENAME Send output to FILENAME.output stdout Send output to the screen.prompt MAIN CONTINUE Replace the standard prompts.quit Exit this program.read FILENAME Execute SQL in FILENAME.schema ?TABLE? Show the CREATE statements.separator STRING Change separator string for "list" mode.show Show the current values for various settings.tables ?PATTERN? List names of tables matching a pattern.timeout MS Try opening locked tables for MS milliseconds.width NUM NUM ... Set column widths for "column" modesqlite> }puts {<h2>Changing Output Formats</h2><p>The sqlite program is able to show the results of a queryin five different formats: "line", "column", "list", "html", and "insert".You can use the ".mode" dot command to switch between these outputformats.</p><p>The default output mode is "list". Inlist mode, each record of a query result is written on one line ofoutput and each column within that record is separated by a specificseparator string. The default separator is a pipe symbol ("|").List mode is especially useful when you are going to send the outputof a query to another program (such as AWK) for additional processing.</p>}Code {sqlite> (((.mode list)))sqlite> (((select * from tbl1;)))hello|10goodbye|20sqlite>}puts {<p>You can use the ".separator" dot command to change the separatorfor list mode. For example, to change the separator to a comma anda space, you could do this:</p>}Code {sqlite> (((.separator ", ")))sqlite> (((select * from tbl1;)))hello, 10goodbye, 20sqlite>}puts {<p>In "line" mode, each column in a row of the databaseis shown on a line by itself. Each line consists of the columnname, an equal sign and the column data. Successive records areseparated by a blank line. Here is an example of line modeoutput:</p>}Code {sqlite> (((.mode line)))sqlite> (((select * from tbl1;)))one = hellotwo = 10one = goodbyetwo = 20sqlite>}puts {<p>In column mode, each record is shown on a separate line with thedata aligned in columns. For example:</p>}Code {sqlite> (((.mode column)))sqlite> (((select * from tbl1;)))one two ---------- ----------hello 10 goodbye 20 sqlite>}puts {<p>By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You canadjust the column widths using the ".width" command. Like this:</p>}Code {sqlite> (((.width 12 6)))sqlite> (((select * from tbl1;)))one two ------------ ------hello 10 goodbye 20 sqlite>}puts {<p>The ".width" command in the example above sets the width of the firstcolumn to 12 and the width of the second column to 6. All other columnwidths were unaltered. You can gives as many arguments to ".width" asnecessary to specify the widths of as many columns as are in yourquery results.</p><p>If you specify a column a width of 0, then the columnwidth is automatically adjusted to be the maximum of threenumbers: 10, the width of the header, and the width of thefirst row of data. This makes the column width self-adjusting.The default width setting for every column is this auto-adjusting 0 value.</p><p>The column labels that appear on the first two lines of outputcan be turned on and off using the ".header" dot command. In theexamples above, the column labels are on. To turn them off youcould do this:</p>}Code {sqlite> (((.header off)))sqlite> (((select * from tbl1;)))hello 10 goodbye 20 sqlite>}puts {<p>Another useful output mode is "insert". In insert mode, the outputis formatted to look like SQL INSERT statements. You can use insertmode to generate text that can later be used to input data into a different database.</p><p>When specifying insert mode, you have to give an extra argumentwhich is the name of the table to be inserted into. For example:</p>}Code {sqlite> (((.mode insert new_table)))sqlite> (((select * from tbl1;)))INSERT INTO 'new_table' VALUES('hello',10);INSERT INTO 'new_table' VALUES('goodbye',20);sqlite>}?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -