网络编程3.21作业

1.现象

1.增

2.删

3.改

4.查

2.源码

#include<myhead.h>

int callback(void* arg,int a,char **msgtext,char **msgheader){
	if(*(int *)arg==0){
		for(int i=0;i<a;i++){
			printf("%s\t",*(msgheader+i));
		}
		printf("\n");
	}
	for(int i=0;i<a;i++){
		printf("%s\t",*(msgtext+i));
	}
	printf("\n");

	return 0;
}

int seach(sqlite3 *ppDb){
	char sql[128]="select *from workman;";	
	char *errmsg=NULL;
	int flag=0;
	if(sqlite3_exec(ppDb, sql, &callback, &flag, &errmsg) != SQLITE_OK)
	{
		printf("exec error:%s\n", errmsg);

		sqlite3_free(errmsg);
		return -1;
	}
}


int insert(sqlite3 *ppDb){

	int num;
	char name[20]="";
	double score;
	char ch[128]="";
	char *errmsg=NULL;
	printf("请输入学号:");
	scanf("%d",&num);
	printf("请输入姓名:");
	scanf("%s",name);
	printf("请输入成绩:");
	scanf("%lf",&score);
	sprintf(ch,"insert into workman values(%d,\"%s\",%lf);",num,name,score);
	if(sqlite3_exec(ppDb,ch,NULL,NULL,NULL) != SQLITE_OK){
		printf("添加失败\n");
		return -1;
	}
	return 0;
}



int update(sqlite3 *ppDb){
	int num;
	char name[20]="";
	double score;
	char ch[128]="";
	printf("请输入待修改学生信息学号:");
	scanf("%d",&num);
	getchar();

	printf("请输入被修改后的成绩:");
	scanf("%lf",&score);
	getchar();
	sprintf(ch,"update workman set score=%lf where numb=%d;",score,num);
	if(sqlite3_exec(ppDb,ch,NULL,NULL,NULL)!=SQLITE_OK){
		printf("修改失败!");
	}
	printf("修改成功!");

		printf("更新后的学生信息库为:\n");
		seach(ppDb);

};



int delete(sqlite3 *ppDb){
	int menu=0;
	int num=0;
	char name[20]="";
	double score=0;
	char ch[128]="select name from workman;";

	printf("1.删除某一个学生信息(此操作不可撤回，请谨慎操作！)\n");
	printf("2.删除所有信息学生(此操作不可撤回，请谨慎操作！！！)\n");
	scanf("%d",&menu);
	getchar();
	switch(menu){

	case 1:
		printf("学生信息库里有以下学生:\n");
		seach(ppDb);
		printf("请输入待删除的学生学号:");
		scanf("%d",&num);
		getchar();
		sprintf(ch,"delete from workman where numb=%d;",num);
		if(sqlite3_exec(ppDb,ch,NULL,NULL,NULL)!=SQLITE_OK){
			printf("删除失败!!!\n");
			return -1;
		}
		printf("更新后的学生信息库为:\n");
		seach(ppDb);
		break;

	case 2:
		printf("学生信息库里有以下学生:\n");
		seach(ppDb);
		sprintf(ch,"delete from workman;");
		printf("是否删除:\n");
		printf("1.删除\n");
		printf("2.取消\n");
		scanf("%d",&num);
		getchar();
		if(num==1){
		if(sqlite3_exec(ppDb,ch,NULL,NULL,NULL)!=SQLITE_OK){
			printf("删除失败!!!\n");
			return -1;
		}
		printf("删除成功!\n");
		printf("更新后的学生信息库为:\n");
		seach(ppDb);
		}else{
			printf("取消成功\n");
		}
		break;
	default:
		printf("输入错误，请重新输入！\n");
		break;
	}
}
int main(int argc, const char *argv[])
{
	//1、定义数据库句柄指针
	sqlite3 *ppDb = NULL;

	//2、打开（创建）数据库
	if(sqlite3_open("./my.db", &ppDb) != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_open error:errmsg = %s, errcode = %d\n",\
				sqlite3_errmsg(ppDb), sqlite3_errcode(ppDb));
		return -1;
	}
	printf("sqlite3_open success\n");

	//3、创建数据库表
	//3.1 准备sql语句
	char sql[128] = "create table if not exists workman(numb int primary key, name char, score double);";
	//char *sql =  "create table if not exists Stu(numb int, name char, score double);";
	char *errmsg = NULL;      //用于接收出错时的错误信息

	//3.2 执行sql语句
	if(sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error:%s\n", errmsg);

		return -1;
	}
	printf("create table Stu success\n");

	//4、对数据表进行操作
	int menu = -1;
	while(1)
	{
		system("clear");
		printf("\t\t============= 学生管理系统==============\n");
		printf("\t\t============1、添加学生信息=============\n");
		printf("\t\t============2、删除学生信息=============\n");
		printf("\t\t============3、修改学生信息=============\n");
		printf("\t\t============4、查找学生信息=============\n");
		printf("\t\t============0、退出系统=================\n");
		printf("请输入功能:");
		scanf("%d", &menu);
		getchar();

		//对功能多分支选择
		switch(menu)
		{
		case 1:
			{
				insert(ppDb);
			}
			break;
		case 2:
			{
				delete(ppDb);
			}
			break;

		case 3:
			{
				update(ppDb);
			}
			break;
		case 4:
			{
				seach(ppDb);
			}
			break;
		case 0:goto END;
		default:printf("您输入的功能有误，请重新输入!!!!\n");
		}

		printf("请输入任意键，按回车清屏\n");
		while(getchar() != '\n');
	}



END:


	//关闭数据库
	sqlite3_close(ppDb);

	return 0;
}