sqlite3 C 笔记

一.安装

1.下载源码和Windows dll两个压缩包

2.编译lib 将dll中的def文件复制到源码目录下执行

LIB /out:sqlite3.lib /MACHINE:IX86 /DEF:sqlite3.def

3.将.lib和dll, sqlite3.h复制到源码目录

就可以使用sqlite3了

二.入门编程

#include <string.h>
using namespace std;
#include "sqlite3.h"
#pragma comment(lib, "SQLITE3.LIB")
static int SelectCallback(void *notUsed, int argc, char **argv, char **azColName)
{
 for (int i = 0 ; i < argc ; i++)
 {
 printf("%s = %s", azColName[i], (argv[i] ? argv[i] : "NULL"));
 if (i != argc -1)
 {
 printf(", ");
 }
 }
 printf("\n");
 return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
 sqlite3 * pDB;
 char* errMsg;
// 打开SQLite数据库
 int res = sqlite3_open("sql.db", &pDB);
 if ( res != SQLITE_OK ){
 printf("Can't open database: %s\n", sqlite3_errmsg(pDB));
 sqlite3_close(pDB);
 return -1;
 }
// 创建表
 string strSQL= "create table test (id int, name text);";
 res = sqlite3_exec(pDB , strSQL.c_str() ,0 ,0, &errMsg);
 if (res != SQLITE_OK)
 {
 printf("Create table error: %s\n", errMsg);
 //return -1;
 }
// 插入数据
 res = sqlite3_exec(pDB,"begin transaction;",0,0, &errMsg);
 for (int i= 1; i < 10; ++i)
 {
 char sql[512];
 sprintf_s(sql, "insert into test values(%d, %s);", (i+10), "\"Test Name\"");
res = sqlite3_exec(pDB, sql,0,0, &errMsg);
 if (res != SQLITE_OK)
 {
 printf("Insert error: %s\n", errMsg);
 return -1;
 }
 }
 res = sqlite3_exec(pDB,"commit transaction;",0,0, &errMsg);
// 查询数据
 strSQL= "select * from test;";
 res = sqlite3_exec(pDB, strSQL.c_str(), SelectCallback, 0 , &errMsg);
 if (res != SQLITE_OK)
 {
 printf("Select error: %s\n", errMsg);
 return -1;
 }
 // 关闭数据库
 sqlite3_close(pDB);
return 0;
}

三.常用语句

selete * from tableName where 条件

select count(*) from tableName

create table tableName(id integer primary key autoincrement,name varchar(100)

insert into tableName values(va1,va2)

四.常用函数

sqlite3_open()

sqlite3_exec();一般需要写回调函数

sqlite3_get_table();使用此函数可以不用写回调函数

sqlite3_close(db);

五.本文目的

一一般网上大多数Sqlite3 c/C++教程都是使用四中的函数来进行结果查询,感觉麻烦,比如只是想知道一个表中有没有叫王二的人可以使用selete count(*) from tableName where name = "王二"那么结果呢?

	sqlite3_stmt *stmt;
	sprintf(sql, "%s","select count(*) from fuck where id = 200;");
	rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, /*&tail*/0);
	if (rc !=SQLITE_OK)
	{
		return 0;
	}
	rc = sqlite3_step(stmt);

	iCount = sqlite3_column_int(stmt, 0);

	sqlite3_finalize(stmt);

	cout << "daxiao " << iCount << endl;

只要判断返回值就行了