Android——sqlite3 基本命令操作

標籤：

             平時用到database的地方不多，這裡記錄一下shell終端下直接對db的基本操作！

                                                 撰寫不易,轉載請註明出處：http://blog.csdn.net/jscese/article/details/40016701

一.概念：

            sqlite3 為android所使用的輕量級資料庫，小巧方便，用於管理android系統中的各種db檔案，在ubuntu中能夠安裝sqliteman 來查看android系統中的db檔案，Framework中的介面位置：/frameworks/base/core/java/android/database/sqlite/SQLiteDatabase.java

二.shell使用：

      我使用的是ubuntu的minicom下的shell終端。以系統setting的database為例，檔案夾為：/data/data/com.android.providers.settings/databases/setting.db

cd 到databases檔案夾下。開啟資料庫檔案：sqlite3 setting.db

SQLite version 3.7.11 2012-03-20 11:35:50                                                                                                Enter ".help" for instructions                                                                                                           Enter SQL statements terminated with a ";"                                                                                               sqlite> 

能夠看到SQL版本號碼，以及簡單提示，使用SQL語句時須要以 “；” 分號結尾！

sqlite3  *.db 開啟資料庫，假設存在就開啟操作，假設不存在就建立，改動之後可以儲存建立。

使用.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.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 databasessqlite> sqlite> 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

能夠看到所支援的命令，當中經常使用的：

.database  顯示資料庫資訊；包括當前資料庫的位置
.tables  或者 .table 顯示表名稱 沒有表則不顯示
.schema  命令能夠查看建立資料對象時的SQL命令；

.mode csv|column|insert|line|list|tabs|tcl   改變輸出格式

預設情況，使用 select * from system 查看system表的所有資料：

sqlite> select * from system;1|volume_music|152|volume_ring|53|volume_system|74|volume_voice|45|volume_alarm|66|volume_notification|57|volume_bluetooth_sco|7... 

能夠看到是列表的形式顯示出來的，並且ID name value 都是以 “ | ” 分隔開來，切割符號由 .separator "X" 來定義。X即為切割符！

使用 .mode culumn 之後：

sqlite> .mode columnsqlite> select * from system;1           volume_music  15        2           volume_ring   5         3           volume_syste  7         4           volume_voice  4         5           volume_alarm  6         6           volume_notif  5         7           volume_bluet  7  

其他類似，都僅僅是為了 改變輸出的格式而已。

1.建立指令：

sqlite> .schema systemCREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON CONFLICT REPLACE,value TEXT);CREATE INDEX systemIndex1 ON system (name);

建立的時候指定參數和屬性，這裡有三個，而且自增，關於資料類型：

NULL: 這個值為空白值
INTEGER: 值被標識為整數,根據值的大小能夠依次被儲存為1,2,3,4,5,6,7,8個位元組
REAL: 全部值都是浮動的數值,被儲存為8位元組的IEEE浮動標記序號.
TEXT: 文本. 值為文本字串,使用資料庫編碼儲存(TUTF-8, UTF-16BE or UTF-16-LE).
BLOB: 值是BLOB資料,怎樣輸入就怎樣儲存,不改變格式.

2.插入資料：

sqlite> .mode insertsqlite> select * from system;INSERT INTO table VALUES(1,'volume_music','15');INSERT INTO table VALUES(2,'volume_ring','5');INSERT INTO table VALUES(3,'volume_system','7');INSERT INTO table VALUES(4,'volume_voice','4');INSERT INTO table VALUES(5,'volume_alarm','6');INSERT INTO table VALUES(6,'volume_notification','5');INSERT INTO table VALUES(7,'volume_bluetooth_sco','7');

如我要在system表裡面插入一條資料：

insert into system values('45','sqlite','jscese');

這裡插入資料要跟建立的時候數量要相應，不然會報：

Error: table table_name has * columns but * values were supplied

3.查詢指定資料：

sqlite> select * from system where name='sqlite';INSERT INTO table VALUES(45,'sqlite','jscese');

依據表類型值來篩選查詢，這裡表的屬性有 _id ,name,value ,可在建立命令中看到！

4.刪除資料：

delete from system where value='jscese';

刪除整個表： drop table table_name

5.更新表資料：

45|sqlite|jscesesqlite> update system set name='sqlite3' where value='jscese';sqlite> select * from system where value='jscese';45|sqlite3|jscese

6.操作問題：

在使用SQL指令之後沒有加 分號就 enter，會進入輸入模式，這個時候再補上 一個 。 分號就可以：

sqlite> select * from system   ...> ;

我直接在minicom下使用 sqlite 無法識別 上下左右方向鍵，以及 回退鍵！

出現 ^[[A  ^H 這種亂碼。

網上別人給出的。我沒試過～http://ljhzzyx.blog.163.com/blog/static/3838031220102595026789/

本機終端adb shell 是能夠識別 回退鍵的

一般退出sqlite3 使用 .quit

實在退不出   ...> 模式 就使用   ctrl+D  強退！

Android——sqlite3 基本命令操作