sqlite3 常用命令介绍
@H_403_10@2. .backup
@H_403_10@3. .bail .bail命令为执行一个命令时，如果遇到错误，则停止。默认为OFF。
@H_403_10@4. .dababases
@H_403_10@5. .exit 退出当前数据库。
@H_403_10@6. .header .header ON 表示显示表头，.header OFF 表示不显示表头。即表的属性栏。
@H_403_10@7. .import .import表示将文件FILE中的数据导入到表中。具体方法如下：
1>. Ubuntu下新建一个文件imfile,写入一行数据，比如如下，然后保存imfile文件：
@H_403_10@8. .mode 1>. .mode csv 模式，数据属性列之间逗号显示：
2>. .mode column模式，属性列对齐：
3>. .mode html模式，以html表格的格式显示：
4>. .mode insert模式，已insert sql语句的形式显示：
5>. .mode line模式，以每一行的形式显示：
6>. .mode list模式，以列表形式显示。如下分割因为上面使用了.mode csv模式，所以分割符以逗号分割，而不是默认的“|”的符号：
7>. .mode tabs模式，每一属性栏以tab键分割，显示如下：
8>. .mode tcl模式：
@H_403_10@9. .nullvalue nullvalue命令就是将表中属性为空的值以某种字符串显示出来。默认是显示""，即我们肉眼看到的都是空的。下面看效果：
@H_403_10@10. .output .output即将你操作的结果已文件形式保存到当前目录下：
@H_403_10@11. .output stdout .output sdout即在当前标准输入输出屏幕输出：
@H_403_10@12. .quit .quit表示退出当前数据库。与.exit等同。
@H_403_10@13. .read .read表示读取指定文件的sql 命令。
1>. 新建一个文件readfile,编辑该文件，往文件中写入如下sql语句：
@H_403_10@14. .restore .restore命令用于回复当前数据库的修改。
@H_403_10@15. .schema .schema表示查询表的建表语句。
@H_403_10@16. .separator .separator表示表的属性列之间以什么字符串分割。
@H_403_10@17. .show .show表示显示当前各种各样的设置：
@H_403_10@18. .stats .stats表示显示当前操作的各种状态，包括内存使用等，见如下红色状态：
@H_403_10@19. .table .table表示列出当前数据库所有的表和Views：
@H_403_10@20. .timer .timer ON表示显示执行当前操作所需时间：
1. 概述
本文介绍Android手机ENG版本下sqlite3常用命令行命令。2. 命令介绍
@H_403_10@1. .help$ adb shell $ sqlite3 data/data/com.android.providers.contacts/databases/contacts2.db 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 @R_301_456@ timer measurement on or off sqlite>.help 是sqlite3的帮助命令，输入.help可以查看命令行所有命令。
@H_403_10@2. .backup
sqlite> .backup main bryan.db sqlite> .quit root@android:/data/data/com.android.providers.contacts/databases # ls bryan.db bryan.db-journal contacts2.db contacts2.db-journal profile.db profile.db-journal.backup 是用来备份当前数据库到一个新的数据库中的。当前数据库默认为main，因此执行上述命令之后，退出，查看当前数据库下新增了备份数据库bryan.db，进入bryan.db，你会发现bryan.db的表，View等schema都跟contacts.db一模一样。如下，我们为了讲述下面的命令，就在bryan.db中做测试进行讲解。
@H_403_10@3. .bail .bail命令为执行一个命令时，如果遇到错误，则停止。默认为OFF。
@H_403_10@4. .dababases
sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main //data/data/com.android.providers.contacts/databases/conta.databases 是显示数据库的路径和数据库文件。后面的没有显示出来，是因为sqlite3的行数限制。
@H_403_10@5. .exit 退出当前数据库。
@H_403_10@6. .header .header ON 表示显示表头，.header OFF 表示不显示表头。即表的属性栏。
sqlite> .header on sqlite> select * from view_bw_smart_dial; _id|call_log_id|data_id|raw_contact_id|contact_id|data_type|display_name|number|normalized_number|number_type|call_type|date|sub_id|photo_id|photo_uri|lookup 2|1|3|1|1|2|Bryan Zhu|15811005268|+8615811005268|2|2|1383185775270|0|1|content://com.android.contacts/contacts/1/photo|3505r1-294957274159354F 4|2|6|2|2|2|Kidult Chu|13913003315|+8613913003315|2|2|1383185800159|0|4|content://com.android.contacts/contacts/2/photo|3505r2-3B372D4F3D4D2B354F 6|0|9|3|3|2|Cdma Test 01|02587651234|+862587651234|2|-1|0|-1|7|content://com.android.contacts/contacts/3/photo|3505r3-2B2D3F274D2F4B4D1214 8|0|12|4|4|2|Cdma Test 02|01088015678|+861088015678|2|-1|0|-1|10|content://com.android.contacts/contacts/4/photo|3505r4-2B2D3F274D2F4B4D1216 10|0|15|5|5|2|秦始皇|18905553456|+8618905553456|2|-1|0|-1|13|content://com.android.contacts/contacts/5/photo|3505r5-E05DE8E01C2CE05706 12|3|18|6|6|2|姚秦法师|13721116667|+8613721116667|2|2|1383185815628|0|16|content://com.android.contacts/contacts/6/photo|3505r6-E01C4AE05DE8E0432AE024D6 14|0|21|7|7|2|鸠摩罗什|01055203344|+861055203344|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058C sqlite>
sqlite> .header off sqlite> select * from view_bw_smart_dial; 2|1|3|1|1|2|Bryan Zhu|15811005268|+8615811005268|2|2|1383185775270|0|1|content://com.android.contacts/contacts/1/photo|3505r1-294957274159354F 4|2|6|2|2|2|Kidult Chu|13913003315|+8613913003315|2|2|1383185800159|0|4|content://com.android.contacts/contacts/2/photo|3505r2-3B372D4F3D4D2B354F 6|0|9|3|3|2|Cdma Test 01|02587651234|+862587651234|2|-1|0|-1|7|content://com.android.contacts/contacts/3/photo|3505r3-2B2D3F274D2F4B4D1214 8|0|12|4|4|2|Cdma Test 02|01088015678|+861088015678|2|-1|0|-1|10|content://com.android.contacts/contacts/4/photo|3505r4-2B2D3F274D2F4B4D1216 10|0|15|5|5|2|秦始皇|18905553456|+8618905553456|2|-1|0|-1|13|content://com.android.contacts/contacts/5/photo|3505r5-E05DE8E01C2CE05706 12|3|18|6|6|2|姚秦法师|13721116667|+8613721116667|2|2|1383185815628|0|16|content://com.android.contacts/contacts/6/photo|3505r6-E01C4AE05DE8E0432AE024D6 14|0|21|7|7|2|鸠摩罗什|01055203344|+861055203344|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058C sqlite>
@H_403_10@7. .import .import表示将文件FILE中的数据导入到表中。具体方法如下：
1>. Ubuntu下新建一个文件imfile,写入一行数据，比如如下，然后保存imfile文件：
15|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0||2>. 将imfile文件push进数据库当前路径：
$ adb push imfile /data/data/com.android.providers.contacts/databases/3>. 进入数据库bryan.db中：
# sqlite3 bryan.db sqlite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter sql statements terminated with a ";"4>. .import数据进Table bw_smart_dial中：
sqlite> select * from bw_smart_dial; 1|1|2|1|0|1|Bryan Zhu|||0|2|1383185769588|0|0|| 2|1|3|1|0|2||15811005268|+8615811005268|2|2|1383185769588|0|0|| 3|2|5|2|0|1|Kidult Chu|||0|2|1383185794716|0|0|| 4|2|6|2|0|2||13913003315|+8613913003315|2|2|1383185794716|0|0|| 5|0|8|3|0|1|Cdma Test 01|||0|-1||-1|0|| 6|0|9|3|0|2||02587651234|+862587651234|2|-1||-1|0|| 7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0|| 8|0|12|4|0|2||01088015678|+861088015678|2|-1||-1|0|| 9|0|14|5|0|1|秦始皇|||0|-1||-1|0|| 10|0|15|5|0|2||18905553456|+8618905553456|2|-1||-1|0|| 11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0|| 12|3|18|6|0|2||13721116667|+8613721116667|2|2|1383185811830|0|0|| 13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0|| 14|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| sqlite> sqlite> .import imfile bw_smart_dial sqlite> select * from bw_smart_dial; 1|1|2|1|0|1|Bryan Zhu|||0|2|1383185769588|0|0|| 2|1|3|1|0|2||15811005268|+8615811005268|2|2|1383185769588|0|0|| 3|2|5|2|0|1|Kidult Chu|||0|2|1383185794716|0|0|| 4|2|6|2|0|2||13913003315|+8613913003315|2|2|1383185794716|0|0|| 5|0|8|3|0|1|Cdma Test 01|||0|-1||-1|0|| 6|0|9|3|0|2||02587651234|+862587651234|2|-1||-1|0|| 7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0|| 8|0|12|4|0|2||01088015678|+861088015678|2|-1||-1|0|| 9|0|14|5|0|1|秦始皇|||0|-1||-1|0|| 10|0|15|5|0|2||18905553456|+8618905553456|2|-1||-1|0|| 11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0|| 12|3|18|6|0|2||13721116667|+8613721116667|2|2|1383185811830|0|0|| 13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0|| 14|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| 15|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| /** 该行就表示新import进来的数据 */ sqlite>
@H_403_10@8. .mode 1>. .mode csv 模式，数据属性列之间逗号显示：
sqlite> .mode csv sqlite> select * from bw_smart_dial; 1,1,2,"Bryan Zhu",1383185769588,3,15811005268,+8615811005268,5,"Kidult Chu",1383185794716,4,6,13913003315,+8613913003315,8,"Cdma Test 01",-1,9,02587651234,+862587651234,7,11,"Cdma Test 02",12,01088015678,+861088015678,14,"秦始皇",10,15,18905553456,+8618905553456,17,"姚秦法师",1383185811830,18,13721116667,+8613721116667,13,20,"鸠摩罗什",21,01055203344,+861055203344,"","" sqlite>
2>. .mode column模式，属性列对齐：
sqlite> .mode column sqlite> select * from bw_smart_dial; 1 1 2 1 0 1 Bryan Zhu 0 2 1383185769588 0 0 2 1 3 1 0 2 1581100526 +8615811005268 2 2 1383185769588 0 0 3 2 5 2 0 1 Kidult Chu 0 2 1383185794716 0 0 4 2 6 2 0 2 1391300331 +8613913003315 2 2 1383185794716 0 0 5 0 8 3 0 1 Cdma Test 01 0 -1 -1 0 6 0 9 3 0 2 0258765123 +862587651234 2 -1 -1 0 7 0 11 4 0 1 Cdma Test 02 0 -1 -1 0 8 0 12 4 0 2 0108801567 +861088015678 2 -1 -1 0 9 0 14 5 0 1 秦始皇 0 -1 -1 0 10 0 15 5 0 2 1890555345 +8618905553456 2 -1 -1 0 11 3 17 6 0 1 姚秦法师 0 2 1383185811830 0 0 12 3 18 6 0 2 1372111666 +8613721116667 2 2 1383185811830 0 0 13 0 20 7 0 1 鸠摩罗什 0 -1 -1 0 14 0 21 7 0 2 0105520334 +861055203344 2 -1 -1 0 15 0 21 7 0 2 0105520334 +861055203344 2 -1 -1 0 sqlite>
3>. .mode html模式，以html表格的格式显示：
sqlite> .mode html sqlite> .header on sqlite> select * from bw_smart_dial; <TR><TH>_id</TH> <TH>call_log_id</TH> <TH>data_id</TH> <TH>raw_contact_id</TH> <TH>contact_id</TH> <TH>data_type</TH> <TH>display_name</TH> <TH>number</TH> <TH>normalized_number</TH> <TH>number_type</TH> <TH>call_type</TH> <TH>date</TH> <TH>sub_id</TH> <TH>photo_id</TH> <TH>photo_uri</TH> <TH>lookup</TH> </TR> <TR><TD>1</TD> <TD>1</TD> <TD>2</TD> <TD>1</TD> <TD>0</TD> <TD>1</TD> <TD>Bryan Zhu</TD> <TD></TD> <TD></TD> <TD>0</TD> <TD>2</TD> <TD>1383185769588</TD> <TD>0</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>2</TD> <TD>1</TD> <TD>3</TD> <TD>1</TD> <TD>0</TD> <TD>2</TD> <TD></TD> <TD>15811005268</TD> <TD>+8615811005268</TD> <TD>2</TD> <TD>2</TD> <TD>1383185769588</TD> <TD>0</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>3</TD> <TD>2</TD> <TD>5</TD> <TD>2</TD> <TD>0</TD> <TD>1</TD> <TD>Kidult Chu</TD> <TD></TD> <TD></TD> <TD>0</TD> <TD>2</TD> <TD>1383185794716</TD> <TD>0</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>4</TD> <TD>2</TD> <TD>6</TD> <TD>2</TD> <TD>0</TD> <TD>2</TD> <TD></TD> <TD>13913003315</TD> <TD>+8613913003315</TD> <TD>2</TD> <TD>2</TD> <TD>1383185794716</TD> <TD>0</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>5</TD> <TD>0</TD> <TD>8</TD> <TD>3</TD> <TD>0</TD> <TD>1</TD> <TD>Cdma Test 01</TD> <TD></TD> <TD></TD> <TD>0</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>6</TD> <TD>0</TD> <TD>9</TD> <TD>3</TD> <TD>0</TD> <TD>2</TD> <TD></TD> <TD>02587651234</TD> <TD>+862587651234</TD> <TD>2</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>7</TD> <TD>0</TD> <TD>11</TD> <TD>4</TD> <TD>0</TD> <TD>1</TD> <TD>Cdma Test 02</TD> <TD></TD> <TD></TD> <TD>0</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>8</TD> <TD>0</TD> <TD>12</TD> <TD>4</TD> <TD>0</TD> <TD>2</TD> <TD></TD> <TD>01088015678</TD> <TD>+861088015678</TD> <TD>2</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>9</TD> <TD>0</TD> <TD>14</TD> <TD>5</TD> <TD>0</TD> <TD>1</TD> <TD>秦始皇</TD> <TD></TD> <TD></TD> <TD>0</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>10</TD> <TD>0</TD> <TD>15</TD> <TD>5</TD> <TD>0</TD> <TD>2</TD> <TD></TD> <TD>18905553456</TD> <TD>+8618905553456</TD> <TD>2</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>11</TD> <TD>3</TD> <TD>17</TD> <TD>6</TD> <TD>0</TD> <TD>1</TD> <TD>姚秦法师</TD> <TD></TD> <TD></TD> <TD>0</TD> <TD>2</TD> <TD>1383185811830</TD> <TD>0</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>12</TD> <TD>3</TD> <TD>18</TD> <TD>6</TD> <TD>0</TD> <TD>2</TD> <TD></TD> <TD>13721116667</TD> <TD>+8613721116667</TD> <TD>2</TD> <TD>2</TD> <TD>1383185811830</TD> <TD>0</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>13</TD> <TD>0</TD> <TD>20</TD> <TD>7</TD> <TD>0</TD> <TD>1</TD> <TD>鸠摩罗什</TD> <TD></TD> <TD></TD> <TD>0</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>14</TD> <TD>0</TD> <TD>21</TD> <TD>7</TD> <TD>0</TD> <TD>2</TD> <TD></TD> <TD>01055203344</TD> <TD>+861055203344</TD> <TD>2</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> <TR><TD>15</TD> <TD>0</TD> <TD>21</TD> <TD>7</TD> <TD>0</TD> <TD>2</TD> <TD></TD> <TD>01055203344</TD> <TD>+861055203344</TD> <TD>2</TD> <TD>-1</TD> <TD></TD> <TD>-1</TD> <TD>0</TD> <TD></TD> <TD></TD> </TR> sqlite>
4>. .mode insert模式，已insert sql语句的形式显示：
sqlite> .mode insert sqlite> select * from bw_smart_dial; INSERT INTO table VALUES(1,'Bryan Zhu',NULL,NULL); INSERT INTO table VALUES(2,'15811005268','+8615811005268',NULL); INSERT INTO table VALUES(3,'Kidult Chu',NULL); INSERT INTO table VALUES(4,'13913003315','+8613913003315',NULL); INSERT INTO table VALUES(5,'Cdma Test 01',NULL); INSERT INTO table VALUES(6,'02587651234','+862587651234',NULL); INSERT INTO table VALUES(7,'Cdma Test 02',NULL); INSERT INTO table VALUES(8,'01088015678','+861088015678',NULL); INSERT INTO table VALUES(9,'秦始皇',NULL); INSERT INTO table VALUES(10,'18905553456','+8618905553456',NULL); INSERT INTO table VALUES(11,'姚秦法师',NULL); INSERT INTO table VALUES(12,'13721116667','+8613721116667',NULL); INSERT INTO table VALUES(13,'鸠摩罗什',NULL); INSERT INTO table VALUES(14,'01055203344','+861055203344',NULL); INSERT INTO table VALUES(15,'',''); sqlite>
5>. .mode line模式，以每一行的形式显示：
sqlite> .mode line sqlite> select * from bw_smart_dial; _id = 1 call_log_id = 1 data_id = 2 raw_contact_id = 1 contact_id = 0 data_type = 1 display_name = Bryan Zhu number = normalized_number = number_type = 0 call_type = 2 date = 1383185769588 sub_id = 0 photo_id = 0 photo_uri = lookup = _id = 2 call_log_id = 1 data_id = 3 raw_contact_id = 1 contact_id = 0 data_type = 2 display_name = number = 15811005268 normalized_number = +8615811005268 number_type = 2 call_type = 2 date = 1383185769588 sub_id = 0 photo_id = 0 photo_uri = lookup = _id = 3 call_log_id = 2 data_id = 5 raw_contact_id = 2 contact_id = 0 data_type = 1 display_name = Kidult Chu number = normalized_number = number_type = 0 call_type = 2 date = 1383185794716 sub_id = 0 photo_id = 0 photo_uri = lookup = _id = 4 call_log_id = 2 data_id = 6 raw_contact_id = 2 contact_id = 0 data_type = 2 display_name = number = 13913003315 normalized_number = +8613913003315 number_type = 2 call_type = 2 date = 1383185794716 sub_id = 0 photo_id = 0 photo_uri = lookup = _id = 5 call_log_id = 0 data_id = 8 raw_contact_id = 3 contact_id = 0 data_type = 1 display_name = Cdma Test 01 number = normalized_number = number_type = 0 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = _id = 6 call_log_id = 0 data_id = 9 raw_contact_id = 3 contact_id = 0 data_type = 2 display_name = number = 02587651234 normalized_number = +862587651234 number_type = 2 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = _id = 7 call_log_id = 0 data_id = 11 raw_contact_id = 4 contact_id = 0 data_type = 1 display_name = Cdma Test 02 number = normalized_number = number_type = 0 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = _id = 8 call_log_id = 0 data_id = 12 raw_contact_id = 4 contact_id = 0 data_type = 2 display_name = number = 01088015678 normalized_number = +861088015678 number_type = 2 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = _id = 9 call_log_id = 0 data_id = 14 raw_contact_id = 5 contact_id = 0 data_type = 1 display_name = 秦始皇 number = normalized_number = number_type = 0 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = _id = 10 call_log_id = 0 data_id = 15 raw_contact_id = 5 contact_id = 0 data_type = 2 display_name = number = 18905553456 normalized_number = +8618905553456 number_type = 2 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = _id = 11 call_log_id = 3 data_id = 17 raw_contact_id = 6 contact_id = 0 data_type = 1 display_name = 姚秦法师 number = normalized_number = number_type = 0 call_type = 2 date = 1383185811830 sub_id = 0 photo_id = 0 photo_uri = lookup = _id = 12 call_log_id = 3 data_id = 18 raw_contact_id = 6 contact_id = 0 data_type = 2 display_name = number = 13721116667 normalized_number = +8613721116667 number_type = 2 call_type = 2 date = 1383185811830 sub_id = 0 photo_id = 0 photo_uri = lookup = _id = 13 call_log_id = 0 data_id = 20 raw_contact_id = 7 contact_id = 0 data_type = 1 display_name = 鸠摩罗什 number = normalized_number = number_type = 0 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = _id = 14 call_log_id = 0 data_id = 21 raw_contact_id = 7 contact_id = 0 data_type = 2 display_name = number = 01055203344 normalized_number = +861055203344 number_type = 2 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = _id = 15 call_log_id = 0 data_id = 21 raw_contact_id = 7 contact_id = 0 data_type = 2 display_name = number = 01055203344 normalized_number = +861055203344 number_type = 2 call_type = -1 date = sub_id = -1 photo_id = 0 photo_uri = lookup = sqlite>
6>. .mode list模式，以列表形式显示。如下分割因为上面使用了.mode csv模式，所以分割符以逗号分割，而不是默认的“|”的符号：
sqlite> .mode list sqlite> select * from bw_smart_dial; _id,call_log_id,data_id,raw_contact_id,contact_id,data_type,display_name,number,normalized_number,number_type,call_type,date,sub_id,photo_id,photo_uri,lookup 1,Bryan Zhu,Kidult Chu,Cdma Test 01,Cdma Test 02,秦始皇,姚秦法师,鸠摩罗什,sqlite>
7>. .mode tabs模式，每一属性栏以tab键分割，显示如下：
sqlite> .mode tabs sqlite> select * from bw_smart_dial; _id call_log_id data_id raw_contact_id contact_id data_type display_name number normalized_number number_type call_type date sub_id photo_id photo_uri lookup 1 1 2 1 0 1 Bryan Zhu 0 2 1383185769588 0 0 2 1 3 1 0 2 15811005268 +8615811005268 2 2 1383185769588 0 0 3 2 5 2 0 1 Kidult Chu 0 2 1383185794716 0 0 4 2 6 2 0 2 13913003315 +8613913003315 2 2 1383185794716 0 0 5 0 8 3 0 1 Cdma Test 01 0 -1 -1 0 6 0 9 3 0 2 02587651234 +862587651234 2 -1 -1 0 7 0 11 4 0 1 Cdma Test 02 0 -1 -1 0 8 0 12 4 0 2 01088015678 +861088015678 2 -1 -1 0 9 0 14 5 0 1 秦始皇 0 -1 -1 0 10 0 15 5 0 2 18905553456 +8618905553456 2 -1 -1 0 11 3 17 6 0 1 姚秦法师 0 2 1383185811830 0 0 12 3 18 6 0 2 13721116667 +8613721116667 2 2 1383185811830 0 0 13 0 20 7 0 1 鸠摩罗什 0 -1 -1 0 14 0 21 7 0 2 01055203344 +861055203344 2 -1 -1 0 15 0 21 7 0 2 01055203344 +861055203344 2 -1 -1 0 sqlite>
8>. .mode tcl模式：
sqlite> .mode tcl sqlite> select * from bw_smart_dial; "_id" "call_log_id" "data_id" "raw_contact_id" "contact_id" "data_type" "display_name" "number" "normalized_number" "number_type" "call_type" "date" "sub_id" "photo_id" "photo_uri" "lookup" "1" "1" "2" "1" "0" "1" "Bryan Zhu" "" "" "0" "2" "1383185769588" "0" "0" "" "" "2" "1" "3" "1" "0" "2" "" "15811005268" "+8615811005268" "2" "2" "1383185769588" "0" "0" "" "" "3" "2" "5" "2" "0" "1" "Kidult Chu" "" "" "0" "2" "1383185794716" "0" "0" "" "" "4" "2" "6" "2" "0" "2" "" "13913003315" "+8613913003315" "2" "2" "1383185794716" "0" "0" "" "" "5" "0" "8" "3" "0" "1" "Cdma Test 01" "" "" "0" "-1" "" "-1" "0" "" "" "6" "0" "9" "3" "0" "2" "" "02587651234" "+862587651234" "2" "-1" "" "-1" "0" "" "" "7" "0" "11" "4" "0" "1" "Cdma Test 02" "" "" "0" "-1" "" "-1" "0" "" "" "8" "0" "12" "4" "0" "2" "" "01088015678" "+861088015678" "2" "-1" "" "-1" "0" "" "" "9" "0" "14" "5" "0" "1" "秦��\213�\232\207" "" "" "0" "-1" "" "-1" "0" "" "" "10" "0" "15" "5" "0" "2" "" "18905553456" "+8618905553456" "2" "-1" "" "-1" "0" "" "" "11" "3" "17" "6" "0" "1" "��\232秦��\225��\210" "" "" "0" "2" "1383185811830" "0" "0" "" "" "12" "3" "18" "6" "0" "2" "" "13721116667" "+8613721116667" "2" "2" "1383185811830" "0" "0" "" "" "13" "0" "20" "7" "0" "1" "鸠�\221���\227��\200" "" "" "0" "-1" "" "-1" "0" "" "" "14" "0" "21" "7" "0" "2" "" "01055203344" "+861055203344" "2" "-1" "" "-1" "0" "" "" "15" "0" "21" "7" "0" "2" "" "01055203344" "+861055203344" "2" "-1" "" "-1" "0" "" "" sqlite>
@H_403_10@9. .nullvalue nullvalue命令就是将表中属性为空的值以某种字符串显示出来。默认是显示""，即我们肉眼看到的都是空的。下面看效果：
sqlite> .header on sqlite> select * from bw_smart_dial; _id|call_log_id|data_id|raw_contact_id|contact_id|data_type|display_name|number|normalized_number|number_type|call_type|date|sub_id|photo_id|photo_uri|lookup 1|1|2|1|0|1|Bryan Zhu|||0|2|1383185769588|0|0|| 2|1|3|1|0|2||15811005268|+8615811005268|2|2|1383185769588|0|0|| 3|2|5|2|0|1|Kidult Chu|||0|2|1383185794716|0|0|| 4|2|6|2|0|2||13913003315|+8613913003315|2|2|1383185794716|0|0|| 5|0|8|3|0|1|Cdma Test 01|||0|-1||-1|0|| 6|0|9|3|0|2||02587651234|+862587651234|2|-1||-1|0|| 7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0|| 8|0|12|4|0|2||01088015678|+861088015678|2|-1||-1|0|| 9|0|14|5|0|1|秦始皇|||0|-1||-1|0|| 10|0|15|5|0|2||18905553456|+8618905553456|2|-1||-1|0|| 11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0|| 12|3|18|6|0|2||13721116667|+8613721116667|2|2|1383185811830|0|0|| 13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0|| 14|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| 15|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| sqlite> sqlite> sqlite> .nullvalue "空" sqlite> select * from bw_smart_dial; _id|call_log_id|data_id|raw_contact_id|contact_id|data_type|display_name|number|normalized_number|number_type|call_type|date|sub_id|photo_id|photo_uri|lookup 1|1|2|1|0|1|Bryan Zhu|空|空|0|2|1383185769588|0|0|空|空 2|1|3|1|0|2|空|15811005268|+8615811005268|2|2|1383185769588|0|0|空|空 3|2|5|2|0|1|Kidult Chu|空|空|0|2|1383185794716|0|0|空|空 4|2|6|2|0|2|空|13913003315|+8613913003315|2|2|1383185794716|0|0|空|空 5|0|8|3|0|1|Cdma Test 01|空|空|0|-1|空|-1|0|空|空 6|0|9|3|0|2|空|02587651234|+862587651234|2|-1|空|-1|0|空|空 7|0|11|4|0|1|Cdma Test 02|空|空|0|-1|空|-1|0|空|空 8|0|12|4|0|2|空|01088015678|+861088015678|2|-1|空|-1|0|空|空 9|0|14|5|0|1|秦始皇|空|空|0|-1|空|-1|0|空|空 10|0|15|5|0|2|空|18905553456|+8618905553456|2|-1|空|-1|0|空|空 11|3|17|6|0|1|姚秦法师|空|空|0|2|1383185811830|0|0|空|空 12|3|18|6|0|2|空|13721116667|+8613721116667|2|2|1383185811830|0|0|空|空 13|0|20|7|0|1|鸠摩罗什|空|空|0|-1|空|-1|0|空|空 14|0|21|7|0|2|空|01055203344|+861055203344|2|-1|空|-1|0|空|空 15|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| sqlite>
@H_403_10@10. .output .output即将你操作的结果已文件形式保存到当前目录下：
sqlite> .output outputfile1 /** 表示要保存的文件 */ sqlite> select * from view_bw_smart_dial; /** 执行操作结果 */ sqlite> .quit /** 退出当前数据库 */ root@android:/data/data/com.android.providers.contacts/databases # ls bryan.db bryan.db-journal contacts2.db contacts2.db-journal imfile outputfile1 /** 当前目录中有了要保存的文件 */ profile.db profile.db-journal tputfile1 /** 输入cat outputfile1 命令查看当前文件 */ < _id|call_log_id|data_id|raw_contact_id|contact_id|data_type|display_name|number|normalized_number|number_type|call_type|date|sub_id|photo_id|photo_uri|lookup 2|1|3|1|1|2|Bryan Zhu|15811005268|+8615811005268|2|2|1383185775270|0|1|content://com.android.contacts/contacts/1/photo|3505r1-294957274159354F 4|2|6|2|2|2|Kidult Chu|13913003315|+8613913003315|2|2|1383185800159|0|4|content://com.android.contacts/contacts/2/photo|3505r2-3B372D4F3D4D2B354F 6|0|9|3|3|2|Cdma Test 01|02587651234|+862587651234|2|-1|0|-1|7|content://com.android.contacts/contacts/3/photo|3505r3-2B2D3F274D2F4B4D1214 8|0|12|4|4|2|Cdma Test 02|01088015678|+861088015678|2|-1|0|-1|10|content://com.android.contacts/contacts/4/photo|3505r4-2B2D3F274D2F4B4D1216 10|0|15|5|5|2|秦始皇|18905553456|+8618905553456|2|-1|0|-1|13|content://com.android.contacts/contacts/5/photo|3505r5-E05DE8E01C2CE05706 12|3|18|6|6|2|姚秦法师|13721116667|+8613721116667|2|2|1383185815628|0|16|content://com.android.contacts/contacts/6/photo|3505r6-E01C4AE05DE8E0432AE024D6 14|0|21|7|7|2|鸠摩罗什|01055203344|+861055203344|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058C 15|0|21|7|7|2|鸠摩罗什|01055203344|+861055203344|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058C root@android:/data/data/com.android.providers.contacts/databases #
@H_403_10@11. .output stdout .output sdout即在当前标准输入输出屏幕输出：
sqlite> .output stdout sqlite> select * from view_bw_smart_dial; 2|1|3|1|1|2|Bryan Zhu|15811005268|+8615811005268|2|2|1383185775270|0|1|content://com.android.contacts/contacts/1/photo|3505r1-294957274159354F 4|2|6|2|2|2|Kidult Chu|13913003315|+8613913003315|2|2|1383185800159|0|4|content://com.android.contacts/contacts/2/photo|3505r2-3B372D4F3D4D2B354F 6|0|9|3|3|2|Cdma Test 01|02587651234|+862587651234|2|-1|0|-1|7|content://com.android.contacts/contacts/3/photo|3505r3-2B2D3F274D2F4B4D1214 8|0|12|4|4|2|Cdma Test 02|01088015678|+861088015678|2|-1|0|-1|10|content://com.android.contacts/contacts/4/photo|3505r4-2B2D3F274D2F4B4D1216 10|0|15|5|5|2|秦始皇|18905553456|+8618905553456|2|-1|0|-1|13|content://com.android.contacts/contacts/5/photo|3505r5-E05DE8E01C2CE05706 12|3|18|6|6|2|姚秦法师|13721116667|+8613721116667|2|2|1383185815628|0|16|content://com.android.contacts/contacts/6/photo|3505r6-E01C4AE05DE8E0432AE024D6 14|0|21|7|7|2|鸠摩罗什|01055203344|+861055203344|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058C 15|0|21|7|7|2|鸠摩罗什|01055203344|+861055203344|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058C sqlite>
@H_403_10@12. .quit .quit表示退出当前数据库。与.exit等同。
@H_403_10@13. .read .read表示读取指定文件的sql 命令。
1>. 新建一个文件readfile,编辑该文件，往文件中写入如下sql语句：
INSERT INTO bw_smart_dial VALUES(16,'斛冰堂',NULL);2>. 将该文件push到当前数据库所在目录中：
root@android:/data/data/com.android.providers.contacts/databases # ls bryan.db bryan.db-journal contacts2.db contacts2.db-journal imfile outputfile1 profile.db profile.db-journal readfile /** push进来的文件 */3>. 执行.read命令：
sqlite> .read readfile sqlite> select * from bw_smart_dial; 1|1|2|1|0|1|Bryan Zhu|||0|2|1383185769588|0|0|| 2|1|3|1|0|2||15811005268|+8615811005268|2|2|1383185769588|0|0|| 3|2|5|2|0|1|Kidult Chu|||0|2|1383185794716|0|0|| 4|2|6|2|0|2||13913003315|+8613913003315|2|2|1383185794716|0|0|| 5|0|8|3|0|1|Cdma Test 01|||0|-1||-1|0|| 6|0|9|3|0|2||02587651234|+862587651234|2|-1||-1|0|| 7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0|| 8|0|12|4|0|2||01088015678|+861088015678|2|-1||-1|0|| 9|0|14|5|0|1|秦始皇|||0|-1||-1|0|| 10|0|15|5|0|2||18905553456|+8618905553456|2|-1||-1|0|| 11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0|| 12|3|18|6|0|2||13721116667|+8613721116667|2|2|1383185811830|0|0|| 13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0|| 14|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| 15|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| 16|0|20|7|0|1|斛冰堂|||0|-1||-1|0|| sqlite>
@H_403_10@14. .restore .restore命令用于回复当前数据库的修改。
sqlite>select * from bw_smart_dial; 1|1|2|1|0|1|Bryan Zhu|||0|2|1383185769588|0|0|| 2|1|3|1|0|2||15811005268|+8615811005268|2|2|1383185769588|0|0|| 3|2|5|2|0|1|Kidult Chu|||0|2|1383185794716|0|0|| 4|2|6|2|0|2||13913003315|+8613913003315|2|2|1383185794716|0|0|| 5|0|8|3|0|1|Cdma Test 01|||0|-1||-1|0|| 6|0|9|3|0|2||02587651234|+862587651234|2|-1||-1|0|| 7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0|| 8|0|12|4|0|2||01088015678|+861088015678|2|-1||-1|0|| 9|0|14|5|0|1|秦始皇|||0|-1||-1|0|| 10|0|15|5|0|2||18905553456|+8618905553456|2|-1||-1|0|| 11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0|| 12|3|18|6|0|2||13721116667|+8613721116667|2|2|1383185811830|0|0|| 13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0|| 14|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| 15|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| 16|0|20|7|0|1|斛冰堂|||0|-1||-1|0|| sqlite> .restore main contacts2.db /** 前一个命令查询结果有16条，执行这条命令之后，将当前bryan.db恢复成Contacts2.db，查询，14条数据. */ sqlite> select * from bw_smart_dial; 1|1|2|1|0|1|Bryan Zhu|||0|2|1383185769588|0|0|| 2|1|3|1|0|2||15811005268|+8615811005268|2|2|1383185769588|0|0|| 3|2|5|2|0|1|Kidult Chu|||0|2|1383185794716|0|0|| 4|2|6|2|0|2||13913003315|+8613913003315|2|2|1383185794716|0|0|| 5|0|8|3|0|1|Cdma Test 01|||0|-1||-1|0|| 6|0|9|3|0|2||02587651234|+862587651234|2|-1||-1|0|| 7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0|| 8|0|12|4|0|2||01088015678|+861088015678|2|-1||-1|0|| 9|0|14|5|0|1|秦始皇|||0|-1||-1|0|| 10|0|15|5|0|2||18905553456|+8618905553456|2|-1||-1|0|| 11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0|| 12|3|18|6|0|2||13721116667|+8613721116667|2|2|1383185811830|0|0|| 13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0|| 14|0|21|7|0|2||01055203344|+861055203344|2|-1||-1|0|| sqlite>
@H_403_10@15. .schema .schema表示查询表的建表语句。
sqlite> .schema bw_smart_dial CREATE TABLE bw_smart_dial (_id INTEGER PRIMARY KEY AUTOINCREMENT,call_log_id INTEGER NOT NULL DEFAULT 0,data_id INTEGER NOT NULL DEFAULT 0,raw_contact_id INTEGER NOT NULL DEFAULT 0,contact_id INTEGER NOT NULL DEFAULT 0,data_type INTEGER NOT NULL DEFAULT 0,display_name TEXT,number TEXT,normalized_number TEXT,number_type INTEGER NOT NULL DEFAULT 0,call_type INTEGER NOT NULL DEFAULT -1,date INTEGER,sub_id INTEGER NOT NULL DEFAULT -1,photo_id INTEGER NOT NULL DEFAULT 0,photo_uri TEXT,lookup TEXT); sqlite>
@H_403_10@16. .separator .separator表示表的属性列之间以什么字符串分割。
sqlite>.separator "---" sqlite> select * from bw_smart_dial; 1---1---2---1---0---1---Bryan Zhu---------0---2---1383185769588---0---0------ 2---1---3---1---0---2------15811005268---+8615811005268---2---2---1383185769588---0---0------ 3---2---5---2---0---1---Kidult Chu---------0---2---1383185794716---0---0------ 4---2---6---2---0---2------13913003315---+8613913003315---2---2---1383185794716---0---0------ 5---0---8---3---0---1---Cdma Test 01---------0----1-------1---0------ 6---0---9---3---0---2------02587651234---+862587651234---2----1-------1---0------ 7---0---11---4---0---1---Cdma Test 02---------0----1-------1---0------ 8---0---12---4---0---2------01088015678---+861088015678---2----1-------1---0------ 9---0---14---5---0---1---秦始皇---------0----1-------1---0------ 10---0---15---5---0---2------18905553456---+8618905553456---2----1-------1---0------ 11---3---17---6---0---1---姚秦法师---------0---2---1383185811830---0---0------ 12---3---18---6---0---2------13721116667---+8613721116667---2---2---1383185811830---0---0------ 13---0---20---7---0---1---鸠摩罗什---------0----1-------1---0------ 14---0---21---7---0---2------01055203344---+861055203344---2----1-------1---0------ sqlite>
@H_403_10@17. .show .show表示显示当前各种各样的设置：
sqlite> .show echo: off explain: off headers: off mode: list nullvalue: "" output: stdout separator: "---" stats: off width: sqlite>
@H_403_10@18. .stats .stats表示显示当前操作的各种状态，包括内存使用等，见如下红色状态：
sqlite> .stats ON sqlite> select * from bw_smart_dial; 1---1---2---1---0---1---Bryan Zhu---------0---2---1383185769588---0---0------ 2---1---3---1---0---2------15811005268---+8615811005268---2---2---1383185769588---0---0------ 3---2---5---2---0---1---Kidult Chu---------0---2---1383185794716---0---0------ 4---2---6---2---0---2------13913003315---+8613913003315---2---2---1383185794716---0---0------ 5---0---8---3---0---1---Cdma Test 01---------0----1-------1---0------ 6---0---9---3---0---2------02587651234---+862587651234---2----1-------1---0------ 7---0---11---4---0---1---Cdma Test 02---------0----1-------1---0------ 8---0---12---4---0---2------01088015678---+861088015678---2----1-------1---0------ 9---0---14---5---0---1---秦始皇---------0----1-------1---0------ 10---0---15---5---0---2------18905553456---+8618905553456---2----1-------1---0------ 11---3---17---6---0---1---姚秦法师---------0---2---1383185811830---0---0------ 12---3---18---6---0---2------13721116667---+8613721116667---2---2---1383185811830---0---0------ 13---0---20---7---0---1---鸠摩罗什---------0----1-------1---0------ 14---0---21---7---0---2------01055203344---+861055203344---2----1-------1---0------ Memory Used: 535928 (max 1054312) bytes Number of Outstanding Allocations: 3097 (max 6229) Number of Pcache Overflow Bytes: 333240 (max 657984) bytes Number of Scratch Overflow Bytes: 0 (max 4456) bytes Largest Allocation: 64000 bytes Largest Pcache Allocation: 4244 bytes Largest Scratch Allocation: 4456 bytes Lookaside Slots Used: 53 (max 93) Successful lookaside attempts: 833 Lookaside failures due to size: 276 Lookaside failures due to OOM: 0 Pager Heap Usage: 329212 bytes Page cache hits: 60 Page cache misses: 76 Schema Heap Usage: 129568 bytes Statement Heap/Lookaside Usage: 8560 bytes Fullscan Steps: 13 Sort Operations: 0 Autoindex Inserts: 0 sqlite>
@H_403_10@19. .table .table表示列出当前数据库所有的表和Views：
sqlite> .table _sync_state photo_files view_entities _sync_state_Metadata properties view_groups accounts raw_contacts view_raw_contacts agg_exceptions search_index view_raw_entities android_Metadata search_index_content view_smart_dial bw_smart_dial search_index_docsize view_stream_items calls search_index_segdir view_v1_contact_methods contacts search_index_segments view_v1_extensions data search_index_stat view_v1_group_membership data_usage_stat settings view_v1_groups default_directory status_updates view_v1_organizations directories stream_item_photos view_v1_people groups stream_items view_v1_phones mimetypes v1_settings view_v1_photos name_lookup view_bw_smart_dial visible_contacts nickname_lookup view_contacts voicemail_status packages view_data phone_lookup view_data_usage_stat sqlite>
@H_403_10@20. .timer .timer ON表示显示执行当前操作所需时间：
sqlite> .timer ON sqlite> select * from view_smart_dial; 1|Bryan Zhu|15811005268|2|1|27926948|27926948|SIM1 3|Cdma Test 01|02587651234|2|7|2362837801|2362837801|SIM1 4|Cdma Test 02|01088015678|2|10|2362837802|2362837802|SIM1 2|Kidult Chu|13913003315|2|4|543858248|543858248|SIM1 6|姚秦法师|13721116667|2|16|92674632744|9737|SIM1 5|秦始皇|18905553456|2|13|74674448264|774|SIM1 7|鸠摩罗什|01055203344|2|19|548665867436|5657|SIM1 @R_301_456@ Time: user 0.040000 sys 0.000000 sqlite>
3. 总结
关于sqlite3命令行下的各种命令，具体语法可以通过.help帮助文档来查看。笔者也是初学，按照帮助文档一步一步操作，多多动手实践，即可得出自己想要看到的效果。上述各种命令行命令可以组合成你想要显示的效果。
半童哥哥 2013.10.31
