鍍金池/ 教程/ 數(shù)據(jù)庫(kù)/ SQLite – C/C++
SQLite Having 子句
SQLite 運(yùn)算符
SQLite 注入
SQLite Delete 語(yǔ)句
SQLite – Python
SQLite 數(shù)據(jù)類(lèi)型
SQLite 簡(jiǎn)介
SQLite 創(chuàng)建數(shù)據(jù)庫(kù)
SQLite Vacuum
SQLite Group By
SQLite 日期 & 時(shí)間
SQLite AND/OR 運(yùn)算符
SQLite 刪除表
SQLite Distinct
SQLite Alter 命令
SQLite PRAGMA
SQLite 約束
SQLite 創(chuàng)建表
SQLite Like 子句
SQLite Limit 子句
SQLite Autoincrement
SQLite 子查詢(xún)
SQLite – C/C++
SQLite – PHP
SQLite 命令
SQLite Order By
SQLite Select 語(yǔ)句
SQLite Unions 子句
SQLite – Perl
SQLite – Java
SQLite 別名
SQLite 常用函數(shù)
SQLite Explain（解釋?zhuān)?/span>
SQLite NULL 值
SQLite Glob 子句
SQLite 表達(dá)式
SQLite 視圖
SQLite Where 子句
SQLite Truncate Table
SQLite 索引
SQLite Insert 語(yǔ)句
SQLite 安裝
SQLite Indexed By
SQLite 分離數(shù)據(jù)庫(kù)
SQLite 觸發(fā)器
SQLite 語(yǔ)法
SQLite Joins
SQLite Update 語(yǔ)句
SQLite 附加數(shù)據(jù)庫(kù)
SQLite 事務(wù)

SQLite – C/C++

安裝

在 C/C++ 程序中使用 SQLite 之前，我們需要確保機(jī)器上已經(jīng)有 SQLite 庫(kù)。可以查看 SQLite 安裝章節(jié)了解安裝過(guò)程。

C/C++ 接口 API

以下是重要的 C&C++ / SQLite 接口程序，可以滿足您在 C/C++ 程序中使用 SQLite 數(shù)據(jù)庫(kù)的需求。如果您需要了解更多細(xì)節(jié)，請(qǐng)查看 SQLite 官方文檔。

序號(hào)API & 描述
1sqlite3_open(const char *filename, sqlite3 *ppDb)

該例程打開(kāi)一個(gè)指向 SQLite 數(shù)據(jù)庫(kù)文件的連接，返回一個(gè)用于其他 SQLite 程序的數(shù)據(jù)庫(kù)連接對(duì)象。

如果 filename 參數(shù)是 NULL 或 ':memory:'，那么 sqlite3_open() 將會(huì)在 RAM 中創(chuàng)建一個(gè)內(nèi)存數(shù)據(jù)庫(kù)，這只會(huì)在 session 的有效時(shí)間內(nèi)持續(xù)。

如果文件名 filename 不為 NULL，那么 sqlite3_open() 將使用這個(gè)參數(shù)值嘗試打開(kāi)數(shù)據(jù)庫(kù)文件。如果該名稱(chēng)的文件不存在，sqlite3_open() 將創(chuàng)建一個(gè)新的命名為該名稱(chēng)的數(shù)據(jù)庫(kù)文件并打開(kāi)。

2sqlite3_exec(sqlite3, const char sql, sqlite_callback, void data, char *errmsg)

該例程提供了一個(gè)執(zhí)行 SQL 命令的快捷方式，SQL 命令由 sql 參數(shù)提供，可以由多個(gè) SQL 命令組成。

在這里，第一個(gè)參數(shù) sqlite3 是打開(kāi)的數(shù)據(jù)庫(kù)對(duì)象，sqlite_callback 是一個(gè)回調(diào)，data 作為其第一個(gè)參數(shù)，errmsg 將被返回用來(lái)獲取程序生成的任何錯(cuò)誤。

sqlite3_exec() 程序解析并執(zhí)行由 sql 參數(shù)所給的每個(gè)命令，直到字符串結(jié)束或者遇到錯(cuò)誤為止。

3sqlite3_close(sqlite3)

該例程關(guān)閉之前調(diào)用 sqlite3_open() 打開(kāi)的數(shù)據(jù)庫(kù)連接。所有與連接相關(guān)的語(yǔ)句都應(yīng)在連接關(guān)閉之前完成。

如果還有查詢(xún)沒(méi)有完成，sqlite3_close() 將返回 SQLITE_BUSY 禁止關(guān)閉的錯(cuò)誤消息。

連接數(shù)據(jù)庫(kù)

下面的 C 代碼段顯示了如何連接到一個(gè)現(xiàn)有的數(shù)據(jù)庫(kù)。如果數(shù)據(jù)庫(kù)不存在，那么它就會(huì)被創(chuàng)建，最后將返回一個(gè)數(shù)據(jù)庫(kù)對(duì)象。

    #include 
    #include 

    int main(int argc, char* argv[])
    {
       sqlite3 *db;
       char *zErrMsg = 0;
       int rc;

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

       if( rc ){
          fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
          exit(0);
       }else{
          fprintf(stderr, "Opened database successfullyn");
       }
       sqlite3_close(db);
    }

現(xiàn)在，讓我們來(lái)編譯和運(yùn)行上面的程序，在當(dāng)前目錄中創(chuàng)建我們的數(shù)據(jù)庫(kù) test.db。您可以根據(jù)需要改變路徑。

    $gcc test.c -l sqlite3
    $./a.out
    Opened database successfully

如果要使用 C++ 源代碼，可以按照下列所示編譯代碼：

    $g++ test.c -l sqlite3

在這里，把我們的程序鏈接上 sqlite3 庫(kù)，以便向 C 程序提供必要的函數(shù)。這將在您的目錄下創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)文件 test.db，您將得到如下結(jié)果：

    -rwxr-xr-x. 1 root root 7383 May  8 02:06 a.out
    -rw-r--r--. 1 root root  323 May  8 02:05 test.c
    -rw-r--r--. 1 root root    0 May  8 02:06 test.db

創(chuàng)建表

下面的 C 代碼段將用于在先前創(chuàng)建的數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)表：

    #include 
    #include 
    #include 

    static int callback(void *NotUsed, int argc, char **argv, char **azColName){
       int i;
       for(i=0; i<argc; i++){
          printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
       }
       printf("n");
       return 0;
    }

    int main(int argc, char* argv[])
    {
       sqlite3 *db;
       char *zErrMsg = 0;
       int  rc;
       char *sql;

       /* Open database */
       rc = sqlite3_open("test.db", &db);
       if( rc ){
          fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
          exit(0);
       }else{
          fprintf(stdout, "Opened database successfullyn");
       }

       /* Create SQL statement */
       sql = "CREATE TABLE COMPANY("
             "ID INT PRIMARY KEY     NOT NULL,"
             "NAME           TEXT    NOT NULL,"
             "AGE            INT     NOT NULL,"
             "ADDRESS        CHAR(50),"
             "SALARY         REAL );";

       /* Execute SQL statement */
       rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
       if( rc != SQLITE_OK ){
       fprintf(stderr, "SQL error: %sn", zErrMsg);
          sqlite3_free(zErrMsg);
       }else{
          fprintf(stdout, "Table created successfullyn");
       }
       sqlite3_close(db);
       return 0;
    }

上述程序編譯和執(zhí)行時(shí)，它會(huì)在 test.db 文件中創(chuàng)建 COMPANY 表，最終文件列表如下所示：

    -rwxr-xr-x. 1 root root 9567 May  8 02:31 a.out
    -rw-r--r--. 1 root root 1207 May  8 02:31 test.c
    -rw-r--r--. 1 root root 3072 May  8 02:31 test.db

INSERT 操作

下面的 C 代碼段顯示了如何在上面創(chuàng)建的 COMPANY 表中創(chuàng)建記錄：

    #include 
    #include 
    #include 

    static int callback(void *NotUsed, int argc, char **argv, char **azColName){
       int i;
       for(i=0; i<argc; i++){
          printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
       }
       printf("n");
       return 0;
    }

    int main(int argc, char* argv[])
    {
       sqlite3 *db;
       char *zErrMsg = 0;
       int rc;
       char *sql;

       /* Open database */
       rc = sqlite3_open("test.db", &db);
       if( rc ){
          fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
          exit(0);
       }else{
          fprintf(stderr, "Opened database successfullyn");
       }

       /* Create SQL statement */
       sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
             "VALUES (1, 'Paul', 32, 'California', 20000.00 ); "
             "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
             "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "
             "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)"
             "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"
             "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)"
             "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

       /* Execute SQL statement */
       rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
       if( rc != SQLITE_OK ){
          fprintf(stderr, "SQL error: %sn", zErrMsg);
          sqlite3_free(zErrMsg);
       }else{
          fprintf(stdout, "Records created successfullyn");
       }
       sqlite3_close(db);
       return 0;
    }

上述程序編譯和執(zhí)行時(shí)，它會(huì)在 COMPANY 表中創(chuàng)建給定記錄，并會(huì)顯示以下兩行：

    Opened database successfully
    Records created successfully

SELECT 操作

在我們開(kāi)始講解獲取記錄的實(shí)例之前，讓我們先了解下回調(diào)函數(shù)的一些細(xì)節(jié)，這將在我們的實(shí)例使用到。這個(gè)回調(diào)提供了一個(gè)從 SELECT 語(yǔ)句獲得結(jié)果的方式。它聲明如下：

    typedef int (*sqlite3_callback)(
    void*,    /* Data provided in the 4th argument of sqlite3_exec() */
    int,      /* The number of columns in row */
    char**,   /* An array of strings representing fields in the row */
    char**    /* An array of strings representing column names */
    );

如果上面的回調(diào)在 sqlite_exec() 程序中作為第三個(gè)參數(shù)，那么 SQLite 將為 SQL 參數(shù)內(nèi)執(zhí)行的每個(gè) SELECT 語(yǔ)句中處理的每個(gè)記錄調(diào)用這個(gè)回調(diào)函數(shù)。

下面的 C 代碼段顯示了如何從前面創(chuàng)建的 COMPANY 表中獲取并顯示記錄：

    #include 
    #include 
    #include 

    static int callback(void *data, int argc, char **argv, char **azColName){
       int i;
       fprintf(stderr, "%s: ", (const char*)data);
       for(i=0; i<argc; i++){
          printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
       }
       printf("n");
       return 0;
    }

    int main(int argc, char* argv[])
    {
       sqlite3 *db;
       char *zErrMsg = 0;
       int rc;
       char *sql;
       const char* data = "Callback function called";

       /* Open database */
       rc = sqlite3_open("test.db", &db);
       if( rc ){
          fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
          exit(0);
       }else{
          fprintf(stderr, "Opened database successfullyn");
       }

       /* Create SQL statement */
       sql = "SELECT * from COMPANY";

       /* Execute SQL statement */
       rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
       if( rc != SQLITE_OK ){
          fprintf(stderr, "SQL error: %sn", zErrMsg);
          sqlite3_free(zErrMsg);
       }else{
          fprintf(stdout, "Operation done successfullyn");
       }
       sqlite3_close(db);
       return 0;
    }

上述程序編譯和執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：

    Opened database successfully
    Callback function called: ID = 1
    NAME = Paul
    AGE = 32
    ADDRESS = California
    SALARY = 20000.0

    Callback function called: ID = 2
    NAME = Allen
    AGE = 25
    ADDRESS = Texas
    SALARY = 15000.0

    Callback function called: ID = 3
    NAME = Teddy
    AGE = 23
    ADDRESS = Norway
    SALARY = 20000.0

    Callback function called: ID = 4
    NAME = Mark
    AGE = 25
    ADDRESS = Rich-Mond
    SALARY = 65000.0

    Operation done successfully

UPDATE 操作

下面的 C 代碼段顯示了如何使用 UPDATE 語(yǔ)句來(lái)更新任何記錄，然后從 COMPANY 表中獲取并顯示更新的記錄：

    #include 
    #include 
    #include 

    static int callback(void *data, int argc, char **argv, char **azColName){
       int i;
       fprintf(stderr, "%s: ", (const char*)data);
       for(i=0; i<argc; i++){
          printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
       }
       printf("n");
       return 0;
    }

    int main(int argc, char* argv[])
    {
       sqlite3 *db;
       char *zErrMsg = 0;
       int rc;
       char *sql;
       const char* data = "Callback function called";

       /* Open database */
       rc = sqlite3_open("test.db", &db);
       if( rc ){
          fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
          exit(0);
       }else{
          fprintf(stderr, "Opened database successfullyn");
       }

       /* Create merged SQL statement */
       sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; "
             "SELECT * from COMPANY";

       /* Execute SQL statement */
       rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
       if( rc != SQLITE_OK ){
          fprintf(stderr, "SQL error: %sn", zErrMsg);
          sqlite3_free(zErrMsg);
       }else{
          fprintf(stdout, "Operation done successfullyn");
       }
       sqlite3_close(db);
       return 0;
    }

上述程序編譯和執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：

    Opened database successfully
    Callback function called: ID = 1
    NAME = Paul
    AGE = 32
    ADDRESS = California
    SALARY = 25000.0

    Callback function called: ID = 2
    NAME = Allen
    AGE = 25
    ADDRESS = Texas
    SALARY = 15000.0

    Callback function called: ID = 3
    NAME = Teddy
    AGE = 23
    ADDRESS = Norway
    SALARY = 20000.0

    Callback function called: ID = 4
    NAME = Mark
    AGE = 25
    ADDRESS = Rich-Mond
    SALARY = 65000.0

    Operation done successfully

DELETE 操作

下面的 C 代碼段顯示了如何使用 DELETE 語(yǔ)句刪除任何記錄，然后從 COMPANY 表中獲取并顯示剩余的記錄：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   if( rc ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create merged SQL statement */
   sql = "DELETE from COMPANY where ID=2; " \
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }else{
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

上述程序編譯和執(zhí)行時(shí)，它會(huì)產(chǎn)生以下結(jié)果：

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully
上一篇：SQLite – PHP下一篇：SQLite 表達(dá)式