sql语句与sqlite3的C -APls

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

int do_insert(sqlite3 *db);
int do_delete(sqlite3 *db);
int do_update(sqlite3 *db);

int main(int argc,const char *argv[])
{
	sqlite3 *db = NULL;
	//打开数据库，如果存在则直接打开，不存在就创建后再打开
	if(sqlite3_open("./mysqlite.db",&db) != SQLITE_OK){
		//打印错误码和错误信息
		fprintf(stderr,"errcode = %d   sqlite3_open : %s\n",sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建一个表
	char sql[128] = "create table if not exists stu1 (id int primary key,name char,score float);";
	char *errmsg = NULL;
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
		fprintf(stderr,"__%d__ : %s\n",__LINE__,errmsg);
		return -1;
	}
	printf("creat table success\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);
				break;
			case '2':
				do_delete(db);
				break;
			case '3':
				do_update(db);
				break;
			case '4':
				//do_select();
				break;
			case '5':
				goto END;
			default:
				printf("输入错误，请重新输入\n");
		}
		printf("请输入任意字符清屏>>> ");
		while(getchar()!=10);
	}

END:
	//关闭数据库
	if(sqlite3_close(db) != SQLITE_OK){
		//打印错误码和错误信息
		fprintf(stderr,"errcode = %d   sqlite3_open ; %s\n",sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_close success\n");

	return 0;
}

//插入操作
int do_insert(sqlite3 *db){
	char sql[128] = "";
	char *errmsg = NULL;
	int in_id = 0;
	char in_name[32] = "";
	float in_score = 0;

	printf("请输入要插入的信息（id,name,score）>>> \n");
	scanf("%d %s %f",&in_id,in_name,&in_score);
	while(getchar()!=10);
	
	sprintf(sql,"insert into stu1 values (%d,\"%s\",%f);",in_id,in_name,in_score);

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
		fprintf(stderr,"__%d__ : %s\n",__LINE__,errmsg);
		return -1;
	}
	printf("insert success\n");
	return 0;
}

//删除操作
int do_delete(sqlite3 *db){
	char sql[128] = "";
	char *errmsg = NULL;
	int in_id = 0;

	printf("请输入要删除的id：");
	scanf("%d",&in_id);
	while(getchar()!=10);
	sprintf(sql,"delete from stu1 where id=%d",in_id);

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
		fprintf(stderr,"__%d__ : %s\n",__LINE__,errmsg);
		return -1;
	}
	printf("delete success\n");
	return 0;
}

//修改操作
int do_update(sqlite3 *db){
	char sql[128] = "";
	char *errmsg = NULL;
	int in_id = 0;
	char in_name[32] = "";
	float in_score = 0;

	printf("请输入要修改的id：");
	scanf("%d",&in_id);
	while(getchar()!=10);
AGAIN:
	printf("---------------------------\n");
	printf("----请选择要修改的内容-----\n");
	printf("---------1.name------------\n");
	printf("---------2.score-----------\n");
	printf("---------------------------\n");
	printf("请输入你的选择>>> ");
	char c = getchar();
	while(getchar()!=10);
	switch(c){
		case '1':
			printf("请输入修改后的name >>> ");
			scanf("%s",in_name);
			while(getchar()!=10);
			sprintf(sql,"update stu1 set name=\"%s\" where id=%d",in_name,in_id);
			break;
		case '2':
			printf("请输入修改后的score >>> ");
			scanf("%f",&in_score);
			while(getchar()!=10);
			sprintf(sql,"update stu1 set score=%f where id=%d",in_score,in_id);
			break;
		default :
			printf("输入错误,请重新输入\n");
			goto AGAIN;
	}

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
		fprintf(stderr,"__%d__ : %s\n",__LINE__,errmsg);
		return -1;
	}
	printf("update success\n");
	return 0;
}