编写员工管理系统(含有增删改功能)
#include <myhead.h>
int main(int argc, const char *argv[])
{
//定义数据库句柄指针
sqlite3 * ppDb = NULL;
//打开数据库,如果数据库不存在,则创建数据库
//将数据库句柄由参数2返回
if(sqlite3_open("./my.db", &ppDb)!= SQLITE_OK)
{
printf("sqlite3_open error,errcode=%d,errmsg=%s\n",\
sqlite3_errcode(ppDb),sqlite3_errmsg(ppDb));
return -1;
}
printf("sqlite3_open success\n");
//创建数据表
//1、准备sql语句
char sql[128]="create table if not exists Worker(nummb int, name char, salary double)";
//char *sql="create table if not exists Worker(nummb int, name char, salary double)";
char *errmsg = NULL; //存放执行sql语句的错误信息
if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!= SQLITE_OK)
{
printf("exec error : %s\n",errmsg);
errmsg = NULL;
return -1;
}
printf("员工信息表创建成功\n");
int menu =-1;
for(;;)
{
BACK:
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=========0、退出========\n");
printf("请输入操作码:");
scanf("%d",&menu);
getchar();
switch(menu)
{
case 1:
{
//do_add(ppDb)
int nummb;
char name[50];
double salary;
char sql1[256];
printf("请输入员工的工号:");
scanf("%d",&nummb);
getchar();
printf("请输入员工的姓名:");
fgets(name,50,stdin);
name[strlen(name)-1]=0;
printf("请输入员工的薪资:");
scanf("%le",&salary);
snprintf(sql1,sizeof(sql1),"insert into Worker values (%d, '%s',%lf)",nummb,name,salary);
char *errmsg = NULL; //存放执行sql语句的错误信息
if(sqlite3_exec(ppDb,sql1,NULL,NULL,&errmsg)!= SQLITE_OK)
{
printf("exec error : %s\n",errmsg);
errmsg = NULL;
return -1;
}
printf("员工信息输入成功\n");
}
break;
case 2:
{
int nummb;
char sql1[256];
printf("请输入要删除员工的工号:");
scanf("%d",&nummb);
getchar();
snprintf(sql1,sizeof(sql1),"delete from Worker where nummb=%d",nummb);
char *errmsg = NULL; //存放执行sql语句的错误信息
if(sqlite3_exec(ppDb,sql1,NULL,NULL,&errmsg)!= SQLITE_OK)
{
printf("exec error : %s\n",errmsg);
errmsg = NULL;
return -1;
}
printf("员工信息删除成功\n");
}
break;
case 3:
{
//do_change(ppDb)
printf("1.修改工号\n2.修改姓名\n3.修改薪资\n0.返回上一步\n");
printf("请输入操作码:");
int num=1;
scanf("%d",&num);
getchar();
switch(num)
{
case 1:
{
//修改工号
int oldnummb;
int newnummb;
char sql1[256];
printf("请输入要修改员工的工号:");
scanf("%d",&oldnummb);
getchar();
printf("请输入修改后的工号:");
scanf("%d",&newnummb);
getchar();
snprintf(sql1,sizeof(sql1),"update Worker set nummb=%d where nummb=%d",newnummb,oldnummb);
char *errmsg = NULL; //存放执行sql语句的错误信息
if(sqlite3_exec(ppDb,sql1,NULL,NULL,&errmsg)!= SQLITE_OK)
{
printf("exec error : %s\n",errmsg);
errmsg = NULL;
return -1;
}
printf("员工信息修改成功\n");
}
break;
case 2:
{
//修改姓名
int oldnummb;
char name[50];
char sql1[256];
printf("请输入要修改员工的工号:");
scanf("%d",&oldnummb);
getchar();
printf("请输入修改后的姓名:");
fgets(name,50,stdin);
name[strlen(name)-1]=0;
snprintf(sql1,sizeof(sql1),"update Worker set name='%s' where nummb=%d",name,oldnummb);
char *errmsg = NULL; //存放执行sql语句的错误信息
if(sqlite3_exec(ppDb,sql1,NULL,NULL,&errmsg)!= SQLITE_OK)
{
printf("exec error : %s\n",errmsg);
errmsg = NULL;
return -1;
}
printf("员工信息修改成功\n");
}
break;
case 3:
{
//修改薪资
int oldnummb;
double salary;
char sql1[256];
printf("请输入要修改员工的工号:");
scanf("%d",&oldnummb);
getchar();
printf("请输入修改后的薪资:");
scanf("%le",&salary);
getchar();
snprintf(sql1,sizeof(sql1),"update Worker set salary=%le where nummb=%d",salary,oldnummb);
char *errmsg = NULL; //存放执行sql语句的错误信息
if(sqlite3_exec(ppDb,sql1,NULL,NULL,&errmsg)!= SQLITE_OK)
{
printf("exec error : %s\n",errmsg);
errmsg = NULL;
return -1;
}
printf("员工信息修改成功\n");
}
break;
case 0:
goto BACK;
default:printf("您输入的功能有误,请重新输入!!!!\n");
}
}break;
case 0:
goto END;
default:printf("您输入的功能有误,请重新输入!!!!\n");
}
printf("请输入任意键,按回车清屏\n");
while(getchar()!= '\n');
}
END:
//关闭数据库
sqlite3_close(ppDb);
return 0;
}
效果图