當前位置:首頁 » 安卓系統 » androidsqlite優化

androidsqlite優化

發布時間: 2022-09-21 03:23:07

1. 如何使用sqlite,Android上SQLite的最佳實踐

SQLite3是目前最新的SQLite版本。可以從網站上下載SQLite3的源代碼(本書使用的版本是sqlite-3.6.12.tar.gz)。
壓縮後進入sqlite-3.6.12的根目錄,首先命令「./configure」生成Makefile文件,接著運行命令「make」對源代碼進行編譯,最後運行命令「make install」安裝SQLite3。安裝完畢後,可以運行命令sqlite3查看SQLite3是否能正常運行,如下所示:
[root@localhost ~]# sqlite3
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
可以看到,SQLite3啟動後會停留在提示符sqlite>處,等待用戶輸入SQL語句。
在使用SQLite3前需要先了解下SQLite3支持的數據類型。SQLite3支持的基本數據類型主要有以下幾類:
NULL
NUMERIC
INTEGER
REAL
TEXT
SQLite3會自動把其他數據類型轉換成以上5類基本數據類型,轉換規則如下所示:
char、clob、test、varchar—> TEXT
integer—>INTEGER
real、double、float—> REAL
blob—>NULL
其餘數據類型都轉變成NUMERIC
下面通過一個實例來演示SQLite3的使用方法。
新建一個資料庫
新建資料庫test.db(使用.db後綴是為了標識資料庫文件)。在test.db中新建一個表test_table,該表具有name,、sex、age三列。SQLite3的具體操作如下所示:
[root@localhost home]# sqlite3 test.db
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test_table(name, sex, age);
如果資料庫test.db已經存在,則命令「sqlite3 test.db」會在當前目錄下打開test.db。如果資料庫test.db不存在,則命令「sqlite3 test.db」會在當前目錄下新建資料庫test.db。為了提高效率,SQLite3並不會馬上創建test.db,而是等到第一個表創建完成後才會在物理上創建資料庫。
由於SQLite3能根據插入數據的實際類型動態改變列的類型,所以在create語句中並不要求給出列的類型。
創建索引
為了加快表的查詢速度,往往在主鍵上添加索引。如下所示的是在name列上添加索引的過程。
sqlite> create index test_index on test_table(name);
操作數據
如下所示的是在test_table中進行數據的插入、更新、刪除操作:
sqlite> insert into test_table values ('xiaoming', 'male', 20);
sqlite> insert into test_table values ('xiaohong', 'female', 18);
sqlite> select * from test_table;
xiaoming|male|20
xiaohong|female|18
sqlite> update test_table set age=19 where name = 'xiaohong';
sqlite> select * from test_table;
xiaoming|male|20
xiaohong|female|19
sqlite> delete from test_table where name = 'xiaoming';
sqlite> select * from test_table;
xiaohong|female|19
批量操作資料庫
如下所示的是在test_table中連續插入兩條記錄:
sqlite> begin;
sqlite> insert into test_table values ('xiaoxue', 'female', 18);
sqlite> insert into test_table values ('xiaoliu', 'male', 20);
sqlite> commit;
sqlite> select * from test_table;
xiaohong|female|19
xiaoxue|male|18
xiaoliu|male|20
運行命令commit後,才會把插入的數據寫入資料庫中。
資料庫的導入導出
如下所示的是把test.db導出到sql文件中:
[root@localhost home]# sqlite3 test.db ".mp" > test.sql;
test.sql文件的內容如下所示:
BEGIN TRANSACTION;
CREATE TABLE test_table(name, sex, age);
INSERT INTO "test_table" VALUES('xiaohong','female',19);
CREATE INDEX test_index on test_table(name);
COMMIT;
如下所示的是導入test.sql文件(導入前刪除原有的test.db):
[root@localhost home]# sqlite3 test.db < test.sql;
通過對test.sql文件的導入導出,可以實現資料庫文件的備份。
11.2.2 SQLite3的C介面
以上介紹的是SQLite3資料庫的命令操作方式。在實際使用中,一般都是應用程序需要對資料庫進行訪問。為此,SQLite3提供了各種編程語言的使用介面(本書介紹C語言介面)。SQLite3具有幾十個C介面,下面介紹一些常用的C介面。
sqlite_open
作用:打開SQLite3資料庫
原型:int sqlite3_open(const char *dbname, sqlite3 **db)
參數:
dbname:資料庫的名稱;
db:資料庫的句柄;
sqlite_colse
作用:關閉SQLite3資料庫
原型:int sqlite_close(sqlite3 *db)
例如:
test.c:
#include <stdio.h>
#include <sqlite3.h>

static sqlite3 *db=NULL;

int main()
{
int rc;
rc= sqlite3_open("test.db", &db);

if(rc)
{
printf("can't open database!\n");
}
else
{
printf("open database success!\n");
}

sqlite3_close(db);
return 0;
}
運行命令「gcc –o test test.c –lsqlite3」進行編譯,運行test的結果如下所示:
[root@localhost home]# open database success!
sqlite_exec
作用:執行SQL語句
原型:int sqlite3_exec(sqlite3 *db, const char *sql, int (*callback)(void*,int,char**,char**), void *, char **errmsg)
參數:
db:資料庫;
sql:SQL語句;
callback:回滾;
errmsg:錯誤信息
例如:
test.c:
#include <stdio.h>
#include <sqlite3.h>

static sqlite3 *db=NULL;
static char *errmsg=NULL;

int main()
{
int rc;

rc = sqlite3_open("test.db", &db);
rc = sqlite3_exec(db,"insert into test_table values('bao', 'male', 24)", 0, 0, &errmsg);

if(rc)
{
printf("exec fail!\n");
}
else
{
printf("exec success!\n");
}

sqlite3_close(db);
return 0;
}
編譯完成後,運行test的結果如下所示:
[root@localhost home]# ./test
exec success!
[root@localhost home]# sqlite3 test.db
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from test_table;
bao|male|24
sqlite3_get_table
作用:執行SQL查詢
原型:int sqlite3_get_table(sqlite3 *db, const char *zSql, char ***pazResult, int *pnRow, int *pnColumn, char **pzErrmsg)
參數:
db:資料庫;
zSql:SQL語句;
pazResult:查詢結果集;
pnRow:結果集的行數;
pnColumn:結果集的列數;
errmsg:錯誤信息;
sqlite3_free_table
作用:注銷結果集
原型:void sqlite3_free_table(char **result)
參數:
result:結果集;
例如:
test.c:
#include <stdio.h>
#include <sqlite3.h>

static sqlite3 *db=NULL;
static char **Result=NULL;
static char *errmsg=NULL;

int main()
{
int rc, i, j;
int nrow;
int ncolumn;

rc= sqlite3_open("test.db", &db);
rc= sqlite3_get_table(db, "select * from test_table", &Result, &nrow, &ncolumn,
&errmsg);

if(rc)
{
printf("query fail!\n");
}
else
{
printf("query success!\n");
for(i = 1; i <= nrow; i++)
{
for(j = 0; j < ncolumn; j++)
{
printf("%s | ", Result[i * ncolumn + j]);
}
printf("\n");
}
}

sqlite3_free_table(Result);
sqlite3_close(db);
return 0;
}
編譯完成後,運行test的結果如下所示:
[root@localhost home]# ./test
query success!
xiaohong | female | 19 |
xiaoxue | female | 18 |
xiaoliu | male | 20 |
bao | male | 24 |
sqlite3_prepare
作用:把SQL語句編譯成位元組碼,由後面的執行函數去執行
原型:int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **stmt, const char **pTail)
參數:
db:資料庫;
zSql:SQL語句;
nByte:SQL語句的最大位元組數;
stmt:Statement句柄;
pTail:SQL語句無用部分的指針;
sqlite3_step
作用:步步執行SQL語句位元組碼
原型:int sqlite3_step (sqlite3_stmt *)
例如:
test.c:
#include <stdio.h>
#include <sqlite3.h>

static sqlite3 *db=NULL;
static sqlite3_stmt *stmt=NULL;

int main()
{
int rc, i, j;
int ncolumn;

rc= sqlite3_open("test.db", &db);
rc=sqlite3_prepare(db,"select * from test_table",-1,&stmt,0);

if(rc)
{
printf("query fail!\n");
}
else
{
printf("query success!\n");
rc=sqlite3_step(stmt);
ncolumn=sqlite3_column_count(stmt);
while(rc==SQLITE_ROW)
{
for(i=0; i<2; i++)
{
printf("%s | ", sqlite3_column_text(stmt,i));
}
printf("\n");
rc=sqlite3_step(stmt);
}
}

sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
編譯完成後,運行test的結果如下所示:
[root@localhost home]# ./test
query success!
xiaohong | female | 19 |
xiaoxue | female | 18 |
xiaoliu | male | 20 |
bao | male | 24 |
在程序中訪問SQLite3資料庫時,要注意C API的介面定義和數據類型是否正確,否則會得到錯誤的訪問結果。

2. 如何使用SQLite

前些時候看到興趣小組里有人問「Android上SQLite的最佳實踐」是什麼,好奇地搜了一下,確實沒有一個好一點的指導文檔,平時的使用也只是簡單的拷貝code,並沒有深入的研究過。以下是我看到的Kevin關於其使用的心得,原文的大體的意思是:Android例子涵蓋了一些Sqlite的基本用法,但它們並沒有深入地給出合理的使用方法,更重要的是,不合理的使用方法。大多數例子和文檔只是涉及最基本的資料庫查詢,或者教你如何創建一個ContentProvider。從來不提及的地方像: · 什麼地方創建和保存SQLiteOpenHelper實例? · 可以有多少個實例? · 多線程同時訪問資料庫有沒有什麼要擔心的?基本的內容是,你可以任意次數地連接Sqlite資料庫,而且Android系統也支持你這樣做。Sqlite擁有文件級別的鎖,用來同步訪問和防止錯誤。如果你只知道這些,那麼,將會給你帶來很大的痛苦。開源的一個好處是,你可以深入代碼一探究竟。從代碼和一些測試中,我了解到以下事實: · Sqlite擁有文件級別的鎖。許多線程可以同時讀,但只有一個可以寫。鎖阻止多個同時寫入。 · Android在SQLiteDatabase中實現了一些java鎖來確保動作是同步進行。 · 如果你用多個線程瘋狂地訪問資料庫,你的資料庫不會(或不應該)崩潰。沒提到的是,如果你通過多個不同的真實連接同時寫資料庫,其中的某個會失敗,它不會等到前一個完成後繼續寫入。簡單地,不會寫入你的改變,更糟糕的是,你也得不到一個異常,只是在LogCat中輸出一些message,僅此而已。SQLiteOpenHelper類做了一些有趣的事。盡管它有方法可以獲得一個只讀的連接和可讀寫的連接,但實質上它們是同一個連接。假設沒有文件寫錯誤的話,只讀的連接實質上就是一個可讀寫的連接。有趣吧。因此,如果你的app中使用一個helper的話,即便從多線程中使用,你也從未使用多個連接。同樣,一個helper中只有一個SQLiteDatabase的實例,這個實例中實現了一些java鎖。因此,當你正在執行資料庫的操作時,其它db的操作都將鎖定。即便是你使用多個線程來做這些事以便優化資料庫的性能,壞消息,沒有什麼用。按照我的認識,SQLite工作的方式,基本上不可能會破壞你的資料庫,除非代碼里有bug或者有硬體問題。因此,我推薦這樣使用:創建一個SQLiteOpenHelper靜態對象。什麼時候去close它呢?不需要。當app關閉,它會自動釋放文件引用。但是,會不會有「close() was never explicitly called on database」異常呢?如果你注意的話,當連接掛在那裡的時候,你沒有得到那個異常。你只是在連接已經建立,而你又嘗試打開另一個時才會有異常。因此,你只需要打開一次連接。像這樣來使用:public class DatabaseHelper extends OrmLiteSqliteOpenHelper{ private static DatabaseHelper instance; public static synchronized DatabaseHelper getHelper(Context context) { if (instance == null) instance = new DatabaseHelper(context); return instance; }//Other stuff... } 就這些。。。

3. 如何使用SQLite

以下是我看到的Kevin關於其使用的心得,原文的大體的意思是:Android例子涵蓋了一些Sqlite的基本用法,但它們並沒有深入地給出合理的使用方法,更重要的是,不合理的使用方法。大多數例子和文檔只是涉及最基本的資料庫查詢,或者教你如何創建一個ContentProvider。從來不提及的地方像: · 什麼地方創建和保存SQLiteOpenHelper實例? · 可以有多少個實例? · 多線程同時訪問資料庫有沒有什麼要擔心的?基本的內容是,你可以任意次數地連接Sqlite資料庫,而且Android系統也支持你這樣做。Sqlite擁有文件級別的鎖,用來同步訪問和防止錯誤。如果你只知道這些,那麼,將會給你帶來很大的痛苦。開源的一個好處是,你可以深入代碼一探究竟。從代碼和一些測試中,我了解到以下事實: · Sqlite擁有文件級別的鎖。許多線程可以同時讀,但只有一個可以寫。鎖阻止多個同時寫入。 · Android在SQLiteDatabase中實現了一些java鎖來確保動作是同步進行。 · 如果你用多個線程瘋狂地訪問資料庫,你的資料庫不會(或不應該)崩潰。沒提到的是,如果你通過多個不同的真實連接同時寫資料庫,其中的某個會失敗,它不會等到前一個完成後繼續寫入。簡單地,不會寫入你的改變,更糟糕的是,你也得不到一個異常,只是在LogCat中輸出一些message,僅此而已。SQLiteOpenHelper類做了一些有趣的事。盡管它有方法可以獲得一個只讀的連接和可讀寫的連接,但實質上它們是同一個連接。假設沒有文件寫錯誤的話,只讀的連接實質上就是一個可讀寫的連接。有趣吧。因此,如果你的app中使用一個helper的話,即便從多線程中使用,你也從未使用多個連接。同樣,一個helper中只有一個SQLiteDatabase的實例,這個實例中實現了一些java鎖。因此,當你正在執行資料庫的操作時,其它db的操作都將鎖定。即便是你使用多個線程來做這些事以便優化資料庫的性能,壞消息,沒有什麼用。按照我的認識,SQLite工作的方式,基本上不可能會破壞你的資料庫,除非代碼里有bug或者有硬體問題。因此,我推薦這樣使用:創建一個SQLiteOpenHelper靜態對象。什麼時候去close它呢?不需要。當app關閉,它會自動釋放文件引用。但是,會不會有「close() was never explicitly called on database」異常呢?如果你注意的話,當連接掛在那裡的時候,你沒有得到那個異常。你只是在連接已經建立,而你又嘗試打開另一個時才會有異常。因此,你只需要打開一次連接。像這樣來使用:public class DatabaseHelper extends OrmLiteSqliteOpenHelper{ private static DatabaseHelper instance; public static synchronized DatabaseHelper getHelper(Context context) { if (instance == null) instance = new DatabaseHelper(context); return instance; }//Other stuff... } 就這些。。。

4. android手機上sqllite插入數據的性能是多少

SQLite 因其小巧輕便被安卓系統廣泛採用,當然在操作小數據量時,差異並不明顯;但當 SQLite 在操作略大一點的數據時就顯得力不存心了,這時的 CRUD 操作對移動存儲設備的性能有著極大的要求,另外用戶體驗的良好性也對 SQLite 的性能優化提出了要求。那麼,當我們在操作大數據量時如何對 SQLite 進行優化呢?正確的操作是:開啟事務。下面我們通過採用不同的方式向資料庫中插入 10000 條數據來進行比較以體現開啟事務對 SQLite 性能提升方面所做出的貢獻。首先看一張截圖來進行一個感性的認識:

源碼及安裝文件下載方式一:SQLiteDataBase.zip
從上圖中我們會很清晰的看到通過普通方式插入 10000 條數據和開啟事務插入 10000 條數據之間的差異,整整差了 83 秒。下面我們來看測試代碼:

package cn.sunzn.sqlitedatabase;

import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.view.Menu;
import android.view.View;
import android.widget.EditText;

public class MainActivity extends Activity {

protected static final int SUCCESS_INSERT_TO_DB_ONE = 1;
protected static final int SUCCESS_INSERT_TO_DB_TWO = 2;
private EditText et_usedtime1;
private EditText et_usedtime2;
Handler handler = new Handler() {

public void handleMessage(Message msg) {
super.handleMessage(msg);
switch (msg.what) {
case SUCCESS_INSERT_TO_DB_ONE:
Integer usetime_one = (Integer) msg.obj;
et_usedtime1.setText("插入10000條數據耗時:" + usetime_one / 1000 + "秒");
break;
case SUCCESS_INSERT_TO_DB_TWO:
Integer usetime_two = (Integer) msg.obj;
et_usedtime2.setText("插入10000條數據耗時:" + usetime_two / 1000 + "秒");
break;
default:
break;
}
}
};

public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

et_usedtime1 = (EditText) findViewById(R.id.et_usedtime1);
et_usedtime2 = (EditText) findViewById(R.id.et_usedtime2);
}

/**
* 1. 普通方式插入資料庫 10000 條數據
*/
public void insert1(View view) {
MySQLiteOpenHelper openHelper = new MySQLiteOpenHelper(getApplicationContext());
final SQLiteDatabase database = openHelper.getWritableDatabase();
if (database.isOpen()) {
new Thread() {
public void run() {
long start = System.currentTimeMillis();
for (int i = 0; i < 10000; i++) {
ContentValues values = new ContentValues();
values.put("name", "tom:" + i);
database.insert("person", "_id", values);
}
database.close();
long end = System.currentTimeMillis();
int usetime_one = (int) (end - start);
Message message = new Message();
message.what = SUCCESS_INSERT_TO_DB_ONE;
message.obj = usetime_one;
handler.sendMessage(message);
};
}.start();
}
}

/**
* 2. 開啟事務插入資料庫 10000 條數據
*/
public void insert2(View view) {
MySQLiteOpenHelper openHelper = new MySQLiteOpenHelper(getApplicationContext());
final SQLiteDatabase database = openHelper.getWritableDatabase();
if (database.isOpen()) {
new Thread() {
public void run() {
long start = System.currentTimeMillis();
database.beginTransaction();
for (int i = 0; i < 10000; i++) {
ContentValues values = new ContentValues();
values.put("name", "tom:" + i);
database.insert("person", "_id", values);
}
database.setTransactionSuccessful();
database.endTransaction();
database.close();
long end = System.currentTimeMillis();
int usetime_two = (int) (end - start);
Message message = new Message();
message.what = SUCCESS_INSERT_TO_DB_TWO;
message.obj = usetime_two;
handler.sendMessage(message);
};
}.start();
}
}

public boolean onCreateOptionsMenu(Menu menu) {
getMenuInflater().inflate(R.menu.activity_main, menu);
return true;
}
}

為什麼只是開啟了一個事務就會有這么大的差距呢?很簡單,SQLite 預設為每個操作開啟了一個事務,那麼測試代碼循環插入 10000 次開啟了 10000 個事務,"事務開啟 + SQL 執行 + 事務關閉" 自然耗費了大量的時間,這也是後面顯式開啟事務後為什麼如此快的原因。

5. sqlite查詢怎麼優化

SQLite是個典型的嵌入式DBMS,它有很多優點,它是輕量級的,在編譯之後很小,其中一個原因就是在查詢優化方面比較簡單,它只是運用索引機制來進行優化的,經過對SQLite的查詢優化的分析以及對源代碼的研究,我將SQLite的查詢優總結如下:
一、影響查詢性能的因素:
1. 對表中行的檢索數目,越小越好
2. 排序與否。
3. 是否要對一個索引。
4. 查詢語句的形式
二、幾個查詢優化的轉換
1. 對於單個表的單個列而言,如果都有形如T.C=expr這樣的子句,並且都是用OR操作符連接起來,形如: x = expr1 OR expr2 = x OR x = expr3 此時由於對於OR,在SQLite中不能利用索引來優化,所以可以將它轉換成帶有IN操作符的子句:x IN(expr1,expr2,expr3)這樣就可以用索引進行優化,效果很明顯,但是如果在都沒有索引的情況下OR語句執行效率會稍優於IN語句的效率。
2. 如果一個子句的操作符是BETWEEN,在SQLite中同樣不能用索引進行優化,所以也要進行相應的等價轉換: 如:a BETWEEN b AND c可以轉換成:(a BETWEEN b AND c) AND (a>=b) AND (a<=c)。 在上面這個子句中, (a>=b) AND (a<=c)將被設為dynamic且是(a BETWEEN b AND c)的子句,那麼如果BETWEEN語句已經編碼,那麼子句就忽略不計,如果存在可利用的index使得子句已經滿足條件,那麼父句則被忽略。
3. 如果一個單元的操作符是LIKE,那麼將做下面的轉換:x LIKE 『abc%』,轉換成:x>=『abc』 AND x<『abd』。因為在SQLite中的LIKE是不能用索引進行優化的,所以如果存在索引的話,則轉換後和不轉換相差很遠,因為對LIKE不起作用,但如果不存在索引,那麼LIKE在效率方面也還是比不上轉換後的效率的。
三、 幾種查詢語句的處理(復合查詢)
1.查詢語句為:<SelectA> <operator> <selectB> ORDER BY <orderbylist> ORDER BY
執行方法: is one of UNION ALL, UNION, EXCEPT, or INTERSECT. 這個語句的執行過程是先將selectA和selectB執行並且排序,再對兩個結果掃描處理,對上面四種操作是不同的,將執行過程分成七個子過程:
outA: 將selectA的結果的一行放到最終結果集中
outB: 將selectA的結果的一行放到最終結果集中(只有UNION操作和UNION ALL操作,其它操作都不放入最終結果集中)
AltB: 當selectA的當前記錄小於selectB的當前記錄
AeqB: 當selectA的當前記錄等於selectB的當前記錄
AgtB: 當selectA的當前記錄大於selectB的當前記錄
EofA: 當selectA的結果遍歷完
EofB: 當selectB的結果遍歷完
下面就是四種操作的執行過程:
執行順序

UNION ALL

UNION

EXCEPT

INTERSECT

AltB:

outA, nextA

outA, nextA

outA,nextA

nextA

AeqB:

outA, nextA

nextA

nextA

outA, nextA

AgtB:

outB, nextB

outB, nextB

nextB

nextB

EofA:

outB, nextB

outB, nextB

halt

halt

EofB:

outA, nextA

outA, nextA

outA,nextA

halt

2. 如果可能的話,可以把一個用到GROUP BY查詢的語句轉換成DISTINCT語句來查詢,因為GROUP BY有時候可能會用到index,而對於DISTINCT都不會用到索引的 。
四、子查詢扁平化
例子:SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
對這個SQL語句的執行一般默認的方法就是先執行內查詢,把結果放到一個臨時表中,再對這個表進行外部查詢,這就要對數據處理兩次,另外這個臨時表沒有索引,所以對外部查詢就不能進行優化了,如果對上面的SQL進行處理後可以得到如下SQL語句:SELECT x+y AS a FROM t1 WHERE z<100 AND a>5,這個結果顯然和上面的一樣,但此時只需要對
數據進行查詢一次就夠了,另外如果在表t1上有索引的話就避免了遍歷整個表。
運用flatten方法優化SQL的條件:
1.子查詢和外查詢沒有都用集函數
2.子查詢沒有用集函數或者外查詢不是個表的連接
3.子查詢不是一個左外連接的右操作數
4.子查詢沒有用DISTINCT或者外查詢不是個表的連接
5.子查詢沒有用DISTINCT或者外查詢沒有用集函數
6.子查詢沒有用集函數或者外查詢沒有用關鍵字DISTINCT
7.子查詢有一個FROM語句
8.子查詢沒有用LIMIT或者外查詢不是表的連接
9.子查詢沒有用LIMIT或者外查詢沒有用集函數
10.子查詢沒有用集函數或者外查詢沒用LIMIT
11.子查詢和外查詢不是同時是ORDER BY子句
12.子查詢和外查詢沒有都用LIMIT
13.子查詢沒有用OFFSET
14.外查詢不是一個復合查詢的一部分或者子查詢沒有同時用關鍵字ORDER BY和LIMIT
15.外查詢沒有用集函數子查詢不包含ORDER BY
16.復合子查詢的扁平化:子查詢不是一個復合查詢,或者他是一個UNION ALL復合查詢,但他是都由若干個非集函數的查詢構成,他的父查詢不是一個復合查詢的子查詢,也沒有用集函數或者是DISTINCT查詢,並且在FROM語句中沒有其它的表或者子查詢,父查詢和子查詢可能會包含WHERE語句,這些都會受到上面11、12、13條件的限制。
例: SELECT a+1 FROM (
SELECT x FROM tab
UNION ALL
SELECT y FROM tab
UNION ALL
SELECT abs(z*2) FROM tab2
) WHERE a!=5 ORDER BY 1
轉換為:
SELECT x+1 FROM tab WHERE x+1!=5
UNION ALL
SELECT y+1 FROM tab WHERE y+1!=5
UNION ALL
SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5
ORDER BY 1
17.如果子查詢是一個復合查詢,那麼父查詢的所有的ORDER BY語句必須是對子查詢的列的簡單引用
18.子查詢沒有用LIMIT或者外查詢不具有WHERE語句
子查詢扁平化是由專門一個函數實現的,函數為:
static int flattenSubquery(
Parse *pParse, /* Parsing context */
Select *p, /* The parent or outer SELECT statement */
int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
int isAgg, /* True if outer SELECT uses aggregate functions */
int subqueryIsAgg /* True if the subquery uses aggregate functions */
)
它是在Select.c文件中實現的。顯然對於一個比較復雜的查詢,如果滿足上面的條件時對這個查詢語句進行扁平化處理後就可以實現對查詢的優化。如果正好存在索引的話效果會更好!

五、連接查詢
在返回查詢結果之前,相關表的每行必須都已經連接起來,在SQLite中,這是用嵌套循環實現的,在早期版本中,最左邊的是最外層循環,最右邊的是最內層循環,連接兩個或者更多的表時,如果有索引則放到內層循環中,也就是放到FROM最後面,因為對於前面選中的每行,找後面與之對應的行時,如果有索引則會很快,如果沒有則要遍歷整個表,這樣效率就很低,但在新版本中,這個優化已經實現。
優化的方法如下:
對要查詢的每個表,統計這個表上的索引信息,首先將代價賦值為SQLITE_BIG_DBL(一個系統已經定義的常量):
1) 如果沒有索引,則找有沒有在這個表上對rowid的查詢條件:
1.如果有Rowid=EXPR,如果有的話則返回對這個表代價估計,代價計為零,查詢得到的記錄數為1,並完成對這個表的代價估計,
2.如果沒有Rowid=EXPR 但有rowid IN (...),而IN是一個列表,那麼記錄返回記錄數為IN列表中元素的個數,估計代價為NlogN,
3.如果IN不是一個列表而是一個子查詢結果,那麼由於具體這個子查詢不能確定,所以只能估計一個值,返回記錄數為100,代價為200。
4.如果對rowid是范圍的查詢,那麼就估計所有符合條件的記錄是總記錄的三分之一,總記錄估計為1000000,並且估計代價也為記錄數。
5.如果這個查詢還要求排序,則再另外加上排序的代價NlogN
6.如果此時得到的代價小於總代價,那麼就更新總代價,否則不更新。
2) 如果WHERE子句中存在OR操作符,那麼要把這些OR連接的所有子句分開再進行分析。
1. 如果有子句是由AND連接符構成,那麼再把由AND連接的子句再分別分析。
2. 如果連接的子句的形式是X<op><expr>,那麼就再分析這個子句。
3. 接下來就是把整個對OR操作的總代價計算出來。
4. 如果這個查詢要求排序,則再在上面總代價上再乘上排序代價NlogN
5. 如果此時得到的代價小於總代價,那麼就更新總代價,否則不更新。
3) 如果有索引,則統計每個表的索引信息,對於每個索引:
1. 先找到這個索引對應的列號,再找到對應的能用到(操作符必須為=或者是IN(…))這個索引的WHERE子句,如果沒有找到,則退出對每個索引的循環,如果找到,則判斷這個子句的操作符是什麼,如果是=,那麼沒有附加的代價,如果是IN(sub-select),那麼估計它附加代價inMultiplier為25,如果是IN(list),那麼附加代價就是N(N為list的列數)。
2. 再計算總的代價和總的查詢結果記錄數和代價。
3. nRow = pProbe->aiRowEst[i] * inMultiplier;/*計算行數*/
4. cost = nRow * estLog(inMultiplier);/*統計代價*/
5. 如果找不到操作符為=或者是IN(…)的子句,而是范圍的查詢,那麼同樣只好估計查詢結果記錄數為nRow/3,估計代價為cost/3。
6. 同樣,如果此查詢要求排序的話,再在上面的總代價上加上NlogN
7. 如果此時得到的代價小於總代價,那麼就更新總代價,否則不更新。
4) 通過上面的優化過程,可以得到對一個表查詢的總代價(就是上面各個代價的總和),再對第二個表進行同樣的操作,這樣如此直到把FROM子句中所有的表都計算出各自的代價,最後取最小的,這將作為嵌套循環的最內層,依次可以得到整個嵌套循環的嵌套順序,此時正是最優的,達到了優化的目的。
5) 所以循環的嵌套順序不一定是與FROM子句中的順序一致,因為在執行過程中會用索引優化來重新排列順序。
六、索引
在SQLite中,有以下幾種索引:
1) 單列索引
2) 多列索引
3) 唯一性索引
4) 對於聲明為:INTEGER PRIMARY KEY的主鍵來說,這列會按默認方式排序,所以雖然在數據字典中沒有對它生成索引,但它的功能就像個索引。所以如果在這個主鍵上在單獨建立索引的話,這樣既浪費空間也沒有任何好處。
運用索引的注意事項:
1) 對於一個很小的表來說沒必要建立索引
2) 在一個表上如果經常做的是插入更新操作,那麼就要節制使用索引
3) 也不要在一個表上建立太多的索引,如果建立太多的話那麼在查詢的時候SQLite可能不會選擇最好的來執行查詢,一個解決辦法就是建立聚蔟索引
索引的運用時機:
1) 操作符:=、>、<、IN等
2) 操作符BETWEEN、LIKE、OR不能用索引,
如BETWEEN:SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20;
這時就應該將其轉換成:
SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20;
此時如果在myfield上有索引的話就可以用了,大大提高速度

再如LIKE:SELECT * FROM mytable WHERE myfield LIKE 'sql%';
此時應該將它轉換成:
SELECT * FROM mytable WHERE myfield >= 'sql' AND myfield < 'sqm';
此時如果在myfield上有索引的話就可以用了,大大提高速度

再如OR:SELECT * FROM mytable WHERE myfield = 'abc' OR myfield = 'xyz';
此時應該將它轉換成:
SELECT * FROM mytable WHERE myfield IN ('abc', 'xyz');
此時如果在myfield上有索引的話就可以用了,大大提高速度
3) 有些時候索引都是不能用的,這時就應該遍歷全表(程序演示)
SELECT * FROM mytable WHERE myfield % 2 = 1;
SELECT * FROM mytable WHERE substr(myfield, 0, 1) = 'w';
SELECT * FROM mytable WHERE length(myfield) < 5;

6. android sqlite資料庫的更新

一、使用嵌入式關系型SQLite資料庫存儲數據
在Android平台上,集成了一個嵌入式關系型資料庫——SQLite,SQLite3支持NULL、INTEGER、REAL(浮點數字)、 TEXT(字元串文本)和BLOB(二進制對象)數據類型,雖然它支持的類型只有五種,但實際上sqlite3也接受varchar(n)、 char(n)、decimal(p,s) 等數據類型,只不過在運算或保存時會轉成對應的五種數據類型。 SQLite最大的特點是你可以把各種類型的數據保存到任何欄位中,而不用關心欄位聲明的數據類型是什麼。例如:可以在Integer類型的欄位中存放字元串,或者在布爾型欄位中存放浮點數,或者在字元型欄位中存放日期型值。 但有一種情況例外:定義為INTEGER PRIMARY KEY的欄位只能存儲64位整數, 當向這種欄位保存除整數以外的數據時,將會產生錯誤。 另外,在編寫CREATE TABLE 語句時,你可以省略跟在欄位名稱後面的數據類型信息,如下面語句你可以省略name欄位的類型信息:
CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))
SQLite可以解析大部分標准SQL語句,如:
復制代碼 代碼如下:

查詢語句:select * from 表名 where 條件子句 group by 分組字句 having ... order by 排序子句
如: select * from person
select * from person order by id desc
select name from person group by name having count(*)>1
分頁SQL與mysql類似,下面SQL語句獲取5條記錄,跳過前面3條記錄
select * from Account limit 5 offset 3 或者 select * from Account limit 3,5
插入語句:insert into 表名(欄位列表) values(值列表)。如: insert into person(name, age) values(『傳智',3)
更新語句:update 表名 set 欄位名=值 where 條件子句。如:update person set name=『傳智『 where id=10
刪除語句:delete from 表名 where 條件子句。如:delete from person where id=10

二、使用SQLiteOpenHelper對資料庫進行版本管理
我們在編寫資料庫應用軟體時,需要考慮這樣的問題:因為我們開發的軟體可能會安裝在很多用戶的手機上,如果應用使用到了SQLite資料庫,我們必須在用戶初次使用軟體時創建出應用使用到的資料庫表結構及添加一些初始化記錄,另外在軟體升級的時候,也需要對數據表結構進行更新。那麼,我們如何才能實現在用戶初次使用或升級軟體時自動在用戶的手機上創建出應用需要的資料庫表呢?總不能讓我們在每個需要安裝此軟體的手機上通過手工方式創建資料庫表吧?因為這種需求是每個資料庫應用都要面臨的,所以在Android系統,為我們提供了一個名為SQLiteOpenHelper的抽象類,必須繼承它才能使用,它是通過對資料庫版本進行管理來實現前面提出的需求。

為了實現對資料庫版本進行管理,SQLiteOpenHelper類提供了兩個重要的方法,分別是onCreate(SQLiteDatabase db)和onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion),前者用於初次使用軟體時生成資料庫表,後者用於升級軟體時更新資料庫表結構。當調用SQLiteOpenHelper的getWritableDatabase()或者getReadableDatabase()方法獲取用於操作資料庫的SQLiteDatabase實例的時候,如果資料庫不存在,Android系統會自動生成一個資料庫,接著調用onCreate()方法,onCreate()方法在初次生成資料庫時才會被調用,在onCreate()方法里可以生成資料庫表結構及添加一些應用使用到的初始化數據。onUpgrade()方法在資料庫的版本發生變化時會被調用,一般在軟體升級時才需改變版本號,而資料庫的版本是由程序員控制的,假設資料庫現在的版本是1,由於業務的變更,修改了資料庫表結構,這時候就需要升級軟體,升級軟體時希望更新用戶手機里的資料庫表結構,為了實現這一目的,可以把原來的資料庫版本設置為2(有同學問設置為3行不行?當然可以,如果你願意,設置為100也行),並且在 onUpgrade()方法裡面實現表結構的更新。當軟體的版本升級次數比較多,這時在onUpgrade()方法裡面可以根據原版號和目標版本號進行判斷,然後作出相應的表結構及數據更新。

getWritableDatabase()和 getReadableDatabase()方法都可以獲取一個用於操作資料庫的SQLiteDatabase實例。但 getWritableDatabase() 方法以讀寫方式打開資料庫,一旦資料庫的磁碟空間滿了,資料庫就只能讀而不能寫,倘若使用getWritableDatabase()打開資料庫就會出錯。getReadableDatabase()方法先以讀寫方式打開資料庫,如果資料庫的磁碟空間滿了,就會打開失敗,當打開失敗後會繼續嘗試以只讀方式打開資料庫。
注意:getWritableDatabase(),getReadableDatabase的區別是當資料庫寫滿時,調用前者會報錯,調用後者不會,所以如果不是更新資料庫的話,最好調用後者來獲得資料庫連接。
代碼:
復制代碼 代碼如下:

public class DatabaseHelper extends SQLiteOpenHelper {
//類沒有實例化,是不能用作父類構造器的參數,必須聲明為靜態
private static final String name = "ljqdb"; //資料庫名稱
private static final int version = 1; //資料庫版本
public DatabaseHelper(Context context) {
//第三個參數CursorFactory指定在執行查詢時獲得一個游標實例的工廠類,設置為null,代表使用系統默認的工廠類
super(context, name, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS person (
personid integer primary key autoincrement, name varchar(20), age INTEGER)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(" ALTER TABLE person ADD phone VARCHAR(12) NULL "); //往表中增加一列
// DROP TABLE IF EXISTS person 刪除表
}
}

在實際項目開發中,當資料庫表結構發生更新時,應該避免用戶存放於資料庫中的數據丟失。
三、使用SQLiteDatabase操作SQLite資料庫
Android提供了一個名為SQLiteDatabase的類,該類封裝了一些操作資料庫的API,使用該類可以完成對數據進行添加(Create)、查詢(Retrieve)、更新(Update)和刪除(Delete)操作(這些操作簡稱為CRUD)。對SQLiteDatabase的學習,我們應該重點掌握execSQL()和rawQuery()方法。execSQL()方法可以執行insert、delete、update和CREATE TABLE之類有更改行為的SQL語句; rawQuery()方法用於執行select語句。
execSQL()方法的使用例子:
復制代碼 代碼如下:

SQLiteDatabase db = ....;
db.execSQL("insert into person(name, age) values('林計欽', 24)");
db.close();

執行上面SQL語句會往person表中添加進一條記錄,在實際應用中, 語句中的「林計欽」這些參數值會由用戶輸入界面提供,如果把用戶輸入的內容原樣組拼到上面的insert語句, 當用戶輸入的內容含有單引號時,組拼出來的SQL語句就會存在語法錯誤。要解決這個問題需要對單引號進行轉義,也就是把單引號轉換成兩個單引號。有些時候用戶往往還會輸入像「 & 」這些特殊SQL符號,為保證組拼好的SQL語句語法正確,必須對SQL語句中的這些特殊SQL符號都進行轉義,顯然,對每條SQL語句都做這樣的處理工作是比較煩瑣的。 SQLiteDatabase類提供了一個重載後的execSQL(String sql, Object[] bindArgs)方法,使用這個方法可以解決前面提到的問題,因為這個方法支持使用佔位符參數(?)。使用例子如下:
復制代碼 代碼如下:

SQLiteDatabase db = ....;
db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"傳智播客", 4});
db.close();

execSQL(String sql, Object[] bindArgs)方法的第一個參數為SQL語句,第二個參數為SQL語句中佔位符參數的值,參數值在數組中的順序要和佔位符的位置對應。
SQLiteDatabase的rawQuery()用於執行select語句,使用例子如下:
復制代碼 代碼如下:

SQLiteDatabase db = ....;
Cursor cursor = db.rawQuery("select * from person", null);
while (cursor.moveToNext()) {
int personid = cursor.getInt(0); //獲取第一列的值,第一列的索引從0開始
String name = cursor.getString(1);//獲取第二列的值
int age = cursor.getInt(2);//獲取第三列的值
}
cursor.close();
db.close();

rawQuery()方法的第一個參數為select語句;第二個參數為select語句中佔位符參數的值,如果select語句沒有使用佔位符,該參數可以設置為null。帶佔位符參數的select語句使用例子如下:
復制代碼 代碼如下:

Cursor cursor = db.rawQuery("select * from person where name like ? and age=?", new String[]{"%林計欽%", "4"});

Cursor是結果集游標,用於對結果集進行隨機訪問,如果大家熟悉jdbc, 其實Cursor與JDBC中的ResultSet作用很相似。使用moveToNext()方法可以將游標從當前行移動到下一行,如果已經移過了結果集的最後一行,返回結果為false,否則為true。另外Cursor 還有常用的moveToPrevious()方法(用於將游標從當前行移動到上一行,如果已經移過了結果集的第一行,返回值為false,否則為true )、moveToFirst()方法(用於將游標移動到結果集的第一行,如果結果集為空,返回值為false,否則為true )和moveToLast()方法(用於將游標移動到結果集的最後一行,如果結果集為空,返回值為false,否則為true ) 。

除了前面給大家介紹的execSQL()和rawQuery()方法, SQLiteDatabase還專門提供了對應於添加、刪除、更新、查詢的操作方法: insert()、delete()、update()和query() 。這些方法實際上是給那些不太了解SQL語法的菜鳥使用的,對於熟悉SQL語法的程序員而言,直接使用execSQL()和rawQuery()方法執行SQL語句就能完成數據的添加、刪除、更新、查詢操作。

7. android sqlite3 表 有沒有限制

SQLite只支持平面事務;它沒有嵌套和營救點能力。嵌套意味著在一個事務中可以有子事務的能力。營救點允許一個事務返回到前面已經到達的狀態。它沒有能力確保高層次事務的並發。它允許在單個的資料庫文件上多個並發的讀事務,但是只能有一個排他的寫事務。這個局限性意味著如果有事務在讀資料庫文件的一部分,所有其他的事務將被禁止寫該文件的任何一部分。類似的,如果有事務在寫資料庫文件的一部分,所有其他事務將被禁止讀或者寫該文件的任何一部分。

應用限制

因為它事務處理的有限並發,SQLite只擅長處理小型的事務。在很多情況下,這不是問題。每個應用迅速的完成它的資料庫工作然後繼續前進,因此沒有一個事務會持有資料庫超過多少毫秒。但是在一些應用中,特別是寫入密集的,要求更多的並發的事務處理(表或者行級別的而不是資料庫級別的)那麼你將要為該應用使用其他不同的DBMS。SQLite並不打算成為一個企業DBMS。他最適合於實現,維護和管理的簡單性比商業資料庫的無盡復雜特性更為重要的情況。

NFS問題

SQLite使用本地文件鎖原語來控制事務處理的並發性。如果資料庫文件駐留在網路分區上,可能會導致文件鎖不能工作。很多的NFS實現被認為在它們的文件鎖中是有bug的(在Unix和Windows上)。如果文件鎖不能像預計的一樣工作,那麼就可能會有兩個或兩個以上的應用程序在同時修改相同資料庫的同一部分,導致了資料庫的毀壞。因為這個問題的出現是因為位於下層的文件系統的實現的BUG,所以SQLite沒有辦法阻止它的發生。

另一原因是大多數網路文件系統的連接延時,效果不是很好。在這種環境下,在資料庫文件必須要跨網路訪問的情況下,實現了客戶端-伺服器的模型的DBMS會比SQLite更有效。

資料庫規模

因為它的開發人員的開發設計選擇,SQLite可能不是一個做非常大型的資料庫好選擇。在理論上,一個資料庫文件文件可以有2TB(241)。日誌子系統的內存開銷和資料庫大小是成比例的。對每個寫事務,無論事務實際是寫是讀那個頁,SQLite為每個資料庫頁維護一個內存內信息位。默認的頁大小是1024位元組。即使如此,對一個有超過幾百萬頁的資料庫,內存開銷可能成為一個嚴重的瓶頸。

對象的數目和類型

一個表或者索引被限制為最多有264 – 1個項。當然,你不可能有這么多的條目,因為資料庫的241位元組大小限制。在SQLite的當前的實現中,一個單獨的條目能夠持有230位元組的數據。(下層的文件格式支持行大小相當於262位元組的數據。)在打開一個資料庫文件時,SQLite會閱讀並且預處理來自主目錄表的所有條目並且創建很多內存目錄對象。所以,為了最好的性能,最好控製表,索引,視圖和觸發器的數目。同樣雖然沒有限製表中列的數目,超過幾百列還是似乎太過的。只有表開始的31列是候選為必然被優化的。你能夠在一個索引中盡可能加入列,但是有超過30列的索引將不會被用來優化。

宿主變數引用

在一些嵌入DBMS中,SQL語句能夠直接引用宿主變數(即來自應用程序空間的那些值)。在SQLite中這是不行的。作為替代SQLite允許使用sqlite3_bind_* API函數來對輸入參數而不是輸出值綁定對SQL語句宿主變數。這種策略通常比直接的訪問策略更好,因為後者需要特殊的預處理來將SQL語句轉化為特殊的API調用。

存儲過程

很多DBMS有被稱為存儲過程的能力來創建和存儲。存儲過程是形成邏輯作業單元和執行特殊任務的一組SQL語句。SQL查詢過程能夠使用這些過程。SQLite沒有這個能力。

另外一些局限

不支持外鍵

如果你的表格中有類似的語句,sqlite會忽略的:

create table zope_suf.userroles (
name varchar(64) not null references zope_suf.users(name)

8. sqlite 創建的視圖,查詢很慢,怎麼解決

和表的優化差不多 盡量不使用*
在java中盡量少使用+連接代碼

9. Android 如何提高Sqlite的效率

對於Android平台上的資料庫而言使用了嵌入式越來越流行的SQLite,為了更好的跨平台我們推薦大家使用原始SQL語句直接操作,在代碼和處理效率上都有不小的提高,不過要做好SQL語句異常處理。
下面我們說下rawQuery的好處,可以看到查詢的代碼直接使用SQL語句,通過性能實測效率比Android封裝過的類要快不少,但不能配合一些 Adapter的使用,不過總體上在跨平台上很突出,下面為本地使用方法的偽代碼,沒有做任何構造和實例化,希望讓項目經理知道rawSQL的優勢在 Android平台上的使用。
SQLiteDatabase db;
String args[] = {id};
ContentValues cv = new ContentValues();

cv.put("android123", id);
Cursor c = db.rawQuery("SELECT * FROM table WHERE android123=?", args); 執行本地SQL語句查詢

if (c.getCount() != 0) {
//dosomething

ContentValues cv = new ContentValues();

cv.put("android123","cwj");
db.insert("table", "android123", cv); //插入數據

String args[] = {id};
ContentValues cv2= new ContentValues();

cv2.put("android123", id);
db.delete("table", "android123=?", args); //刪除數據

}

10. ios 從哪些方面去做sqlite 資料庫的優化

先來看看.h文件

#import <Foundation/Foundation.h>
#import <sqlite3.h>

#define kFilename @"testdb.db"
@class sqlTestList;
@interface sqlService : NSObject {
sqlite3 *_database;

}

@property (nonatomic) sqlite3 *_database;
-(BOOL) createTestList:(sqlite3 *)db;//創建資料庫
-(BOOL) insertTestList:(sqlTestList *)insertList;//插入數據
-(BOOL) updateTestList:(sqlTestList *)updateList;//更新數據
-(NSMutableArray*)getTestList;//獲取全部數據
- (BOOL) deleteTestList:(sqlTestList *)deletList;//刪除數據:
- (NSMutableArray*)searchTestList:(NSString*)searchString;//查詢資料庫,searchID為要查詢數據的ID,返回數據為查詢到的數據
@end

@interface sqlTestList : NSObject//重新定義了一個類,專門用於存儲數據
{
int sqlID;
NSString *sqlText;
NSString *sqlname;
}

@property (nonatomic) int sqlID;
@property (nonatomic, retain) NSString *sqlText;
@property (nonatomic, retain) NSString *sqlname;

@end

再來看看.m文件

//
// sqlService.m
// SQLite3Test
//
// Created by fengxiao on 11-11-28.
// Copyright 2011 __MyCompanyName__. All rights reserved.
//

#import "sqlService.h"

@implementation sqlService

@synthesize _database;

- (id)init
{
return self;
}

- (void)dealloc
{
[super dealloc];
}

//獲取document目錄並返回資料庫目錄
- (NSString *)dataFilePath{

NSArray *paths = (NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSLog(@"=======%@",documentsDirectory);
return [documentsDirectory :@"data.db"];//這里很神奇,可以定義成任何類型的文件,也可以不定義成.db文件,任何格式都行,定義成.sb文件都行,達到了很好的數據隱秘性

}

//創建,打開資料庫
- (BOOL)openDB {

//獲取資料庫路徑
NSString *path = [self dataFilePath];
//文件管理器
NSFileManager *fileManager = [NSFileManager defaultManager];
//判斷資料庫是否存在
BOOL find = [fileManager fileExistsAtPath:path];

//如果資料庫存在,則用sqlite3_open直接打開(不要擔心,如果資料庫不存在sqlite3_open會自動創建)
if (find) {

NSLog(@"Database file have already existed.");

//打開資料庫,這里的[path UTF8String]是將NSString轉換為C字元串,因為SQLite3是採用可移植的C(而不是
//Objective-C)編寫的,它不知道什麼是NSString.
if(sqlite3_open([path UTF8String], &_database) != SQLITE_OK) {

//如果打開資料庫失敗則關閉資料庫
sqlite3_close(self._database);
NSLog(@"Error: open database file.");
return NO;
}

//創建一個新表
[self createTestList:self._database];

return YES;
}
//如果發現資料庫不存在則利用sqlite3_open創建資料庫(上面已經提到過),與上面相同,路徑要轉換為C字元串
if(sqlite3_open([path UTF8String], &_database) == SQLITE_OK) {

//創建一個新表
[self createTestList:self._database];
return YES;
} else {
//如果創建並打開資料庫失敗則關閉資料庫
sqlite3_close(self._database);
NSLog(@"Error: open database file.");
return NO;
}
return NO;
}

//創建表
- (BOOL) createTestList:(sqlite3*)db {

//這句是大家熟悉的SQL語句
char *sql = "create table if not exists testTable(ID INTEGER PRIMARY KEY AUTOINCREMENT, testID int,testValue text,testName text)";// testID是列名,int 是數據類型,testValue是列名,text是數據類型,是字元串類型

sqlite3_stmt *statement;
//sqlite3_prepare_v2 介面把一條SQL語句解析到statement結構里去. 使用該介面訪問資料庫是當前比較好的的一種方法
NSInteger sqlReturn = sqlite3_prepare_v2(_database, sql, -1, &statement, nil);
//第一個參數跟前面一樣,是個sqlite3 * 類型變數,
//第二個參數是一個 sql 語句。
//第三個參數我寫的是-1,這個參數含義是前面 sql 語句的長度。如果小於0,sqlite會自動計算它的長度(把sql語句當成以\0結尾的字元串)。
//第四個參數是sqlite3_stmt 的指針的指針。解析以後的sql語句就放在這個結構里。
//第五個參數是錯誤信息提示,一般不用,為nil就可以了。
//如果這個函數執行成功(返回值是 SQLITE_OK 且 statement 不為NULL ),那麼下面就可以開始插入二進制數據。

//如果SQL語句解析出錯的話程序返回
if(sqlReturn != SQLITE_OK) {
NSLog(@"Error: failed to prepare statement:create test table");
return NO;
}

//執行SQL語句
int success = sqlite3_step(statement);
//釋放sqlite3_stmt
sqlite3_finalize(statement);

//執行SQL語句失敗
if ( success != SQLITE_DONE) {
NSLog(@"Error: failed to dehydrate:create table test");
return NO;
}
NSLog(@"Create table 'testTable' successed.");
return YES;
}

//插入數據
-(BOOL) insertTestList:(sqlTestList *)insertList {

//先判斷資料庫是否打開
if ([self openDB]) {

sqlite3_stmt *statement;

//這個 sql 語句特別之處在於 values 裡面有個? 號。在sqlite3_prepare函數里,?號表示一個未定的值,它的值等下才插入。
static char *sql = "INSERT INTO testTable(testID, testValue,testName) VALUES(?, ?, ?)";

int success2 = sqlite3_prepare_v2(_database, sql, -1, &statement, NULL);
if (success2 != SQLITE_OK) {
NSLog(@"Error: failed to insert:testTable");
sqlite3_close(_database);
return NO;
}

//這里的數字1,2,3代表上面的第幾個問號,這里將三個值綁定到三個綁定變數
sqlite3_bind_int(statement, 1, insertList.sqlID);
sqlite3_bind_text(statement, 2, [insertList.sqlText UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(statement, 3, [insertList.sqlname UTF8String], -1, SQLITE_TRANSIENT);

//執行插入語句
success2 = sqlite3_step(statement);
//釋放statement
sqlite3_finalize(statement);

//如果插入失敗
if (success2 == SQLITE_ERROR) {
NSLog(@"Error: failed to insert into the database with message.");
//關閉資料庫
sqlite3_close(_database);
return NO;
}
//關閉資料庫
sqlite3_close(_database);
return YES;
}
return NO;
}

//獲取數據
- (NSMutableArray*)getTestList{

NSMutableArray *array = [NSMutableArray arrayWithCapacity:10];
//判斷資料庫是否打開
if ([self openDB]) {

sqlite3_stmt *statement = nil;
//sql語句
char *sql = "SELECT testID, testValue ,testName FROM testTable";//從testTable這個表中獲取 testID, testValue ,testName,若獲取全部的話可以用*代替testID, testValue ,testName。

if (sqlite3_prepare_v2(_database, sql, -1, &statement, NULL) != SQLITE_OK) {
NSLog(@"Error: failed to prepare statement with message:get testValue.");
return NO;
}
else {
//查詢結果集中一條一條的遍歷所有的記錄,這里的數字對應的是列值,注意這里的列值,跟上面sqlite3_bind_text綁定的列值不一樣!一定要分開,不然會crash,只有這一處的列號不同,注意!
while (sqlite3_step(statement) == SQLITE_ROW) {
sqlTestList* sqlList = [[sqlTestList alloc] init] ;
sqlList.sqlID = sqlite3_column_int(statement,0);
char* strText = (char*)sqlite3_column_text(statement, 1);
sqlList.sqlText = [NSString stringWithUTF8String:strText];
char *strName = (char*)sqlite3_column_text(statement, 2);
sqlList.sqlname = [NSString stringWithUTF8String:strName];
[array addObject:sqlList];
[sqlList release];
}
}
sqlite3_finalize(statement);
sqlite3_close(_database);
}

return [array retain];//定義了自動釋放的NSArray,這樣不是個好辦法,會造成內存泄露,建議大家定義局部的數組,再賦給屬性變數。
}

//更新數據
-(BOOL) updateTestList:(sqlTestList *)updateList{

if ([self openDB]) {
sqlite3_stmt *statement;//這相當一個容器,放轉化OK的sql語句
//組織SQL語句
char *sql = "update testTable set testValue = ? and testName = ? WHERE testID = ?";

//將SQL語句放入sqlite3_stmt中
int success = sqlite3_prepare_v2(_database, sql, -1, &statement, NULL);
if (success != SQLITE_OK) {
NSLog(@"Error: failed to update:testTable");
sqlite3_close(_database);
return NO;
}

附上出處鏈接:http://www.cnblogs.com/xiaozhu/archive/2012/12/07/2808170.html

熱點內容
腳本函數未定義 發布:2025-01-12 09:39:44 瀏覽:634
頁面PHP 發布:2025-01-12 09:38:07 瀏覽:200
郵政銀行打電話登錄密碼是什麼 發布:2025-01-12 09:37:27 瀏覽:563
linuxroot遠程登錄 發布:2025-01-12 09:37:26 瀏覽:302
怎麼算伺服器ip 發布:2025-01-12 08:59:19 瀏覽:854
安卓與ios哪個適合做主力機 發布:2025-01-12 08:54:11 瀏覽:341
微軟怎麼關閉配置更新 發布:2025-01-12 08:34:23 瀏覽:316
wifi的有限的訪問許可權 發布:2025-01-12 08:34:14 瀏覽:610
cftp文件重命名 發布:2025-01-12 08:33:27 瀏覽:882
https的加密演算法 發布:2025-01-12 08:19:15 瀏覽:654