#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");
}
}
06-15
1679
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
12-27
1120
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)