首页 > Other > sqlite3命令行工具的简单用法实例

sqlite3命令行工具的简单用法实例

2013-03-13 11:33 星期三    浏览: 1,958    绿 发表评论 阅读评论

调试程序,需要检查app创建的数据库中的数据。参照Google官方文档目录下的帮助文件:docs/tools/help/sqlite3.html

我们可以知道通过adb shell连接到设备上后,在设备上直接使用sqlite3命令行工具。也可以将数据库文件pull到PC上,然后进行操作。本次操作选取后者:

先将/data/data/com.redwolfsoft.newsclient/databases/下的newsclient.db文件拷贝到/sdcard/tmp下,然后再pull(避免没有权限问题),然后取出数据库文件:

./adb pull    /sdcard/tmp/newsclient.db   ~/tmp2/

查看sqlite3在何路径下:
bill@ubuntu:~/tmp2$ which sqlite3
/home/bill/android-sdk-linux/tools/sqlite3

进入sqlite3:
bill@ubuntu:~/tmp2$ sqlite3 ./newsclient.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter “.help” for instructions
Enter SQL statements terminated with a “;”

查看帮助:
sqlite> .help
.backup ?DB? FILE Backup DB (default “main”) to FILE
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? … Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.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.
With no args, it turns EXPLAIN on.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices ?TABLE? Show names of all indices
If TABLE specified, only show indices for tables
matching LIKE pattern TABLE.
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Print STRING in place of NULL values
.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
.restore ?DB? FILE Restore content of DB (default “main”) from FILE
.schema ?TABLE? Show the CREATE statements
If TABLE specified, only show tables matching
LIKE pattern TABLE.
.separator STRING Change separator used by output mode and .import
.show Show the current values for various settings
.stats ON|OFF Turn stats on or off
.tables ?TABLE? List names of tables
If TABLE specified, only list tables matching
LIKE pattern TABLE.
.timeout MS Try opening locked tables for MS milliseconds
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 … Set column widths for “column” mode
.timer ON|OFF Turn the CPU timer measurement on or off

查看数据库关联文件信息:sqlite> .databases
seq name file
— ————— ———————————————————-
0 main /home/bill/tmp2/./newsclient.db
1 temp

查看表信息,本例中有两个表(android_metadata为Android默认创建):
sqlite> .tables
android_metadata mytable

查看创建表格的语句:sqlite> .schema android_metadata
CREATE TABLE android_metadata (locale TEXT);

sqlite> .schema mytable
CREATE TABLE mytable (category integer, title varchar, time varchar, url varchar, isread integer, details nvarchar);

不指定表格名称,则查看所有(若记不清楚表格名称,可在表格名称前后加问号进行模糊匹配):
sqlite> .schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE mytable (category integer, title varchar, time varchar, url varchar, isread integer, details nvarchar);

查看环境变量(注意headers表示表头信息,默认关闭,select操作时将不显示):
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: “”
output: stdout
separator: “|”
stats: off
width:

打开表头信息:sqlite> .headers on
sqlite> .show
echo: off
explain: off
headers: on
mode: list
nullvalue: “”
output: stdout
separator: “|”
stats: off
width:

同mysql,可在其下面执行SQL语句。用select查看表格所有列数据:sqlite> select * from android_metadata;
locale
zh_CN

查看表格中category和isread两列数据,并指定了查询条件(竖线“|”标示分隔符号,此处的header为off):
sqlite> select category,isread  from  mytable  where category=1;
1|0
1|0
1|0
1|0
1|0

header为on时则显示了列头:
sqlite> select category,isread from mytable where category=1;
category|isread
1|0
1|0
1|0
1|0
1|0

退出sqlite3命令行:sqlite> .quit

本文链接地址: http://blog.redwolf-soft.com/?p=2176

原创文章,版权©红狼博客所有, 转载随意,但请注明出处。

    分享到:

相关文章:

  • 无相关文章
分类: Other 标签: , ,
  1. 本文目前尚无任何评论.
订阅评论
  欢迎参与讨论,请在这里发表您的看法、交流您的观点。