本文同步自我是一只香脆的大鸡排
sqlite 大家不会陌生 Android 原生数据库就是它了开发过程中我们如果想零时查看数据库中的表结构和内容往往要大费周折的将数据库拷贝出来然后拿工具打开查看, 每回都这样倒腾实在有些麻烦
实际上在 Android Shell 下已经有 sqlite3 环境了, 并且足够满足基础的使用我们来看一下如何使用
目录
数据库文件路径
查看表名称
使用 sql 查询
格式化
花式输出
帮助
数据库文件路径
shell 下找到你的应用名称, 除非我们额外配置过, 否则它们通常放在
/data/data / 应用包名 / databases /
目录下
- root@p212:/data/data/com.xxx.xxx/databases # ls
- Broad.db
- okgo.db
注意如果提示权限问题, 请使用 root 账户
查看表名称
- qlite3 Borad.db.table
- qlite3 Borad.db .tables
- PassengerEntity RoutingEntity android_metadata
这样会列出该数据库中所有的表
使用 sql 查询
- qlite3 Borad.db
- qlite3 Borad.db
- SQLite version 3.8.10.2 2015-05-20 18:17:19
- Enter ".help" for usage hints.
- sqlite>
这样会进入 sqlite 的命令下同时会提示出 sqlite 的版本, 以及输入. help 回车会得到帮助信息
我们现在来查询一条 sql
- sqlite > select * from RoutingEntity;
- sqlite> select * from RoutingEntity;
- 1|2|2|1518247150757|1|1
- 2|2|2|1518247168420|1|1
这样就得到了 RoutingEntity 表中内容了注意务必使用标准的 sql 结束符 (就是分号)
格式化
很快我们发现没有表头看不清每行数据的意思我们可以使用
.header on
后再一次查询:
- sqlite> .header on
- sqlite> select * from RoutingEntity;
- id|current_num|max_num|time|warning_num|type
- 1|2|2|1518247150757|1|1
- 2|2|2|1518247168420|1|1
- sqlite>
就会得到带表头的结果, 但是很快又会发现表头没有对齐
再输入:
.mode column
后再一次查询:
- sqlite> .mode column
- sqlite> select * from RoutingEntity;
- id current_num max_num time warning_num type
- ---------- ---------- ---------- ------------- ---------- ----------
- 1 2 2 1518247150757 1 1
- 2 2 2 1518247168420 1 1
此时会得到一张很漂亮的表结构信息
也就是说, 我们如果需要得到这样的表结构, 我们依次需要输入
- qlite3 xxx.db // 打开数据库
- .header on // 启用表头
- .mode column // 使用列模式
- select * from xxx // 查询某张表
才可以得到漂亮的数据吐槽一句, sqlite 的这种设计有点愚蠢, 为什么不是一开始就默认启用表头和列模式呢?
花式输出
除了 column 模式以外, 其实还内置了几组不同的模式分别是:
- ascii
- csv
- column
- html
- insert
- line
- list
- tabs
- tcl
我们试一下 html 模式
- sqlite> .mode html
- sqlite> select * from RoutingEntity;
- <TR><TH>id</TH>
- <TH>current_num</TH>
- <TH>max_num</TH>
- <TH>time</TH>
- <TH>warning_num</TH>
- <TH>type</TH>
- </TR>
- <TR><TD>1</TD>
- <TD>2</TD>
- <TD>2</TD>
- <TD>1518247150757</TD>
- <TD>1</TD>
- <TD>1</TD>
- </TR>
- <TR><TD>2</TD>
- <TD>2</TD>
- <TD>2</TD>
- <TD>1518247168420</TD>
- <TD>1</TD>
- <TD>1</TD>
- </TR>
哇哦, 有点炸裂
再来一组 tcl
- sqlite> .mode tcl
- sqlite> select * from RoutingEntity;
- "id" "current_num" "max_num" "time" "warning_num" "type"
- "1" "2" "2" "1518247150757" "1" "1"
- "2" "2" "2" "1518247168420" "1" "1
- csv
- sqlite> .mode csv
- sqlite> select * from RoutingEntity;
- id,current_num,max_num,time,warning_num,type
- 1,2,2,1518247150757,1,1
- 2,2,2,1518247168420,1,1
帮助
如果我们想知道更多的用法, 可以使用 .help 来打开查看
- sqlite> .help
- .backup ?DB? FILE Backup DB (default "main") to FILE
- .bail on|off Stop after hitting an error. Default OFF
- .binary on|off Turn binary output on or off. Default OFF
- .clone NEWDB Clone data into NEWDB from the existing database
- .databases List names and files of attached databases
- .dbinfo ?DB? Show status information about the database
- .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
- .eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN
- .exit Exit this program
- .explain ?on|off? Turn output mode suitable for EXPLAIN on or off.
- With no args, it turns EXPLAIN on.
- .fullschema Show schema and the content of sqlite_stat tables
- .headers on|off Turn display of headers on or off
- .help Show this message
- .import FILE TABLE Import data from FILE into TABLE
- .indexes ?TABLE? Show names of all indexes
- If TABLE specified, only show indexes for tables
- matching LIKE pattern TABLE.
- .limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
- .log FILE|off Turn logging on or off. FILE can be stderr/stdout
- .mode MODE ?TABLE? Set output mode where MODE is one of:
- ascii Columns/rows delimited by 0x1F and 0x1E
- 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 strings
- tabs Tab-separated values
- tcl TCL list elements
- .nullvalue STRING Use STRING in place of NULL values
- .once FILENAME Output for the next SQL command only to FILENAME
- .open ?FILENAME? Close existing database and reopen FILENAME
- .output ?FILENAME? Send output to FILENAME or stdout
- .print STRING... Print literal STRING
- .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
- .save FILE Write in-memory database into FILE
- .scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
- .schema ?TABLE? Show the CREATE statements
- If TABLE specified, only show tables matching
- LIKE pattern TABLE.
- .separator COL ?ROW? Change the column separator and optionally the row
- separator for both the output mode and .import
- .shell CMD ARGS... Run CMD ARGS... in a system shell
- .show Show the current values for various settings
- .stats on|off Turn stats on or off
- .system CMD ARGS... Run CMD ARGS... in a system shell
- .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
- .timer on|off Turn SQL timer on or off
- .trace FILE|off Output each SQL statement as it is run
- .vfsname ?AUX? Print the name of the VFS stack
- .width NUM1 NUM2 ... Set column widths for "column" mode
- Negative values right-justify
来源: https://juejin.im/post/5a80fab0f265da4e7a7854ff