3.21作业

本文主要是介绍3.21作业，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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;
}