Sqlite的操作(增加,删除,查询,修改)

int OpenDB(sqlite3** db)
{
  int result = sqlite3_open(DBNAME,db);
  #ifdef DEBUG
  if(result!=0)
  {
    printf("Open DataBase Fail ,Result Code:%d \n",result);
  }
  else
  {
    printf("Open DataBase Sucess\n");
  }
  #endif
  return result;
}
int CloseDB(sqlite3* db)
{
  int result =  sqlite3_close(db);
  #ifdef DEBUG
  if(result!=0)
  {
    printf("Close DataBase fail,ResultCode:%d \n",result);
  }
  else
  {
    printf("Close DataBase Success\n");
  }
  #endif
  return result;
}
 
创建表:
 
 

//创建表操作
void CreateTable()
{
  sqlite3 *db;
  sqlite3_stmt *stmt = NULL;
  char * ErrMessage = NULL;
  const char * Error = NULL;
  char * str_CreateTable="Create Table Student ( studentID INTEGER,studentName Text,HeadImage Blob)"; //创建表的SQL语句
  int rc =0;
  if(OpenDB(&db)!=SQLITE_OK)
  {    
    return;
  }
  rc = sqlite3_exec(db,str_CreateTable,0,0,&ErrMessage); //执行
  if(rc!=SQLITE_OK)
  {
    printf("操作数据库失败!ErrorMessge:%s\n",ErrMessage);
    return;
  }
  sqlite3_finalize(stmt);
  CloseDB(db);
}

Insert操作
//Insert操作
void InsertOperation(void)
{
  sqlite3 *db;
  sqlite3_stmt *stmt = NULL;
  char * ErrMessage = NULL;
  const char * Error = NULL;
  char * str_Insert="Insert Into Student ( [studentID] ,[studentName],[HeadImage]) values(1,'Alex',?)"; //SQL语句
  int rc =0;
  if(OpenDB(&db)!=SQLITE_OK)
  {    
    return;
  }
    
  rc = sqlite3_prepare(db,str_Insert,-1,&stmt,&Error);
  if(rc!=SQLITE_OK)
  {
    printf("准备执行插入语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
    return;
  }
  char _headImage[3] = {0x41,0x42,0x43};
    
  sqlite3_bind_blob(stmt,1,&_headImage,3,NULL);   //二进制数据
  rc = sqlite3_step(stmt);
  if(rc!=SQLITE_DONE)
  {
    printf("插入失败！Result Code:%d  ErrorMessage:%s",rc,Error);
    return;
  }
  printf("插入成功!\n");
CloseDB(db);
}
 
Update操作:
//Insert操作
void InsertOperation(void)
{
  sqlite3 *db;
  sqlite3_stmt *stmt = NULL;
  char * ErrMessage = NULL;
  const char * Error = NULL;
  char * str_Insert="Insert Into Student ( [studentID] ,[studentName],[HeadImage]) values(1,'Alex',?)"; //SQL语句
  int rc =0;
  if(OpenDB(&db)!=SQLITE_OK)
  {    
    return;
  }
    
  rc = sqlite3_prepare(db,str_Insert,-1,&stmt,&Error);
  if(rc!=SQLITE_OK)
  {
    printf("准备执行插入语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
    return;
  }
  char _headImage[3] = {0x41,0x42,0x43};
    
  sqlite3_bind_blob(stmt,1,&_headImage,3,NULL);   //二进制数据
  rc = sqlite3_step(stmt);
  if(rc!=SQLITE_DONE)
  {
    printf("插入失败！Result Code:%d  ErrorMessage:%s",rc,Error);
    return;
  }
  printf("插入成功!\n");
CloseDB(db);
}
Delete操作:
void DeleteOperation(void)
{
  sqlite3 *db;
  sqlite3_stmt *stmt = NULL;
  char * ErrMessage = NULL;
  const char * Error = NULL;
  char * str_Update="Delete From Student  where [studentID] =1"; //SQL语句
  int rc =0;
  if(OpenDB(&db)!=SQLITE_OK)
  {    
    return;
  }
    
  rc = sqlite3_prepare(db,str_Update,-1,&stmt,&Error);
  if(rc!=SQLITE_OK)
  {
    printf("准备执行删除语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
    return;
  }
  rc = sqlite3_step(stmt);
  if(rc!=SQLITE_DONE)
  {
    printf("删除失败！Result Code:%d  ErrorMessage:%s",rc,Error);
    return;
  }
  printf("删除成功!\n");
CloseDB(db);
}
Select操作:
void SelectOperation(void)
{
   sqlite3 *db;
  sqlite3_stmt *stmt = NULL;
  char * ErrMessage = NULL;
  const char * Error = NULL;
  char * str_Update="select * From Student "; //SQL语句
  int rc =0;
  if(OpenDB(&db)!=SQLITE_OK)
  {    
    return;
  }
    
  rc = sqlite3_prepare(db,str_Update,-1,&stmt,&Error);
  if(rc!=SQLITE_OK)
  {
    printf("准备执行查询语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
    return;
  }
  while(1)
  {
    if(sqlite3_step(stmt)!=SQLITE_ROW)
    {
      sqlite3_finalize(stmt);
      break;
    }
   int studentid =sqlite3_column_int(stmt,0);
   const char * studentName = sqlite3_column_text(stmt,1);
   const void * image = sqlite3_column_blob(stmt,2);
   printf("studentID is %d ,Name is %s,HeadImage is %s \n",studentid,studentName,image);
  }
  CloseDB(db);
}