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

Dismiss_0

已于 2022-11-18 19:31:51 修改

阅读量1.1k

点赞数 2

文章标签： c语言 linux

于 2022-11-18 19:31:43 首次发布

本文链接：https://blog.youkuaiyun.com/m0_73859283/article/details/127928317

版权

一、超市管理系统

采用数据库（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 不显示则为0
				check.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 不显示则为0
				check.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 不显示则为0
			check.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 不显示则为0
			check.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;
}