华清远见上海中心22071班 9.21作业

1.完成数据库的插入、删除、修改，插入选择全字段插入、删除、修改选择用id的方式

代码：

#include <stdio.h>
#include <sqlite3.h>
#include <string.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("./stu.db",&db)!=SQLITE_OK)
	{
		fprintf(stderr,"__%d__ sqlite3_open:%s\n",__LINE__,sqlite3_errmsg(db));
		fprintf(stderr,"error_code:%d\n",sqlite3_errcode(db));
		return -1;
	}
	printf("sqlite3 open success\n");
	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, "__%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':
			//do_select();
			break;
		case '5':
			goto END;
		default:
			printf("输入错误请重新输入");
		}
	}
END:
	if(sqlite3_close(db)!=SQLITE_OK)
	{
		fprintf(stderr,"__%d__ sqlite3_close:%s\n",__LINE__,sqlite3_errmsg(db));
		fprintf(stderr,"error_code:%d\n",sqlite3_errcode(db));
		return -1;
	}
	printf("sqlite3 close success\n");
	return 0;
}
int do_insert(sqlite3*db)
{
	int id_num;
	char name[20];
	float score;
	char sql[128]="";
	printf("请输入学号:");
	scanf("%d",&id_num);
	while(getchar()!=10);
	printf("请输入学生姓名:");
	fgets(name,sizeof(name),stdin);
	name[strlen(sql)-1]=0;
	printf("请输入学生成绩:");
	scanf("%f",&score);
	while(getchar()!=10);
	char*errmsg=NULL;
	sprintf(sql,"insert into stu values (%d,'%s',%f);",id_num,name,score);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		fprintf(stderr,"__%d__ sqlite3_close:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("插入完成\n");
}
int do_delete(sqlite3 *db)
{
	int id_num;
	printf("请输入要删除的学生学号:");
	scanf("%d",&id_num);
	while(getchar()!=10);
	char *errmsg=NULL;
	char sql[128]="";
	sprintf(sql,"delete from stu where id=%d",id_num);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		fprintf(stderr,"__%d__ sqlite3_close:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("删除成功\n");
}	
int do_update(sqlite3*db)
{
	int id_num;
	char name[20];
	float score;
	char *errmsg=NULL;
	char sql[128]="";
	printf("请输入要修改的学生学号:");
	scanf("%d",&id_num);
	while(getchar()!=10);
	while(1)
	{
		char c=0;
		printf("想要修改学号请按1,想修改姓名请按2,想修改成绩请按3:");
		c=getchar();
		while(getchar()!=10);
		switch(c)
		{
		case '1':printf("请输入新学号:");
				 scanf("%d",&id_num);
				 sprintf(sql,"update stu set id=%d where id=%d",id_num,id_num);
				 goto END;
		case '2':printf("请输入新的学生姓名:");
				 fgets(name,sizeof(name),stdin);
				 name[strlen(name)-1]=0;
				 sprintf(sql,"update stu set name='%s' where id=%d",name,id_num);
				 goto END;
		case '3':printf("请输入新成绩:");
				 scanf("%f",&score);
				 sprintf(sql,"update stu set score=%f where id=%d",score,id_num);
				 goto END;
		default:printf("输入错误，没有该学号的学生\n");
		}
	}
END:
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		fprintf(stderr,"__%d__ sqlite3_close:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("修改完成\n");
}

终端：