SQlite 数据库增删改查-C编程简单实例

/*@brief 本程序测试sqlite数据库的增删改查*/ #include "sqlite3.h"#include <stdio.h>#include <string.h>#define _DEBUG_sqlite3 * pDB = NULL;//增加用户int AddUser(const char* sName,int sNameLen, const char* sAge,int sAgeLen);//删除用户int DeleteUser(const char* sName,int sNameLen);//修改用户int ModifyUser(const char* sName,int sNameLen, const char* sAge,int sAgeLen);//查找用户int SelectUser();int AddUser(const char* sName,int sNameLen, const char* sAge,int sAgeLen){char strSql[500] = {"\0"};char nameBuff[100] = {"\0"};char ageBuff[100] = {"\0"};strncat(nameBuff,sName,sNameLen);strncat(ageBuff,sAge,sAgeLen);sprintf(strSql,"insert into user(name,age)values('%s',%s);",nameBuff,ageBuff);#ifdef _DEBUG_printf("%d,strSql = %s\n",__LINE__,strSql);#endif    char* cErrMsg;    int nRes = sqlite3_exec(pDB , strSql ,0 ,0, &cErrMsg);#ifdef _DEBUG_    if(nRes != SQLITE_OK)    {printf("%d,strSql = %s\n",__LINE__,strSql);    }#endif    return 1;}int DeleteUser(const char* sName,int sNameLen){#if 0    char strSql = "";    strSql += "delete from user where name='";    strSql += sName;    strSql += "';";#endifchar strSql[500] = {"\0"};char nameBuff[100] = {"\0"};strncat(nameBuff,sName,sNameLen);sprintf(strSql,"delete from user where name='%s';",nameBuff);#ifdef _DEBUG_printf("%d,strSql = %s\n",__LINE__,strSql);#endif    char* cErrMsg;    int nRes = sqlite3_exec(pDB , strSql ,0 ,0, &cErrMsg);#ifdef _DEBUG_if(nRes != SQLITE_OK){printf("%d,strSql = %s\n",__LINE__,strSql);}#endif    return 1;}int ModifyUser(const char* sName,int sNameLen, const char* sAge,int sAgeLen){#if 0    char strSql = "";    strSql += "update user set age =";    strSql += sAge;    strSql += " where name='";    strSql += sName;    strSql += "';";#endifchar strSql[500] = {"\0"};char nameBuff[100] = {"\0"};char ageBuff[100] = {"\0"};strncat(nameBuff,sName,sNameLen);strncat(ageBuff,sAge,sAgeLen);sprintf(strSql,"update user set age =%s where name='%s';",ageBuff,nameBuff);#ifdef _DEBUG_printf("%d,strSql = %s\n",__LINE__,strSql);#endif    char* cErrMsg;    int nRes = sqlite3_exec(pDB , strSql  ,0 ,0, &cErrMsg);#ifdef _DEBUG_if(nRes != SQLITE_OK){printf("%d,strSql = %s\n",__LINE__,strSql);}#endif    return 1;}static int UserResult(void *NotUsed, int argc, char **argv, char **azColName){int i = 0;for(i = 0 ; i < argc ; i++)    {        //printf("%s %s\n",azColName[i],argv[i]);        printf("\t %s",argv[i]);    }printf("\n");    return 0;}int SelectUser(){    char* cErrMsg;    int nRes = sqlite3_exec(pDB, "select * from user;", UserResult , 0 , &cErrMsg);  #ifdef _DEBUG_if(nRes != SQLITE_OK){printf("%d,%s\n",__LINE__,cErrMsg);}#endif    return 1;}int main(int argc, char* argv[]){    char* cErrMsg;    int nRes = sqlite3_open("test.db", &pDB);char sql[500];#ifdef _DEBUG_    if(nRes == SQLITE_OK)    {printf("%d:Open db test.db success.\n",__LINE__);    }    else    {printf("%d,%s,%d\n",__LINE__,cErrMsg,nRes);    }#endifif(argc <= 1){printf("%d:Please enter your operation command!!\n\n",__LINE__);printf("(1)create:create table user.\n");printf("(2)add [name] [age]:add to table user.\n");printf("(3)del [name]:delet from table user.\n");printf("(4)modify [name] [age]:modify from table user.\n");printf("(5)show:show table user.\n\n");return -1;}//创建表if(0 == strcmp(argv[1],"create")){sprintf(sql,"%s"," CREATE TABLE user(name VARCHAR(50),age INTEGER);");nRes = sqlite3_exec( pDB , sql , 0 , 0 , &cErrMsg );#ifdef _DEBUG_if(nRes == SQLITE_OK){printf("%d:Creat table success,table name: user.\n",__LINE__);}else{printf("%d,%s,%d\n",__LINE__,cErrMsg,nRes);}#endif}#if 1//增加if(0 == strcmp(argv[1],"add")){AddUser(argv[2],strlen(argv[2]), argv[3],strlen(argv[3]));}//删除if(0 == strcmp(argv[1],"del")){DeleteUser(argv[2],strlen(argv[2]));}//修改if(0 == strcmp(argv[1],"modify")){ModifyUser(argv[2],strlen(argv[2]),argv[3],strlen(argv[3]));}//查看if(0 == strcmp(argv[1],"show")){printf("\t*name*\t*age*\n");SelectUser();}#endifsqlite3_close(pDB);return 0;}

[root@none:/mnt/nfs]# ./appsqlite 165:Open db test.db success.175:Please enter your operation command!!(1)create:      create table user.(2)add [name] [age]:    add to table user.(3)del [name]:  delet from table user.(4)modify [name] [age]: modify from table user.(5)show:        show table user.[root@none:/mnt/nfs]# ./appsqlite show165:Open db test.db success.        *name*  *age*         vivo    123         xiaomi  8[root@none:/mnt/nfs]#