📄 sqlite.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>sqlite3: A command-line access program for SQLite databases</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <h2>sqlite3: A command-line access program for SQLite databases</h2><p>The SQLite library includes a simple command-line utility named<b>sqlite3</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>sqlite3</b>.<h3>Getting Started</h3><p>To start the <b>sqlite3</b> program, just type "sqlite3" followed bythe name the file that holds the SQLite database. If the file doesnot exist, a new one is created automatically.The <b>sqlite3</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><blockquote><tt>$ <b>sqlite3 ex1</b><br>SQLite version 3.3.10<br>Enter ".help" for instructions<br>sqlite> <b>create table tbl1(one varchar(10), two smallint);</b><br>sqlite> <b>insert into tbl1 values('hello!',10);</b><br>sqlite> <b>insert into tbl1 values('goodbye', 20);</b><br>sqlite> <b>select * from tbl1;</b><br>hello!|10<br>goodbye|20<br>sqlite></tt></blockquote><p>You can terminate the sqlite3 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 sqlite3 program looks for a semicolon to know when your SQL command iscomplete. If you omit the semicolon, sqlite3 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><blockquote><tt>sqlite> <b>CREATE TABLE tbl2 (</b><br> ...> <b> f1 varchar(30) primary key,</b><br> ...> <b> f2 text,</b><br> ...> <b> f3 real</b><br> ...> <b>);</b><br>sqlite></tt></blockquote><h3>Aside: Querying the SQLITE_MASTER table</h3><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><blockquote><tt>$ <b>sqlite3 ex1</b><br>SQlite vresion 3.3.10<br>Enter ".help" for instructions<br>sqlite> <b>select * from sqlite_master;</b><br> type = table<br> name = tbl1<br>tbl_name = tbl1<br>rootpage = 3<br> sql = create table tbl1(one varchar(10), two smallint)<br>sqlite></tt></blockquote><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><h3>Special commands to sqlite3</h3><p>Most of the time, sqlite3 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 sqlite3 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><blockquote><tt>sqlite> <b>.help</b><br>.bail ON|OFF Stop after hitting an error. Default OFF<br>.databases List names and files of attached databases<br>.dump ?TABLE? ... Dump the database in an SQL text format<br>.echo ON|OFF Turn command echo on or off<br>.exit Exit this program<br>.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.<br>.header(s) ON|OFF Turn display of headers on or off<br>.help Show this message<br>.import FILE TABLE Import data from FILE into TABLE<br>.indices TABLE Show names of all indices on TABLE<br>.load FILE ?ENTRY? Load an extension library<br>.mode MODE ?TABLE? Set output mode where MODE is one of:<br> csv Comma-separated values<br> column Left-aligned columns. (See .width)<br> html HTML <table> code<br> insert SQL insert statements for TABLE<br> line One value per line<br> list Values delimited by .separator string<br> tabs Tab-separated values<br> tcl TCL list elements<br>.nullvalue STRING Print STRING in place of NULL values<br>.output FILENAME Send output to FILENAME<br>.output stdout Send output to the screen<br>.prompt MAIN CONTINUE Replace the standard prompts<br>.quit Exit this program<br>.read FILENAME Execute SQL in FILENAME<br>.schema ?TABLE? Show the CREATE statements<br>.separator STRING Change separator used by output mode and .import<br>.show Show the current values for various settings<br>.tables ?PATTERN? List names of tables matching a LIKE pattern<br>.timeout MS Try opening locked tables for MS milliseconds<br>.width NUM NUM ... Set column widths for "column" mode<br>sqlite></tt></blockquote><h3>Changing Output Formats</h3><p>The sqlite3 program is able to show the results of a queryin eight different formats: "csv", "column", "html", "insert","line", "tabs", and "tcl".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><blockquote><tt>sqlite> <b>.mode list</b><br>sqlite> <b>select * from tbl1;</b><br>hello|10<br>goodbye|20<br>sqlite></tt></blockquote><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><blockquote><tt>sqlite> <b>.separator ", "</b><br>sqlite> <b>select * from tbl1;</b><br>hello, 10<br>goodbye, 20<br>sqlite></tt></blockquote><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><blockquote><tt>sqlite> <b>.mode line</b><br>sqlite> <b>select * from tbl1;</b><br>one = hello<br>two = 10<br><br>one = goodbye<br>two = 20<br>sqlite></tt></blockquote><p>In column mode, each record is shown on a separate line with thedata aligned in columns. For example:</p><blockquote><tt>sqlite> <b>.mode column</b><br>sqlite> <b>select * from tbl1;</b><br>one two <br>---------- ----------<br>hello 10 <br>goodbye 20 <br>sqlite></tt></blockquote><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><blockquote><tt>sqlite> <b>.width 12 6</b><br>sqlite> <b>select * from tbl1;</b><br>one two <br>------------ ------<br>hello 10 <br>goodbye 20 <br>sqlite></tt></blockquote><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><blockquote><tt>sqlite> <b>.header off</b><br>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -