利用数据库执行的超市管理系统

一、超市管理系统

        采用数据库（sqlite3）对数据进行增删改查处理。

        1）主函数 提前创好的数据库

#include "../include/sql.h"void menu_1()
{puts("**********超市*********");puts("*                    *");puts("*------(1)管理员------*");puts("*------(2)用户--------*");puts("*------(0)退出--------*");puts("*                    *");puts("**********************");
}
void menu_4()
{puts("**********************管理系统***********************");puts("*                                                  *");puts("*-------------------(1)登录-------------------------*");puts("*-------------------(2)注册-------------------------*");puts("*-------------------(3)离职-------------------------*");puts("*-------------------(4)修改密码----------------------*");puts("*-------------------(0)返回-------------------------*");puts("*                                                  *");puts("****************************************************");
}int main()
{//打开数据库sqlite3 *db = NULL;char dbname[10] = "SQL.db";int rc;rc = sqlite3_open(dbname,&db);if (rc != SQLITE_OK){perror("Open Error");return -1;}//定义管理员结构体 用来存储用户名和密码UP user;//用来存储创建表的名字//管理员信息char usertable[5] = "user";//商品信息char goodstable[10] = "goods";//用户选择int op = 0;//子函数返回值int ret = 0;while (1){lable:menu_1();puts("请输入选项:");scanf("%d", &op);if (0 == op)break;switch (op){case 1: //管理员{while (1){menu_4();memset(&user, '\0', sizeof(user));puts("尊敬的管理员请输入您的选项:");scanf("%d", &op);if (0 == op)goto lable;switch (op){case 1: //登录{printf("请输入用户名:");scanf("%s", user.user);printf("请输入密码:");scanf("%s", user.passwd);//查找数据库检测次管理员是否存在ret = select_table(db, usertable, &user);if (ret == NOEXIST){puts("用户不存在或者密码错误");break;}else if (ret == EXIST){puts("登录成功");int ret_t = 0;//调用管理员管理商品ret_t = manger_control(db, goodstable);if(999 == ret_t){sqlite3_close(db);return 0;}}break;}case 2: //注册{printf("请输入用户名:");scanf("%s", user.user);printf("请输入密码:");scanf("%s", user.passwd);//查找数据库检测次管理员是否存在ret = select_table(db, usertable, &user);if (ret == NOEXIST){//不存在则添加insert_table(db, usertable, &user);}else if (ret == EXIST){puts("管理员已经存在，添加失败！");break;;}break;}case 3: //离职{printf("请输入用户名:");scanf("%s", user.user);printf("请输入密码:");scanf("%s", user.passwd);//查找数据库检测次管理员是否存在ret = select_table(db, usertable, &user);if (ret == NOEXIST){puts("用户不存在或者密码错误");break;}else if (ret == EXIST){//存在则删除delete_table(db, usertable, &user);}break;}case 4: //修改密码{printf("请输入用户名:");scanf("%s", user.user);printf("请输入密码:");scanf("%s", user.passwd);//查找数据库检测次管理员是否存在ret = select_table(db, usertable, &user);if (ret == NOEXIST){puts("用户不存在或者密码错误");break;}else if (ret == EXIST){//存在则更新密码update_table(db, usertable, &user);}break;}default:puts("输入错误，请重新输入");	break;}}}case 2: //用户{int ret_t = 0;//调用管理员管理商品ret_t = users_buy(db, goodstable);if(999 == ret_t){sqlite3_close(db);return 0;}break;}}}//关闭数据库sqlite3_close(db);return 0;
}

        2）管理员管理系统

#include "../include/sql.h"void menu_2()
{puts("********************商品管理系统*******************");puts("*                                                 *");puts("*-------------------(1)添加商品-------------------*");puts("*-------------------(2)查找商品-------------------*");puts("*-------------------(3)显示商品-------------------*");puts("*-------------------(4)修改商品-------------------*");puts("*-------------------(5)删除商品-------------------*");puts("*-------------------(0)退出系统-------------------*");puts("*                                                 *");puts("***************************************************");
}int manger_control(sqlite3 *dbname, char *tbname)
{int op = 0;int ret = 0;char *goods_table = tbname;sqlite3 *db = dbname;//定义商品结构体GD goods;//定义查询结构体NC check;while (1){menu_2();puts("尊敬的管理员请输入您的选项");scanf("%d", &op);if(0 == op) break;switch (op){case 1:  //添加商品{ret = insert_goods(db, goods_table);if(ret < 0){puts("添加商品失败");break;}puts("添加商品成功");break;}case 2:  //查找商品{puts("1-按照商品编号查找:");puts("2-按照商品名字查找:");scanf("%d", &op);switch(op){case 1:{puts("请输入所要查找的商品编号:");scanf("%d",&goods.number);//是否查到要显示 显示则为1 不显示则为0check.check_show = 1;//为0 按照编号 为1 按照名称check.check_way = 0;ret = select_goods(db, goods_table, &goods, &check);if(ret == NOEXIST){puts("没有找到商品");break;}puts("商品信息如上");break;}case 2:{puts("请输入所要查找的商品名字:");scanf("%s",goods.name);//是否查到要显示 显示则为1 不显示则为0check.check_show = 1;//按照名字查找 为0 则按照编号 为1 则按照名字查找check.check_way = 1;ret = select_goods(db, goods_table, &goods, &check);if(ret == NOEXIST){puts("没有找到商品");break;}puts("商品信息如上");break;	}}break;}case 3:	 //显示商品{ret = show_goods_alldata(db, goods_table);if(ret == NOEXIST){puts("没有商品");break;}puts("商品信息如上");break;}case 4:	 //修改商品{puts("请输入所要修改商品的编号:");scanf("%d",&goods.number);//是否查到要显示 显示则为1 不显示则为0check.check_show = 1;//为0 按照编号 为1 按照名称查找check.check_way = 0;ret = select_goods(db, goods_table, &goods, &check);if(ret == NOEXIST){puts("没有找到商品");break;}else{ret = update_goods(db, goods_table, &goods);if(ret < 0){ puts("修改失败");break;}}break;}case 5:	 //删除商品{puts("请输入所要删除改商品的编号:");scanf("%d",&goods.number);//是否查到要显示 显示则为1 不显示则为0check.check_show = 1;//为0 按照编号 为1 按照名称查找check.check_way = 0;ret = select_goods(db, goods_table, &goods, &check);if(ret == NOEXIST){puts("没有找到商品");break;}else{puts("1-确认删除");puts("2-取消删除");int option = 0;scanf("%d",&option);switch(option){case 1:{ret = delete_goods(db, goods_table, &goods);if(ret < 0){puts("删除失败");break;}puts("删除成功");break;}case 2:{break;}}}break;}default:puts("输入错误，请重新输入！");}}return 999;
}

        3）用户购买商品

#include "../include/sql.h"void menu_3()
{puts("**********************用户系统**********************");puts("*                                                 *");puts("*-------------------(1)购买商品--------------------*");puts("*-------------------(2)显示商品--------------------*");puts("*-------------------(0)退出系统--------------------*");puts("*                                                 *");puts("***************************************************");
}int users_buy(sqlite3 *dbname, char *tbname)
{if (NULL == dbname || NULL == tbname){puts("NULL ERROR");return -1;}//用户的选择int op = 0;int option = 0;int option_1 = 0;//用户购买商品的数量int amount = 0;//用户所需支付的金额float money = 0;//定义用户想要查询的商品GD goods;//用来接收用户选择返回值int ret = 0;//定义商品查询结构体NC check;while (1){menu_3();puts("尊敬的用户请输入您的选项");scanf("%d", &op);if (0 == op)break;switch (op){case 1: //购买商品{puts("尊敬的用户请输入您想要购买的商品名称:");scanf("%s", goods.name);if (strcmp(goods.name, "退出") == 0){break;}//调用名字查找商品函数check.check_show = 1; //显示check.check_way = 1; //按照名字查找if (NOEXIST == select_goods(dbname, tbname, &goods, &check)){puts("尊敬的用户抱歉，该商品不存在");puts("如需退出查询请输入:退出 或继续输入商品名称");continue;}puts("是否需要购买此商品:");puts("1-购买商品");puts("2-不购买此商品");scanf("%d", &option);switch (option){//购买商品case 1:{puts("请用户您输入购买数量");label:scanf("%d", &amount);//如果输入商品数量大于存货 或者存货为0//则输入失败if (goods.amount < amount || goods.amount == 0 ||amount < 0){puts("商品数量达不到您的需求请重新输入数量");goto label;break;}///如果数量满足需求则显示所需金额else{//计算金额money = (float)amount * goods.price;//减小商品存货goods.amount = goods.amount - amount;printf("所需支付金额为:%.2f\n", money);puts("是否确认购买此商品");puts("1-确认购买");puts("2-取消购买");scanf("%d", &option_1);switch (option_1){case 1:ret = 999;break;case 2:ret = 111;break;}}break;}case 2:{ret = 111;break;}}if (999 == ret){puts("此次购买商品成功！");char sql[100] = {'\0'};sprintf(sql,"update %s set amount = %d where number = %d" \,tbname, goods.amount, goods.number);int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(dbname,sql,NULL,NULL,&errmsg);if(ret != SQLITE_OK){printf("Update Error:%s\n",errmsg);return -1;}break;}else if (111 == ret){puts("此次交易结束!");break;}}case 2:{ret = show_goods_alldata(dbname, tbname);if(ret == NOEXIST){puts("没有商品");break;}puts("商品信息如上");break;}}}return 999;
}

        4）添加数据

#include "../include/sql.h"//在表格内插入数据
int insert_table(sqlite3 *db, char *tbname, UP *user)
{if(NULL == db || NULL == tbname){perror("NULL ERROR");return -1;}//添加的名字和密码char name[20] = {'\0'};int age = 0;printf("请输入新管理员用户的姓名:");scanf("%s",name);printf("请输入新管理员用户的年龄:");scanf("%d",&age);//定义字符数组用来存放数据库插入命令char sql[100] = {'\0'};sprintf(sql, "insert into %s values('%s','%s','%s',%d)",tbname, user->user, \user->passwd, name, age);int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);if(ret != SQLITE_OK){printf("Insert Error:%s\n",errmsg);return -1;}puts("Insert OK");return 0;
}
//添加商品
int insert_goods(sqlite3 *db, char *tbname)
{if(NULL == db || NULL == tbname){perror("NULL ERROR");return -1;}//添加商品信息GD goods;printf("请输入添加的商品编号:");scanf("%d",&goods.number);//定义查询结构体NC check;//查询到不显示信息check.check_show = 0;//为0 按照编号查找check.check_way = 0;if(EXIST == select_goods(db, tbname, &goods, &check)){printf("商品已存在\n");return -1;}printf("请输入添加的商品名称:");scanf("%s",goods.name);printf("请输入添加的商品价格:");scanf("%f",&goods.price);printf("请输入添加的商品数量:");scanf("%d",&goods.amount);//判断是否输入正确if(goods.number < 0 || goods.price < 0 || goods.amount < 0){puts("商品信息输入错误!");return -1;}//定义字符数组用来存放数据库插入命令char sql[100] = {'\0'};sprintf(sql, "insert into %s values(%d,'%s',%f,%d)",tbname, goods.number, \goods.name, goods.price, goods.amount);int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);if(ret != SQLITE_OK){printf("Insert Error:%s\n",errmsg);return -1;}return 0;
}

        5）查找数据

#include "../include/sql.h"
//查看管理员信息
int show_user_table(void *arg, int columnNum, char *columnValue[], char *columnName[]);
//不显示表格数据
int unshow_goodsdata(void *arg, int columnNum, char *columnValue[], char *columnName[]);
//显示表格数据
int show_goodsdata(void *arg, int columnNum, char *columnValue[], char *columnName[]);
//显示表格数据
int get_goods_data(void *arg, int columnNum, char *columnValue[], char *columnName[]);//匹配查找管理员信息
int select_table(sqlite3 *db, char *tbname,UP *user)
{	if(NULL == db || NULL == tbname){perror("NULL ERROR");return -1;}//定义回调函数参数VA values;//判断是否查到信息values.i = 0;char sql[150] = {'\0'};sprintf(sql,"select * from %s where username = '%s' and pwd = '%s'", tbname, \user->user, user->passwd);int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(db, sql, show_user_table, &values, &errmsg);if(ret != SQLITE_OK){printf("Select Error:%s\n", errmsg);return -1;}puts("");//如果没查到则参数值不变if(values.i == 0){return NOEXIST;}else if(values.i != 0)//如果查到则参数值发生改变{return EXIST;}return 0;
}//匹配查找商品信息
int select_goods(sqlite3 *db, char *tbname, GD *goods, NC *check)
{	if(NULL == db || NULL == tbname || NULL == goods || NULL == check){perror("NULL ERROR");return -1;}//定义回调函数参数VA values;//判断是否查到信息values.i = 0;char sql[120] = {'\0'};int ret = -1;char *errmsg = NULL;//为0按照编号查找if(0 == check->check_way){sprintf(sql,"select * from %s where number = %d", tbname, \goods->number);}//为1按照名字查找else if(1 == check->check_way){sprintf(sql,"select * from %s where name = '%s'", tbname, \goods->name);//获取查找后的值sqlite3_exec(db, sql, get_goods_data, &values, &errmsg);goods->number = values.number;goods->price = values.price;goods->amount = values.amount;}//如果为0则不许显示信息只是查询if(0 == check->check_show){ret = sqlite3_exec(db, sql, unshow_goodsdata, &values, &errmsg);}//如果为1则查询并显示信息else if(1 == check->check_show){ret = sqlite3_exec(db, sql, show_goodsdata, &values, &errmsg);}if(ret != SQLITE_OK){printf("Select Error:%s\n", errmsg);return -1;}//如果没查到则参数值不变if(values.i == 0){return NOEXIST;}else if(values.i != 0)//如果查到则参数值发生改变{return EXIST;}return 0;
}
//显示所有商品信息
//匹配查找商品信息
int show_goods_alldata(sqlite3 *db, char *tbname)
{	if(NULL == db || NULL == tbname){perror("NULL ERROR");return -1;}//定义回调函数参数VA values;//判断是否查到信息values.i = 0;char sql[120] = {'\0'};sprintf(sql,"select * from %s", tbname);int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(db, sql, show_goodsdata, &values, &errmsg);if(ret != SQLITE_OK){printf("Select Error:%s\n", errmsg);return -1;}//如果没查到则参数值不变if(values.i == 0){return NOEXIST;}else if(values.i != 0)//如果查到则参数值发生改变{return EXIST;}return 0;
}//回调函数判断是否查到 不显示信息
//回调函数
int unshow_goodsdata(void *arg, int columnNum, char *columnValue[], char *columnName[])
{//强转VA *values = (VA *)arg;(values->i)++;return 0;
}
//回调函数获取查到商品的值
int get_goods_data(void *arg, int columnNum, char *columnValue[], char *columnName[])
{//强转VA *values = (VA *)arg;//输出列名if(values->i == 0){for(int i = 0; i < columnNum; i++){printf("%s\t",columnName[i]);}puts("");}(values->i)++;values->number = (atoi)(columnValue[0]);values->price = (float)((atof)(columnValue[2]));values->amount = (atoi)(columnValue[3]);return 0;
}
//回调函数判断是否查到 显示信息
int show_goodsdata(void *arg, int columnNum, char *columnValue[], char *columnName[])
{//强转VA *values = (VA *)arg;//输出列名if(values->i == 0){for(int i = 0; i < columnNum; i++){printf("%s\t",columnName[i]);}puts("");}//加一确保查到数据(values->i)++;//输出显示商品信息for(int i = 0; i < columnNum; i++){	printf("%s\t",columnValue[i]);}puts("");return 0;
}//回调函数查找管理员
int show_user_table(void *arg, int columnNum, char *columnValue[], char *columnName[])
{//强转VA *values = (VA *)arg;//输出列名if(values->i == 0){for(int i = 0; i < columnNum; i++){printf("%s\t",columnName[i]);}puts("");}//加一确保查到数据(values->i)++;//输出管理员信息for(int i = 0; i < columnNum; i++){	printf("%s\t",columnValue[i]);}return 0;
}

        6）删除数据

#include "../include/sql.h"//在表格内删除管理员数据
int delete_table(sqlite3 *db, char *tbname, UP *user)
{if(NULL == db || NULL == tbname){perror("NULL ERROR");return -1;}//定义字符数组用来存放数据库插入命令char sql[100] = {'\0'};sprintf(sql, "delete from %s where username = %s",tbname, user->user);int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);if(ret != SQLITE_OK){printf("Delete Error:%s\n",errmsg);return -1;}puts("Delete OK");return 0;
}
//删除商品信息
int delete_goods(sqlite3 *db, char *tbname, GD *goods)
{if(NULL == db || NULL == tbname || NULL == goods){perror("NULL ERROR");return -1;}//定义字符数组用来存放数据库插入命令char sql[100] = {'\0'};sprintf(sql, "delete from %s where number = %d",tbname, goods->number);int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);if(ret != SQLITE_OK){printf("Delete Error:%s\n",errmsg);return -1;}return 0;
}

        7）更新数据

#include "../include/sql.h"int update_order(sqlite3 *db, char *tbname, char *buf);
//修改管理员密码
int update_table(sqlite3 *db, char *tbname, UP *user)
{if(NULL == db || NULL == tbname){perror("NULL ERROR");return -1;}//修改后的密码char passwd[30] = {'\0'};puts("请输入要修改后的密码:");scanf("%s",passwd);char sql[100] = {'\0'};sprintf(sql,"update %s set pwd = '%s' where username = %s" \,tbname, passwd, user->user);int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);if(ret != SQLITE_OK){printf("Update Error:%s\n",errmsg);return -1;}puts("Update Success");return 0;
}
//修改商品信息
int update_goods(sqlite3 *db, char *tbname, GD *goods)
{if(NULL == db || NULL == tbname || NULL == goods){perror("NULL ERROR");return -1;}char sql[100] = {'\0'};//开始选择信息进行修改while(1){puts("1-修改商品的价格");puts("2-修改商品的数量");puts("3-查看商品信息");puts("4-确认修改信息完成");int option = 0;scanf("%d",&option);if(4 == option){break;}switch(option){//修改价格case 1:{printf("修改价格为:");scanf("%f",&goods->price);sprintf(sql,"update %s set price = %f where number = %d" \,tbname, goods->price, goods->number);update_order(db, tbname, sql);break;}//修改数量case 2:{printf("修改数量为:");scanf("%d",&goods->amount);sprintf(sql,"update %s set amount = %d where number = %d" \,tbname, goods->amount, goods->number);update_order(db, tbname, sql);break;}//输出改变之后的商品信息case 3:{NC check;check.check_show = 1;check.check_way = 0;select_goods(db, tbname, goods, &check);break;}default:{puts("输入错误，请重新输入选择");}}}return 0;
}
//修改商品信息命令函数
int update_order(sqlite3 *db, char *tbname, char *buf)
{if(NULL == db || NULL == tbname || NULL == buf){perror("NULL ERROR");return -1;}int ret = -1;char *errmsg = NULL;ret = sqlite3_exec(db,buf,NULL,NULL,&errmsg);if(ret != SQLITE_OK){printf("Update Error:%s\n",errmsg);return -1;}puts("修改信息成功");return 0;
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！