sqlite3的简单用法

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