SQLite3 命令行命令介绍

SQLite3 常用命令介绍

1. 概述

        本文介绍Android手机ENG版本下SQLite3常用命令行命令。

2. 命令介绍

1. .help

$ adb shell$ sqlite3 data/data/com.android.providers.contacts/databases/contacts2.dbsqlite> .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 offsqlite> 

        .help 是SQLite3的帮助命令，输入.help可以查看命令行所有命令。

2. .backup

sqlite> .backup main bryan.dbsqlite> .quitroot@android:/data/data/com.android.providers.contacts/databases # lsbryan.dbbryan.db-journalcontacts2.dbcontacts2.db-journalprofile.dbprofile.db-journal

        .backup 是用来备份当前数据库到一个新的数据库中的。当前数据库默认为main，因此执行上述命令之后，退出，查看当前数据库下新增了备份数据库bryan.db，进入bryan.db，你会发现bryan.db的表，View等schema都跟contacts.db一模一样。如下，我们为了讲述下面的命令，就在bryan.db中做测试进行讲解。

3. .bail

        .bail命令为执行一个命令时，如果遇到错误，则停止。默认为OFF。

4. .dababases

sqlite> .databasesseq  name             file                                                      ---  ---------------  ----------------------------------------------------------0    main             //data/data/com.android.providers.contacts/databases/conta

        .databases 是显示数据库的路径和数据库文件。后面的没有显示出来，是因为SQLite3的行数限制。

5. .exit

        退出当前数据库。

6. .header

        .header ON 表示显示表头，.header OFF 表示不显示表头。即表的属性栏。

sqlite> .header onsqlite> 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|lookup2|1|3|1|1|2|Bryan Zhu|15811005268|[phone]|2|2|1383185775270|0|1|content://com.android.contacts/contacts/1/photo|3505r1-294957274159354F4|2|6|2|2|2|Kidult Chu|13913003315|[phone]|2|2|1383185800159|0|4|content://com.android.contacts/contacts/2/photo|3505r2-3B372D4F3D4D2B354F6|0|9|3|3|2|Cdma Test 01|02587651234|[phone]|2|-1|0|-1|7|content://com.android.contacts/contacts/3/photo|3505r3-2B2D3F274D2F4B4D12148|0|12|4|4|2|Cdma Test 02|01088015678|[phone]|2|-1|0|-1|10|content://com.android.contacts/contacts/4/photo|3505r4-2B2D3F274D2F4B4D121610|0|15|5|5|2|秦始皇|18905553456|[phone]|2|-1|0|-1|13|content://com.android.contacts/contacts/5/photo|3505r5-E05DE8E01C2CE0570612|3|18|6|6|2|姚秦法师|13721116667|[phone]|2|2|1383185815628|0|16|content://com.android.contacts/contacts/6/photo|3505r6-E01C4AE05DE8E0432AE024D614|0|21|7|7|2|鸠摩罗什|01055203344|[phone]|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058Csqlite> 

sqlite> .header offsqlite> select * from view_bw_smart_dial;2|1|3|1|1|2|Bryan Zhu|15811005268|[phone]|2|2|1383185775270|0|1|content://com.android.contacts/contacts/1/photo|3505r1-294957274159354F4|2|6|2|2|2|Kidult Chu|13913003315|[phone]|2|2|1383185800159|0|4|content://com.android.contacts/contacts/2/photo|3505r2-3B372D4F3D4D2B354F6|0|9|3|3|2|Cdma Test 01|02587651234|[phone]|2|-1|0|-1|7|content://com.android.contacts/contacts/3/photo|3505r3-2B2D3F274D2F4B4D12148|0|12|4|4|2|Cdma Test 02|01088015678|[phone]|2|-1|0|-1|10|content://com.android.contacts/contacts/4/photo|3505r4-2B2D3F274D2F4B4D121610|0|15|5|5|2|秦始皇|18905553456|[phone]|2|-1|0|-1|13|content://com.android.contacts/contacts/5/photo|3505r5-E05DE8E01C2CE0570612|3|18|6|6|2|姚秦法师|13721116667|[phone]|2|2|1383185815628|0|16|content://com.android.contacts/contacts/6/photo|3505r6-E01C4AE05DE8E0432AE024D614|0|21|7|7|2|鸠摩罗什|01055203344|[phone]|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058Csqlite> 

7. .import

        .import表示将文件FILE中的数据导入到表中。具体方法如下：
1>. Ubuntu下新建一个文件imfile, 写入一行数据，比如如下，然后保存imfile文件：

15|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||

2>. 将imfile文件push进数据库当前路径：

$ adb push imfile /data/data/com.android.providers.contacts/databases/

3>. 进入数据库bryan.db中：

# sqlite3 bryan.dbSQLite version 3.7.11 2012-03-20 11:35:50Enter ".help" for instructionsEnter 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|[phone]|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|[phone]|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|[phone]|2|-1||-1|0||7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0||8|0|12|4|0|2||01088015678|[phone]|2|-1||-1|0||9|0|14|5|0|1|秦始皇|||0|-1||-1|0||10|0|15|5|0|2||18905553456|[phone]|2|-1||-1|0||11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0||12|3|18|6|0|2||13721116667|[phone]|2|2|1383185811830|0|0||13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0||14|0|21|7|0|2||01055203344|[phone]|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|[phone]|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|[phone]|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|[phone]|2|-1||-1|0||7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0||8|0|12|4|0|2||01088015678|[phone]|2|-1||-1|0||9|0|14|5|0|1|秦始皇|||0|-1||-1|0||10|0|15|5|0|2||18905553456|[phone]|2|-1||-1|0||11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0||12|3|18|6|0|2||13721116667|[phone]|2|2|1383185811830|0|0||13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0||14|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||15|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||                 /** 该行就表示新import进来的数据 */sqlite> 

8. .mode

1>. .mode csv 模式，数据属性列之间逗号显示：

sqlite> .mode csvsqlite> 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,"",""sqlite> 

2>.  .mode column模式，属性列对齐：

sqlite> .mode columnsqlite> 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  [phone]     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  [phone]     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  [phone]      2            -1                         -1          0                                 7           0            11          4               0           1           Cdma Test 02                                 0            -1                         -1          0                                 8           0            12          4               0           2                         0108801567  [phone]      2            -1                         -1          0                                 9           0            14          5               0           1           秦始皇                                    0            -1                         -1          0                                 10          0            15          5               0           2                         1890555345  [phone]     2            -1                         -1          0                                 11          3            17          6               0           1           姚秦法师                                 0            2           1383185811830  0           0                                 12          3            18          6               0           2                         1372111666  [phone]     2            2           1383185811830  0           0                                 13          0            20          7               0           1           鸠摩罗什                                 0            -1                         -1          0                                 14          0            21          7               0           2                         0105520334  [phone]      2            -1                         -1          0                                 15          0            21          7               0           2                         0105520334  [phone]      2            -1                         -1          0                                 sqlite> 

3>. .mode html模式，以html表格的格式显示：

sqlite> .mode htmlsqlite> .header onsqlite> 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>[phone]</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>[phone]</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>[phone]</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>[phone]</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>[phone]</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>[phone]</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>[phone]</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>[phone]</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 insertsqlite> select * from bw_smart_dial;INSERT INTO table VALUES(1,1,2,1,0,1,'Bryan Zhu',NULL,NULL,0,2,1383185769588,0,0,NULL,NULL);INSERT INTO table VALUES(2,1,3,1,0,2,NULL,'15811005268','[phone]',2,2,1383185769588,0,0,NULL,NULL);INSERT INTO table VALUES(3,2,5,2,0,1,'Kidult Chu',NULL,NULL,0,2,1383185794716,0,0,NULL,NULL);INSERT INTO table VALUES(4,2,6,2,0,2,NULL,'13913003315','[phone]',2,2,1383185794716,0,0,NULL,NULL);INSERT INTO table VALUES(5,0,8,3,0,1,'Cdma Test 01',NULL,NULL,0,-1,NULL,-1,0,NULL,NULL);INSERT INTO table VALUES(6,0,9,3,0,2,NULL,'02587651234','[phone]',2,-1,NULL,-1,0,NULL,NULL);INSERT INTO table VALUES(7,0,11,4,0,1,'Cdma Test 02',NULL,NULL,0,-1,NULL,-1,0,NULL,NULL);INSERT INTO table VALUES(8,0,12,4,0,2,NULL,'01088015678','[phone]',2,-1,NULL,-1,0,NULL,NULL);INSERT INTO table VALUES(9,0,14,5,0,1,'秦始皇',NULL,NULL,0,-1,NULL,-1,0,NULL,NULL);INSERT INTO table VALUES(10,0,15,5,0,2,NULL,'18905553456','[phone]',2,-1,NULL,-1,0,NULL,NULL);INSERT INTO table VALUES(11,3,17,6,0,1,'姚秦法师',NULL,NULL,0,2,1383185811830,0,0,NULL,NULL);INSERT INTO table VALUES(12,3,18,6,0,2,NULL,'13721116667','[phone]',2,2,1383185811830,0,0,NULL,NULL);INSERT INTO table VALUES(13,0,20,7,0,1,'鸠摩罗什',NULL,NULL,0,-1,NULL,-1,0,NULL,NULL);INSERT INTO table VALUES(14,0,21,7,0,2,NULL,'01055203344','[phone]',2,-1,NULL,-1,0,NULL,NULL);INSERT INTO table VALUES(15,0,21,7,0,2,'','01055203344','[phone]',2,-1,'',-1,0,'','');sqlite> 

5>. .mode line模式，以每一行的形式显示：

sqlite> .mode linesqlite> 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 = 15811005268normalized_number = [phone]      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 = 13913003315normalized_number = [phone]      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 = 02587651234normalized_number = [phone]      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 = 01088015678normalized_number = [phone]      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 = 18905553456normalized_number = [phone]      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 = 13721116667normalized_number = [phone]      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 = 01055203344normalized_number = [phone]      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 = 01055203344normalized_number = [phone]      number_type = 2        call_type = -1             date =            sub_id = -1         photo_id = 0        photo_uri =            lookup = sqlite> 

6>. .mode list模式，以列表形式显示。如下分割因为上面使用了.mode csv模式，所以分割符以逗号分割，而不是默认的“|”的符号：

sqlite> .mode listsqlite> 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,lookup1,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> 

7>. .mode tabs模式，每一属性栏以tab键分割，显示如下：

sqlite> .mode tabssqlite> select * from bw_smart_dial;_idcall_log_iddata_idraw_contact_idcontact_iddata_typedisplay_namenumbernormalized_numbernumber_typecall_typedatesub_idphoto_idphoto_urilookup112101Bryan Zhu0213831857695880021310215811005268+861581100526822138318576958800325201Kidult Chu0213831857947160042620213913003315+861391300331522138318579471600508301Cdma Test 010-1-1060930202587651234+8625876512342-1-107011401Cdma Test 020-1-10801240201088015678+8610880156782-1-109014501秦始皇0-1-101001550218905553456+86189055534562-1-1011317601姚秦法师021383185811830001231860213721116667+86137211166672213831858118300013020701鸠摩罗什0-1-101402170201055203344+8610552033442-1-101502170201055203344+8610552033442-1-10sqlite> 

8>. .mode tcl模式：

sqlite> .mode tclsqlite> 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""[phone]""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""[phone]""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""[phone]""2""-1""""-1""0""""""7""0""11""4""0""1""Cdma Test 02""""""0""-1""""-1""0""""""8""0""12""4""0""2""""01088015678""[phone]""2""-1""""-1""0""""""9""0""14""5""0""1""秦��\213�\232\207""""""0""-1""""-1""0""""""10""0""15""5""0""2""""18905553456""[phone]""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""[phone]""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""[phone]""2""-1""""-1""0""""""15""0""21""7""0""2""""01055203344""[phone]""2""-1""""-1""0"""""sqlite> 

9. .nullvalue

        nullvalue命令就是将表中属性为空的值以某种字符串显示出来。默认是显示""，即我们肉眼看到的都是空的。下面看效果：

sqlite> .header onsqlite> 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|lookup1|1|2|1|0|1|Bryan Zhu|||0|2|1383185769588|0|0||2|1|3|1|0|2||15811005268|[phone]|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|[phone]|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|[phone]|2|-1||-1|0||7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0||8|0|12|4|0|2||01088015678|[phone]|2|-1||-1|0||9|0|14|5|0|1|秦始皇|||0|-1||-1|0||10|0|15|5|0|2||18905553456|[phone]|2|-1||-1|0||11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0||12|3|18|6|0|2||13721116667|[phone]|2|2|1383185811830|0|0||13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0||14|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||15|0|21|7|0|2||01055203344|[phone]|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|lookup1|1|2|1|0|1|Bryan Zhu|空|空|0|2|1383185769588|0|0|空|空2|1|3|1|0|2|空|15811005268|[phone]|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|[phone]|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|[phone]|2|-1|空|-1|0|空|空7|0|11|4|0|1|Cdma Test 02|空|空|0|-1|空|-1|0|空|空8|0|12|4|0|2|空|01088015678|[phone]|2|-1|空|-1|0|空|空9|0|14|5|0|1|秦始皇|空|空|0|-1|空|-1|0|空|空10|0|15|5|0|2|空|18905553456|[phone]|2|-1|空|-1|0|空|空11|3|17|6|0|1|姚秦法师|空|空|0|2|1383185811830|0|0|空|空12|3|18|6|0|2|空|13721116667|[phone]|2|2|1383185811830|0|0|空|空13|0|20|7|0|1|鸠摩罗什|空|空|0|-1|空|-1|0|空|空14|0|21|7|0|2|空|01055203344|[phone]|2|-1|空|-1|0|空|空15|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||sqlite> 

10. .output

        .output即将你操作的结果已文件形式保存到当前目录下：

sqlite> .output outputfile1                /** 表示要保存的文件 */sqlite> select * from view_bw_smart_dial;  /** 执行操作结果 */sqlite> .quit                              /** 退出当前数据库 */    root@android:/data/data/com.android.providers.contacts/databases # lsbryan.dbbryan.db-journalcontacts2.dbcontacts2.db-journalimfileoutputfile1                                /** 当前目录中有了要保存的文件 */profile.dbprofile.db-journaltputfile1                                  /** 输入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|lookup2|1|3|1|1|2|Bryan Zhu|15811005268|[phone]|2|2|1383185775270|0|1|content://com.android.contacts/contacts/1/photo|3505r1-294957274159354F4|2|6|2|2|2|Kidult Chu|13913003315|[phone]|2|2|1383185800159|0|4|content://com.android.contacts/contacts/2/photo|3505r2-3B372D4F3D4D2B354F6|0|9|3|3|2|Cdma Test 01|02587651234|[phone]|2|-1|0|-1|7|content://com.android.contacts/contacts/3/photo|3505r3-2B2D3F274D2F4B4D12148|0|12|4|4|2|Cdma Test 02|01088015678|[phone]|2|-1|0|-1|10|content://com.android.contacts/contacts/4/photo|3505r4-2B2D3F274D2F4B4D121610|0|15|5|5|2|秦始皇|18905553456|[phone]|2|-1|0|-1|13|content://com.android.contacts/contacts/5/photo|3505r5-E05DE8E01C2CE0570612|3|18|6|6|2|姚秦法师|13721116667|[phone]|2|2|1383185815628|0|16|content://com.android.contacts/contacts/6/photo|3505r6-E01C4AE05DE8E0432AE024D614|0|21|7|7|2|鸠摩罗什|01055203344|[phone]|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058C15|0|21|7|7|2|鸠摩罗什|01055203344|[phone]|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058Croot@android:/data/data/com.android.providers.contacts/databases # 

11. .output stdout

        .output sdout即在当前标准输入输出屏幕输出：

sqlite> .output stdoutsqlite> select * from view_bw_smart_dial;2|1|3|1|1|2|Bryan Zhu|15811005268|[phone]|2|2|1383185775270|0|1|content://com.android.contacts/contacts/1/photo|3505r1-294957274159354F4|2|6|2|2|2|Kidult Chu|13913003315|[phone]|2|2|1383185800159|0|4|content://com.android.contacts/contacts/2/photo|3505r2-3B372D4F3D4D2B354F6|0|9|3|3|2|Cdma Test 01|02587651234|[phone]|2|-1|0|-1|7|content://com.android.contacts/contacts/3/photo|3505r3-2B2D3F274D2F4B4D12148|0|12|4|4|2|Cdma Test 02|01088015678|[phone]|2|-1|0|-1|10|content://com.android.contacts/contacts/4/photo|3505r4-2B2D3F274D2F4B4D121610|0|15|5|5|2|秦始皇|18905553456|[phone]|2|-1|0|-1|13|content://com.android.contacts/contacts/5/photo|3505r5-E05DE8E01C2CE0570612|3|18|6|6|2|姚秦法师|13721116667|[phone]|2|2|1383185815628|0|16|content://com.android.contacts/contacts/6/photo|3505r6-E01C4AE05DE8E0432AE024D614|0|21|7|7|2|鸠摩罗什|01055203344|[phone]|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058C15|0|21|7|7|2|鸠摩罗什|01055203344|[phone]|2|-1|0|-1|19|content://com.android.contacts/contacts/7/photo|3505r7-E0A81EE031E6E06912E0058Csqlite> 

12. .quit

        .quit表示退出当前数据库。与.exit等同。

13. .read

        .read表示读取指定文件的SQL 命令。
1>. 新建一个文件readfile,编辑该文件，往文件中写入如下SQL语句：

INSERT INTO bw_smart_dial VALUES(16,0,20,7,0,1,'斛冰堂',NULL,NULL,0,-1,NULL,-1,0,NULL,NULL);

2>. 将该文件push到当前数据库所在目录中：

root@android:/data/data/com.android.providers.contacts/databases # lsbryan.dbbryan.db-journalcontacts2.dbcontacts2.db-journalimfileoutputfile1profile.dbprofile.db-journalreadfile                             /** push进来的文件 */

3>. 执行.read命令：

sqlite> .read readfilesqlite> 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|[phone]|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|[phone]|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|[phone]|2|-1||-1|0||7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0||8|0|12|4|0|2||01088015678|[phone]|2|-1||-1|0||9|0|14|5|0|1|秦始皇|||0|-1||-1|0||10|0|15|5|0|2||18905553456|[phone]|2|-1||-1|0||11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0||12|3|18|6|0|2||13721116667|[phone]|2|2|1383185811830|0|0||13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0||14|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||15|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||16|0|20|7|0|1|斛冰堂|||0|-1||-1|0||sqlite> 

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|[phone]|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|[phone]|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|[phone]|2|-1||-1|0||7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0||8|0|12|4|0|2||01088015678|[phone]|2|-1||-1|0||9|0|14|5|0|1|秦始皇|||0|-1||-1|0||10|0|15|5|0|2||18905553456|[phone]|2|-1||-1|0||11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0||12|3|18|6|0|2||13721116667|[phone]|2|2|1383185811830|0|0||13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0||14|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||15|0|21|7|0|2||01055203344|[phone]|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|[phone]|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|[phone]|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|[phone]|2|-1||-1|0||7|0|11|4|0|1|Cdma Test 02|||0|-1||-1|0||8|0|12|4|0|2||01088015678|[phone]|2|-1||-1|0||9|0|14|5|0|1|秦始皇|||0|-1||-1|0||10|0|15|5|0|2||18905553456|[phone]|2|-1||-1|0||11|3|17|6|0|1|姚秦法师|||0|2|1383185811830|0|0||12|3|18|6|0|2||13721116667|[phone]|2|2|1383185811830|0|0||13|0|20|7|0|1|鸠摩罗什|||0|-1||-1|0||14|0|21|7|0|2||01055203344|[phone]|2|-1||-1|0||sqlite> 

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> 

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---[phone]---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---[phone]---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---[phone]---2----1-------1---0------7---0---11---4---0---1---Cdma Test 02---------0----1-------1---0------8---0---12---4---0---2------01088015678---[phone]---2----1-------1---0------9---0---14---5---0---1---秦始皇---------0----1-------1---0------10---0---15---5---0---2------18905553456---[phone]---2----1-------1---0------11---3---17---6---0---1---姚秦法师---------0---2---1383185811830---0---0------12---3---18---6---0---2------13721116667---[phone]---2---2---1383185811830---0---0------13---0---20---7---0---1---鸠摩罗什---------0----1-------1---0------14---0---21---7---0---2------01055203344---[phone]---2----1-------1---0------sqlite> 

17. .show

        .show表示显示当前各种各样的设置：

sqlite> .show     echo: off  explain: off  headers: off     mode: listnullvalue: ""   output: stdoutseparator: "---"    stats: off    width: sqlite> 

18. .stats

        .stats表示显示当前操作的各种状态，包括内存使用等，见如下红色状态：

sqlite> .stats ONsqlite> 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---[phone]---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---[phone]---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---[phone]---2----1-------1---0------7---0---11---4---0---1---Cdma Test 02---------0----1-------1---0------8---0---12---4---0---2------01088015678---[phone]---2----1-------1---0------9---0---14---5---0---1---秦始皇---------0----1-------1---0------10---0---15---5---0---2------18905553456---[phone]---2----1-------1---0------11---3---17---6---0---1---姚秦法师---------0---2---1383185811830---0---0------12---3---18---6---0---2------13721116667---[phone]---2---2---1383185811830---0---0------13---0---20---7---0---1---鸠摩罗什---------0----1-------1---0------14---0---21---7---0---2------01055203344---[phone]---2----1-------1---0------Memory Used:                         535928 (max 1054312) bytesNumber of Outstanding Allocations:   3097 (max 6229)Number of Pcache Overflow Bytes:     333240 (max 657984) bytesNumber of Scratch Overflow Bytes:    0 (max 4456) bytesLargest Allocation:                  64000 bytesLargest Pcache Allocation:           4244 bytesLargest Scratch Allocation:          4456 bytesLookaside Slots Used:                53 (max 93)Successful lookaside attempts:       833Lookaside failures due to size:      276Lookaside failures due to OOM:       0Pager Heap Usage:                    329212 bytesPage cache hits:                     60Page cache misses:                   76Schema Heap Usage:                   129568 bytesStatement Heap/Lookaside Usage:      8560 bytesFullscan Steps:                      13Sort Operations:                     0Autoindex Inserts:                   0sqlite> 

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_membershipdata_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> 

20. .timer

        .timer ON表示显示执行当前操作所需时间：

sqlite> .timer ONsqlite> select * from view_smart_dial;1|Bryan Zhu|15811005268|2|1|27926948|27926948|SIM13|Cdma Test 01|02587651234|2|7|2362837801|2362837801|SIM14|Cdma Test 02|01088015678|2|10|2362837802|2362837802|SIM12|Kidult Chu|13913003315|2|4|543858248|543858248|SIM16|姚秦法师|13721116667|2|16|92674632744|9737|SIM15|秦始皇|18905553456|2|13|74674448264|774|SIM17|鸠摩罗什|01055203344|2|19|548665867436|5657|SIM1CPU Time: user 0.040000 sys 0.000000sqlite> 

3. 总结

        关于SQLite3命令行下的各种命令，具体语法可以通过.help帮助文档来查看。笔者也是初学，按照帮助文档一步一步操作，多多动手实践，即可得出自己想要看到的效果。上述各种命令行命令可以组合成你想要显示的效果。

 半童哥哥
2013.10.31