目录
打开数据库
int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
参数:filename 数据库名称
ppdb 数据库句柄
返回值:成功为0 SQLITE_OK ,出错 错误码
关闭数据库
int sqlite3_close(sqlite3* db);
参数: dp 操作数据库的指针;
返回值:成功为0 SQLITE_OK ; 出错 错误码
得到错误信息的描述
const char *sqlite3_errmsg(sqlite3*db);
功能:通过db句柄得到错误信息的描述
API函数
int sqlite3_exec(
sqlite3* db, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void* arg,int,char**,char**), /* Callback function */
void * arg, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);
功能:执行一条sql语句
参数: db 数据库句柄
sql 要执行的sql语句,例如创建表“create table stu(id int, ...)”
callback 回调函数,只有sql语句为查询语句时,才传参
arg 为回调函数传递参数
errmsg 错误消息
返回值:成功 SQLITE_OK
回调函数
typedef int(*sqlite_callback)(void* para, int f_num, char** f_value, char** f_name);
功能:每找到一条记录执行一次回调函数;
参数:para 传递给回调函数的参数;
f_num 记录包含的字段数目;
f_value 包含每个字段值的指针数组
f_name 包含每个字段名称的指针数组
返回值:成功返回0, 失败返回-1;
查询回调函数
int (*callback)(void* arg,int ncolumns ,char** f_value,char** f_name), /* Callback function */
功能:查询语句执行之后,会回调此函数(每找到一条记录自动执行一次回调函数)
参数:arg 接收sqlite3_exec 传递来的参数
ncolumns 列数
f_value 列的值得地址
f_name 列的名称
返回值:0;
不使用回调函数查询
int sqlite3_get_table(
sqlite3 *db, /* An open database */
const char *zSql, /* SQL to be evaluated */
char ***pazResult, /* Results of the query */
int *pnRow, /* Number of result rows written here */
int *pnColumn, /* Number of result columns written here */
char **pzErrmsg /* Error msg written here */
);
void sqlite3_free_table(char **result);
简易学生表创建
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#define DATABASE "stu.db"
/*实现插入操作*/
int do_insert(sqlite3 * db)
{
int id;
char name[32] = {};
int score;
char sql[128] = {};
char *errmsg;
printf("input id:");
scanf("%d", &id);
getchar();
printf("input name:");
scanf("%s", name);
getchar();
printf("input score:");
scanf("%d", &score);
getchar();
//对需要用的sql语句进行拼接
sprintf(sql, "insert into stu values(%d, '%s', %d);", id, name, score);
if ( sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}else{
printf("insert done\n");
}
return 0;
}
/*实现按照id删除操作*/
int do_delete(sqlite3 * db)
{
int id;
char sql[128] = {};
char *errmsg;
printf("input id:");
scanf("%d", &id);
getchar();
sprintf(sql, "delete from stu where id = %d", id);
if ( sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}else{
printf("delete done\n");
}
return 0;
}
/*实现数据更新功能*/
int do_update(sqlite3 *db)
{
int id;
char sql[128] = {};
int score;
char *errmsg;
printf("input update id:");
scanf("%d", &id);
getchar();
printf("input update score:");
scanf("%d", &score);
getchar();
sprintf(sql, "update stu set score = %d where id = %d", score, id);
if( sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}else{
printf("update done\n");
}
return 0;
}
/*回调函数*/
int callback(void *para, int f_num, char **f_value, char **f_name)
{
int i;
//查找一条打印一条
for(i = 0; i < f_num; i++)
{
printf("%-11s ", f_value[i]);
}
putchar(10);
return 0;
}
/*实现查询操作*/
int do_query(sqlite3 * db)
{
char sql[128] = {};
char *errmsg;
sprintf(sql, "select * from stu;");
if( sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}else{
printf("query done\n");
}
return 0;
}
/*不使用回调函数查询*/
int do_query1(sqlite3 * db)
{
char sql[128] = {};
char *errmsg;
char **resultp;
int nrow;
int ncloumn;
int index;
int i, j;
sprintf(sql, "select * from stu;");
if( sqlite3_get_table(db, sql, &resultp, &nrow, &ncloumn, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}else{
printf("query1 done\n");
}
for(j = 0;j < ncloumn; j++)
{
printf("%-11s", resultp[j]);
}
putchar(10);
index = ncloumn;
for(i = 0; i < nrow; i++)
{
for(j = 0; j < ncloumn; j++)
{
printf("%-11s ", resultp[index++]);
}
putchar(10);
}
return 0;
}
int main(int agrc, char *argv[])
{
sqlite3 * db;
char *errmsg;
int cmd;
/*打开一个数据库,如果打开失败通过sqlite3_errmsg(db)返回出错误信息*/
if (sqlite3_open(DATABASE, &db) != SQLITE_OK)
{
printf("%s\n", sqlite3_errmsg(db));
return -1;
}else{
printf("open DATABASE success\n");
}
/*创建一个数据库的表格*/
if( sqlite3_exec(db, "create table stu (id Integer, name char, score Integer);",
NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("%s\n", errmsg);
}else{
printf("create table or open success\n");
}
while(1)
{
printf("************************************\n");
printf("1:insert 2:delete 3:query 4:update 5:quit\n");
printf("************************************\n");
printf("input cmd:");
scanf("%d", &cmd);
getchar();
switch(cmd)
{
case 1:
do_insert(db);
break;
case 2:
do_delete(db);
break;
case 3:
do_query1(db);
// do_query(db);
break;
case 4:
do_update(db);
break;
case 5:
sqlite3_close(db);
exit(0);
defalut:
printf("Error cmd\n");
}
}
return 0;
}