用c语言实现sqlite3增删改功能

用c语言实现sqlite3增删改功能

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
int do_insert(sqlite3 *db);
int do_delete(sqlite3 *db);
int do_update(sqlite3 *db);
int do_select(sqlite3 *db);

int main(int argc, const char *argv[])
{
	//创建并打开一个数据库
	sqlite3 *db = NULL;
	if(sqlite3_open("./chatonline.db", &db) != SQLITE_OK){
		fprintf(stderr,"sqlite3_open failed:%d:%s\n",sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建一张表格：数据库中的sql语句怎么写，该字符串就怎么写
	char sql[128] = "create table if not exists stu(id int PRIMARY KEY, name char,score float);";
	char *errmsg =NULL;
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("table stu create success\n");

	char choose = 0;
	while(1){
		system("clear");
		printf("--------------------------\n");
		printf("---------1.插入-----------\n");
		printf("---------2.删除-----------\n");
		printf("---------3.修改-----------\n");
		printf("---------4.查询-----------\n");
		printf("---------5.退出-----------\n");
		printf("--------------------------\n");
		printf("请输入>>>");
		choose = getchar();
		while(getchar()!=10);

		switch(choose)
		{
		case '1':
			do_insert(db);
			break;
		case '2':
			do_delete(db);
			break;
		case '3':
			do_update(db);
			break;
		case '4':
			do_select(db);
			break;
		case '5':
			goto END;
			break;
		default:
			printf("输入错误，请重新输入\n");
		}
		printf("输入任意字符清屏>>>");
		while(getchar()!=10);
	}
END:
	if(sqlite3_close(db) != SQLITE_OK){
		fprintf(stderr,"line:%d sqlite3_close:%d:%s\n",\
				__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	return 0;
}

int do_insert(sqlite3 *db){
	char sql[128] = "";
	int id = 0;
	char name[20]="";
	float score = 0;
	char *errmsg = NULL;
	char choose;
	while(1){
		printf("***准备插入数据，输入'c'开始插入数据 or 输入'q'退出插入程序***\n");
		printf("请输入'c' or 'q' >>>");
		scanf("%c",&choose);
		while(getchar()!=10);
		if(choose == 'c'){
			bzero(sql,sizeof(sql));
			bzero(name,sizeof(name));
			//id号
			printf("请输入id号>>>");
			scanf("%d",&id);
			while(getchar()!=10);
			//名字
			printf("请输入名字>>>");
			scanf("%s",name);
			while(getchar()!=10);
			//成绩
			printf("请输入成绩>>>");
			scanf("%f",&score);
			while(getchar()!=10);
			//拼接sql语句
			sprintf(sql,"insert into stu values (%d,\"%s\",%g);",id,name,score);
			if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
				fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
				return -1;
			}
			printf("插入数据成功\n");
		}else if(choose == 'q'){
			printf("退出程序成功\n");
			break;
		}
	}
	return 0;
}

int do_delete(sqlite3 *db){
	char sql[128] = "";
	int id = 0;
	char *errmsg = NULL;
	char choose;
	while(1){
		printf("***准备删除数据，输入's'开始删除数据 or 输入'q'退出删除程序***\n");
		printf("请输入's' or 'q' >>>");
		scanf("%c",&choose);
		while(getchar()!=10);
		if(choose == 's'){
			//id号
			bzero(sql,sizeof(sql));
			printf("请输入要删除的数据的id号>>>");
			scanf("%d",&id);
			while(getchar()!=10);
			//拼接sql语句
			sprintf(sql,"delete from stu where id=%d;",id);
			if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
				fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
				return -1;
			}
			printf("删除数据成功\n");
		}else if(choose == 'q'){
			printf("退出程序成功\n");
			break;
		}
	}
	return 0;
}

int do_update(sqlite3 *db){
	char sql[128] = "";
	int old_id = 0;
	int new_id = 0;
	char name[20]="";
	float score = 0;
	char *errmsg = NULL;
	char choose;
	int num = 0;
	while(1){
		bzero(sql,sizeof(sql));
		bzero(name,sizeof(name));
		printf("***准备修改数据，输入'x'开始修改数据 or 输入'q'退出插入程序***\n");
		printf("请输入'x' or 'q' >>>");
		scanf("%c",&choose);
		while(getchar()!=10);
		if(choose == 'x'){
			printf("请输入要修改的数据的id号>>>");
			scanf("%d",&old_id);
			while(getchar()!=10);
			printf("---请选择要修改的数据内容---\n");
			printf("-----------1.id-------------\n");
			printf("-----------2.名字-----------\n");
			printf("-----------3.成绩-----------\n");
			printf("请输入>>>");
			scanf("%d",&num);
			while(getchar()!=10);
			switch(num)
			{
			case 1:
				printf("请输入新的id号>>>");
				scanf("%d",&new_id);
				while(getchar()!=10);
				sprintf(sql,"update stu set id=%d where id=%d;",old_id,new_id);
				//拼接sql语句
				if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
					fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
					return -1;
				}
				printf("修改数据成功\n");
				break;
			case 2:
				printf("请输入新的名字>>>");
				scanf("%s",name);
				while(getchar()!=10);
				sprintf(sql,"update stu set name=\"%s\" where id=%d;",name,old_id);
				//拼接sql语句
				if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
					fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
					return -1;
				}
				printf("修改数据成功\n");
				break;
			case 3:
				printf("请输入新的成绩>>>");
				scanf("%f",&score);
				while(getchar()!=10);
				sprintf(sql,"update stu set score=%g where id=%d;",score,old_id);
				//拼接sql语句
				if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
					fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
					return -1;
				}
				printf("修改数据成功\n");
				break;
			default:
				printf("输入错误，请重新输入\n");
			}
			}else if(choose == 'q'){
				printf("退出程序成功\n");
				break;
			}
		}
		return 0;
	}

	int do_select(sqlite3 *db){

	}

运行结果：

增：

 删：

改：