深入SQLite一：简介

1
D:\>sqlite3 database.db
2
sqlite> create table cnblogs(user text, fans integer);
3
sqlite> insert into cnblogs values('txw1958',36);
4
sqlite> select * from cnblogs;
5
sqlite> update cnblogs set fans=100 where user='txw1958';
6
sqlite> delete from cnblogs where user='txw1958';
7
sqlite> select * from cnblogs;
8
sqlite> drop table cnblogs;

SQLite系统命令

01
sqlite> .help
02
.backup ?DB? FILE      Backup DB (default "main") to FILE(备份数据库到文件)
03
.bail ON|OFF           Stop after hitting an error.  Default OFF(遇到错误退出，默认关闭)
04
.databases             List names and files of attached databases (查看目前挂的数据库的名字与文件)
05
.dump ?TABLE? ...      Dump the database in an SQL text format(以SQL格式输出表结构)
06
                         If TABLE specified, only dump tables matching
07
                         LIKE pattern TABLE.
08
.echo ON|OFF           Turn command echo on or off 打开/关闭命令行回显
09
.exit                  Exit this program(退出程序)
10
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
11
                         With no args, it turns EXPLAIN on.
12
.header(s) ON|OFF      Turn display of headers on or off 打开|关闭头显示
13
.help                  Show this messfans(显示帮助信息)
14
.import FILE TABLE     Import data from FILE into TABLE(把文件中的数据导入到表中,各字段用separator的值为分隔符)
15
.indices ?TABLE?       Show names of all indices 显示索引名字
16
                         If TABLE specified, only show indices for tables
17
                         matching LIKE pattern TABLE.
18
.load FILE ?ENTRY?     Load an extension library 加载扩展库
19
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout 打开|关闭日志记录
20
.mode MODE ?TABLE?     Set output mode where MODE is one of: (设置输出格式)
21
                         csv      Comma-separated values (各字段以逗号为分隔符输出)
22
                         column   Left-aligned columns.  (See .width)(以.width设置的宽度显示各字段)
23
                         html     HTML <table> code (html表格格式输出)
24
                         insert   SQL insert statements for TABLE (以insert SQL语句形式输出)
25
                         line     One value per line (field = value的形式逐行输出)
26
                         list     Values delimited by .separator string (各字段以separator的值为分隔符输出)
27
                         tabs     Tab-separated values
28
                         tcl      TCL list elements
29
.nullvalue STRING      Print STRING in place of NULL values (输出字符串取代空值)
30
.output FILENAME       Send output to FILENAME (设置输出到文件)
31
.output stdout         Send output to the screen (设置输出到屏幕,默认)
32
.prompt MAIN CONTINUE  Replace the standard prompts (修改提示符)
33
.quit                  Exit this program  (修改分隔符)
34
.read FILENAME         Execute SQL in FILENAME (执行文件中的SQL)
35
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
36
.schema ?TABLE?        Show the CREATE statements
37
                         If TABLE specified, only show tables matching
38
                         LIKE pattern TABLE.
39
.separator STRING      Change separator used by output mode and .import (改变输出模式分隔符)
40
.show                  Show the current values for various settings (显示各项配置信息)
41
.stats ON|OFF          Turn stats on or off
42
.tables ?TABLE?        List names of tables (列出表名)
43
                         If TABLE specified, only list tables matching
44
                         LIKE pattern TABLE.
45
.timeout MS            Try opening locked tables for MS milliseconds (超时时间,单位:毫秒)
46
.width NUM1 NUM2 ...   Set column widths for "column" mode(设置列宽)
47
.timer ON|OFF          Turn the CPU timer measurement on or off
48
sqlite>