一、定义
sqllite属于轻量级的嵌入式数据库,能支持的数据量为2TB,体积小,支持ACID事务
二、c编程
安装
sudo apt update
sudo apt install sqlite3
sudo apt install libsqlite3-dev
pkg-config --libs sqlite3
2.1 接口
官网
1. 打开数据库
int sqlite3_open(const char * filename, sqlite3 **ppDb);
filename为数据库路径名, ppdb为数据库的操作指针
2. 关闭数据库
int sqlite3_close(sqlite3 *db);
db为数据库的操作指针
成功返回 SQLITE_OK,出错返回错误码
3.执行sql语句
int sqlite_exec(
sqlite3 *db, const char *sql,
int (*callback)(void *, int, char **, char **),
void* arg, char**errmsg
);
db为数据库操作句柄,sql为具体语句,callback为回调函数,只有sql为select时才会使用,arg为回调函数传参, errmsg为返回消息。
返回值:成功为SQLITE_OK
在回调函数中有:
typedef int(*sqlite3_callback)
(void *para, int f_num, char **f_value, char **f_name);
para 为传递给回调函数的参数。f_num为记录中包含的字段数目,即列的数目。f_value为每个字段值的指针数组,f_name为每个字段名称的指针数组,成功返回0,失败返回-1。
因为每一个记录都会执行callback,所以每一行需要for循环输出每个列
查询时也可以不用callback,使用:
sqlite3_get_table(sqlite3 *db, const char *sql,
char ***resultp, int *nrow, int *ncolumn, char **errmsg);
其中,db为数据库句柄,sql为语句,resultp为指向sql结果的指针,nrow为满足条件的记录的数目,ncolumn为每条记录包含的字段数目,errmsg为错误信息,成功返回0,失败返回错误码
resultp为指向二维数组的指针,如下:
4.具体代码和结果
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#define DATABASE "stu.db"
int do_insert(sqlite3 *db){
int id;
char name[32] = {0};
int score;
char sql[128] = {0};
char *errormsg;
printf("input id:\n");
scanf("%d", &id);
printf("input name:\n");
scanf("%s", name);
printf("input score:\n");
scanf("%d", &score);
sprintf(sql, "insert into stu values(%d, '%s', %d);", id, name, score);
if(sqlite3_exec(db, sql, NULL, NULL, &errormsg) !=SQLITE_OK){
printf("%s\n", errormsg);
return -1;
}else{
printf("insert done\n");
return 0;
}
};
int do_delete(sqlite3 *db){
int id;
char name[32] = {0};
int score;
char sql[128] = {0};
char *errormsg;
printf("delete id:\n");
scanf("%d", &id);
sprintf(sql, "delete from stu where id=%d;", id);
if(sqlite3_exec(db, sql, NULL, NULL, &errormsg) !=SQLITE_OK){
printf("%s\n", errormsg);
return -1;
}else{
printf("delete done\n");
return 0;
}
};
int db_update(sqlite3 *db){
int id;
char name[32] = {0};
int score;
char sql[128] = {0};
char *errormsg;
printf("update id:\n");
scanf("%d", &id);
printf("update name:\n");
scanf("%s", name);
printf("update score:\n");
scanf("%d", &score);
sprintf(sql, "update stu set score = %d where id=%d;", score,id);
if(sqlite3_exec(db, sql, NULL, NULL, &errormsg) !=SQLITE_OK){
printf("%s\n", errormsg);
return -1;
}else{
printf("update done\n");
return 0;
}
};
// 注意select要有回调函数和具体参数,每找到一个记录都会执行一次回调函数
int callback(void *para, int f_num, char **f_value, char **f_name){
for(int i = 0; i<f_num;++i){
printf("%s\n", f_value[i]);
}
return 0;
}
#if 0
int do_query(sqlite3 *db){
int id;
char *errormsg;
printf("query id:\n");
scanf("%d", &id);
char sql[128] = {0};
sprintf(sql, "select * from stu where id=%d;",id);
// NULL对于callback的para
if(sqlite3_exec(db, sql, callback, NULL, &errormsg) !=SQLITE_OK){
printf("%s\n", errormsg);
return -1;
}else{
printf("query done\n");
return 0;
}
};
#else
int do_query(sqlite3 *db){
int id;
char **resultp;
char *errormsg;
int nrow;
int ncolumn;
printf("query id:\n");
scanf("%d", &id);
char sql[128] = {0};
sprintf(sql, "select * from stu where id=%d;",id);
// NULL对于callback的para
if(sqlite3_get_table(db, sql, &resultp, &nrow, &ncolumn, &errormsg) !=SQLITE_OK){
printf("%s\n", errormsg);
return -1;
}else{
printf("query done\n");
}
for(int i = 0;i<nrow;++i){
for(int j = 0; j<ncolumn;++j){
// +1是因为把表头跳过
printf("%s\t", resultp[(i + 1) * ncolumn + j]);
}
printf("\n");
}
};
#endif
int main(int argc, void *argv[]){
sqlite3 *db;
char *errmsg;
if(sqlite3_open(DATABASE, &db) != SQLITE_OK){
// 返回错误信息
printf("%s\n", sqlite3_errmsg(db));
return -1;
}else{
printf("open DATABASE success\n");
}
char *sql = "create table stu (id Integer, name char, score Integer);";
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg)!=SQLITE_OK){
printf("%s\n", errmsg);
}else{
printf("create table success\n");
}
int cmd;
while(1){
printf("***************************");
printf("1:insert,2:delete,3:query,4:update,5:quit\n");
scanf("%d",&cmd);
getchar();
switch(cmd){
case 1:
do_insert(db);
break;
case 2:
do_delete(db);
break;
case 3:
do_query(db);
break;
case 4:
db_update(db);
break;
case 5:
break;
default:
printf("error cmd\n");
return -1;
}
}
}
gcc -o mysqllite mysqllite.c -lsqlite3