c语言实现Sqlite3的创建db和增删改查db操作

原创
2019/10/14 11:28
阅读数 2.6K

SQLite,是一款轻型的数据库,而且目前已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中使用广泛,现在准备学习一下sqlite3的使用方法并写一个测试demo,后面在项目智能家居网关中用起来,代替文本数据库(增删改查非常不方便)

用法介绍

创建或者打开一个存在的数据库db文件

 sqlite3 *db = 0;
 OpenSqlite("./db/company.db",&db);
//创建或者打开已存在的数据库文件
int OpenSqlite(const char *DbFilePath,sqlite3 **db)
{
    int ret = 0;
    ret = sqlite3_open(DbFilePath,db);
    if(ret != SQLITE_OK){
        fprintf(stderr,"Cannot open db: %s\n",sqlite3_errmsg(*db));
        return -1;
    }
    DEBUG_INFO("Open database\n");
    return 0;
}

创建数据库表格

/* 创建数据库表格 */
   sql = "CREATE TABLE COMPANY("  \
         "ID INT PRIMARY KEY     NOT NULL," \
         "NAME           TEXT    NOT NULL," \
         "AGE            INT     NOT NULL," \
         "ADDRESS        CHAR(50)," \
         "SALARY         REAL );";
   CreateSqliteTable(db, sql);
//创建一个数据库表
int CreateSqliteTable(sqlite3 *db,const char *sql)
{
   if (NULL == db)
        return -1;
   
   int ret = 0;
   char *zErrMsg = 0;
   ret = sqlite3_exec(db, sql, mycallback, 0, &zErrMsg);
   if(ret != SQLITE_OK ){
   fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
      return -1;
   }else{
      fprintf(stdout, "Table created successfully\n");
      return 0;
   }
}

在数据库表中插入数据

/* 在数据库表中插入数据 */
   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 );";
   InsertSqliteata(db,sql);
//sqlite表插入数据
int InsertSqliteata(sqlite3 *db,const char *sql)
{
   if (NULL == db)
        return -1;
   
   int ret = 0;
   char *zErrMsg = NULL;
   ret = sqlite3_exec(db, sql, mycallback, 0, &zErrMsg);
   if( ret != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
      return -1;
   }else{
      fprintf(stdout, "Records created successfully\n");
      return 0;
   }
}

查询数据库

/*查询整个表格数据*/
   //sql = "select * from COMPANY";
   /*查询id=1的数据*/
   sql = "SELECT * FROM COMPANY WHERE ID=1";
   /*查询id=1,或者id=2的数据*/
   //sql = "SELECT * FROM COMPANY WHERE ID=1 OR ID=2";
   DEBUG_INFO("sql=%s",sql);
   LookUpSqlite(db,sql);
//查询数据库
int LookUpSqlite(sqlite3 *db,const char *sql)
{
   if(NULL == db)
     return -1;
   
   int ret = 0;
   char *zErrMsg = NULL;
   const char* data = "Callback function called";
   ret = sqlite3_exec(db, sql, mycallback, (void*)data, &zErrMsg);
   if( ret != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
      return -1;
   }else{
      fprintf(stdout, "Operation done successfully\n");
      return 0;
   }
}

删除表中某条数据

//删除表中某条数据
   sql = "DELETE from COMPANY WHERE ID = 1";
   DeleteSqlite(db,sql);
//删除数据库表中某条数据
int DeleteSqlite(sqlite3 *db,const char *sql)
{
    if (NULL == db)
        return -1;
   
   int ret = 0;
   char *zErrMsg = NULL;
   ret = sqlite3_exec(db, sql, mycallback, 0, &zErrMsg);
   if( ret != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
      return -1;
   }else{
      fprintf(stdout, "Records created successfully\n");
      return 0;
   }
}

修改数据库表中某个字段的值

	sql = "UPDATE COMPANY SET SALARY = 888888888.00 WHERE ID = 2;";
   UpdateSqlite(db,sql);

//sqlite修改操作
int UpdateSqlite(sqlite3 *db,const char *sql)
{
  if (NULL == db)
        return -1;
   
   int ret = 0;
   char *zErrMsg = NULL;
   ret = sqlite3_exec(db, sql, mycallback, 0, &zErrMsg);
   if( ret != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
      return -1;
   }else{
      fprintf(stdout, "Records created successfully\n");
      return 0;
   }  
}

命令行用sqlite3查看数据库修改情况

打开数据库文件

查看数据库文件中有哪些表

查看数据表中都有哪些字段和值

整个工程github链接

https://github.com/jorinzou/sqlite3.git

参考

https://www.runoob.com/sqlite/sqlite-update.html

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部