day6 sqlite3--增删改功能实现

代码：

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

int do_insert(sqlite3* db,char sql[])
{
	int id;
	char name[20]="";
	float score;
	printf("请输入stu的id 姓名 分数>>>");
	scanf("%d %s %f",&id,name,&score);
	//增加数据
	ssize_t res =sprintf(sql,"insert into stu values(%d,'%s',%02.f\n);",id,name,score);
	if(res<0)
	{
		perror("sprintf");
		return -1;
	}
	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("insert stu table success!\n");
}

int do_delete(sqlite3* db,char sql[])
{
	int id;
	printf("请输入要删除stu的id>>>");
	scanf("%d",&id);
	//删除数据
	ssize_t res=sprintf(sql,"delete from stu where id=%d;",id);
	if(res<0)
	{
		perror("sprintf");
		return -1;
	}
	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("delete success!\n");
}
int do_update(sqlite3* db,char sql[])
{
	int id;
	float score;
	printf("请输入要更新stu的id>>>");
	scanf("%d",&id);
	getchar();
	printf("请输入要修改成绩>>>");
	scanf("%f",&score);
	getchar();
	//更新数据
	ssize_t res=sprintf(sql,"update stu set score =%.2f where id=%d;",score,id);
	if(res<0)
	{
		perror("sprintf");
		return -1;
	}
	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("update success!\n");
}
int main(int argc,const char *argv[])
{
	//如果数据库不存在，则创建后打开
	//如果存在则直接打开
	sqlite3* db=NULL;
	if(sqlite3_open("./my.db",&db)!=SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_open: %s errorde:%d\n",sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建一个表
	//注意：C代码中编写的sql语句与在数据库中编写的一致
	
	char sql[128]="create table if not exists stu (id int,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("create table stu success\n");

	char c=0;
	while(1)
	{
		printf("按任意键继续..");
		getchar();
		system("clear");
		printf("********************\n");
		printf("********1.增********\n");
		printf("********2.删********\n");
		printf("********3.改********\n");
		printf("********4.查********\n");
		printf("********5.退出******\n");
		printf("********************\n");
		printf("请输入>>>");
		c =getchar();
		while(getchar()!=10);

		switch(c)
		{
		case '1':
			do_insert(db,sql);
			break;
		case '2':
			do_delete(db,sql);
			break;
		case '3':
			do_update(db,sql);
			break;
		case '4':
			//do_select();
			break;
	
		case '5':
			goto END;
			break;
		default:printf("输入有误，请重新输入\n");
		}
		while(getchar()!=10);
	}

END:
	//关闭数据库
	if(sqlite3_close(db)!=SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_open: %s errorde:%d\n",sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}
	return 0;
}

对my.db数据库进行增操作运行结果：

 

 

进行删操作

进行改操作