SQLite的入门（之一）

准备工作

下载相关文件。
- 文件1：sqlite-dll-win64-x64-3180000.zip，包含sqlite3.def和sqlite3.dll
- 文件2：sqlite-tools-win32-x86-3180000.zip，包含sqlite3.exe等shell工具。
- 文件3：sqlite-amalgamation-3180000.zip，包含sqlite3.h头文件。

生成lib文件

使用VS2015工具lib命令行，移动到def所在的文件夹并利用sqlite3.def生成对应lib文件。命令如下：

lib /DEF:sqlite3.def /MACHINE:X64

新建一个数据库

在命令行中使用sqlite3.exe，建立一个名为tbLzkila的表，并保存到test.db文件中：

sqlite> create table tbLzkila   ...> (   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,   ...> name VARCHAR(64),   ...> age INTERGER   ...> );sqlite> .save test.db

新建一个工程

打开VS2015，新建一个win32控制台项目。把sqlite3.dll,sqlite3.h,sqlite3.lib放入项目根文件夹下。然后在项目属性添加lib文件sqlite3.lib。

测试代码

#include "stdafx.h"#include "sqlite3.h"#include <iostream>using namespace std;sqlite3 * pDB = NULL;//增加用户  bool addUser(const string& sName, const string& sAge);//删除用户  bool deleteUser(const string& sName);//修改用户  bool modifyUser(const string& sName, const string& sAge);//查找用户  bool selectUser();int main(){    //打开路径采用utf-8编码      //如果路径中包含中文，需要进行编码转换      int nRes = sqlite3_open("E:\\__Dev\\SQLite\\sqlite-tools-win32-x86-3180000\\test.db", &pDB);    if (nRes != SQLITE_OK)    {        cout << "Open database fail: " << sqlite3_errmsg(pDB);        goto QUIT;    }    ////添加“赵钱孙李”      //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;    }QUIT:    sqlite3_close(pDB);    system("pause");    return 0;}bool addUser(const string& sName, const string& sAge){    string strSql = "";    strSql += "insert into tbLzkila(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: " << cErrMsg << endl;        return false;    }    else    {        cout << "add user success: " << sName.c_str() << "\t" << sAge.c_str() << endl;    }    return true;}bool deleteUser(const string& sName){    string strSql = "";    strSql += "delete from tbLzkila 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: " << cErrMsg << endl;        return false;    }    else    {        cout << "delete user success: " << sName.c_str() << endl;    }    return true;}bool modifyUser(const string& sName, const string& sAge){    string strSql = "";    strSql += "update tbLzkila set age =";    strSql += sAge;    strSql += " where name='";    strSql += sName;    strSql += "';";    char* cErrMsg;    int nRes = sqlite3_exec(pDB, strSql.c_str(), 0, 0, &cErrMsg);    if (nRes != SQLITE_OK)    {        cout << "modify user fail: " << cErrMsg << endl;        return false;    }    else    {        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 tbLzkila;", userResult, 0, &cErrMsg);    if (res != SQLITE_OK)    {        cout << "select fail: " << cErrMsg << endl;        return false;    }    return true;}