sqlite3的簡單用法

安裝sqlite3

#在終端輸入sqlite3，查看是否安裝sqlite3，沒有的話先安裝sqlite3
sudo apt-get install sqlite3
sudo apt-get install libsqlite3-dev

基本用法

• 打開數據庫 sqlite3 test.db
• 查看表 .tables
  可以使用圖形化工具navicat來創建的查看數據庫

sqlite3 C/C++接口

//打開數據庫
sqlite3_open(const char *filename, sqlite3 **ppDb);
//解析并執行由 sql 參數所給的每個命令，直到字符串結束或者遇到錯誤為止
sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg);
//關閉數據庫
sqlite3_close(sqlite3*);

Example

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

const char* data = "Callback function called";
//插入數據回調函數
static int insert_callback(void *NotUsed, int argc, char **argv, char **azColName){
   int i;
   for(i=0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}
//讀取數據回調函數
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++){
        //將讀取到的數據轉化為浮點型
        t_val = atof(argv[2]);
        h_val = atof(argv[3]);
        l_val = atof(argv[4]);
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(void)
{
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    //打開數據庫
    rc = sqlite3_open("test.db", &db);
    if(!rc){
        fprintf(stdout, "open database successfully\n");
    }
    char *sql_insert;
    //向數據庫插入一條數據
    sql_insert = "insert into sensor(sensor_id, temperature, humidity, illumination) values('syn05', 0.4, 2.9, 3000);";
    rc = sqlite3_exec(db, sql_insert, insert_callback, 0, &zErrMsg);
    if(rc != SQLITE_OK){
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    else{
        fprintf(stdout, "insert data successfully\n");
    }
    //從數據庫sensor表中選取id為syn05的最新數據
    char *sql;
    sql = "select * from sensor where sensor_id = 'syn05' order by id desc limit 0,1;";
    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");
    }
}

編譯

需要在編譯選項上加入-lsqlite3
如果使用的編輯器，比如codeblocks，按如下位置配置

出現fatal error: sqlite3.h: No such file or directory，安裝libsqlite3-dev
sudo apt-get install libsqlite3-dev

Navicat的使用

下載Navicat：navicat112_mariadb_cs_x64.tar.gz
點擊” ./start_navicat”安裝出現界面便面為亂碼
解決辦法：打開start_navicat文件， export LANG=”en_US.UTF-8” 改為 export LANG=”zh_CN.UTF-8”。

參考：
1. http://www.runoob.com/sqlite/sqlite-c-cpp.html
2. http://blog.csdn.net/xc889078/article/details/8793362
3. https://www.cnblogs.com/qkhh/archive/2008/08/30/1280175.html
4. http://blog.csdn.net/u010312436/article/details/51541231
5. https://www.cnblogs.com/miaoer/p/6270473.html