使用SQL制作一个学生信息管理系统

#include<head.h>
int do_add(sqlite3 *ppDb);
int do_select(sqlite3 *ppDb);
int callback(void *arg,int cols,char **col_text,char **col_name);
int do_update(sqlite3 *ppDb);
int do_delete(sqlite3 *ppDb);
int main(int argc, const char *argv[])
{
	//打开或创建数据库
	sqlite3 *ppDb=NULL;//创建打开数据库句柄指针
	int rtsqlite3_open=sqlite3_open("test.db",&ppDb);//不存在test.db则创建
	if(rtsqlite3_open!=SQLITE_OK){//错误信息输出
		printf("sqlite3_open error:%d,%s\n",sqlite3_errcode(ppDb),sqlite3_errmsg(ppDb));//
		return -1;
	}
	printf("数据库打开成功\n");
	//对数据库进行相关操作
	//创建数据表
	//1、准备SQL语句
	char sql[128]="create table if not exists Stu(numb int primary key,name char,score double);";//创建数据表语句
	char *errmsg=NULL;//用于接收错误信息
	if((sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
		printf("Stu creat fail:%s\n",errmsg);
		sqlite3_free(errmsg);//释放空间
		return -1;
	}
	printf("Stu creat success\n");
	//菜单
	int option;
	while(1){
		//system("clear");//清屏
		printf("=====xxxx学生信息管理系统=====\n");
		printf("=====1>>添加学生信息==========\n");
		printf("=====2>>删除学生信息==========\n");
		printf("=====3>>修改学生信息==========\n");
		printf("=====4>>查找学生信息==========\n");
		printf("=====5>>退出信息系统==========\n");
		printf("enter option>>");
		scanf("%d",&option);
		getchar();
		switch(option){
			case 1 :
				{
					do_add(ppDb);//添加学生信息
				}
				break;
			case 2 :
				{
					do_delete(ppDb);//删除学生信息
				}
				break;
			case 3 :
				{
					do_update(ppDb);//修改学生信息
				}
				break;
			case 4 :
				{
					do_select(ppDb);//查找学生信息
				}
				break;
			case 5 :
				{
					goto END;//退出信息系统
				}
			default : printf("enter option error,please reenter\n");
		}
	}
END:
	sqlite3_close(ppDb);//关闭数据库
	return 0;
} 
int do_add(sqlite3 *ppDb){
	//准备sql语句
	char sql[128]={0};
	int numb=0;
	char name[20]={0};
	double score=0;
	printf("pelses enter:\n");
	printf("numb:");
	scanf("%d",&numb);
	printf("name:");
	scanf("%s",name);
	name[strlen(name)]=0;
	printf("score:");
	scanf("%lf",&score);
	getchar();
	snprintf(sql,sizeof(sql),"insert into Stu values(%d,\"%s\",%.2lf);",numb,name,score);//转为字符串存放到sql中
	char *errmsg=NULL;//用于接收错误信息
	if((sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
		printf("Stu creat fail:%s\n",errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	printf("insert success\n");
}
int do_select(sqlite3 *ppDb){
	printf("=====>>A展示全部=====\n");
	printf("=====>>P展示一条=====\n");
	printf("please enter>>");
	int flag=0;
	char op=getchar();
	getchar();
	char *errmsg=NULL;//用于接收错误信息
	if(op=='P'){
		
		char name[20]={0};
		printf("请输入查找的姓名:");
		scanf("%s",name);
		getchar();
		char sq[128]={0};
		snprintf(sq,sizeof(sq),"select * from Stu where name=\"%s\";",name);
		if((sqlite3_exec(ppDb,sq,callback,&flag,&errmsg))!=SQLITE_OK){
			printf("select fail:%s\n",errmsg);
			sqlite3_free(errmsg);
			return -1;
		}
		printf("show success\n");
	}else if(op=='A'){
		char sql[128]="select * from Stu;";
		if((sqlite3_exec(ppDb,sql,callback,&flag,&errmsg))!=SQLITE_OK){
			printf("select fail:%s\n",errmsg);                      
			sqlite3_free(errmsg);
			return -1;
		}
		printf("show success\n");
	}else{
		printf("enter error,please reenter\n");
	}
}
int callback(void *arg,int cols,char **col_text,char **col_name){
	if(*(int*)arg==0){
		for(int i=0;i<cols;i++){//展示表头
			printf("%s\t",*(col_name+i));
		}
		*(int*)arg=1;
		putchar(10);
	}
	for(int j=0;j<cols;j++){//展示内容
		printf("%s\t",col_text[j]);
	}
	printf("\n");
	return 0;
}
int do_update(sqlite3 *ppDb){
	char *errmsg=NULL;//用于接收错误信息
	char sql[128]={0};
	printf("=====>>S按学号修改=====\n");
	printf("=====>>N按姓名修改=====\n");
	printf("please enter>>");
	char ch=getchar();
	getchar();
	if(ch=='S'){
		printf("=====>>Q修改学生姓名=====\n");
		printf("=====>>W修改学生成绩=====\n");
		printf("please enter>>");
		char C=getchar();
		getchar();
		printf("请输入需要修改信息的学号：");
		int n;                                
		scanf("%d",&n);
		getchar();
		switch(C){
		case 'Q' :
			{
				printf("请输入修改后的姓名:");
				char newname[20]={0};
				scanf("%s",newname);
				getchar();
				snprintf(sql,sizeof(sql),"update Stu set name=\"%s\" where numb=%d;",newname,n);
				if((sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
					printf("update fail:%s\n",errmsg);
					sqlite3_free(errmsg);//释放空间
					return -1;
				}
				printf("update success\n");
			}
			break;
		case 'W':
			{
				printf("请输入修改后的成绩：");
				double sc;
				scanf("%lf",&sc);
				getchar();
				snprintf(sql,sizeof(sql),"update Stu set score=%lf where numb=%d;",sc,n);
				if((sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
					printf("update fail:%s\n",errmsg);
					sqlite3_free(errmsg);//释放空间
					return -1;
				}
				printf("update success\n");
			}
			break;
		default : printf("enter error,please reenter\n");break;
		}
	}
	if(ch=='N'){
		printf("=====>>J修改学生学号=====\n");
		printf("=====>>K修改学生成绩=====\n");
		printf("please enter>>");
		char cc=getchar();
		getchar();
		printf("请输入需要修改信息的姓名:");
		char nn[20]={0};
		scanf("%s",nn);
		getchar();
		switch(cc){
		case 'K' :
			{
				printf("请输入修改后的成绩:");
				double scc;
				scanf("%lf",&scc);
				getchar();
				snprintf(sql,sizeof(sql),"update Stu set score=%lf where name=\"%s\";",scc,nn);
				if((sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
					printf("update fail:%s\n",errmsg);
					sqlite3_free(errmsg);//释放空间
					return -1;
				}
				printf("update success\n");
			}
			break;
		case 'J':
			{
				printf("请输入修改后的学号:");
				int number;
				scanf("%d",&number);
				getchar();
				snprintf(sql,sizeof(sql),"update Stu set numb=%d where name=\"%s\";",number,nn);
				if((sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
					printf("update fail:%s\n",errmsg);
					sqlite3_free(errmsg);//释放空间
					return -1;
				}
				printf("update success\n");
			}
			break;
		default : printf("enter error,please reenter\n");break;
		}

	}
}
int do_delete(sqlite3 *ppDb){
	printf("=====>>D按学号删除=====\n");
	printf("=====>>F按姓名删除=====\n");
	char *errmsg=NULL;//用于接收错误信息
	char sql[128]={0};
	printf("enter>>:");
	char chh=getchar();
	getchar();
	if(chh=='D'){
		printf("请输入需要删除的学号:");
		int num;
		scanf("%d",&num);
		getchar();
		snprintf(sql,sizeof(sql),"delete from Stu where numb=%d;",num);
		if((sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
			printf("delete fail:%s\n",errmsg);
			sqlite3_free(errmsg);//释放空间
			return -1;
		}
		printf("delete success\n");
	}else if(chh=='F'){
		printf("请输入需要删除的姓名:");                             
		char name1[20]={0};
		scanf("%s",name1);
		getchar();
		snprintf(sql,sizeof(sql),"delete from Stu where name=\"%s\";",name1);
		if((sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg))!=SQLITE_OK){
			printf("default fail:%s\n",errmsg);
			sqlite3_free(errmsg);//释放空间
			return -1;
		}   
		printf("delete success\n"); 
	}

}