sqlite3数据库的增删改

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

void do_insert(sqlite3* db,char* errmsg)
{
	char sql[128] = "";
	printf("输入id 姓名 成绩:");
	int id;
	char name[20]="";
	float score;
	scanf("%d %s %2f",&id,name,&score);
	sprintf(sql,"insert into stu values(%d,'%s',%2f);",id,name,score);

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) !=SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return;
	}
	printf("插入成功\n");
}
void do_delete(sqlite3* db,char* errmsg)
{
	char sql[128]="";
	printf("输入要删除的id");
	int id;
	scanf("%d",&id);
	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;
	}
	printf("删除成功\n");

}
void do_update(sqlite3* db,char* errmsg)
{
	char sql[128]="";
	int id;
	char name[20]="";
	float score;
	printf("输入要修改成绩的id:");
	scanf("%d",&id);

	printf("输入新的成绩:");
	scanf("%2f",&score);
	sprintf(sql,"update stu set score=%2f where id=%d;",score,id);

/*	printf("输入新的姓名:");
	scanf("%s",name);
	sprintf(sql,"update stu set name=%s where id=%d;",name,id);
	*/
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) !=SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return;
	}
	printf("修改成功\n");

}
int main(int argc, const char *argv[])
{
	//如果数据库不存在，则创建后打开
	//如果存在则直接打开
	sqlite3* db = NULL;
	if(sqlite3_open("./my.db",&db) != SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_open :%s errcode:%d\n",sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}
	printf("sqlite3_open成功\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("创建数据表成功\n");

	char c = 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("请输入>>>");
		c = getchar();
		while(getchar() != 10);
		switch(c)
		{
			case '1':
				do_insert(db,errmsg);
				break;
			case '2':
				do_delete(db,errmsg);
				break;
			case '3':
				do_update(db,errmsg);
				break;
			case '4':
			//	do_select();
				break;
			case '5':
				goto END;
				break;
			default:
				printf("请重新输入\n");
		}
		printf("输入清屏>>>");
		while(getchar() != 10);
	}

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