SQLite之API操作

SQLite编程预备

(1)下载安装数据库

 sudo apt update
 sudo apt install sqlite3

(2)安装函数库

 sudo apt-get install libsqlite3-dev

否则编译会出现以下问题：
zgy@zgy-virtual-machine:~/sqlite$ gcc student.c -o student -lsqlite3
student.c:2:10: fatal error: sqlite3.h: 没有那个文件或目录
2 | #include <sqlite3.h>
| ^~~~~~~~~~~
compilation terminated.
zgy@zgy-virtual-machine:~/sqlite$ sudo apt-get install libsqlite3-dev

SQLite编程接口

（1）int sqlite3_open(char * path,sqlite3 **db);
功能:打开sqlite数据库,没有则创建
参数：path------数据库文件路径
db--------指向sqlite句柄的指针
返回值–成功返回0（SQLITE_OK），失败返回错误码

（2）int sqlite3_close(sqlite3 *db);
功能:关闭sqlite数据库
参数 : db--------指向sqlite句柄的指针
返回值–成功返回0（SQLITE_OK），失败返回错误码

（3）const char* sqlite3_errmsg(sqlite3 *db);
功能:通过db句柄，得到数据库操作的错误信息
参数 : db--------指向sqlite句柄的指针
返回值–返回错误信息的首地址

（4）int sqlite3_exec(
sqlite3 *db, //打开的数据库
const char * sql, //要执行的sql语句
int (*callback)(void *arg,int,char **,char **) //回调函数，保存我们所查询的表格，以供查询
void * arg, //回调函数参数
char **errmsg, //储存错误消息的首地址
);
功能:执行一条sql语句
返回值–成功返回0（SQLITE_OK），失败非0值（错误码），并把错误信息放在errmsg里。

int (*callback)(
void *arg, //传递给回调函数的参数
int, //记录包含的字段数目—就是列的数目
char **, //每个字段值的指针数组，具体数据
char ** //每个字段名称的指针数组，表头
)
成功返回0，失败返回-1；

（4）int sqlite3_get_table(
sqlite3 *db, //打开的数据库
const char * sql, //执行的SQL语句
char ***resultp, //用来指向sql执行结果的指针
int *nrow, //满足条件记录的数目-----多少行（不包括表头）
int *ncolumn, //每条记录包含的字段数目----多少列
char **errmsg //错误信息
)

成功返回0，失败错误码。

eg.

Name	score
zhangsan	90
lisi	80
cc	99

则nrow=3，ncolumn=2;
resultp[0]=“Name”;
resultp[1]=“score”;
resultp[2]=“zhangsan”;
resultp[3]=“90”;
resultp[4]=“lisi”;
…

代码演示

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <unistd.h>



#define DATABASE "stu.db"

int do_insert(sqlite3 *db,char *errmsg);
int do_delete(sqlite3 *db,char *errmsg);
int do_query(sqlite3 *db,char *errmsg);
int do_update(sqlite3 *db,char *errmsg);
int do_quit(sqlite3 *db);
int callback(void * arg,int f_num,char **f_values,char **f_name);

int main(int argc,const char *argv[])
{
	sqlite3 *db;
	char *errmsg;
	char sql[128] = {0};
	int cmd=5;

	if(sqlite3_open(DATABASE,&db) != SQLITE_OK){
		printf("%s\n",sqlite3_errmsg(db));
		return -1;
	}else{
		printf("open DATABASE success.\n");
	}

	sprintf(sql,"create table stu(id integer primary key autoincrement not null,name char unique,score integer);");

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
		printf("%s\n",errmsg);
	}else{
		printf("create table success.\n");
	}
	sqlite3_exec(db,".header on",NULL,NULL,&errmsg);
	sqlite3_exec(db,".mode column",NULL,NULL,&errmsg);
	while(1){
		printf("\033[41;33m**********************************************\033[0m\n");
		printf("\033[42;33m1:insert 2:delete 3:query 4:update 5:quit     \033[0m\n");
		printf("\033[41;33m**********************************************\033[0m\n");
//\033[41;33m 红底黄字 \033[0m
		scanf("%d",&cmd);
		getchar();
		switch(cmd){
			case 1:
				do_insert(db,errmsg);
				break;
			case 2:
				do_delete(db,errmsg);
				break;
			case 3:
				do_query(db,errmsg);
				break;
			case 4:
				do_update(db,errmsg);
				break;
			case 5:
				do_quit(db);
				break;
			dafault:
				printf("error cmd!\n");
				break;
		}
		if(cmd==5){
			printf("quit success.\n");
			sleep(1);
			system("clear");	
			break;
		}
	system("clear");	
	}
}

int do_insert(sqlite3 *db,char * errmsg){
//	printf("insert\n");
	char sql[128] = {0};
	char name[24] = {0};
	int score = 0;
	printf("please input name:");
	scanf("%s",name);
	getchar();

	printf("please input score:");
	scanf("%d",&score);
	getchar();

	sprintf(sql,"insert into stu(name,score) values('%s',%d);",name,score);
	score = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(score != 0){
		printf("%s\n",errmsg);
		sleep(1);
	}else{
		printf("insert success.\n");
		sleep(1);
	}
	return 0;
}
int do_delete(sqlite3 *db,char *errmsg){
//	printf("delete\n");
	char sql[128] = {0};
	char name[24] = {0};
	int score = 0;
	printf("please input name:");
	scanf("%s",name);
	getchar();
/*
	printf("please input score:");
	scanf("%d",&score);
	getchar();
*/
	sprintf(sql,"delete from stu where name = '%s'",name);
	score = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(score != 0){
		printf("%s\n",errmsg);
		sleep(1);
	}else{
		printf("delete success.\n");
		sleep(1);
	}

	return 0;
}
int callback(void * arg,int f_num,char **f_values,char **f_name)
{
	int i = 0;
	for(i = 0;i < f_num;i++){
		printf("%s",f_name[i]);
	}
	putchar(10);
	
	for(i = 0;i < f_num;i++){
		printf("%s",f_values[i]);
	}
	putchar(10);
	return 0;
}



int do_query(sqlite3 *db,char *errmsg)
{
//	printf("query\n");
	int t=0;
	char sql[128] = {0};
	sprintf(sql,"select * from stu;");

	t = sqlite3_exec(db,sql,callback,NULL,&errmsg);
	if(t != 0){
		printf("%s\n",errmsg);
		sleep(1);
	}else{
		printf("querry success.\n");
		sleep(5);
	}
	return 0;
}
int do_update(sqlite3 *db,char *errmsg){
//	printf("update\n");
	char sql[128] = {0};
	char name[24] = {0};
	int score = 0;
	printf("please input name:");
	scanf("%s",name);
	getchar();

	printf("please input score:");
	scanf("%d",&score);
	getchar();

	sprintf(sql,"update stu set score = %d where name = '%s'",score,name);
	score = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(score != 0){
		printf("%s\n",errmsg);
		sleep(1);
	}else{
		printf("update success.\n");
		sleep(1);
	}
	return 0;
}
int do_quit(sqlite3 *db){
	printf("quiting\n");
	sqlite3_close(db);
//	system("clear");	
	return 0;
}