#include "sqlite3.h"
#include <iostream>
using namespace std;
sqlite3 *pDB = NULL;
//删除表内数据
bool DeleteTable();
//重新设置表内数据自增id
bool Setsequence();
//向创建的user表内插入数据
bool AddUser(const string& sName, const string& sAge);
//删除表内的某一角色
bool DeleteUser(const string& sName);
//修改某一角色的某一属性
bool ModifyUser(const string& sName, const string& sAge);
//查询user表中所有角色的所有信息
bool SelectUser();
//查询user表中特定角色的信息
bool push_clicked();
int main()
{
//打开sqlite数据库(按照自己创建的数据库位置打开)
int nRes = sqlite3.open("D:\\example\\SQLite\\Debug\\test.db",&pDB);
if(nRes != SQLITE_OK)
{
cout<<"Open database fail: "<<sqlite3_errmsg(pDB);
goto QUIT;
}
DeleteTable();
Setsequence();
if( !AddUser("zhao","18") || !AddUser("qian","19") || !AddUser("sun","20") || !AddUser("li","21"))
{
goto QUIT;
}
if(!DeleteUser("zhao"))
{
goto QUIT;
}
if(!ModifyUser("sun","15"))
{
goto QUIT;
}
if(!SelectUser())
{
goto QUIT;
}
push_clicked();
QUIT:
sqlite3_close(pDB);
system("pause");
return 0;
}
bool DeleteTable()
{
string strSql = "delete from user";
char *cErrMsg;
int nRes = sqlite3_exec(pDB , strSql.c_str(), 0, 0, &cErrMsg);
if(nRes != SQLITE_OK)
{
cout<<"Delete user fail!"<<endl;
return false;
}
cout<<"Delete user success!"<<endl;
return true;
}
bool Setsequence();
{
string strSql = "update sqlite_sequence set seq = 0 where name = 'user'";
char *cErrMsg;
int nRes = sqlite3_exec(pDB , strSql.c_str(), 0, 0, &cErrMsg);
if(nRes != SQLITE_OK)
{
cout<<"Set user fail!"<<endl;
return false;
}
cout<<"Set user success!"<<endl;
return true;
}
bool AddUser(const string& sName, const string& sAge)
{
string strSql = "";
strSql += "insert into user(name,age)";
strSql += "values('";
strSql += sName;
strSql += "',";
strSql += sAge;
strSql += ")";
char *cErrMsg;
int nRes = sqlite3_exec(pDB , strSql.c_str(), 0, 0, &cErrMsg);
if(nRes != SQLITE_OK)
{
cout<<"add user fail!"<<endl;
return false;
}
cout<<"add user success: "<<sName.c_str()<<"\t"<<sAge.c_str()<<endl;
return true;
}
bool DeleteUser(const string& sName)
{
string strSql = "";
strSql += "delete from user where name = '";
strSql += sName;
strSql += "'";
char *cErrMsg;
int nRes = sqlite3_exec(pDB , strSql.c_str(), 0, 0, &cErrMsg);
if(nRes != SQLITE_OK)
{
cout<<"delete user fail!"<<endl;
return false;
}
cout<<"delete user success: "<<sName.c_str()<<endl;
return true;
}
bool ModifyUser(const string& sName, const string& sAge)
{
string strSql = "";
strSql += "update user set age = ";
strSql += sAge;
strSql += " where name = '"; //where前的空格不能删除
strSql += sName;
strSql += "'";
char *cErrMsg;
int nRes = sqlite3_exec(pDB , strSql.c_str(), 0, 0, &cErrMsg);
if(nRes != SQLITE_OK)
{
cout<<"modify user fail!"<<endl;
return false;
}
cout<<"modify user success: "<<sName.c_str()<<"\t"<<sAge.c_str()<<endl;
return true;
}
static int UserResult(void *NotUsed, int argc , char **argv, char **azColName)
{
for(int i=0 ; i<argc ; i++)
{
cout<<azColName[i]<<" = "<<(argv[i] ? argv[i] : "NULL")<<", ";
}
cout<<endl;
return 0;
}
bool SelectUser()
{
char *cErrMsg;
int res = sqlite3_exec(pDB , "select * from user", UserResult, 0, &cErrMsg);
if(res != SQLITE_OK)
{
cout<<"select user fail!"<<endl;
return false;
}
return true;
}
bool push_clicked()
{
char *cErrMsg;
int res = sqlite3_exec(pDB , "select name from user where age = 21", UserResult, 0, &cErrMsg);
if(res != SQLITE_OK)
{
cout<<"find user fail!"<<endl;
return false;
}
return true;
}