4.25作业

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




void do_delete(sqlite3 *db)
{
	int id;
	printf("请输入要删除的id:");
	fflush(stdout);
	scanf("%d", &id);
	while(getchar() != '\n');

	char sql[128] = "";
    char *errmsg = NULL;
	sprintf(sql, "delete from stu where id = %d", id);

	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("delete fail: %s\n", errmsg);
        return -1;
	}
	printf("删除成功\n");
}




void do_insert(sqlite3 *db){  
	int id;
	printf("请输入id:\n");
	scanf("%d", &id);
	getchar();

	char name[100];
	printf("请输入姓名:\n");
	scanf("%s", name);
	getchar();

	float score;
	printf("请输入分数:\n");
	scanf("%f", &score);
	getchar();

	char buf[128] = "";
	char *errmsg = NULL;
	sprintf(buf,"insert into stu values (%d, '%s', %f);", id, name, score);
	
	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
		fprintf(stderr, "line : %d sqlite3_exec : %s\n", __LINE__, sqlite3_errmsg(db));
        return -1;
	}
    printf("新增成功\n");
}



void do_update(sqlite3 *db)
{
   	int id;
	char name[100];
	float score;


	printf("请输入id\n");
	scanf("%d",&id);
	while(getchar()!=10);

    printf("请输入名字\n");
	scanf("%s",name);
	while(getchar()!=10);

    printf("请输入分数\n");
	scanf("%f",&score);
	while(getchar()!=10);

    char sql[128]="";
    char *errmsg=NULL;
	sprintf(sql,"update stu set name=\"%s\", score=%f  where id=%d;",name,score,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");
	 
}



int main(int argc, const char *argv[])
{
	//创建并打开数据库
	sqlite3* db = NULL;	
	if(sqlite3_open("my.db", &db) != SQLITE_OK)
	{
		printf("line:%d sqlite3_open:[%d] %s\n", __LINE__, sqlite3_errcode(db), sqlite3_errmsg(db));
		return -1;
	
	}
	printf("open sqlite 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)
	{
		printf("line:%d sqlite3_exec:%s\n",__LINE__, errmsg);
		return -1;
	}
	printf("create table stu success\n");
 
	char c = 0;
	while(1)
	{
		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':
				break;
			case '5':
				goto END;
				break;
			default:
				printf("输入错误， 请重新输入\n");
		}
	}
END:
	if(sqlite3_close(db) != SQLITE_OK)
	{
		printf("line:%d sqlite3_close:[%d] %s\n", __LINE__, sqlite3_errcode(db), sqlite3_errmsg(db));
		return -1;
	}
	db = NULL;
	return 0;
}