SQLite C/C++库 连接与简单应用

 内容参考了菜鸟教程中的样例，从数据库、数据表创建到数据操作（增删改查）。另外还增加了一些教程以外的信息备注，包括如何在创建表格前先检查表格是否存在等。

适合做小型项目开发参考。

#include <sqlite3.h>

#include <stdio.h>



static int callback(void *NotUsed, int argc, char **argv, char **azColName){

int i;

for(i=0; i<argc; i++){

printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");

}

printf("\n");

return 0;

}



static int callback2(void *data, int argc, char **argv, char **azColName){

int i;

fprintf(stderr, "%s: ", (const char*)data);

for(i=0; i<argc; i++){

printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");

}

printf("\n");

return 0;

}



int main(int argc, char *argv[])

{



sqlite3 *db;

char *zErrMsg = 0;

int rc;



// 打开或创建数据库，如果数据库不存在就分创建，存在就直接打开

rc = sqlite3_open("test.db", &db);

if( rc ){

fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));

exit(0);

}else{

fprintf(stderr, "Opened database successfully\n");

}



// .db文件中可以有多个数据库。

// 创建附加的数据可使用ATTACH DATABASE 'testDB.db' as 'TEST';



// 创建数据表，这里需要注意一下，如果数据表已存在就会报错。所以在创建之前需要先检查数据表是否存在。可以用：

// select * from sqlite_master where type = 'table' and name = 't_cmpt_cp' // 检查t_cmpt_cp表是否存在

// select name from sqlite_master where type='table' order by name; // 打印所有的数据表

// select count(*) from sqlite_master where type='table' and name = 'yourtablename'; // 有输出1，没有输出0

// 需要查询表的结构可使用.schema ，如.schema COMPANY

/* Create SQL statement */

std::string sql = "CREATE TABLE COMPANY(" \

"ID INT PRIMARY KEY NOT NULL," \

"NAME TEXT NOT NULL," \

"AGE INT NOT NULL," \

"ADDRESS CHAR(50)," \

"SALARY REAL );";



/* Execute SQL statement */

rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);

if( rc != SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

}else{

fprintf(stdout, "Table created successfully\n");

}



/* Create SQL statement */

sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \

"VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \

"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \

"VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \

"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \

"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \

"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \

"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";



/* Execute SQL statement */

rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);

if( rc != SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

}else{

fprintf(stdout, "Records created successfully\n");

}





/* Create SQL statement */

sql = "SELECT * from COMPANY";

const char* data = "Callback function called";

/* Execute SQL statement */

rc = sqlite3_exec(db, sql.c_str(), callback2, (void*)data, &zErrMsg);

if( rc != SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

}else{

fprintf(stdout, "Operation done successfully\n");

}





/* Create merged SQL statement */

sql = "DELETE from COMPANY where ID=2; " \

"SELECT * from COMPANY";

/* Execute SQL statement */

rc = sqlite3_exec(db, sql.c_str(), callback2, (void*)data, &zErrMsg);

if( rc != SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

}else{

fprintf(stdout, "Operation done successfully\n");

}



//删除数据表用DROP TABLE COMPANY;



sqlite3_close(db);



return 0;

}