1、使用sqlite3实现简易的学生管理系统

#include<myhead.h>

//封装添加学生信息函数
int do_add(sqlite3 *ppDb)
{
	//准备sql语句
	int add_numb = 0;
	char add_name[20] = "";
	double add_socre = 0;

	printf("输入学号:");
	scanf("%d",&add_numb);
	printf("输入姓名:");
	scanf("%s",add_name);
	printf("输入成绩:");
	scanf("%lf",&add_socre);
	getchar();

	char sql[128]="";
	sprintf(sql,"insert into Stu values(%d,\"%s\",%lf);",add_numb,add_name,add_socre);

	//定义接收错误信息的变量
	char *errmsg = NULL;

	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("add error:%s\n",errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	printf("插入成功\n");

	return 0;

}

//删除学生信息
int do_delete(sqlite3 *ppDb)
{
	char delete_name[20] = "";
	
	printf("请输入要删除信息的学生的姓名:");
	scanf("%s",delete_name);
	getchar();

	char sql[128] = "";
	sprintf(sql,"delete from Stu where name=\"%s\";",delete_name);
	
	char *errmsg = NULL;

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

}

//修改学生信息
int do_update(sqlite3 *ppDb)
{
	char name[20] = "";
	int update_numb = 0;
	double update_score = 0;

	printf("请输入要修改信息的学生的姓名:");
	scanf("%s",name);
	printf("修改学号为:");
	scanf("%d",&update_numb);
	printf("修改成绩为:");
	scanf("%lf",&update_score);
	getchar();

	char sql[128]="";
	sprintf(sql,"update Stu set numb=%d,score=%lf where name=\"%s\";",update_numb,update_score,name);

	char *errmsg = NULL;
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("update error:%s\n",errmsg);
		sqlite3_free(errmsg);
		return -1;
	}

	printf("修改成功\n");
	return 0;
}

//定义回调函数处理查询后的结果集
int callback(void* arg,int cols,char **msgtext,char **msgheader)
{
	
	//输出所有数据
    //输出表头
	if(*((int *)arg) == 0)
	{
		for(int i=0;i<cols;i++)
		{
			printf("%s\t",*(msgheader+i));
		}
		printf("\n");
		(*((int *)arg))++;
	}

	//输出当前记录的内容
	for(int i=0;i<cols;i++)
	{
		printf("%s\t",*(msgtext+i));
	}
	printf("\n");

	return 0;
}

//定义查找函数
int do_search(sqlite3 *ppDb)
{
	char sql[128] = "select * from Stu;";

	//定义错误信息容器
	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;
	}
	return 0;
}

int do_search_1(sqlite3 *ppDb)
{
	char sql[128] = "select * from Stu;";
	char *errmsg = NULL;
	
	//准备接收结果集的相关变量
	char **retPtr = NULL;    //记录结果集的起始地址
	int rows = 0;             //记录结果集的行数
	int cols = 0;             //记录结果集的列数

	if(sqlite3_get_table(ppDb,sql,&retPtr,&rows,&cols,&errmsg) != SQLITE_OK)
	{
		printf("get_table error:%s\n",errmsg);
		sqlite3_free(errmsg);
		return -1;
	}

	//此时查询的结果集已经放入了retPtr作为起始地址的二维容器中
	for(int i=0;i<rows+1;i++)
	{
		for(int j=0;j<cols;j++)
		{
			printf("%s\t",*(retPtr+(i*cols)+j));
		}
		printf("\n");
	}
	sqlite3_free_table(retPtr);
	return 0;
}

/*****************主函数************************/
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 Stu(numb int, 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============XXX 学生管理系统=============\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:
			{
				do_add(ppDb);
			}
			break;
		case 2:
			{
				do_delete(ppDb);
			}
			break;
		case 3:
			{
				do_update(ppDb);
			}
			break;
		case 4:
			{
				do_search(ppDb);
				//do_search_1(ppDb);
			}
			break;
		case 0:goto END;
		default:printf("您输入的功能有误，请重新输入!!!!\n");
		}

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

END:

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

	return 0;
}

2、思维导图