Sqlite簡單介紹與一些常用的例子

1:常用介面

  個人比較喜歡sqlite, 使用最方便，唯一的準備工作是下載250K的源；而且作者很熱心，有問必答。

以下示範一下使用sqlite的步驟，先建立一個資料庫，然後查詢其中的內容。2個重要結構體和5個主要函數：

sqlite3               *pdb, 資料庫控制代碼，跟檔案控制代碼FILE很類似

sqlite3_stmt      *stmt, 這個相當於ODBC的Command對象，用於儲存編譯好的SQL語句

 

sqlite3_open(),   開啟資料庫

sqlite3_exec(),   執行非查詢的sql語句

sqlite3_prepare(), 準備sql語句，執行select語句或者要使用parameter bind時，用這個函數（封裝了sqlite3_exec）.

Sqlite3_step(), 在調用sqlite3_prepare後，使用這個函數在記錄集中移動。

Sqlite3_close(), 關閉資料庫檔案

 

還有一系列的函數，用於從屬記錄集欄位中擷取資料，如

sqlite3_column_text(), 取text類型的資料。

sqlite3_column_blob（），取blob類型的資料

sqlite3_column_int(), 取int類型的資料

…

 2:sqlite資料類型介紹

     在進行資料庫Sql操作之前，首先有個問題需要說明，就是Sqlite的資料類型，和其他的資料庫不同，Sqlite支援的資料類型有他自己的特色，這個特色有時會被認為是一個潛在的缺點，但是這個問題並不在我們的討論範圍之內。
大多數的資料庫在資料類型上都有嚴格的限制，在建立表的時候，每一列都必須制定一個資料類型，只有符合該資料類型的資料可以被儲存在這一列當中。而在Sqlite 2.X中，資料類型這個屬性只屬於資料本生，而不和資料被存在哪一列有關，也就是說資料的類型並不受資料列限制（有一個例外：INTEGER PRIMARY KEY，該列只能存整型資料）。
但是當Sqlite進入到3.0版本的時候，這個問題似乎又有了新的答案，Sqlite的開發人員開始限制這種無類型的使用，在3.0版本當中，每一列開始擁有自己的類型，並且在資料存入該列的時候，資料庫會試圖把資料的類型向該類型轉換，然後以轉換之後的類型儲存。當然，如果轉換被認為是不可行的，Sqlite仍然會儲存這個資料，就像他的前任版本一樣。
舉個例子，如果你企圖向一個INTEGER類型的列中插入一個字串，Sqlite會檢查這個字串是否有整型資料的特徵, 如果有而且可以被資料庫所識別，那麼該字串會被轉換成整型再儲存，如果不行，則還是作為整型儲存。

總的來說，所有存在Sqlite 3.0版本當中的資料都擁有以下之一的資料類型：
空（NULL）：該值為空白
整型（INTEGEER）：有符號整數，按大小被儲存成1,2,3,4,6或8位元組。
實數（REAL）：浮點數，以8位元組指數形式儲存。
文本（TEXT）：字串，以資料庫編碼方式儲存（UTF-8, UTF-16BE 或者 UTF-16-LE）。
BLOB：BLOB資料不做任何轉換，以輸入形式儲存。

ps: 在關聯式資料庫中，CLOB和BLOB類型被用來存放大對象。BOLB表示二進位大對象，這種資料類型通過用來儲存圖片，圖象，視頻等。CLOB表示字元大對象，能夠存放大量基於字元的資料。

對應的，對於資料列，同樣有以下的資料類型：

TEXT
NUMERIC
INTEGER
REAL
NONE
資料列的屬性的作用是確定對插入的資料的轉換方向：

TEXT 將資料向文本進行轉換，對應的資料類型為NULL，TEXT 或 BLOB
NUMERIC 將資料向數字進行轉換，對應的資料類型可能為所有的五類資料，當試圖存入文本 時將執行向整型或浮點類型的轉換（視具體的數值而定），轉換若不可行，則保留文本類型儲存，NULL或BLOB不做變化
INTEGER 將資料向整型轉換，類似於NUMERIC，不同的是沒有浮點標誌的浮點數將轉換為整型儲存
REAL 將資料向浮點數類型轉換，類似於NUMERIC，不同的是整數將轉換為浮點數儲存
NULL 不做任何轉換的資料列類型
 

執行個體代碼如下,

附件工程可直接編譯，例子使用了blob資料類型。

#include "sqlite3.h"                                //包含一個標頭檔就可以使用所以sqlite的介面了

#include "stdlib.h"

#include "stdio.h"

#include "string.h"

 

#pragma comment(lib, "sqlite.lib")           //我把sqlite編譯成了一個靜態lib檔案。

 

void       createdb();

void       querydb();

 

int         main()

{

            createdb();

            querydb();

 

            return 0;

}

 

void       createdb()

{

            int                     ret;

            sqlite3               *pdb = 0;

            sqlite3_stmt      *stmt = 0;

            char                  *error = 0;

            char                  *sql = "insert into table1 values('value11',:aaa)";

            int                     index;

            static void          *value = "asdfadsfasdfjasdfjaksdfaskjdfakdsfaksfja";

 

            ret = sqlite3_open("db1.sdb", &pdb);                    //開啟資料庫，跟開啟文字檔一樣

            if( ret != SQLITE_OK )

                        return;

            ret = sqlite3_exec(pdb, "create table table1(col1 char(20), col2 BLOB)", 0,0, &error );

            if( ret != SQLITE_OK )

                        return;

 

            ret = sqlite3_prepare(pdb, sql,strlen(sql), &stmt, &error);

            if( ret != SQLITE_OK )

                        return;

 

            index = sqlite3_bind_parameter_index(stmt, ":aaa");

 

            ret = sqlite3_bind_blob(stmt, index, value, strlen(value), SQLITE_STATIC);

            if( ret != SQLITE_OK )

                        return;

 

            ret = sqlite3_step(stmt);

           

            if( ret != SQLITE_DONE )

                        return;

 

            sqlite3_close(pdb);        

}

 

void       querydb()

{

            int                     ret;

            sqlite3   *pdb = 0;

            sqlite3_stmt      *pstmt = 0;

            char      *error = 0;

            char      *sql = "select * from table1";

            int                     len;

            int                     i;

            char      *name;

            void       *value;

 

            ret = sqlite3_open("db1.sdb", &pdb);

            if( ret != SQLITE_OK )

                        return;

 

            ret = sqlite3_prepare(pdb, sql, strlen(sql), &pstmt, &error);

            if( ret != SQLITE_OK )

                        return;

 

            while( 1 )

            {

                        ret = sqlite3_step(pstmt);

                        if( ret != SQLITE_ROW )

                                    break;

 

                        name = sqlite3_column_text(pstmt, 0);

                        value = sqlite3_column_blob(pstmt, 1);

                        len = sqlite3_column_bytes(pstmt,1 );

            }

}

 

 

執行個體二:SQLite中如何用api操作blob類型的欄位

 

   在實際的編程開發當中我們經常要處理一些大容量位元據的儲存，片或者音樂等等。對於這些位元據（blob欄位）我們不能像處理普通的文本那樣簡單的插入或者查詢，為此SQLite提供了一組函數來處理這種BLOB欄位類型。下面的代碼示範了如何使用這些API函數。

 

首先我們要建立一個資料庫：

sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob);", 0, 0, &zErrMsg);

 

//由於mmmm.rar是一個二進位檔案，所以要在使用insert語句時先用？號代替

     sqlite3_prepare(db, "insert into list values ('mmmm.rar',?);", -1, &stat, 0);

    

     FILE *fp;

     long filesize = 0;

     char * ffile;

 

     fp = fopen("mmmm.rar", "rb");

 

     if(fp != NULL)

     {

         //計算檔案的大小

         fseek(fp, 0, SEEK_END);

         filesize = ftell(fp);

         fseek(fp, 0, SEEK_SET);

 

         //讀取檔案

         ffile = new char[filesize+1];

         size_t sz = fread(ffile, sizeof(char), filesize+1, fp);

             

         fclose(fp);

     }

 

     //將檔案資料繫結到insert語句中，替換“？”部分

     sqlite3_bind_blob(stat, 1, ffile, filesize, NULL);

    

//執行綁定之後的SQL語句

     sqlite3_step(stat);

這時資料庫當中已經有了一條包含BLOB欄位的資料。接下來我們要讀取這條資料：

    

     //選取該條資料

     sqlite3_prepare(db, "select * from list;", -1, &stat, 0);

     sqlite3_step(stat);

//得到紀錄中的BLOB欄位

const void * test = sqlite3_column_blob(stat, 1);

//得到欄位中資料的長度

     int size = sqlite3_column_bytes(stat, 1);

    

     //拷貝該欄位

     sprintf(buffer2, "%s", test);

此時可以將buffer2寫入到檔案當中，至此BLOB資料處理完畢。

 

執行個體三:sqlite 中用blob儲存圖片和取出圖片

 

#include<iostream>
#include<string>
#include<sqlite3.h>

using namespace std;

int main()
{
        sqlite3 *db;
 sqlite3_stmt *stat;
 char *zErrMsg = 0;

 char buffer2[1024]="0";

 sqlite3_open("./MetaInfo.db", &db);
    int result;

     if(result)
      {
         cout<<"Open the database sqlite.db failed"<<endl;
       }
  
     else
          cout<<"Open the database sqlite.db sucessfully"<<endl;

 sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob);", 0, 0, &zErrMsg);
 sqlite3_prepare(db, "insert into list values ('./data/2.bmp',?);", -1, &stat, 0);
 
 FILE *fp;
 long filesize = 0;
 char * ffile;

 fp = fopen("./data/2.bmp", "rb");

 if(fp != NULL)
 {
  fseek(fp, 0, SEEK_END);
  filesize = ftell(fp);
  fseek(fp, 0, SEEK_SET);

  ffile = new char[filesize+1];
  size_t sz = fread(ffile, sizeof(char), filesize+1, fp);
  
  fclose(fp);
 }

 sqlite3_bind_blob(stat, 1, ffile, filesize, NULL);
 sqlite3_step(stat);

 sqlite3_prepare(db, "select * from list;", -1, &stat, 0);
 sqlite3_step(stat);

 const void * test = sqlite3_column_blob(stat, 1);
 int size = sqlite3_column_bytes(stat, 1);

 sprintf(buffer2, "%s", test);

 FILE *fp2;

 fp2 = fopen("outfile.png", "wb");

 if(fp2 != NULL)
 {
  size_t ret = fwrite(test, sizeof(char), size, fp2);
  fclose(fp2);
 }

 delete(ffile);
 sqlite3_finalize(stat);
 sqlite3_close(db);
   return 0;
}