#include"stdlib.h"
#include"stdio.h"

#include"sqlite3.h"

intmain()
{

charcsql[1024]={0};
sqlite3*psql=NULL;
char*pError=NULL;
inti=0,j=0;
char**ppTableData=NULL;
intnRow=0,nColumn=0;
intpos=0;

//打开数据库
sqlite3_open("server.db",&psql);

//如果userInfo表不存在，则创建一个。
sprintf(csql,"createtableifnotexistsuserInfo"
"("
"cUserNamevarchar(32)notnullprimarykey,"//用户名关键字不能为空
"cUserPwdvarchar(32)notnull,"//用户密码不能为空
"nUserPowerintergerdefault1,"//用户权限默认为1
"cCreateTimevarchar(32)default(datetime('now','localtime')),"//创建时间默认为当前本地时间
"cModifyTimevarchar(32)default(datetime('now',"//最后一次修改时间
"cLoginTimevarchar(32)default(datetime('now',"//最后一次登录时间
"cDescribevarchar(256)default('nodescribe')"//用户描述信息
")");

if(sqlite3_exec(psql,csql,&pError)!=sqlITE_OK)
{
printf("(%s)\r\n",pError);
}

//删除一项
sprintf(csql,"deletefromuserInfowherecUserName='%s'","admin");
if(sqlite3_exec(psql,pError);
}

//插入一项UserName=admin,cUserPwd=password
sprintf(csql,"insertintouserInfo(cUserName,cUserPwd)values('%s','%s')","admin","password");
if(sqlite3_exec(psql,pError);
}
//修改一项
sprintf(csql,"updateuserInfosetcUserPwd='%s',nUserPower=%d,cDescribe='%s'wherecUserName='%s'","88888888",2,"superuser",pError);
return-1;
}

//查找所有项,并显示
sprintf(csql,"select*fromuserInfo");
if(sqlite3_exec(psql,pError);
}
else
{
//获取选择的项目
sqlite3_get_table(psql,&ppTableData,&nRow,&nColumn,&pError);
printf("nRow=%d,nColumn=%d\r\n",nRow,nColumn);
pos=nColumn;
for(i=0;i<nRow;i++)
{
for(j=0;j<nColumn;j++)
{
printf("i%d,j%d,value=%s\r\n",i,j,ppTableData[pos++]);
}
}
//释放空间
sqlite3_free_table(ppTableData);
}

//查找cUserName=admin,cUserPwd=password的项
sprintf(csql,"select*fromuserInfowherecUserName='admin'andcUserPwd='password'");
if(sqlite3_exec(psql,pError);
}
else
{
sqlite3_get_table(psql,&pError);
if(nRow<=0)
{
printf("nofindcUserName='admin'andcUserPwd='password'\r\n");
}
else
{
printf("findsuccess\r\n");
pos=nColumn;
for(i=0;i<nRow;i++)
{
for(j=0;j<nColumn;j++)
{
printf("i%d,ppTableData[pos++]);
}
}
}
sqlite3_free_table(ppTableData);
}

//关闭数据库
sqlite3_close(psql);

return0;
}