SQLite数据库的C API函数:
官方手册文档：C/C++ Interface For SQLite Version3.0
SQL准备知识：SQL语法知识(转载)
一、数据库连接函数 :sqlite_open()、sqlite_open_v2()、sqlite3_open16()
///open or create sqlite3 database file. ///if sqlite3_open() success,returun SQLITE_OK(0),else return SQLIET_ERROR(1) nRC = sqlite3_open("sqlite3.db", &pDB); if (nRC != SQLITE_OK) { ///sqlite3_errmsg(sqlite3*): return English_language text that describes the error. fprintf(stderr, "Open database error: %s\n", sqlite3_errmsg(pDB)); sqlite3_close(pDB); exit(0); } else { fprintf(stdout, "Open database success.\n"); }二、SQLite执行查询函数(重点)
int sqlite3_exec( sqlite3*, /* An open database */ const char *sql, /* SQL to be evaluated */ int (*callback)(void*,int,char**,char**), /* Callback function */ void *, /* 1st argument to callback */ char **errmsg /* Error msg written here */ );该函数提供了一种快速、方便执行SQL命令的方法。注意改函数参数中传入的回调函数
该回调函数是一种格式化的定义，重点理解该回调函数的参数代表的含义。
///回调函数，该函数的声明为格式化，必须具有四个参数。 /* * void* para: 无用参数，sqlite3_exec()函数的第四个参数传入 * int colCount: 查询到的记录(行)有多少个字段(列)(即这条记录有多少列) * char** colValue: 查询出的数据都保存在这里，是一个一维数组，每个元素都是一个char*值，是一个字段的内容 * char** ColName: 与colCount对应，表示这个字段的字段名称(列名称) * return: 执行成功返回SQLITE_OK(0)，否则返回其他值 */ static int Sqlite3Callback(void* para, int colCount, char** colValue, char** ColName) { int i; fprintf(stderr, "%s: ", (const char*)para); for (i = 0; i < colCount; i++) { printf("%s = %s\n", ColName[i], colValue[i] ? colValue[i] : "NULL"); } printf("\n"); return 0; }执行查询的的DEMO：常见的操作及SQL指令编写
1、CREATE：创建表：注意重复创建表的问题。
///Create SQL statement---CREATE TABLE 注意避免重复创建表操作 pSQL = "CREATE TABLE IF NOT EXISTS Company(" \ "ID INT PRIMARY KEY NOT NULL," \ "NAME TEXT NOT NULL," \ "AGE INT NOT NULL," \ "ADDRESS CHAR(50) ," \ "SALARY REAL );"; nRC = sqlite3_exec(pDB, pSQL, Sqlite3Callback, 0, &pErrMsg); if (nRC!=SQLITE_OK) { fprintf(stderr, "SQL error1: %s\n", pErrMsg); sqlite3_free(pErrMsg); } else { fprintf(stdout, "Table created successed.\n"); }2、INSERT：插入操作(依据主键带来的重复插入问题)
///Create SQL Statement---INSERT 注意避免重复插入操作 pSQL = "INSERT OR IGNORE INTO Company(ID,NAME,AGE,ADDRESS,SALARY)" \ "VALUES (1, 'Paul',32,'California',20000.00);" \ "INSERT OR IGNORE INTO Company(ID,NAME,AGE,ADDRESS,SALARY)" \ "VALUES (2, 'Allen',25,'Texas',15000.00);" \ "INSERT OR IGNORE INTO Company(ID,NAME,AGE,ADDRESS,SALARY)" \ "VALUES (3, 'Teddy',23,'Norway', 20000.00);" \ "INSERT OR IGNORE INTO Company(ID,NAME,AGE,ADDRESS,SALARY)" \ "VALUES (4, 'Paul',25,'Rich-Mond', 65000.00);"; nRC = sqlite3_exec(pDB, pSQL, Sqlite3Callback, 0, &pErrMsg); if (nRC != SQLITE_OK) { fprintf(stderr, "SQL error2: %s\n", pErrMsg); sqlite3_free(pErrMsg); } else { fprintf(stdout, "Records create successed.\n"); }3、SELECT：查询指令(重点：查询指令是SQL中最重要的操作之一，要熟练理解SQL中SELECT的使用)
///Create SQL Statement---SELECT pSQL = "SELECT * FROM Company;"; nRC = sqlite3_exec(pDB, pSQL, Sqlite3Callback, (void*)data, &pErrMsg); if (nRC!=SQLITE_OK) { fprintf(stderr, "SQL error:3 %s\n", pErrMsg); sqlite3_free(pErrMsg); } else { fprintf(stdout, "SELECT operation successfully.\n"); }4、UPDATA：更新操作
///UPDATE pSQL = "UPDATE Company SET SALARY = 25000.00 WHERE ID=1;" \ "SELECT * FROM Company;"; nRC = sqlite3_exec(pDB, pSQL, Sqlite3Callback, (void*)data, &pErrMsg); if (nRC!=SQLITE_OK) { fprintf(stderr, "SQL Error4: %s", pErrMsg); sqlite3_free(pErrMsg); } else { fprintf(stdout, "Updata Opreation successfully.\n"); }5、DELETE：删除操作
///DELETE pSQL = "DELETE FROM Company WHERE ID=2; "\ "SELECT * FROM Company;"; nRC = sqlite3_exec(pDB, pSQL, Sqlite3Callback, (void*)data, &pErrMsg); if (nRC != SQLITE_OK) { fprintf(stderr, "SQL Error5: %s", pErrMsg); sqlite3_free(pErrMsg); } else { fprintf(stdout, "Delete Opreation successfully.\n"); }三、连接关闭(简单函数)
///int sqlite3_close(sqlite3*); sqlite3_close(pDB); ///close database.注意：执行该函数之前必须保证所有的查询操作都完成，否则该函数会返回SQLITE_BUSY并显示错误消息“由于未完成的语句，所以无法关闭连接”。
四、完整代码下载：SQLiteDemoCode
