功能:假如我家开了个水果超市,有以下水果,想实现自动化管理,扫描二维码就能知道当前的水果状态,进货几天了,
好久需要再次进货,那些水果畅销,那些水果不畅销,那些水果春夏秋冬的价格波动,好,那么现在我想将
这些信息保存在数据库中,那么我应该怎么做;
提示: 建立一张fruit表,
假如水果有: 苹果,香蕉,梨,橘子,葡萄....(可以自己查一下英文保存到数据库)
水果价格: 苹果 5元/斤 香蕉 3元/斤 梨 3.5元/斤 橘子2.5元/斤 葡萄 8元/斤....
当前存货: 苹果 80斤 香蕉 200斤 梨 50斤 橘子300斤 葡萄 100斤....
超市每天水果都有进货和卖出嘛,水果的价格随着季节和天气也会有波动,顾客也会看一下每天水果的价格的嘛,
所以要求,根据上述提示,利用数据库完成水果店各种水果的增(进货)删(卖出)改(波动)查(看价格)功能。
好久需要再次进货,那些水果畅销,那些水果不畅销,那些水果春夏秋冬的价格波动,好,那么现在我想将
这些信息保存在数据库中,那么我应该怎么做;
提示: 建立一张fruit表,
假如水果有: 苹果,香蕉,梨,橘子,葡萄....(可以自己查一下英文保存到数据库)
水果价格: 苹果 5元/斤 香蕉 3元/斤 梨 3.5元/斤 橘子2.5元/斤 葡萄 8元/斤....
当前存货: 苹果 80斤 香蕉 200斤 梨 50斤 橘子300斤 葡萄 100斤....
超市每天水果都有进货和卖出嘛,水果的价格随着季节和天气也会有波动,顾客也会看一下每天水果的价格的嘛,
所以要求,根据上述提示,利用数据库完成水果店各种水果的增(进货)删(卖出)改(波动)查(看价格)功能。
并将进出货的时间和顾客光顾的时间记录到数据库中保存。
代码如下
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
int do_insert(sqlite3 * db)
{
char catagory[32]={0};
float price;
int inventory;
char incometime[32]={0};
char * errmsg;
char sql[1024] = {0};
printf("plz input catagory:");
scanf("%s",catagory);
getchar();
printf("plz input price:");
scanf("%f",&price);
getchar();
printf("plz input inventory:");
scanf("%d",&inventory);
getchar();
printf("plz input incometime:");
scanf("%s",incometime);
getchar();
sprintf(sql,"insert into fruit(catagory,price,inventory,incometime) values('%s',%f,%d,'%s');",catagory,price,inventory,incometime);
printf("%s",sql);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
{
if(strstr(errmsg,"UNIQUE") == NULL)
{
printf("%s\n",errmsg);
return -1;
}
sprintf(sql,"update fruit set price = %f, inventory = %d, incometime = '%s' where catagory = '%s';",price,inventory,incometime,catagory);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
return -1;
}
}
printf("insert successfully\n");
return 0;
}
int do_delete(sqlite3 * db)
{
char catagory[32] = {0};
int inventory;
char saletime[32] = {0};
char * errmsg;
char sql[1024] = {0};
printf("plz input catagory:");
scanf("%s",catagory);
getchar();
printf("plz input inventory:");
scanf("%d",&inventory);
getchar();
printf("plz input saletime:");
scanf("%s",saletime);
sprintf(sql,"update fruit set inventory = %d,saletime = '%s' where catagory = '%s';",inventory,saletime,catagory);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
return -1;
}
return 0;
}
int do_update(sqlite3 * db)
{
char catagory[32] = {0};
float price;
char * errmsg;
char sql[1024] = {0};
printf("plz input catagory:");
scanf("%s",catagory);
getchar();
printf("plz input price:");
scanf("%f",&price);
getchar();
sprintf(sql,"update fruit set price = %f where catagory = '%s';",price,catagory);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
return -1;
}
return 0;
}
int callback(void * arg,int f_num,char ** f_value,char ** f_name)
{
printf("price:%s\n",f_value[1]);
return 0;
}
int do_query(sqlite3 * db)
{
char catagory[32] = {0};
float price;
char * errmsg;
char sql[1024] = {0};
printf("plz input catagory:");
scanf("%s",catagory);
getchar();
sprintf(sql,"select * from fruit where catagory = '%s';",catagory);
if(sqlite3_exec(db,sql,callback,NULL,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
return -1;
}
return 0;
}
int main()
{
sqlite3 * db;
char * errmsg;
//打开数据库
if(sqlite3_open("./fruit.db",&db) != SQLITE_OK)
{
printf("%s\n",sqlite3_errmsg(db));
exit(-1);
}
//新建表
if(sqlite3_exec(db,"create table fruit (catagory char unique, price float, inventory Interger, incometime char,saletime char);",NULL,NULL,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(-1);
}
int cmd;
while(1)
{
printf("1:insert 2:delete 3:update 4:query 5:quit\n");
printf("plz input the number\n");
scanf("%d",&cmd);
getchar();
switch(cmd)
{
case 1:
do_insert(db);
break;
case 2:
do_delete(db);
break;
case 3:
do_update(db);
break;
case 4:
do_query(db);
break;
case 5:
sqlite3_close(db);
exit(0);
default:
printf("cmd error\n");
}
}
return 0;
}