Day6--网络编程

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
int do_insert(sqlite3 *db);
int do_delete(sqlite3 *db);
int do_update(sqlite3 *db);
int main(int argc, const char *argv[])
{
	if(argc<2){
		printf("输入错误,请重新输入\n");
		return -1;
	}
	//创建数据库
	sqlite3* db=NULL;
	if(sqlite3_open(argv[1],&db)!=SQLITE_OK){
		fprintf(stderr,"sqlite3_open:%s errcode:%d __%d__\n",\
				sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
		return -1;
	}

	//创建表格
	char ptr[128]="create table if not exists stu (id int, name char, score float);";
	char *str=NULL;
	if(sqlite3_exec(db,ptr,NULL,NULL,&str)!=SQLITE_OK){
		fprintf(stderr,"sqlite3_exec:%s errcode:%d __%d__\n",\
				sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
		return -1;
	}

	//建立菜单
	char c;
	while(1){
		system("clear");
		printf("*********1.增*********\n");
		printf("*********2.删*********\n");
		printf("*********3.改*********\n");
		printf("*********4.查*********\n");
		printf("*********5.退出*******\n");
		printf("请输入序号>>>");
		scanf(" %c",&c);
		switch(c){
			case '1':
				do_insert(db);
				break;
			case '2':
				do_delete(db);
				break;
			case '3':
				do_update(db);
				break;
			case '4':
				//do_search();
				break;
			case '5':
				goto END;
				break;
			default:
				printf("输入错误请重新输入\n");
				break;
		}
	}  
END:
	//关闭数据库	
	if(sqlite3_close(db)!=SQLITE_OK){
		fprintf(stderr,"sqlite3_close:%s errcode:%d __%d__\n",\
				sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
		return -1;
	}
	return 0;
}
/*
 * function:    增
 * @param [ in] 数据库的首地址
 * @param [out] 
 * @return      成功返回0,失败返回-1;
 */
int do_insert(sqlite3 *db){
	char buf[128]="";
	char name[128]="";
	float score;
	int id;
	printf("请输入需要插入的数据>>>");
	scanf("%d %s %f",&id,name,&score);
	sprintf(buf,"insert into stu values (%d,%s,%f)",id,name,score);
	char *str=NULL;
	if(sqlite3_exec(db,buf,NULL,NULL,&str)!=SQLITE_OK){
		fprintf(stderr,"sqlite3_exec:%s errcode:%d __%d__\n",\
				sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
		return -1;
	}
	return 0;
}

/*
 * function:    删
 * @param [ in] 数据库首地址
 * @param [out] 
 * @return      成功返回0,失败返回-1
 */
int do_delete(sqlite3 *db){
	char buf[128]="";
	char name[128]="";
	char *str=NULL;
	float score;
	int id;
	char c;
	printf("*****1.根据id删除*****\n");
	printf("*****2.根据name删除***\n");
	printf("*****3.根据score删除**\n");
	printf("*****4.删除全部数据***\n");
	printf("*****5.退出***********\n");
	while(1){
		printf("请输入序号>>>");
		scanf(" %c",&c);
		switch(c){
			case '1':
				{
					bzero(buf,sizeof(buf));
					printf("请输入id>>>");
					scanf("%d",&id);
					sprintf(buf,"delete from stu where id=%d",id);
					if(sqlite3_exec(db,buf,NULL,NULL,&str)!=SQLITE_OK){
						fprintf(stderr,"sqlite3_exec:%s errcode:%d __%d__\n",\
								sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
						return -1;
					}
				}	
				break;
			case '2':
				{
					bzero(buf,sizeof(buf));
					bzero(name,sizeof(name));
					printf("请输入name>>>");
					scanf("%s",name);
					sprintf(buf,"delete from stu where name=%s",name);
					if(sqlite3_exec(db,buf,NULL,NULL,&str)!=SQLITE_OK){
						fprintf(stderr,"sqlite3_exec:%s errcode:%d __%d__\n",\
								sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
						return -1;
					}
				}	
				break;
			case '3':
				{
					bzero(buf,sizeof(buf));
					printf("请输入score>>>");
					scanf("%f",&score);
					sprintf(buf,"delete from stu where score=%f",score);
					if(sqlite3_exec(db,buf,NULL,NULL,&str)!=SQLITE_OK){
						fprintf(stderr,"sqlite3_exec:%s errcode:%d __%d__\n",\
								sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
						return -1;
					}
				}	
				break;
			case '4':
				{
					bzero(buf,sizeof(buf));
					sprintf(buf,"delete from stu");
					if(sqlite3_exec(db,buf,NULL,NULL,&str)!=SQLITE_OK){
						fprintf(stderr,"sqlite3_exec:%s errcode:%d __%d__\n",\
								sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
						return -1;
					}
				}	
				break;
			case '5':
				goto END;
				break;
			default:
				printf("输入错误请重新输入\n");
				break;
		}
	}
END:
	return 0;
}

/*
 * function:    改
 * @param [ in] 数据库首地址
 * @param [out] 
 * @return      成功返回0，失败返回-1
 */
int do_update(sqlite3 *db){
	char buf[128]="";
	char *str=NULL;
	int id;
	char name[128]="";
	float score;
	char c;
	printf("*****1.根据id修改score*****\n");
	printf("*****2.根据name修改score***\n");
	printf("*****3.退出****************\n");
	while(1){
		printf("请输入序号>>>");
		scanf(" %c",&c);
		switch(c){
			case '1':
				bzero(buf,sizeof(buf));
				printf("请输入id和需要修改的score>>>");
				scanf("%d %f",&id,&score);
				sprintf(buf,"update stu set score=%f where id=%d",score,id);
				if(sqlite3_exec(db,buf,NULL,NULL,&str)!=SQLITE_OK){
					fprintf(stderr,"sqlite3_exec:%s errcode:%d __%d__\n",\
							sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
					return -1;
				}
				break;
			case '2':
				bzero(buf,sizeof(buf));
				bzero(name,sizeof(name));
				printf("请输入name和需要修改的score>>>");
				scanf("%s %f",name,&score);
				sprintf(buf,"update stu set score=%f where name=%s",score,name);
				if(sqlite3_exec(db,buf,NULL,NULL,&str)!=SQLITE_OK){
					fprintf(stderr,"sqlite3_exec:%s errcode:%d __%d__\n",\
							sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
					return -1;
				}
				break;
			case '3':
				goto END;
				break;
			default:
				printf("输入错误请重新输入\n");
				break;
		}
	}
END:
	return 0;
}